表計算を作成していると、同じような項目を何度も入力することがあります。
例えば型番が決まっている商品の売上台帳を作り時であれば、型番に連動して品名・単価などは基本的に同じ内容であるはずです。
その都度全ての情報を入力していると時間もかかりますし、何より間違いの元です。
こういった情報はマスターとして管理しておき、キー番号で呼び出すようにしておくべきです。
例えば上の例ではコード欄に任意の数字を打ち込むことで、自動的に「OS」「品名」「単価」の情報が表示されるようになっています。
つまり日付、コード、数量さえ間違えなければ正確に資料を作成することが出来るわけです。
Excelで計算や分析を行う際には使用頻度の高い計算式ですので、ぜひ習得しましょう！
●目次
vlookup関数の使い方
計算式は以下の通りです。
=vlookup(検索対象,範囲,列番号,検索する型)
となります。
例えば見本の表では VLOOKUP($B4,マスター!$A:$D,2,FALSE) という計算式を入れています。
$B4が検索対象、つまりこのデータと一致するものを探すということです。
$はコピーしても検索対象が変わらないようにということで固定させるためです。
マスター!$A:$Dは対象となる範囲です。
この場合であればマスターシートのA列からD列が対象になっています。
今回は別のシートを参照にしていますが、同一シート内でも、もちろん計算可能です。
次の2が2列目ということですね。
最後にFALSEと入れていますが、ここには基本的に0かFALSEを入れておくといいです。
(TRUEや1を入れる場合もありのですが、一致しない場合近似値で計算するので正確に計算する場合には向かないため)
VLOOKUP($B4,マスター!$A:$D,A2,FALSE)
のように列番号をセルに入力されている値で計算することも可能です。
この場合であればA2に入力している数字を何列目かの数字として使用することになります。
複数列に計算式を一気にコピーする場合に使うと楽になります。
検索結果が複数ある場合の表示について
検索結果が複数ある場合の表示はどうなるのでしょうか？
例えばマスターデータで同じコード番号で2つの商品を登録した場合。
このような場合には表の上側にある商品が検索対象になります。
一生懸命下のデータを変更しても修正が反映されることはありませんので注意しましょう。
別シートの参照について
先ほどの見本の計算式でも使っていますが、vlookupでは別のシートを参照することも問題なく可能です。
むしろマスターデータを別シートに分けている方が間違えてマスターデータを消す心配もなくなりますので分けておくほうが安心です。
エラーを表示したくないときの対処方法
Vlookup関数では検索の型を0もしくはfalseとしている場合に計算結果がエラーになることがあります。
異常ではないのですが計算式のひな形として表を作る場合に見た目上よくないので印刷などのことを考えエラーを非表示にしたいということもあるはずです。
IFERROR関数を組み合わせることで解決します。
IFERROR((VLOOKUP($B19,マスター!$A:$D,2,FALSE)),””)
上記のようにvlookup関数の前にIFERROR関数をはさみエラーとなった場合の表示を指定できます。
最後の””となっているところがエラーとなった場合の結果です。
任意の数字や計算式でも大丈夫ですが、今回空白表示にしたいので””としています。
ちなみに未入力の場合0と表示されます。
検索できない場合の対応(文字列と数値)
Vlookup関数を使うときにどうみても検索値とマスターのデータで相違が無いにも関わらずうまく計算できていない場合があります。
数字をキーとして使っている場合におこることのある現象なのですが、数字とテキストという書式の違いで発生することが多いです。
例えばマスターは文字列の書式で作成しているにも関わらす、入力する表の方では数字として入力している場合。
仮に1と入力しといても一方は文字、もう一方は数字でありコンピューターは違うものとして認識します。
そのため書式を統一する必要があるのです。
手っ取り早いのは×1をして、文字列を強引に数字に変えることです。
書式が統一されれば正常に計算されるようになるはずです。
複数条件で検索する方法
vlookupでは検索対象はあくまでひとつです。
しかし場合によっては複数の検索条件によって結果を分岐させたい場合もあります。
例えば商品コードとエリアによって単価が変わる場合。
この場合には商品コードだけ、もしくはエリア名どちらの検索対象でもうまくいきません。
この場合では&を使って検索対象をつなげるのです。
見本ファイルのマスターであればA2&B2のような形で検索候補を一つにします。
そしてマスターと計算用のファイルの2つで同じように繋げた検索候補同士をvlookupで計算すればうまく表示することができるはずです。
