タグ別アーカイブ: リスト

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重にして[[連番]]にします

波括弧 / {}による参照

波括弧 / {}の中には数字が入ります

波括弧 / {} に入れる最小の数字は0です

0から始まるのは、M言語が過去記事で紹介したように0をベースとしているからです

では、角括弧 / []と同じデータを使用して、波括弧 / {}の中に数字を入れてみましょう!

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

まずは波括弧 / {}に 0をいれてみます

➡ = 変更された型{0}

すると、1行目の「レコード」が参照されます

ちなみに上の画像は、レコードツールという表示になっています

次に、波括弧 / {}に 1をいれてみます

➡ = 変更された型{1}

こんどは2行目のレコードが参照されます

角括弧と波括弧の組み合わせによる参照

角括弧と波括弧の組み合わせでは、2種類の参照方法を紹介します

1つ目は、角括弧と波括弧を並列で組み合わせます

角括弧と波括弧の組み合わせにより、「1番目のレコード」の「連番列」の値を抽出します

= 変更された型{0} [連番]

こちらは画面上をみると、リストでもレコードでもなく、値が参照されているのがわかります

こちらは、ドリルダウンを行った時と同じ内容です

2つ目は、波括弧 / {}の中に角括弧 / []を入れます

角括弧 / []の中には、次の画像のように「列名=列の中味」を入れ、列の中味を通じてレコードを参照します

= 変更された型{[連番=1]}

すると、1行目のレコードが抽出されます

<まとめ>

 今回は、角括弧 / []と波括弧 / {}を使用して、エディタ内で参照作業を4つ行いました

1.角括弧 ➡リストを参照

2.波括弧➡レコードを参照

3.角括弧と波括弧の組み合わせ①➡値を参照

4.波括弧の中に角括弧を入力➡レコードを参照

 記事の冒頭で、Power Queryの「データ構造化」には4つの概念(値、リスト、レコード、テーブル)があると紹介しました

実際に手を動かして、上記の4つの作業を行ってみて、4つの概念について肌で感じて頂けると幸いです

 4つの概念を肌で感じた時には、Power Queryへの理解が一段と深まっているはずです

今回は、以上です

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

次回は、空のクエリから3種類の括弧を使用してリスト、レコード、そしてテーブルまで作成します

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

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

M言語に慣れる_7回目~空のクエリからテーブル作成~

【M言語はむずかしくない!】今回は、空のクエリから、3種類の括弧を使ってテーブルを作成します。テーブルを作成した後は、M関数も作成を行います!その際、M関数を使いやすくする方法についてもあわせて解説します!!

前回は2種類の括弧を使い、エディタ内で各種参照を行いました

角括弧 / [] ➡ リスト、列の抽出

波括弧 / {} ➡ レコードの抽出

前回で、M言語における2種類の括弧の役割がおぼろげながら見えてきたと思います

 今回、3種類の括弧を使ってテーブルを作成した後は、過去に紹介した方法とは別の方法でM関数を参照する方法についても解説します!

3種類の括弧に慣れると、M言語がグンと理解し易くなりますし、応用も効きます!

ぜひ、実際に手を動かして取り組んでみてください

まずは、空のクエリを作成するところから行います

なお、

他のクエリも後程参照したいので、演習を行う際には、既存クエリが存在するファイルを使用してください

目次

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

リスト作成

レコード作成

テーブル作成

M関数の用法を参照・作成/列名変更

<まとめ>

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

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

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

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

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

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

リスト作成

リストは波括弧 / {}を使用して作成します

①値が1個

では、数式バーに={“a”}と入力してみます

リストができ、値がリストに1つ入りました

②値が2個

では、={1,2}と入力してみます

リストができ、値がリストに2つ入りました

③値が10個

こちらは、実際に使う機会がないとは思いますが、エディタ内でデータの構造化が行われていることを肌で感じてもらうために紹介します

={1..10}と入力すると、1から10まで自動的に値が作成されます

④リストが2個:各値1個

波括弧の中にコンマ / ,を介して2つ波括弧を入れると、リストが2個できます

入力はこちらのように入力します

