【経理会計Excel】PowerQueryで複数のデータファイルを一つにまとめる方法

今回は、PowerQuery(パワークエリ)を用いて、請求書の元となるたくさんのデータファイルを一つの一覧表にまとめる簡単なテクニックを解説します。

はじめに

売上データを日々登録するのに、売上データ管理システムのような専用プログラムで作成されたシステムにデータを蓄積しておくというケースがあります。
そこに蓄積されたデータはExcelでも活用できるようにデータを取り出せる形になっていることが多いです。
しかしそのダウンロードしたデータの形が思った通りの形ではない場合もあります。

例えば請求書を作成するにあたり、一ヶ月分の売上先ごとに取りまとめたいのですが、Excelに取り出せるデータは、次の例のように1日ごとに1ファイルになっていて、そこにはどこに何を販売したかというファイルの形式でしか取り出せないというケースがあるかもしれません。

ファイル例

そうなると請求書を作成するときに、1ヶ月分であれば20個から30個程度になる売上ファイルを、一つの一覧表にまとめる必要が出てきます。
一般的にこの作業をする手順を考えると、ファイルを一つずつ開いてコピーして、また次のファイルを開いてという作業を何回も繰り返すことになります。
非常に面倒な作業である上に、時間もかかり、また、作業ミスによってコピーしないファイルが出てきたり、同じファイルを2回貼り付けたりするということも考えられます。

そこでExcelでこのようなバラバラのファイルをひとつの一覧表にまとめる機能があるのでご紹介します。
今回はサンプルファイルとしてZIP圧縮ファイルを用意しています。解凍してぜひ実践してみてください。

サンプルファイル

サンプルファイルはコチラ

元データについて

今回は下記のようなデータを12月1日から12月5日までの5つのファイルで用意しています。
「191226pqdata」というフォルダーに中に「1日から5日」のフォルダーがあってその中に「191201.csv」から「191205.csv」までの5ファイルです。

「191226pqdata」フォルダー内のcsvファイル

サンプルファイルは、Excelのブックファイルではなく、「CSVファイル」という種類のファイルです。
CSVファイルは、普段はセルに設定されている書式や様々な設定がされていません。また、計算式も保存されていません。記録されているのは文字と数字だけです。
これが項目ごとにカンマで区切られて記録されています。

メモ帳でCSVファイルを開くとカンマ区切りになっている

CSVファイルは非常に単純な記録形式になっているので、機能や見やすさはありませんが、Excel以外のソフトウェアでも簡単に開くことができます。
売上管理システムのような専用プログラムでは、データ取り出し機能のことを「エクスポート」と呼んでおり、このCSV形式でエクスポートされるような形になっています。

解説

データの取得機能

では実際に操作してみましょう。
Excelで新規ファイルを一つ作成してください。
今回のような複数のファイルを一つの一覧表に集約するという機能は、「データの取得」機能です。
「データの取得」機能は、リボンの[データ]タブの中にあります。
「データの取得」をクリックすると、Excelでデータを様々な形で取り込むことができるというのが分かると思います。
今回は一つのフォルダーの中のデータを全て一つにまとめるので、[ファイルから]の中の[フォルダーから]をクリックします。

データ→データの取得→ファイルから→フォルダーから

すると、フォルダーを指定するウィンドウが表示されます。
[フォルダーパス]の[参照]のボタンをクリックして、解凍したサンプルファイルの中にある「1日から5日」のフォルダーを指定しOKボタンをクリックします。

[フォルダーパス]→[参照]のボタン

ファイルの一覧表が表示されますので、[結合]ボタンの中の、[データの結合と変換]をクリックします。

データの結合と変換

[Fileの結合ウィンドウ]が表示されますので、そのままOKボタンをクリックします。

Fileの結合ウィンドウ

[PowerQueryエディター]の画面が表示されます。
詳しくは後で説明しますが、
この「PowerQuery」という機能が結合を自動化してくれています。
この時点ですでに全ファイルが結合されていますね。

PowerQueryエディター

実際のファイルの内容の左に「Source.Name」という項目名でファイル名も表示されています。
今回の場合、ファイル名が売上日付になっているので、この項目を変換して売上日を一覧表に表示してみましょう。
[PowerQueryエディター]の[列の追加]タブをクリックし、「Source.Name」の項目名をクリックした後、[例からの列]の下向き三角をクリックし、[選択範囲から]を選択します。

PowerQueryエディター

そうすると右側に空白列を表示することができます。

PowerQueryエディター、空白列を表示

ファイル名が「191201.csv」の行の新たにできた空白列に「2019/12/01」と入力します。

PowerQueryエディター、空白列に「2019/12/01」と入力

この時点では何も起きません。
もしくは、入力した行以外のデータが不規則なデータになっている場合もあります。
ここで、ファイル名が「191202.csv」の行の空白列に「2019/12/02」と入力します。

PowerQueryエディター

