ホーム ▷ ブログ記事 ▷ 弱点のあるVLOOKUPをOFFSETへ変換するためのエクセル関数式を紹介
ブログ記事｜弱点のあるVLOOKUPをOFFSETへ変換するためのエクセル関数式を紹介
弱点のあるVLOOKUPをOFFSETへ変換するためのエクセル関数式を紹介
私自身も間違いなく、その通りの道を歩んできた一人です。
多くの方がご存知の通り、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関数では不可能な 右列の参照をする計算式も、簡単に作れる、その方法も紹介します。
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
このBLOGの他の連載中、連載完了の各シリーズは下に紹介したURLまたは画像をクリックすると、該当するシリーズのBLOG記事の一覧に行きます。
－－－ 連 載 中 －－－
シリーズ
世の中に物申す
http://blog.goo.ne.jp/pizzica0912/e/5daf02cffe9da5b480f3820aa21dd756
どのような不利益が降りかかったとしても、世の中に発信していかなくては‥‥と
私の心が揺れ動くことを微力ながらも発信しています。
シリーズ
認知症の父の自宅介護
http://blog.goo.ne.jp/pizzica0912/e/357b83df4b2f1613c2fa5e366d6d96e3
育ててくれた親が病気になれば、介護をするのは人として当たり前のことです。（写真は大昔に父が箱根へ連れていってくれた時の写真です）
そんな父への介護のピークは、どの施設からも（病状が酷く）受け入れを断られ、2002年～2011年までの間に朝5時～夜中の2時まで毎日続けた自宅介護でした。
シリーズ
全ての説得を拒否した芸能人の子供が多く通っている誰もが名前を知っている有名私立小学校の教師をしている兄の悪業
http://blog.goo.ne.jp/pizzica0912/e/ca82c30d916dba8ba415de28051e33a7
16年に及ぶ介護放棄を行って上、未だに父親の墓を知らず、その事実を保護者の方たち関係者に隠し、何食わぬ顔で教師を続けている人間です。
シリーズ
教師に道徳的指導は行わないと私に語った校長
http://blog.goo.ne.jp/pizzica0912/e/11b4e5673c18af2b8cbeb1cf3726222d
民法第877条違反の懸念が強い教師がいることを保護者に隠し、道徳的な指導を教師に行わないと言った有名私立小学校の校長の話です。
シリーズ
エクセル便利メモ Eエクセルの便利メモ Excelを極めるにはVLOOKUP関数を使ってはいけない
エクセルに限定せず、素人の私が便利だと思った事などを備忘録的に掲載していきます
シリーズ
唯一の被爆国 日本の戦争について
http://blog.goo.ne.jp/pizzica0912/e/fdaa79c8088b9c248a8b233e824576cf
唯一の被爆国である日本の戦争について様々な角度から自分の思うことを記していきたいと思います。
シリーズ
心の偏差値
http://blog.goo.ne.jp/pizzica0912/e/cb3273f5b779ad947160f885015d4e38
頭デッカチ、頭がいくら良くても、本当に大切なのは心の豊かさ、その思いを綴っております。
シリーズ
音楽の素晴らしさ
http://blog.goo.ne.jp/pizzica0912/e/7e3b92fd45e0082610ac0f53b1c957c1
音楽の素晴らしさを私の拙い文章でどこま表現できるかわかりませんが、記していきたいと思います。
シリーズ
世界が実力を認めたピアニスト 川上敦子さん
http://blog.goo.ne.jp/pizzica0912/e/ab144349ca849a6e6f7e8514a704a86e
大成功のカーネギーホールのリサイタルと同じ演奏を聴きました！
既にシリーズ掲載を完了している 伝説のピアニスト 川上敦子さん（全25話）の続編にあたります。
シリーズ
日本を思う
http://blog.goo.ne.jp/pizzica0912/e/405be3999114db53c15c40c8f9074e75
私が生まれ育った日本。 この日本を思う心を綴っていきたいと思います。
シリーズ
歴史あれこれ
http://blog.goo.ne.jp/pizzica0912/e/2e5b20b732b5089016301b0f7c860a53
学校の授業で教わる歴史とは違った切り口で、歴史の話を伝えていければ ‥‥ と思っています。
ランダムで古今東西の話が飛び出してくると思いますので、ご了承下さい。
シリーズ
DIY 自分で作ったり修理したりする
http://blog.goo.ne.jp/pizzica0912/e/4234d39dbee719b1c82d8ec942361b29
下手ですが、私の趣味のＤＩＹ関係を載せていきます。（写真は自作した立水栓です）
シリーズ
風水より仏事（ほとけごと）
http://blog.goo.ne.jp/pizzica0912/e/ab37d0d7c9acb16caa0d647b5541617c
ご先祖を大切にする日々のことを載せていきます。
シリーズ
花や野菜の魅力
http://blog.goo.ne.jp/pizzica0912/e/f062dde5f866923a99549713ea022672
庭の花や野菜を載せていきたいと思います。
シリーズ
スピチュアルや超常現象的なもの（現時点で人類が解明できないだけ ‥‥ ）
http://blog.goo.ne.jp/pizzica0912/e/197f6045042ab5d365c80c8abb98a006
簡単に目では見えない、耳で見えない、現時点での人類の力で科学的に解明できないだけで、きっと存在するだろう ‥‥ と、私が思うものについて載せていくシリーズとなります。
シリーズ
お勧めの本
http://blog.goo.ne.jp/pizzica0912/e/fe93d4fd3e51353a24e82ed31f06fdc9
私が読んで印象に残っている本を紹介するシリーズとなります。
シリーズ
アニメや絵本など、子供のものから学ぶものが多くあります！
http://blog.goo.ne.jp/pizzica0912/e/a59682f062a7f4d84e440c3cb53c22c9
子供番組や絵本、アニメなど、決して馬鹿になど出来ず、逆に、大人の方が、その内容から大いに学ぶ場合も多くあり、そのようなものを取り上げていくシリーズとなります。
シリーズ
遠き日の思い出
http://blog.goo.ne.jp/pizzica0912/e/1fd8827372937ffcaeff5020c130d363
発掘された小学校三年生（1975年）の日記や写真を中心に子供の頃の楽しかった想い出を振り返ります。
（馬が子供の頃より大好きだった私がポニーに乗っている写真です）
シリーズ
美味しいものシリーズ
http://blog.goo.ne.jp/pizzica0912/e/d50801e93535e5d591d2f926236429cf
私が巡り遭えた美味しいものを載せていきます。
シリーズ
名言集より
これは！ と感じた 名言 を載せていきたいと思います。
一話完結・番外編・その他
http://blog.goo.ne.jp/pizzica0912/e/6fb93531a650944a79df8f17953f0b2f
シリーズものでない一話完結の話を掲載しております。
Facebookのダイジェスト
http://blog.goo.ne.jp/pizzica0912/e/b3416c4cddea8f8ef7dd0788fcd1aa47
私がFacebookで掲載している記事をダイジェストで載せています。
－－－ フォトチャンネル －－－
様々な写真集を掲載したフォトチャンネルへの入口のURLとなります。
http://blog.goo.ne.jp/pizzica0912/e/c9ced1117c86eb51aea4177123b57118
－－－ 既に掲載完了済み －－－
伝説のピアニスト 川上敦子さん （全25話）
http://blog.goo.ne.jp/pizzica0912/e/425e60748dfa331bec8b445953003a7a
演奏不可能と言われたリストの超絶技巧練習曲（1837年版）を日本で初演したピアニスト
川上敦子さんのことを掲載したシリーズです。
最終話：2015年3月11日掲載
川上敦子さんから頂戴したお心
祖母の思い出 （全第14話 最終話を掲載）
http://blog.goo.ne.jp/pizzica0912/e/4816511909af978673200140e1fbc228
4人の祖父母の中で唯一、長い交流のあった私が32歳の時に他界した祖母の思い出を記したシリーズとなります。
最終話：2015年6月10日掲載
多くの教えと思い出をありがとう。（シリーズ最終話）
重度の介護が必要だた父の緊急入院から納骨まで （全21話）
http://blog.goo.ne.jp/pizzica0912/e/c203e74471097ecc448cff62eae56542
重度の介護状態で自宅介護を受けていた父が緊急入院して他界してしまった話を記したものです。
最終話：2014年11月29日掲載
認知症でも、まだ手が動かせていた頃の父が描いた絵
愛犬パークの15年の生涯 （全18話）
http://blog.goo.ne.jp/pizzica0912/e/a8ccdc94c043ada8644af3606c3c34a5
保健所で殺処分されるところを助けられた生命。我が家に様々な想い出を残してくれた愛犬パークの15年の生涯を綴ります。
最新話：2015年11月30日掲載
母の腕の中で静かに天国へ旅立って逝ったパーク
音楽(クラシック) ブログランキングへ 
福祉・介護 ブログランキングへ 
にほんブログ村
2016/10/10 23:00:08