= {{“a”},{“b”}}

上の画像の上下どちらかの”List”をクリックすると、適用したステップ画面に「ナビゲーション」が追加されます

上のGIF画像を見ると、ファイルやフォルダからデータを取得する時に出てくる「ナビゲーター」画面の意味合いが良く分かると思います

⑤リストが2個:各値2個

入力はこちらのように入力します

= {{“a”,”b”},{1,2}}

上の画像の、下のリストをクリックすると、下の画像のようにリスト内容が表示されます

レコード作成

角括弧 / []を使用して作成します

①値1

こちらは=[見出し1=値1]と入力します

①値2

こちらは=[見出し1=値1, 見出し2=値2 ]と入力します

レコードは行方向で通常並んでいますが、今回のように数式バーに直接入力した時は、表示のされ方が列方向になります

テーブル作成

テーブルを作成する場合には、これまでの解説で登場しなかった丸括弧 / ()も使用します

そして、丸括弧のまえに=#tableをつけてます

=#tableとコードを書くことにより、丸括弧 / ()内の内容がテーブルになります

ちなみに#の後に続く、tは小文字のtですので、その点は注意しておきましょう!

①1列:値1

こちらは、=#table({列名1},{{値1}})と入力します

②1列:各列値2

こちらは、= #table({列名1},{{値1},{値2}})と入力します

③2列:各列値2

こちらは、

= #table({列名ⅰ,列名ⅱ},{{ⅰ値1,ⅰ値1},{ⅱ値2,ⅱ値2}})

波括弧の組み合わせが多くなので、上の内容をコピーして修正してみる方法でもいいかもしれません

M関数の用法を参照・作成/列名変更

M関数参照

では、エディタ画面の左から他のクエリを開きます

他のクエリが開いたら、どの列でもいいので、列名変更を行ってみます

すると、数式バーには「Table.RenameColumns」というM関数が表示されているはずです

表示を確認したら、M関数の丸括弧の部分を、下のGIF画像のように全部削除してください

すると、下の画像のようにTable.RenameColumns関数の解説が表示されます

上の画像の解説画面を下にスクロールしていくと、使用例が出ています

これは「CustomerNum」という列名を「CustomerID」に変えるという使用例です

上の画像の使用例には、黄色の印が2か所あります

黄色の箇所の間には、もう一つM関数が入っています

こちらは、通常のテーブルの内容に置き換えておきます

黄色の間の箇所を、置き換えた内容が以下です

Table.RenameColumns(
テーブル名,
{列名変更前, 列名変更後}
)

こちらの内容をコピーして活用し、先ほど空のクエリにて作成したテーブルの列名を変更しましょう!

M関数作成

まずは、「Table.RenameColumns(」の部分を、空クエリで作成したテーブルの前にコピーします

そして、残りの箇所を上の画像の後ろにコピーします

コピーする時には、3行に分かれている内容を1行に集約しておいてください

,
{列名変更前, 列名変更後}
)

コピーした後の画像がこちらです

こちらを既存の列名/氏名と新列名/名前に置き換えます

置き換えた後は、下の画像のように「氏名」の列名の列が「名前」に変更されています

<まとめ>

今回は、大きく分けて2つのことを行いました

 1つ目は、3種類の括弧を使用して、空のクエリにて「リスト」「レコード」「テーブル」を作成しました

 2つ目は、空のクエリで作成した「テーブル」にて列名を変更するM関数を使用しました

 M関数を使用する前に、他クエリで列名変更を行い、記録されたM関数から解説を表示しました

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

そして、こちらの使用例をカスタマイズして、M関数を使用しました

 今回は2回に分けて、3種類の括弧について解説しましたが、括弧の意味・役割をおぼろげながらでも理解できていると、紹介した使用例なども活用しやすくなります

ぜひ、今回の解説を機に3種類の括弧に強くなっていきましょう!

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

次回は「each」コードについて慣れていきましょう!

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

にほんブログ村

UNIQUE関数xFILTER関数から重複無リスト抽出~条件一致~

 FILTER関数UNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります!

 今回は FILTER関数UNIQUE関数 の組み合わせにより、ある表から条件に合う重複が無いリストを作成する方法を解説します

