タグ別アーカイブ: M式

M言語に慣れる_1回目~コード構造の把握~

【M言語は難しくない!】M言語への理解を深めて、Power Queryの「まだ触れたことのない便利機能」を有効活用できるようになりましょう!!

 パワークエリは直感的なクリック操作で一括処理が行えるのが大きなメリットです。ところが、エクセルシート上では簡単に行えていた「セル単位での操作」や、「別シートの参照」、「関数を組み合わせた処理/ネスト」に相当する処理が行えません

要は、Power Queryエディタ内のメニューにある、行列単位の一括処理しか行えないのです

 Power Queryエディタ内のメニューにある処理以外の事をしようとすると、M言語の領域にまで踏み込むしかありません

ところが、詳細エディタや数式バーを見ると、小難しそうなコードが並んでいます

M言語は一見、取っ付き難いのは確かです

但し、特定のルールや使用パターンさえ押さえてしまえば、実はそんなに難しくないはずです

まずは簡単な例にて、M言語を活用することが必ずしも難しくないことを紹介したいと思います

下の画像は、あるクエリのPower Queryエディタ内の画像です

このエディタ内で<赤印の「販売金額」列の1行目の「54000」>の箇所を抽出してみます

エクセルシート上で言えば、「=セル名(例:A9など)」を数式バーに入れる処理です

ちなみに適用したステップには、まだ1つのステップ「ソース」しかありません

この場合、値の抽出は数式バーに簡単な1行を入れるだけで行えます

下のGIF画像の数式バーに注目してください!

=ステップ名[列名]{行位置}という単純なコードを入力しただけです

 但し、コード内の角括弧:[]と波括弧:{}の使い方のルールが分からないと小難しく感じてしまうただそれだけなのです

逆に、[]{}の意味が分かるだけでも「かなりのことが行えそう!」と感じませんか?

少し種明かしをすると、[]がセル名のA列やB列に相当するものです

{}が「A9セル」や「B2セル」の行番号に相当するものです

そしてコード内のステップ名が、セル名の前にを伴って付くシート名(例:平均値の計算!A2)みたいなものです

ルールやパターンが分かれば、エクセルシート上での処理とそれほど違いはないのです

この回では、M言語に慣れるためのウォーミングアップとして、M言語の特徴を3つに絞り解説します

そして、最後にPower Queryの「まだ触れたことのない便利機能」の事例を1点解説します

今回解説する「まだ触れたことのない便利機能」 の便利さを肌で理解した時には、「M言語理解へのハードル」が確実に少し下がっているはずです

1.コードのカラー

まず、Power Queryエディタ(以降、エディタ)で「列のマージ」をしてみます

マージする内容は、下の画像の「姓」と「名」です

「姓」と「名」の間には、スペースを入れます

すると、数式バーが次のような表示になります

上の画像には、黒を除いた「3色」が含まれています

この3色にはそれぞれ意味があります

①赤➡文字列

列をマージする際に、新しく追加される列の「列名」として指定した文字列「氏名」が赤色になっています

②青➡システム予約語

後の回で、個々の用語の意味などは詳細を解説しますが、「each」や「type」などが青色になっています

これらは「システム予約語」と言われるものです

他に「if」「else」なども該当します

システム予約語は、システム言語として、予め使用することが決まっている用語です

③緑➡ハードコード(自動記録)された値

こちらは、エディタ内でステップを追加した際に、ハードコード(自動記録)されたです

上の画像は、列をマージした時、自動的にデータ形式が「文字列」として自動記録されたことを表しています

2.大文字と小文字の区別

M言語を扱う際に、意識しておかなくてはならないことの一つとして「大文字と小文字」の区別があります

仮に、下の画像の「T」を小文字の「t」に置き換えてみます

すると、下のGIF画像のようにエラーになります

M言語を扱う際には、大文字と小文字の区別は特に意識しましょう!

3.ステップの引継ぎ

前述の1.で列のマージを行ったエディタ内には、下の画像のように2つの適用したステップがあるものとして以降の解説を行います

上の画像の2つ目のステップ「挿入された結合列」の数式バーの表示には、「ソース」という名前があるのが確認できます

