スポンサーリンク
はじめに
ExcelのPowerQueryや、あとテーブルというのは、見出し(タイトル)の文字列は固定にして変えないのが本来のやり方だ。
特にPowerQueryは、ここのタイトル文字列はこれと、細かく内部に指定しておくことが多いため、タイトル文字列が変化する表の処理にはあまり適さない。が、対策はそれなりにある。
今回は、そういったタイトル文字列が変わり得る表を、PowerQueryで加工してみる。
あとついでに、これまでの記事で扱った色々なテクニックを使っていく。
今回の例題
下図の表では、
- A列「部門」B列「科目」は、タイトル文字列は固定とする。
- しかしC列以降には年月の文字が入っているが、この年月は変わり得るものとする。
たとえばC列が2020年04月から始まるかもしれない。
ただ「○○年○○月_予算」「○○年○○月_実績」の順で並ぶ法則は固定。
とする。このC列以降のタイトル文字列が変わり得ることに、どう対処するかだ。
今回の、この表のサンプルファイルは、こちらに保存してあります。
改良点～目標
あとこの表は、タイトル文字列が変わり得るということ以外に、改良したい点が幾つかある(明確に問題とまで断言はできないが)。
列挙すると、次のような感じになる。
- 年月「2020年12月」とか、そういう数値そのものと言えない部分は、ヨコ軸でなく縦軸にするべき。
- 「予算」「実績」とかいうのは、数値そのものと言える項目なので、横軸のままで良い。
- 「予算」「実績」については、それらの差額の列を追加したい。
- 科目の列には「売上」「原価」「販管費」とあるが、売上から原価を引いて求められる「粗利」の科目もほしい。
それらを改良して、下図のような感じに組み替えることを目指してみたい。
これは、通常のExcel操作でも難しいし、VBAでもちょっと込み入ったものになる。PowerQueryが最も適している。
ファイルを開く
では新規Excelブックにて、PowerQueryでExcelブックを開く定型手順
リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「ブックから」
として、今回のファイルを開く。
そしてナビゲーター画面で、シート名「元データ」を選び「データの変換」を押す。
「変更された型」はちょっと厄介
タイトル文字列の決めつけが入る
これでPowerQueryの画面にデータが読み込まれるのだが、最上部の数式ウインドウをよく見てみよう。
= Table.TransformColumnTypes(昇格されたヘッダー数,{{“部門”, type text}, {“科目”, type text}, {“2020年12月_予算”, Int64.Type}, ・・・・・・
という数式が表示されている。
何が問題かって、“部門”とか“科目”とか、タイトル文字列がこの時点で決定されていることだ。
特に“2020年12月_予算”とか、変化し得るタイトル文字列の部分が決定されているのがマズい。
今回のケースに限らず、ちょっと慣れてきてPowerQueryのステップを後から色々と変更していると、こういう風にタイトル文字列がきっちり決められている場合に、後からエラーメッセージが出ることがある。
ここでエラーの原因になっているのが、生成されたPowerQueryのステップで一番最後にある「変更された型」だ。
ついでに
リボン「ホーム」タブ
→「詳細エディター」
で、ソースコードの全体を見てみよう。
すると下図のように、「変更された型」の部分に、“2020年12月_予算”とかタイトル文字列を決めつけた記述がある。
逆に、それ以外の部分では、それらしいものは無い。
慣れていないと見ても分からないかも知れないが、とにかくタイトル文字列を決めつけているものは存在しない。
ということで、問題になるのは「変更された型」の部分だ。
「変更された型」は(デフォルトでは)勝手に入る
そもそも単にExcelブックを読み込んだだけで、型の変換なんてコマンドを指定してはいないのに、この「変更された型」が勝手に入っている。
要するにPowerQueryでは、読み込んだデータの各列に対して、文字列なのか数値なのかとか、データの型ってものを決めたがっているということだ。
この「変更された型」が入っていなかったら、データの型は「すべて」ということになるのだが、それだとちょっとデータが重くなる。
文字列なら文字列、数値なら数値と、データの型を限定して決めてスマートにしたいので、勝手に「変更された型」なんていうものが入る。
今回の例題に限らず、「変更された型」は、余計なお節介となることが多いものだ。
ただ、これを自動的に入らないようにする設定もできるので、その記事を追加した。
さて、長くなったが、とにかく問題の原因は「変更された型」ステップだ。
このステップの左端にある☓印を押して、ステップを削除しよう。
これでひとまず、タイトル文字列が決めつけられてしまう問題は回避できる。
ここから先も、同様にタイトル文字列が決めつけられるような処理になっていないか、留意しながら進めていく必要はあるけど。
タイトル文字列が固定されている列を除き縦横変換する
今回の場合は、左端「部門」「科目」の2列だけは一応、タイトル文字列は固定ということで良い。
その2列を除いた全ての列が、タイトル文字列が変わり得る数値項目の列だ。
では固定となる「部門」「科目」の2列を選択した上で
リボン「変換」タブ
→「列のピボット解除」の右端の▼ボタンをクリック
→「その他の列のピボット解除」
としよう。
「列のピボット解除」は、PowerQueryの大きな機能の一つで、ヨコ方向の軸を縦方向へ変換できるものだ。
その中でも「その他の列のピボット解除」は、固定選択した以外の列を、列名が分からなくて良いので縦方向へ変換できるものだ。
これで、「部門」「科目」の2列を除いた数値項目の列が、縦方向に変換される。
念のため数式ウインドウを確認してみても、
= Table.UnpivotOtherColumns(昇格されたヘッダー数, {“部門”, “科目”}, “属性”, “値”)
となっていて、列名の情報が必要なのはこの2列だけということで認識されている。
まだ不安かもしれないがこれでOKなので、先に進む。
区切り記号で分割
では「属性」の列が「2020年12月_予算」とかいうようになっているが、これを
「2020年12月」「予算」
というように、年月の情報と項目の情報とに分割してみたい。
この場合、「_」の記号で、年月の情報と項目の情報が結合されているので、その記号を元に分割する。
「属性」列を選択した状態で
リボン「変換」タブ
→「列の分割」
→「区切り記号による分割」
とする。
区切り記号の入力画面になるので、上から順に「カスタム」「_」としよう(今回の場合、いかにも区切り記号っぽいのが「_」なので、デフォルトでこの表示になっていると思う)。
そして「OK」を押す。
ここでも型の変換が勝手に入る
そうすると分割されるが、年月の情報が「属性.1」という項目になっているが、本来「2020年12月」などの表示のはずが、勝手に「2020/12/01」とかいう表示になっている。
これもまた、先程と同様「変更された型」ステップが勝手に挟まれ、「2020年12月」などが勝手に日付と判定されたことによるものだ。
まあこのままでも場合によっては処理を進められるが、ひとまずこの「変更された型」ステップは☓印を押して削除しておこう。
これで表示が「2020年12月」など表示が元通りになった。
ついでに
「属性.1」→「年月」
「属性.2」→「項目」
とタイトルを変更しておく。
縦の項目を横へ変換
次は、「科目」の列を一度、ヨコ軸に配置してみる。「科目」列を選択した状態で
リボン「変換」タブ
→「列のピボット」
としよう。
数値項目を選択する画面で「値」を選択して「OK」を押す。
カスタム列の追加
なぜ科目をヨコ軸にしたかというと、新しい科目を追加してみたいからだ。縦軸のままだと無理に近い。
「売上」「原価」の科目があるが、
< 売上 － 原価 >
で求められる「粗利」の科目を追加したい。
では
「列の追加」タブ
→「カスタム列」
としよう。
「新しい列名」欄に、科目名の「粗利」を入力。
「カスタム列の式」欄に
= [売上]-[原価]
と入力する。
[原価]
の文字列は、右端の「使用できる列」から項目をダブルクリックして挿入すれば良い。
入力できたら「OK」を押す。
これで、新しい科目「粗利」が右端に追加される。
科目の順番としては、売上＞原価＞粗利＞販管費 なので、そのように列の順序は入れ替えておこう。
再度、科目を縦軸に戻す
先程、科目をヨコ軸に移動したのは、科目「粗利」を追加するため、一時的にやっただけのものだ。
ではこの「科目」を、再び縦軸に戻そう。
ヨコ軸を縦軸にするのは、最初にやった「列のピボット解除」だ。
「部門」「年月」「項目」の3列を選択して
リボン「変換」タブ
→「列のピボット解除」の右端の▼ボタンをクリック
→「その他の列のピボット解除」
とする。
これで、売上・原価などの科目は「属性」という項目になって縦軸に変換される。
この「属性」を、「科目」タイトルにしておく。
予算・実績をヨコ軸に変換する
では、予算・実績の種別が書いてある「項目」列を、縦軸からヨコ軸に変換してみる。
「項目」列を選択した状態で
リボン「変換」タブ
→「列のピボット」
とする。
数値項目を選択する画面で「値」を選択して「OK」を押す。
これで「予算」「実績」がヨコ軸に変換される。
と、それは良いのだが、「科目」の並びがちょっと良くない。
この「科目」は、
売上
原価
粗利
販管費
の順に元々並んでいて、その順序が乱れないようにしたい。
この並び順を元通りに保つというのは、科目だけについて保てるようにするのか、他の項目についても保つのかという問題はあるが、ひとまず全項目について保つようにしてみたい。
では今の「列のピボット」処理により、PowerQueryのステップの最後に「ピボットされた列1」というのが追加されたが、これを消すのは勿体ない。
その1個前に「名前が変更された列1」というのがあるはず(「属性」列を「科目」に名前変更したもの)で、それを選択しておくことで前に戻れる。
そして、列のピボット処理に関わる項目である「項目」「値」以外の「部門」「年月」「科目」列を選択した状態で
リボン「ホーム」タブ
→グループ化
とする。
後付けでステップを差し挟むので、「ステップの挿入」という確認メッセージが出ると思うが、構わず「挿入」を押す。
次の画面で、「操作」欄で「すべての行」を選んで「OK」を押す。
これで「部門」「年月」「科目」列だけが表示された状態で、他の項目が「カウント」列に押し隠される。
ではこの状態での各項目の並び順を、数字を使って保持するために、インデックス項目を追加する。
リボン「列の追加」タブ
→インデックス列
とする。派生メニューで「1から」を選んで1開始にしても良いし、デフォルトで「インデックス列」を押したら0になるけどそれでも良い。
インデックス番号が追加される。
インデックスを付けたので、一時的にグループ化して隠しておいた「カウント」列を再度展開する。
「カウント」列の右端の□ボタンを押す
→非表示にしておいた「項目」「値」だけにチェックを入れる
→「元の列名をプレフィックスとして使用します」のチェックを外す
→「OK」
このままだと、追加したインデックス列のインデックス列としての性質が邪魔するらしく、なぜか「Expression.Error: インデックスがレコードの範囲外です。」というエラーになってしまう。
何か処理ステップを挟めばそれは解決できるようなので、ひとまず「インデックス」列をどこか左側にドラッグして移動しておく。
そうして一番下の「ピボットされた列1」を再度押すと、「予算」「実績」列がヨコ軸に変換された状態で表示されてくれる。
「インデックス」列の並びが変になっているので、この列を昇順に並べ替えよう。
インデックス列を昇順にしたことで、科目などデータの並び順が最初の通りに戻る。
ではここから、予算と実績の差額を項目として追加しよう。
先ほど、粗利の項目を追加したのと同様に
「列の追加」タブ
→「カスタム列」
とする。
「新しい列名」欄に、科目名の「予実差異」と入力。
「カスタム列の式」欄に
= [実績]-[予算]
と入力する。
[予算]
の文字列は、右端の「使用できる列」から項目をダブルクリックして挿入すれば良い。
入力できたら「OK」を押す。
これで、新しい項目「予実差異」が追加される。
色々とやってみたが、PowerQueryではこうやって縦横を入れ替えたり元に戻したりを繰り返すなどして、数値を柔軟に加工することができる。
タイトル文字列が可変の場合なんて特に、こういう長ったらしい処理を組み込む際に注意が必要になってくるが、数をこなして慣れていきたい。
スポンサーリンク
