vlookup関数の挙動について

質問

エクセルシートzzzに以下のデータがあります。
全部で140行あります。
A列　 B列
・
・
・
5/31　1000
5/31　1000
5/31　1000
収入　10000
支出　2000

あるシートで
=VLOOKUP("収入",'zzz'!A1:H300,1)とすれば
当然、「収入」という文字列が戻ってきます。
また、
=VLOOKUP("5/31",'zzz'!A1:H300,1)とすれば
当然、「5/31」という文字列が戻ってきます。
ところが、
=VLOOKUP("支出",'zzz'!A1:H300,1)とすると
なぜか、「支出」ではなく、「5/31」が戻ってきます。
しかし、H300をH500にすると、つまり
=VLOOKUP("支出",'zzz'!A1:H500,1)とすると
データは140行しかないにかかわらず
なぜかきちんと「支出」が戻ってきます。
ちなみにA列の書式はすべて文字列です。

何か考えられる原因がありましたら
教えていただけないでしょうか。

zzzシートで直接、=VLOOKUP("支出",A1:H300,1)としても
同様に5/31という値が返ってきます。
A1:H300とA1:H500の差の200行には何もデータは入っておりません。

動作環境はXP SP2 Excel２０００です。

回答No.3

_Kyle · ベストアンサー

横から失礼します。

TRUE型検索は【データがソートされている場合には】
あたかも"上のセルから順に比較"していったかのように値を返しますが、
実際は"上のセルから順に比較"しているわけではありません。

"上のセルから順に比較"するのであれば、
範囲を広げただけで異なる結果が返る理由を説明できませんし、
　=VLOOKUP(3,{3;4;1;2;5},1)
の戻り値は、3でも4でもなく、2です。

>検索の型がTRUEの場合は、検索対象のデータが昇順に並んでいないと
>取れる値は保証できないということなのでしょうか？

「保証できない」というよりも「得られる結果に意味がない」というニュアンスです。
ヘルプにも、

>データは、昇順に並べ替えておく必要があります。
>そうしないと、正しく計算が行われません。

とあり、製造元自身が「正しく計算が行われない」と言い切っちゃってますから、
極端なハナシ、どんな値が返ってこようとそれが「正常な挙動」ということです。

ただ、ソートされていないデータについてTRUE型検索を行なった場合でも
「検索値に十分に大きい値を設定すれば、最後のデータが返る」
ことが知られていて、
最終データの位置を調べるためにその性質を利用する人もいます。
（公式の挙動ではないので私は怖くて使えませんが…）

また、TRUE型検索は、内部的にはおそらくバイナリサーチ(二分探索)か
それに類するアルゴリズムで検索を行なっていると思われますから、
それを前提に考えればご質問のような挙動を「説明」することはできます。

■二分探索(ウィキペディア)
http://ja.wikipedia.org/wiki/%E4%BA%8C%E5%88%86%E6%8E%A2%E7%B4%A2

例えば、

●Case１

{"5/29","5/30","5/31","収入","支出"}　というデータについて、
"支出"をキーに二分探索した場合、

１．
真ん中の"5/31"をチェックすると、文字列同士の大小比較で　"5/31"<"支出"　なので、
"5/31"の後方、すなわち、{"収入";"支出"} の中に解があると判断。
２．
真ん中(偶数前取)の"収入"をチェックすると、"支出"<"収入"　なので、
"収入"の前方に解があると判断するが、"収入"の前方には要素がないので、
その直前の値"5/31"を近似解として返す。
----------------------
●Case２

{"5/29";"5/30";"5/31";"収入";"支出";空白}　というデータについて、
"支出"をキーに二分探索した場合、

１．
真ん中(偶数前取)の"5/31"をチェックすると、"5/31"<"支出"　なので、
"5/31"の後方、すなわち、{"収入";"支出";空白} の中に解があると判断。
２．
真ん中の、"支出"をチェックすると、"支出"="支出"　なので、解として返す。
----------------------
●Case３

{5;4;3;10;1;4;1;4;3;2}　というデータについて、
3をキーに二分探索した場合、

１．1<3　⇒　{4;1;4;3;2}
２．3<4　⇒　{4;1}
３．3<4　⇒　{}　⇒　1
で1を返す。

もう少し大きなデータでも手計算とVLOOKUPの戻り値を比較してみましたが、
やはり基本的には「偶数前取の二分探索」で動作するようです。

二分探索の場合、データの個数によってチェックするデータと順番が違ってきますから、
VLOOKUPの第二引数の範囲が違えば同じデータでも異なる値が返ります。

もちろん「たぶん、おそらく、そうだろう」という程度のハナシで、
「いつもそうである」「どのバージョンでもそうである」という保証はありません。

ご参考まで。長乱文陳謝。

お礼

興味深く読ませていただきました
ありがとうございました

回答No.4

nag0720

＃２です。すみません、私が間違っていたようですね。
Kyleさんの説明が正しいです。
少ないデータでしか検証していなかったので勘違いしてしまいました。

回答No.2

nag0720

＞検索の型がTRUEの場合は、検索対象のデータが昇順に並んでいないと
＞取れる値は保証できないということなのでしょうか？

検索の型がTRUEの場合は、上のセルから順に比較していって、
検索値と同じ---->その値を返す
検索値を超えた---->直前の値を返す

という仕様です。
検索途中で検索値を超えているセルがあるとそこで検索が終了してしまいます。
例えば、
1
2
4
3
のときに、3で検索しても、4と比較した時点で3より超えていると判断されて2が返ります。

詳しくは、ExcelのヘルプでVLOOKUPを調べてください。

回答No.1

nag0720

VLOOKUPの書式は、VLOOKUP(検索値,範囲,列番号,検索の型)　です。

この「検索の型」とは、
TRUE： 検索値が見つからない場合に、検索値未満で最も大きい値を返す。
FALSE： 検索値と完全に一致する値を返す。見つからない場合はエラー値 #N/Aを返す。

検索の型を指定しないとTRUEとみなされます。この場合は検索する列はソートされていることが前提です。

完全一致検索の場合は、検索の型をFALSEにしてください。

お礼

回答ありがとうございます。
検索の型がTRUEの場合は、検索対象のデータが昇順に並んでいないと
取れる値は保証できないということなのでしょうか？