こんにちは。引き続きSpreadsheets/Excel Advent Calendar 2020のやつです。
昨日も私でした。度々失礼します。
パンチライン第二弾、今度はもうちょっと使いみちがありそうなやつをヤッていきます。
VLOOKをVLOOKしたいとき
...って言って伝わりますか？詳しく説明する気はないけど伝わってほしい。VLOOKIFSみたいなことがしたいわけです。
こういうパンチラインで解決できるんじゃないかというのが今回のお話です。
=VLOOKUP(A2:A,QUERY(シート1!$A:$C,"Where B ='"&$B2:B&"'"),2,0)
お題
2つのキーでVLOOKしたいとき、ないですか？僕は息を吸って吐くよりも多くあります。いわばV柱です。
例えば以下のような、日付 x 商品別の売上データである売上シートがあるとします。
3万円で売れているゴリラ、ラッコの五倍の価格で取引されるリンゴが爆誕していますが無視してください。
売上シート↓
| 日付 | 商品 | 売上金額 |
|---|---|---|
| 2020-12-01 | りんご | ¥50,000 |
| 2020-12-01 | ごりら | ¥30,000 |
| 2020-12-02 | りんご | ¥50,000 |
| 2020-12-02 | らっこ | ¥10,000 |
| 2020-12-03 | りんご | ¥50,000 |
次に、同様に日付と商品のカラムをもつ在庫数データである在庫シートがあるとします。
在庫シート↓
| 日付 | 商品 | 在庫数 | ★売上 |
|---|---|---|---|
| 2020-12-01 | りんご | 1,000 | ? |
| 2020-12-02 | らっこ | 2,000 | ? |
| 2020-12-03 | ごりら | 500 | ? |
（ゴリラ500頭もおるん？）
さて、この在庫シート各行に該当する売上シートの売上金額をD列に出力したいとします。つまり、①日付と②商品という2つのキーで名寄せを行いたいわけです。
普通にやるなら：SUMIFS
素朴な解としては以下のようにSUMIFSを用いることでしょう。
=SUMIFS(売上シート!C:C, 売上シート!A:A,在庫シート!A2, 売上シート!B:B,在庫シート!B2)
しかしながら問題があります。SUMには伝家の宝刀 ARRAYFORMULA が使えないため、在庫シートの行数分関数をコピーしておく必要があります。
この手のシートは行数けっこうイカツいケースがほとんどだと思うのでなるべく手作業は避けたい。あと SUMIFS は単純に重いのでなるべく使いたくないというのもあります。
普通にやるなら2：結合キーでVLOOK
次の手段としては複数の列をかけあわせたキー用の列をあらかじめ作っておき、それをキーにしてVLOOKUPする方法でしょうか。
ここでいうと双方のシートに日付と商品を結合した文字列（=A2&"_"&B2で2020-12-01_りんごというキーを作る的な）を用意しておき、それをキーにVLOOKUPするようなイメージです。
これもこれで非常に面倒。VLOOKUPのためだけの列を作るのも面倒だし、取り回しを考えると元あるデータの左端にキー列を作っておきたいので、元データがA1からはじまらない問題もあります。
ということでパンチライン。要はVLOOKUPの参照範囲を先にQUERYで用意しておくというものです。
=VLOOKUP(A2:A,QUERY(シート1!$A:$C,"Where B ='"&$B2:B&"'"),2,0)
※ 前提としてARRAYFORMULAの後にくっつけることを想定しているので、範囲は見出し行を除く2行目を指定しています。
全文書くとこんな感じか。
=ARRAYFORMULA(IF(a2:a="","",VLOOKUP(A2:A,QUERY(シート1!$A:$C,"Where B ='"&$B2:B&"'"),2,0)
内訳解説
内訳はしごく単純で、QUERY と VLOOKUP でのみ構成されています。
売上シートをWhere ②でQuery
QUERY(シート1!$A:$C,"Where B ='"&$B2:B&"'")
まずは売上シートをQUERYするわけですが、Where句で在庫シートのB列を指定します。これによってまずB列 = ②商品で絞り込まれたテーブルが生まれることになるようなイメージです。
例えば売上シートのB2がりんごであれば、在庫シートのB列がりんごオンリーのテーブルがここでは生成されている（シートには出てないけど）ということです。
Queryされた(イマジナリー)テーブルに対し①でVLOOKUP
で、そのテーブルを対象範囲として今度はA列 = ①日付 でVLOOKUPをかけます。
=VLOOKUP(A2:A,QUERY(省略),2,0)
結果としてA列： ①日付 と B列：②商品 のAND条件でVLOOKUPがかけられるという次第です。かしこい！！！
12/22 追記
サンプルを作ったよ。 docs.google.com
いかがだよ
非常に良かったですね。今日のパンチラインは昨日のより分かりやすかった気がする。週末に書くブログは労働という枷から開放されているだけあって冴えわたっていますね。
次回はmiyayuki777さん、『誕生日なのでせっかくなのでなにか書きます』とのことでした。めでたい！！！！！！誕生日といえばExcel/SpreadSheet、そう決まっていますよね。
ということでみなさま良いお年を :pray:
