PowerQuery入門

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

Transcript

1. PowerQuery入門 楽々データ操作！ @ikuma-t
2. はじめに～勉強会のゴール～ 2 「昨日遅くまで変換してたデータ、どういうロジックで変換したか忘れた… 」 「VLOOKUPがネストしすぎて、期待通りの結果が出ない、つらい」 「計算式埋め込みすぎて、Excelフリーズした…」 Excelでデータをこねくり回しているうちに、こんな風になってしまった経験は無いでしょうか。 この勉強会では、以下の2点をゴールとして進めていきます。 • Exceｌの機能「PowerQuery」の基本的な使い方を学び、これらの悩みを解消すること • 「PowerQuery」にどんな機能があるのかを知り、必要に応じて調べられること
3. 目次 3 PowerQueryとは 1 最速でPowerQuerｙを使う 2 もっと！PowerQuery 3 まとめ 4
4. 4 PowerQueryとは 01
5. PowerQueryの概要 PowerQueryとは… データの 「加工」に特化したExcelの機能 5 PowerQuery is 何？ 01
6. PowerQueryは「データの加工に特化したExcelの機能」 6 PowerQueryは加工でよく使う機能を「自動」or「簡単」に実行可能にする 02 加工でよく使う機能を「自動」or「簡単」に実行可能にする 1 加工プロセスを「ステップ」として記録する ２
7. 7 # Excel手加工 #PowerQuery 例えば、CSVの取り込み 自分で区切り位置を指定 PowerQueryが勝手に推測
8. 8 # Excel手加工 #PowerQuery 例えば、他テーブルの参照 VLOOKUP（式）を考えて書く 専用画面でかんたんに設定
9. PowerQueryは「データの加工に特化したExcelの機能」 9 加工プロセスを「ステップ」として記録する 02 加工でよく使う機能を「自動」or「簡単」に実行可能にする 1 加工プロセスを「ステップ」として記録する ２
10. PowerQueryは「データの加工に特化したExcelの機能」 10 加工プロセスを「ステップ」として記録する 02 元データ受領 1 データをExcelに貼り付ける 2 カンマで列に区切る 3 不要な列・行を削除する 4 導出項目の計算式を入力する 5 見た目を整える 6 CSV、Excel、txt…etc 「クエリ」として読込 PowerQueryエディタ 加工プロセスをステップとして自動的に記録 カンマで分割 不要行・列削除 カンマで分割 カンマで分割 不要行・列削除 計算式追加 加工プロセスは残らない 完成 必要に応じて クエリを出力 PowerQuery Excel手加工
11. PowerQueryを利用するメリット 11 VLOOKUPではだめですか？ 03 プロセスの変更・転用が容易 データ加工プロセスはステップ として記録され、いつでも変更 転用が可能です。 VBAや式よりも速度が速い M言語で構成されたプログラム により、軽快かつ高速にデータ 加工を行うことができます。 計算式を覚えるよりも直感的 に理解することができるため、 データ加工に集中できます。 操作が簡単 Easy!
12. 02 最速でPowerQueryを使う 12
13. ①単価マスタ.csv （タブ区切り） ②売上原本.csv （カンマ区切り） ①売上実績.xlsx 出力データ 入力データ • 売上原本にある品目CDは、 接頭辞に「X」が付与されている。 • 売上原本には売上数量のみが記録さ れている。 • 販売単価はマスタの値に準ずる。 • 売上原本には数カ月分の売上が記録 されている • 必要な項目は • 売上実績日 • 品名 ※単価マスタから取得 • 売上数量 • 売上金額 ※導出項目 • 売上実績が2020/12のデータのみ を抽出する。 最速でPowerQueryを使う この章では、PowerQueryを今すぐ使いたい方向けに最小限の操作を解説します。 操作の流れと使用するデータは以下のとおりです。 13 必要最小限を実戦形式で学ぼう 04 データの取込 1 列の分割 2 テーブルの結合 3 列・行の追加・並替・絞込 4 データの出力 5
14. 品目CD 1 2 3 ①データの取込 【やること】 1. 元データをPowerQueryエディタに取り込む（型や区切り文字が自動判定される） 2. 数値で取り込まれた「品目CD」を、文字列に変更する 14 ここでやることの確認 05 入力データ ①単価マスタ.csv （タブ区切り） ②売上原本.csv （カンマ区切り） PowerQuery エディタ データ取込 品目CD 0001 0002 0003 型変換
15. ①データの取込 PowerQueryは ・[データ] > 「データの取得」 から利用することができます。 取り込みファイルに応じて、 必要な形式を選択します。 15 PowerQueryエディタにデータを取り込む 05 1 2 3
16. ①データの取込 16 PowerQueryエディタにデータを取り込む 05 1 ファイルを選択すると、 データの区切り文字、型が 自動で検出されます。 [データの変換]を実行すると、 選択したファイルが PowerQueryに読み込まれます。
17. ①データの取込 型を変更する列を右クリックし、 「型の変更」から変更先の型を選択します。 ダイアログが出てくるので、 「現在のものを置換」を選択します。 17 PowerQueryエディタにデータを取り込む 05 1 2 3 4
18. ①データの取込 PoweｒQueryエディタ上で新規にデータを取り込むには 「ホーム」 > 「新しいソース」 > 取り込みファイルの形式 を選択します。 18 PowerQueryエディタにデータを取り込む 05
19. ①データの取込 取り込み内容を確認し、 「OK」を押します。 19 PowerQueryエディタにデータを取り込む 05 1
20. ①データの取込 20 ここまでの編集結果を確認しましょう 05 単価マスタ 売上原本
21. 品目CD X0001 X0002 X0003 ②列の分割 【やること】 1. 売上実績の「品目CD」をXと数値に分割する（数値カラムは自動で型が推測される） 2. 数値で取り込まれた「品目CD」を、文字列に変更する ここでやることの確認 06 21 列を分割 品目CD.2 1 2 3 型変換 品目CD 0001 0002 0003
22. ②列の分割 22 「X0001」→「0001」に分割する 06 分割対象の列をクリックした状態で [ホーム] > 「列の分割」 > 「位置」 を選択します。 ※「X0001」「X0002」と必ず2番目以降が 品目CDになるので、今回は位置区切りを選択 1 2
23. ②列の分割 23 「X0001」→「0001」に分割する 06 1 2 文字列を分割する位置を入力し、 OKを押します。 ※「X0001」「X0002」と必ず2番目以降が 品目CDになるので、今回は「1」を入力
24. ②列の分割 24 数値「1」を文字列「0001」として解釈させる 06 1 2 3 4 型を変更する列を右クリックし、 「型の変更」から変更先の型を選択します。 ダイアログが出てくるので、 「現在のものを置換」を選択します。
25. ②列の分割 25 ここまでの編集結果を確認しましょう 07 売上原本
26. ③テーブルの結合 【やること】 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
27. ③テーブルの結合 27 テーブルを結合する 07 テーブルを結合するために 「ホーム」 > 「クエリのマージ」を選択します。 1
28. ③テーブルの結合 28 テーブルを結合する 07 1. 結合する列を選択します 2. 結合対象のテーブルを選択します 3. 結合対象のテーブルから、結合する列を選択します 4. 結合の種類を確認します 5. OKを選択します 1 2 3 4 5
29. ③テーブルの結合 29 結合されたデータを展開する 07 結合直後は結合対象のテーブルのすべての情報が追加されているため、 必要な列のみを抽出します。 単価マスタのすべての情報が 「Table」として追加されている 1
30. ③テーブルの結合 30 結合されたデータを展開する 07 1. 項目を表示するには「展開」を選択します 2. 表示する項目を選択します 3. 「単価マスタ.〇〇」と表示する場合は、 チェックをオンにします。 4. OKを選択します 1 2 3 4
31. ③テーブルの結合 31 ここまでの編集結果を確認しましょう 07 売上原本
32. ④列・行の追加・並替・絞込 ここでやることの確認 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. 売上実績日に日付フィルタをかける
33. ④列・行の追加・並替・絞込 33 売上金額カラムの追加 08 1 2 3 4 1. [列の追加] > 「カスタム列」を選択します 2. 追加する列名を入力します 3. 計算式を入力します • 記載方法はExcelの式同様 • 項目は[項目名]の形式にする • 「使用できる列」をクリックすると、 項目が挿入される 4. OKを選択します
34. ④列・行の追加・並替・絞込 34 不要な列を削除する 08 削除したいカラムを右クリックし、「削除」を選択します 1
35. ④列・行の追加・並替・絞込 35 列を移動する 08 位置を変更したいカラムを選択し、移動したい場所までドラッグします 1
36. ④列・行の追加・並替・絞込 36 売上実績日でフィルタをかける 08 1. 対象の列のフィルターを開きます 2. 「日付フィルター」 > 「指定の値の間」 を選択します。 1 2
37. ④列・行の追加・並替・絞込 37 売上実績日でフィルタをかける 08 1 2 3 1. フィルターの開始日を入力します ※日付はカレンダーから選択することも可能 2. フィルターの終了日を入力します 3. OKを選択します
38. ④列・行の追加・並替・絞込 38 ここまでの編集結果を確認しましょう １3 売上原本
39. ⑤データの出力 【やること】 1. PowerQueryエディタ上で編集したデータを、Excelシートに出力する。 39 ここでやることの確認 09 出力データ PowerQuery エディタ データ出力 売上実績日 品名 数量 売上金額 2020/12/22 鴨南蛮そばセット 44 =[数量]*[単価] ①売上実績.xlsx
40. ⑤データの出力 1. [ホーム] > 「閉じて読み込む」 > 「閉じて次に読み込む」を選択する 2. 出力先を適宜選択し、「OK」を選択する ※「接続の作成のみ」とすると、Excelに出力されず、PowerQuery上に保存される。 40 加工したデータをExcelに出力する ０９ 1
41. ⑤データの出力 41 ここまでの編集結果を確認しましょう 09 売上実績
42. 03 もっと！PowerQuery 42
43. ステップに名前・説明をつける • ステップには任意の名前を設定可能 • 将来の自分・チームメンバーに共有することを 見据え、わかりやすい名前に変更すると◎ 43 あとで見返した際にわかりやすいように １0 任意の名前に変更！ 申し送り事項を記入 ステップに名前をつける ステップに説明をつける • プロパティ画面で説明を記入可能 • ステップ名設定も可能 • PowerQuery自体が手順書になる
44. テーブルの結合方式を理解する 44 SQLの結合方式と同じ 11 結合の種類 説明 左外部 「左」のすべての行に対し、「右」にあるキーが同じ行を結合 右外部 「右」のすべての行に対し、「左」にあるキーが同じ行を結合 完全外部結合 「左」と「右」のすべての行に対し、キーが同じ行を結合 内部 キーが合致する行のみを結合 左反 「左」のすべての行に対し、「右」の同じキーがない行を結合 右反 「右」のすべての行に対し、「左」の同じキーがない行を結合 左（最初） 右（2番目）
45. クエリにデータを追加する（クエリの追加） 45 データが複数ファイルにまたがっている際に便利 12 売上原本①（2020/10～2021/01） 売上原本②（2021/02～2021/07） 定義が同じデータを 1つにまとめたい場合 売上原本（2020/10～2021/07）
46. クエリを再利用する（参照・複製） 46 作り込んだクエリの派生を作成する（複製） / 作り込んだクエリをデータソースとして使用する（参照） 13 参照 複製 複製 or 参照 • 新規クエリを作成 • もとのステップをそのまま引き継ぐ • 新規クエリを作成 • もとのステップは「ソース」として一つ にまとまる 【対象のクエリ】
47. クエリを再利用する（カスタム関数/パラメータ/M言語） 47 「データソースは違うけれど、編集手順は同じ」という場合 14 取込ファイル：売上原本（8月）.csv 列の分割 2 テーブルの結合 3 列・行の追加・並替・絞込 4 データの出力 5 パラメータ：FilePath カスタム関数 FilePathで指定したファイルに 加工を実行した結果を返す • PowerQueryでは一度作成した操作をカスタム関数としてまとめることができます。 • カスタム関数には任意でパラメータを指定することができ、関数呼び出し時に値がセットされます。
48. クエリを再利用する（カスタム関数/パラメータ/M言語） 48 「データソースは違うけれど、編集手順は同じ」という場合 14 • カスタム関数を作成するには、PowerQueryを構成するM言語について一部理解する必要があります。 • 詳細についてはhttps://docs.microsoft.com/ja-jp/powerquery-m/を参照してください。 • パラメータ参照の際に、初期設定のセキュリティではエラーになることがあります。以下を確認してください。 • [ファイル] > 「オプションと設定」 → 「クエリのオプション →「プライバシー」
49. グループ化で合計を求める 49 SQLでいう「GROUP BY」 15 集計単位 集計関数 元データ 商品別売上 -- SQLでいうと以下のような形 SELECT u.itm_cd 商品コード ,SUM(u.amount) 商品別売上 FROM uriage u GROUP BY u.itm_cd ;
50. データ量を削減する 50 データとしては出力せず、PowerQuery上だけで保持する 16 「参照用にマスタは必要だけれど、マスタそのものは出力する必要ない」という場合、 PowerQuery上にのみデータを残すと、ファイル容量を削減できます。 PowerQueryエディタへ の接続に絞る PowerQuery上のみ Excelにも反映
51. 参考になる書籍・ソース 51 もっとPowerQueryを知りたいと思ったら… 17 データ分析を視野にいれるなら Excelパワーピボット 7つのステップでデータ 集計・分析を「自動化」する本 公式ドキュメントも 日本ではPowerQueryの書籍 がまだ少ないので、困ったら一次 情報がおすすめです。 とりあえずこれ PowerQueryを使って加工した データで、ダッシュボードを作る ところまで学べます。 Excel Power Query入門 PowerQueryの基本的な操作 を学ぶことができます。 Kindle unlimitedで読めます。 https://docs.microsoft.com/ja-jp/ power-query/
52. 04 まとめ 52
53. まとめ 53 PowerQueryとは… データの 「加工」に特化したExcelの機能 加工でよく使う機能を「自動」or「簡単」に実行可能にする 1 加工プロセスを「ステップ」として記録する ２
54. 【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 参考文献
55. Ex. 演習用データ 55
56. 品目CD 品名 販売単価 0001 黒ねこクッキー 310 0002 しろねこクッキー 210 0003 野性爆弾 300 0004 ハーブティー飲み比べセット 300 0005 ブラジルコーヒー豆100g 100 0006 手作り豆腐キット 220 0007 鴨南蛮そばセット 350 0008 極み！だしパック 450 0009 うまい！千葉の米 1340 0010 かに食べる用のスプーン 800 56 演習用データ：単価マスタ.csv
57. 売上実績日,品目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