では、3つ目のステップとして、既存の列を削除したらどうなるでしょうか?

3つ目のステップの中には、2つ目のステップの名前「挿入された結合列」が含まれています

つまり、各適用したステップは、前のステップ名を通じ、基本的には<一つ前ステップの内容>を自動的に引き継いでいきます

4.参照ステップ作成

前述の3.ではエディタ内で<一つ前のステップの内容>を自動的に引き継ぐというPower Queryの特徴について解説しました

但し、これはあくまでも”基本的には”、”自動的には”という話しであり、前ステップをスキップして参照することもできます

では、この「参照のスキップ」を利用した、新たな数字の集計方法を紹介します

エディタ内の話しに戻り、一つステップを追加します

上の画像の「注文金額」の列から全体平均を算出します

すると下の画像のように、「削除された列」ステップを引き継いだ「計算された平均」というステップが追加されます

この「計算された平均」で算出した値「53609」を、他のステップで参照して活用できるようにします

この、他のステップで参照できるようにしたステップを、以降「参照ステップ」と呼ぶことにします

では、この「計算された平均」ステップを「参照ステップ」として確定するための処理を数式バーで行います

下のGIF画像のように「関数のマーク」を左クリックすると、新たなステップが追加されます

「カスタム1」というステップが新たに追加されましたが、このステップはある種、ダミーのステップです

更にこのステップから、下の図のように「前のステップ」をスキップして「削除された列」ステップを参照します

前のステップをスキップして参照するには、下のGIF画像のように数式バーでステップ名「削除された列」を入力し、Enterを押します

すると、エディタの画面が「削除された列ステップ」の内容になります

エディタ画面からは下の画像の「平均金額」は消えましたが、これで「平均金額」を他の画面で参照して活用できるようになりました

今回は、「平均金額」を参照ステップとして活用したカスタム列を作成してみます

作成する内容は「個々の注文金額-平均」です

注文金額については、カスタム列作成の右側から挿入できます

「平均」については、前述のステップ名「計算された平均」を参照します

つまり、「計算された平均」ステップで算出された「53609」が「注文金額」から差し引かれます

カスタム列を作成した後のエディタ画面は、次の画像のようになります

このカスタム列を作成するまでに、ステップを新たに追加したり、ステップをスキップしたりしたので、図で整理して今回の処理を振り返ります

ⅰ)カスタム1の列をダミーとして追加

この処理で追加されたステップにより、「計算された平均」ステップを残したまま、エディタ画面を「削除された列」ステップの画面に戻せています

つまり、「削除された列」➡「計算された平均」というステップの順序が、「計算された平均」➡「削除された列」という順序に変えることができました

ⅱ)カスタム列の作成

ⅰ)で変えた順序を活かして、個々の注文金額マイナス全体の平均を計算します

<まとめ>

今回は「M言語に慣れる」の1回目として、M言語の特徴を3つに絞って解説しました

1.コードのカラー

2.大文字と小文字の区別

3.ステップの引継ぎ

 上の3つを意識してPower Queryを活用してもらえると、M言語が必ずしも難解なわけではないことが、分かって頂けると思います

そして、3つの特徴の後には3.ステップの引継ぎを応用した「参照ステップ」について解説しました

こちらでは、ステップの順序を入れ替えて「ステップで算出した値」を有効活用できるようにしました

ⅰ)一旦、注文金額の平均値を計算➡参照ステップ

ⅱ)カスタム列・作成画面で「各行の注文金額-平均値/参照ステップ」を計算

この上のⅰ)ⅱ)の処理はエクセルシート上で例えるなら、以下の画像のような処理です

 データが存在するシート/画像左とは別のシート/画像右で平均値を計算しておき、元のデータが存在するシートで注文金額から平均値を引いています

ですので、今回使用した参照ステップの値はエクセルシートでの操作でいえば別シートでの計算です 

 M言語を使用していけば上の事例と同じ様に、エクセルシート上での細かい操作に近いことが一括で行えることが理解できてきたと思います

 では、次回からはM言語を有効活用してPower Queryの魅力を新たに引き出す方法について、もっと具体的に解説していきます

M言語の記事一覧については、ここから見ることができます

記事を最後までお読み下さり、誠にありがとうございました

