Office関連 | フリーランスITエンジニアのブログ

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月分の合計を
　求める方法としては、以下の３つが考えられます。
　　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関数を使用する
　　でも問題なしです