そして、最後はUNIQUE関数xFILTER関数の組み合わせを応用した内容も解説します

条件に合う重複無リスト作成

今回は、下の画像の左にある表から「70点未満の得点を一度でも取った人」リストを重複が無いように作成します

 抽出元の表には、田中さんや佐藤さんのように同じ名前の人のデータが複数含まれています

まずは設定条件/<70に沿ったFILTER関数を作成してみましょう

FILTER関数の書き方は次の通りです

=FILTER(抽出範囲,条件)

上の書き方を今回のやりたい事に当てはめると次の画像の通りになります

 上の画像では、まだFILTER関数しか入力していないので、リストに佐藤さんの名前が複数回登場しています

ですので、FILTER関数にUNIQUE関数を下の画像のように組み合わせます

これで、佐藤さんの登場は1回のみになりました

応用編

仮に、下の画像のように指定範囲を空欄にした場合には、下の画像のように「0」が関数の抽出結果に含まれてしまいます

この時には、FILTER関数の設定条件に「AND条件」を加えます

このANDで追加される条件は「>0」です

 この「AND条件」をFILTER関数に設定する場合には下の画像の黄色で囲まれた箇所のように「*」を使います

これで不必要な0が消えました

<まとめ>

 今回は、UNIQUE関数とFILTER関数を組み合わせて、条件に合った、重複が無いリストを作成しました

 この組み合わせを覚えると、今までピボットテーブル経由で取得していたデータが関数だけで取得できるのでとても便利です

ぜひ有効活用していきましょう!

 後、FILTER関数の設定条件に「*」を使用してAND条件で抽出する方法もぜひ覚えておきましょう

最後に、「+」を使用するとOR条件にて抽出できる旨も付け加えておきます

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

にほんブログ村

UNIQUE関数~重複排除~

  今回は、1つのセルに関数を入力するだけで重複排除したリストが取得できる、「UNIIQUE関数」について解説します

尚、今回の解説で使用するデータは次の画像になります

上の画像のデータは、既にテーブル化してあります

ですので、関数の引数(中味)の指定については、過去の記事で解説した「テーブルとの連携」により指定する方法で解説します

詳細を知りたい方は過去の記事をご確認お願いします

1つの列での重複排除

このUNIQUE関数の書き方は至ってシンプルです

対象となるデータ範囲を指定するだけです

=UNIQUE(対象範囲)

今回はテーブルと連携するので、下のGIF画像のように対象範囲を指定します

上のGIF画像では、対象範囲を指定する際に、年度の列にてが出ていることにご注目ください

複数の列での重複排除

UNIQUE関数で複数の列を対象範囲に指定すると、指定された列の組み合わせの中から、重複しない組み合わせを出力します

行方向での重複排除

UQNIQUE関数の2つの引数を「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関数の醍醐味は他の関数との組み合わせです

次回からは他関数との組み合わせについて解説を行って行きます!

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

にほんブログ村

【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へ

にほんブログ村

複数リストを1対1の関係でうまく組み合わせる

 パワークエリの便利機能、というかもはや「不思議」ですらあるのが「1行に対して複数の値を持つリスト」を組み合わせることができる点です

上の画像で言えば、メンバーが田中さんという値ではじまる1行に対して「商品コード」が3つ割り当たっています

但し

この1行へのリストの割当を複数行うと次のように不都合が起こる場合があります

上の画像には田中さんの商品コード/A1501の行が3つあり、A1501に商品名が3つあることになってしまっています

つまり、割り当てた2つのリスト間が本来あるべき1対1の関係でなく、1対多の関係になってしまっています

今回は、複数のリストを割り当てる場合にきちんと1対1の関係になるように、M言語を使用して調整します!

複数リストの作成

今回使用する元データはこちらの3つの表です

こちらの3つの表をつなげて、次のような表にしたいのです

では、1行に割り当てられるようなリストはそもそもどのように作成されるのかを復習したいと思います

こちらのリストはカスタム列で作成します

