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

XLOOKUP関数を使用した複数ドロップダウンリストでの検索

複数ドロップダウンリストとの連動による検索

 今回は、新関数の特徴を活かして「複数のドロップダウンリストと連動した検索作業」をサクッと行う方法を解説します

 この検索方法が行えると、上のGIF画像にあるようなクロス表からの検索がグッと楽になります

「河合さんの数学の点数は?」「中島さんの国語の点数は?」

こんな検索もドロップダウンリストを2回操作するだけで行えます! 

従来であれば、複数の種類の関数を組み合わせなければこの検索は行えませんでした

 今回は、XLOOKUP関数を一度だけ組み合わせるだけで作成する方法を解説します

尚、XLOOKUP関数の基本的な使い方に関しては、過去の記事をご確認ください

今回解説に使用するデータ

今回、解説に使用するデータは、生徒毎の3科目の点数表です

 この表から、下の図の黄色に印を付けた箇所からリストの選択を行うだけで上の表から該当の点数を検索できるようにします

今回のポイント

今回は新関数の特徴を最大限に生かします

新関数の特徴は、1つのセルへの入力で複数の値を抽出できる点です

XLOOKUP関数も新関数ですので、複数の値を抽出できます

例として今回のデータを使用して、XLOOKUP関数を「複数の値」を抽出するように2つ設定してみます

①列方向に抽出

下の図をご覧下さい

 XLOOKUP関数の3番目の引数/戻り列を、1列だけでなく「C列~E列」指定しています

 戻り列を複数指定すると、下のGIF画像のように検索値の「伊藤さん」の全ての点数を抽出します

②行方向に抽出

 XLOOKUP関数のメリットの一つは、従来のVLOOKUP関数では検索できなかった方向にも検索が行えることです

①の列方向に抽出では、「伊藤さん」の点数を全ての科目について抽出しました

今回は、全ての生徒の数学の点数を抽出してみます

今度は、3番目の引数で「複数」を指定します

すると下の図のように、全ての生徒の数学の点数を抽出します

XLOOKUP関数の組み合わせ

では、前述の①と②を組み合わせて、検索を行う関数を完成させます

 列方向に抽出する①のXLOOKUP関数の3番目の引数に、②の行方向に抽出するXLOOKUP関数を組み合わせます

このように「行列」双方向のXLOOKUP関数を組み合わせるだけで、「 複数のドロップダウンリストと連動した検索作業 」を行う関数の入力が完成です

<まとめ>

 今回は、行列双方向に抽出するXLOOKUP関数を組み合わせて「複数のドロップダウンリストと連動した検索作業」を行う関数を解説しました

 今回解説した方法はXLOOKUP関数の2つの特徴、ⅰ)複数の値を出力、ⅱ)行列双方向で検索化、を組み合わせたものといえます

XLOOKUP関数にはまだまだ紹介していない魅力があります

また次回、XLOOKUP関数の魅力について解説します!

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 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

...


にほんブログ村