エクセルの関数

質問

ＶＬＯＯＫＵＰ関数とＩＮＤＥＸ＋ＭＡＴＣＨ関数の使い分けが良く分かりません。

ＶＬＯＯＫＵＰ関数が検索値と完全一致するものを検索
　　　　　　　　検索値と一致する値がない時データを昇順に並べ替えて検索値未満を検索
ＩＮＤＥＸ＋ＭＡＴＣＨ関数もＭＡＴＣＨ関数があるから似た事ができると思うのですがどうでしょうか？

この２つがいまひとつ理解ができません。分かりやすい説明をいただければ幸いです。
よろしくお願いします。

回答No.4

a987654 · ベストアンサー

＞いつでもＩＮＤＥＸ＋ＭＡＴＣＨを使えばＶＬＯＯＫＵＰを使う必要がない
結果は同じものが出せます。
違いは、INDEX、INDIRECT、MATCHを完全に理解して使っていれば
何の問題もありませんが、複数の人が使う共通のデータであったり
他の人に渡すデータの場合は VLOOKUP の方が受け入れられやすいと
思います。

もう１点 VLOOKUP の長所を挙げると（KURUMITO様の式を借ります）
=VLOOKUP(電話番号,B:C,2,)　　　　　[FALSEは省略しても同じです]
=INDEX(A:C,MATCH(電話番号,B:B,0),1)
=INDIRECT("A"&MATCH(電話番号,B:B,0))
どの数式が一番短いか一目瞭然ですよね。
数式の短さはデータが多くなるとファイルの大きさをいたずらに
増やす原因となりますので、同じ結果を得られるのであれば数式は
短いに越したことはありません。

VLOOKUP の難点については KURUMITO様が回答にありますように
検索対象が指定領域の最左列に限定される事です。

蛇足ですが
OFFSET ＋ MATCH　でも同様の結果を出せます。
使い方によっては VLOOKUP よりも数式を短くすることも可能です。
OFFSETには引数の数が３個（セルの値）か５個（領域）かで全く別の
使い方ができます。
下記参考に研究してみて下さい。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/itiran.html

お礼

なるほど、ＶＬＯＯＫＵＰの式が短くて済むのならいいですね。

そしてＯＦＦＳＥＴ＋ＭＡＴＣＨですか・・。
実はエクセル２級を勉強しているところです。
試験範囲外ですが便利そうですね。
ただ、式の基準にＢ３の空白を選ぶのが理解できませんでした。

回答No.6

tom04

No.3・5です！
何度もごめんなさい。

前回の回答で間違いをしていました。

VLOOKUP関数で参照データ範囲を降順に並べて検査の型を「-1」にすれば良い
というようなことを書いてしまいましたが、
検証せずに書いたことを謝らないといけません。
それではエラーになってしまいます。

VLOOKUPの場合完全一致（検査の型が「0」）であれば昇順でも、降順でも、規則性がなくても構わないと思いますが、
基本は昇順みたいです。
どうも失礼しました。

そして、余計なお世話かも知れませんが、
もう一度↓に画像をアップしてみました。

B11セルはVLOOKUP関数を使って
=VLOOKUP(A11,A4:B8,2,1)
という数式

D11セルにはVLOOKUP関数を使うとエラーになりますので
=INDEX(D4:D8,MATCH(C11,C4:C8,-1))
という数式が入っています。

結局境界をどちらに入れるか？ということが問題になります。
通常、使い方としては
「～以上～未満」　と　「～を超えて～以下」　というものが1セットだと思ったほうが良いかも知れません。

「～以上～以下」は出来ないことはないと思いますが、数式としてはかなり複雑になるか、表の作り方を相当工夫しなければならないと思います。

今回も回答にはなっていませんが、
ほんとぉ～！にごめんなさいね。m(__)m

回答No.5

tom04

No.3です！
たびたびごめんなさい。
補足欄に
>右の表についてですが、私の認識ではＶＬＯＯＫＵＰは昇順に並んでないといけないので、
こういう場合にＩＮＤＥＸを使うのでしょうか？

とありましたので、再びおじゃましました。

今回表にさせてもらったのは何行目の何列目を返す！という場合、
私はINDEX関数とMATCH関数を併用する場合が多いと言う意味で
あのような表を作成してみました。

VLOOKUP関数で対処出来るのであればわざわざINDEX関数等を使わなくても良いのではないかと思います。
INDEX関数を使うと行・列の数値（何行目か？何列目か？）を求めなければならないので、MATCH関数を使うことが多いと思います。

