タグ別アーカイブ: グループ化
【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関数の入力
関数の参照
次に「列の追加」タブから「インデックス列」を挿入してみます
すると、確かに連番は作成されますが、グループ毎の連番ではありません
こちらのステップについては後で削除するとして、まずは過去の記事のように関数の解説を参照してみましょう
関数の後の丸括弧を全て削除した後にエンターキーを押し、解説を表示します
テーブルに新しい名前の列を追加するという内容から解説が始まっていますが、関数の作成の仕方については、次のような内容が書いてあります
=Table.AddIndexColumn(テーブル名,”新列名”,連番の開始番号,連番の増分)
このM関数では、引数を4つ指定します
・テーブル名
・新列名
・連番の開始番号：通常は1から指定します
・連番の増分：通常は１つずつ連番を増やします
この数式の内容に従い、カスタム列作成画面から「連番」を作成し直します
なお、既に追加した連番については削除しておきましょう
カスタム列作成
それでは、「列の追加」タブから「カスタム列」をクリックします
次に開いた画面では、M関数にてカスタム列を作成していきます
使用するM関数は、前述の次の関数です
=Table.AddIndexColumn(テーブル名,新列名,連番の開始番号,連番の増分)
こちらは、入力補完機能を活用して入力していきます
まずは、「INDEX」と入力してみましょう
すると上のGIF画像のように「Table.AddIndexColumn」が選択できるようになります
↓で２つ下にカーソルを移動し、TABキーで該当のM関数を選択します
次に、前述のようにテーブルを引数の1つ目に指定します
こちらは、列の挿入で代用します
上のGIF画像のように「部門別」の列は３つのテーブルから構成されています
ですので、下の画像の「部門別」を挿入すれば、自動的に３つのテーブルを引数として設定できます
下のGIF画像のように「部門別」列を挿入したら、残りの3つの引数もそれぞれ指定します
残りの３つの引数は、次の内容となります
・新列名 ➡ 連番
・連番の開始番号 ➡ 1
・連番の増分 ➡ 1
下の画像のように、上で紹介した３つの引数を設定したら、OKボタンを押します
すると、新しく追加した「連番」の列名の列に更に３つテーブルが作成されます
この後は、下の画像の赤い丸印をつけた箇所から展開する処理になります
その前に、他の列は削除しておきます
他の列を削除したら展開処理します
上のGIF画像のように、次に開いた画面で「元の列名をプレフィックスとして使用します」のチェックは外しておきましょう！
すると上の画像のように展開されます
きちんとグループ毎に連番も作成されています
グループ毎に適切に連番が作成されていることを確認したら、読み込み処理を行います
＜まとめ＞
今回はグループ毎に連番を作成する方法について解説しました
作成にあたってはポイントが2点ありました
1つ目は、グループ化の際に「操作」の指定を「すべての行」にする点です
こちらの処理により、グループ毎にテーブルが新規に作成されます
2つ目は、既存のメニューにより「連番」を作成するのではなく、カスタム列・作成画面からM関数にて「連番」を作成する点です
カスタム列からM関数を作成することにより、1つ目のポイントで作成した「グループ毎のテーブル」を引数にして関数を設定できます
今回の方法はエディタ内の１つの列に、複数のテーブルが作成される点が少しトリッキーです
但し、今回の処理はデータ分析やデータ整理などで使える場面が多いはずです
グループ毎に連番を作成した後、クエリを「複製」していけば、連番別に更に階層化してデータ整理が行えたりできます（例：連番１のレコードのみを集める等）
今回解説した方法の活用事例等についても、今後はこのブログで発信していきたいと思います！
長文に最後までお付き合い頂きありがとうございました
参考までに今回使用したファイルを添付します
次回は、前行を参照する方法を解説します
【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ボタンを押せば、以下のようにグループ化が行われています
＜まとめ＞
今回は、エディター画面でグループ毎に合計金額を集計する方法について解説しました
記事の冒頭でグループ化は「スマホのように直感的に行える」と述べました
グループ化設定する項目の位置については、集計する対象の列と混同しやすいので、この点だけは注意が必要です
まず最初に設定するのが、グループ化する列です
そして、一番最後に設定するのが「集計する対象の列」となります
今回の解説は以上です
次回は、グループ化・機能を更に有効活用するためにピボットテーブルと連携します
複数条件でグループ化～中級編12回目～
前回はPower Queryのグループ化機能により、グループ毎に合計金額を集計しました
今回は前回の内容を活かしながら、グループ化の切り口を複数指定できるようにしましょう！
実質的には、ピボットテーブルでグループを複数階層化するのと同じことが行えるようになります
では、ポイントの解説から始めましょう！
●今回のポイント
A.グループ化指定
今回はグループ化の出力指定を、基本ではなく「詳細設定」にて指定します
指定方法を詳細設定に指定することで、グループ化切り口を追加できるようになります
後の指定方法については、前回の基本設定と同じです
B.ステップ修正
中級編8回目で解説をしましたが、Power Queryでは「やり直し操作」はステップ管理画面にて行います
なお今回は、前回の集計結果がエクセルシートに読込まれた状態からグループ化処理を行います
ですので、クエリー編集⇒エディター画面を開く⇒ステップ管理⇔ステップ修正の流れになります
さて、
ポイントを2点確認したところで、詳細な解説を始めましょう！
下のファイルは前回の集計結果をシートに読込んだ状態になっていますので、ぜひこちらをご活用ください
クエリー編集
クエリーを右クリックし、編集をクリックします
もし、クエリ―画面が表示されていない場合には、以下の画面を操作してください
ステップ変更
開いたエディター画面にて、前回グループ化を行ったステップを変更します
するとグループ化画面を修正できるようになります
詳細設定およびグループ化の追加
グループ化画面にて、基本ではなく「詳細設定」を選択しましょう！
そして、「グループ化の追加」ボタンを押すと、グループ化の切り口を２つ選択できるようになります
グループ化項目の設定
今回は、ⅰ）年度、ⅱ)製品別、にて設定します
なお、新しい列名は元データの「売上金額」にしましょう
前述の２つの設定が終了したら、OKボタンを押しましょう！
読込処理
ホームタブに戻り、「閉じて読み込む」をクリックしましょう！
集計結果がシートに読込まれます
集計結果は「年度」「製品別」、そして「売上金額」の順にて並んでいます
なおクエリを見て見ると、１．クエリー編集前、クエリーの行数が3行だったのが、6行に増えているのも確認できます！
＜まとめ＞
今回は、グループ化を複数の切り口で行う方法を解説しました
グループ化設定画面にて詳細設定を選択すれば、後は画面に従って追加処理を行って行くだけで複数のグループ化切り口を設定できました
後、今回は前回の集計結果を「クエリーの編集」⇒「ステップ変更」の手順で設定を行いました
実際の業務では、前回集計した結果を修正する場面は沢山でてくると思いますので、より実践的なPower Query活用術を学べたと思います！
では次回から上級編を開始します
ぜひ、IF式の組み合わせにもチャレンジしてみてください
Excellentな仕事術～重なった図形を自由自在に操る～
図形を重ねて仕事をすることって多いと思いますが、図形が多すぎて埋もれてしまった時に困った事が起こります。
図が選択できなくなってしまうのです。
例えば、上の図でBが埋もれているのが分かりますか？
このBの図を左に持っていきたいのですが、皆さんはこんな時にどうされていますか？
一度、全てを背面にずらしますか？
それとも一度、上にあるものを外によけますか？
どっちにしろ、いずれの方法も相当時間がかかるはずです
何故なら、途中で違うものを選択してずらしてしまったりしてしまうからです
でも、次に紹介する方法なら、一発でBの図を選択できます
ホームタブから”検索と選択”を選びます
そして”オブジェクトの選択と表示”をクリックすると
シートにある図の一覧が表示されるのです
実はこれだけで選び放題なのです
選択に表示されている”テキスト”を選択することは、図を選択する効果を持つのです
他にもこんな事ができます
図を表示したり、非表示にしたりできます
そして、次が一番おすすめですが、Ctrlキーを押しながら複数の図を選択することで
図のグループ化も簡単に行えます
図を大量に重ねている時に１センチでも全てを下にずらしたい時など、もの凄い労力を使いますよね？そんな時にこの機能なら一発でそんな苦労を解消できます
そして、最後に、もっと丁寧に図形を扱いたい、という人は
図を挿入する毎に、図に名前をつけましょう！
これで選択作業がもっと楽になります
動画で”動き”を確認したい方は以下をご覧ください
https://youtu.be/bK0mTi6nOxk