LWP | SUMIFSと複合参照でクロス集計表を作る
SUMIFSと複合参照でクロス集計表を作る
売上明細を「エリア×担当者」で見比べる、ナマズさんとフナさんの集計相談
Copyright © 2026 LWP 山中 一弘
本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。
ストーリー
売上の明細は揃ったのに、会議で見たい「エリア別・担当者別」の比較ができません。営業のナマズさんが見たい切り口を伝え、フナさんがSUMIFSで交点を集計する方法を考えます。式を横と下へ広げたあとには、もう一つ確かめることがありました。
Excelのテーブルと標準関数を使う、説明用の物語です。図1～6は同じ5件の売上明細を扱い、図7の二つのケースはゼロの意味を比べるための別例です。
1 売上明細を、見比べる表へ
図1｜元表はExcelのテーブル「売上データ」。集計シートのB5に、東京と田中の交点を求める式を入れる。
2 交点は「東京、かつ田中」
図2｜二つの条件を両方満たす行を合計する。「東京、または田中」の集計とは異なる。
3 SUMIFSに、三つの範囲を渡す
図3｜合計範囲を先頭に置き、そのあとへ条件範囲と条件を対で渡す。図は引数の対応図で、貼り付け用の式は記事末尾に掲載。
4 左を見る列、上を見る行を固定
図4｜複合参照で、固定する方向と動かす方向を分ける。両方を完全に固定すると、交点ごとの条件が切り替わらない。
5 元表の列も、横フィルでずらさない
図5｜集計表の見出しを固定する$と、元表の列を固定する構造化参照は別の役割。エリア列と担当者列も同じ書き方に揃える。
6 表が埋まったら、総額を照合
図6｜今回の見出しは元表の全エリア・全担当者を重複なく含む。総額の一致を確かめ、代表の交点も明細へ戻って照合する。
7 「0円」と「該当なし」は分けて読む
図7｜合計が0というだけでは、該当行がないとは判断できない。件数を数えると区別できる。
まとめ
ナマズさんが決めた「エリア×担当者」という切り口を、フナさんがSUMIFSの二つの条件へ置き換えました。左の見出しは列を固定、上の見出しは行を固定し、元表の列指定も横フィルでずれない形に揃えます。
表が埋まったら、元の総額と代表の交点を照合します。ゼロの意味まで説明したいときには、COUNTIFSで該当行の件数も確かめます。次に商品と月で見たいなら、まずどちらを縦・横に並べるかを決めるところから始められます。
今回の仕掛け
SUMIFSは、同じ行で複数の条件を満たす売上金額を合計します。合計範囲と条件範囲は、行数だけでなく対応する行も揃えます。元表をExcelのテーブルにしておくと、テーブルへ追加した行を構造化参照で扱えます。
$A5はA列を固定して行番号を動かし、B$4は4行目を固定して列文字を動かす複合参照です。一方、売上データ[[売上金額]:[売上金額]]は、同じ列を両端に指定して、横方向のフィルで参照列が動かないようにします。通常のコピーとフィルハンドルによる横フィルは、構造化参照の調整が同じとは限りません。
集計から漏れた見出しや重複した見出しがあると、総額の照合は崩れます。総額が一致しても、個々の交点の取り違えを見逃すことはあるため、「東京×田中」のような代表例を元の明細と照合します。売上金額は数値で入力し、エリア名や担当者名の余分な空白・表記違いも確認します。
合計0には、該当なし、0円の行、正負の金額の相殺などがあります。表示形式で0を「－」に変えても、その意味は判別できません。図7はこのうち最初の二つを比較しています。
今回の数式のサンプル
数式例 エリアと担当者が一致する売上を合計
01 |
|
|---|---|
02 |
|
03 |
|
04 |
|
05 |
|
数式の説明
合計範囲は売上金額列です。エリア列が$A5の値と一致し、担当者列がB$4の値と一致する行を合計します。三つの構造化参照は同じテーブルのデータ行を指します。
入力・概要
元データ用のシートに、見出しを「エリア」「担当者」「売上金額」の順に置き、次の5件を入力します。見出しを含む範囲をExcelのテーブルに変換し、テーブル名を「売上データ」にします。金額の単位は円です。
東京／田中／120000
東京／鈴木／85000
大阪／田中／95000
東京／田中／70000
大阪／鈴木／110000
別の集計シートは通常のセル範囲を使います。A4を「エリア」、B4を「田中」、C4を「鈴木」、D4を「佐藤」、A5を「東京」、A6を「大阪」、A7を「名古屋」にします。B5の数式バーへ式の全文を貼り付け、右のD5まで、続いてB5:D5を下の7行目までフィルします。
結果
期待結果は、東京行が190000・85000・0、大阪行が95000・110000・0、名古屋行が0・0・0です。B5:D7の合計は480000となり、元表5件の合計と一致します。これは掲載データと数式の読解・計算による確認で、今回新たにExcel上で実行した測定結果ではありません。
読むポイント
C5の式では担当者の条件がC$4へ、B6の式ではエリアの条件が$A6へ変わります。元表の三つの列指定は変わりません。フィル後にこの対応を見れば、どこを固定しているか確かめられます。
数式例 同じ条件に該当する行数を数える
01 |
|
|---|---|
02 |
|
03 |
|
04 |
|
数式の説明
COUNTIFSには合計範囲を渡しません。エリアと担当者の条件を両方満たす行を1件ずつ数えます。条件範囲と見出しの対応は、先ほどのSUMIFSと同じです。
入力・概要
新しい「件数集計」シートに、売上集計と同じ見出しをA4:D7へ配置します。このシートのB5に式を入力し、右と下へフィルします。元の売上集計の式は残して、金額と件数を見比べます。
結果
元の5件を使うと、東京行の件数は2・1・0、大阪行は1・1・0、名古屋行は0・0・0となり、合計は5件です。図7の「0円の行が1件」は比較用の別例で、この5件に追加したデータではありません。
読むポイント
件数が0なら、この二つの条件に該当する行がありません。合計が0でも件数が1以上なら、該当する明細の金額を確認します。金額と件数を一緒に見ることで、会議でゼロの意味を説明しやすくなります。
出典メモ
LWP記事元資料「Excelでデータベース形式の表をマトリックス形式に変換する ― SUMIFSと複合参照の定番パターン」。SUMIFS・COUNTIFSの掲載式と、売上明細5件を引き継ぎました。
Microsoft Support：SUMIFS関数。複数条件による合計と引数の順序を確認しました。
Microsoft Support：COUNTIFS関数。複数条件に一致する行の件数を確認しました。
Microsoft Support：Excelテーブルで構造化参照を使用する。テーブル列の参照と、コピー・フィル時の調整を確認しました。
Microsoft Support：相対参照・絶対参照・複合参照を切り替える。列または行だけを固定する指定を確認しました。