VLOOKUP関数で検索できません

質問

A列に勤務時間帯、B列に人数を入力したリストがあります。
E1にVLOOKUP関数を入力しましたがエラーになるので困っています。

A1 → 8:00~18:00　　B1 = 5
A2 → 9:00~19:00　　B2 = 7
D1 → 8:00~18:00
E1 → =VLOOKUP(D1,A1:B2,2,0)

チルダを抜くと検索できるようになりますが、
VLOOKUP関数でチルダは使えないのでしょうか。
チルダありでも検索できるうまい方法があれば教えて下さい。

回答No.4

KURUMITO · ベストアンサー

Ｅ1セルには次の式を入力してみてはいかがでしょう。
式を確定する段階でＣｔｒｌキーとＳｈｉｆｔキーを押しながらＥｎｔｅｒキーを押します。

=VLOOKUP(SUBSTITUTE(D1,"~",""),SUBSTITUTE(A1:B2,"~",""),2,0)

お礼

ご回答ありがとうございます。
おかげさまで教えていただいたとおり、SUBSTITUTEでうまく処理できました。
質問での表は、質問しやすくするために簡素化してあります。
なおSUBSTITUTE(D1,"~","~~")として特に配列数式は使いませんでした。

回答No.7

noname#175206

もしくは、D1にはチルダを二つ並べて「8:00~~18:00」としてもいいかと。そうすると、A1の「8:00~18:00」が検索ヒットとなって、E1に「5」と出ます。

　検索キーとするデータのほうにチルダを二つ連続すれば、検索元のデータの一つチルダを文字として検索できるようです。

お礼

何度もご回答いただき大変ありがとうございました。
色々と参考になりました。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

数式で対処しても良いですが
~~で~を～に置換してしまったほうが良いように思います
添付図参照

お礼

ご回答ありがとうございます。
おっしゃるとおりですね、「～」に置換した方が見栄えも良くなるし・・・
ただ、実際の帳票は共有で使うもっと複雑なものでして、A列はかってに書き換えできない事情が
あります。そのことを質問するとき書いておくべきでした。すみません。

回答No.5

keithin

=VLOOKUP(SUBSTITUTE(D1,"~","~~"),A:B,2,FALSE)
として，~を~~に変えて検索させるとヒットします。

半角チルダは次に続くワイルドカードをワイルドカードと見なさないという特殊機能を持った文字のため，ご質問のような変な挙動となります。

お礼

ご回答ありがとうございます。
ご教示のとおりSUBSTITUTE関数でうまく処理できました。

回答No.3

chie65536（@chie65535）

因みに、チルダの次の文字が「?」「*」「~」でない場合、最初の「~」は無視されます。

なので、D1セルに「8:00~18:00」と入れると、「~」が無視されて「8:0018:00」を探しに行きます。

「~」を検索するには「~~」にする、というのは、良く忘れるので、チルダは使わないで、全角の「～」でも使った方が良いでしょう。

以下、蛇足ですが。

本来、チルダは「○○から××」のように範囲を表す場合に使う文字ではありません。

チルダの本来の意味は、スペイン語やポルトガル語、ベトナム語などで、 アルファベットの上に付して特殊な発音を指示する記号です（母音の上に書く「N」が由来で、「N」が変形して「~」になったと言われている）

「○○から××」のように範囲を表す場合は「○○～××」のように、全角の「～」を使うべきです。

そういう意味から言えば「チルダを使うのは、二重の意味で間違っている」と言えます。

お礼

再度にわたり丁寧なご説明ありがとうございます。
A列の時刻については、どんな文字を使うか強制することができないという事情があります。
そのため今回のトラブルが起こったのですが、おかげで勉強になりました。
ありがとうございます。

回答No.2

noname#175206

エクセルはチルダを機能を持つ特殊記号としています。予約記号ということですね。

http://support.microsoft.com/kb/214138/ja

　そのため、エラーとなるのでしょう。チルダ（~）の代りに、たとえばハイフン（-）だと動作します。

　全角文字の「～」はチルダとはならないので、全角文字でもよければ、「～」を使用することもできます。

お礼

ご回答ありがとうございます。
事情があって全角の「～」に置き換えることができません。
教えていただいたサイトが勉強になりました。

回答No.1

chie65536（@chie65535）

チルダ「~」は「検索時、ワイルドカード文字をワイルドカード文字として扱わず、単なる文字として扱う場合の、特殊文字」です。

セルに

123
12*
124
12~
134
1*1

と言うデータが入っている場合、「12*」で検索すると、「123」「12*」「124」「12~」の４つが一致します。

「12*」だけを検索する時は「*をワイルドカード文字にしない」ように、*の前に~を付けて「12~*」で検索しなければなりません。

同様に「12~」を検索する時は「~がワイルドカードを無効にする文字になってしまう」ので、~に更に~を付けて「12~~」で検索しなければなりません。

D1セルに「8:00~~18:00」のように「チルダを２つ連続して入力」してみましょう。

なお、同じ事が「?」と「*」でも起きるので、探す値に「?」や「*」を含む場合は、「~?」、「~*」と入力しなければなりません。

お礼

お礼が遅くなりました。ご回答ありがとうございます。
ワイルドカード用の特殊文字ということで納得しました。
なるほどです。