VBAで言えば、メソッドにあたるものです
フィルター作業
文法
リストの中から条件に一致するものを抽出
=List.Selct(リスト,条件)
実際の使用例
➡M言語に慣れる_8回目～EACHを使いこなしてM関数作成～
条件に合うリスト作成
文法
累計などを集計する時に使用します。条件に合うリストを作成します
=List.FirstN(リスト,条件)
実際の使用例
リスト系・操作関数
タグ別アーカイブ: リスト
M言語に慣れる_6回目～3種類の括弧～
【M言語は難しくない】Power Queryを扱っていると、3種類の括弧が出てくるのが分かります。 「M言語が難解」と感じる理由の一つは、この３種類の括弧が組み合わせられて出てくるからです。
・丸括弧 / ()
・角括弧 / []
・波括弧 / {}
特に下の２つの括弧の[]と{}は、初めて見たときは意味合いが全く不明です
実は、この２つの括弧がM言語を使いこなすための重要な鍵です
M言語を学ぶ目的の一つは、行列単位での一括処理からの脱却です
シート上でセルを扱うように、Power Queryエディタ上でも１つの値を扱いたいのです
少し、シート上でのセル操作についてお話したいと思います
セル名は例えば「A８」であれば、Aは列名で８は行番号です
この列名と行番号を組み合わせれば、望みのセルにたどり着きます
実は角括弧：[]はシート上の列名に、そして波括弧：{}は行番号に相当します
今回はこれらの括弧に、次の２つの方法を通じて慣れて頂くとともに、それぞれの意味合いを掴んて頂きたいと思います！
なお、解説は今回を1回目として、2回に分けて行います
・既存のクエリから角括弧 / []と波括弧 / {}を使い、１つの値を参照する ➡1回目
・空のクエリから3種類の括弧を使って、テーブルを作成する ➡2回目
この３つの括弧の意味合いを掴んだ時には、M言語への苦手意識が薄れているはずです！
目次
今回のポイント
今回の解説にあたっては、そもそも、Power Queryとは何なのか？という点がとても重要です
エクセルはもともと表計算ソフトであり、データベースではありません
Power Queryは、エクセルをデータベースとして使用できるようにするツールです
ですので、エディタ内ではデータベースとして使用するための「データの構造化」が行われています
この構造化が「Power Queryは何か？」を理解するためのキーワードです
まずは、構造化には4種類の概念があることを常に頭に入れておきましょう！
①値
値の種類には、主に次のようなものがあります
・1/ 整数（数値）
・b / テキスト（文字列）
・2021/03/04（日付）
今回の解説で使用する値の概念は、もっと本源的なものです
例えば、１は数字ですが、データベース上は２つの型式になりうります
今回解説する値の概念に照らすと、１は整数でもない文字列でもない、ただの「１」です
②リスト
①の値が、列方向に順次並べられたものです
通常の列とは違います
その点は後で解説します
③レコード
行方向に、複数の②のリストを1行で組み合わせたものです
④テーブル
上の②と③を組み合わせたものです
これらの４つの概念を頭に入れたところで、本格的な解説をはじめます
M言語に慣れる_7回目～空のクエリからテーブル作成～
【M言語はむずかしくない！】今回は、空のクエリから、3種類の括弧を使ってテーブルを作成します。テーブルを作成した後は、M関数も作成を行います！その際、M関数を使いやすくする方法についてもあわせて解説します！！
前回は2種類の括弧を使い、エディタ内で各種参照を行いました
角括弧 / [] ➡ リスト、列の抽出
波括弧 / {} ➡ レコードの抽出
前回で、M言語における2種類の括弧の役割がおぼろげながら見えてきたと思います
今回、3種類の括弧を使ってテーブルを作成した後は、過去に紹介した方法とは別の方法でM関数を参照する方法についても解説します！
3種類の括弧に慣れると、M言語がグンと理解し易くなりますし、応用も効きます！
ぜひ、実際に手を動かして取り組んでみてください
まずは、空のクエリを作成するところから行います
なお、
他のクエリも後程参照したいので、演習を行う際には、既存クエリが存在するファイルを使用してください
目次
準備作業/空のクエリを作成
まずは空のクエリから作成します
「データタブ」➡「データの取得」の順でクリックします
その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします
そして、下の画像にある「空のクエリ」をクリックします
するとエディタが開き、空のクエリが立ち上がります
リスト作成
リストは波括弧 / {}を使用して作成します
①値が１個
では、数式バーに={“a”}と入力してみます
リストができ、値がリストに１つ入りました
②値が２個
では、={1,2}と入力してみます
リストができ、値がリストに2つ入りました
UNIQUE関数xFILTER関数から重複無リスト抽出～条件一致～
FILTER関数やUNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります！
今回は FILTER関数とUNIQUE関数 の組み合わせにより、ある表から条件に合う重複が無いリストを作成する方法を解説します
そして、最後はUNIQUE関数ｘFILTER関数の組み合わせを応用した内容も解説します
関数の指定範囲に空欄が含まれると、下の画像のように出力結果に「０」が含まれてしまいますので、０を含まれないようにする方法も解説します
目次
条件に合う重複無リスト作成
応用編
＜まとめ＞
条件に合う重複無リスト作成
今回は、下の画像の左にある表から「70点未満の得点を一度でも取った人」リストを重複が無いように作成します
抽出元の表には、田中さんや佐藤さんのように同じ名前の人のデータが複数含まれています
まずは設定条件/<70に沿ったFILTER関数を作成してみましょう
FILTER関数の書き方は次の通りです
=FILTER(抽出範囲,条件)
上の書き方を今回のやりたい事に当てはめると次の画像の通りになります
上の画像では、まだFILTER関数しか入力していないので、リストに佐藤さんの名前が複数回登場しています
ですので、FILTER関数にUNIQUE関数を下の画像のように組み合わせます
これで、佐藤さんの登場は1回のみになりました
UNIQUE関数～重複排除～
今回は、1つのセルに関数を入力するだけで重複排除したリストが取得できる、「UNIIQUE関数」について解説します
尚、今回の解説で使用するデータは次の画像になります
上の画像のデータは、既にテーブル化してあります
ですので、関数の引数（中味）の指定については、過去の記事で解説した「テーブルとの連携」により指定する方法で解説します
詳細を知りたい方は過去の記事をご確認お願いします
１つの列での重複排除
このUNIQUE関数の書き方は至ってシンプルです
対象となるデータ範囲を指定するだけです
=UNIQUE(対象範囲)
今回はテーブルと連携するので、下のGIF画像のように対象範囲を指定します
上のGIF画像では、対象範囲を指定する際に、年度の列にて↓が出ていることにご注目ください
複数の列での重複排除
UNIQUE関数で複数の列を対象範囲に指定すると、指定された列の組み合わせの中から、重複しない組み合わせを出力します
行方向での重複排除
UQNIQUE関数の２つの引数を「TRUE」と指定すると、行方向での重複排除を行うことができます
1回登場する組み合わせを抽出
下の図のようにUNIQUE関数の3番目の引数を「TRUE」と指定した場合には、1回登場する組み合わせのみが抽出されます
=UNIQUE(Data[[年度]:[地域]],,TRUE)
下の図のように、画像左側の「TRUE」を指定していない場合と比較してみましょう
3番目の引数をTRUEと指定した右側では、1回登場している組み合わせのみが抽出されているので、左側より行数が少なくなっています
例えば、元のデータの黄色に印を付けた箇所は複数登場しているので、UNIQUE関数で抽出されていないのです
テーブルと連携しないケース
テーブルと連携しないケースでは、下の図のように対象範囲に空欄が入る場合があります
この場合には、UNIQUE関数の出力に0が含まれます
この場合には、FILTER関数を使用して「空欄以外」の値を抽出するようにする方法があります
下の図のように、FILTER関数の抽出条件を「<>””」とします
こうすれば、0は出力されません
まとめ
今回は、UNIQUE関数により重複排除したリストを出力する方法を解説しました
省略可能ですが、2番目の引数をTRUEとすれば行方向での重複排除も行えます
最後にFILTER関数と連携して「0」を出力しないようにする方法も解説しました
今回は他の新関数との組み合わせについては1パターンしか解説しませんでしたが、このUNIQUE関数の醍醐味は他の関数との組み合わせです
次回からは他関数との組み合わせについて解説を行って行きます！
【M言語に慣れる】_14回目_複数行を１つのセルにまとめる
【M言語は難しくない】今回は複数行に拡散している値を、次のGIF画像のように「記号」をつなぎ目にして、１つのセルにまとめます
目次
今回のポイント
今回のポイントは２つあります
1つ目は過去記事で紹介した「すべての行」によるグループ化です
この「すべての行」によるグループ化により、グループ別にテーブルを作成します
2つ目はリストの「値の抽出 / １つのセルにリスト化」機能の活用です
こちらは、実際の例で見てみましょう！
空のクエリで次の画像のように、２つのリストを作成したとします
上の画像のリストを一旦、テーブル化した後、黄色に印を付けた「展開マーク」をクリックをすると「値を抽出する」が選択できます
上のGIF画像のように「値を抽出する」を選択した後は、リストの「区切り記号」を指定できます
「区切り記号」を指定した後は、下の画像のように１つのセルにリストの値が出力されます
今回使用するデータ
今回使用するデータは、次の画像のデータです
名前の列をグループ化し、グループ毎に「商品列に含まれる商品」を１つのセルに出力します
グループ化の実施
今回の解説は、使用データをエディタで開くところから始めます
上の画像にて、黄色の印を付けた「グループ化」をクリックします
なお、事前に「注文日」の列は削除しておきます
グループ化の内容は次の画像のように指定します
・グループ化項目：名前の列をグループ化します
・新しい列名：詳細
・操作：前述のように「すべての行」を指定します
・列：空欄のままでOKです
上記のように指定してOKボタンを押すと、エディタ画面は次のようになります
グループ化された名前毎に、テーブルが作成されています
次はグループ毎に、上の画像の商品列の内容をリスト化します
カスタム列の追加
前述のグループ毎にリスト化するには、カスタム列・作成画面を開くところから始めます
カスタム列・作成画面で、次の画面のように詳細列を指定すると、過去の記事の通りグループ毎にテーブルが作成されます
次の画像の「詳細.1」列が、上の画像から出力されたカスタム列の内容です
今回はテーブルではなく、各テーブル内の商品リストを出力します
リストを出力するには、カスタム列・作成画面にて次の画像のようにリストになる列を加えます
これで、商品リストが各テーブル毎に出力されます
各リストにカーソルをあてると「リストの中味」が次の画像のように見れます
こちらを前述の「今回のポイント」にて紹介したように、「展開マーク」をクリックし、「値を抽出する」から１つのセルにリストを出力します
「値を抽出する」をクリックした後は次の画像のように、値を区切る記号を指定する画面が出てきますので、そちらで「記号」を指定してください
記号を指定したら、次の画像のようにグループ毎に1つのセルにリストが出力されています
＜まとめ＞
今回は複数の行の内容を、１つのセルに出力する方法を解説しました
ポイントは２つあり、まず1つ目はグループ化において「すべての行」を指定することです
この「すべての行」を指定すると、グループ毎にテーブルが作成されます
グループ毎にテーブルを作成したら、「カスタム列・作成画面」にて各テーブルからリストを作成しておきます
2つ目のポイントは、各リストの内容を「値を抽出する」機能にて１つのセルにリストを出力することです
今回紹介した方法は、それほど使用頻度は高くないと思います
ただ、今回の内容は、M言語の重要概念であるテーブルとリストを体感するのにいい内容だったと思います
特にグループ化において、「すべての行」を指定してグループ毎にテーブルを作成するパターンは色々と応用できそうです！
ぜひ、実際に手を動かしてみて試してみてください
記事を最後まで見て頂き誠にありがとうございました
参考までに今回使用したファイルを添付します
次回は、M関数のText関数について２つ事例を紹介します
複数リストを1対1の関係でうまく組み合わせる
パワークエリの便利機能、というかもはや「不思議」ですらあるのが「1行に対して複数の値を持つリスト」を組み合わせることができる点です
上の画像で言えば、メンバーが田中さんという値ではじまる1行に対して「商品コード」が３つ割り当たっています
但し、
この1行へのリストの割当を複数行うと次のように不都合が起こる場合があります
上の画像には田中さんの商品コード/A1501の行が３つあり、A1501に商品名が３つあることになってしまっています
つまり、割り当てた２つのリスト間が本来あるべき1対1の関係でなく、1対多の関係になってしまっています
今回は、複数のリストを割り当てる場合にきちんと1対1の関係になるように、M言語を使用して調整します！
複数リストの作成
今回使用する元データはこちらの３つの表です
こちらの３つの表をつなげて、次のような表にしたいのです
では、1行に割り当てられるようなリストはそもそもどのように作成されるのかを復習したいと思います
こちらのリストはカスタム列で作成します
今回の場合であれば、上の画像のように「他のクエリ名＋列名]で作成します
別な方法では、前ステップ名＋列名という方法もあります
リストからテーブル作成
記事の冒頭に述べさせて頂いたように、作成したリストをそのまま展開処理を行うと、列間の1対多の関係性がぐちゃぐちゃになります
*次の画像のように１つの商品コードに商品名が３つある
この関係を1対1にするには、カスタム列作成画面でM関数のTable.FromColunsを使用して２つのリストからテーブルを作成します
上のGIF画像だと式が分かりにくいので、詳細は以下をご確認ください
Table.FromColumns({[商品コード],[商品名]})
波括弧：/{}を使用して複数のリストを括るのは忘れないようにしてください！
上のカスタム列を展開すると次のような形になります
きちんと商品コードと商品名が1対1になっています
＜まとめ＞
今回は、複数のリストを1対多ではなく、1対1で展開できるようにしました
ポイントは1つでけあり、M関数：Table.FromColunsを使用して、複数のリストからテーブルを作成する点でした
内容は短いのですが、複数のクエリ間の関係とリスト間の関係性が少しわかりにくいかも知れません
ぜひ、以下のサンプルファイルをダウンロードして関係性を確かめてみてください
クエリの列名をダイナミックに変更する～M関数使用～
今回は、クエリのデータの中味ではなく、列名を別クエリ（別表）からダイナミックに変更する方法を解説したいと思います
上のGIF画像では、画面左にある表の内容を変えたら、右にある表の列名が変更されています
この仕組みは、クエリ上で列名を変更した後、エディタ内に記録された内容にM関数を間接的に組み込む形で作成します
一から、仕組みを構築するわけではないので、最初の印象よりも簡単に作成できます
今回のポイント
入れ子のリスト
仮に、空のクエリに下の数式を入力してみたとしましょう
= {{1,2},{3,4},{5,6}}
すると、次の画像のようにリストが複数できます
今の段階では、この内容が後で出てくるということだけ覚えておいてください
使用するM関数
今回は２つのM関数を使用します
① Table.RenameColumns ➡列名変更
書き方：Table.RenameColumns(テーブル名, リスト名)
②Table.ToColumns ➡テーブルからリスト（入れ子）作成
書き方：Table.ToColumns(テーブル名)
今回のポイントを確認したところで、本格的な解説を始めたいと思います
クエリの列名変更（仮の処理）
まずは、元のデータ/テーブル名：AllDataをテーブル化してエディタで開きます
そして、エディタ内で全ての列名を変更します
すると、数式バーは次のような表示になっています
= Table.RenameColumns(ソース,
{{“年度”, “Year”}, {“製品別”, “Product”}, {“地域”, “Region”}, {“売上金額”, “Sales”}}
)
今回のポイントで紹介したM関数「Table.RenameColumns」が入力されています
そして、第二引数は入れ子になったリストになっています
こちらの第二引数については、後で修正します
列名変換のための入れ子リスト作成
今度は、新旧の列名を変換するためのデータをテーブル化してエディタで開きます
エディタで開いたら、変換タブで行列を入れ替えます
ここから入れ子のリストを、今回のポイントで紹介したM関数「Table.ToColumns」を使用して作成します
こちらは、下の画像の「詳細エディター」で作業を行います
下の図の2行目/転置されたテーブルの下にM関数を追加します
注意点は2点あります
1点は、2行目が最終行でなくなるので、「,」を上の画像の黄色い印のように加えること
2点目は、inの後はletの最終行のリストに置き換えること
この2点です
詳細エディターでの作業を終えたら、エディタは次の画像のようになっています
リストが入れ子になっているのがよく分かります
リスト名は上の画像のように「列名」としておきます
入れ子リストの組み入れ
前述の仮で列名を変えてあるクエリに、新たに作成したリスト「列名」を組み入れます
上の画像の数式バーの部分を抜き出すと、次のようになっています
= Table.RenameColumns(ソース,
{{“年度”, “Year”}, {“製品別”, “Product”}, {“地域”, “Region”}, {“売上金額”, “Sales”}}
)
こちらの第二引数の入れ子リストを、前述のリスト「列名」に入れ替えます
これで、列名のリストの基となるデータを変えれば、ダイナミックに「AllData」クエリの列名が変更されていきます
＜まとめ＞
今回は、入れ子のリストの仕組み、並びにM関数を２つ使用して、クエリの列名をダイナミックに変更する仕組みを解説しました
今回の解説の入り口は、空のクエリで入れ子のリストを作成する点です
こちらはよく復習されておくと、色んな場面で応用が効くと思います
後、記事の途中で詳細エディターを使用した場面がありましたが、少し補足をしておきます
通常、詳細エディターを開くと次のようになっています
上の図のように、ソース➡ソース、追加されたカスタム➡追加されたカスタムという風にテーブル名が次の行に引き継がれていきます
今回もテーブル名をM関数/Table.ToColumnsの()の中に引継ぎました
なお、「,」はletの最終行には付けないので、この点はご注意ください
最後まで記事の最後までお読みくださり、誠にありがとうございました
参考までに今回使用したファイルも添付します
Power Queryって何？12～重複のないリストを一瞬で作成
こんにちは、Excellent仕事術のガッツ鶴岡です
ドロップダウンリストはガッツに頼らず、エクセルの入力作業を効率化するのに欠かせないものです
ところが、ドロップダウンリストの元になる「重複のないリストデータ」を作成すること自体が、実は結構な手間だったりします
Power Queryでは、この重複の無いリストデータの作成を直感的なクリック操作により作成することができます
しかも、並べ替えなども行い易い画面構成になっています
では、ポイントを絞りながらリストデータ作成の解説を行って行きます
１．元データをPower Queryエディターで読み込む
では、まず元データをテーブル化してPower Queryエディターを開きます
２．変換タブの「リストに変換」をクリック
Power Queryエディターが開いたら、変換タブに移動しましょう
すると、「リストに変換」というボタンが見つかるはずです
リストかする列をカーソルで指定したら、この「リストに変換」ボタンをクリックします
すると、次の画像のように選択した1列だけが残っています
３．リストデータのチェック及び再変換
ここから、過去の記事で紹介した「個別の値のカウント」処理を行い、リストに重複がないかどうかを確認します
ちなみに「リストに変換」処理を行うと上の画像のようにもう一つの「変換タブ」へ自動的に移動しています
出力された結果を見ると｢12」です
では、画面右の「適用したステップ」にて「計算された個別カウント」を削除して1つ前の状態に戻りましょう！
既に「重複の排除が済んでいたかどうか？」のチェックを行いしましょう！
この解説で使用しているデータだと20行あるので、重複排除は行われていません
ですので、下の画像の「重複の削除」ボタンで重複を消します
重複の削除を行うと、次のGIF画像のように「いくつの行を削除したか？」については出力されないので、この点は注意が必要です
すると12行になりました
後は、昇順もしくは降順で並び替えを行いましょう！
＜まとめ＞
今回紹介した、Power Queryエディターのリスト化専用の変換タブは、ユーザーのニーズに合わせてよく作成されていると思います
このリスト化に必要な次の３つの処理を、うまく組み合わせて処理が進められるようになっています
・重複削除の有無
・重複削除
・並び替え
ぜひ、ドロップダウンリストを作成する時には、この「リストに変換機能」をうまく活用して省力化につなげて頂けると嬉しいです
長文に最後までお付き合い頂き、誠にありがとうございました
新型関数のおすすめ記事～スピルを体験しよう～
新たなに加わった新型関数は使い勝手がとてもいいです
ピボットテーブルの集計データを無理に使い回さなくても、集計が簡単に行えます
１つのセルに関数を入れたら、値があふれでるスピル体験をしていない人はぜひ早めに体験してみましょう！
これまで五月雨式に記事を書きましたが、以下にまとめて紹介します
１．FILETER関数⇒特定の条件のデータを抽出する関数
１セルに１FILTER関数を入力すれば、それだけで複数のデータを抽出してくれます
FILTER関数を使用して、検索付き機能付きののリストを作成します
テーブル機能と連携して効率よくデータを抽出する方法を紹介しています
２．XLOOKUP関数⇒VLOOKUP関数の進化型関数
エクセル分析を行う人で、2軸でのランク付けを楽に行いたい人におすすめです
以下のGIFでは、ⅰ売上とⅱ前期比の２つの軸でランク付けを、自動で行っています
XLOOKUP関数をアニメ付きのエクセルを使用し、XLOOKUP関数が集計されるまでの複数の動きを体感できるようにします
３．SORT関数⇒データを並べ替えて出力する関数です
①SORT関数によるエクセルデータの並び替え~列の並び替えも可
エクセルではこれまで、列方向のフィルターや並び替えが弱い傾向はありました
列フィルターを「列の非表示」を通じて実現する方法はこれまでもありましたが、列方向の並び替え機能はあまり使い勝手はよくありませんでした
今回紹介するSORT関数は、そんなこれまでのエクセルの弱点を補ってくれる関数です
４．UNIQUE関数⇒重複を排除してリスト化する関数
UNIQUE関数を使用してリストを簡単に作成する裏技も紹介します
今回は以上です
ではスピル体験をぜひ楽しんでください！
