Vlookupそこにあるのに#N/Aが出る

vlookupに関して質問です。
質問１
目の前に検索してる文字列があるというのに、認識せず#N/Aが出ます。
・範囲設定ミス等はない　=vlookup(A1,Sheet2!A:E,5,0)のようにしてます
・改行コード等はなくLENと見た目が一致
・見えてるセルを選択しリターン押すと「エラー解消」することもあれば、反対にエラーになることもある。大抵は左詰めが右詰めに変わる。

何とかしてまともに認識してくれるようにしたいのです。

質問２
vlookupの結果が書いてあるセルをctrl+Cでコピーし、別シートでctrl+Fで検索ウインドウを出してペーストすると、末尾にスペースが追加されます。コピー元のセルにはありません。バグですか？

No.5 回答者： いしい 回答日時： 2015/06/23 22:46

> 見えてるセルを選択しリターン押すと「エラー解消」することもあれば、反対にエラーになることもある。

大抵は左詰めが右詰めに変わる

これはNo.1さんの回答の通り、
「数値が文字列となっているセルが編集→確定することによって数値になっている」ということだと思いますが、
セルの書式を「文字列」から「数値」に変更しても「文字列」として表示されることがあるようです。
そのセルはいったん編集モードにしてから確定すると「数値」として表示されるようです。

参考ページ
「数値を文字列として書式設定した場合の予期しない動作」
https://support.microsoft.com/ja-jp/kb/141765/ja

> 数値に文字列の書式を設定するには、以下の手順を実行します。
>
> 1. セルまたはセルの範囲を選択します。
> 2. [書式] メニューの [セル] をクリックします。
> 3. [表示形式] タブの [分類] で [文字列] をクリックし、[OK] をクリックします。
> 4. 選択したセルまたはセルの範囲に数値を入力します。
>
> 上記の手順を実行した後、書式を数値に戻した場合は、数値を再入力する必要があります。
>
> 注 : 数値の入力後にセルの書式を文字列数値形式に変更した場合、数値は残りますが、セル内で左揃えされて文字列として表示されます。

また、次のサイトも参考になるかもしれません。

「VLOOKUPは数値か文字列かでErrorになる」
http://excel-magic.com/post-1107/

> VLOOKUP関数使っている時に、検索値が範囲の中に絶対あるはずなのに#N/Aになる！っていうことありませんか？

No1の回答へのお礼を読んで、
セルの書式設定を全て「文字列」にしても#N/Aになっていると思ったのでノーブレークスペースを疑ったのですが、
スペースがノーブレークスペースでない場合は、
上記のページに書いてあることも確認したら良いかもしれません。

また、文字列表示になっている数字が数値表示に変わるとき、
文字列表示の時の末尾にあった通常のスペースは削除されるようです。

セルの書式設定を全て「文字列」にしても
「文字の配置」を設定してない状態で右詰めになっているとしたら、
再現方法が分かりませんので何とも言えませんが、
セルの書式設定を全て「文字列」にして
「文字の配置」を設定してない状態でセルの文字が全て左詰めになっていることを確認してから、
LENによる文字列の長さ（同じか否か）、=vlookupセルの状態（#N/Aか否か）を確認しないと混乱しそうです。
セルの書式を全て「数値」にして右詰めになっていることを確認してからの方が問題の原因を見つけやすい気もしますが…。

この回答へのお礼

ありがとうございます。
参照URL拝見しました。正にこれです。結果、clean関数で対処しました。clean関数は文字列操作する関数なので、恐らく戻り値も文字列なのではと思ってます。余計なコード削除して文字列に統一されるなら、これはいいと思い使ってます。

お礼日時：2015/07/30 20:38

No.4 回答者： WindFaller 回答日時： 2015/06/23 16:05

こんにちは。

#3さんのご指摘、私も同感です。ネットからコピー＆ペーストしたものに、そういう現象が現れることがあります。それで、私自身、常に使っているマクロ・ユーティリティにそういう対策のものがありますが、nbspに合わせて、作り変えてみました。

私が10年来使ってきたマクロですが、「*」の印がついたものは、新たに今回、nbspとして加えたもので、果たして有用なのかは分かりません。不要なスペースは、全・半角とも「 Replace(strVal, Space(1), "", , , 1)」で落ちているはずですし、不要なコードは、Clean関数で落ちるはずです。