するとExcelが変換の法則を判断して、自動的に他の日付も出してくれます。
こうなったらOKボタンをクリックします。
これで日付欄が自動作成されるようになりました。

「カスタム」となっている項目名を「日付」に変更します。

PowerQueryエディター、「日付」に変更

ここでこの日付は、まだ文字列として判断しているので、[ホーム]タブの[データ型]が「テキスト型」になっているところを「日付」に変更します。

PowerQueryエディター、「日付」に変更

新たに「日付」とした項目名のところを右クリックして、[移動]の中の[先頭に移動]を選択します。

PowerQueryエディター、[先頭に移動]を選択

日付が先頭の列に移動しました。
ここまで作成したら日付の元となった「Source.Name」の項目は不要ですので、「Source.Name」の項目名をクリックしてDeleteキーで削除します。

PowerQueryエディター、Deleteキーで削除

Excelでは元のデータを削除したらそれを元にしたデータの計算がおかしくなりますが、PowerQueryでは削除しても、それを元にしたデータはそのまま保持されます。

では、このデータをExcelのシート上に表示させます。
[ホーム]タブの[閉じて読み込む]の下向き三角をクリックし、[閉じて次に読み込む]をクリックします。

PowerQueryエディター、[閉じて次に読み込む]

データのインポートの画面が表示されますので、[テーブル]を選択し、[新規ワークシート]を選択すれば新しいワークシートにこの結果を表示します。
また[既存のワークシート]を選択して[出力先のセル]を選択して、そこに表示させることもできます。
今回は[テーブル]、[新規ワークシート]を選択して、OKボタンをクリックします。

データのインポート

Excelに処理した結果を表示することができました。

Excelに結果表示

PowerQueryでデータの追加

先述のように、今回はPowerQuery機能の一部を使って複数ファイルを一つにまとめるという操作をしました。
ファイルの内容をコピーして一つ一つ貼り付けるのではなく、PowerQueryで行うことでのメリットもたくさんあります。
圧縮ファイルの中に「追加データ」というフォルダーがあり、その中に12月6日と7日のデータが「1206.csv」「1207.csv」というファイル名で入っています。
他のデータの項目名の並びは、「売上先」「商品」「個数」の順ですが、7日のデータだけは、「商品」「個数」「売上先」で並んでいます。

PowerQueryのメリット

では、エクスプローラを使って、このデータを「1日から5日」のフォルダーに移動してみましょう。

PowerQuery、ファイル追加

この時点では、まだ何も変わりませんが、今回出来上がった表を右クリックして[更新]をクリックします。

PowerQuery、ファイル追加、更新

すると、新たに12月6日と7日のデータが一瞬にして追加されます。
しかも7日のデータの項目順もしっかり他に合わせて並び変わっています。

PowerQuery、ファイル追加完了

自動的には更新されないのですが、更新作業を行うことで、PowerQueryに登録した今回の処理内容である「フォルダーの中のデータを一つの一覧表にまとめる」一連の作業が実行されます。
項目順も、あくまで項目名で判断しますので列の順もバラバラでも大丈夫です。
これで翌日以降もデータファイルをどんどんフォルダーに入れ、更新するだけですべてのデータを反映した表が作られていきます。

PowerQueryのデータはテーブルになる

PowerQueryで処理されたデータは、Excelのテーブルの形で出力されます。
テーブルになっていると、フィルターや並べ替えが簡単にできたり、綺麗な書式が自動的についたりするメリットがたくさんあるのですが、書式の設定などが思い通りにできない場合もあるので、テーブルのデータを値として別シートに貼り付けて活用しても良いかもしれません。
値として貼り付けたデータは、元のデータと連携が外れているので更新することはできなくなっているので注意が必要です。

まとめ

今回は、実際の業務でもよくあると思われる、バラバラになっている元データファイルを一つのファイルにまとめる最も簡単なテクニックである、PowerQueryを使った方法を紹介しました。
PowerQueryはExcel2016バージョン以降、またはOffice365で標準装備されています。
Excel2010以降であれば、後で必要なインストールを行うことで機能を追加することができます。
PowerQueryのような新しい機能は英語表記であることが多く、それで難しく感じるかもしれません。でも実際には画面を見て行きながらどうしていくか選んで操作するという作業でできてしまいますので、便利な機能は積極的に使って業務効率化をしていきましょう。

執筆者情報
サトウヨシヒロ
ExcelをはじめとしたOfficeソフトのプロフェッショナル。
テクニカルライターであり、インストラクターであり、業務改善のエキスパートで、セミナーやパソコンレッスン、雑誌、ブログ記事の監修・執筆をしています。
Officeの魔法使い
Twitter・佐藤嘉浩(Excelの魔法使い・スピルのひと)@仙台

Excelに関する最新ニュース・情報20件

2019.12.13

sweeep|請求書AI-OCR

請求書の会計処理を自動化します
請求書AI-OCR「sweeep」

面倒な請求書の会計処理をAIで自動化。100枚をたった3分で処理できます。月末に貯まった請求書を一掃しましょう。