Excelのパワークエリで、CSVの請求データを加工する話 作例①

Excel2016以降に標準搭載されている機能であるパワークエリ（Power Query）を使って、データの取り込み→加工→集計を自動化する手順を紹介します。

Twitterで勉強中の進捗をつぶやいていたんですが、詳しい過程とその成果物を紹介するのは140文字では不可能だったので、ブログにまとめてみました。

これまで外部データ接続でExcelにインポートしていたCSVを、 #パワークエリ を使う方法に作り変えた。
従来はインポート後に作業列であれこれ加工していたのが、クエリで事前処理することでキレイに整形できた📄
クエリの関数はまだまだ使いこなせないけど、ひとつ成果物が出来上がって成長した気分。

— RN:kanade (@no9_vc) 2021年4月20日

なお、パワークエリの機能全般についてはこちらの本で勉強しました。色々つぶやいていたら著者様から直接アドバイスをいただけたり、本以外でもお世話になっております。

www.amazon.co.jp

使用するデータ

とある不動産会社では、毎月の賃料請求データをCSVで提供しており、ユーザーはこれをダウンロードして任意の形に加工できます。

このCSVをExcelに読み込んでピボットテーブルで集計したいのですが、後述するようにいくつかの問題点があり、そのままでは正しく集計ができません。手作業で加工するには膨大な手間がかかり、毎月のルーチンワークとしては煩雑です。

使用するデータはこんな感じ。当然ながらダミーデータです。

※CSVのまま埋め込み表示できなかったので、Excelブックにして表示させています。ダウンロードしてCSV（カンマ区切り）で保存すると、後のステップで使えるデータソースになります。

問題点

①タイトル行に改行が含まれている

試しにExcelでCSVを開いてみると、タイトル行の一部にセル内改行が含まれている項目があります。

改行が含まれている列名（セルを選択して数式バーを見るとより分かりやすいです）

このセル内改行が曲者で、そのまま「外部データの取り込み」を行うと、タイトル行の中身が崩れてしまいます。これではピボットテーブルでの集計に使えません。

セル内改行が全体の改行と認識され、タイトル行の残りが2行目以降にはみ出してしまいました

これを回避するため、従来（Excel2013以前の「外部データの取り込み」を使っていた頃）はCSVファイルをExcelで開いて直接編集し、「文字の置換」で改行コードを削除し、上書き保存してからExcelへ取り込む必要がありました。

「文字の置換」ダイアログ。「置換前」にCtrl+Jで改行記号を挿入しており、よく見るとドットが入っています

セル内改行については、こちらのサイトが詳しいです。

Office TANAKA - Excel Tips[セル内の改行を削除する]

②消費税額が複数の列に分散している

「消費税額」という同じ名前の列が複数存在しています。

取り込んだデータは最終的にテーブルとして読み込みますが、1つのテーブル内に同じ名前の列を配置できないため、このままExcelに取り込むと列名に連番が振られてしまいます。

従来はExcelに取り込んだ後、別途「消費税合計」といった作業列を作成してSUM関数で各列の値を加算し、ピボットテーブルに使用していました。が、列が増えすぎて管理しにくく、ソースファイルの変更で行数が増減した際に作業列の関数が上手く動作しないという不安定さがありました。

③集計時に不要な列がある

駐車場以降の項目で「○○（課税）」「課税前」「消費税額」と3つの列が並んでいます。順に税込金額・税別金額・消費税額を意味しますが（税込金額＝税別金額＋消費税）、集計時にこれら全ての値を足してしまうと、合計金額が一致しなくなります。税抜法か税込法のどちらかに揃えれば、もう片方は不要ということになります。

さらに後ろには「請求金額合計」列もあります。合計金額はピボットテーブルの集計で算出できるため、この列も不要です。

④集計したい値が横方向に並んでいる

物件別・室番号別に請求額を合計したいのですが、合計したい値が横方向に並んでいます。このような形式はマトリクス表と呼ばれ、人間が読む分には良いのですが、ピボットテーブルでの集計には不向きです。

しかしながら、これを手作業で縦方向に並べ替えるには膨大な手間がかかります。 

解決策

①Power Query エディターはタイトル行を自動認識してくれる

空のブックを新規作成し、CSVを下の手順で読み込みます。

［データ］タブ＞データの取得＞ファイルから＞テキストまたはCSVから

プレビュー画面で［データの変換］をクリックすると、Power Query エディターが起動します。
クエリの名前は自分で分かりやすいようにすればよいのですが、ここでは「Q請求明細」とします（QはQueryのQ。他のシート名やテーブル名と区別するため）。

「昇格されたヘッダー数」のステップで自動的にセル内改行を除去してくれており、データ行が正常に取り込まれていることが確認できます。

毎月手作業で元データを加工していた身としては、これだけでも大変ありがたいです。

②複数の列の値を加算する

「消費税額」だけを加算した列を追加します。

名前に「消費税額」を含む3列をCtrlで選択し、［列の追加］タブ＞標準＞加算

最後列に新しい列「加算」が追加されました

この後、加算前の「消費税額」とつく3列はDeleteキーで削除します。関数であれば元の値を残しておかないとエラーになりますが、パワークエリはデータの加工履歴を「ステップ」として順に記録しており、加算前の列を削除してもエラーは発生しません。

残った「加算」列をダブルクリックし、名前を「消費税額」に変えておきます。

③既存の列名を変更し、不要な列を削除する

今回は税抜法で集計したいので、税込金額の列は不要です。従って「課税前」の3列をそれぞれ対応する項目名に改め、「○○（課税）」の3列は削除します。

さらに後のステップを考慮すると、「請求金額合計」「備考」の2列も不要なので、同様に削除します。

　列名変更と削除を経て残った数値項目の列。随分スッキリしました

④列のピボット解除

最後に、横方向に並んでいる項目を縦方向に並べ替えます。

「会員番号」～「部屋番号」列を選択し、［変換］タブ＞列のピボット解除＞その他の列のピボット解除を選択

「家賃」以降の列の内容が縦方向に並べ替えられました。

ピボット解除後のレイアウト

金額のデータが1つの列に並んだので、これでピボットテーブルでまとめて集計できるようになりました。あっさり書いていますが、こういった強力なデータ加工機能がパワークエリのすごいところです。

この後、仕上げに列名を「属性」→「請求項目」、「値」→「金額」と改めておきます。

クエリの読み込みと集計

ワークシートへ読み込み

ここまでPowerQuery エディターで処理した内容を、Excelのワークシートに読み込みます。

［ホーム］タブ＞閉じて読み込む

Power Query エディターが閉じ、ワークシート内にテーブルとして読み込まれました。

ワークシート内に読み込まれたクエリの内容

ピボットテーブルで集計

このテーブルを基にして、新しいシートにピボットテーブルを作成します。

フィールドはこのように配置します

出来上がったピボットテーブル（デザインや列項目の並びは手動で調節しています）

 完成形

以下が完成したファイルです。シートを切り替えると、集計元のテーブルを表示できます。

④で値を盾に並べ替えた効果が分かるよう、フィールドを別方向に配置したピボットテーブルも追加してみました。

もう少し補足したいですが、長くなりすぎるので一旦ここで区切ります。

参考になった点などあれば、コメントやツイートで反応をいただけると嬉しいです。