タグ別アーカイブ: すべての行

【M言語に慣れる】_14回目_複数行を1つのセルにまとめる

【M言語は難しくない】今回は複数行に拡散している値を、次のGIF画像のように「記号」をつなぎ目にして、1つのセルにまとめます

目次

今回のポイント

今回使用するデータ

グループ化の実施

カスタム列の追加

<まとめ>

今回のポイント

今回のポイントは2つあります

1つ目は過去記事で紹介した「すべての行」によるグループ化です

この「すべての行」によるグループ化により、グループ別にテーブルを作成します

2つ目はリストの「値の抽出 / 1つのセルにリスト化」機能の活用です

こちらは、実際の例で見てみましょう!

空のクエリで次の画像のように、2つのリストを作成したとします

上の画像のリストを一旦、テーブル化した後、黄色に印を付けた「展開マーク」をクリックをすると「値を抽出する」が選択できます

上のGIF画像のように「値を抽出する」を選択した後は、リストの「区切り記号」を指定できます

「区切り記号」を指定した後は、下の画像のように1つのセルにリストの値が出力されます

今回使用するデータ

今回使用するデータは、次の画像のデータです

名前の列をグループ化し、グループ毎に「商品列に含まれる商品」を1つのセルに出力します

グループ化の実施

今回の解説は、使用データをエディタで開くところから始めます

上の画像にて、黄色の印を付けた「グループ化」をクリックします

なお、事前に「注文日」の列は削除しておきます

グループ化の内容は次の画像のように指定します

・グループ化項目:名前の列をグループ化します

・新しい列名:詳細

・操作:前述のように「すべての行」を指定します

・列:空欄のままでOKです

上記のように指定してOKボタンを押すと、エディタ画面は次のようになります

グループ化された名前毎に、テーブルが作成されています

次はグループ毎に、上の画像の商品列の内容をリスト化します

カスタム列の追加

前述のグループ毎にリスト化するには、カスタム列・作成画面を開くところから始めます

カスタム列・作成画面で、次の画面のように詳細列を指定すると、過去の記事の通りグループ毎にテーブルが作成されます

次の画像の「詳細.1」列が、上の画像から出力されたカスタム列の内容です

今回はテーブルではなく、各テーブル内の商品リストを出力します

リストを出力するには、カスタム列・作成画面にて次の画像のようにリストになる列を加えます

これで、商品リストが各テーブル毎に出力されます

各リストにカーソルをあてると「リストの中味」が次の画像のように見れます

こちらを前述の「今回のポイント」にて紹介したように、「展開マーク」をクリックし、「値を抽出する」から1つのセルにリストを出力します

値を抽出する」をクリックした後は次の画像のように、値を区切る記号を指定する画面が出てきますので、そちらで「記号」を指定してください

記号を指定したら、次の画像のようにグループ毎に1つのセルにリストが出力されています

<まとめ>

今回は複数の行の内容を、1つのセルに出力する方法を解説しました

 ポイントは2つあり、まず1つ目はグループ化において「すべての行」を指定することです

 この「すべての行」を指定すると、グループ毎にテーブルが作成されます

 グループ毎にテーブルを作成したら、「カスタム列・作成画面」にて各テーブルからリストを作成しておきます

 2つ目のポイントは、各リストの内容を「値を抽出する」機能にて1つのセルにリストを出力することです

今回紹介した方法は、それほど使用頻度は高くないと思います

 ただ、今回の内容は、M言語の重要概念であるテーブルとリストを体感するのにいい内容だったと思います

 特にグループ化において、「すべての行」を指定してグループ毎にテーブルを作成するパターンは色々と応用できそうです!

ぜひ、実際に手を動かしてみて試してみてください

記事を最後まで見て頂き誠にありがとうございました

参考までに今回使用したファイルを添付します

次回は、M関数のText関数について2つ事例を紹介します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

【分析作業用】グループ毎の平均と個の平均の差を自動集計する方法

 Power Queryでは過去の記事で紹介したように、簡単にグループ化して数字を集計することができます。但し、グループ化した際には、元データの1部の列は非表示になります。

 実は、グループ化には「すべての行」というオプションがあり、こちらのオプションを選択すると「グループ化した数字」と「グループ化していない数字」を並列表示できます

 今回は更に、並列に表示した数字間で差(「グループ毎の平均」-「個の平均」)を集計します

この差とはつまり数字のバラツキです