今回の場合であれば、上の画像のように「他のクエリ名+列名]で作成します

別な方法では、前ステップ名+列名という方法もあります

リストからテーブル作成

記事の冒頭に述べさせて頂いたように、作成したリストをそのまま展開処理を行うと、列間の1対多の関係性がぐちゃぐちゃになります

*次の画像のように1つの商品コードに商品名が3つある

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

この関係を1対1にするには、カスタム列作成画面でM関数のTable.FromColunsを使用して2つのリストからテーブルを作成します

上のGIF画像だと式が分かりにくいので、詳細は以下をご確認ください

Table.FromColumns({[商品コード],[商品名]})

波括弧:/{}を使用して複数のリストを括るのは忘れないようにしてください!

上のカスタム列を展開すると次のような形になります

きちんと商品コードと商品名が1対1になっています

<まとめ>

今回は、複数のリストを1対多ではなく、1対1で展開できるようにしました

ポイントは1つでけあり、M関数:Table.FromColunsを使用して、複数のリストからテーブルを作成する点でした

内容は短いのですが、複数のクエリ間の関係とリスト間の関係性が少しわかりにくいかも知れません

ぜひ、以下のサンプルファイルをダウンロードして関係性を確かめてみてください

アイコン

リストの重複 22.76 KB 4 downloads

...


にほんブログ村

クエリの列名をダイナミックに変更する~M関数使用~

 今回は、クエリのデータの中味ではなく、列名を別クエリ(別表)からダイナミックに変更する方法を解説したいと思います

 上のGIF画像では、画面左にある表の内容を変えたら、右にある列名が変更されています

 この仕組みは、クエリ上で列名を変更した後、エディタ内に記録された内容にM関数を間接的に組み込む形で作成します

 一から、仕組みを構築するわけではないので、最初の印象よりも簡単に作成できます

今回のポイント

入れ子のリスト

仮に、空のクエリに下の数式を入力してみたとしましょう

= {{1,2},{3,4},{5,6}}

すると、次の画像のようにリストが複数できます

今の段階では、この内容が後で出てくるということだけ覚えておいてください

使用するM関数

今回は2つの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点です

詳細エディターでの作業を終えたら、エディタは次の画像のようになっています

リストが入れ子になっているのがよく分かります

リスト名は上の画像のように「列名」としておきます

入れ子リストの組み入れ

前述の仮で列名を変えてあるクエリに、新たに作成したリスト「列名」を組み入れます

AllData

上の画像の数式バーの部分を抜き出すと、次のようになっています

= Table.RenameColumns(ソース,

{{“年度”, “Year”}, {“製品別”, “Product”}, {“地域”, “Region”}, {“売上金額”, “Sales”}}

)

こちらの第二引数の入れ子リストを、前述のリスト「列名」に入れ替えます

これで、列名のリストの基となるデータを変えれば、ダイナミックに「AllData」クエリの列名が変更されていきます

<まとめ>

 今回は、入れ子のリストの仕組み、並びにM関数を2つ使用して、クエリの列名をダイナミックに変更する仕組みを解説しました

今回の解説の入り口は、空のクエリで入れ子のリストを作成する点です

こちらはよく復習されておくと、色んな場面で応用が効くと思います

後、記事の途中で詳細エディターを使用した場面がありましたが、少し補足をしておきます

通常、詳細エディターを開くと次のようになっています

 上の図のように、ソース➡ソース、追加されたカスタム➡追加されたカスタムという風にテーブル名が次の行に引き継がれていきます

今回もテーブル名をM関数/Table.ToColumnsの()の中に引継ぎました

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

なお、「,」はletの最終行には付けないので、この点はご注意ください

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

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

アイコン

Dynamic_Column_Name 24.30 KB 9 downloads

...

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

にほんブログ村

Power Queryって何?12~重複のないリストを一瞬で作成

こんにちは、Excellent仕事術ガッツ鶴岡です

 ドロップダウンリストはガッツに頼らず、エクセルの入力作業を効率化するのに欠かせないものです

 ところが、ドロップダウンリストの元になる「重複のないリストデータ」を作成すること自体が、実は結構な手間だったりします

 Power Queryでは、この重複の無いリストデータの作成を直感的なクリック操作により作成することができます

 しかも、並べ替えなども行い易い画面構成になっています

 では、ポイントを絞りながらリストデータ作成の解説を行って行きます