参考までに解説に使用したエクセルファイルを添付します

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


にほんブログ村

M言語に慣れる_5回目~M関数を検索する方法~

【M言語は難しくない!】今回は使用方法に合ったM関数を、Power Queryエディタ(以降エディタ)上で検索する方法を紹介します

エクセル関数では、エクセルシート上で直接、関数を検索できます

実は、Power Queryでも同じような機能があります

エクセル関数と同じ様に検索した後に、そのまま使い回すことはできませんが、この機能を覚えておくととても便利です

目次

準備作業 / 空のクエリ作成

M関数一覧を取得

一覧をテーブル化

実際に検索してみる

<まとめ>

準備作業 / 空のクエリ作成

まずは準備として、空のクエリを作成します

「データタブ」➡「データの取得」の順でクリックします

次に「その他のデータソース」を選択し、「空のクエリ」をクリックします

M関数一覧を取得

空のクエリの数式バー上で=#sharedを入力し、エンターを押してください

すると、M関数の一覧が表示されます

以降は有料ページ/課金サービスへ

【分析】Power Queryで手軽にABC分析

 

 ABC分析は「重点分析」とも言われ、分析手法の中でも最も基本的なものです。ところが、実際にエクセルでABC分析用の表作成を行おうとすると「データのバケツリレー」の手間を要します。データのバケツリレーは「列から列」へと続き、数式を細かく設定する手間もかかります。今回紹介する方法であれば、Power Queryエディタ画面(以降、エディタ)でシンプルに処理していけます!

以下が、通常のABC分析の表を作成するときの手順です

①重点管理する項目(金額など)を降順で並び替え

②①を累計で表示 ➡「=」から始まる数式を入力

③②を比率に変換➡ 事前に累計の値を算出 ➡累計の値で各行を割り算

④③をランク付け➡IF式を入力

もし、データの追加などがあったら、①~④の修正作業が必要です

もちろん、データ自体が変われば、①~④の更新処理が必要です

Power Queryであれば、一度、クエリを作成してしまえばデータ更新時に「ソース変更」「クエリの更新」をクリック処理をするだけで済みます

今回使用するデータと行いたいこと

使用データ

今回は、下の画像にある2つの列から構成されるデータを使用します

行いたいこと

前述の使用データの各行に、次の内容でランクを付けます

A ➡全体・販売個数の50%以下

B ➡ 全体・販売個数の80%以下

C ➡ 全体・販売個数の80%超

今回のポイント

今回使用する主な技術は、主に以下の1~3の内容です

1と2は過去に紹介した内容です

最後の3.List関数が今回の一番のポイントになります

1.並び替え/降順

まずは元データを降順で並び替えます

2.条件列の追加

「列の追加タブ」の「条件列」メニューでランク付けを行います

3.List関数

このList関数で累計値と比率を計算します

List関数は2種類使用します

まず1つ目は、List.Select関数です

リストから条件に沿う値を抽出し、リストを再作成します

書き方は次の通りです

List.Select(リスト,条件)

上の画像では、1~4の値からなるリストから、2超の値を抽出した上でリストを再作成しています

2つ目は、List.Sum関数です

こちらの関数は、直感的にも分かり易いと思います

List.Sum()の丸括弧の中に入れたリスト内の各値を合計します

但し、丸括弧の中に入るのはあくまでリストであり、列ではないことに注意しましょう!

では、今回のポイントを紹介したところで本格的な解説を始めます

尚、解説はエディタ内のみの内容になります

並び替え

まずは、エディタ内で販売個数を降順で並び替えます

累計値

算出ロジック

List関数を組み合わせて累計値を計算するのが、今回の記事の最大ポイントです

累計値を計算するロジックについては、次の画像を基にして解説します

通常のABC分析では、N行目のとN-1行目の累計値の合計を計算します

今回の記事では、上の画像のようにⅰ)N行目の値以上の値のリストを作成、ⅱ)ⅰのリストを合計、というⅰ)⇒ⅱ)の流れで行います

List.Select関数

まず、カスタム列・作成画面で「販売個数」列を挿入してA列を作成してみます

この処理だと下の画像のように、販売個数と同じ列ができるだけです

