見出し画像
Photo by chiyoizmo

「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」とします。
作成の流れは、

  1. 必要なデータ、マスターを揃える

  2. Power Queryで集計する

  3. 2のデータをPower Pivotで計算

  4. シートに見たい数字を表示させる

完成形です↓

完成図

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

用意するマスター

店舗

シート「店舗」にテーブルとして作成

目標と単価

シート「目標と単価」にテーブルとして作成

実績

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

シート「実績」にテーブルとして作成

対象年月

シート「対象年月」にテーブルとして作成

社員マスタ

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

CSVファイルで準備

Power Queryでの処理

先ほど作った各種マスター、データをどんどんPower Queryにいれていきます。

Excelシートに作ったマスターの取り込み

店舗、目標と単価、実績、対象年月マスターをPowerQueryに入れます。
順序は任意ですが、ここでは店舗マスターから。
手順は↓

  1. テーブルの任意のセルを選択しておく

  2. データタブから「テーブルまたは範囲から」をクリック

シート上でのマスター取り込み

すると自動的にPowerQueryに追加されます。
同様の手順で「目標と単価」「実績」「対象年月」マスターを追加します。

外部ファイルの取り込み

最後に「社員マスター.csv」をPowerQueryにいれます。
手順は↓

  1. データタブから「テキストまたはCSVから」をクリック

CSVインポート

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

csvインポート

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

PowerQuery取り込み

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

取り込み完了

テーブル操作

月別の表にするのでまず対象年月をソースにします。

対象年月

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

社員マスター結合

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

powerquery

Power Pivotでの処理

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

クエリと接続から引き継ぐクエリ上で右クリック→読み込み先を選択
このデータをデータモデルに追加する（M)にチェックをいれる

達成率やマージン金額の計算

Power Pivotウィンドウを開き、必要な計算列や合計の式を作ります。

緑ヘッダーがPower Queryデータ、黒ヘッダーがPower Pivotで追加した計算列

シート上で表示して完成

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

メニューのピボットテーブルを選択
シートでピボット表を表示させる

タイトルや条件付き書式で達成率をピンクにしたら完成！

まとめ

DAX関数やその周辺知識は膨大で、わたし自身使いこなしているレベルではありませんが、上記のようなやり方で

・給与計算及び一覧表
・請求書発行
・DMラベルリストアップ
・PDFの請求書から仕訳を作成、など
を運用していました。

また、
・VBAでいろいろやっているもの
・シートに関数をたくさん入れてる煩雑な表
などを手放すことができるかもしれません。

これが誰かの業務効率化の一助になれば幸いです。

この記事が気に入ったらサポートをしてみませんか？