[技術]Excelで複数条件にマッチする行の合計値の求め方

Excelを使用して金額などの集計を行う際
複数の条件にマッチする行の合計値を算出するやり方。

Excelマクロを使わず、計算式のみで算出する方法です。

ここ数日、Excelで資料を作る機会があったので
備忘を含めてメモを書いておく。


※※※※※※※※※※※※※※※※※※※※
以降の表に関して閲覧環境により全てが
表示されない可能性があります。
その場合、お手間をかけますが、横画面表示
又はPC環境での閲覧をお願いします。
※※※※※※※※※※※※※※※※※※※※

集計対象となる表

A B C D E
品目 ジャンル 2015/01 2015/02 2015/03
3 パソコン 家電 200,000 20,000 80,000
4 テレビ 家電 220,000 270,000 20,000
5 HDD PC用品 50,000 80,000 10,000
6 洗濯機 家電 300,000 18,000 250,000
7 iPad PC用品 100,000 50,000 220,000

○ジャンルが「家電」である品目の2月~3月分の合計を
 求める方法としては、以下の3つが考えられます。
  1. IF関数、SUM関数を使用して行う
  2. SUMIFS関数を使用して行う
  3. SUMPRODUCT関数を使用して行う

この3つのうち、一番スマートに計算できるのは 3番 となります。
やり方を説明します。

SUMPRODUCT 関数
 書式 : SUMPRODUCT(配列1,配列2,配列3,・・・)
 説明 : 範囲または配列の対応する要素の
       計算し、その計算結果の合計を返します。
       ※ Excelの関数説明より

この関数のいいところは引数(配列1,配列2 など)に対して
計算式を書くことができることと関数の説明にもある通り
配列同士の論理積で計算できることにあります。
※ 論理積とは 中学/高校の数学で学んだ論理演算で
   A AND B : A と B が両方成り立つ
   A OR B : A と Bの少なくとも1つが成り立つ
  などです


よく使われる SUM関数、COUNT関数などの場合
合計範囲や検索範囲のように、あくまで
Excelの範囲を指定することしかできません。

SUM関数
 書式 : SUM(数値1,数値2,・・・)
       SUM(セルの範囲)
       ※ セルの範囲 → C3:C6 のように指定
         (C3セルからC6セルまでの意味)
 説明 : セル範囲に含まれる数値を全て合計する。
       ※ Excelの関数説明より

COUNT関数
 書式 : COUNT(数値1,数値2,・・・)
       COUNT(セルの範囲)
       ※ セルの範囲 → C3:C6 のように指定
         (C3セルからC6セルまでの意味)
 説明 : 範囲内の数値が含まれるセル(範囲)の個数を返す。
       ※ Excelの関数説明より


話を戻して、実際の記載方法ですが
まず、各条件を計算式化します。
 1.ジャンルが「家電」である → IF(B3:B7=”家電”)
 2.期限が「2015/02」である → IF(C2:E2>=201502)
 3.合計値検索範囲 → C3:E7
※ 3の条件に関しては、1と2の条件を満たすもの全てが対象と
  なるため、論理積ではなく論理和となります。

SUMPRODUCT 式に当てはめると、このようになります。
SUMPRODUCT(([1の条件] AND [2の条件]) OR [3の条件])
→SUMPRODUCT((B3:B7=”家電”)*(C2:E2>=201502),(C3:E7))

この計算式が展開されると

A B C D E F
品目 ジャンル 2015/01 2015/02 2015/03 展開式
3 パソコン 家電 200,000 20,000 80,000 ※1 100,000
4 テレビ 家電 220,000 270,000 20,000 ※2 290,000
5 HDD PC用品 50,000 80,000 10,000 ※3 0
6 洗濯機 家電 300,000 18,000 250,000 ※4 268,000
7 iPad PC用品 100,000 50,000 220,000 ※5 0
658,000

SUMPRODUCT(([1の条件] AND [2の条件]) OR [3の条件])
となるので
※1:
 ((TRUEFALSE)200,000)+(TRUETRUE)20,000)+
 (TRUETRUE)80,000))
 =(10200000)+(1120000)+(1180000) = 100000
※2:
 ((TRUEFALSE)220,000)+(TRUETRUE)270,000)+
 (TRUETRUE)20,000))
 =(10220000)+(11270000)+(1120000) = 290000
※3:
 ((FALSEFALSE)50,000)+(FALSETRUE)80,000)+
 (FALSETRUE)10,000))
 =(0050000)+(0180000)+(0110000) = 0
※4:
 ((TRUEFALSE)200,000)+(TRUETRUE)20,000)+
 (TRUETRUE)80,000))
 =(10300000)+(1118000)+(11250000) = 268000
