VLOOKUPを使っていたころ、店舗と商品の2つの条件で検索したいときは、A列に「店舗コード_商品コード」の検索キーを用意して検索していました。
XLOOKUPなら、作業列を作らなくても、式の中で条件をつなげて検索できます。私も、自分用のファイルでちょっと調べたいときには使っています。ただ、データが多いと遅くなるので、職場に公開するファイルでは使っていません。XLOOKUPでも、検索キーの列を用意しています。
この記事では、次の2つの方法と、その使い分けを紹介します。
- 作業列を使わずに、式の中で条件をつなげる方法
- 検索キーの列を用意する方法
なお、この記事の表は説明のために作ったもので、コードや在庫数は架空です。
例として使う表
「在庫データ」シートに、店舗コード・商品コード・在庫数が縦に並んでいます。店舗コードは3桁、商品コードは4桁です。
| A | B | C | |
|---|---|---|---|
| 1 | 店舗コード | 商品コード | 在庫数 |
| 2 | 101 | 1001 | 12 |
| 3 | 101 | 1002 | 5 |
| 4 | 102 | 1001 | 8 |
| 5 | 102 | 1002 | 0 |
| 6 | 103 | 1001 | 20 |
| 7 | 103 | 1002 | 3 |
「在庫検索」シートのA2に店舗コード、B2に商品コードを入力して、C2に在庫数を出します。
作業列を使わずに検索する
C2に次の式を入れます。
=XLOOKUP(A2&B2,在庫データ!$A$2:$A$7&在庫データ!$B$2:$B$7,在庫データ!$C$2:$C$7,"該当なし")検索値は、A2とB2を&でつなげたものです。店舗コード102、商品コード1001なら「1021001」になります。
検索範囲も、店舗コードの列と商品コードの列を&でつなげています。こうすると、各行の店舗コードと商品コードをつなげた一覧の中から探してくれます。
店舗コード102、商品コード1001なら、在庫数の8が返ってきます。見つからないときは「該当なし」と表示されます。
VLOOKUPの検索範囲は表の範囲をそのまま指定するので、このように式の中で列をつなげることはできません。作業列なしで複数条件を検索できるのは、XLOOKUPならではの使い方です。
区切りを入れないときの注意
この例は、店舗コードも商品コードも桁数がそろっているので、つなげても別の組み合わせと同じになることはありません。
桁数がそろっていないコードの場合は注意が必要です。例えば、店舗コード「1」と商品コード「23」、店舗コード「12」と商品コード「3」は、どちらもつなげると「123」になり、区別できません。このような場合は、A2&”_”&B2 のように、間に区切りを入れてください。
データが多いと遅くなる
作業列がいらないので便利ですが、データが多いと遅くなります。
30万行のダミーデータに対して、この形のXLOOKUPを1000行入れて試したところ、はっきり分かるほど遅くなりました。
そのため私は、この方法は自分用のファイルで、少しだけ調べたいときに使っています。
検索キーの列を用意する
職場に公開するファイルでは、在庫データの側に検索キーの列を用意しています。
在庫データのD列に、次の式を入れて下にコピーします。
=A2&"_"&B2| A | B | C | D | |
|---|---|---|---|---|
| 1 | 店舗コード | 商品コード | 在庫数 | 検索キー |
| 2 | 101 | 1001 | 12 | 101_1001 |
| 3 | 101 | 1002 | 5 | 101_1002 |
| 4 | 102 | 1001 | 8 | 102_1001 |
| 5 | 102 | 1002 | 0 | 102_1002 |
| 6 | 103 | 1001 | 20 | 103_1001 |
| 7 | 103 | 1002 | 3 | 103_1002 |
検索キーには、店舗コードと商品コードの間に「_」を入れています。見たときに、どこまでが店舗コードか分かりやすくするためです。
在庫検索シートのC2は、次の式にします。
=XLOOKUP(A2&"_"&B2,在庫データ!$D$2:$D$7,在庫データ!$C$2:$C$7,"該当なし")検索値も、検索キーと同じように「_」を入れてつなげます。
VLOOKUPのときは、検索する列を表の一番左に置く必要があったので、A列に検索キーを用意していました。XLOOKUPは検索する列が左になくてもよいので、この例のように、表の右端に検索キーの列を足すだけで済みます。
パワークエリで検索キーを作る
データをパワークエリで取り込んでいる場合は、パワークエリで検索キーの列を作ることもできます。
私は、パワークエリで店舗コードと商品コードを「_」でつなげた列を作り、検索キーと値の列だけを残して、他の列は削除しています。読み込むデータが減るので、ファイルも軽くなります。「_」を入れておくと、フィルターで絞り込んで確認するときにも見やすくなります。
パワークエリの使い方は、別の記事で紹介する予定です。
まとめ
- XLOOKUPなら、検索値と検索範囲を&でつなげて、作業列なしで複数条件を検索できる
- ただし、データが多いと遅くなるので、自分用のちょっとした検索向き
- 職場に公開するファイルでは、検索キーの列を用意する。XLOOKUPなら表の右端に足すだけでよい
XLOOKUPのその他の使い方は、こちらの記事で紹介しています。
