【説明画像あり】VLOOKUPで、情報が呼出せない

質問

タイトルの件、質問いたします。

まず、下記のような、関数を組んでいます。

呼出ブック【図１、図２】：B2セル↓↓
=IF(ISERROR(VLOOKUP(SUBSTITUTE($B$1,"-",""),[検索先.xls]Sheet1!A:C,2,FALSE)),"",VLOOKUP(SUBSTITUTE($B$1,"-",""),[検索先.xls]Sheet1!A:C,
※本関数は、教えてgoo回答者様にご教授いただきました。

呼出ブックのB1セルに、検索先ブック_Sheet1【図３、図４】のNOを入力すると、B2セルに情報が呼び出されます。

例：呼出ブックB1セルに　20012　と入力すると　呼出ブックB2セルに　佐藤　と表示される
例：呼出ブックB1セルに　20013　と入力すると　呼出ブックB2セルに　鈴木　と表示される

しかし、

図３のとおり

検索先ブックの、A3【20012】、A4【20013】は、呼出されますが
検索先ブックの、A2【20011】　　　　　　 は、呼出されません。←【問題】

理由は、図５のとおり

A2とA3、A4セルを、よく見ると、違いがあります。
A2セル　　　　には、何のエラーもありませんが
A3セル、A4セルには、緑色の三角のマークがあります。

図３のとおり、A3セル、A4セルには、！マークがありクリックすると
【数値が文字列として保存されています】とあります。
図４のとおり、セルの書式設定で調べると、確かに表示形式が、文字列となっています。

エラーの指示に従い、A3、A4セルを、【数値に変換する】にすると
A2セルのように、エラーがなくなります。しかし、Vlookup関数が、きかなくなり、呼出ブックに
番号を入力しても、呼び出されなくなります。

セルの書式設定で、表示形式を、【文字列】、【標準】、【数値】として変換しますが、まったく、呼び出せません。

検証した結果、
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
表示形式を何に変更するというより、
緑サンカクの、エラー表示があるものは、呼出される　【どんな、表示形式でも：文字列、標準、数値】
緑サンカクの、エラー表示がないものは、呼出されない【どんな、表示形式でも：文字列、標準、数値】
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

ご存知の方、vlookup関数が、呼出せる、セルの書式設定を教えてください。宜しくお願いします。

【エクセルバージョン】
2007、2003では確認していませんが、今後は、運用します。
ファイル系先は、xlsで保存しています。

【図４の詳細】
A B
1　20011　佐藤
2　20012　鈴木
3　20013　高橋

回答No.2

keithin · ベストアンサー

今までの数式は，緑の付いている「文字列」を呼び出せる数式にしてあります。
これはあなたのこれまでのご相談の添付画像で，「ゼロから始まっている（緑の付いた）数字」が特に目立ったからです。
ゼロから始まる数字は，原則として緑を付けて文字列にしておかないと，ゼロから記入できません。
しかしこの数式では，今回ご質問のように緑のついていない数字のセルは呼び出せません。

逆に緑の付いていない数字を呼び出したい場合，数式の方を変えないといけません。
「どっちも区別なく呼び出せる」VLOOKUPの使い方はありません。

対策としては
方法１）全ての数字を緑付きの文字列に統一する【推奨】
方法２）今の数式を更に複雑にして，緑が付いた場合と緑が付いてない場合をそれぞれ想定してどっちが来ても呼び出せるように仕込みを増やす【簡単ですが推奨できません】
方法３）実はゼロから始まる数字を呼び出すというこれまでのサンプルがウソだったので，今回のサンプルの20012のようにゼロから始まらない「数値」に統一する【全てやり直すならこれも良い】

のどれかになります。

【推奨】
緑の付いていないＡ列を列選択
データメニュー(またはデータタブ）の区切り位置を開始，
何も指定せず次々送り，ウィザードの3/3画面で明示的に「文字列」の設定にマークしてＯＫする
緑が付いたことを確認する
数式は今まで通りで使う

【すべてやり直す】
緑の付いているＡ列を列選択
データメニュー(データタブ）の区切り位置を開始，
何も指定せず次々送り，ウィザードの3/3画面で明示的に「標準」の設定をマークしてＯＫする
緑が消えたことを確認する
数式は
IF(ISERROR(VLOOKUP(SUBSTITUTE($B$1,"-","")*1,[検索先.xls]Sheet1!A:C,2,FALSE)),"",VLOOKUP(SUBSTITUTE($B$1,"-","")*1,[検索先.xls]Sheet1!A:C,
とする。

おまけ：
この場合に「ゼロから始まる数字」をセルに表示するには
セルの書式設定の表示形式のユーザー定義で
00000000
などのように設定を付けて表示させる

参考：
単純に前回の応用で，
=IF(ISERROR(VLOOKUP(SUBSTITUTE($B$1,"-","")*1,[検索先.xls]Sheet1!A:C,2,FALSE)),"",VLOOKUP(SUBSTITUTE($B$1,"-","")*1,[検索先.xls]Sheet1!A:C,
＆IF(ISERROR(VLOOKUP(SUBSTITUTE($B$1,"-",""),[検索先.xls]Sheet1!A:C,2,FALSE)),"",VLOOKUP(SUBSTITUTE($B$1,"-",""),[検索先.xls]Sheet1!A:C,
と更に式を倍に増やせば，緑でも緑無しでもどっちが来ても対応できます。

回答No.3

imogasi

エクセルのほんとに基本的なことが判ってないので、無駄な労力を費やしている。
こういうのはスクールでも行ってじっくり学ぶか、経験を積むうちに会得するか、するのだろう。
ーー
エクセル関数で
（１）対象にするのは、「セルの値」です。
（２）セルの情報は、セルの値のほかに、表示形式や、数式や、コメントや、入力規則ほか色々あります。
（３）このうち、エクセル関数で対象にするのは「セルの値」だけです。
結果もそのセルの「セルの値」だけしか、返し様が無いのだ。
（４）だからセルの表示形式で、文字列に見せても、解決にならない。
セルの値の数値を文字列に変えるにはTEXT関数などがあり、数字文字列を数値に直すかんすうもある。
しかし計算ソフトが主流のエクセルでは、数字文字列を数値化するお節介場面が相当あるので、つい両者の違いに鈍感に也勝ち。
VLOOKUPは記を気化してくれない比較をおこなう。
（５）関数の対象とは、関数の「引数」といわれるもの（関数の位置）で指定するデータで、主にセルの番地で表されるセルの値です。
ーー
そのほかに、
（６）エクセルのセルの値には、主なものは、数値と文字列で、日付も数値で表されます。
この区別が大切なことを気づいていない。
ーー
VLOOKUPの検索表で行ごとにバラバラなら統一するほか無い。
（１）元データを修正するか、（２）関数で統一するか。
別列に文字列をだすか、式の第１引数の部分で変換するか。
例
G1:H2に
111 a
123 b　＜－G２は’１２３ー数値
ーー
A２：A3にデータがあるとして
B2の式は　=VLOOKUP(VALUE(A2),$G$1:$H$2,2,FALSE)
123 b <--数の123
123 b　　＜－'123 で文字列
（３）検索表に両方持つか
G1:H3
111 a
123 b　＜－－G2は数値
123 b　＜－－G3は文字列（’付き）
ーー
上と同じく
123 b　＜ーーA6は値
123 b　＜－－A6は文字列
ーー
式は=VLOOKUP(A6,$G$1:$H$3,2,FALSE)
ーーー
検索表のセル番地は＄を漬けておいたほうが良い。

回答No.1

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

数値(計算できる値)と数字(文字列)の違いを認識しましょう
数式で表すと =1="1" はFALSEとなり、検索できないと考えます

で、ど知らかに統一する必要があると思います。
計算する可能性があるなら数値で統一し、必要ないなら数字にしてはどうでしょうか？

セルの書式設定で書式を変更しても数値と数字の変換は完成しませんので、
別列に計算後、コピー＆値の貼り付けなど、もう一捻り必要となります。