タグ別アーカイブ: INDEX

M言語に慣れる_10回目~グループ毎に連番作成~

【M言語は難しくない】今回はデータをグループ化した際に、1から始まる連番をグループ毎に作成する方法について解説します。この処理の仕方を覚えると、エクセルの使い方の幅が広がります

コードの採番や顧客の2回目のリピート状況把握など、使いみちは沢山あります!

しかも知られざるグループ化機能を使えば、1つのM関数を入力するだけでできてしまいます

まさに魔法です

ぜひ、実際に手を動かしてこの魔法を体験してください!

目次

今回のポイント

今回の使用データ

グループ化の実施

M関数の入力

<まとめ>

今回のポイント

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

グループ化

グループ化する際に、よく選択される操作は「合計」などです

今回は「すべての行」という操作を選択します

 この「すべての行」を選択して操作を行うと、グループ毎テーブルが作成されます

M関数作成

連番は「列の追加」タブの「インデックス列」のメニューから、クリック操作で簡単に作成できます

 今回は、既存のメニューは使用せず、カスタム列の作成画面から「Table.AddIndexColumn」というM関数を使用して「連番」を作成します

 M関数で作成することで、ポイントの1点目で作成されたグループ毎のテーブルを、関数の引数として指定できるようになります

今回の使用データ

今回は下の画像にあるデータの「部門」列をグループ化します

グループ化した後は、グループ毎に連番を振ります

以下が実際に使用するサンプルデータです

グループ化の実施

解説は、前述のデータをテーブル化し、エディタを開いたところから行います

エディタを開いたらまずは、グループ化を行います

グループ化は「ホームタブ」の「グループ化」メニューから行います

次に開いた画面では、前述のように操作を「全ての行」で設定します

・グループ化対象列 ➡ 「部門」列

・新しい列名 ➡ 「部門列」

・操作 ➡ 「すべての行」

空欄のままでOKです

上の内容で指定してOKボタンをクリックすると、エディタ内では次の画像のように、テーブルがグループ別に作成されています

M関数の入力

関数の参照

次に「列の追加」タブから「インデックス列」を挿入してみます

すると、確かに連番は作成されますが、グループ毎の連番ではありません

こちらのステップについては後で削除するとして、まずは過去の記事のように関数の解説を参照してみましょう

関数の後の丸括弧を全て削除した後にエンターキーを押し、解説を表示します

テーブルに新しい名前の列を追加するという内容から解説が始まっていますが、関数の作成の仕方については、次のような内容が書いてあります

=Table.AddIndexColumn(テーブル名,”新列名”,連番の開始番号,連番の増分)

このM関数では、引数を4つ指定します

・テーブル名

・新列名

・連番の開始番号:通常は1から指定します

・連番の増分:通常は1つずつ連番を増やします

この数式の内容に従い、カスタム列作成画面から「連番」を作成し直します

なお、既に追加した連番については削除しておきましょう

カスタム列作成

それでは、「列の追加」タブから「カスタム列」をクリックします

次に開いた画面では、M関数にてカスタム列を作成していきます

使用するM関数は、前述の次の関数です

=Table.AddIndexColumn(テーブル名,新列名,連番の開始番号,連番の増分)

こちらは、入力補完機能を活用して入力していきます

まずは、「INDEX」と入力してみましょう

すると上のGIF画像のように「Table.AddIndexColumn」が選択できるようになります

↓で2つ下にカーソルを移動し、TABキーで該当のM関数を選択します

次に、前述のようにテーブルを引数の1つ目に指定します

こちらは、列の挿入で代用します

画像に alt 属性が指定されていません。ファイル名: 3つのテーブル.gif

上のGIF画像のように「部門別」の列は3つのテーブルから構成されています

ですので、下の画像の「部門別」を挿入すれば、自動的に3つのテーブルを引数として設定できます

下のGIF画像のように「部門別」列を挿入したら、残りの3つの引数もそれぞれ指定します

残りの3つの引数は、次の内容となります

・新列名 ➡ 連番

・連番の開始番号 ➡ 1

・連番の増分 ➡ 1

下の画像のように、上で紹介した3つの引数を設定したら、OKボタンを押します

すると、新しく追加した「連番」の列名の列に更に3つテーブルが作成されます

この後は、下の画像の赤い丸印をつけた箇所から展開する処理になります

その前に、他の列は削除しておきます

他の列を削除したら展開処理します

上のGIF画像のように、次に開いた画面で「元の列名をプレフィックスとして使用します」のチェックは外しておきましょう!

すると上の画像のように展開されます

きちんとグループ毎に連番も作成されています

グループ毎に適切に連番が作成されていることを確認したら、読み込み処理を行います

<まとめ>

今回はグループ毎に連番を作成する方法について解説しました

作成にあたってはポイントが2点ありました

1つ目は、グループ化の際に「操作」の指定を「すべての行」にする点です

画像に alt 属性が指定されていません。ファイル名: グループ化全ての行.jpg

こちらの処理により、グループ毎にテーブルが新規に作成されます

 2つ目は、既存のメニューにより「連番」を作成するのではなく、カスタム列・作成画面からM関数にて「連番」を作成する点です

 カスタム列からM関数を作成することにより、1つ目のポイントで作成した「グループ毎のテーブル」を引数にして関数を設定できます

 今回の方法はエディタ内の1つの列に、複数のテーブルが作成される点が少しトリッキーです

 但し、今回の処理はデータ分析やデータ整理などで使える場面が多いはずです

 グループ毎に連番を作成した後、クエリを「複製」していけば、連番別に更に階層化してデータ整理が行えたりできます(例:連番1のレコードのみを集める等)

 今回解説した方法の活用事例等についても、今後はこのブログで発信していきたいと思います!