※5:
 ((FALSEFALSE)200,000)+(FALSETRUE)20,000)+
 (FALSETRUE)80,000))
 =(00100000)+(0150000)+(01220000) = 0

100000(※1)+290000(※2)+0(※3)+268000(※4)+0(※5)
=658000

となります。
関数の引数を増やすことで、条件を容易に増やすことが
できるため、複数条件で集計を行いときなどに便利です。


以下、2つの方法に関しても簡単に説明します。
 1. IF関数、SUM関数を使用して行う
 2. SUMIFS関数を使用して行う


1.IF関数、SUM関数を使用して行う
 IF関数を使用して行う場合、条件判定しか行えないため
 作業列を設ける必要があります。
 E列の後ろに、C/D/E それぞれに対応するF/G/H列を設ける。

 F3セルに、以下条件文を入力し、F3~H7にコピーする。
  IF($B3=”家電”,IF(C3>=201502,”1″,”0″),”0″)

 さらにI列を設けて、I4~I7セルにコピーを行う
 行単位での合計値を算出する。
  SUM(IF($F3=”1″,$F3,0)+SUM(IF($G3=”1″,$G3,0)+
  SUM(IF($H3=”1″,$H3,0)

 I列の合計値を求める。
  SUM(I3:I7)

C D E F G H I
2015/01 2015/02 2015/03 C列用条件 D列用条件 E列用条件 行計
3 200,000 20,000 80,000 0 1 1 100,000
4 220,000 270,000 20,000 0 1 1 290,000
5 50,000 80,000 10,000 0 0 0 0
6 300,000 18,000 250,000 0 1 1 268,000
7 100,000 50,000 220,000 0 0 0 0
658,000

2. SUMIFS関数を使用して行う

SUMIFS関数
 書式 : SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2,・・・)
 説明 : 特定の複数条件に一致する数値の合計を求めます。
       ※ Excelの関数説明より

似た関数ではSUMIFというのもあります

SUMIF関数
 書式 : SUMIF(範囲,検索条件,合計範囲)
 説明 : 指定された検索条件に一致するセルの値を合計します。
       ※ Excelの関数説明より

 SUMIFS関数を使用する場合、関数説明にもあるように
 複数条件を指定することができます。
 ただし、合計範囲自体は複数列(行)指定を行うことができません。
 また、合計範囲と条件範囲指定は合わせる(行と行、列と列)必要も
 あります。
 そのため、合計したい列を個別に指定する必要がでてきます。

 ですので、合計値を求める際は
  1.201501 の家電の合計:下表(※1)
  2.201502 の家電の合計:下表(※2)
  3.201503 の家電の合計:下表(※3)

 の合算を求めることとなります。
 ただし、2月~3月分という指定があるため
 1 の合計値は計算せずとも 0 となります。

A B C D E
品目 ジャンル 2015/01 2015/02 2015/03
3 パソコン 家電 200,000 20,000 80,000
4 テレビ 家電 220,000 270,000 20,000
5 HDD PC用品 50,000 80,000 10,000
6 洗濯機 家電 300,000 18,000 250,000
7 iPad PC用品 100,000 50,000 220,000
※1 ※2 ※3
0 308,000 350,000 658,000

※2.201502 の家電の合計
 SUMIFS(D3:D7,B3:B7,”家電”) 又は SUMIF(B3:B7,”家電”,D3:D7)
  20,000 + 270,000 + 18,000 = 308,000
※3.201503 の家電の合計
 SUMIFS(E3:E7,B3:B7,”家電”) 又は SUMIF(B3:B7,”家電”,E3:E7)
  80,000 + 20,000 + 250,000 = 350,000

 ※ 今回のケースでは、SUMIFSで指定可能な列の条件は
  「ジャンル」のみであるため、SUMIF関数を使用する
  でも問題なしです

パッと見るための目次

まとめ

以下、3つの方法を紹介しました。
  1. IF関数、SUM関数を使用して行う
  2. SUMIFS関数を使用して行う
  3. SUMPRODUCT関数を使用して行う

下の方法にいくにつれ、計算式自体は複雑になりますが
汎用性は大きくなっていきます。

簡単に用途をまとめると

方法 表の大きさ 条件数 集計対象
1 小さい 1つ以下 単一行/列
2 大きい 1つ以上 単一行/列
3 大きい 1つ以上 複数行/列

計算式にて合計値を算出する際の用途にあわせて
使い分ければ大量な表の合計値の計算も簡単にかつ
正確に行うことができることとなります。

参考になれば幸いです。

コメントを残す