1.元データをPower Queryエディターで読み込む

では、まず元データをテーブル化してPower Queryエディターを開きます

テーブル化

2.変換タブの「リストに変換」をクリック

Power Queryエディターが開いたら、変換タブに移動しましょう

すると、「リストに変換」というボタンが見つかるはずです

リストかする列をカーソルで指定したら、この「リストに変換」ボタンをクリックします

リストに変換

すると、次の画像のように選択した1列だけが残っています

3.リストデータのチェック及び再変換

ここから、過去の記事で紹介した「個別の値のカウント」処理を行い、リストに重複がないかどうかを確認します

ちなみに「リストに変換」処理を行うと上の画像のようにもう一つの「変換タブ」へ自動的に移動しています

出力された結果を見ると「12」です

では、画面右の「適用したステップ」にて「計算された個別カウント」を削除して1つ前の状態に戻りましょう!

既に「重複の排除が済んでいたかどうか?」のチェックを行いしましょう!

この解説で使用しているデータだと20行あるので、重複排除は行われていません

ですので、下の画像の「重複の削除」ボタンで重複を消します

重複の削除を行うと、次のGIF画像のように「いくつの行を削除したか?」については出力されないので、この点は注意が必要です

すると12行になりました

後は、昇順もしくは降順で並び替えを行いましょう!

<まとめ>

 今回紹介した、Power Queryエディターのリスト化専用の変換タブは、ユーザーのニーズに合わせてよく作成されていると思います

 このリスト化に必要な次の3つの処理を、うまく組み合わせて処理が進められるようになっています

・重複削除の有無

・重複削除

・並び替え

 ぜひ、ドロップダウンリストを作成する時には、この「リストに変換機能」をうまく活用して省力化につなげて頂けると嬉しいです

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

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

にほんブログ村

新型関数のおすすめ記事~スピルを体験しよう~

新たなに加わった新型関数は使い勝手がとてもいいです

ピボットテーブルの集計データを無理に使い回さなくても、集計が簡単に行えます

1つのセルに関数を入れたら、値があふれでるスピル体験をしていない人はぜひ早めに体験してみましょう!

これまで五月雨式に記事を書きましたが、以下にまとめて紹介します

1.FILETER関数⇒特定の条件のデータを抽出する関数

1セルに1FILTER関数を入力すれば、それだけで複数のデータを抽出してくれます

エクセルなのに・検索付きリスト_1/2

FILTER関数を使用して、検索付き機能付きののリストを作成します

Filter関数&テーブル機能でのデータ検索

テーブル機能と連携して効率よくデータを抽出する方法を紹介しています

2.XLOOKUP関数⇒VLOOKUP関数の進化型関数

XxXLookup関数で集計上手になる!

エクセル分析を行う人で、2軸でのランク付けを楽に行いたい人におすすめです

以下のGIFでは、ⅰ売上とⅱ前期比の2つの軸でランク付けを、自動で行っています

XLOOKUP関数を体で覚える

XLOOKUP関数をアニメ付きのエクセルを使用し、XLOOKUP関数が集計されるまでの複数の動きを体感できるようにします

3.SORT関数⇒データを並べ替えて出力する関数です

SORT関数によるエクセルデータの並び替え~列の並び替えも可

エクセルではこれまで、列方向のフィルターや並び替えが弱い傾向はありました

列フィルターを「列の非表示」を通じて実現する方法はこれまでもありましたが、列方向の並び替え機能はあまり使い勝手はよくありませんでした

今回紹介するSORT関数は、そんなこれまでのエクセルの弱点を補ってくれる関数です

4.UNIQUE関数⇒重複を排除してリスト化する関数

リストをUNIQUE関数でより便利に

UNIQUE関数を使用してリストを簡単に作成する裏技も紹介します

今回は以上です

ではスピル体験をぜひ楽しんでください!

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

にほんブログ村