
「PowerQuery妄想自動集計表」
はじめに
数年前に初めて触れたPowerBI。
Excelは以前からずっと使ってたけど、
Power Query?
Power Pivot?
DAX?
最初は何のことかすら不明だった過去の自分に向けて、
会社員時代に習得した資料の作り方を記します。
パワー何とかって?
という方の取っ掛かりになればとても嬉しいです。
なお、ここでは大枠や流れのみを説明し、
詳細な操作や関数の細かい説明などは省きます。
メリットとデメリット
メリット
シートに直接は何も書かない(関数や罫線など)ので他の人に計算処理を変更される心配がない
一度処理を作ってしまえばあとは更新作業だけ
デメリット
学習コストは多少かかる
例外処理が多いと煩雑になりがち
Officeのバージョン
今回の環境です↓
エディション Windows 11 Home
バージョン 22H2
OS ビルド 22621.2715
エクスペリエンス Windows Feature Experience Pack 1000.22677.1000.0
Microsoft® Excel® 2019 MSO (バージョン 2310 ビルド 16.0.16924.20054) 64
完成させる表
book名は「受注達成奨励一覧表.xlsx」とします。
作成の流れは、
必要なデータ、マスターを揃える
Power Queryで集計する
2のデータをPower Pivotで計算
シートに見たい数字を表示させる
完成形です↓

シンプルにスタッフ別に目標と実績、それに伴う達成率と奨励金を表示させます。
用意するマスター
店舗

目標と単価

実績
ここでは2か月分の実績を集計する想定ですので1人2行ずつとなります。

対象年月

社員マスタ
社員の所属店舗コードを持つ、社員マスター.csvをExcelとは別に用意します。
※内容はダミーで作成

Power Queryでの処理
先ほど作った各種マスター、データをどんどんPower Queryにいれていきます。
Excelシートに作ったマスターの取り込み
店舗、目標と単価、実績、対象年月マスターをPowerQueryに入れます。
順序は任意ですが、ここでは店舗マスターから。
手順は↓
テーブルの任意のセルを選択しておく
データタブから「テーブルまたは範囲から」をクリック

すると自動的にPowerQueryに追加されます。
同様の手順で「目標と単価」「実績」「対象年月」マスターを追加します。
外部ファイルの取り込み
最後に「社員マスター.csv」をPowerQueryにいれます。
手順は↓
データタブから「テキストまたはCSVから」をクリック

社員マスター.csvを選択したらインポート(M)をクリック。

すると下記のような画面が現れるので「読み込み」をクリック。

取り込まれると下記のような表示がでます。

テーブル操作
月別の表にするのでまず対象年月をソースにします。

次に社員マスターを結合。
ここではカスタム列で新しい列を作っています。

目標単価」「実績」「店舗」を順に結合していきます。
ここでは外部結合のみでつないでます。
Power Queryでの最終形は↓

Power Pivotでの処理
Power QueryでのデータをそのままPower Pivotへ引き継ぎます。


達成率やマージン金額の計算
Power Pivotウィンドウを開き、必要な計算列や合計の式を作ります。

シート上で表示して完成
最後にシートに表示させれば完成です。
メニューのピボットテーブルを選択→シートの既存か新規を選択するとピボットテーブルのフィールドが現れます。
あとは通常のピボットテーブルと同じ操作感でできると思います。


タイトルや条件付き書式で達成率をピンクにしたら完成!
まとめ
DAX関数やその周辺知識は膨大で、わたし自身使いこなしているレベルではありませんが、上記のようなやり方で
・給与計算及び一覧表
・請求書発行
・DMラベルリストアップ
・PDFの請求書から仕訳を作成、など
を運用していました。
また、
・VBAでいろいろやっているもの
・シートに関数をたくさん入れてる煩雑な表
などを手放すことができるかもしれません。
これが誰かの業務効率化の一助になれば幸いです。