#N/Aの表示、非表示について

質問

excel2010

あるwebサイトにvlookup関数の高速化という内容が掲示されていたので、
それを使用してみました。
そこで、ちょっとおかしな状況になっているので、
それに関する質問です。

構成
SHEET5に下記の様なデータがあります。
A列にALLコード、B列にデータです
ALLコード　　　　　　　　　　データ
0109S10100100B60　　　722
0110H10100110B32　　　18
0110S10100020B62　　　0
0110S10100040B62　　　77
0110S10100050B62　　　19
0110S10100070B62　　　35
0110S10100090B62　　　16
0110S10100100B60　　　200
0111H10100110B32　　　41
0111S10100030B62　　　68
0111S10100040B62　　　70
0111S10100050B62　　　32
0111S10100070B62　　　60
0111S10100080B62　　　17
0111S10100090B62　　　25
0111S10100100B60　　　100
0112H10100110B32　　　14
0112H20100020B41　　　11
0112H20100030B42　　　89
0112H20100040B43　　　23
0112H20100050B42　　　27
0112H20100070B44　　　5
0112H20100080B40　　　17
0112S10100010B62　　　11
0112S10100020B62　　　12
0112S10100030B62　　　69

上記ALLコードの先頭４桁はシリアル番号になっており、
実際のコードは、それ以降の12桁なので、異なるシートにVLOOKUP関数で下記の様に
マトリクス表にしました。
A列：コード
B列：0109
C列：0110
D列：0111
でB2セルに
=IF(INDEX(Sheet5!$A$2:$A$1000004,MATCH(CONCATENATE(B$1,$A2),Sheet5!$A$2:$A$1000004,1),1)=CONCATENATE(B$1,$A2),VLOOKUP(CONCATENATE(B$1,$A2),Sheet5!$A$2:$B$1000004,2,TRUE),NA())
という式を入れて、対象範囲B2:D15までコピーしています。

コード　　　　　　　　　　0109　　　0110　　　0111
S10100081B62　　　#N/A　　　#N/A　　　#N/A
S10100091B62　　　#N/A　　　#N/A　　　#N/A
S10100110B69　　　#N/A　　　#N/A　　　#N/A
S10100100B60　　　722　　　200　　　　100
S10100120B60　　　#N/A　　　#N/A　　　#N/A
S10100010B62　　　#N/A　　　#N/A　　　#N/A
S10100020B62　　　#N/A　　　0　　　　　#N/A
S10100030B62　　　#N/A　　　#N/A　　　　68
S10100040B62　　　#N/A　　　77　　　　　70
S10100050B62　　　#N/A　　　19　　　　　32
S10100060B62　　　#N/A　　　#N/A　　　#N/A
S10100070B62　　　#N/A　　　35　　　　　60
S10100080B62　　　#N/A　　　#N/A　　　　17
S10100090B62　　　#N/A　　　16　　　　　25

N/Aがあると見づらいので、
式のNA()を削除し、
B2セル
=IF(INDEX(Sheet5!$A$2:$A$1000004,MATCH(CONCATENATE(B$1,$A2),Sheet5!$A$2:$A$1000004,1),1)=CONCATENATE(B$1,$A2),VLOOKUP(CONCATENATE(B$1,$A2),Sheet5!$A$2:$B$1000004,2,TRUE),)
で対象範囲コピーしたところ、下記の様な結果になりました。

コード　　　　　　　　　　0109　　　0110　　　0111
S10100081B62　　　#N/A　　　　　0　　　　　　0
S10100091B62　　　#N/A　　　　　0　　　　　　0
S10100110B69　　　0　　　　　　　0　　　　　　0
S10100100B60　　　722　　　　　200　　　　　100
S10100120B60　　　0　　　　　　　0　　　　　　　0
S10100010B62　　　#N/A　　　　　0　　　　　　0
S10100020B62　　　#N/A　　　　　0　　　　　　0
S10100030B62　　　#N/A　　　　　0　　　　　　68
S10100040B62　　　#N/A　　　　　77　　　　　　70
S10100050B62　　　#N/A　　　　　19　　　　　　32
S10100060B62　　　#N/A　　　　　0　　　　　　　0
S10100070B62　　　#N/A　　　　　35　　　　　　60
S10100080B62　　　#N/A　　　　　0　　　　　　　17
S10100090B62　　　#N/A　　　　　16　　　　　　25

0110と0111は正しく表示しているのですが、0109列は#N/Aが残っています。
コピーペーストを間違ったわけではありませんが、
何故0109だけ#N/A表示が残っているのか、想定される原因を教えていただきたく。

