これまでSUMIFやSUMIFS、COUNTIFS、AVERAGEIFSといった、条件に一致する行だけを集計する関数を1つずつ見てきました。今回はそれらを実際の場面でどう組み合わせて使うか、「月別×選手別の安打数クロス集計表」を作る例で解説します。
複合条件集計とは
SUMIFSやCOUNTIFSは複数条件を扱えますが、それ単体では「1つのセルの答え」しか出せません。行見出し(選手名)と列見出し(月)をそのまま条件として組み込み、同じ数式を表全体にコピーすることで、初めて「月別×選手別」のようなクロス集計表になります。今回は関数そのものの新しい仕組みではなく、既に覚えた関数を「表の形」に組み立てる考え方が主役です。
今回使う数式の基本形はこうです。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2)
成績管理表での実例:月別×選手別の安打数クロス集計表
大阪グーニーズの「成績データ」シートには、試合ごとの記録が1行1打席の形で入っているとします。A列に試合日、B列に選手名、C列に安打数、そしてD列に「その試合が何月か」を取り出した月の数値を入れておきます。
| 試合日 | 選手名 | 安打 | 月 |
|---|---|---|---|
| 4/6 | SUZUKI | 2 | 4 |
| 5/11 | SUZUKI | 1 | 5 |
| 5/18 | NAKATA | 3 | 5 |
| … | … | … | … |
このデータをもとに、別シート(または同シートの空いた場所)に、行=選手名、列=月のクロス集計表を作ります。
| 選手名 | 4月 | 5月 | 6月 |
|---|---|---|---|
| SUZUKI | 6 | 4 | 5 |
| NAKATA | 3 | 7 | 2 |
このとき、表の左上(SUZUKI×4月のセル)に入る数式はこうなります。
=SUMIFS($C$2:$C$200, $B$2:$B$200, $A10, $D$2:$D$200, B$9)
「選手名が$A10(このセルの行)と一致し、かつ月がB$9(このセルの列見出し)と一致する行の安打だけを合計する」という式です。この1つのセルを表全体にコピーするだけで、すべてのマス目が自動で埋まります。
成績データシートのD列に「=MONTH(A2)」と入力し、試合日から月だけを取り出しておきます。これが後で条件範囲になります。
別シートに、縦方向に選手名を、横方向に月(4,5,6…)を並べた表の枠だけを先に作ります。
表の一番左上(選手×月が交わる最初のセル)にだけ、上記の数式を入力します。まだこの段階ではコピーしません。
数式内の$の位置を確認したら、そのセルをコピーし、表の残り全マスに貼り付けます。1つの数式が行・列に応じて自動的に条件を変えてくれます。
しくみを理解する:$(絶対参照)が表を成立させている
クロス集計表が1つの数式のコピーだけで成立するのは、$の付け方を「行見出し」と「列見出し」で使い分けているからです。
- $A10(列だけ固定):右方向にコピーしても常にA列(選手名)を見続け、下方向にコピーすると行番号がずれて次の選手を見る
- B$9(行だけ固定):下方向にコピーしても常に9行目(月の見出し)を見続け、右方向にコピーすると列がずれて次の月を見る
- $C$2:$C$200など集計対象・条件範囲そのものは、行も列も完全固定にして、コピーしてもズレないようにしておく
よくあるミス:表が崩れる原因
$を全く付けずに数式を作ってしまうと、コピーしたときに選手名の参照先や月の参照先までずれてしまい、表のマス目ごとに違う(間違った)条件で集計されてしまいます。数式を確定する前に、必ず$の位置を見直してください。
$C$2:$C$200(安打)に対して条件範囲を$B$2:$B$199(選手名)のように1行だけ短く指定してしまうと、範囲のサイズが一致せずエラーになります。複数条件を指定するときは、すべての範囲の行数を必ず揃えてください。
- クロス集計表は、SUMIFS/COUNTIFSの条件に行見出し・列見出しをそのまま使うことで作れる
- 左上の1マスだけ数式を作り、$の使い分け(行固定・列固定・完全固定)を正しく設定してから表全体にコピーするのが基本の作り方
- 表が崩れるときは、$の付け忘れか範囲の行数不一致を疑う
次回予告
次回のテーマは「IF関数」です。ここまでの「集計」から一歩進んで、条件式の真偽によって処理を分岐させる仕組みを、打席結果の記号から「安打かどうか」を自動判定する例で解説していきます。
▶︎ 次回：IF関数の使い方としくみ
コメント