長文に最後までお付き合い頂きありがとうございました

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

次回は、前行を参照する方法を解説します

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


にほんブログ村

M言語に慣れる_11回目_前行を参照して計算

【M言語は難しくない】今回は連番波括弧:{}を組み合わせて活用して、前行を参照する仕組みを作成します。前行を参照する仕組みを作成したら、下の図のように「前行との差額」を計算します。

この処理はエクセルシート上であれば、四則演算の式を挿入すれば簡単に行えます

=(列名/N行目)-(列名/N-1行目)

但しセル単位/1つの値単位の操作になるので、M言語を使用しなければPower Queryでは行えません

M言語を使用すればできるといっても、とても難しい処理なのでは?

いえ、決してそんなことはありません!

 エクセルシート上の行番号/レコードに相当する波括弧:{}を、連番/0,1,2・・・とうまく組み合わせて使いこなせば、簡単にできます

 実際に手を動かして前行を参照した計算が行えるようになった時には、M言語の活用に大き自信を持てるようになっているはずです!

目次

今回のポイント

今回使用するデータ

連番の追加

レコードの参照

カスタム列・作成画面で連番を操作

<まとめ>

今回のポイント

過去の記事で、丸括弧と波括弧を組み合わせてエディタ内のデータを参照する方法について解説しました

 括弧を使用して参照する方法は、下のGIF画像のように<前ステップ名 + 波括弧/レコード位置 + 角括弧/リスト>を組み合わせる方法でした

今回は、この「括弧の組み合わせて参照する仕組み」を、更に「連番 /0,1,2,3・・・」と組み合わせて活用します!

今回使用するデータ

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

上の画像のB列にある「株価」を使用して、計算を行います

各行にて、前行との株価の差額を計算します

連番の追加

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

まず、「列の追加」タブから「インデックス列」のメニューをクリックし「連番」を追加します

インデックス列は「0」から始まるように指定します

1ではなく0にて開始する理由は、M言語は「0ベース」だからです

この追加した「インデックス列/連番」を「レコードの参照」に応用します

レコードの参照

前述の「インデックス列/連番」を「レコードの参照」に応用する点について、具体例で解説します

上の画像のように、エディタ内にインデックス列/連番を追加した段階では、適用したステップは下の画像のようになっています

まず、画像内の最終ステップの上で「F2キー」を押して、ステップ名を「DATA」に変更します

その後に、数式バーのマークをクリックして、「DATA」ステップを確定させます

これで、「DATA」ステップを前ステップとして参照する「確定処理」ができました

この確定処理を行った段階にて、適用したステップは次の画像のようになっています

ここから、インデックス列/連番を活用して、手動で各株価を参照してみましょう!

まずは、下の画像にて黄色の印をつけた箇所の株価を参照してみましょう

こちらは、インデックス列の番号「0」を、下の画像のように波括弧 / {}内に使用します

*角括弧:[]と波括弧:{}を使う順番は、前述の「今回のポイント」で解説した

次は、4行目を参照してみましょう

こちらは、インデックス列の「3」を使用します

実際に例を使い、インデックス列の値を使用して株価を参照する方法について解説しました

2つの株価は、上記では手動にて参照しましたが、次の章ではカスタム列・作成画面から同じロジックを自動で適用できるようにします

カスタム列・作成画面で連番を操作

前行参照

前章にて、手動で行ったロジックをカスタム列・作成画面にて適用すると下の図のような数式になります

こちらも数式が<前ステップ名 + 角括弧 + 波括弧>となっています

違うのは、波括弧 / {}内に「ⅰ:直接インデックス列」を挿入した上で「ⅱ:1を引く」という2点です

結果、エディタ内に次の画像のように「前行を参照した列」が挿入されます

ちなみに、以上のようなカスタム列を挿入する前に、次の画像のように数式バーを前の状態に戻しておきましょう

差額計算

これで前行を参照する仕組みができました

次に、カスタム列作成画面を「差額」が計算できるように修正します

こちらは、下の画像のように「株価」の列を挿入して「前行」を引くだけです

これで、差額を算出した列が挿入されます

エラー修正

今のままだと「差額」の列の1行目がエラーになってしまします

ですので、IF式をカスタム列作成画面に組み入れてエラーを回避します

IF式は「IF 条件式 then ~条件に合う場合~else~条件に合わない場合~」と書きます

条件式は「インデックス列」が0の場合を条件にします

これで、1行目のエラーを回避できました

<まとめ>

 今回は、前行を参照して「前行との差額」を算出する方法を解説しました

 エディタ内で、インデックス列を挿入すると自動的に「0」から始まる連番が挿入されます

 この連番をレコードの番号に置き換えて、前行を参照する仕組みに応用しました

 実際に作業としてはカスタム列・作成画面にて、角括弧の中に「インデックス列」を挿入して活用しました

画像に alt 属性が指定されていません。ファイル名: カスタム列作成画面-1.jpg

 今回紹介した「前行参照」が行えると、分析用の集計作業で行えることの幅が広がります

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

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

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

次回は「例外処理」について解説します

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


にほんブログ村