バラツキを自動抽出することで、数字の分析に役立てるようにします

では今回、解説に使用する元データとアウトプットする内容について解説します

次の画像の画像は今回使用するデータです

こちらのデータからクエリを作成し、エクセルシートに次のように出力します

①グループ平均

グループ化により、部門ごとの平均給料を集計します

②すべての行

①の部門ごとの数字と並行して、元のデータ(個)を表示します

③差

②の個の数字から①のグループ平均の数字の差を集計します

 

上記の①~③で今回のアウトプットの内容について解説しました

では、①~③の順に詳細な解説を行っていきます


①グループ平均

こちらは過去の記事で既に解説した内容になります

①では部門毎に、給料の平均額を計算します

つまり、グループ化する項目は「部門」、集計する項目は「給料」になります

上の画像のように、元のデータをテーブル化して、Power Querエディタ(以降、エディタ)を開いた後、ホームタブから「グループ化」をクリックします

「グループ化」をクリックした後に、上の画像の画面が開きますので、こちらで4つの項目を指定します

・グループ化する項目➡部門

・新しい列名➡給料・部門平均

・操作➡平均

・列(集計する列)⇒給料

上記の4つの項目を指定して、OKボタンを押すと次の画像の画面のようにグループ毎の平均値が集計されます

②すべての行

①で行ったステップに修正を加えて、元のデータの個々の数字を表示します

まずは、①で行ったステップの右横のマークをクリックします

上のGIF画像内で開いた「グループ化」画面で再度、グループ化条件を設定します

まずは、下の画像の画面上の「詳細設定」を指定します

上の画像のように「詳細設定」を指定すると、下の画像の下にある黄色の箇所のように「集計の追加」を行えるようになります

こちらの「集計の追加」をクリックすると新しい列名が指定できるようになります

次に、追加された新しい列の「操作」にて、下の画像のように「すべての行」を指定します

ちなみに、下の図の右にある、本来は集計する列を指定する箇所は「ブランク」のままでいいです

では、新しい列名を上の画像のように指定したところで、OKボタンをクリックします

すると、上のGIF画像のように新たな列が追加されます

次に、上の画像の右上にある「黄色の箇所」のマークをクリックします

すると、上の画像の画面が開きますので、こちらで黄色の箇所を設定します

・氏名➡チェック

・給料➡チェック

・元の列名をプレフィックスとして使用します➡チェックを外す

上の3つを指定したら、画面右下のOKボタンをクリックします

すると、上のグループGIF画像のように「氏名」と「給料」の列が右横に展開します

これで、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりました

③差

上の②で 、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりましたので、互いの差を計算します

計算式は下のようになります

ⅰ)個々の元データ <マイナス> ⅱ)グループ化した項目

実は上の式がとても重要です

差の計算は下の画像の画面で行うのですが、計算対象となる列指定の順番が重要です

計算式の左からⅰ)⇒ⅱ)の順番で列を指定します

列の指定はCtrlキーで行います

仮に、ⅱ)からⅰ)の順番で列指定をすると「ⅱ)⁻ⅰ)」の計算式で差が計算されます

が集計できたところで、エクセルシートに読込むのですが、その前に2つ処理を行います

まず、2つの列の「列名」を下の図のように修正します

次に、グループの平均を集計した列の数字を丸めておきます

では、2つの処理を行ったので、エクセルシートに次の画面から「読込先」を指定して読み込み処理を行います

次のGIF画像が実際に「読込処理」を行った時の画像です

<まとめ>

 今回は、過去に解説したグループ化を更に踏み込んで、個々の元データも並列で表示する方法を解説しました

 個々の元データを表示するには、グループ化を設定する画面で「すべての行」を指定します

画像に alt 属性が指定されていません。ファイル名: image-261.png

 更に、個々の元データを表示した後は、グループの平均と個々の元データの差を自動集計しました

画像に alt 属性が指定されていません。ファイル名: Difference.gif

個々の平均との差分を集計するだけでも、数字全体の特徴は掴みやすくなります

 仮に、Power Queryを使用しないで集計しようとすれば、ピボットテーブルやエクセル関数を組み合わせて処理を行わねばなりません

 このブログでは今後、Power Queryを分析作業に直接役に立つような手法も発信していきますので、よろしくお願いします!

長文を最後まで読んでくださり、誠にありがとうございました

参考までに今回使用したデータを添付します

アイコン

差の集計 23.13 KB 16 downloads

...
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村