【M言語は難しくない】今回はデータをグループ化した際に、1から始まる連番をグループ毎に作成する方法について解説します。この処理の仕方を覚えると、エクセルの使い方の幅が広がります
コードの採番や顧客の2回目のリピート状況把握など、使いみちは沢山あります!
しかも知られざるグループ化機能を使えば、1つのM関数を入力するだけでできてしまいます
まさに魔法です
ぜひ、実際に手を動かしてこの魔法を体験してください!
目次
今回のポイント
今回のポイントは2つあります
グループ化
グループ化する際に、よく選択される操作は「合計」などです
今回は「すべての行」という操作を選択します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/グループ化全ての行.jpg)
この「すべての行」を選択して操作を行うと、グループ毎にテーブルが作成されます
M関数作成
連番は「列の追加」タブの「インデックス列」のメニューから、クリック操作で簡単に作成できます
今回は、既存のメニューは使用せず、カスタム列の作成画面から「Table.AddIndexColumn」というM関数を使用して「連番」を作成します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/M関数-2.jpg)
M関数で作成することで、ポイントの1点目で作成されたグループ毎のテーブルを、関数の引数として指定できるようになります
今回の使用データ
今回は下の画像にあるデータの「部門」列をグループ化します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/使用データ-2.jpg)
グループ化した後は、グループ毎に連番を振ります
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/賀三やり直し.png)
以下が実際に使用するサンプルデータです
グループ化の実施
解説は、前述のデータをテーブル化し、エディタを開いたところから行います
エディタを開いたらまずは、グループ化を行います
グループ化は「ホームタブ」の「グループ化」メニューから行います
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/グループ化メニュー.jpg)
次に開いた画面では、前述のように操作を「全ての行」で設定します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/グループ化詳細-1-644x358.jpg)
・グループ化対象列 ➡ 「部門」列
・新しい列名 ➡ 「部門列」
・操作 ➡ 「すべての行」
・列 ➡ 空欄のままでOKです
上の内容で指定してOKボタンをクリックすると、エディタ内では次の画像のように、テーブルがグループ別に作成されています
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/3つのテーブル.gif)
M関数の入力
関数の参照
次に「列の追加」タブから「インデックス列」を挿入してみます
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/インデックス列.jpg)
すると、確かに連番は作成されますが、グループ毎の連番ではありません
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/インデックス列作成.jpg)
こちらのステップについては後で削除するとして、まずは過去の記事のように関数の解説を参照してみましょう
関数の後の丸括弧を全て削除した後にエンターキーを押し、解説を表示します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/M関数解説.jpg)
テーブルに新しい名前の列を追加するという内容から解説が始まっていますが、関数の作成の仕方については、次のような内容が書いてあります
=Table.AddIndexColumn(テーブル名,”新列名”,連番の開始番号,連番の増分)
このM関数では、引数を4つ指定します
・テーブル名
・新列名
・連番の開始番号:通常は1から指定します
・連番の増分:通常は1つずつ連番を増やします
この数式の内容に従い、カスタム列作成画面から「連番」を作成し直します
なお、既に追加した連番については削除しておきましょう
カスタム列作成
それでは、「列の追加」タブから「カスタム列」をクリックします
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/カスタム列作成-1.jpg)
次に開いた画面では、M関数にてカスタム列を作成していきます
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/カスタム列作成画面.jpg)
使用するM関数は、前述の次の関数です
=Table.AddIndexColumn(テーブル名,新列名,連番の開始番号,連番の増分)
こちらは、入力補完機能を活用して入力していきます
まずは、「INDEX」と入力してみましょう
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/カスタム列作成入力補完.gif)
すると上のGIF画像のように「Table.AddIndexColumn」が選択できるようになります
↓で2つ下にカーソルを移動し、TABキーで該当のM関数を選択します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/2M関数カスタム列.jpg)
次に、前述のようにテーブルを引数の1つ目に指定します
こちらは、列の挿入で代用します
![画像に alt 属性が指定されていません。ファイル名: 3つのテーブル.gif](https://analytic-vba.com/wp/wp-content/uploads/2021/03/3%E3%81%A4%E3%81%AE%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB.gif)
上のGIF画像のように「部門別」の列は3つのテーブルから構成されています
ですので、下の画像の「部門別」を挿入すれば、自動的に3つのテーブルを引数として設定できます
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/部門別挿入.jpg)
下のGIF画像のように「部門別」列を挿入したら、残りの3つの引数もそれぞれ指定します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/GIF部門別挿入.gif)
残りの3つの引数は、次の内容となります
・新列名 ➡ 連番
・連番の開始番号 ➡ 1
・連番の増分 ➡ 1
下の画像のように、上で紹介した3つの引数を設定したら、OKボタンを押します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/引数残り2つ.jpg)
すると、新しく追加した「連番」の列名の列に更に3つテーブルが作成されます
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/新連番列.jpg)
この後は、下の画像の赤い丸印をつけた箇所から展開する処理になります
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/展開-1.jpg)
その前に、他の列は削除しておきます
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/他の列の削除.jpg)
他の列を削除したら展開処理します
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/GIF展開.gif)
上のGIF画像のように、次に開いた画面で「元の列名をプレフィックスとして使用します」のチェックは外しておきましょう!
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/展開後2.jpg)
すると上の画像のように展開されます
きちんとグループ毎に連番も作成されています
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/展開後.jpg)
グループ毎に適切に連番が作成されていることを確認したら、読み込み処理を行います
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/読み込み.gif)
<まとめ>
今回はグループ毎に連番を作成する方法について解説しました
作成にあたってはポイントが2点ありました
1つ目は、グループ化の際に「操作」の指定を「すべての行」にする点です
![画像に alt 属性が指定されていません。ファイル名: グループ化全ての行.jpg](https://analytic-vba.com/wp/wp-content/uploads/2021/03/%E3%82%B0%E3%83%AB%E3%83%BC%E3%83%97%E5%8C%96%E5%85%A8%E3%81%A6%E3%81%AE%E8%A1%8C.jpg)
こちらの処理により、グループ毎にテーブルが新規に作成されます
2つ目は、既存のメニューにより「連番」を作成するのではなく、カスタム列・作成画面からM関数にて「連番」を作成する点です
カスタム列からM関数を作成することにより、1つ目のポイントで作成した「グループ毎のテーブル」を引数にして関数を設定できます
今回の方法はエディタ内の1つの列に、複数のテーブルが作成される点が少しトリッキーです
但し、今回の処理はデータ分析やデータ整理などで使える場面が多いはずです
グループ毎に連番を作成した後、クエリを「複製」していけば、連番別に更に階層化してデータ整理が行えたりできます(例:連番1のレコードのみを集める等)
![](https://analytic-vba.com/wp/wp-content/uploads/2021/03/複製.jpg)
今回解説した方法の活用事例等についても、今後はこのブログで発信していきたいと思います!
長文に最後までお付き合い頂きありがとうございました
参考までに今回使用したファイルを添付します
次回は、前行を参照する方法を解説します
![にほんブログ村 資格ブログ ビジネススキルへ](http://qualification.blogmura.com/businessskill/img/businessskill88_31.gif)
![にほんブログ村 IT技術ブログ VBAへ](http://it.blogmura.com/vba/img/vba88_31.gif)
はじめまして。医薬のビッグデータをハンドリングするためにパワーピボットに着目し、色々探していてこちらにいきあたりました。M言語というかなりレアなトピックを平易に深堀り頂き、大変参考になります!!!ありがとうございます。最近だったらこういう情報はnoteでだして有料化、なトレンドありますがこちらでお願いします(笑)
コメントありがとうございます!役に立てているようで光栄です。今後はイベントやサークルなどの活動も行って行きたいと思いますのでそちらもよろしくお願いします
はじめまして。今年の春から社内SEに従事している者です。開発内容共有化の点でローコードでの業務改善に着目しており、Excellent仕事術様の内容は非常に参考になります。いつもありがとうございます。
カスタム列で入力補完機能を使用されていますが、私のエクセルでは機能しません。HomeandBusiness 2019ですが、バージョンの問題でしょうか?大変お手数ですが、アドバイス頂けますと幸いです。
返信が遅くなり、申し訳ありません。バージョンにより入力補完機能は使用できません。2019バージョンは入力補完機能が使用できないとの認識です。また何かありましたらご連絡ください
udemyのクーポンを頂き、
Section 4の9.でグループ毎に連番を付与する
というこのページと同様の内容のものも視聴しました。
グループ内で連番を金額の降順で付与するにはどうしたらよいのか教えて頂けますでしょうか。
グループ化前に部門、金額を降順ソートし、このステップをグループ化してもソートは無視されます。