Contents
VLOOKUP関数で表を結合する
同じキーをもつ2つの表のデータを合わせたい時は、VLOOKUP関数を使用します。この関数は非常に利用用途が高いため、是非理解してください。
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
[検索値] = 検索する文字、又は数値を指定します。
[範囲] = 検索する表の範囲を指定します。
[列番号] = [範囲]の内、実際に表示させたい情報が何列目(左端から1列目として)に有るかを指定します。
[検索方法] = 省略可。[検査値]に一致するデータ(完全一致)を探す場合には「FALSE」、検索値に近いデータ(部分一致)を探す場合には「TRUE」を指定します。省略すると「TRUE」となります。
関数の説明 | [範囲]の中から指定された[検索値]を元に、必要な情報[列番号]を 表示させる関数。 |
---|---|
EXCELバージョン | Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016 |
別々に作成されている表にて、キーワードとなる列(同じ列タイトル)がある場合、VLOOUP関数を使用すれば下記のように1つの表にまとめる事が可能です。
<表の結合イメージ>
使い方例1)表の結合/VLOOKUP関数
下の表は、あるペットショップの「商品ID」ごとの1日の売上数(左)と、各「商品ID」の商品情報(右)が入力されています。
「商品ID」を元にこの表を結合し、「注文数」「商品名」「単価」が同じ一覧に表示されるようにしてみましょう。
まずは、必要な情報を入力できるように、左側の表にそれぞれ「商品名」「単価」の列を追加する。
では、新しく作成したD列・E列にそれぞれ「商品名」・「単価」が表示されるようにVLOOKUP関数を入力しましょう。
① VLOOKUP関数を使用して、「商品ID」を元に「商品名」が表示されるようにする。
(1)[検索値] = 検索する元となる「商品ID」のセル「C4」を指定します。
(2)[範囲] = 商品ID一覧(右の表)のうち、セル範囲「H4:J13」を指定します。
(3)[列番号] = 商品ID一覧(右の表)のうち、今回必要な情報は「商品名」の入力されている2列目の為、「2」を指定します。
(4)[検索方法] = 完全一致するデータを取り出す為、「FALSE」を指定します。
=VLOOKUP(C4,H4:J13,2,FALSE)
② VLOOKUP関数を使用して、「商品ID」を元に「単価」が表示されるようにする。
(1)[検索値] = 検索する元となる「商品ID」のセル「C4」を指定します。
(2)[範囲] = 商品ID一覧(右の表)のうち、セル範囲「H4:J13」を指定します。
(3)[列番号] = 商品ID一覧(右の表)のうち、今回必要な情報は「単価」の入力されている3列目の為、「3」を指定します。
(4)[検索方法] = 完全一致するデータを取り出す為、「FALSE」を指定します。
=VLOOKUP(C4,H4:J13,3,FALSE)
使い方例2)元の表がそれぞれ別のシートに分かれている場合の結合方法
もし、下のように結合前の2つの表が別々のシート内にある場合の結合方法も覚えておきましょう。
VLOOKUP関数を使用して、「商品ID」シートから「商品名」・「単価」が表示されるようにする。
別シートのデータを利用する時の指定方法
別のシートの表を結合する場合、VLOOKUP関数式にはセル位置の前にシート名+「!」(エクスクラメーション)と記載することで指定します。
例えば、商品IDシートの、セル範囲「A1:C3」を選択する場合には、
商品ID!A1:C3
となります。『売上実績』シートにある、ペットショップ日次売上表に、『商品ID』シートの商品ID一覧のデータを結合・マージしたい場合、以下のように表記します。
(1)「商品名」の検索
=VLOOKUP(C4,商品ID!B4:D13,2,FALSE)
(2)「単価」の検索
=VLOOKUP(C4,商品ID!B4:D13,3,FALSE)
VLOOKUPの「完全一致」と「部分一致」
VLOOKUP関数の使い方について、[検索方法]で使用した「完全一致(FALSE)」と「部分一致(TRUE)」の違いについて確認しておきましょう。
部分一致(TRUE)について
数値で[検索値]が指定されている場合に限り、[検索値]以下の数字でかつ、最も近い数値(=近似値)を[範囲]内から検索できるようになります。
使用例)部分一致=TRUEを使用した例
下の表は、ある学校の期末テスト結果を点数ごとに評価する為に作成したものです。
各点数に応じて、評価をD列に表示するようにしましょう。
上記では、[検索値]である98点以下で、かつ、98点に一番近い数字である90点の評価が採用され、評価は「A」となりました。VLOOKUP関数式をどのように指定するのか見てみましょう。
① VLOOKUP関数を使用して、「点数」を元に「評価」が表示されるようにする。
(1)[検索値] = 検索する元となる「点数」のセル「C4」を指定します。
(2)[範囲] = 評価基準(右の表)のうち、セル範囲「F4:G8」を指定します。
(3)[列番号] = 評価基準(右の表)のうち、今回必要な情報は「評価」の入力されている2列目の為、「2」を指定します。
(4)[検索方法] = 部分一致するデータを取り出す為、「TRUE」を指定します。
=VLOOKUP(C4,F4:G8,2,TRUE)
もし、昇順でなければ正しい結果が表示されなくなります。
VLOOKUPで0を返す、#N/Aを返す場合表示しない方法
以下の場合には、VLOOKUP関数の結果として、「0」または「#N/A(エラー)」が表示されることがあるので注意が必要です。
① 検索した結果となるセルが空白であった場合 →「0」
下の表では、商品ID「B003」を右の表から検索したところ、商品名が空白の為、「0」が表示されています。
② もし検索の元となるセル=[検索値]が空白の場合 →「#N/A」
下の表では、検索する為に必要な「商品ID」が空白で入力されていない為、エラー「#N/A」が表示されています。
このままでは見た目が少し悪い為、それぞれ「0」や「#N/A」が表示されないようにIF関数を使用して数式を工夫してみましょう。 ⇒ IF関数の使い方はこちら
「0」値を表示させない方法)IF関数
① IF関数を使用して、もしVLOOKUP関数の結果が「0」なら空白が表示されるようにする。
(1)IF関数の入力を入力します。 →「=IF(」
(2)IF関数の内容として、『VLOOKUP関数の結果=「0」の場合は空白「””(ダブルクォーテーション)」、違っている場合はVLOOKUP関数の結果』と指定します。
→「VLOOKUP(C4,I4:K13,2,FALSE)=0,“”,VLOOKUP(C4,I4:K13,2,FALSE)」
(3)IF関数の残りの「)」を入力します。
=IF( VLOOKUP(C4,I4:K13,2,FALSE)=0,””,VLOOKUP(C4,I4:K13,2,FALSE) )
「#N/A」を表示させない方法)ISERROR関数、IF関数組み合わせ
「#N/A」を表示させない方法として、以下の関数式を利用する方法もあります。
ISERROR(検証対象)
[検証対象] = 式や参照先など、エラー判定したい対象を指定します。
関数の説明 | [検証対象]が#N/AエラーであればTRUEを、それ以外であればFALSEを返す関数 |
---|---|
EXCELバージョン | Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016 |
① ISERROR関数、IF関数を使用して、もしVLOOKUP関数が#N/Aエラーを返す場合、空白が表示されるようにする。
(1)IF関数の入力を入力します。 →「=IF(」
(2)IF関数の内容として、『ISERROR関数の中のVLOOKUP(C5,I4:K13,2,FALSE)の返り値が#N/Aエラーの場合は空白「””(ダブルクォーテーション)」、違っている場合はVLOOKUP関数の結果』を指定します。
→「ISERROR(VLOOKUP(C5,I4:K13,2,FALSE)),“”,VLOOKUP(C5,I4:K13,2,FALSE)」
(3)IF関数の残りの「)」を入力します。
=IF( ISERROR(VLOOKUP(C5,I4:K13,2,FALSE)), “”,VLOOKUP(C5,I4:K13,2,FALSE) )
「#N/A」を表示させない方法)IF関数で予めエラー要因を排除
① IF関数を使用して、もしVLOOKUP関数の元となる[検索値]が空白なら空白が表示されるようにする。
(1)IF関数の入力を入力します。 →「=IF(」
(2)IF関数の内容として、『セルC5が空白の場合は空白「””(ダブルクォーテーション)」、違っている場合はVLOOKUP関数の結果』と指定します。
→「C5=””,“”,VLOOKUP(C5,I4:K13,2,FALSE)」
(3)IF関数の残りの「)」を入力します。
=IF( C5=””,””,VLOOKUP(C5,I4:K13,2,FALSE) )
複数条件指定でVLOOKUP関数を利用する方法
下の表は、ある中古車販売店の販売価格一覧をまとめた表(左)です。
今回、セルG4・H4にそれぞれ「メーカー」と「車体名」を指定すると、簡単に販売価格が検索できる表を右に追加いたしました。
この場合、セルG4・H4のそれぞれの[検索値]を使用して、「販売価格」が表示されるようにVLOOKUP関数を使用して設定をしましょう。
使い方例)複数の条件でのVLOOKUP関数
通常、VLOOKUP関数の[検索値]は1つしか指定できない為、次のように検索用の列「検索用名称」を追加します。
検索用の文字として、C列とD列の文字を結合した結果を数式を入力して求めておきましょう。
セルE4例 → =C4&D4
セルJ4に、VLOOKUP関数を設定する。
① VLOOKUP関数を使用して、「メーカー」・「車体名」を元に「販売価格」が表示されるようにする。
(1)[検索値] = 検索する元となる「メーカー」と「車体名」を「&」で結合して指定します。 →「H4&I4」
(2)[範囲] = 車体販売価格一覧(左の表)のうち、セル範囲「E4:F11」を指定します。
(3)[列番号] = 車体販売価格一覧(左の表)のうち、今回必要な情報は「販売価格」の入力されている2列目の為、「2」を指定します。
(4)[検索方法] = 完全一致するデータを取り出す為、「FALSE」の意味である「0」を指定します。
=VLOOKUP(H4&I4,E4:F11,2,0)
[検索方法]の指定方法として、「TRUE」・「FALSE」と入力する他に、
・「FALSE」 = 「0」
・「TRUE」 = 「0」以外の数字
でも指定出来ます。