では、カスタム列・作成画面で前ステップ名(カスタム列を作成するステップの1つ前)を入れてみましょう

この段階では、前述の「降順に並び替えられたステップ」が前ステップです

下の画像が、前ステップ名を入れたカスタム列・作成画面の画像です

上の画像の内容でカスタム列を作成すると、次の画像のように各行にてリストが作成されます

このリストの中味は、一律に販売個数の列の内容です

この各行のリストから、前述のロジックの通り「各行の値以上」のリストを各行に再作成していきます

こちらのリストの再作成は、List.Select関数で行います

条件式は、一部、過去の記事で解説したカスタム関数の内容を使います

上の画像のように、「(x)=>」にてxを変数として宣言し、「x>=」を条件式とします

この内容でカスタム列を作成すると、行毎に作成されるリストの内容が変わっています

例えば、上の画像のように2番目の行であれば。2つの値しかリストの中にありません

では、前述の紹介したロジックの通りにリストが再作成できたので、List.Sumの解説に移ります

List.Sum関数

では、List.Select関数で作成したリストをList.Sum関数で合計し、累計を作成します

上の画像のようにカスタム列・作成画面に数式をセットしてOKボタンを押すと、累計の列がエディタ内に追加されます

比率

では、累計を計算したので「比率 =各行の累計値÷販売個数の合計値」 を計算します

販売個数の合計値の算出には、前述のList.Sum関数を使います

ちなみに、上の画像の「追加されたカスタム」とは前ステップ名です

ランク付け / 条件列

では、最後にランク付けを行います

ランク付けは「列のの追加タブ」にある「条件列」で行います

記事の冒頭にあったように、ランク付けの条件を「条件列の追加」画面に設定します

比率が0.5以下であれば ➡ A

比率が0.8以下であれば ➡ B

とします

上記の条件以外はCとして出力するようにします

この条件列を作成すれば、ABC用の表作成は完成です

<まとめ>

今回は、Power QueryでABC分析の表を作成しました

一番のポイントは、List関数を2つ組み合わせて「累計」の列を作成する点です

List関数の中には、常に「リスト」を指定します

M言語では、「リスト」と「」は明確に違います

今回の内容では、List関数の丸括弧の中は「ステップ名[列名]」で指定しました

この「ステップ名」を指定する場合があることを強く意識しておけば、後はそれ程難しい点はありません

 私は以前、企画の仕事をしている時にABC分析表を毎月作成しておりましたが、面倒で仕方ありませんでした

 今回、紹介したPower Queryの仕組みであれば、一度クエリを作成してしまえば、毎月ほぼ「ソース変更」「クエリ更新」をクリック処理で行うだけで済みます

分析で一番大事なのは、数字を解釈することです

 今回紹介した仕組みで、表作成を効率化して有意義な分析を行えるようにしていきましょう!

次回からはRFM分析の解説をはじめます!

https://analytic-vba.com/power-query/analysis/recency-group/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

M言語に慣れる_6回目~3種類の括弧~

【M言語は難しくない】Power Queryを扱っていると、3種類の括弧が出てくるのが分かります。 「M言語が難解」と感じる理由の一つは、この3種類の括弧が組み合わせられて出てくるからです。

・丸括弧 / ()

・角括弧 / []

・波括弧 / {}

特に下の2つの括弧の[]{}は、初めて見たときは意味合いが全く不明です

実は、この2つの括弧M言語を使いこなすための重要な鍵です

M言語を学ぶ目的の一つは、行列単位での一括処理からの脱却です

シート上でセルを扱うように、Power Queryエディタ上でも1つの値を扱いたいのです

少し、シート上でのセル操作についてお話したいと思います

セル名は例えば「A8」であれば、Aは列名で8は行番号です

この列名と行番号を組み合わせれば、望みのセルにたどり着きます

実は角括弧:[]はシート上の列名に、そして波括弧:{}行番号に相当します

今回はこれらの括弧に、次の2つの方法を通じて慣れて頂くとともに、それぞれの意味合いを掴んて頂きたいと思います!

なお、解説は今回を1回目として、2回に分けて行います

・既存のクエリから角括弧 / []と波括弧 / {}を使い、1つの値を参照する ➡1回目

