Power Queryでは過去の記事で紹介したように、簡単にグループ化して数字を集計することができます。但し、グループ化した際には、元データの1部の列は非表示になります。
実は、グループ化には「すべての行」というオプションがあり、こちらのオプションを選択すると「グループ化した数字」と「グループ化していない数字」を並列に表示できます
今回は更に、並列に表示した数字間で差（「グループ毎の平均」－「個の平均」）を集計します
この差とはつまり数字のバラツキです
バラツキを自動抽出することで、数字の分析に役立てるようにします
では今回、解説に使用する元データとアウトプットする内容について解説します
次の画像の画像は今回使用するデータです
こちらのデータからクエリを作成し、エクセルシートに次のように出力します
①グループ平均
グループ化により、部門ごとの平均給料を集計します
②すべての行
①の部門ごとの数字と並行して、元のデータ（個）を表示します
③差
②の個の数字から①のグループ平均の数字の差を集計します
上記の①~③で今回のアウトプットの内容について解説しました
では、①～③の順に詳細な解説を行っていきます
①グループ平均
こちらは過去の記事で既に解説した内容になります
①では部門毎に、給料の平均額を計算します
つまり、グループ化する項目は「部門」、集計する項目は「給料」になります
上の画像のように、元のデータをテーブル化して、Power Querエディタ（以降、エディタ）を開いた後、ホームタブから「グループ化」をクリックします
「グループ化」をクリックした後に、上の画像の画面が開きますので、こちらで４つの項目を指定します
・グループ化する項目➡部門
・新しい列名➡給料・部門平均
・操作➡平均
・列（集計する列）⇒給料
上記の４つの項目を指定して、OKボタンを押すと次の画像の画面のようにグループ毎の平均値が集計されます
②すべての行
①で行ったステップに修正を加えて、元のデータの個々の数字を表示します
まずは、①で行ったステップの右横のマークをクリックします
上のGIF画像内で開いた「グループ化」画面で再度、グループ化条件を設定します
まずは、下の画像の画面上の「詳細設定」を指定します
上の画像のように「詳細設定」を指定すると、下の画像の下にある黄色の箇所のように「集計の追加」を行えるようになります
こちらの「集計の追加」をクリックすると新しい列名が指定できるようになります
次に、追加された新しい列の「操作」にて、下の画像のように「すべての行」を指定します
ちなみに、下の図の右にある、本来は集計する列を指定する箇所は「ブランク」のままでいいです
では、新しい列名を上の画像のように指定したところで、OKボタンをクリックします
すると、上のGIF画像のように新たな列が追加されます
次に、上の画像の右上にある「黄色の箇所」のマークをクリックします
すると、上の画像の画面が開きますので、こちらで黄色の箇所を設定します
・氏名➡チェック
・給料➡チェック
・元の列名をプレフィックスとして使用します➡チェックを外す
上の３つを指定したら、画面右下のOKボタンをクリックします
すると、上のグループGIF画像のように「氏名」と「給料」の列が右横に展開します
これで、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりました
③差
上の②で 、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりましたので、互いの差を計算します
計算式は下のようになります
ⅰ）個々の元データ ＜マイナス＞ ⅱ）グループ化した項目
実は上の式がとても重要です
差の計算は下の画像の画面で行うのですが、計算対象となる列指定の順番が重要です
計算式の左からⅰ）⇒ⅱ）の順番で列を指定します
列の指定はCtrlキーで行います
仮に、ⅱ）からⅰ）の順番で列指定をすると「ⅱ）⁻ⅰ）」の計算式で差が計算されます
差が集計できたところで、エクセルシートに読込むのですが、その前に２つ処理を行います
まず、２つの列の「列名」を下の図のように修正します
次に、グループの平均を集計した列の数字を丸めておきます
では、２つの処理を行ったので、エクセルシートに次の画面から「読込先」を指定して読み込み処理を行います
次のGIF画像が実際に「読込処理」を行った時の画像です
＜まとめ＞
今回は、過去に解説したグループ化を更に踏み込んで、個々の元データも並列で表示する方法を解説しました
個々の元データを表示するには、グループ化を設定する画面で「すべての行」を指定します
更に、個々の元データを表示した後は、グループの平均と個々の元データの差を自動集計しました
個々の平均との差分を集計するだけでも、数字全体の特徴は掴みやすくなります
仮に、Power Queryを使用しないで集計しようとすれば、ピボットテーブルやエクセル関数を組み合わせて処理を行わねばなりません
このブログでは今後、Power Queryを分析作業に直接役に立つような手法も発信していきますので、よろしくお願いします！
長文を最後まで読んでくださり、誠にありがとうございました
参考までに今回使用したデータを添付します
コメントを残す