ちなみにALLコード先頭に
0108S10100100B60 888
を追加して１列増やすと、
コード　　　　　　　0108　　　0109　　　0110　　　0111
S10100081B62　　　#N/A　　　0　　　　　0　　　　　0
S10100091B62　　　#N/A　　　0　　　　　0　　　　　0
S10100110B69　　　0　　　　　0　　　　　0　　　　　　0
S10100100B60　　　888　　　　722　　　　200　　　100
S10100120B60　　　0　　　　　0　　　　　0　　　　　　0
S10100010B62　　　#N/A　　　0　　　　　0　　　　　0
S10100020B62　　　#N/A　　　0　　　　　0　　　　　0
S10100030B62　　　#N/A　　　0　　　　　0　　　　　68
S10100040B62　　　#N/A　　　0　　　　　77　　　　　70
S10100050B62　　　#N/A　　　0　　　　　19　　　　　32
S10100060B62　　　#N/A　　　0　　　　　0　　　　　　0
S10100070B62　　　#N/A　　　0　　　　　35　　　　　60
S10100080B62　　　#N/A　　　0　　　　　0　　　　　17
S10100090B62　　　#N/A　　　0　　　　　16　　　　　25
の様になり、0109の#N/A表示は解消されました。
EXCELの仕様なのでしょうか？
とりあえず、ダミー列のデータ挿入で非表示にすれば、
見た目上は、すっきりですが、何か異なる方法があれば教えていただきたく。
※実際は、もっと行・列共に数が多いのですが、抜粋で例をあげています。

回答No.2

Chiquilin · ベストアンサー

高速化したいから二分検索（TRUE検索）にしたということでしょうか。
確かにその量なら分かりますけど……

ただエラーの条件指定が不足しています。Sheet5の A列は昇順にデータが
並んでいますが 先頭の A2よりも小さな値を検索値にした場合にもエラー
が起きます。

=IF(B$1&$A2<Sheet5!$A$2,"☆",IF(IFERROR(VLOOKUP(B$1&$A2,Sheet5!$A$2:$B$1000004,1,TRUE)=B$1&$A2,FALSE),VLOOKUP(B$1&$A2,Sheet5!$A$2:$B$1000004,2,TRUE),"★"))

こうでしょうか。もちろん Sheet5の先頭に「0」みたいなダミーの値を入れ
ておく手もありますけど。

お礼

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

0のダミーではなく計算式を組み込んで対応しました。
ばっちりでした。
こんなに早く回答いただき助かりました(^^)

回答No.4

chie65536（@chie65535）

＞何故0109だけ#N/A表示が残っているのか、想定される原因を教えていただきたく。

それは「MATCH関数が#N/Aエラーを返すから」です。

＞あるwebサイトにvlookup関数の高速化という内容が掲示されていたので、それを使用してみました。

そのページにある「高速化」の意味、判ってますか？

これの意味は

「VLOOKUP(CONCATENATE(B$1,$A2),Sheet5!$A$2:$B$1000004,2,FALSE)と書いて、完全一致する物を探すと、VLOOKUPは、検索される表の全件を対象に探すので、処理が遅くなる。なので、INDEXとMATCHを使って、完全一致する場合だけ『全件検索を行なわないVLOOKUP』を呼び出して、さもなければNA()を表示する」

です。

ここでのキモは「VLOOKUPの検索の型がTRUEになっている」と言う事です。

この「検索の型」が「TRUE」になっていると、VLOOPUPは「全件検索をせず、検索している表のデータが検索値を超えた時点で、検索を打ち切る」のです。

つまり「検索を途中でやめてしまう分、高速化する」のです。

で、この「高速化関数」では「MATCHで一番近い値を探してみて、探した結果が完全一致するかどうか」という細工をしています。

この「MATCHで一番近い値を探してみて」は、検索の型に「1」を指定していて「検査値以下の最大の値」を探すようにしています。この場合も、MATCHは「検索値を超えたら、検索を打ち切る」ので、高速です。

ここで、もし「検査値以下の最大の値」が「無かった」ら、どうなるでしょうか？

その時は「MATCH関数が#N/Aを返す」のです。

「MATCH関数が#N/Aを返す」と、どうなるかと言うと「式の結果が#N/Aになる」と言う事です。

「式の結果が#N/Aになる」と言うのは「絶対的な事象」ですので、式の一部を書き換えても無駄です。つまり「式からNA()を外しても無駄」です。

では、どうすれば良いのかと言うと

・検索される表の１行目に、どんなデータよりも小さい値を入れておいて、MATCH関数が#N/Aを返す事が無いようにする

・IFERROR関数、IF関数などを用いて、MATCH関数がエラーを起こす場合を除外する

などの方法を取らないといけません。

お礼

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

凄くわかりやすい解説で理解できました(^^)

回答No.3

f272

> EXCELの仕様なのでしょうか？

その通りです。
MATCH(CONCATENATE(B$1,$A2),Sheet5!$A$2:$A$1000004,1)
というようにMATCＨ関数を使っていますが，検査値以下の最大の値が検索されます。
"0110..."の場合は完全一致が見つからなくてもそれよりも小さいものがありますから，それを返しますからとりあえずNAにはなりません。”0109...”を探そうとすると返すものがありません。
対処法としては
Sheet5の最初の行に
0000000000000000 0
のようなダミーを入れておけばよいでしょう。

お礼

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

対処法まで参考になりました。

回答No.1

kkkkkm

Vlookupではなく他の関数の結果で#N/Aが出てるのだと思いますので、それぞれの関数で正しく結果が返されているか調べてみてはいかがでしょう。

もしくはIFERROR関数と使うとか。

お礼

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

列を増やしたときに#N/Aが0になる”からくり”が知りたかったのです。