Excel

【EXCEL】エクセルのVLOOKUP関数で合計は出る?SUM系の関数と組み合わせる方法3選

当サイトでは記事内に広告を含みます。

VLOOKUP関数は、その使い方さえ分かれば大変便利な関数ですよね。

そしてエクセルをより深く活用していくと、VLOOKUP関数で検索ワードに該当する値の合計を算出する必要に迫られることがあります。

例えば、以下のような「商品在庫一覧表」があり、カテゴリが「果物」に属する商品の在庫高を知りたい場合を考えましょう。

この場合、はたしてVLOOKUP関数で目的の在庫高を算出することは出来るのでしょうか?

結論から言うと、VLOOKUP関数では不可能です。

検索ワードが複数存在する場合、VLOOKUP関数では最初に登場するレコード(一番上位に存在するレコード)しか引当ててくれないからです。

もっと根本的に言い表すならば、「VLOOKUP関数は検索条件を満たすレコード「値」を抽出する関数」であり「合計を算出する関数」ではないという事です。(下図参照ください。)

=VLOOKUP(“果物”,E5:I20,4,FALSE)

一番最初に出てくるレコード(在庫高「6000」)が関数の戻り値(結果)になってしまいます。

繰り返しますがVLOOKUP関数は、合計を算出するための関数ではなく、特定のレコード「値」を見つけ出すための関数なのです。

ではどのようにすればよいのでしょうか?

心配無用です。エクセルにはちゃんと合計を出すための関数が備わっています。

という事で、今回は代表的な方法(関数)を3つ解説します。

・SUMIF関数

・SUMIFS関数

・SUMPRODUCT関数

での処理。

それではサンプルを用いて詳細を確認していきましょう。

エクセルのVLOOKUP関数で合計は出る?方法1:SUMIF関数

SUMIF(「検索範囲」,「検索条件(検索ワード)」,「合計範囲※省力可」)

冒頭部分であげた「商品在庫一覧表」の場合は以下のようになります。

関数の戻り値(結果)は以下の通り、7910円と表示されます。

一件複雑そうに見えますが、慣れれば非常にシンプルで使い勝手の良い関数です。

尚、20年以上エクセルを愛用している筆者(私)の経験上2つほど注意・把握しておくことも併せてお伝えします。

 

注意点1:「検索条件」を満たすレコードが存在しない場合は「0」となる。(エラーとならない)

SUMIF関数は、検索条件を満たすレコードが存在しない場合は「0」を返してきます。(VLOOKUP関数は「#N/A」というエラーを返してきます。)

「エラー」にならないという事は、検索条件の指定を間違えていても「そのミス」に気が付きにくいという事でもあります。
SUMIF関数の条件設定の際は、ミスがないか注意を払うことが重要です。

注意点2:「検索範囲」と「合計範囲」の行範囲は同じにしておく。(行範囲として指定する)
SUMIF関数の3つ目の引数である「合計範囲」は、先頭のセルのみを指定するだけでも合計してくれるのですが、以下のように検索範囲の行数と同じ行数を範囲指定するようにすることをお勧めします。

今回の例でいえば「検索範囲」⇒E5:E20とすれば、「合計範囲」はH5(先頭の行)とだけ記載すれば、正しく合計されるのですが、「合計範囲」はH5:H20と同じ行範囲になるように指定しましょう。
「注意点1」で解説しましたようにSUMIF関数は、検索条件を満たすレコードがなくても「0」が帰ってくるので条件指定や範囲指定ミスなどに気が付かないことが起こりえます。

こういった事態に陥らない為にも「検索範囲」「合計範囲」の指定は、省略せずに範囲としてフル指定するようにしましょう。

(表計算が複雑になった時に事故らない為に私自身はいつも「範囲」として指定しています。)

 

エクセルのVLOOKUP関数で合計は出る?方法2:SUMIFS関数

SUMIFS関数は、複数の「検索条件」を満たすレコードの合計を算出してくれる関数です。(EXCEL2007頃に登場した関数です。)

考え方・使い方はSUMIF関数と同じですが、引数の指定方法(並び方)が以下の通り変わっています。

SUMIFS(「合計範囲」,「検索条件範囲1」,「検索条件1」, 「検索条件範囲2」,「検索条件2」,…【条件127個まで指定可】)

1番目の引数:合計したい項目
2番目以降の引数:「検索条件」を指定する。

では、今回の「商品在庫一覧表」を例にしてSUMIFS関数を使ってみましょう。

検索条件1:「仕入れ先」⇒「巨人市場」

検索条件2:「カテゴリ」⇒「果物」

上記2つの条件を満たす在庫高を求める場合は、下図の計算式になります。

関数の戻り値(結果)は以下の通り、7910円と表示されます。

SUMIF関数の引数の並び方と「順序」こそ違いますが、実にシンプルでわかりやすくのではないでしょうか。

尚SUMIFS関数は、検索条件が1つだけの場合でも使用する事は可能です。(大は小を兼ねる。)

上述のとおりSUMIFS関数はExcel2007から登場した関数でそれまでは、複数の「検索条件」が存在した場合は、次に解説するSUMPRODUCT関数が用いられる事が多かったです。

 

エクセルのVLOOKUP関数で合計は出る?方法:SUMPRODUCT関数

SUMPRODUCT(「配列1」,「配列2」,「配列3」,・・・)

このSUMPRODUCT関数の「構文」を見てもお分かりの通り少しイメージし難い高度な関数です。

SUMPRODUCT関数は、引数を指定するだけで配列計算の合計を算出してくれる関数です。(文系の方はアレルギー反応が出るかもしれませんが、数学で言うところのベクトルの「内積」を計算する関数)

本記事のサンプルデータ「商品在庫一覧表」の場合、以下の様になります。

SUMPRODUCT(「配列1(条件指定)」,「配列2(計算させたい項目)