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関数を使用する
でも問題なしです
まとめ
以下、3つの方法を紹介しました。
1. IF関数、SUM関数を使用して行う
2. SUMIFS関数を使用して行う
3. SUMPRODUCT関数を使用して行う
下の方法にいくにつれ、計算式自体は複雑になりますが
汎用性は大きくなっていきます。
簡単に用途をまとめると
| 方法 | 表の大きさ | 条件数 | 集計対象 |
|---|---|---|---|
| 1 | 小さい | 1つ以下 | 単一行/列 |
| 2 | 大きい | 1つ以上 | 単一行/列 |
| 3 | 大きい | 1つ以上 | 複数行/列 |
計算式にて合計値を算出する際の用途にあわせて
使い分ければ大量な表の合計値の計算も簡単にかつ
正確に行うことができることとなります。
参考になれば幸いです。