表を扱っている時に、特定の条件に当てはまる個所が何か所あるか、またその条件に一致する個数なり、金額なりの合計がいくらになるのか調べたいときがある。それも年月日などのデータが複数列に作成されていて、表の並び替えも面倒、これ一回きりの集計ではないから、使いまわしのできる集計表にしたい。そんなときがある。今職場で、年間の行事予定表があり、横に曜日が表示され、1週間で次の行になる、その表の中に給食回数や授業時間数を入力する表を作って、一括して集計する。まだ完成はしていないが、ここに示すのはその前段階の小さな表である。
表の中に、学年、年月日、種類、個数というものが、2列にわたって入っている次のような表があります。このデータは「学年」などとそのデータの範囲にわかりやすく名前を定義してあります。関数の計算式を見るときはそのように見て下さい。
この表の中で、ある学年の、ある月の、ある種類の数を数え、その時の個数を集計したい。
3つの条件に合う表内のデータ集計
| 学年 | 年月 | 種類 | 回数 | 個数 |
|---|---|---|---|---|
| 1 | 1104 | x | 1 | 4 |
| 2 | 1104 | o | 1 | 5 |
| 3 | 1104 | x | 1 | 4 |
こんなときに利用できるのが、SUMPRODUCT関数である。この関数を次のように使って集計する。
SUMPRODUCT関数を利用して複数条件に一致する数値の計算
| 学年 | 年月 | 種類 | 回数 | 個数 |
|---|---|---|---|---|
| 1 | 1104 | x | =SUMPRODUCT((学年A=I16)*(TEXT(月日A,"yymm")=J16)*(種類A=K16)+(学年B=I16)*(TEXT(月日B,"yymm")=J16)*(種類B=K16)) | =SUMPRODUCT((学年A=I16)*(TEXT(月日A,"yymm")=J16)*(種類A=K16)*個数A+(学年B=I16)*(TEXT(月日B,"yymm")=J16)*(種類B=K16)*個数B) |
| 2 | 1104 | o | =SUMPRODUCT((学年A=I17)*(TEXT(月日A,"yymm")=J17)*(種類A=K17)+(学年B=I17)*(TEXT(月日B,"yymm")=J17)*(種類B=K17)) | =SUMPRODUCT((学年A=I17)*(TEXT(月日A,"yymm")=J17)*(種類A=K17)*個数A+(学年B=I17)*(TEXT(月日B,"yymm")=J17)*(種類B=K17)*個数B) |
| 3 | 1104 | x | =SUMPRODUCT((学年A=I18)*(TEXT(月日A,"yymm")=J18)*(種類A=K18)+(学年B=I18)*(TEXT(月日B,"yymm")=J18)*(種類B=K18)) | =SUMPRODUCT((学年A=I18)*(TEXT(月日A,"yymm")=J18)*(種類A=K18)*個数A+(学年B=I18)*(TEXT(月日B,"yymm")=J18)*(種類B=K18)*個数B) |
この結果が、次のようになる。
3つの条件に合う表内のデータ集計
| 学年 | 年月 | 種類 | 回数 | 個数 |
|---|---|---|---|---|
| 1 | 1104 | x | 1 | 4 |
| 2 | 1104 | o | 1 | 5 |
| 3 | 1104 | x | 1 | 4 |
この時に注意が必要なのは、年月日の日付をTEXT関数でYY/MMと表示するのだが、集計の表の中の「1104」と入力する前に、これらの年月を入力する欄のセル範囲を
セルの書式＞表示形式＞文字列
と設定しておく必要がある。数字を入力後などに文字列にしてもエクセルは別のものと読んでしまうようである。
このことに気づかせてくれたのは、次のサイトである。感謝したい。