参照データの誤記
ビジネスで頻繁に用いられるExcelの関数と言えばVLOOKUP関数です。
ある元データから特定の値を参照することが可能で便利な関数です。
しかし関数にも限界があり、参照データに誤記があれば当然値の参照はできません。
こうした場合、参照データの誤記修正ができるなら良いのですが、場合によっては参照元は編集できないこともあります。
そこで今回は参照データを編集せずに誤記を考慮した関数の設定を紹介します。
VLOOKUP関数の基本的な使い方はこちらで紹介しています。
VLOOKUP関数の設定
データの例
まずは参照データとして下図のような部品データを例とします。
こちらのデータは編集できないものとします。
こちらの部品データの「品目番号」をキーとして「棚番」を表示させる関数を作成します。
つまり、下図のようなリストを完成させることが今回の目的です。
VLOOKUP関数を活用し、「品目番号」を検索値、参照データのB列からD列が検索範囲、列番号は3で検索方法はFALSE(完全一致)とした場合、次のような関数になります。
=VLOOKUP(A3,部品データ!$B$3:$D$10,3,FALSE)
これで品番「0000-125A」の棚番は正しく「A1」と表示されました。
続いてオートフィルで下2つの品番の棚番を表示させてみましょう。
「#N/A」のエラーが表示されてしまいました。
原因は参照データの誤記です。
今回参照した「0000-130K」および「0000-129G」の部品データをよく見てみましょう。
「0000-130K」は「0000_130K」のようにハイフンがアンダーバーに、
「0000-129G」は「0000ー129G」のようにハイフンが半角から全角に記載されていました。
このような記号の不統一による参照不可がエラーを発生させていました。
本来であれば記号の誤記修正を行いたいところ。
しかし前述の通り、参照元である部品データは編集できません。
ワイルドカードを活用する
記号の不統一はよくある誤記です。
検索値の一部を無視し、信頼できる範囲だけで検索することで誤記を気にせずVLOOKUP関数を使用できます。
このために必要となるのがワイルドカードの「*」(アスタリスク)です。
ダブルクオーテーション「”」で挟んで使用します。
検索値の部分を変更し、前半の「0000-」は無視、後半の「125A」などの部分のみをキーとして関数を修正します。
=VLOOKUP("*"&RIGHT(A3,4),部品データ!$B$3:$D$10,3,FALSE)Right関数は文字列の右端から指定の文字数を抽出する関数です。
この関数でオートフィルを使用すると以下のようになります。
正しい棚番が入力されました。
後半の信頼できる部分のみをキーとしたことで誤記を無視することができました。
よくある間違った事例
VLOOKUP関数の間違った使用例として、検索方法の「TRUE」と「FALSE」を誤ったまま使用している例を見かけます。
TRUEは「近似一致」、FALSEは「完全一致」です。
ハイフンの半角や全角の違いも「近似一致」であると勘違いしてTRUEを使ってしまうと以下のように失敗します。
=VLOOKUP(A3,部品データ!$B$3:$D$10,3,TRUE)
棚番が表示されていますが、全く別の棚番です。
TRUEはあくまでも「数値的な」近似一致の機能です。
例えば「10から20の間」のような、数値の前後を含む使い方をします。
よって、今回のような数値の上も下もない品番をキーとした場合では上手く表示できません。
さらに、TRUEとFALSEを間違えて設定した場合、エラーにならないというのが大きな問題です。
正しく表示されていると思い込んでしまうと痛い目にあいます。
まとめ
参照データに誤記があってもVLOOKUP関数で値を参照する方法を紹介しました。
- ハイフンなどの記号の不統一による誤記に注意する
- 誤記を無視するにはワイルドカードを活用する
- 完全一致と近似一致を勘違いして使用しないこと
当サイトでは各種PCスキルを公開しています。
