vlookup

Excel関数であるVLOOKUPについて

vlookupの構文は、
(検索キー, 範囲, 番号, [並べ替え済み])です。

質問は、次の説明についてです。
並べ替え済みを TRUE に指定するか省略し、範囲の先頭列が並べ替え順でない場合、間違った値が返されることがあります。

これ（間違った値が返される場合）は、どのような場合なのでしょうか？
検索対象の列（指定した範囲の先頭列）が並べ替え済みにすることが必須なのでしょうか？

初心者で恐縮ですが、よろしくお願いします。

No.3 ベストアンサー 回答者： hallo-2007 回答日時： 2014/12/31 23:27

本当に、この説明は不親切ですよね。

Falseを選択した場合の理解は大丈夫でしょうか。
Flaseを指定すると完全一致ですので、並び替えの必要はありません。
但し、一致する検索値が存在しない場合に　エラーが表示されます。

Trueを指定する場合の例ですが。
以下は、定形外郵便料金の表です。
　A　　　B
重量　　価格
0　　　120
50　　 140
100　　 205
150　　250
250
ここで、Vlookup(50,A:B,True)の場合は　140円と希望の金額が表示されます。
falseを指定しても同じ結果です。
これを仮に　４０ｇを検索させます。
Vlookup(40,A:B,True)となります。結果は120円と希望の金額が表示されます。
Falseを指定するとエラーになります。
これが、True（近似値）の検索事例です。
この様な使い方の場合に元表が

　A　　　B
重量　　価格
50　　 140
150　　250
0　　　120
100　　 205
250
と順に並び替えが出来ていない表を使用した場合の説明をしてあります。
仮に　Vlookup(100,A:B,True)を検索させると
140円がでると思います。
１００ｇが５０と１５０の間にあるので、その上の行の１４０を返してきます。

Trueは、この表の様に、何グラムから何グラムまでと云った範囲があるときに
使うものだと覚えておけば良いでしょう。

この回答へのお礼

ご回答ありがとうございます。

とても分かりやすい例もあり、大変助かりました。
ありがとうございます。

お礼日時：2015/01/07 20:25

No.7 回答者： MarcoRossiItaly 回答日時： 2015/01/01 14:54

ご質問の趣旨は、「『間違った値が返されることがある』という現象は、その頻度を考えると、実務上、どの程度の問題になるのか？仮に関数があまり間違えないとすれば、データによっては、並べ変えないまま TRUE 型で使うことも考えられるか？」ということでしょうか？

答えは、「たまたま間違えないケースもあるかもしれないが、殆ど当てにはならない。したがって、並べ替えないまま使うべきではない。」です。


とにかく、まずはヘルプを見るようにします。その中の関数リファレンスの VLOOKUP に、「……並べ替えておく必要があります。そうしないと、正しく計算が行われません。」と明確に宣告されています。「行われないことがあります」というよりも、「行われません」という説明どおりです。

何事も、インターネットで検索して見付かった情報よりも、まずはヘルプの情報に基づいて判断したほうがよいと思います。ユーザーはいつでも、関数やソフトの仕様に従って利用する必要があります。

No.6 回答者： mike_g 回答日時： 2015/01/01 01:11

》 vlookupの構文は、

》 (検索キー, 範囲, 番号, [並べ替え済み])です
と断定し切っちゃってますが、Excel の“公式な”ヘルプ文としては[並べ替え済み]なんてな言葉は使ってない、というのが私の見解です。その部分は「検索の型」とか「検索方法」としているはずです。(原文では「range_lookup」かな？)
その部分の引数として実際に入力するのは TRUE(または 1 または省略)、あるいは、FALSE(または 0)です。

では、何に対して TRUE、FALSE なのかを考えるに、「(検索)範囲」が(昇順に)「並べ替え済み」になっているか？、ということなのです。


》 検索対象の列（指定した範囲の先頭列）が
》 並べ替え済みにすることが必須なのでしょうか？
検索キー以下の最も近い一致を返したい場合には、必須です。
ピッタンコ一致を返したい場合には、並べ替えしてもしなくてもＯＫです。

