スポンサーリンク
はじめに
下図のExcel表は、タイトル行の作り方に問題がある。
- 1行目が、B列～J列までで9個の文字があるのでなく、「4月」「5月」「6月」の3個しか文字が無いうえ、セル結合までされている。
- 2行目「実績」「予算」「差異」の文字列が3回繰り返されている。Excel表のタイトル行というのは、決して同一の文字列が2回以上繰り返されてはいけない。
Excel初心者のうちは、これの何が悪いのかも分からないものだが、こういう表の作り方は一刻も早く卒業しなければならない。
このくらいならマシな方だが、こんな風にタイトル行がセル結合されていたりするExcel表の整形にも、PowerQueryは非常に役立つので、今回はそれを取り上げる。
そして今回の場合は、これまでの記事で扱ったテクニックの組み合わせでいける。
上記の表のExcelファイルは、こちらに保存しておいたので、今回はこれを用いて説明する。
元データExcelファイルを開く
それではまず、今回の表を、PowerQueryを用いて下図のような形式に加工することを目標にする。
タイトル行が1行に統合され、全てのタイトル文字が違うものになっているもので、ひとまず使えるものになる。
ではまず、
リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「ブックから」
として、今回のExcelファイルを開く。
そしてナビゲーター画面の左端からシートをクリックし「データの変換」を押す。
表の縦横を入れ替えてからの作業
縦横の入れ替え
考え方として、まずタイトル行の2行(横方向)をPowerQuery上で結合するのは難しいので、結合しやすいように縦方向に変換する。
リボン「変換」タブ
→「入れ替え」
で、表の縦横を丸ごと入れ替える。
データのフィル
タイトル行は元々セル結合とかされてたので、縦横入れ替え後は下図のように「Column1」の中に「null」という箇所が出てくる。
そこには、1個上のセルの値を埋め込めば良いので
リボン「変換」タブ
→「フィル」
→「下」 とする。
列のマージ
「Column2」において「実績」「予算」「差異」の文字列が繰り返されているのが良くないので、「Column1」と「Column2」を結合して1つの列にする。結合といっても、もちろんセル結合なんかではない。
リボン「変換」タブ
→「列のマージ」
とする。
列のマージに関し、区切り記号など設定する画面が出てくる。
区切り記号は、後で元の見出しに戻した時を考えて、適切な記号にする。
ひとまず今回は「コロン」を選択。
「新しい列名」についても、一時的なものでしか無いので適当で良い。そのまま「OK」を押す。
縦横を元に戻す
縦横の再入れ替え
縦横をいったん入れ替えてからの作業を済ませた。もう一度、
リボン「変換」タブ
→「入れ替え」
として、縦横を元に戻す。
ヘッダー設定
縦横を入れ替えた直後は、タイトル行が正しく認識されず「Column1」「Column2」などとなっているので
リボン「変換」タブ
→「1行目をヘッダーとして使用」
とする。
Excelシートに展開
1列目のタイトルが「:部門」と、余計な「:」文字が付いているが、これはF2キーからの列名変更で消しておけば良い。そして
リボン「ホーム」タブ
→閉じて読み込む
とする。
これで、下図のようにExcelシートに展開されて、今回の作業は終わり。
最後に
今回のような表の形式に変換しても間違いとまでは行かないが、横にだだっ広くなるし、データベースとして活用はしにくい。
一応、報告書形式として元のフォーマットを極力崩さない形への変換を、今回は取り上げた。
実際の理想としては、下図のように変換するべきだ。
「実績」「予算」「差異」の数値項目は横軸、数値以外の「部門」「月」は縦軸に置いた形式だ。
次回の記事で、この形式に変換する方法を取り上げる。
スポンサーリンク
