実務で使えるExcel入門セミナー OFFSET関数&MATCH関数の組み合わせで、VLOOKUP関数で表示できない、左側にあるデータも表示させることができます

シェアする

  • このエントリーをはてなブックマークに追加

VLOOKUP関数は非常に便利な関数ですが、弱点もあります。それは、”検索した列の、左側にある列のデータ”を表示できないこと。 
一つの解決策として、「OFFSET関数&MATCH関数」の組み合わせを使う方法があります。 

スポンサーリンク
自動サイズレスボンシブ

VLOOKUP関数の弱点

VLOOKUP関数は、データが入力されたリストの中から、

スクリーンショット 2015 06 23 9 50 17
(証券コード、社名、決算期、取引所、事業内容、のリスト)


探したいデータを指定すると、

スクリーンショット 2015 06 23 9 48 02
(ここでは「社名(ブスケツ不動産)」を探したいデータにします。)


指定した範囲の、一番左の列を、に検索して、


スクリーンショット 2015 06 23 9 54 33


探したいデータ(ブスケツ不動産)が見つかったら、 
指定した数だけ、そのセルから右に進んだセルを、表示します。

スクリーンショット 2015 06 23 10 04 02
(”3”と指定して、右に3つ進んだセルを表示させる)


すると、

スクリーンショット 2015 06 23 10 10 55


表示させたいデータ(ブスケツ不動産の取引所)を表示させることができます。


膨大なデータを含む資料の中から、必要なデータを一瞬で表示させることができるので、財務データだけでなく、在庫データ、人事データなどをExcelで処理する場合にも重宝します。


ただ、VLOOKUP関数にも弱点があります。


それは、「探したいデータ」の左側の列に、表示させたいデータがある場合です。


ここまでの、VLOOKUP関数の動きを見ていただいて、何となくお分かりいただけたかと思いますが、 
VLOOKUP関数は、範囲を指定すると、指定範囲一番左の列が自動的に、検索する列になってしまいます。


ですので、VLOOKUP関数で表示させるセルは、必ず、検索する列の右側に来ることになり、


検索列の左側を表示させることはできません。


このVLOOKUP関数の弱点を克服する方法の一つとして、「”OFFSET関数&MATCH関数”の組み合わせ」があります。

「”OFFSET関数&MATCH関数”の組み合わせ」で、左側の列も表示できます

OFFSET関数

まず、OFFSET関数の特長をおさえます。 
OFFSET関数の”動き”が理解できると、「左側の列」を表示できる理由が何となく分かります。


OFFSET関数は、「基準となるセル」を指定して、

スクリーンショット 2015 06 23 10 50 51
(青で色づけしたB1セルを「基準となるセル」に指定)


「基準となるセル」から移動する距離を、セルの数で指定します。 
順番は、”行”→”列”の順です。


縦にセル3つ分、横にセル1つ分移動させるとすると、


=OFFSET(B1, 3 , -1)


になります。 
列に”-1”と、マイナスが着いているのは、

右に移動するときはプラス、 
左に移動するときはマイナス、


の指示が必要だからです。

スクリーンショット 2015 06 23 10 56 54


すると、

スクリーンショット 2015 06 23 11 04 01


「基準となるセル=B1セル」から、3行と-1列進んだ「A4」セルのデータを、表示することができました。


これが、OFFSET関数の動きです。


まとめると、


1.基準となるセルを指定


2.「基準となるセル」から移動する距離を、「行→列」の順に指定


3.2.に従って移動したセルのデータを表示


こうなります。


これを踏まえると、


「探したいデータ」が何行目にあるか


が分かれば、あとは、表示したいデータのセルが、

・右の列ならプラス

・左の列ならマイナス


で指定すれば、自在に表示したいデータを指定できることになります。


ということで、次は、


「探したいデータ」が何行目にあるか


を考えます。


MATCH関数がその答えです。

MATCH関数

MATCH関数の動きを見てみましょう。 
MATCH関数は、使い勝手の良い便利な関数です。


探したいデータを指定して、

スクリーンショット 2015 06 23 11 28 59
(ここでは「マキシマム ザ ホルモン」を指定)


さらに、検索範囲を指定すると、

スクリーンショット 2015 06 23 11 31 14
(A1セルからA8セルまでを指定)


探したいデータが、指定した範囲の先頭のセルから、何番目にあるかを表示してくれます。

スクリーンショット 2015 06 23 11 35 59
(探したいデータ(=マキシマムザホルモン)は、A5セルにあり、検索範囲の先頭セル(A1セル)から数えて5番目)


これがMATCH関数の動きです。


まとめると、


1.探したいデータを指定


2.検索範囲を指定


3.検索範囲の先頭のセルから何番目に、探したいデータがあるかを表示


こうなります。


このMATCH関数の動きを踏まえると、OFFSET関数の”行”の移動距離の指定のところ、つまり、


「探したいデータ」が何行目にあるか


に組み込むことができるので、両方を組み合わせることで、 
「検索列の左側にあるデータ」を表示することが可能になります。

「OFFSET関数&MATCH関数」を組み合わせる

元にリストに戻って、社名から証券コードを表示させることを考えます。


証券コードは、社名の左側の列にあるので、VLOOKUP関数では表示させることができないデータでした。

スクリーンショット 2015 06 23 11 54 59


OFFSET関数からスタートです。 
「基準となるセル」を指定します。

スクリーンショット 2015 06 23 12 02 11


「社名」の列の先頭行のセルを「基準となるセル」にします。


次に、「基準となるセル」から移動する距離です。 
先に”行”の移動距離を入力しますが、ここで、MATCH関数です。


「探したいデータ」がリストの先頭行のセルから、何番目にあるかを表示させるようにします。

スクリーンショット 2015 06 23 12 06 19


探したいデータは、C12セルに指定。 
C12セルには「ブスケツ不動産」と入力されています。


検索範囲は、社名が入力されているC14セルからC20セルまで。


先頭行のセルはC14セルなので、


C14セルから数えて何番目のセルに「ブスケツ不動産」が入力されているか


を表示してくれます。


なお、MATCH関数で表示される番号は、検索範囲の先頭のセルを「1」と数えるのに対して、 
OFFSET関数の移動距離の指定は、「基準となるセル」を「0」と数えるので、OFFSET関数の指定方法に合わせて、

=OFFSET(C14,MATCH(C12,$C$14:$C$20,0) -1


「-1」を付け足します


最後に、「基準となるセル」から移動する距離の””です。


表示したいデータは「証券コード」ですが、「証券コード」の列は、「基準となるセル」のC列より、1列左です。


「左側の列へ移動」を指示するためには、マイナスをつければいいので、「-1」と入力

スクリーンショット 2015 06 23 12 27 41


すると、

スクリーンショット 2015 06 23 12 30 11


VLOOKUP関数では不可能だった、「探したいデータ」の左側にある列のデータを表示することができました。

まとめ

VLOOKUP関数の弱点は、「探したいデータ」の左側の列に、表示させたいデータがある場合に、それを表示させられないことです。 
この弱点は「”OFFSET関数&MATCH関数”の組み合わせ」で克服することができます。 
なお、この方法以外にも「”INDEX関数&MATCH関数”の組み合わせ」で可能ですので、そちらも参考にしてみて下さい。


おまけ

ホルモン見たくなってきました。 
首、鍛えよう。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする