VLOOKUPを利用する場合にTRUE(近似一致)、FALSE(完全一致)で結果はどのように変わるのか検証

Excelピボットテーブル データ集計・分析の「引き出し」が増える本 [ 木村 幸子 ]

数値の場合

検索範囲が「昇順である」の場合（昇順に並び替えておかないと正確に検索できない）

• Excel VLOOKUP の「TRUE(近似一致)」または「FALSE(完全一致)」で、挙動がどのように変わるのか確認。

■前提条件

1. 正確にVLOOKUPを使用するために必要なこと。
   1. ①検索範囲が「昇順」でなければならない
   2. ②数値と文字列が混ざる場合は、文字列に統一すること

■「検索条件」の省略について

1. 【カンマ有で省略】
   1. = VLOOKUP (検索値, 検索範囲, 戻り値の列番号, ) ⇒「FALSE(完全一致)」となる(省略していないとみなされる)
2. 【カンマ無で省略】
   1. = VLOOKUP (検索値, 検索範囲, 戻り値の列番号) ⇒ 「TRUE(近似一致)」となる(省略しているとみなされる)

1. 違うのは最後の「,　(コンマ)」を付けるか付けないかです。
   1. ※「, (カンマ)」があることで、省略していないとみなされ、空白値「0(ゼロ)」（FALSE(完全一致)）があるとみなされます。

検索範囲が「昇順である」の場合

元データ1

A	B
1	値1
2	値2
3	値3

TRUE(近似一致)

数値	値
1	値1
1.1	値1
1.2	値1
1.3	値1
1.4	値1
1.5	値1
1.6	値1
1.7	値1
1.8	値1
1.9	値1
2	値2
2.1	値2
2.2	値2
2.3	値2
2.4	値2
2.5	値2
2.6	値2
2.7	値2
2.8	値2
2.9	値2
3	値3

FALSE(完全一致)

数値	値
1	値1
1.1	#N/A
1.2	#N/A
1.3	#N/A
1.4	#N/A
1.5	#N/A
1.6	#N/A
1.7	#N/A
1.8	#N/A
1.9	#N/A
2	値2
2.1	#N/A
2.2	#N/A
2.3	#N/A
2.4	#N/A
2.5	#N/A
2.6	#N/A
2.7	#N/A
2.8	#N/A
2.9	#N/A
3	値3

検索範囲が「昇順でない」の場合

元データ2

A	B
3	値3
2	値2
1	値1

TRUE(近似一致)

数値	値
1	#N/A
1.1	#N/A
1.2	#N/A
1.3	#N/A
1.4	#N/A
1.5	#N/A
1.6	#N/A
1.7	#N/A
1.8	#N/A
1.9	#N/A
2	値2
2.1	値1
2.2	値1
2.3	値1
2.4	値1
2.5	値1
2.6	値1
2.7	値1
2.8	値1
2.9	値1
3	値1

FALSE(完全一致)

数値	値
1	値1
1.1	#N/A
1.2	#N/A
1.3	#N/A
1.4	#N/A
1.5	#N/A
1.6	#N/A
1.7	#N/A
1.8	#N/A
1.9	#N/A
2	値2
2.1	#N/A
2.2	#N/A
2.3	#N/A
2.4	#N/A
2.5	#N/A
2.6	#N/A
2.7	#N/A
2.8	#N/A
2.9	#N/A
3	値3

VLOOKUP関数は、検索範囲が昇順(小さい順)にきちんと並んでいないと、不可解な、意味のない結果を返す。

※これは二分探索(バイナリサーチ)というアルゴリズムを利用しているためである。

VLOOKUPで、数値データを「近似一致」で検索した場合

（検索範囲を正確に昇順に整列した条件のもとで、）検索値を超えない範囲の最大値を返す。

検索範囲が昇順でない場合は、確実な動作が得られない。

正確に動作させるには、念のため、検索範囲の方を昇順で並べ替えておくとよいでしょう。

また、「検索値を超えない範囲の最大値」を戻り値とするため、実際上は「切り捨て」と同様の意味になります。もしこれを「四捨五入」にしたい場合には、

'=VLOOKUP(ROUND(A2),検索範囲,2,TRUE)

のように、あらかじめ検索値を四捨五入しておくとよいでしょう。

[ポイント最大23倍]マイクロソフト エクセル 2016 Microsoft Office Excel for Windows 1ユーザー2台用 オフィス 単体ソフト 永続ライセンス カード版【Windows用】

文字列の場合

元データ３

A	B
1ばん	1
1ばんめ2	1.5
1ばんめ3	1.8
2ばんめ1	2
2ばんめ2	2.2
2ばんめ大	2.9
3ばん1	3
3ばんめ2	3.3
3ばんめ９	3.6

TRUE(近似一致)

数値	値
1ばん	1
1ばんめ1	1
1ばんめ22	1.5
9ばんめ9	3.6
漢字	3.6
2番目	2.9
1	#N/A
2	#N/A
3	#N/A
1ばん	1
2ばん	1.8
3番	3.6
3ばん	2.9
2ばんめ大	2.9
1ばん	1
1ばんめ2	1.5
1ばんめ3	1.8
2ばんめ1	2
2ばんめ2	2.2
2ばんめ大	2.9
3ばん1	3

FALSE(完全一致)

数値	値
1ばん	1
1ばんめ1	#N/A
1ばんめ22	#N/A
1ばんめ9	#N/A
漢字	#N/A
2番目	#N/A
1	#N/A
2	#N/A
3	#N/A
1ばん	1
2ばん	#N/A
3番	#N/A
3ばん	#N/A
2ばんめ大	2.9
1ばん	1
1ばんめ2	1.5
1ばんめ3	1.8
2ばんめ1	2
2ばんめ2	2.2
2ばんめ大	2.9
3ばん1	3

VLOOKUPで、文字データを「近似一致」で検索した場合

（検索範囲を正確に昇順に整列した条件のもとで、）

文字列に対してVLOOKUPを使用した場合、近似値で検索すると「前方一致」となる。

近似一致の結果は、前方一致のよう

「検索値→範囲」　ではなく、「範囲の各値→検索値」の前方一致

複数が一致する場合はできるだけ多くの文字が一致するものが返っている

Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術