Excel使いこなしの基礎と応用


Excel2013のVLOOKUP関数の使い方

別表のデータから検索してその値を自動入力できるVLOOKUP関数(ヴイルックアップ)は様々な場面で使われます。商品コードから価格と商品名を、氏名から出身地と年齢を、購入金額から配達料を自動で転記してくれるのです。

目次


VLOOKUP関数に使う表は縦並べで作成

精米の価格表と配達料金をまとめた表からデータを抽出してみることにしましょう。
▶基本的な表の作成方法
米の価格表と配達料金をまとめた表からデータを抽出
VLOOKUPは縦方向に検索する関数であるため、扱う表は縦並べに作成されている必要があります。「VLOOKUP」はVertical Look Up(縦の検索)の略ですね。

横方向に検索するなら「HLOOKUP関数」(Horizontal Look Up)を使う。
操作はVLOOKUPと同じだよ。
「V」を「H」にするだけ!

データを抽出する価格表や配達料金表と転記先の請求明細を同じシート上に置いてもいいですが、実務で作業する時は別シートか別ブックに置いて管理することをお勧めします。 ▶ブックとシートについて
シートの管理
大きな表の場合は「名前の定義」でセル範囲に名前を付けておくと計算式や関数への取り込みが容易になって便利です。

複数データ抽出/商品コードから商品名と単価を自動入力

下図がデータを自動入力する「請求明細」です。
請求明細のサンプル
左端のセルに「商品コード」を入力すると自動で右隣の「商品名」「単価」が表示されるようにしていきます。

「商品名」にVLOOKUP関数を適用
まず、「商品名」の入る最初のセル「B5」をクリックして選択。
数式」タブの「関数ライブラリ」にある「検索/行列▼」からドロップダウンするリストの一番下「VLOOKUP」をクリックします。
数式タブ関数ライブラリの検索/行列からVLOOKUP
関数の引数」ダイアログボックスが開きます。
関数の引数ダイアログボックスの4項目
入力箇所は4項目。

スポンサーリンク

(1)検索値: 検索に使う値はどれですか? に答えます。
この例では「商品コード」を検索値にしますから、商品コードを入力する最初のセル「A5」を選択します。選択されたセル周囲に破線が回転し始め、ダイアログボックスの「検索値」の項目に「A5」と入りました。
関数の引数ダイアログボックスの検索値を入力
(2)範囲: 検索するデータがあるセル範囲は? に答えます。
「価格表」の見出し行以外のセル範囲をドラッグして指定します。選択されたセル範囲の周囲を破線が回り、ダイアログボックスの「範囲」に「価格表!A5:C9」と入りました。
関数の引数ダイアログボックスの範囲を入力
(3)列番号:(2)の範囲の内、参照するデータの列は左から何番目? に答えます。
「商品名」を参照して表示したいわけですから、左から2番めの列ですね。半角で「2」と入力します。
関数の引数ダイアログボックスの列番号を入力
(4)検索方法: FALSE=検索値と完全一致で検索するか、TRUE=完全ではないが近い値も検索するか、どちらの方法を選ぶ? に答えます。
この場合は近似値を参照する必要が無いので「FALSE」完全一致の検索方法を選びます。「検索方法」の項目に「数字の0」を入力します。「FALSE」と打ち込んでもOK。
関数の引数ダイアログボックスの検索方法を入力
これで、全ての入力が完了。
関数の引数ダイアログボックスの全ての項目に入力完了
自分で数式バーに入力するなら「=VLOOKUP(A5,価格表!A5:C9,2,0)」になります。ここで、一つ厄介なことが…。
ダイアログボックスの項目を入力し終えて「OK」すると、
#N/Aエラー
「#N/A」というエラー値が返されています。
検索値である「商品コード」に何も入れてないのだから、当然といえば当然。
試しに「商品コード」を入力してみると、
商品コード入力で商品名表示
ちゃんと自動入力できてますね。
このエラー値を表示させないようにするにはIFERROR関数をVLOOKUP関数に付け加える必要があります。
=VLOOKUP(A5,価格表!A5:C9,2,0)」という式を「=IFERROR(VLOOKUP(A5,価格表!A5:C9,2,0),"")」 に変えてみて下さい。「#N/A」が消えて空欄になります。
※この関数の詳しい記述方法はIFERROR関数でエラー値を消すを参照して下さい

