SUM関数（合計値）やAVERAGE関数（平均値）などの習得からスタートし、もしも**ならば ‥‥（**でなかったならば ‥‥ ）という条件により計算値を変える IF関数を経て、表（データベース）の中から条件に合致する値を探して引き出す VLOOKUP関数 へと続いていくのが、Excel（エクセル）の習得の定番だと言われています。
私自身も間違いなく、その通りの道を歩んできた一人です。
多くの方がご存知の通り、VLOOKUP関数は 左端からしか検索できません。
データベースが都合よく左端に検索するデータが入っていない場合には、列を入れ替える、列を重複させる などをする必要が生じてしまいます。
広く知られていることですが、VLOOKUP関数を使わずに、OFFSET関数とMATCH関数を組み合わせた式 を使うことで、同様の結果を引き出すことが可能です。
そして、この OFFSET関数とMATCH関数を組み合わせ は 基準値のある列よりも 右側にある列も検索可能 な VLOOKUP関数 より優れた特徴を備えているのです。
しかし、弱点もあります。
VLOOKUPに比べて、関数式も複雑（長く）なるので、面倒だという点があります。
C10セルからR19セルまでに及ぶデータベースの中からB1セルと同じ値の示すものをC列のC10からC19の中より探し、その該当する行の R列の値を表示させる という命令を出すVLOOKUP関数の関数式は
=VLOOKUP($B1,$C$10:$R$19,16,FALSE)
となります。
私もまさに、この落とし穴にハマっている人間なのですが、VLOOKUP関数に慣れれば慣れるほど、この程度の関数式ならば、エクセルのガイド機能など使用せず、直接、関数計算式を入力することが出来ます。
この 慣れこそがダメ なのです。
上に紹介した VLOOKUPの関数式と同じ命令を出すOFFSET関数とMATCH関数を組み合わせ は次のような計算式になります。
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
正直、私は、この上に紹介した計算式（OFFSET関数とMATCH関数を組み合わせ）をスラスラと入力することが出来ません。
複合関数なので、エクセルに常設されているガイド機能も存在しません。
ハッキリ言えば、計算式の法則性にさして複雑さもないので、さっさと習得すれば良いだけのことなのですが、VLOOKUP関数に慣れすぎてしまっているが故に、逆に、OFFSET関数とMATCH関数を組み合わせた計算式を覚えようという意欲が湧いてこないのです。
きっと、世の中には、私と同じような理由で、いつまでも VLOOKUP関数から抜け出せない症候群 に陥ってしまっている人が多くいる筈だと思っているのです。
だから、これからエクセルを習得して行こうと思っている人には、敢えて VLOOKUP関数を決して覚えてはダメ！ だと私は忠告しているのです。
‥‥ ということで、既に VLOOKUP関数の呪縛 から抜け出せないでいる私のような人間に、先ほど述べた （OFFSET関数とMATCH関数を組み合わせの）法則性 を 利用して、VLOOKUP関数から自動的にOFFSET関数とMATCH関数を組み合わせを作る計算式を作りましたので紹介します。
作り方は至って簡単です。
VLOOKUPの関数式を作ります。
=VLOOKUP($B1,$C$10:$R$19,16,FALSE)
Windowsのメモ帳（テキストを貼れるものならば何でも可）に関数式を貼り付けます。
関数式の先頭の ＝（イコール）まで、連れていくと、面倒なことになるので
＝（イコール以外の） VLOOKUP($B1,$C$10:$R$19,16,FALSE)
をコピーします。
これをC3セルに貼り付けると、D3セルに
OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
が計算されます。
この値を（計算された結果）を再びメモ帳に戻して貼り付け、先頭に ＝（イコール）をつけます。
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
このような感じでメモ帳を使います。
D3セルに
=IF($C3="","","OFFSET("&I3&",MATCH("&G3&I3&":$"&K3&"$"&P3&",0)-1,"&S3-1&")")
という関数式が入れてあります。
せっかくなので、私は10個（10行分）、同様の関数計算式を入れたプログラムを作りました。
D4セルは
=IF($C4="","","OFFSET("&I4&",MATCH("&G4&I4&":$"&K4&"$"&P4&",0)-1,"&S4-1&")")
D5セルは
=IF($C5="","","OFFSET("&I5&",MATCH("&G5&I5&":$"&K5&"$"&P5&",0)-1,"&S5-1&")")
となり、コピペ で量産可能です。
イメージ画像と同じ関数プログラムを作って頂ければ、どなたでも、VLOOKUP関数からの変換が可能です。
ただし、注意していただきたいのは、変換する前のVLOOKUP関数の計算式には、必要に応じて $記号 の絶対値は式の中に入れておいて下さい。（絶対値の指定がない計算式を変換させようとすてるとエラーになります）
そして、最後にVLOOKUP関数では不可能な 右列の参照をする計算式 も、簡単に作れる、その方法も紹介します。
C列からR列までのデータベースで、C列を基準にR列を参照する場合、VLOOKUP関数では基準である自分（この場合はC列）が 1 となり、R列は1から数えて16番目なので、関数式は
=VLOOKUP($B1,$C$10:$R$19,16,FALSE)
となり、FALSEの前の関数式の数字が、16 になります。
しかし、OFFSET関数では、基準である自分が 0 となるため、C列を0としてR列まで数えると、16ではなく 15 となるので
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
と、関数式の最後の数字は 15 となります。
さて、これまでは VLOOKUP関数的な発想で、C列からRまでのデータベースを想像していましたが、実際のデータベースは、もう2列、右側に存在した A列からR列まで あったと想定して下さい。
データベースがA列から始まっていたとしても、検索の基準の列は 引き続きC列 だと思っていて下さい。
C列を基準に、隣のD列を参照する場合の計算式は
VLOOKUP関数では
=VLOOKUP($B1,$C$10:$R$19,2,FALSE)
となり、関数式はFALSEの前が、C列の隣を示す2 となります。
そして、OFFSET関数では
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,1)
となり、関数式の最後は、C列の隣を示す1 となります。
OFFSET関数の計算式の最後の数字に注目して下さい。
C列が基準なので、D列が1なので、E列ならば2となり、自分自身のC列ならば、0 となります。
‥‥ ということは ‥‥
その通りです。
B列は -1 なり、A列は -2 なります。
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,-2)
という計算式で、vlookupでは不可能なC列を基準にA列を参照できるのです。
つまり、一つ作業工程が増えますが、C列からA列を参照する関数式を作りたい時は、最初にD列を参照するVLOOKUPの関数式を作り、ここで紹介したプログラムを使って、OFFSET関数の計算式に変換して、その後、手作業で、計算式の最後の数字を 1から-2に修正 すれば、手軽にC列からみて右側にあるA列を参照する関数計算式を作ることが出来ます。
シリーズ
「エクセルの便利メモ」 Excelを極めるにはVLOOKUP関数を使ってはいけない
の他の記事（目次）は下記のURLとなります。
http://blog.goo.ne.jp/pizzica0912/e/2d6c432428b6d6f4708f239c9af2ffd5
音楽(クラシック) ブログランキングへ
私自身も間違いなく、その通りの道を歩んできた一人です。
多くの方がご存知の通り、VLOOKUP関数は 左端からしか検索できません。
データベースが都合よく左端に検索するデータが入っていない場合には、列を入れ替える、列を重複させる などをする必要が生じてしまいます。
広く知られていることですが、VLOOKUP関数を使わずに、OFFSET関数とMATCH関数を組み合わせた式 を使うことで、同様の結果を引き出すことが可能です。
そして、この OFFSET関数とMATCH関数を組み合わせ は 基準値のある列よりも 右側にある列も検索可能 な VLOOKUP関数 より優れた特徴を備えているのです。
しかし、弱点もあります。
VLOOKUPに比べて、関数式も複雑（長く）なるので、面倒だという点があります。
C10セルからR19セルまでに及ぶデータベースの中からB1セルと同じ値の示すものをC列のC10からC19の中より探し、その該当する行の R列の値を表示させる という命令を出すVLOOKUP関数の関数式は
=VLOOKUP($B1,$C$10:$R$19,16,FALSE)
となります。
私もまさに、この落とし穴にハマっている人間なのですが、VLOOKUP関数に慣れれば慣れるほど、この程度の関数式ならば、エクセルのガイド機能など使用せず、直接、関数計算式を入力することが出来ます。
この 慣れこそがダメ なのです。
上に紹介した VLOOKUPの関数式と同じ命令を出すOFFSET関数とMATCH関数を組み合わせ は次のような計算式になります。
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
正直、私は、この上に紹介した計算式（OFFSET関数とMATCH関数を組み合わせ）をスラスラと入力することが出来ません。
複合関数なので、エクセルに常設されているガイド機能も存在しません。
ハッキリ言えば、計算式の法則性にさして複雑さもないので、さっさと習得すれば良いだけのことなのですが、VLOOKUP関数に慣れすぎてしまっているが故に、逆に、OFFSET関数とMATCH関数を組み合わせた計算式を覚えようという意欲が湧いてこないのです。
きっと、世の中には、私と同じような理由で、いつまでも VLOOKUP関数から抜け出せない症候群 に陥ってしまっている人が多くいる筈だと思っているのです。
だから、これからエクセルを習得して行こうと思っている人には、敢えて VLOOKUP関数を決して覚えてはダメ！ だと私は忠告しているのです。
‥‥ ということで、既に VLOOKUP関数の呪縛 から抜け出せないでいる私のような人間に、先ほど述べた （OFFSET関数とMATCH関数を組み合わせの）法則性 を 利用して、VLOOKUP関数から自動的にOFFSET関数とMATCH関数を組み合わせを作る計算式を作りましたので紹介します。
作り方は至って簡単です。
VLOOKUPの関数式を作ります。
=VLOOKUP($B1,$C$10:$R$19,16,FALSE)
Windowsのメモ帳（テキストを貼れるものならば何でも可）に関数式を貼り付けます。
関数式の先頭の ＝（イコール）まで、連れていくと、面倒なことになるので
＝（イコール以外の） VLOOKUP($B1,$C$10:$R$19,16,FALSE)
をコピーします。
これをC3セルに貼り付けると、D3セルに
OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
が計算されます。
この値を（計算された結果）を再びメモ帳に戻して貼り付け、先頭に ＝（イコール）をつけます。
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
このような感じでメモ帳を使います。
D3セルに
=IF($C3="","","OFFSET("&I3&",MATCH("&G3&I3&":$"&K3&"$"&P3&",0)-1,"&S3-1&")")
という関数式が入れてあります。
せっかくなので、私は10個（10行分）、同様の関数計算式を入れたプログラムを作りました。
D4セルは
=IF($C4="","","OFFSET("&I4&",MATCH("&G4&I4&":$"&K4&"$"&P4&",0)-1,"&S4-1&")")
D5セルは
=IF($C5="","","OFFSET("&I5&",MATCH("&G5&I5&":$"&K5&"$"&P5&",0)-1,"&S5-1&")")
となり、コピペ で量産可能です。
イメージ画像と同じ関数プログラムを作って頂ければ、どなたでも、VLOOKUP関数からの変換が可能です。
ただし、注意していただきたいのは、変換する前のVLOOKUP関数の計算式には、必要に応じて $記号 の絶対値は式の中に入れておいて下さい。（絶対値の指定がない計算式を変換させようとすてるとエラーになります）
そして、最後にVLOOKUP関数では不可能な 右列の参照をする計算式 も、簡単に作れる、その方法も紹介します。
C列からR列までのデータベースで、C列を基準にR列を参照する場合、VLOOKUP関数では基準である自分（この場合はC列）が 1 となり、R列は1から数えて16番目なので、関数式は
=VLOOKUP($B1,$C$10:$R$19,16,FALSE)
となり、FALSEの前の関数式の数字が、16 になります。
しかし、OFFSET関数では、基準である自分が 0 となるため、C列を0としてR列まで数えると、16ではなく 15 となるので
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,15)
と、関数式の最後の数字は 15 となります。
さて、これまでは VLOOKUP関数的な発想で、C列からRまでのデータベースを想像していましたが、実際のデータベースは、もう2列、右側に存在した A列からR列まで あったと想定して下さい。
データベースがA列から始まっていたとしても、検索の基準の列は 引き続きC列 だと思っていて下さい。
C列を基準に、隣のD列を参照する場合の計算式は
VLOOKUP関数では
=VLOOKUP($B1,$C$10:$R$19,2,FALSE)
となり、関数式はFALSEの前が、C列の隣を示す2 となります。
そして、OFFSET関数では
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,1)
となり、関数式の最後は、C列の隣を示す1 となります。
OFFSET関数の計算式の最後の数字に注目して下さい。
C列が基準なので、D列が1なので、E列ならば2となり、自分自身のC列ならば、0 となります。
‥‥ ということは ‥‥
その通りです。
B列は -1 なり、A列は -2 なります。
=OFFSET($C$10,MATCH($B1,$C$10:$C$19,0)-1,-2)
という計算式で、vlookupでは不可能なC列を基準にA列を参照できるのです。
つまり、一つ作業工程が増えますが、C列からA列を参照する関数式を作りたい時は、最初にD列を参照するVLOOKUPの関数式を作り、ここで紹介したプログラムを使って、OFFSET関数の計算式に変換して、その後、手作業で、計算式の最後の数字を 1から-2に修正 すれば、手軽にC列からみて右側にあるA列を参照する関数計算式を作ることが出来ます。
シリーズ
「エクセルの便利メモ」 Excelを極めるにはVLOOKUP関数を使ってはいけない
の他の記事（目次）は下記のURLとなります。
http://blog.goo.ne.jp/pizzica0912/e/2d6c432428b6d6f4708f239c9af2ffd5
音楽(クラシック) ブログランキングへ