Excelの便利スキル(第10回)検索範囲を変更する(取得値の列位置を変更)
すぐに使えるExcelの便利な小技を集めました
よく使う機能、よく使う関数を少しだけ深掘り
第10回は、検索範囲となる表を更新・修正しても、VLOOKUP関数を修正することなく値が取得される書き方の一つを紹介していきます。
- VLOOKUP関数の「範囲」を変更すると、列番号も自動で変更される方法
では、細かく見ていきましょう。
【第10回】検索範囲を変更する(取得値の列位置を変更)
前々回の記事(Excelの便利スキル(第8回)指定範囲から検索して値を取得する(VLOOKUP関数))で、一覧表から検索値を元に値を取得する方法を紹介しました。しかし、検索範囲となる表を更新したり、修正して資料を活用していくことも少なくないかと思います。
例えば、取扱商品の一覧から「仕入先名」列を削除し、「カラー展開」列の追加を行う場合を考えます。
取扱商品一覧を検索範囲としている発注リストには、発注ロットや担当者の項目を取得するためにVLOOKUP関数が入力されていますが、範囲の一覧表が修正されることで列番号がずれてしまい、正しく値を取得することができません。
検索範囲となる表に列を追加・削除したとしても、列名等が変更されないのであれば、「列番号を取得するセル」を用意しておくことで、VLOOKUP関数を修正する手間を省くことができます。
「列番号を取得するセル」にはMATCH関数を使用します。
- 検査値:検索する値のことです。データやセルを指定します。
- 検査範囲:検索を行う範囲のことです。
- 照合の種類:検索する方法を1、0、-1のいずれかで指定をします。省略することもできますが、その場合は1が指定されたものとして動作します。
- 1:検査値以下の最大の値を検索します。
- 0:検査値と等しい値を検索します。
- -1:検査値以上の最小の値を検索します。
MATCH関数は検査値が見つからない場合にエラー(#N/A)となります。検査範囲は1行のみ、1列のみを指定すると分かりやすいでしょう。今回は検査値である「列名」が検査範囲の中の何番目にあるのかを数えさせることで、列番号を取得します。
VLOOKUP関数の列番号に「列番号を取得するセル」を指定することで、取得する値が範囲の左から何番目かを数えることもなく、VLOOKUP関数も修正することもなく値を取得することができます。
また、表の見た目上「列番号を取得するセル」を挿入することが難しいようであれば、VLOOKUP関数の引数にMATCH関数をネストさせることで対応が可能です。
このExcel小技、すぐに使えそうでしょう?
Excelだけでも様々な処理や操作が可能で、その中でも頻繁に使う機能や関数は絞られるはず。「よく利用する」機能や関数をもう少し詳しく知り、使いこなすことで、今の作業をより早く、より簡単に終わらすことができるようになるといいですよね。