Excelのmatch関数エラー原因が分かりません

質問

match関数で「＃N/A」と表示される原因が分かりません。

例えば、セルB1に任意の文字列を書込み、セルC1に「=match(B1,A:A,0)」と書き込むと、「＃N/A」と表示されます。
ところがA列を選択し、「検索と置換」を使ってB1セルの文字列を「検索する文字列」の欄にコピー/ペーストして検索すると同じ文字列を持つセルが検索できてしまいます。

何が原因でこの様な事が起こるのか教えて頂けないでしょうか。
また、match関数でのエラー回避の方法がありましたら、これも併せてお願いします。

回答No.7

mshr1962

No.1です。補足は読みました。
文字列が原因かどうか確認するならB1に「=A5」のようにA列の値を表示させて
C1に正しい答えが表示されるか確認してください。

正しい答えが出る場合は、A列の文字列に問題があります。
見えない文字としてはスペースの他、外字、タブ、キャリッジリターン、ラインフィード等あります。
CLEAN関数を使って置換するか、手入力し直してください。
http://office.microsoft.com/ja-jp/excel-help/HP010062560.aspx

逆に「#N/A」が表示される場合は、シートまたはブックそのものが壊れてる可能性があります。
新しいブックを作成して、現在のシートの内容をコピーして同じ結果になるか確認してください。

回答No.6

MackyNo1

＞以上の処理を行っても問題が解決しないため、困っています。

確認ですが、「区切り位置」の操作は行ってみましたか？

それでもうまくいかないなら元データに何らかの表示されない文字コードが挿入されている可能性が考えられます（Webページをコピー貼り付けしたような場合）。
検索対象のA列のセルを参照する以下の数式を入力すると、どのような数字が返されるか調べてみてください。

=CODE(MID(A1,LEN(A1),1))

回答No.5

KURUMITO

スキャナーなどのデータがA列で使われているのでしたらB１セルに次の式を入力して下方にオートフィルドラッグします。

=TRIM(SUBSTITUTE(A1,CHAR(9),))

CHAR(9)はタブマークなどがあるのを消すためです。

その後にB列を対象にMATCH関数を使います。C1に検索の文字があるとして

=IF(COUNTIF(B:B,C1),MATCH(C1,B:B,0),"")

回答No.4

KURUMITO

「検索と置換」の操作では例えばB1セルに太陽と入力してあり、太陽を検索すればA列に太陽を含む文字があれば、例えば太陽光発電の文字についても検索されます。太陽以外の文字があっても検索されるのです。太陽の文字の前後に空白などがある場合でも検索されます。しかしながらMACH関数の場合には太陽の文字の前や後ろに空白があれば見かけはB１セルの文字と同じになっていてもエラーの#N/Aが表示されます。
太陽の文字の前後に空白や文字があってもエラー表示をさせないようにするためには次のような式を使用すればよいでしょう。

=MATCH("*"&B1&"*",A:A,0)

A列に太陽の文字がない場合でもエラー表示をしないようにするためには次のような式にします。

=IF(COUNTIF(A:A,"*"&B1&"*"),MATCH("*"&B1&"*",A:A,0),"")

もちろん太陽の文字の前後に空白や文字がある場合にはMATCH関数の対象外とするのでしたら、しかもエラー表示をさせないようにするのでしたら次のような式にします。

=IF(COUNTIF(A:A,B1),MATCH(B1,A:A,0),"")

回答No.3

MackyNo1

すでに皆さんから回答があるように、＃N/Aエラーの原因は検索文字とA列のデータが一致しない（同じ値でない）ためです。
また、、「検索と置換」を使ってB1セルの文字列を「検索する文字列」の欄にコピー/ペーストして検索すると、きちんとヒットするのは、検索する値が「文字列数値」の場合、または検索の「オプション」で「完全に一致するセルを検索する」にチェックを入れていないから発生します（部分検索で他の文字が含まれていてもヒットする）。

解決法は、文字列数字の場合は、A列を選択して「データ」「区切り位置」で「完了」します。
スペースが入っている場合は、A列を選択して、検索の「オプション」で「完全に一致するセルを検索する」にチェックを入れて、検索する文字列にスペースを入力し、置換後の文字列には何も入力せず「すべて置換」します。

お礼

丁寧なご回答、ありがとうございます。
説明不足な点がありまして、申し訳ありません。

補足

説明不足の点がありました。申し訳ありません。補足させて頂きます。

まずスペースの件ですが、A列およびB1セルにおいて、全角と半角スペースを一括変換で削除する処理をしてみました。
また、文字の全角/半角で問題になっている可能性も考えて、asc関数で、A列およびB1セルを半角文字にもしています。

検索の部分検索/完全一致は切り替えても違いはありませんでした。

以上の処理を行っても問題が解決しないため、困っています。

他に考えられる原因がありましたら、お願いします。

回答No.2

noname#204879

お示しの式 =match(B1,A:A,0) を次の式にしたらどうなりますか？

=MATCH(B1&"",A:A,0)
または
=MATCH(N(B1),A:A,0)

お礼

こんなに早く回答を頂いて、ありがとうございます。
説明不足な点がありましたので、No.3の方のところで補足させて頂きます。

回答No.1

mshr1962

考えられるのは
１．A:A列の値とB1の値が数値と文字列のため違うものと判断
２．A:A列の文字の前後にスペース等余分な文字列が含まれるため違うものと判断

お礼

こんなに早く回答を頂いて、ありがとうございます。
説明不足な点がありましたので、No.3の方のところで補足させて頂きます。