パワークエリはEXCEL2016以降に搭載された機能です。様々な作業が可能なようですが、今回紹介する方法は、一番かんたんで基礎的な使い方のひとつと言えると思います。

たとえば経理業務でよくある以下のような作業の効率化におすすめですので、ぜひ活用してみましょう。

• • • 月ごとの集計ファイルを一年分集計する
    • 別々のデータファイルをひとつのファイルにまとめる

どんなデータから何を集計したいか

まず、今回ご紹介するような簡単な操作で集計できるのは、項目や内容の揃ったデータ、例えば金融機関や決済サービスなどから出力するCSVです。ここから様々な応用ができますので、初めはこの簡単な操作をやってみましょう。

例として、モバイル決済サービス「Squareスクエア」から出力したcsvデータを使います。

 

こちらはクレジットカードで支払われた額を数日分まとめて入金されるように設定されたデータです。

出力される内容は図の通りです。これがひと月＝1ファイルあります。

このデータから、以下の項目を一目でわかるように集計してまとめてみましょう。

1. 1. 1. 銀行入金日と入金額
      2. Aの内訳…売上日(決済日)
      3. Aの内訳…差し引かれた手数料額

集計するCSVファイルを一つのフォルダに保存

まず事前準備として、集計したいCSVファイルを一つのフォルダにまとめて保存しておきます。

 

①　エクセルを開きます(新規作成)

②　メニューの データ

③　データの取得

④　ファイルから

⑤　フォルダから

以上の順で選んでいくと、図のような参照ウィンドウが開きますので、

 

⑥　事前に準備したフォルダを指定します。

するとパワークエリが指定されたフォルダの確認と、この後の操作を問いかけてきます。

⑦　下部の 結合  の隣の▼をクリック

⑧　 データの結合と変換  を選択

 「クエリを評価しています… 」

というメッセージが表示されますが、何もしなくてOKです。

⑨　次に 「Fileの結合 」というウィンドウが表示されますが、これも特に何もせずOK。

 

 

 

その後、以下のようなウィンドウが開きます。これがパワークエリエディターです。

今回は自動的に一行目をヘッダーとして採用されましたが、もしそのようになっていなかった場合は、「一行目をヘッダーとして使用」を選択します。

一番左の列にファイル名が表示されています。スクロールして全てのデータが統合されたことを確認したら、「閉じて読み込む」をクリックしましょう。

エクセル・ピボットテーブルで集計・加工

すると今度は以下のようなテーブル形式でエクセルが開きます。

クエリでも様々加工が可能なようですが、エクセルの方が慣れていて扱いやすいので、こちらで加工していきます。

といっても、ピボットテーブルで必要なデータだけ抽出して集計するだけです。

 

 

 

ピボットテーブルのフィールドから、以下のように必要項目をドラッグすると、入金日ごとに、その内訳が表示されます。

 

必要でしたら、決済日・入金日などの項目を整えたシートを別に作ってもよいですが、ひとまずこれで、銀行への入金時に、入金額に対する手数料がいくらなのかは、一目でわかるようになりました。年間の集計ができると、月をまたぐ決済も一覧で確認できて便利ですよね。

 

データの追加や修正もカンタン！

更に、クエリは最初に指定したフォルダと接続(リンク)しているので、今回のデータの続きが届いた時には、同じフォルダに同じように形式を揃えて追加しておけば、クエリがデータを自動で追加更新可能なのです。

クエリのシートの更新をクリックするだけでOK！

 

 

 

 

テーブルが更新されたので、ピボットテーブルも同じように更新すれば、こちらも最新のデータで集計されます。

 

 

 

 

いかがでしたでしょうか。今回例に使用したスクエアの決済は、Freeeなどの会計ソフトで連携ができていれば、手数料なども自動で仕訳されるので、こちらは必要のない作業です。しかし、何らかの事情で連携ができない場合や、同じように、形式の同じ複数のファイルを集計したい場合などに、覚えておけば簡単に応用できる方法かと思います。ぜひ活用してみましょう！