ExcelのVLOOKUP関数とSUMIF関数を組み合わせる方法

この記事では、ExcelのVLOOKUP関数とSUMIF関数を組み合わせる方法をご紹介します。

VLOOKUP関数は検索値に対応した値を抽出するのによく使われる関数ですが、求めた値を合計したい場合はそのままでは完結することができません。

以下では、SUMIF関数を組み合わせて合計値を求める方法をご紹介します。

VLOOKUP関数で合計ができる？

上記の画像の「受注表」は、VLOOKUP関数を使って商品番号を検索値として対応する商品名と単価を「商品管理表」から抽出した表です。

VLOOKUP関数で抽出したひまわりの単価の合計を算出したい場合に、VLOOKUP関数だけではリスト化までしかできません。

それぞれの商品の単価を合計したい場合は、SUMIF関数を使用すると指定した条件の合計値を求めることができて便利です。

ExcelのVLOOKUP関数とSUMIF関数を組み合わせる方法

VLOOKUP関数でリスト化した表で指定した条件の合計値を求めたい場合は、SUMIF関数を組み合わせることで簡単に求めることができます。

SUMIF関数で条件を指定して合計する

SUMIF関数で指定した条件の合計値を算出する方法をご紹介します。

作業時間：1分

データを用意する

上記の画像の「受注表」は、「商品管理表」からVLOOKUP関数を使って商品名と単価を抽出した表です。「受注表」を基に商品番号ごとの合計金額を「売上表」に算出していきます。

関数を入力する

H10セルに「=SUMIF($B$10:$B$20,F10,$D$10:$D$20)」を入力します。検索条件（例：F10セル）を範囲（例：B10セルからB20セル）から検索し、検索条件に該当する合計範囲（例：D10セルからD20セル）の値を足して合計金額をH10セルに表示します。SUMIF関数は、指定した条件の合計値を求めます。SUMIF関数の書式は=SUMIF(範囲,検索条件,[合計範囲])です。

商品番号「101」の合計金額が算出された

商品番号「101」のひまわりの単価の合計が算出されました。

数式をコピーする

数式をコピーして商品番号「102」、「103」、「104」の合計金額を求めます。H10セルのフィルハンドルをドラッグします。

指定した条件の合計金額が算出できた

SUMIF関数を使ってそれぞれの商品の合計金額を算出することができました。

SUMIF関数をVLOOKUP関数を組み込んで合計を算出する方法

以下では、VLOOKUP関数で抽出した値に一致する値の合計をSUMIF関数で算出する方法をご紹介します。

上記のような表を用意します。検索値に任意の商品名を入力して、該当する商品名の合計金額を算出していきます。

A11セルに合計を求めたい商品名（例：バラ）を入力します。

B2セルに「=SUMIF(A2:A8,VLOOKUP(A11,A2:B8,1,FALSE),B2:B8)」を入力します。

VLOOKUP関数で検索値（例：A11セル）を範囲（例：A2セルからB8セル）の指定した列番号（例：1列目）から完全に一致する値を抽出します。

VLOOKUP関数で抽出した値を検索条件としてSUMIF関数で範囲（例：A2セルからB8セル）から検索し、一致した値の合計範囲（例：B2セルからB8セル）の合計値をB11セルに算出します。

SUMIF関数とVLOOKUP関数を使ってB11セルに「合計金額」を算出することができました。

VLOOKUP関数の検索値にSUMIF関数を組み込んで合計する方法

以下では、VLOOKUP関数の検索値にSUMIF関数を組み込んで算出した合計値の近似値に該当する値を抽出する方法をご紹介します。

上記のような表を用意します。例では、任意の商品名を検索値として合計金額を算出し、その金額の近似値が一致する「グループ」がAとBのどちらに当てはまるか抽出していきます。

A11セルに合計を求めたい商品名（例：バラ）を入力します。

B2セルに「=VLOOKUP(SUMIF(A2:A8,A11,B2:B8),D2:E3,2,TRUE)」を入力します。

SUMIF関数で検索条件（例：A11セル）を範囲（例：A2セルからB8セル）から検索し、一致した値の合計範囲（例：B2セルからB8セル）の合計値を求めます。

SUMIF関数で出した合計値をVLOOKUP関数の検索値として、範囲（例：D2セルからE3セル）から近似値に一致する値を指定した列番号（例：2列目）の中から抽出します。

VLOOKUP関数とSUMIF関数を使ってB11セルに該当する「グループ」を算出することができました。