お分かりかな？

No.5 回答者： MackyNo1 回答日時： 2015/01/01 00:15

>並べ替え済みを TRUE に指定するか省略し、範囲の先頭列が並べ替え順でない場合、間違った値が返されることがあります。

これ（間違った値が返される場合）は、どのような場合なのでしょうか？

第4引数をTRUEまたは省略した場合は、二分検索という方法で検索値に該当する値がない場合はその値よりも小さい最大値を見つけます。

たとえば添付画像のように4行のデータがある場合、その中央のデータは4行目のデータになります。
この4行目の値が検索値よりも大きい場合は、それよりも下半分のデータの中央の値（2行目）の値を調べ、検索値よりも小さい場合はそのあとのデータの中の中央の値（6行目）の値を調べることになります。
添付画像では「=VLOOKUP(3,C:D,2)」の数式をF1セルに入力していますが、この4行目の値「5」が検索値の「3」よりも大きいので、2行目のデータを調べることになります。
さらに、この2回目の検索で、検索値よりも大きい場合は上、小さい場合は下半分のデータで検索を続けることになります。
添付画像のように7行のデータの場合は、2行目のデータが検索値よりも小さいので、最終的に3行目のデータが最終結果になるわけです（＝正しい値が返りません）。

＞検索対象の列（指定した範囲の先頭列）が並べ替え済みにすることが必須なのでしょうか？

この方法は、上記のようなアルゴリズムで検索していますので、基本的にデータが昇順に並べ替えられていないと正しい値を返しません(必須の操作です）。

この回答へのお礼

ご回答ありがとうございます。

大変ご丁寧なご回答ありがとうございます。

お礼日時：2015/01/07 20:27

No.4 回答者： mshr1962 回答日時： 2014/12/31 23:36

番号　参照

0　　　A
1　　　B
5　　　C
3　　　D
8　　　E
9　　　F
7　　　G

上のような場合、
検索キー　VLOOKUPの解
0　　　A
1　　　B
2　　　B
3　　　D
4　　　D
5　　　D　（C）
6　　　D　（C）
7　　　D
8　　　E　（G）
9　　　F

となります。（）内が並べ替えがあった場合の解ですね。
このように思わぬ解が変える場合があるのでTRUEの場合は、並び替えが必要となります。

この回答へのお礼

ご回答ありがとうございます。

例があり、助かりました。

お礼日時：2015/01/07 20:26

No.2 回答者： zongai 回答日時： 2014/12/31 23:08

=VLOOKUP(検索値，範囲，列番号，検索方法)

と認識していますが、「並べ替え済み」という説明もあるのかな。

間違った値が返されるとのことですが、「TRUE」でも「FALSE」でもそれに従った値が返るだけです。
並べ替えをせず、検索キーと同じものをヒットするなら「FALSE」を指定すればいいです。

もう少し具体的な内容が書かれてると回答しやすいです。

No.1 回答者： kkkkkm 回答日時： 2014/12/31 23:05

> [並べ替え済み]

ではありません。HELPに書かれているのは「検索の型」です。省略(TRUE)もしくはFALSEを型として指定します。

以下HELPより
検索の型 省略可能です。VLOOKUP を使用して検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを指定する論理値です。

TRUE を指定するか省略すると、検索値と完全に一致する値、またはその近似値が返されます。完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。
重要 検索の型に TRUE を指定するか省略する場合は、範囲の左端の列にある値を昇順に並べ替えておく必要があり、これを行わないと、正しい値が返されない場合があります。

検索の型に FALSE を指定する場合は、範囲の左端の列にある値を並べ替える必要はありません。

検索の型に FALSE を指定すると、検索値と完全に一致する値だけが検索されます。完全に一致する値が範囲の左端の列に複数ある場合は、最初に見つかった値が使用されます。完全に一致する値が見つからない場合は、エラー値 #N/A が返されます。

以上HELP

よく使われるのはは完全一致ですのでFALSEを指定しますが、数値の範囲で何かを区分したりする場合(1から10まではAと表示、11から20まではBと表示などのようなとき)はTRUEを利用します。