VLOOKUP 関数について教えてください

Question

1.営業部員の年齢構成を把握すべく”営業部員リスト”を作り、1列目にカタカナで姓・名　を記入し、2列目には同一Book内の別Sheet”社員リスト”からVLOOKUP関数で年令を得ようとしています。

2．”社員リスト”の1列目はカタカナで書いた姓・名で昇順に整列されています。

3．”営業部員リスト”の2列目以下には、下記の関数式を入れています。

　　’＝VLOOKUP（G5,’社員リスト’$A$5:$H$120,5,TRUE)

4.1度目のトライアルは、一見巧く出力できた（＃N/Aは出なかった)と思っていたところ、ある社員の年令が不自然なことに気づき、調べたところこの社員の姓・名の間にスペースが入っていない事が分かりました。（他は全角のスペースがある）

5．対策1：急いでスペースをいれたら”半角”だったがすぐには気付かず、検索結果には＃N/Aは出ていなかったので安心していたら結果はまたもや不自然な数値になっていました。

6．対策2：半角スペースの後ろに更に”半角スペース”を加え、「一見全角スペース」にしたところ、検索結果が変化したがこの数値も正しいモノではなかった。

　この時、この検索値をEXACT関数で監視していたら、ちゃんと”FALSE”と表示されていました。

　　―――当たり前！！ですね。　人の目はごまかせても、機械はそうは行かないですね。

7．対策3：文字バーを全角に替えて”全角スペース”を入れ直したら、正しく検索されました。

＜疑問点＞関数の説明では、検索の型がTRUE の時、『検索値がない時、検索値未満で最も大きい

　　値が返される』とありますが、検索値が文字列の場合、”未満”とか”最も大きい”と言う概念が分かりません。最初の文字の文字コード？ですか。今回の失敗では半角スペース1個の場合と2個の場合で検索結果が異なっていました。どう理解すれば良いのか？？分かりません。＃N/Aが出ないから、間違いに気付かず、誤ったレポートを作成してしまうことになります。

※添付の絵ではアラキカツミ　や　ザイツテルオ、ハシモトヒロミチ等は姓と名の間のスペースは

　無いにも拘わらず、検索結果は間違っていません。

　ヤマダコウジロウはスペースが無いため？検索結果は間違っています。

　　理解不能です。　教えて下さい。

[モデレーター注]

・タイトル/質問本文に VLOOKUP関数とあり Excelに関する質問と見做し カテゴリを移動しました。

移動前 : Outlook.com | 電子メール | 添付ファイルウィ送信及び受信する

　　　　　　　　　　   　　↓

移動後 : Office | Outlook | その他/不明 | 不明/その他

Answer 1

確かに、VLOOKUPで文字列は認識しません。なので一番最初に社員番号などの値でリストを作成し検索させる必要があります。（リストに無い場合や文字列が入力されたら、検索できないので”ＮＧ”である”＃Ｎ/Ａ”として返事が来ます）

Answer 2

VLookupに拘らなければ、添付画像のように

=IFNA(INDEX(社員リスト!$A$2:$C$4,MATCH(1,INDEX(1/(社員リスト!$A$2:$A$4=A2),0)),3),"該当なし")

といった計算式にすることで

リストの並びを気にせずに使えるんじゃないかと思います。

（式が長く、ちょっと難解なのが気にはなります。）　m(_ _)m

Answer 3

＞VLOOKUPで文字列は認識しません。

回答が何か間違えた方向に行っているようです。

検索値はもちろん文字列でも問題ありません。

＞3．”営業部員リスト”の2列目以下には、下記の関数式を入れています。

　　’＝VLOOKUP（G5,’社員リスト’$A$5:$H$120,5,TRUE)

この場合、完全一致で検索するのですから、VLOOKUP関数の第4引数をTRUEにするのは、間違えています（FALSEにしてください）。

＞4.1度目のトライアルは、一見巧く出力できた（＃N/Aは出なかった)と思っていたところ、ある社員の年令が不自然なことに気づき、調べたところこの社員の姓・名の間にスペースが入っていない事が分かりました。（他は全角のスペースがある）

TRUEにすると『検索値がない時、検索値未満で最も大きい値が返される』のですが、検索値が文字列の場合は文字コードを基準にした大小関係で判定します。

したがって、この場合は（そもそも名前のあいまい検索はないのですか、その文字列をコード順に並べ替えたデータで近似検索するなら）社員リストのA列のデータを文字コード順に昇順に並べ替えておく必要があります。

＞5．対策1：急いでスペースをいれたら”半角”だったがすぐには気付かず、検索結果には＃N/Aは出ていなかったので安心していたら結果はまたもや不自然な数値になっていました。

そもそも上記のような昇順のデータベースになっていないので、間違えた値が返ることになります。

なお、きちんと昇順にしたデータで＃N/Aエラーが出るのは、すべての文字列よりも前のコードの文字列を検索した場合です(検索値未満のデータがないときだけ）。

それ以外のデータを入力した場合は、検索値よりも文字コードが前で最後の文字列が必ずヒットします。

ちなみに、VLOOKUP関数で第4引数をTRUEにしたときの検索方法は、社員リストのA列のデータの中央の値が検索値よりも大きければ（文字列の場合は文字列全体の文字コードで判定）、その中央のセルと最終セルの真ん中のセルを検索し、小さければ、その中央のセルと最初のセルの真ん中のセルを検索して大小関係を判定して、最終的に目的セルを見つけるような検索方法（二分検索）を採用しています。

第4引数がFALSEの場合は、逐一検索といって上から順に該当セルを探しますが、二分検索では、リストのデータが昇順に並んでいないと、全く不明の値が返ることになるわけです。

Answer 4

VLOOKUPで大切なことは、”文字列”なのか”値”なのかです。同じ様に作ったものでも、検索値が文字列と認識（簡単に言ったらExcelのセル番地の中が通常の表示で左側に寄った状態）されたらエラーで返って来ます。

Answer 5

Windows3.1使用中に、日刊工業新聞刊の”極意書”（'92年初版）を読んでExcelが大好きになりました。その後Excel・2002関数Pocketmaster（'01年初版エクスメディア刊）を拠り所にしていました。VLOOKUPは暫く使っていなかったのですが、つい先日”検索値として文字列もOK”と言う説明を読みながら作業していたところ、間違いデータの”ご本人”から指摘されて、1週間ほど、休み休み・悩ンだ末に、このコミュニティにたどり着いたという次第です。Windowsと言うより、ExcelのVer.によって基本構造が異なるのでしょうか。昔は出来たけど、あるときから出来なくなったとか？･･･。改めてこれらの古い本を読んでみても、検索値は文字列でも良いと書いてあります。文字列の五十音順の並べ替えは昔も今も普通に出来るンですから不思議！。

私の小言はさておいて、誠に有難うございました。