Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PowerQuery入門

ikuma-t
February 15, 2021

 PowerQuery入門

Excelの機能PowerQueryについて、社内の勉強会で話した際の資料です。

ikuma-t

February 15, 2021
Tweet

More Decks by ikuma-t

Other Decks in Business

Transcript

  1. PowerQueryは「データの加工に特化したExcelの機能」 10 加工プロセスを「ステップ」として記録する 02 元データ受領 1 データをExcelに貼り付ける 2 カンマで列に区切る 3

    不要な列・行を削除する 4 導出項目の計算式を入力する 5 見た目を整える 6 CSV、Excel、txt…etc 「クエリ」として読込 PowerQueryエディタ 加工プロセスをステップとして自動的に記録 カンマで分割 不要行・列削除 カンマで分割 カンマで分割 不要行・列削除 計算式追加 加工プロセスは残らない 完成 必要に応じて クエリを出力 PowerQuery Excel手加工
  2. PowerQueryを利用するメリット 11 VLOOKUPではだめですか? 03 プロセスの変更・転用が容易 データ加工プロセスはステップ として記録され、いつでも変更 転用が可能です。 VBAや式よりも速度が速い M言語で構成されたプログラム

    により、軽快かつ高速にデータ 加工を行うことができます。 計算式を覚えるよりも直感的 に理解することができるため、 データ加工に集中できます。 操作が簡単 Easy!
  3. ①単価マスタ.csv (タブ区切り) ②売上原本.csv (カンマ区切り) ①売上実績.xlsx 出力データ 入力データ • 売上原本にある品目CDは、 接頭辞に「X」が付与されている。

    • 売上原本には売上数量のみが記録さ れている。 • 販売単価はマスタの値に準ずる。 • 売上原本には数カ月分の売上が記録 されている • 必要な項目は • 売上実績日 • 品名 ※単価マスタから取得 • 売上数量 • 売上金額 ※導出項目 • 売上実績が2020/12のデータのみ を抽出する。 最速でPowerQueryを使う この章では、PowerQueryを今すぐ使いたい方向けに最小限の操作を解説します。 操作の流れと使用するデータは以下のとおりです。 13 必要最小限を実戦形式で学ぼう 04 データの取込 1 列の分割 2 テーブルの結合 3 列・行の追加・並替・絞込 4 データの出力 5
  4. 品目CD 1 2 3 ①データの取込 【やること】 1. 元データをPowerQueryエディタに取り込む(型や区切り文字が自動判定される) 2. 数値で取り込まれた「品目CD」を、文字列に変更する

    14 ここでやることの確認 05 入力データ ①単価マスタ.csv (タブ区切り) ②売上原本.csv (カンマ区切り) PowerQuery エディタ データ取込 品目CD 0001 0002 0003 型変換
  5. ②列の分割 22 「X0001」→「0001」に分割する 06 分割対象の列をクリックした状態で [ホーム] > 「列の分割」 > 「位置」

    を選択します。 ※「X0001」「X0002」と必ず2番目以降が 品目CDになるので、今回は位置区切りを選択 1 2
  6. ③テーブルの結合 【やること】 1. 売上実績の「品目CD」と単価マスタの「品目CD」をキーに結合する 2. 単価マスタの「品名」「販売単価」を表示する ここでやることの確認 07 26 売上実績日

    品目CD 数量 2020/10/2 0002 39 2020/10/4 0007 29 2020/12/22 0004 44 結合 売上原本 品目CD 品名 単価 0002 しろねこクッキー 210 0007 ハーブティー 飲み比べセット 300 0004 鴨南蛮そばセット 350 単価マスタ 売上実績日 品目CD 数量 品名 単価 2020/10/2 0002 39 しろねこクッキー 210 2020/10/4 0007 29 ハーブティー 飲み比べセット 300 2020/12/22 0004 44 鴨南蛮そばセット 350
  7. ④列・行の追加・並替・絞込 ここでやることの確認 08 32 売上実績日 品目CD 数量 品名 単価 売上金額

    2020/10/2 0002 39 しろねこクッキー 210 =[数量]*[単価] 2020/10/4 0007 29 ハーブティー 飲み比べセット 300 =[数量]*[単価] 2020/12/22 0004 44 鴨南蛮そばセット 350 =[数量]*[単価] 売上実績日 品名 数量 売上金額 2020/10/2 しろねこクッキー 39 =[数量]*[単価] 2020/10/4 ハーブティー 飲み比べセット 29 =[数量]*[単価] 2020/12/22 鴨南蛮そばセット 44 =[数量]*[単価] 売上実績日 品名 数量 売上金額 2020/12/22 鴨南蛮そばセット 44 =[数量]*[単価] 【やること】 1. 「売上金額」カラムを追加 2. 不要な列を削除する 3. 列の順番を入れ替える 4. 売上実績日に日付フィルタをかける
  8. ④列・行の追加・並替・絞込 33 売上金額カラムの追加 08 1 2 3 4 1. [列の追加]

    > 「カスタム列」を選択します 2. 追加する列名を入力します 3. 計算式を入力します • 記載方法はExcelの式同様 • 項目は[項目名]の形式にする • 「使用できる列」をクリックすると、 項目が挿入される 4. OKを選択します
  9. ⑤データの出力 【やること】 1. PowerQueryエディタ上で編集したデータを、Excelシートに出力する。 39 ここでやることの確認 09 出力データ PowerQuery エディタ

    データ出力 売上実績日 品名 数量 売上金額 2020/12/22 鴨南蛮そばセット 44 =[数量]*[単価] ①売上実績.xlsx
  10. ステップに名前・説明をつける • ステップには任意の名前を設定可能 • 将来の自分・チームメンバーに共有することを 見据え、わかりやすい名前に変更すると◎ 43 あとで見返した際にわかりやすいように 10 任意の名前に変更!

    申し送り事項を記入 ステップに名前をつける ステップに説明をつける • プロパティ画面で説明を記入可能 • ステップ名設定も可能 • PowerQuery自体が手順書になる
  11. テーブルの結合方式を理解する 44 SQLの結合方式と同じ 11 結合の種類 説明 左外部 「左」のすべての行に対し、「右」にあるキーが同じ行を結合 右外部 「右」のすべての行に対し、「左」にあるキーが同じ行を結合

    完全外部結合 「左」と「右」のすべての行に対し、キーが同じ行を結合 内部 キーが合致する行のみを結合 左反 「左」のすべての行に対し、「右」の同じキーがない行を結合 右反 「右」のすべての行に対し、「左」の同じキーがない行を結合 左(最初) 右(2番目)
  12. クエリを再利用する(参照・複製) 46 作り込んだクエリの派生を作成する(複製) / 作り込んだクエリをデータソースとして使用する(参照) 13 参照 複製 複製 or

    参照 • 新規クエリを作成 • もとのステップをそのまま引き継ぐ • 新規クエリを作成 • もとのステップは「ソース」として一つ にまとまる 【対象のクエリ】
  13. クエリを再利用する(カスタム関数/パラメータ/M言語) 47 「データソースは違うけれど、編集手順は同じ」という場合 14 取込ファイル:売上原本(8月).csv 列の分割 2 テーブルの結合 3 列・行の追加・並替・絞込

    4 データの出力 5 パラメータ:FilePath カスタム関数 FilePathで指定したファイルに 加工を実行した結果を返す • PowerQueryでは一度作成した操作をカスタム関数としてまとめることができます。 • カスタム関数には任意でパラメータを指定することができ、関数呼び出し時に値がセットされます。
  14. グループ化で合計を求める 49 SQLでいう「GROUP BY」 15 集計単位 集計関数 元データ 商品別売上 --

    SQLでいうと以下のような形 SELECT u.itm_cd 商品コード ,SUM(u.amount) 商品別売上 FROM uriage u GROUP BY u.itm_cd ;
  15. 参考になる書籍・ソース 51 もっとPowerQueryを知りたいと思ったら… 17 データ分析を視野にいれるなら Excelパワーピボット 7つのステップでデータ 集計・分析を「自動化」する本 公式ドキュメントも 日本ではPowerQueryの書籍

    がまだ少ないので、困ったら一次 情報がおすすめです。 とりあえずこれ PowerQueryを使って加工した データで、ダッシュボードを作る ところまで学べます。 Excel Power Query入門 PowerQueryの基本的な操作 を学ぶことができます。 Kindle unlimitedで読めます。 https://docs.microsoft.com/ja-jp/ power-query/
  16. 【Webサイト】 • Power Query のドキュメント | Microsoft Docs • https://docs.microsoft.com/ja-jp/power-query/

    • Power Query M 数式言語のリファレンス PowerQuery M | Microsoft Docs • https://docs.microsoft.com/ja-jp/powerquery-m/ • Akira Takao’s blog • Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本の著者ブログ • https://modernexcel7.hatenablog.com/ 【書籍】 • Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本 | 鷹尾 祥 • Excel Power Query入門 | シマモリ マスオ • Power Query基本操作ブック | サトウヨシヒロ • わかりやすい!現場で使えるパワークエリ: マクロより全然簡単!専門分野でなくても業務で必ず役に立つ! エクセル整備 | データセイバー • 中級・上級者向け ExcelPowerQueryでセルをパラメータとして使いたい Excel2019 | 越山紘都 54 参考文献
  17. 品目CD 品名 販売単価 0001 黒ねこクッキー 310 0002 しろねこクッキー 210 0003

    野性爆弾 300 0004 ハーブティー飲み比べセット 300 0005 ブラジルコーヒー豆100g 100 0006 手作り豆腐キット 220 0007 鴨南蛮そばセット 350 0008 極み!だしパック 450 0009 うまい!千葉の米 1340 0010 かに食べる用のスプーン 800 56 演習用データ:単価マスタ.csv
  18. 売上実績日,品目CD,売上数量 2020/10/2,X0002,39 2020/10/4,X0007,29 2020/10/22,X0004,44 2020/10/27,X0003,2 2020/10/30,X0001,46 2020/10/1,X0006,26 2020/11/26,X0001,22 2020/11/13,X0008,6 2020/11/13,X0007,6

    2020/11/28,X0004,40 2020/11/24,X0008,27 2020/11/14,X0003,13 2020/11/24,X0007,46 2020/11/6,X0007,32 2020/11/1,X0002,13 2020/12/8,X0009,41 2020/12/6,X0005,15 2020/12/26,X0001,2 2020/12/5,X0002,28 2020/12/9,X0004,39 2020/12/11,X0002,38 2020/12/15,X0004,29 2020/12/25,X0005,28 2020/12/11,X0006,35 2021/1/28,X0004,38 2021/1/18,X0001,7 2021/1/8,X0001,16 2021/1/17,X0008,36 2021/1/27,X0002,43 2021/1/7,X0010,10 2021/1/17,X0001,20 2021/1/4,X0004,48 2021/1/5,X0003,4 2021/1/11,X0002,21 57 演習用データ:売上原本.csv