'//
Sub SpaceDel()
　 Dim strVal As String
　 Dim c As Range
　 Dim Rng As Range
　 If Not Selection Is Nothing Then
　　　Set Rng = Selection ''範囲選択
　 End If
　 For Each c In Rng
　　　strVal = Application.Clean(c.Value)
　　　strVal = Replace(strVal, Space(1), "", , , 1)
　　　strVal = Replace(strVal, ChrW(160), "")
　　　strVal = Replace(strVal, ChrW(8194), "")　'*
　　　strVal = Replace(strVal, ChrW(8195), "")　'*
　　　strVal = Replace(strVal, ChrW(8201), "")　'*
　　　strVal = Replace(strVal, ChrW(8203), "")　'*
　　　c.Value = Trim(strVal)
　 Next
End Sub
'///

p.s.それら(*)の文字は、VBAから、ワークシートには入らないようでした。

この回答へのお礼

いつもありがとうございます。
取り合えずclean関数使って、出てない様なので様子見です。後ろ向きな対処になってしまいました。申し訳ありません。

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

No.3 回答者： いしい 回答日時： 2015/06/23 11:32

> 別シートでctrl+Fで検索ウインドウを出してペーストすると、末尾にスペースが追加されます

そのスペースは「ノーブレークスペース」ではないですか？
検索ウインドウにペーストして末尾に追加されたスペースで、Sheet2!A:E内を検索してみてはいかがでしょう？
#N/A となる原因も分かるかもしれません。

LEN()の結果が一致ということでノーブレークスペースの問題ではないのかもしれませんが、
文字列の前にあったり、後ろにあったりで偶然一致しているのかもしれませんし…。
=vlookup(A1,Sheet2!A:E,5,0)でしたら、
A1の文字列の前にノーブレークスペースがあり、
Sheet2!A:E内のセルの文字列の後ろにノーブレークスペースがあると、
長さと見た目が一致していても #N/A になりそうです。
A1の文字列の後ろにノーブレークスペースがあり、
Sheet2!A:E内のセルの文字列の前にノーブレークスペースがある場合は、
#N/A にはならないかもしれません。
私のExcel2007の一つのシート内で実験してみたらそんな感じでした。
理由はよく分かりません。

この回答へのお礼

ありがとうございます。
よく考えたら、検索範囲ばかり気を取られ検索文字列はノーマークでした。ノーブレークスペースも調べてみます。

お礼日時：2015/06/30 22:45

No.2 回答者： chonami 回答日時： 2015/06/20 20:07

後から書式設定を変更しても、あくまでも見た目の書式(横位置など)を変えただけでデータ型が変わった訳ではありません。

書式を変えて更に再確定した段階でデータ型が変わります。
区切り位置の操作はデータ型を一斉に変える操作です。

この回答へのお礼

返事が遅くなりました。
区切り位置で確かに変わったようです。ただ、これまでvlookupにヒットしなくてもCtrl＋Fでヒットしてた文字列が、区切り位置によりかからなくなりました。vlookupでもかかりません。桁落ちが起きたかも知れません。もう少し調べます。

お礼日時：2015/06/30 19:56

No.1 回答者： chonami 回答日時： 2015/06/19 11:52

>大抵は左詰めが右詰めに変わる。

ということは、数値が文字列となっているセルが編集→確定することによって数値になっているということですね。
VLOOKUP関数は数値と文字列を厳密に区別しますので、マスタと検索値のデータ型が違っているとエラーになります。
どちらが文字列でどちらが数値なのかわかりませんが、例えば左詰めになっているセル（もしくは列）を選択し
データ→区切り位置→完了
とすることで、文字列の数字を一斉に数値に変えることはできます。

質問2の内容は式自体を見てみないとよくわからないので、数式バーからそのまま貼り付けていただくことはできますか？

この回答へのお礼

ありがとうございます。
確認したいのですが、ctrl+1でセルの書式設定を出し「文字列」を選択、この操作により文字列に変わると思っていいのですよね。一応、全部確認して文字列にしてる筈なんですが。。。

お礼日時：2015/06/20 19:44