Excelシート上で使える、複数の関数を組み合わせた特殊な使い方を説明。
今回は OFFSET、MATCH を組み合わせることで、
VLOOKUP・HLOOKUPよりも汎用性が高いデータ検索方法を紹介。
【本記事の目標】
OFFSET、MATCHを使って
汎用性が高いデータ検索・抽出をしよう
今回組み合わせる関数は下記でも説明していますので、ご参考ください。
VLOOKUP、HLOOKUP関数との違い
VLOOKUP、HLOOKUP関数の欠点
VLOOKUP、HLOOKUP関数は、表になっているデータの中から指定のデータを検索し、それに関連するデータ（同じ行・列のデータ）を抽出することができる、非常に便利な関数です。
１つの関数で検索と抽出の両方ができ非常に便利な関数なのですが、使用する上で大きく2点欠点があります。
1つ目は、検索できる場所がVLOOKUP関数だと左端の列、HLOOKUP関数だと上端の行に限定されてしまう、という点です。
多くの場合、表の形式になっているデータは左端や上端にそのデータを代表する項目（名簿やID等）が記載されているかと思いますので、それらを検索する場合は問題ありませんが、そうでない表の内部のデータを検索する場合はこれらの関数を使うことはできません。
2つ目は、抽出するデータは関数内で指定した領域内に限定されるため、表が拡張された際に関数の修正が必要になる場合がある、という点です。
VLOOKUP、HLOOKUP関数は関数内で範囲を指定します。その指定した範囲の中で、左端もしくは上端のデータを検索し、検索データの別の列もしくは行のデータを抽出してきますが、このとき抽出できるのは最初に指定した範囲内のみのため、
もしデータの拡張（表に1列データを追加した等）があった場合、その拡張した部分のデータを抽出することはできません。
厳密には、表の途中に行もしくは列を追加するといった方法や、指定する範囲をテーブルとして挿入してあれば、Excelの関数自動修正機能を使って修正してくれますが、これらの機能を正確に理解して使用しなければ、気づいたときに突然エラーが出る等の不具合が出てしまいます。
これら２つの欠点を解消しつつ、全く同じ機能を持たせることができるのが、
OFFSET、MATCH関数の組み合わせなのです。
OFFSET、MATCH関数の組み合わせのメリット・デメリット
VLOOKUP、HLOOKUP関数の機能は「検索」と「抽出」に分けることができますが、
「検索」の部分を担うのがMATCH関数
「抽出」の部分を担うのがOFFSET関数 です。
OFFSET、MATCH関数の組み合わせのメリットは、先述した2つの欠点を解消できる、という点です。
1つ目の検索する場所が限定される、という点ですが、
「検索」を担うMATCH関数で領域を指定する必要がありますが、検索をするだけですので、表の中の「どこ」と縛られることはありません。
2つ目の指定した範囲に制限される、という点ですが、
「抽出」を担うOFFSET関数では特に範囲を指定せず、参照セルからいくつ移動した場所のデータを取り出す、というだけですので、極論を言うとExcelシートの端のデータを取り出すことも可能です。
またそれは表にデータが拡張されようと一切影響を受けません。
じゃあこの2つの関数を使うことのデメリットは？というと、
２つの関数を組み合わせるため、使い方が複雑になるため難易度が高いという点と、
中身を正確に把握しておかないと想定と違う動きをしてしまうという点です。
（エラーが出ず、違うデータを抽出してしまう危険があります）
ただ、理解できてしまえば非常に使い勝手が良い関数ですので、
是非とも使いこなせるようになっておくことをお勧めします。
（個人的にはこの使い方を知ってからVLOOKUP関数を使っていないです。。。）
具体的な使用方法は後ほど説明します。
過去に家計簿アプリ作成の際にも説明していますので、そちらも合わせてご参考ください。
OFFSET、MATCHを使ったデータ検索方法
組み合わせの書き方
実際に使用する場合は書き方は下記のようになります。
=OFFSET(検索領域の１つ上のセル, MATCH(検索値, 検索領域, 0), 抽出する列)
指定する場所のイメージ図を描くと、下記にようになります。それぞれ詳しく説明していきましょう。
入力情報① 検索値
検索値はそのまま検索したい文字列、数値を指定します。
セルを指定することも可能です。その場合は指定したセルに入力されているデータを検索します。
入力情報② 検索領域
検索領域は「検索値」を検索したい領域を指定します。
行でも列でも可能ですが、行の場合は１行、列の場合は１列で指定する必要があります。
また上記イメージ図は行で指定する場合を想定して描いていますが、
列で指定する場合は、次の「検索領域の１つ上のセル」が「検索領域の１つ左のセル」に変わりますのでご注意ください。
入力情報③ 検索領域の１つ上のセル
OFFSET関数の参照セルには、MATCH関数で検索する領域の１つ上のセル、もしくは１つ左のセルを指定します。
ただし、上記イメージ図のように１行目が検索領域に入っていないのであれば１つ上のセルを指定することができますが、１列を全て（E列全て）を検索領域に指定している場合、１つ上のセルを指定できない場合があります。
その場合は、検索領域の一番上のセルを指定しておき、
MATCH(検索値, 検索領域, 0)-1
というように ” -1 “ を追加しておくことで同じように使用することが可能です。
入力情報④ 抽出する列
ここではOFFSET関数の参照セルから数えて何列目もしくは何行目のデータを抽出したいかを指定します。
何行目・何列目なので数値を入力します。数値であればOKなので、数値が入力されているセルを指定しても問題ないです。
また、イメージ図では検索領域の右側の列を指定していますが、左側の列を指定しても問題ないです。
ただしその場合は、ここに入力する数値はマイナスになります。
組み合わせの書き方の別バージョン
上記までの書き方で、VLOOKUP関数・HLOOKUP関数と同じ動きをすることができ、
かつVLOOKUP関数・HLOOKUP関数の欠点を解消することができます。
ただ上記の書き方以外にも、微妙に書き方が違いますが全く同じ機能をもつ書き方もあります。
それが下記の書き方です。
=OFFSET(抽出する列の一番上のセル, MATCH(検索値, 検索領域, 0), 0)
指定する場所のイメージ図を描くと下記のようになります。
こちらの書き方ですと、OFFSET関数の参照セルとして抽出したい列の１つ上のセルを指定する必要がある代わりに、抽出したい列が何列目なのかというのを指定する必要が無くなります。
どちらも動きとしては全く同じですので、
・検索領域から何列目、と指定する方が楽なのか
・抽出したい列をOFFSET関数の参照セルにした方が楽なのか
指定しやすい状況に合わせて使い分けていただいたらいいかと思います。
コメント