ExcelにはVLOOKUPという便利な関数があります。これを使えば、指定した値をもとに、表の中から自動でデータを探し出せるため、商品リストや顧客データの管理など、多くのデータを扱う場面で役立ちます。
今回は、VLOOKUPなどの関数を使ったデータの取り出し方と、活用例をご紹介します。
VLOOKUP関数
VLOOKUPとは、指定した範囲内で縦方向に検索し、横方向から一致するデータを取得する関数です。
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
- 検索値：検索する値
- 範囲：検索するデータ範囲
- 列番号：取得したいデータが範囲内の何列目か
- 検索方法（省略可）：完全一致（FALSE）or 近似一致（TRUE）省略時はTRUEとなる
VLOOKUPは、検索範囲の左端の列を上から下に検索し、検索値より右側のデータを取得する仕組みです。そのため、検索列は必ず指定した範囲の左端に置く必要があります。
VLOOKUP関数の使用例
商品番号を入力すると、対応する商品名と価格が自動入力されるようにします。
データを取得
今回は、他のセルにコピーすることを想定して、複合参照や絶対参照を活用した以下の数式を入力します。
=VLOOKUP($F3,$B$2:$D$7,2,FALSE)
- 検索値：$F3（検索したい商品番号が入力されたセル）
- 範囲：$B$2:$D$7（検索列、取得するデータ列を含めた検索範囲）
- 列番号：2（検索範囲内で商品名が入力された列番号）
- 検索方法：FALSE（完全一致）
検索値は複合参照（$F3）にすることでF列を固定し、下にコピーしたときに行番号のみ変わるように設定します。範囲は絶対参照（$B$2:$D$7）で列と行を固定し、どこにコピーしても参照元が変わらないようにします。
価格のセルに数式をコピーし、列番号を変更すれば価格データも取得できます。
しかしこのままでは、検索値が入力されていない場合、該当するデータがない場合にエラーが表示されるため、必要であればIFERROR関数を組み合わせてエラー表示を防ぎましょう。
エラーを防ぐ
IFERROR関数とは、数式の結果がエラーになった時に指定した値を返す関数です。
=IFERROR(値, エラー時の値)
- 値：エラーが発生する可能性のある数式
- エラー時の値：エラー時に表示する値（例：空白 “” 、0 、特定の文字列）
今回はエラー時の値を空白とするため、商品名のG3に以下の数式を入力します。
=IFERROR(VLOOKUP($F3,$B$2:$D$7,2,FALSE),””)
あとは、価格のセルで列番号を変え、まとめてコピーをすれば完成です。
VLOOKUP関数の弱点とINDEX関数とMATCH関数の活用
VLOOKUP関数は、検索対象より左側のデータを取得できないという弱点があります。この弱点を補うために、INDEX関数とMATCH関数を組み合わせることで左側のデータも取得可能になります。
INDEX関数
INDEX関数とは、指定した行・列の交差する値を取得する関数です。
=INDEX(配列, 行番号, [列番号])
- 配列：検索するデータ範囲
- 行番号：取得するデータの行番号
- 列番号（省略可）：範囲が複数列の場合、取得する列番号
MATCH関数
MATCH関数とは、指定した値が範囲の何番目にあるかを取得する関数です。
=MATCH(検査値, 検査範囲, [照合の型])
- 検索値：検索する値
- 検索範囲：検索するデータ範囲（1列 or 1行）
- 検索方法（省略可）：完全一致（0）or 検索値以下で最大の値（1）or 検索値以上で最小の値（-1）省略時は（1）となる
検索範囲が2列以上、2行以上になるとエラーが表示されます。
組み合わせる
VLOOKUPの代わりに2つの関数を合わせて使う場合、数式は以下の通りです。
=INDEX(配列, MATCH(検索値, 検索範囲, 検索方法))
例えば、商品名を元に商品番号を取得する時には以下の数式を入力します。
=INDEX($B$3:$B$7,MATCH(G3,$C$3:$C$7,0))
配列：$B$3:$B$7（商品番号が入力された列）
検索値：$G3（検索したい商品名が入力されたセル）
検索範囲：$C$3:$C$7（商品名が入力された列）
検索方法：0（完全一致）
MATCH関数で検索したい商品名の行番号を取得し、その行番号と商品番号の列（B列）が交差する値をINDEX関数で取得する、という意味になります。
少し数式が複雑になりますが、2つを組み合わせて使うことで、VLOOKUP関数の弱点を補うことができます。
VLOOKUPを活用して数値とグラフを連動させる
VLOOKUPの活用例として、下記の図のような数値とグラフを連動させた検索機能を作成したいと思います。今回はプルダウンで選択された商品番号に応じて、商品名と売上データを取得し、売上データ上に棒グラフを設置します。
プルダウンのリストを作成
- 商品番号のセルをクリックし、[データ]タブから[データの入力規則]を選択
- [データの入力規則]のダイアログボックスが表示されたら、入力値の種類から[リスト]を選択
- 元の値に商品番号を範囲選択
- [OK]をクリック
VLOOKUPを使ってデータを自動取得
- 商品名を表示させたいセルにVLOOKUP関数を入力
今回は「=VLOOKUP($A3,$A$6:$N$11,2,FALSE)」と入力します。
- 売上のセルに数式をコピーし、列番号をそれぞれの月に合わせて変更していきます。
1月「=VLOOKUP($A3,$A$6:$N$11,3,FALSE)」
2月「=VLOOKUP($A3,$A$6:$N$11,4,FALSE)」……
グラフとデータを連携
今回はわかりやすくするために、月別で色を変えたグラフにしていきます。
- 売上データを範囲選択
- [挿入]タブから棒グラフをクリックし[その他の縦棒グラフ]を選択
- [グラフの挿入]のダイアログボックスが表示されたら、カラフルな縦棒グラフを選択
- [OK]をクリック
あとはグラフを調整し、売上データの上に設置すれば完成です。各月のセルの色をグラフに合わせて変更すると、より視覚的にわかりやすくなります。
おわりに
XLOOKUPなどの新しい関数もありますが、環境によっては使えない場合もあるため、VLOOKUPを活用できるようにしておくと便利ですよ。ぜひ今回の内容を参考に、実際に試してみてください。
次回は、VLOOKUPの上位互換であるXLOOKUPと、より見やすいようMAX値を目立たせたグラフとの連携についてご紹介します。
コメント