・空のクエリから3種類の括弧を使って、テーブルを作成する ➡2回目

この3つの括弧の意味合いを掴んだ時には、M言語への苦手意識が薄れているはずです!

目次

今回のポイント

括弧を使ってデータ参照

<まとめ>

今回のポイント

今回の解説にあたっては、そもそも、Power Queryとは何なのか?という点がとても重要です

エクセルはもともと表計算ソフトであり、データベースではありません

Power Queryは、エクセルをデータベースとして使用できるようにするツールです

ですので、エディタ内ではデータベースとして使用するための「データの構造化」が行われています

この構造化が「Power Queryは何か?」を理解するためのキーワードです

まずは、構造化には4種類の概念があることを常に頭に入れておきましょう!

①値

値の種類には、主に次のようなものがあります

・1/ 整数(数値)

・b / テキスト(文字列)

・2021/03/04(日付)

今回の解説で使用する値の概念は、もっと本源的なものです

例えば、1は数字ですが、データベース上は2つの型式になりうります

今回解説する値の概念に照らすと、1は整数でもない文字列でもない、ただの「1」です

②リスト

①の値が、列方向に順次並べられたものです

通常の列とは違います

その点は後で解説します

③レコード

行方向に、複数の②のリストを1行で組み合わせたものです

④テーブル

上の②と③を組み合わせたものです

これらの4つの概念を頭に入れたところで、本格的な解説をはじめます

括弧を使ってデータ参照

今回は、3つの列から構成される、次の画像のデータを解説に使用します

上の画像の表から括弧を使って、様々な参照作業を行うわけですが、まずは参照作業を行い易くします

ちなみに、この章では角括弧 / []波括弧 / {}による参照しか行いませんので予めご了承ください

 参照作業を行い易くする為に、下のGIF画像のように数式バーのマークをクリックし、数式バーの内容を前ステップ名で確定させてます

これで数式バーにて、前ステップ名の次に括弧をつなげていくだけで参照作業ができるようになります

角括弧 / []による参照

角括弧 / []の中には、基本的には列名のみが入ります(列名以外も入れるパターンは後述)

では、数式バーに次の内容を入力してみます

➡ = 変更された型[連番 / 1列目]

すると、次のGIF画像 のように、前述のデータの1列目が参照されます

下の画像の赤丸の箇所に注目してくみてください

リストとして出力されているのが良く分かります

 元の「列名」である「連番」の記載がどこにもなく、データ形式「整数」の表示もありません

変換タブでリスト変換した時と同じ画面になっています

前ステップの列の内容をそのまま参照する場合には、角括弧 / []を2重にして[[連番]]にします

以降は有料ページ/課金サービスへ

M言語に慣れる_8回目~eachを使いこなしてM関数作成~

【M言語はむずかしくない!】 数式バーでよく見かけるコードの一つに、青字の「each」というコードがあります。青字ということは、システム予約語なので、数式バーに頻繁に登場します。ですので、「each」に慣れれば、自ずとM言語自体にも慣れ親しんでいけます。今回はこの「each」についての事例数多く紹介します。

多くの事例を通じて「each」自体についての意味合いを掴み、M関数内でのeachの使い方に慣れていきましょう!

目次

今回のポイント

「each」の使用例

「each」の中味について

M関数を作成する / List.Select

<まとめ>

今回のポイント

「each」とセットでよく出てくるコードに「アンダースコア / _」があります

「each」と 「アンダースコア / _」の2つは切っても切れない関係にあります

何故なら、「each」は「アンダースコア / _」を使用した関数を簡略したものだからです

ですので、今回は 「アンダースコア / _」についても解説します

 但し、「each」と「アンダースコア / _」の詳細な内容は難解なので、「こういうもの」だと割り切って読み進めてください

そして、最後にM関数「List.Select」を「each」と「アンダースコア / _」を使い作成します

実際に手を動かしてみて「each」と「アンダースコア / _」に対する感覚を掴んでみてください

M関数の作成にあたっては、前回も行ったようにM関数の使用例を活用します!

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

「each」の使用例

まずは、「each」の理解の入り口として「each」の使用例を2つ紹介します

フィルタリング

