私はクレジットカードを数枚持っていて、これをweb明細をダウンロードして管理していました、web明細は一月ごとに送られるので、それを連結して一つの表にできると便利です。連結するには手動でやるか、マクロを作成するか、accessで取り込み連結した後、excelに出力するかでした。Power queryなら、表の連結をもっと効率的に出来ることが分かっていたのでやってみることにしました。
月毎の明細データファイルを連結する
使用する明細データファイルのサンプル
明細データはカードのメンバーサイトから、csvデータでダウンロードできます。
この＜ダウンロードリンク＞をクリックすると、この記事で使う明細データサンプルがダウンロードできます。
ダウンロードした複数のcsvファイルを、新しいローカルフォルダを作成し、格納して
記事に従って作業すると、結合ファイルを作成することができます。
サンプルは2024年の8月(UC_c2408.csv)、９月(UC_c2409.csv)、10月(UC_c2410.csv)の支払のカード明細データファイルです。
各ファイルは、同じ構造になっています。下図は8月の支払明細データです。
最初の4行が、ヘッダ行で各ファイルに同等にあります。5行目が列見出し行で、これも各ファイル共通です。
以下、列見出しに対応するデータが支払毎に入ります。支払いデータの行数はファイル毎に異なります。最終行の”利用金額”と”今回のお支払金額”の列には合計額が入っています。
今回記事の作業で各ファイルからヘッダ行4行が取り除かれ、1行目に列見出し、以下行に各月の支払データが続くテーブルが生成されます。最終行の合計行は取り除かれます。
ファイルを結合する
excelブックを開いて”データー”タブの”データ取得”をクリックしてドロップメニューで”フォルダーから”を選択します。
サンプルファイルの格納フォルダを選択すると、上記ダイアログボックスが表れるので、”データーの結合と変換”をクリックします。
下図のダイアログボックスが表れますが、そのまま”OK”をクリックします。
すると、PowerQueryエディターが表れ、3このファイルの結合ファイルが表示されます。ただこれはまだ上位４行の削除、列見出しの設定、集計行の削除はされていません。これを行うにはエディターの左列”クエリ”の”サンプルファイルの変換”をクリックして、下図のようにサンプルファイルとなっている８月支払明細ファイルUC_c2408.csvを表示させ、これを編集します。
ファイルを編集する
下図のリボンメニューの”行の削除”＞”上位の行の削除”をクリックしてダイアログボックスから上位4行を削除します。
そしてメニューから、”1行目をヘッダーとして使用”をクリックすると、5行目が列見出しになります。
集計行を削除するために、”利用日”のフィルタで”空白の削除”をします。
編集結果からエラーを除く
次に、左の列のクエリで一番下の”明細UC_c”をクリックして右に編集結果を表示すると
”テーブルの列Ｃolumn1が見つかりませんでした”というエラーが表示されています。
”Ｃolumn1”は”サンプルファイルの変更”で5行目の列名に変更したことが理由で、右列の”クエリの設定”の最後のステップ”変更された型”で失敗したと言うことらしいです。
そこで、この最後のステップを、右クリックして削除します。
すると、エラー表示が消えて、編集された結合ファイルが表示されます。
ただし、最後の型変更のステップを削除したので、ヘッダ行の各列見出しの左端にあるアイコンを押してデータ型の設定をやり直します。以上でPowerQueryエディターでの編集が完了です。
エディタのトップメニューの”閉じて読み込む”をクリックし、エクセルシートに表示します。
下図がシートに読み込まれた結合テーブルです。
後は、エクセルシートに読み込まれた上記テーブルを、分析可能なピボットテーブルにした例です。
まとめ
毎月のカードの利用明細のチェックはとても大事だけど、ついつい面倒で、スルーしてしまいがちです。今回の記事では、そんなチェック作業の助けとして、PowerQueryで、明細データの集計分析をする方法を紹介しました。同じようなことは、excelマクロのコードでもできるのですが、コードを書く必要のないPowerQueryでやる方が簡単です。
PowerQueryはスクレーピングとか、データ処理解析機能があって大量データを扱うのにとても強力なツールです。日常生活で利用することはあまり有りませんが、もっと利用方法がないか探してゆきたいと、この記事を書いて思いました。
