lookup系の関数では、検索範囲の列について、
列がズレたりした場合、
通常の書き方ですと、毎回修正が必要になってきます。
今回は、列がズレても修正する必要がない
つまり、列を自動で求めてくれる書き方を紹介します。
- 今回はhlookupについてはあまり使用用途がないこと、
vlookup関数とほぼ同じ書き方で出来ることから省略します。
見出し名を参照して列数を求めています。
ですので、見出し名が変更されてしまった場合は別途修正をする必要があります。
vlookup関数の場合
数式としては、
VLOOKUP(検索値,範囲,MATCH(見出し名,範囲の見出し行,0),false)
となります。
実例を示します。
以下の表で商品名から種類、価格を表示させる場合を考えます。
通常は列数を指定することで求めますが、
種類：VLOOKUP(\$G\$5,B:D,2,FALSE)
価格：VLOOKUP(\$G\$5,B:D,3,FALSE)
match関数を使うことで、列数が自動で求まります。
種類：VLOOKUP(\$G\$5,B:D,MATCH(F6,\$B\$5:\$D\$5,0),FALSE)
価格：VLOOKUP(\$G\$5,B:D,MATCH(F7,\$B\$5:\$D\$5,0),FALSE)
MATCH関数でリストの見出し(\$B\$5:\$D\$5)に対し、
種類、価格がB列から数えて何列目にあるかを求めています。
これにより、たとえ列数がズレたとしても見出し名が変更されない限りは
vlookupの式を変更することなく求めることができます。
なお、今回は範囲をB:Dと記載していましたが、
B5:E9と表の範囲のみを記載する人もいるかと思います。
例：VLOOKUP(\$G\$5,\$B\$5:\$D\$5,MATCH(F6,\$B\$5:\$D\$5,0),FALSE)
ですが、こちらの場合、表の下にデータが追加された場合に対応できないため、
列全体を指定した方が楽です。
テーブル形式となっている場合は、テーブル全体を指定すれば、
データ拡張をした場合自動で範囲が拡張されるので問題ありません。
この列全体を指定した場合、
列数全体での挿入には対応していますが、
表の範囲のみの指定での列の挿入の場合、
検索範囲が正しく拡張されないため、
対応することができません。
そこで、数式を以下のように変更します。
表の範囲のみの指定の場合は両方対応しています。
種類：VLOOKUP(\$G\$5,B:D,MATCH(F6,\$5:\$5,0)-MATCH(\$F\$5,\$5:\$5,0)+1,FALSE)
価格：VLOOKUP(\$G\$5,B:D,MATCH(F7,\$5:\$5,0)-MATCH(\$F\$5,\$5:\$5,0)+1,FALSE)
この数式は、
まず、B列の見出しである商品名の列数を求めて、
そこから知りたい見出しの列数との差よりvlookup関数の列数を求めています。
こうすることで、ずれが生じても問題なく求めることが可能となります。
xlookup関数の場合
xlookup関数では、見つからない場合、一致モード、検索モード
の指定ができますが、必須ではないため、この記事では記載を省略しています。
Office365、2021で使えるようになったxlookup関数がかなり便利で、
vlookup関数ではできなかった検索列より前の列を返せたり、
スピルを用いて複数行返せたりします。
vlookup関数ではmatch関数を使って列数を自動で求めることで、
列がズレてもvlookup関数の修正が必要ないようにすることがきましたが、
xlookup関数ではvlookup関数とは検索列の指定方法が、
列番号→範囲
に変更となっているため、
vlookup関数と同じ手法で列数を自動で求めることはできません。
vlookup関数では、match関数を用いることで列数が直接求められ、
そのままvlookup関数に使用できましたが、
xlookupについては範囲での指定のため、
そのままでは使えません。
そこで登場するのがoffset関数です。
offset関数は指定した範囲に対し、指定した数の行、列をずらした範囲を返してくれます。
これを利用します。
通常のxlookup関数の場合
種類：XLOOKUP(G5,B:B,C:C)
価格：XLOOKUP(G5,B:B,D:D)
で求められますが、
これだと列がズレたときは
C:C、D:Dの部分を修正する必要があります。
列全体の挿入とかの場合は自動で合わせてくれるので修正不要ですが、、
表の範囲のみの挿入や、
シート全体を上書きした場合とかは修正が必要となります。
offset関数を使用する場合は下記の通りとなります。
種類：XLOOKUP(G5,OFFSET(A:A,0,MATCH(F5,5:5,0)-1),OFFSET(A:A,0,MATCH(F6,5:5,0)-1))
価格：XLOOKUP(G5,OFFSET(A:A,0,MATCH(F5,5:5,0)-1),OFFSET(A:A,0,MATCH(F7,5:5,0)-1))
match関数にてまず検索の基準となるの見出しの列数を求め、
A列から列数分ズレた範囲をoffset関数で求めています。
次に、検索したい範囲を同様の手順にて求めて、
xlookup関数を使用することで、列が変更となっても求めることができます。
見出し名が変更されてしまった場合
都度、見出し名を参照のために記載している個所(上記例の場合、F6やF7)を変更する必要があります。
計算用シートを別途作成
毎回match関数で列数を求めるのは馬鹿らしいので、
別途計算用のシートを設けることで、簡略化して書くことができます。
むしろ、入力、計算、出力でシートを分けて書くことが推奨されています。
参照：https://xtech.nikkei.com/it/pc/atcl/knowhow/15/102300055/102300003/
計算用シートにて見出し名を記入、その見出し名がある列数をmatch関数で求めることで、
そこを参照するだけでよくなります。
リストシートの見出し名変更の場合も、計算用シートの見出し名の個所を変更するだけでOKとなります。
今回リスト・計算・検索でシート分けしてみました。
これで数式が見やすくなり、
各シートに余計な情報も記載されなくなるので、
シート自体も見やすくなったかと思います。
まとめ
lookup系関数にて列数を自動で求める方法を紹介しました。
これによって列がズレてしまっていても毎回修正が不要となりました。
毎回修正している人はこれを使って楽してみてください。