尚、範囲指定した一番左の列を参照して、何列目を返すか？と言う場合はVLOOKUPとMATCH関数の併用でOKですが、

前回アップした表で、降順にしたのは
～を超えて、～以下　という場合で、VLOOKUP関数も同様に降順にしないといけないはずです。
境界（～より大きい・以上・未満・以下etc.）によって昇順、降順が決まります。

VLOOKUP関数でもMATCH関数でも同様ですが、～超えて～以下の場合は
「検査の型」「照合の型」を「-1」にしなければなりません。

～以上～未満　の場合は昇順で
「検査の型」「照合の型」は　「TRUE」または　「1」にしますよね？

完全一致はもちろん　「FALSE」または「0」のはずです。

前回書かせていただいた数式を見てもらえれば判るとおもいますが、
MATCH関数の照合の型は「-1」にしているはずです。

今回も回答とはいかないのですが、
長々と失礼しました。m(__)m

補足

なるほど、～以上の昇順は本で勉強しました。
逆の～以下も降順に並んでいたら－１して使えるのですね。
↑だと境界が超えるとか未満もそのまま以上以下と同じ式で使えそうな感じですが、境界の違いを意識しないでいいのでしょうか？
理解度が低くて申し訳ないです。

回答No.3

tom04

こんばんは！
すでにKURUMITOさまが回答されている通りで、
一般的な使い方でいうと
VLOOKUP関数は表の1列目のデータを参照して何列目の値を返すか？
INDEX関数とMATCH関数の組み合わせ方法もちゃんと回答されている通りかと思います。

少し発展させて、行・列を参照して表の中のどの値を返すか？という方法を↓に画像で載せてみました。

左側の表の結果は同じですが、
C9セルは
=INDEX(B2:F6,MATCH(A9,A2:A6,0),MATCH(B9,B1:F1,0))
C10セルには
=VLOOKUP(A10,A2:F6,MATCH(B10,A1:F1,0))
という数式が入っています。
この場合は完全一致ですのでVLOOKUP関数でも大丈夫だと思いますが、

例えば右側の表の場合は行・列ともある数値範囲内の場合、表のどのデータを返せば良いか？というときは
VLOOKUP関数は大変使いにくいというか・・・使えるかどうか判りません。
質問の中に
>検索値と一致する値がない時データを昇順に並べ替えて検索値未満を検索

とありますが、
～を超えて～以下と言う場合もINDEXとMATCH関数の併用でできます。
尚、この場合参照する行・列は降順に並べておく必要があります。

J10セルには
=INDEX(K4:M7,MATCH(H10,J4:J7,-1),MATCH(I10,K3:M3,-1))
という数式が入っています。

私の場合は行・列を参照し、表の中のデータを返すと言えば
「何とかの一つ覚え」のようにINDEXとMATCH関数を併用してしまいます。

実は私も、どの方法が一番良いのか理解しかねています。
いずれにしても自分が使いやすい方法でやれば良いのではないでしょうか？

以上、長々と書き込みましたが、
あまりお役に立てないみたいでごめんなさいね。m(__)m

補足

なるほど、右の表についてですが、私の認識ではＶＬＯＯＫＵＰは昇順に並んでないといけないので、
こういう場合にＩＮＤＥＸを使うのでしょうか？

回答No.2

KURUMITO

解答Ｎｏ１です。
例えばＡ列に氏名、Ｂ列に電話番号、Ｃ列に住所があるとします。そこで電話番号を検索して住所を呼び出すのでしたら
=VLOOKUP(電話番号,B:C,2,FALSE)
と入力して求めることができますが電話番号から氏名を求めることはできません。その場合にはINDEX関数を使いますね。
=INDEX(A:C,MATCH(電話番号,B:B,0),1)
また=INDIRECT("A"&MATCH(電話番号,B:B,0))でもよいですね。

補足

↓とあわせて考えると、いつでもＩＮＤＥＸ＋ＭＡＴＣＨを使えばＶＬＯＯＫＵＰを使う必要がないということでしょうか？

回答No.1

KURUMITO

INDEXとMATCH関数を使うことでどの列にある値が検索値となっても望みの列を取り出すことができますが、VLOOKUP関数の場合にはある列にある値を検索値とした場合には必ずその列よりも右の列にある値を取り出すことになります。