下の画像のように、エディタ内に「氏名」「部門」という列名の列があるデータがあるとします

こちらの部門列から「営業」をフィルタリングします

すると、数式バーは以下の表示になり、「each」が条件式に合うものに対して使われているのが分かります

抽出

前述のデータにて、今度は「氏名」の列から「苗字」を抽出します

使用するメニューは「変換」タブ内の「抽出 / 区切り記号の前のテキスト 」になります

区切り文字として使用する記号は「半角スペース」を使用します

すると、数式バーは以下の画像のような表示になり、「each」と「アンダースコア」(“BeforeDelimiter(“の後に表示)が使用されています

上の画像だと分かりずらいと思いますので、文字列に直して表示し直したものも確認しておいてください

= Table.TransformColumns(フィルターされた行, {{“氏名”, each

Text.BeforeDelimiter(_, ” “), type text}})

以降は有料ページ/課金サービスへ

M言語に慣れる_9回目~複数シートをコード1行で結合~

【M言語は難しくない】今回は、空のクエリM関数を1つ入力するだけで複数シートを一気に結合できるようにします!今回紹介するM関数を使いこなせば、面倒なシート間のデータのバケツリレーから抜け出すことができます

 Power Queryで複数シートを結合する方法として一般的に紹介されている方法では、次の画像の処理「クエリの追加」が必ず入ると思います

今回は、「クエリの追加」は使わず空クエリの数式バーに「=Excel.CurrentWorkbook()」を入力します

1つM関数を入力するだけで、ファイルに含まれるシート内のテーブルが全てエディタ内に反映されます

これで、シート間のバケツリレーの作業からは抜け出すことができます!

但し注意点もありますので、そちらもあわせてM関数/Excel.CurrenWorkBookの使用方法を解説します

目次

今回のポイント

今回使用するデータ

準備作業/空のクエリを作成

M関数の入力/Excel.CurrentWorkbook

読み込み処理

データの循環対策

シート追加テスト

<まとめ>

今回のポイント

今回はM関数を入力した後に、1点だけ注意点があります

それは、循環問題です

複数シートを1つにまとめた内容/クエリが、M関数に反映されてしまいます

ですので、クエリを更新すると読み込み行数が倍になります

対策として、適用したステップに1つのステップを追加します

今回使用するデータ

今回使用するデータには、シートが3つ含まれています

1シートが1か月分の出荷データになっています

各シートの内容は、事前に「シート名/Data_y年m月」をテーブル名にしてテーブル化してあります

次のファイルが今回、実際に使用するサンプルデータです

準備作業/空のクエリを作成

まずは空のクエリから作成します

「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

以降は有料ページ/課金サービスへ

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

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

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

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

まさに魔法です

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

目次

今回のポイント

今回の使用データ

グループ化の実施

M関数の入力

<まとめ>

今回のポイント

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

グループ化

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

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

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

M関数作成

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

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

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

今回の使用データ

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

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

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

以降は有料ページ/課金サービスへ

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ベース」だからです

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

以降は有料ページ/課金サービスへ

【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つ事例を紹介します

https://analytic-vba.com/power-query/m-code/begin-text-function/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

【M言語に慣れる】_15回目_TEXT関数/文字列関数

【M言語は難しくない】今回は、TEXT関数の中から、2つの事例を紹介したいと思います。通常のエクセル関数と同じ様に、M言語でも文字列を操作する関数は多数あります。興味のある方はぜひ、こちらのMicrosoft社のページも参照してみてください

目次

文字列の有無を判定する関数

文字列を置き換える関数

<まとめ>

文字列の有無を判定する関数

 Text.Contains関数は、ある文字列の中に指定した文字列があるかとうかを判定し、「TRUE」か「FALSE」で返す関数です

以下の一文は、Microsoft社のページからの抜粋になります

Text.Contains(“Hello World”, “Hello”)

上の ように記述した場合には、「TRUE」を返します

次もMicrosoft社のページからの抜粋になりますが、この場合は「FALSE」を返します

Text.Contains(“Hello World”, “hello”)

ちなみに、

 M言語では、大文字と小文字の違いは考慮されますので、この点は注意が必要です

以降は有料ページ/課金サービスへ