【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つ目のポイントで作成した「グループ毎のテーブル」を引数にして関数を設定できます
今回の方法はエディタ内の１つの列に、複数のテーブルが作成される点が少しトリッキーです
但し、今回の処理はデータ分析やデータ整理などで使える場面が多いはずです
グループ毎に連番を作成した後、クエリを「複製」していけば、連番別に更に階層化してデータ整理が行えたりできます（例：連番１のレコードのみを集める等）
今回解説した方法の活用事例等についても、今後はこのブログで発信していきたいと思います！
長文に最後までお付き合い頂きありがとうございました
参考までに今回使用したファイルを添付します
次回は、前行を参照する方法を解説します
はじめまして。医薬のビッグデータをハンドリングするためにパワーピボットに着目し、色々探していてこちらにいきあたりました。Ｍ言語というかなりレアなトピックを平易に深堀り頂き、大変参考になります！！！ありがとうございます。最近だったらこういう情報はnoteでだして有料化、なトレンドありますがこちらでお願いします(笑)
コメントありがとうございます！役に立てているようで光栄です。今後はイベントやサークルなどの活動も行って行きたいと思いますのでそちらもよろしくお願いします
はじめまして。今年の春から社内SEに従事している者です。開発内容共有化の点でローコードでの業務改善に着目しており、Excellent仕事術様の内容は非常に参考になります。いつもありがとうございます。
カスタム列で入力補完機能を使用されていますが、私のエクセルでは機能しません。HomeandBusiness 2019ですが、バージョンの問題でしょうか？大変お手数ですが、アドバイス頂けますと幸いです。
返信が遅くなり、申し訳ありません。バージョンにより入力補完機能は使用できません。2019バージョンは入力補完機能が使用できないとの認識です。また何かありましたらご連絡ください
udemyのクーポンを頂き、
Section 4の9.でグループ毎に連番を付与する
というこのページと同様の内容のものも視聴しました。
グループ内で連番を金額の降順で付与するにはどうしたらよいのか教えて頂けますでしょうか。
グループ化前に部門、金額を降順ソートし、このステップをグループ化してもソートは無視されます。