最後に、A5セルを選択して「商品名」の列にこの式をフィルコピーします。
フィルハンドルで右クリック
選択したセルの右下角にあるフィルハンドル(四角形の突起)にポイントし、マウスポインタの形が黒い十字型になったらクリック、そのまま下までドラッグします。
▶オートフィルで一括コピー
オートフィルで式をコピー
なぜ、右クリックかというと、表に罫線が引いてある場合、普通にフィルコピーしちゃうと罫線の一部が消えちゃったりしてその都度修正するのがメンドイため。
メニューから「書式なしコピー(フィル)」
コピー後に手を放すとコンテキストメニューが現れるので、「書式なしコピー」をクリックすれば、罫線は無事です。普通にフィルコピーした後、枠横に現れるアイコンをクリックしても同じですが、小っちゃいんでうっかりしちゃうんですよね。

スポンサーリンク

「単価」にVLOOKUP関数を適用
「商品名」と同様に「単価」も最初のセルを選択します。
もう一度「関数の引数」ダイアログボックスを開いて同じ操作を繰り返して「列番号」だけ「3」にしてもいいですが、
「商品名」の式をコピーして「単価」の最初のセルにペースト、「=VLOOKUP(A5,価格表!A5:C9,2,0)」を「=VLOOKUP(A5,価格表!A5:C9,3,0)」に変えてもいいです。たぶん、こっちの方が楽っぽいと思います。
数式バーに式をコピペして修正


重量の合計から配達料を自動入力

今度は「重量の合計」を検索値にして「配達料」を自動入力させます。
重量から配達料を自動入力させるセル
上図の赤線で囲った配達料のセル「E13」をクリックして、「数式」タブ「関数ライブラリ」「検索/行列▼」からVLOOKUPをクリック。「関数の引数」ダイアログボックスを開きます。「検索値」にカーソルが点滅している状態で「重量合計」のセル「F12」をクリック。「検索値」にセル番地が入力されます。
配達料の検索値を入力
「範囲」にカーソルを移し、「配達料金表」の見出し行以外のセル範囲をドラッグして指定、ダイアログボックスの「範囲」に「配達料金表!A5:B8」と入りました。
配達料の範囲を入力
「列番号」はキーボードで「2」を入力、「検索方法」は「1」を入力して(空でもいい)「TRUE」にしました。TRUEを選んだ訳は次章で説明します。
配達料の列番号と検索方法を入力
エラー値を消すために式を「=IFERROR(VLOOKUP(F12,配達料金表!A5:B8,2,1),"")」に修正します。 ▶IFERROR関数でエラー値を消す


「FALSE」と「TRUE」の使い分け

前述したように、「FALSE」は検索値と完全一致の値を抽出して表示、「TRUE」は完全一致が無い時は近似値も検索して表示します。

言葉だけだと分かったような分からないような感じで、イマイチ把握できませんよね。このページでVLOOKUP関数の使用例を二つに分けたのは、この検索方法の違いを明確にしたいためです。

「商品コード」から「商品名」と「単価」を引っぱってくる例では「FALSE」を選んでいます。「商品名」も「単価」も決定された値で、状況によって変動することも曖昧になることもないからです。こちらは比較的わかりやすいと思います。

問題は「TRUE」の方ですね。

配達料金表
「重量の合計」から「配達料」を自動入力する例の参照データである「配達料金表」を見て下さい。

「重量」の値は「10, 20, 30, 50」になっています。
これは、もちろん「10kgまでなら配達料は500円」「30kg以上50kg未満なら1500円」という表示です。

VLOOKUP関数で仮に「FALSE=完全一致検索」を選んだとすると、米2袋で総量が20kgなら問題はありませんが、米4袋で総量40kgだと完全一致の値が無いのでエラーになってしまいます。

そこで、「TRUE=近似値も検索」の出番です。
「近似値も検索」とは、まさに「前行の値以上~次行の値未満」の「~」に含まれる全ての数値も検索対象になるということで、総量40kgの配達料を求めれば、1500円と返してくれるわけです。
商品コードと数量から「重量合計」と「配達料」を自動入力
上図は実際に商品コードと数量を打ち込んで「重量合計」と「配達料」を自動入力させた結果です。

Officeで作れるもの