タグ別アーカイブ: グループ化
複数グループ内で値違いの箇所を見つける～グループ化の応用～
先日ある方から大量の商品データの中から、価格違いの設定が起こっているところを見つけたいという依頼がありました
またルールがあり、同じ素材/同グループであれば販売する色種類が違っていても同じ価格で設定しなければいけないということでした
もちろん、目でみて判断することもできますが、大量データがある場合にはとても困難です
こういった場合はPower Queryのグループ化機能で簡単に価格違いが起こっているグループを見つけることができます
今回は次のデータを使って解説を行いたいと思います
ある商品を価格設定するときに、地区内では同じ価格設定にしなければならないものとします
こちらのデータから価格違いが起こっている地区を見つけます
目次
ポイント
Power Queryのグループ化機能では、グループ化の方法として「最小」「最大」がありますので、こちらを活用します
グループ化の適用
解説はPower Queryエディターからはじめさせて頂きます
ちなみに、上の画像では元のエクセルデータにフィルを適用していますので空欄が埋まっています
上記の画像の状態からまずグループ化をクリックします
デフォルトでは「基本」設定になっていますが、今回は「詳細設定」を指定します
こうすることで、「集計の追加」ボタンにより、グループ化の集計方法を「最小」に加えて「最大」も指定することができます
グループ化を実際に適用すると下の画像のような状態になります
グループ内にもし価格違いがあると、上の画像の黄色の箇所のように「最小:1400＜＞最大:1500」となります
条件列の作成
ここからは条件列を作成して、最小と最大が違うときは「X」そうでなければ「〇」という表示をする列を作成します
条件列は次のように設定します
価格違いがあるグループと明細リストの抽出
上の条件列を作成すると下の画像のような状態になっています
もちろん、「×」の箇所だけフィルターをすれば、グループ内で価格違いが起こっている箇所は分かります
ただ、下の画像のように店舗と価格もリストにしたいとします
目で元のデータをフィルターする方法もありますが、M関数を使い、自動的にフィルターする方法もあります
まずは下の画像の状態で、適用したステップを「myList」とするところから解説を始めたいと思います
後でこちらの「myList」を参照します
次に
関数マーク/fx*をクリックしたステップを追加した後に、元の「フィルした状態/下方向へコピー済みステップ」を参照します
＊参照ステップの記事を参照
これで、グループ化を行う前の状態に戻りました
ここから「地区」列がmyListに含まれるかどうかを判定する列を「カスタム列」作成画面で作成します
List.Contains関数を使うことで、地区の列の値が「myList」ステップの地区列に含まれるかどうかを判定できます
ここから「TRUE」のみをフィルターすればリストは完成です
＜まとめ＞
今回は、グループ化機能の最小と最大をうまく組み合わせて、グループ内の違う価格設定を発見する方法を紹介しました
ピボットテーブルでも同じことができます
ピボットテーブルの場合には分散や標準偏差を出す方法もあります
グループ内で価格にバラツキがある場合には、分散や標準偏差が０以外になります
分布図の作成及びグループ化～データを見やすく分解する
ピボットテーブルのグループ化機能を使うと、数字が見やすくなるため重宝しておりますが、Power BIでも同じようなことが行えます
今回は、Power BIにおけるグループ化機能を量/ヒストグラムと質に分けて解説します
分布の把握は数字分析の基本なので、分析を行う必要のある方には特に有用な情報だと思います
量によるグループ化
今回は下の画像のデータからヒストグラムを作成したいと思います
売上金額の範囲区分を一定の金額で設定し、レコード数（行数）747件の件数を範囲区分別に縦棒グラフで表示します
まず、レポート画面のフィールド欄で「売上金額」上で右クリックします
すると「新しいグループ」という表示が見えますので、こちらをクリックします
「新しいグループ」をクリックした後は、次の画像の画面が開きます
ピンのタイプはデフォルトで「ピンのサイズ」になっていますが、こちらは「ピンの数」に変更します
ここで「ピンの数」とは縦棒の数になります
OKボタンを押すと、新たなフィールドができます
こちらをX軸に配置し、Y軸に売上金額のカウントを配置します
すると、縦棒グラフがヒストグラムとなります
ヒストグラムのデータ区分の範囲はピンのサイズとなります
ちなみにY軸を合計に変えるとデータ範囲別に合計金額を表示することができます
質によるグループ化
こちらのグループ化はレポート管理画面の一つ下のデータ管理画面で行います
下の画像がグループ化を行うデータです
まず、グループ化の対象になる「都道府県」の列を選択します
すると上のタブが「列ツール」に切り替わり、「データグループ」が表示されます
ここで「データグループ」の▼マークをクリックすると次の画面が表示されます
「新しいデータグループ」をクリックすると表示が次のように切り替わります
ここでグループ化するものは、Ctrlキーを押しながら選択し、グループ化をクリックします
この選択、グループ化の処理によりグループ化が実施されます
仮に、この状態のまま画面右下のOKボタンを次のような列ができます
ですので、列名およびグループ名はOKボタンを押す前に修正しておきます
＜まとめ＞
今回は量と質の双方の観点からグループ化を行う方法を解説しました
Power BIではグループ化により、分析可視化だけでなく、分析作業も行えます
ヒストグラムについては、X軸の範囲が良く見えない点について不便に感じる方もいらっしゃると思います
その場合には、「その他のビジュアルの取得」から「histogramのアプリ」を探す方法もあります
ヒストグラム/histogramは複数種類がありますので、ぜひ試してみてください
上の画像の「Histogram Chart」ならばX軸も下の画像のように明確に表示されます
【M言語実践】顧客の2回目のリピート率を分析する方法
パワークエリはとても便利ですが、M言語となると使い道がよく分からないと思います
今回は、M言語を使用した実践的な分析手法を紹介したいと思います
私は以前、EC通販会社に勤めていました
EC通販では顧客の顔が見えないので、データからどういう顧客がいて・どういう行動をしているのか・を分析する必要があります
様々な分析を行いましたが、指標として一番重視していたのが「顧客の2回目リピート率」です
顧客の2回目のリピート率が何故重要なのか？
これには様々な理由がありますが、一番の理由は2回目のリピート率を少しでも改善すれば、売上が長期的に増加するからです
読者の方でも外食をした際に「あの店には2度と行かない・・・」という経験をした方も多いと思います
逆に同じ店で2度食事をして、その店に慣れてくると3回目、4回目とリピートする意欲する気が高くなると思います
ビジネスでも「2回目のリピート」というハードルをクリアすることの意義はとても大きいのです
ただし、普通にエクセルで2回目のリピート率を算出しようとするとかなり面倒です
私は一時期、IF関数で注文回数を付与して、2回目の注文だけシートを分けたりしていました
ましてや、2回目のリピートの有無別に顧客分析をしようとするとかなりハードルが高いです
ところが、M言語を使えば2回目のリピート率は意外と簡単に算出できます！
ポイント
今回使用するデータは会員別の注文データです
上の画像では2回目の注文があった会員に黄色の印を付けましたが、2回目の注文があった会員もいれば、そうでない会員もいます
ここからまず、会員番号毎に注文回数を付与します
そして、上の画像の表から次のような表を作成します
上の画像では、その前の画像の表から注文回数を「1回」に絞り込んであります
結果として、会員番号の列は会員番号が重複なく並んでいます
そして「2回目判定用」なる列が追加されています
こちらは注文回数の差分です
こちらの差分は注文回数を「1回」に絞り込んだ場合には０か１になります
この列が１の会員は2回目の注文があった会員です
つまり、「2回目判定用」の列の合計を「注文回数」の列の合計で割れば2回目のリピート率が出るという仕掛けになります
（注）尚、上記の画像のデータ以前に注文履歴は無いという前提で解説を行わせて頂きます。ですので、本当は初回の注文ではないのでは？という疑問は持つ必要はありません
注文回数の付与
ポイントで解説した通り、まずは会員番号別に注文回数を付与します
こちらについての詳細については、過去の記事をご確認をお願いします
大きく分けて２つ行うことがあります
1つ目は、グループ化です
会員ID別（会員番号別）に「すべての行」でグループ化を行い、会員ID別にテーブルを作成します
次にカスタム列・作成画面にて、テーブル別にM関数/Table.AddIndexColumnを使用して連番を付与します
すると、注文回数が連番で付与されます
注文回数の差分の算出
こちらについても詳細は過去の記事をご参照願います
この差分を算出するには、パワークエリでは本来は困難な「セル単位」や「行単位」の処理に踏み込む必要があります
ポイントとなるのは、こちらも「連番」です
上の画像のように連番をつけると、連番と画面左の行番号とが対応するようになります
ちなみに、M言語は０ベースなので、実際にM言語で使用する1行目は０になります
ここから波括弧：{}を使用します
波括弧は行番号を表します
例えば、注文回数{0}とした場合には、注文回数列の1行目のデータなります
この波括弧と連番をうまく組み合わせることで、注文回数の差分を算出します
上の画像にて黄色の印を付けた波括弧内は、注文回数の各行の１つ下の行番号の値になります（[注文回数]の前のDataは前ステップの名称になります）
つまり上の画像内では、注文回数列をA列だとすると＜＝A3-A2＞と同様の計算が行われています
ちなみに、try~otherwiseとすることでエラーを回避しています
注文回数の絞り込み
上記まで行ったところで、エディタを確認すると以下の画像のような状態になっています
ここから注文回数の列にフィルターをかけます
こうすれば注文回数の列は「１」のみ、2回目判定用の列は０か１になります
ここまでくれば、後は各列を合計して2回目のリピート率を算出するだけです
合計にはM関数/List.Sum関数を使用します
上の画像では、２つの合計値を/で割り算をすることでリピート率を算出しています
％の表示にしたい場合には、List.Sum関数でリスト化した内容をテーブル化した上で％に型式を変えます
＜まとめ＞
今回はM言語の仕組みを使用して、2回目のリピート率を算出しました
2回目のリピート率を算出するのに、主に２つのM言語の技術を活用しました
１つ目はグループ化を通じて各グループ毎に連番を付与する仕組みです
2つ目は注文回数の差分を連番と波括弧を組み合わせて算出する仕組みです
この２つの技術を通じて、注文回数と2回目の注文有無を０か１で表現できるようにしました
今回は2回目のリピート率を算出しましたが、本当に大事なのは2回目のリピートがあった会員とそうでない会員との違いは何かを調べることです
今回作成したクエリを複製して途中のステップを削除すれば、会員番号別に注文の有無が表示できます
他のデータを会員別に紐づければ、2回目の注文があった会員とそうでない会員の購入している商品の違いなども調べることができるはずです
更に注文回数の差分を抽出する仕組みを応用して、初回から2回目までの日数なども調べることができます
M言語が本当に有効なのはこういった複雑な分析が必要な場面だと思います
特にグループ化から連番を付与する仕組みと差分を算出する仕組みは、分析作業にて使う場面も多いと思いますので、しっかり活用できるようにしておきましょう！
マスタデータの履歴管理を行う方法
パワークエリの「クエリのマージ機能」はとても便利で、エクセル関数のVLOOKUP関数より使いやすいです
「クエリのマージ機能」を有効活用すれば、参照表（マスタデータ）の活用もかなり手軽に行えます
今回の記事では、「クエリのマージ機能」に加えて「グループ化機能」も活用して、マスタデータを更に有効に活用できるようにする方法を解説します！
マスタデータの履歴管理
マスタデータには顧客マスタや商品マスタなど様々なものがあります
種類は様々でも、絶対的なルールが一つあります
これは、マスタデータには重複があってはならないということです
もし、マスタデータのIDが「１，２，３・・・」と採番されていたとしたら、IDの１が２つあってはならないということです
ところが、
マスタデータの変更履歴を残す必要がある場合があります
例えば、以下の画像のケースです
上の画像は顧客マスタです
ID：1番の中尾さんのマスタデータが重複しています
何故かというと、IDが1番の中尾さんが名古屋市から東京に引っ越ししているからです
ただし、データ管理上は前の住所も残しておく必要があります
この場合は、日付が最新（最大）のもののみマスタデータとして表示できるように工夫する必要があります
では次から、日付が最新（最大）のもののみをマスタデータとして表示する為のポイントを２つ紹介します
ポイント
グループ化
パワークエリにはグループ化という機能があり、重複を排除してグループ化しつつ合計処理などの操作を行えます
今回はグループ化機能の操作を「最大」で指定して、IDの重複がある場合には「最大の日付」のものを抽出できるようにします
複数キーによるマージ
エクセル関数のVLOOKUP関数では、検索値は一つのみ指定できます
パワークエリのマージ機能では、実は、複数列を照合列として指定できます
手順
マスタデータからクエリ作成
解説は、前述のマスタデータをテーブル化してエディタを開くところからははじめさせて頂きます
エディタを開いたら、下の画像の日付の列が時刻表示になってしまっています
こちらは、日付形式に直しておいてください
日付形式に直したら、クエリ名を「顧客マスタ_元」としてください
グループ化による最大日付の抽出
まずは「顧客マスタ_元」クエリを「複製」してクエリ名を「顧客マスタ_グループ化」に変更してください
この後、ポイントで前述したように下の画像のような設定でグループ化を行ってください
グループ化を行うと、前述のID：1番の方の日付が最新の日付になっているはずです
クエリのマージ処理
では、次にクエリのマージを指定しますが、下の画像の「新規としてクエリをマージ」を指定して、新規にクエリを作成できるようにします
マージ対象のクエリは下の画像のように「顧客マスタ_元」を指定します
ポイントの章で前述したように、複数の列を照合列として指定します
今回のケースの場合は、IDだけでなく「日付」も指定することで、IDに重複がある場合には、日付も一致したマスタデータのみをマージできるようにします
下の画像のように照合列として複数列を指定する場合には、Ctrlキーを押しながら指定します
マージする条件を指定した後は、マージされたクエリの中かから「ID列と日付」以外を展開します
マージされた列を展開した後は、列の順番等を整えてください
＜まとめ＞
今回は、パワークエリのグループ化機能とマージ機能をうまく組み合わせてマスタデータの履歴管理を行う方法を解説しました
今回の最大のポイントはグループ化機能です
グループ化を行う際に、操作を「最大」にして日付が最大のもののみを抽出できるようにします
今回解説した内容で一点、注意点があります
マージする際に、グループ化した内容は1番目にくるようにしてください
元の顧客マスタは、上の画像にて黄色の印をつけた箇所のように、2番目に来るようにしてください
この順番が狂うと、IDが重複したままになってしまいますので注意が必要です
【分析】RFM分析の「F」を集計する
RFM分析は顧客を３つの指標で分類して、顧客別に施策を講じる手法です
Recency いつ？、Frequency 頻度？、Monetary いくら？
今回は、上のFrequencyを顧客別に集計する方法を解説させて頂きます
このFrequencyとは、データ内に出現する「顧客ID別の出現回数」のことです
まず何故、このFrequency/頻度を分析するかという話しをしたいと思います
例として同期間内の注文頻度が10回で注文総額が10万円の顧客グループと、注文頻度が１回で注文総額が10万円の顧客グループを比較して考えて見ましょう
平均注文金額は前者が1万円であり、後者は10万円となります
両グループとも、同期間内の注文総額は一緒です
ですが注文単価が違うことから、注文に含まれる商品の単価も注文の仕方も違う可能性が高いです
加えて、後者は期間限定の「値引き商品」をまとめて購入している可能性も高いです
つまり「頻度」を抽出することにより、顧客の注文行動における特性を炙り出せるのです
今回の使用データと行いたいこと
今回の解説で使用するデータは、次の画像の注文データです
注文データは、注文日が2021年1月から3月までの期間で集計されています
このデータから顧客ID別に、注文頻度を抽出します
例えば、上の画像にある顧客ID「C00564」の顧客ならば2回と抽出できるようにします
頻度の集計
解説は元データをPower Queryエディタで開くところからはじめます
こちらの集計処理は、過去の記事でも紹介したグループ化により一瞬で終了します
まずは「ホーム」タブの「グループ化」をクリックします
するとグループ化・画面が開くので、次の画像のように各項目を設定します
グループ化項目：顧客IDの列
新しい列名：頻度
操作：行数のカウント
この上記の設定により、顧客ID別にIDの登場頻度が集計されます
＜まとめ＞
今回はRecency いつ？、Frequency 頻度？、Monetary いくら？の内、Frequencyを集計しました
グループ化の機能を使えば、簡単に頻度も集計できます
グループ化機能は分析を行う上では欠かせない機能です
実際に手を動かして実践的に活用できるようになりましょう
M言語に慣れる_10回目～グループ毎に連番作成～
【M言語は難しくない】今回はデータをグループ化した際に、１から始まる連番をグループ毎に作成する方法について解説します。この処理の仕方を覚えると、エクセルの使い方の幅が広がります
コードの採番や顧客の2回目のリピート状況把握など、使いみちは沢山あります！
しかも知られざるグループ化機能を使えば、１つのM関数を入力するだけでできてしまいます
まさに魔法です
ぜひ、実際に手を動かしてこの魔法を体験してください！
目次
今回のポイント
今回のポイントは２つあります
グループ化
グループ化する際に、よく選択される操作は「合計」などです
今回は「すべての行」という操作を選択します
この「すべての行」を選択して操作を行うと、グループ毎にテーブルが作成されます
M関数作成
連番は「列の追加」タブの「インデックス列」のメニューから、クリック操作で簡単に作成できます
今回は、既存のメニューは使用せず、カスタム列の作成画面から「Table.AddIndexColumn」というM関数を使用して「連番」を作成します
M関数で作成することで、ポイントの1点目で作成されたグループ毎のテーブルを、関数の引数として指定できるようになります
今回の使用データ
今回は下の画像にあるデータの「部門」列をグループ化します
グループ化した後は、グループ毎に連番を振ります
以下が実際に使用するサンプルデータです
グループ化の実施
解説は、前述のデータをテーブル化し、エディタを開いたところから行います
エディタを開いたらまずは、グループ化を行います
グループ化は「ホームタブ」の「グループ化」メニューから行います
次に開いた画面では、前述のように操作を「全ての行」で設定します
・グループ化対象列 ➡ 「部門」列
・新しい列名 ➡ 「部門列」
・操作 ➡ 「すべての行」
・列 ➡ 空欄のままでOKです
上の内容で指定してOKボタンをクリックすると、エディタ内では次の画像のように、テーブルがグループ別に作成されています
【M言語に慣れる】_14回目_複数行を１つのセルにまとめる
【M言語は難しくない】今回は複数行に拡散している値を、次のGIF画像のように「記号」をつなぎ目にして、１つのセルにまとめます
目次
今回のポイント
今回のポイントは２つあります
1つ目は過去記事で紹介した「すべての行」によるグループ化です
この「すべての行」によるグループ化により、グループ別にテーブルを作成します
2つ目はリストの「値の抽出 / １つのセルにリスト化」機能の活用です
こちらは、実際の例で見てみましょう！
空のクエリで次の画像のように、２つのリストを作成したとします
上の画像のリストを一旦、テーブル化した後、黄色に印を付けた「展開マーク」をクリックをすると「値を抽出する」が選択できます
上のGIF画像のように「値を抽出する」を選択した後は、リストの「区切り記号」を指定できます
「区切り記号」を指定した後は、下の画像のように１つのセルにリストの値が出力されます
今回使用するデータ
今回使用するデータは、次の画像のデータです
名前の列をグループ化し、グループ毎に「商品列に含まれる商品」を１つのセルに出力します
グループ化の実施
今回の解説は、使用データをエディタで開くところから始めます
上の画像にて、黄色の印を付けた「グループ化」をクリックします
なお、事前に「注文日」の列は削除しておきます
グループ化の内容は次の画像のように指定します
・グループ化項目：名前の列をグループ化します
・新しい列名：詳細
・操作：前述のように「すべての行」を指定します
・列：空欄のままでOKです
上記のように指定してOKボタンを押すと、エディタ画面は次のようになります
グループ化された名前毎に、テーブルが作成されています
次はグループ毎に、上の画像の商品列の内容をリスト化します
カスタム列の追加
前述のグループ毎にリスト化するには、カスタム列・作成画面を開くところから始めます
カスタム列・作成画面で、次の画面のように詳細列を指定すると、過去の記事の通りグループ毎にテーブルが作成されます
次の画像の「詳細.1」列が、上の画像から出力されたカスタム列の内容です
今回はテーブルではなく、各テーブル内の商品リストを出力します
リストを出力するには、カスタム列・作成画面にて次の画像のようにリストになる列を加えます
これで、商品リストが各テーブル毎に出力されます
各リストにカーソルをあてると「リストの中味」が次の画像のように見れます
こちらを前述の「今回のポイント」にて紹介したように、「展開マーク」をクリックし、「値を抽出する」から１つのセルにリストを出力します
「値を抽出する」をクリックした後は次の画像のように、値を区切る記号を指定する画面が出てきますので、そちらで「記号」を指定してください
記号を指定したら、次の画像のようにグループ毎に1つのセルにリストが出力されています
＜まとめ＞
今回は複数の行の内容を、１つのセルに出力する方法を解説しました
ポイントは２つあり、まず1つ目はグループ化において「すべての行」を指定することです
この「すべての行」を指定すると、グループ毎にテーブルが作成されます
グループ毎にテーブルを作成したら、「カスタム列・作成画面」にて各テーブルからリストを作成しておきます
2つ目のポイントは、各リストの内容を「値を抽出する」機能にて１つのセルにリストを出力することです
今回紹介した方法は、それほど使用頻度は高くないと思います
ただ、今回の内容は、M言語の重要概念であるテーブルとリストを体感するのにいい内容だったと思います
特にグループ化において、「すべての行」を指定してグループ毎にテーブルを作成するパターンは色々と応用できそうです！
ぜひ、実際に手を動かしてみて試してみてください
記事を最後まで見て頂き誠にありがとうございました
参考までに今回使用したファイルを添付します
次回は、M関数のText関数について２つ事例を紹介します
【分析作業用】グループ毎の平均と個の平均の差を自動集計する方法
Power Queryでは過去の記事で紹介したように、簡単にグループ化して数字を集計することができます。但し、グループ化した際には、元データの1部の列は非表示になります。
実は、グループ化には「すべての行」というオプションがあり、こちらのオプションを選択すると「グループ化した数字」と「グループ化していない数字」を並列に表示できます
今回は更に、並列に表示した数字間で差（「グループ毎の平均」－「個の平均」）を集計します
この差とはつまり数字のバラツキです
バラツキを自動抽出することで、数字の分析に役立てるようにします
では今回、解説に使用する元データとアウトプットする内容について解説します
次の画像の画像は今回使用するデータです
こちらのデータからクエリを作成し、エクセルシートに次のように出力します
①グループ平均
グループ化により、部門ごとの平均給料を集計します
②すべての行
①の部門ごとの数字と並行して、元のデータ（個）を表示します
③差
②の個の数字から①のグループ平均の数字の差を集計します
上記の①~③で今回のアウトプットの内容について解説しました
では、①～③の順に詳細な解説を行っていきます
①グループ平均
こちらは過去の記事で既に解説した内容になります
①では部門毎に、給料の平均額を計算します
つまり、グループ化する項目は「部門」、集計する項目は「給料」になります
上の画像のように、元のデータをテーブル化して、Power Querエディタ（以降、エディタ）を開いた後、ホームタブから「グループ化」をクリックします
「グループ化」をクリックした後に、上の画像の画面が開きますので、こちらで４つの項目を指定します
・グループ化する項目➡部門
・新しい列名➡給料・部門平均
・操作➡平均
・列（集計する列）⇒給料
上記の４つの項目を指定して、OKボタンを押すと次の画像の画面のようにグループ毎の平均値が集計されます
②すべての行
①で行ったステップに修正を加えて、元のデータの個々の数字を表示します
まずは、①で行ったステップの右横のマークをクリックします
上のGIF画像内で開いた「グループ化」画面で再度、グループ化条件を設定します
まずは、下の画像の画面上の「詳細設定」を指定します
上の画像のように「詳細設定」を指定すると、下の画像の下にある黄色の箇所のように「集計の追加」を行えるようになります
こちらの「集計の追加」をクリックすると新しい列名が指定できるようになります
次に、追加された新しい列の「操作」にて、下の画像のように「すべての行」を指定します
ちなみに、下の図の右にある、本来は集計する列を指定する箇所は「ブランク」のままでいいです
では、新しい列名を上の画像のように指定したところで、OKボタンをクリックします
すると、上のGIF画像のように新たな列が追加されます
次に、上の画像の右上にある「黄色の箇所」のマークをクリックします
すると、上の画像の画面が開きますので、こちらで黄色の箇所を設定します
・氏名➡チェック
・給料➡チェック
・元の列名をプレフィックスとして使用します➡チェックを外す
上の３つを指定したら、画面右下のOKボタンをクリックします
すると、上のグループGIF画像のように「氏名」と「給料」の列が右横に展開します
これで、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりました
③差
上の②で 、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりましたので、互いの差を計算します
計算式は下のようになります
ⅰ）個々の元データ ＜マイナス＞ ⅱ）グループ化した項目
実は上の式がとても重要です
差の計算は下の画像の画面で行うのですが、計算対象となる列指定の順番が重要です
計算式の左からⅰ）⇒ⅱ）の順番で列を指定します
列の指定はCtrlキーで行います
仮に、ⅱ）からⅰ）の順番で列指定をすると「ⅱ）⁻ⅰ）」の計算式で差が計算されます
差が集計できたところで、エクセルシートに読込むのですが、その前に２つ処理を行います
まず、２つの列の「列名」を下の図のように修正します
次に、グループの平均を集計した列の数字を丸めておきます
では、２つの処理を行ったので、エクセルシートに次の画面から「読込先」を指定して読み込み処理を行います
次のGIF画像が実際に「読込処理」を行った時の画像です
＜まとめ＞
今回は、過去に解説したグループ化を更に踏み込んで、個々の元データも並列で表示する方法を解説しました
個々の元データを表示するには、グループ化を設定する画面で「すべての行」を指定します
更に、個々の元データを表示した後は、グループの平均と個々の元データの差を自動集計しました
個々の平均との差分を集計するだけでも、数字全体の特徴は掴みやすくなります
仮に、Power Queryを使用しないで集計しようとすれば、ピボットテーブルやエクセル関数を組み合わせて処理を行わねばなりません
このブログでは今後、Power Queryを分析作業に直接役に立つような手法も発信していきますので、よろしくお願いします！
長文を最後まで読んでくださり、誠にありがとうございました
参考までに今回使用したデータを添付します
グループ化1～中級編11回目～
こんにちは、Excellent仕事術のガッツ鶴岡です
前回は、Power Queryのエディター画面（Power Query Editor）にて条件列とインデックス列を作成しました
今回は前回の「列の追加タブ」ではなく、「HOME」タブにてデータのグループ化に取り組みます
ここで言うグループ化とは、単にデータをグループに分けるだけでなく、データをある切り口でグループ化した上で、グループ毎に合計金額などを集計することです
このグループ化の処理が行えると、関数の入力もピボットテーブルの利用もないので、数字の集計処理が格段に早くなります
グループ化はスマホのように直感的に行えるようになっていますので、ぜひサンプルファイルを使用して実際に試してみてください
今回、サンプルに使用するデータに5列あり、列の構成はⅰ）年度別、ⅱ）製品別、且つ、ⅲ）Region、ⅳ）地域、a:売上金額となっています
このデータをグループ化して、ⅱ）製品別のa:売上金額を集計します
１．エディターを開く
①「テーブルまたは範囲から」をクリック
データ上にカーソルを置き、、「テーブルまたは範囲から」をクリックします
②エディターが開く
今回は、既にテーブル化してあるので、①の操作でそのままエディターが開きます
２．グループ化
グループ化の処理は、前述したようにエディター内のHOMEタブで行います
①グループ化をクリック
②グループ化設定 ⇒ OKボタン
今回は、前述のようにグループ化するのは「製品別列」、合計するのは「売上金額列」になります
グループ化画面にて最初に設定するのは、グループ化する列です
グループ化する対象列は▼マークをクリックすると選択できるようになります
後は、画面の表示に従って設定しています
グループ化する列を設定したら、画面下の3項目を設定します
「新しい列名」については、今回は「製品別・売上金額」としました
「操作」では、合計や平均などの集計方法を選択できます
今回は「合計」を集計方法として選択します
最後は集計対象の列、「売上金額」を指定します
指定が終わり、OKボタンを押せば、以下のようにグループ化が行われています
＜まとめ＞
今回は、エディター画面でグループ毎に合計金額を集計する方法について解説しました
記事の冒頭でグループ化は「スマホのように直感的に行える」と述べました
グループ化設定する項目の位置については、集計する対象の列と混同しやすいので、この点だけは注意が必要です
まず最初に設定するのが、グループ化する列です
そして、一番最後に設定するのが「集計する対象の列」となります
今回の解説は以上です
次回は、グループ化・機能を更に有効活用するためにピボットテーブルと連携します