なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

Power Query:VLOOKUP的な検索をする(アイテムアクセス)

Poewr Queryで2つのクエリを紐づけする場合は「クエリのマージ」を使うのが一般的。そっちのほうが処理負担も少ない。

売上データの「ID」を使って、名前マスタの「名前」を結合する(ついでに集計する)場合、

    ソース = 売上データ,
    クエリのマージ = Table.NestedJoin(
        ソース, {"ID"}, 名前マスタ, {"ID"}, "マージ"
    ),
    列の展開 = Table.ExpandTableColumn(
        クエリのマージ, "マージ", {"名前"}
    ),
    値の置換 = Table.ReplaceValue(
        列の展開, null, "【マスタ未登録】",
        Replacer.ReplaceValue,{"名前"}
    ),
    行のグループ化 = Table.Group(
        値の置換, {"ID", "名前"}, 
        {"金額計", each List.Sum([金額] & {0}), type number}
    )

ただ VLOOKUPとか XLOOKUPとか、INDEX/MATCHみたいに直接検索値を指定して値を検索できないのかと思う向きもあるだろう。そういう時はアイテムアクセスを使えばいい。

    ソース = 売上データ,
    名前列の追加 = Table.AddColumn(
        ソース, "名前", each
            try 名前マスタ{[ID = [ID]]}[名前]
            otherwise "【マスタ未登録】",
        type text
    ),
    行のグループ化 = Table.Group(
        名前列の追加, {"ID", "名前"},
        {"金額計", each List.Sum([金額] & {0}), type number}
    )

「こりゃ便利!」と思った人は、データが何万件もあるテーブルでこれをやってみるといい。激重くなること請け合いである。いちいちデータを読み込んでから検索するので当然処理は重くなる。データ量が少なければ特に負担はないだろうから、横着技としては重宝するかもしれない。

因みに「複数条件の時はどうするの?」といわれそうなので一応書いておくと、

    クエリ{[条件1="A", 条件2="B"]}[列名]

のように「,」で連結すれば AND条件で複数条件検索できる。OR条件には対応しない。というか一致するものが複数あるとエラーになるのでご注意。「try/otherwise」との組み合わせが必須かもしれない。

近似値検索がしたい時は別のアプローチが必要になる。