1章：まず整理｜「何を集計したいか」を言語化して設計図を作る
Excelの集計が「複雑でしんどい」状態になる原因の多くは、関数そのものよりも集計したい内容が曖昧なまま書き始めてしまうことです。いきなりSUMIFSやXLOOKUPを打ち始める前に、まずは要件を日本語で整理して“設計図”を作るだけで、式の長さもミスも一気に減ります。
おすすめは、集計したいことを次の3点に分けて書き出す方法です。
- ①何を足す／数えるのか（指標）：売上金額？件数？平均？
- ②どんな条件で絞るのか（条件）：部署、担当者、期間、商品カテゴリ…
- ③結果をどう並べたいか（出力）：月別？担当者別？クロス集計？一覧？
例えば「関東の営業部の売上を月別に出したい」なら、設計図はこうです。
指標：売上金額の合計
条件：エリア=関東、部門=営業部、日付は各月の範囲
出力：2026年1月、2月…の列に月別合計を表示
ここまで言語化できれば、必要なデータ項目（列）も自然に決まります。最低限、日付・部門・エリア・売上金額が必要ですよね。もし元データに「エリア」がなく「都道府県」しかないなら、後で詰みます。こういう不足は、関数を組み始める前に潰すのが最短です。
さらに、設計図作りで必ず決めたいのが集計の粒度です。「担当者別」と言いながら、実は「担当者×商品カテゴリ別」だった、のように粒度がズレると式が急に複雑になります。迷ったら次の一文にしてみてください。
「1行＝何の集計結果か？」（例：1行＝担当者、1列＝月）
最後に、作業を安定させるための下準備もこの章で済ませます。
- 元データは“表（テーブル）”化：途中行追加に強く、参照も読みやすい
- 列名を人間が分かる名前に統一：日付/部署/金額などをブレさせない
- 値の表記ゆれを潰す：「営業」「営業部」「Sales」など混在は条件集計の敵
ここまで整うと、次章以降で扱うSUMIFS/COUNTIFSや動的配列関数は、ただの“実装手段”になります。つまり、難しいのは関数ではなく、設計。先に設計図を作ってから数式を書く。その順番だけで、複雑な集計が驚くほどシンプルになります。
2章：重くしない基本｜SUMIFS/COUNTIFSで“条件集計”を最短で組む
設計図ができたら、次は実装です。複雑に見える集計でも、まずはSUMIFS/COUNTIFSで「条件に合うものだけを足す・数える」を作れると、一気にシンプルになります。しかもピボットより軽く、表（テーブル）と相性も良いのが強みです。
基本形はこれだけ。
- SUMIFS：条件に合う金額などを合計する
- COUNTIFS：条件に合う行数（件数）を数える
例えばテーブル名をSales、列が[日付][部門][エリア][売上]だとします。「関東×営業部×指定月の売上合計」は次の形に落ちます。
=SUMIFS(Sales[売上],Sales[エリア],"関東",Sales[部門],"営業部",Sales[日付],">="&開始日,Sales[日付],"<="&終了日)ここでコツは日付条件を“範囲”で持つこと。月別に出したいなら、集計表側に「開始日」「終了日」を用意して、そこを参照するだけで横展開できます（1章で決めた「1列＝月」をそのまま式にできる）。
もう一つ、現場でよく使うのがCOUNTIFSです。例えば「関東×営業部×指定月の受注件数」を数えるなら、合計対象がなくなるだけ。
=COUNTIFS(Sales[エリア],"関東",Sales[部門],"営業部",Sales[日付],">="&開始日,Sales[日付],"<="&終了日)条件集計を“重くしない”ためのポイントも押さえておきましょう。
- 条件範囲は同じサイズに揃える：Sales[列名]で統一（途中だけA:A参照にしない）
- ワイルドカードは必要なときだけ：「*営業*」は便利だけど計算量が増えがち
- 条件セル参照に寄せる：文字を式に直書きせず、集計表に「エリア」「部門」セルを置く
特に最後は効きます。たとえば集計表のB2にエリア、C2に部門が入っているなら、式はこうなります。
=SUMIFS(Sales[売上],Sales[エリア],$B$2,Sales[部門],$C$2,Sales[日付],">="&開始日,Sales[日付],"<="&終了日)これで「関東→関西」などの切り替えがセルの変更だけになり、式の修正ミスも激減します。まずはSUMIFS/COUNTIFSで条件集計を最短で組み、足りない複雑さは次章の分解（補助列＋役割分担）で安全に増やしていきましょう。
3章：複雑さは分解で勝つ｜補助列＋関数の役割分担で読みやすくする
SUMIFS/COUNTIFSに条件を増やしていくと、ある瞬間から式が「長い・読めない・直せない」になります。ここで効くのが、複雑さを補助列に逃がして、関数に役割分担させる考え方です。やることはシンプルで、元データ側で“判断しやすい形”に整えてから集計するだけ。
典型例が「都道府県からエリア（関東/関西…）を判定して集計したい」ケース。SUMIFSの条件にネストIFを詰め込むと地獄なので、Salesテーブルに[エリア]補助列を追加します。
=XLOOKUP([@都道府県],MstArea[都道府県],MstArea[エリア],"不明")あとは2章の形に戻すだけで、集計式は短く保てます。
=SUMIFS(Sales[売上],Sales[エリア],$B$2,Sales[部門],$C$2,Sales[日付],">="&開始日,Sales[日付],"<="&終了日)次に効くのが、日付の切り口を「毎月」「四半期」「週」などに揃えたいとき。集計表側で開始日・終了日を持つ方法も強いですが、元データ側に[年月]のようなキーを作っておくと、条件が1つ減ってさらに読みやすくなります。
=TEXT([@日付],"yyyy-mm")月別集計は、日付範囲ではなく年月で指定できます。
=SUMIFS(Sales[売上],Sales[エリア],$B$2,Sales[部門],$C$2,Sales[年月],E$1)ポイントは、補助列は「人間が見て即わかる」状態にすること。例えば「キャンペーン対象」「新規/既存」「粗利区分」など、判定ロジックが絡むものほど補助列向きです。式の中でやるとブラックボックス化しますが、補助列なら列見出しがそのまま説明書になります。
そして最後のコツが関数の役割分担です。おすすめは次の2層構造。
- 補助列（前処理）：分類・キー作成・表記ゆれ吸収（例：エリア、年月、区分）
- 集計式（集約）：SUMIFS/COUNTIFSで足す・数えるだけに徹する
この分け方にすると、仕様変更（例：「関東の定義を変える」「新しい都道府県マスタを追加」）が入っても、直す場所は補助列 or マスタに限定できます。逆に、1本の巨大な式に詰め込むと、どこを変えたらいいか分からず壊れやすい。
集計が複雑になってきたら、「式を頑張る」のではなく、分解して、読める部品にする。これだけで、Excelの集計は一気に“チームで運用できる品質”になります。
4章：一発集計の本命｜XLOOKUP・FILTER・UNIQUEで“動的集計”を作る
ここまでのSUMIFS/COUNTIFSは「集計表の形（行・列）」を先に決めて、そこへ数値を流し込む発想でした。一方で、データが増えたり担当者が入れ替わったりすると、集計表の行追加・式コピーが地味に面倒です。そこで本命になるのが、UNIQUE・FILTER・XLOOKUP（＋必要ならSUMIFS）で作る“動的集計”。元データが増えても、集計表が自動で伸びる状態を作れます。
例えば「対象月×対象部門×対象エリアで、担当者別の売上を一覧化したい」ケース。まずは担当者リストを固定で作らず、条件で絞ったデータから自動生成します。
=UNIQUE(FILTER(Sales[担当者], (Sales[エリア]=$B$2)*(Sales[部門]=$C$2)*(Sales[年月]=$E$1) ))これで、条件に合う担当者だけがスピル（自動展開）して並びます。新しい担当者が出てきても行を足す必要はありません。
次に、その担当者ごとの売上を出します。ここでやりがちなのが「XLOOKUPで金額を取る」ですが、売上のように同一担当者が複数行あるデータは、基本的に“合計”が必要なので、XLOOKUP単体では不足します。おすすめは、担当者一覧（UNIQUE）×SUMIFSで“動的に一発集計”する形です。
担当者一覧がA5#にスピルしている前提で、横に売上を出すなら：
=SUMIFS(Sales[売上], Sales[エリア], $B$2, Sales[部門], $C$2, Sales[年月], $E$1, Sales[担当者], A5#)ポイントは最後の条件にA5#（スピル範囲）を渡すこと。これで担当者の人数分がまとめて計算され、結果も縦にスピルします。つまり、一覧作成（UNIQUE）も集計（SUMIFS）も、式1本ずつで終わるわけです。
ではXLOOKUPは何に効くのか？答えは「1対1の情報を付け足す」場面です。例えば担当者名から部署・等級・上長などを引いて表示したいとき。集計表に「担当者」「売上」「等級」を並べたいなら、等級列はこう書けます。
=XLOOKUP(A5#, MstStaff[担当者], MstStaff[等級], "不明")これもスピル対応なので、担当者が増減しても自動追従。3章で触れた「補助列＋役割分担」をここでも徹底し、集計＝SUMIFS、属性付与＝XLOOKUP、母集団生成＝FILTER/UNIQUEと役割を分けると破綻しません。
最後に、FILTERを使うと「集計以前に、必要な行だけを抜き出した一覧」を作れます。例えば上司に見せる用に「関東×営業部×当月の明細だけ」を別シートに出すなら：
=FILTER(Sales, (Sales[エリア]=$B$2)*(Sales[部門]=$C$2)*(Sales[年月]=$E$1), "該当なし")明細→担当者一覧（UNIQUE）→集計（SUMIFS）→属性付与（XLOOKUP）までを動的に繋げると、Excelは「手作業で整える道具」から更新に強い集計システムになります。次章では、こうした動的集計を壊れにくく運用するためのエラー対策・参照固定・命名に踏み込みます。
5章：壊れない運用へ｜エラー対策・絶対参照・命名でメンテ性を上げる
関数で集計が作れても、現場で困るのは「翌月に更新したら崩れた」「誰かが触って#N/Aだらけ」「式をコピーしたら参照がズレた」問題です。ここを防ぐだけで、Excelは一気に“運用できるツール”になります。ポイントは①エラーを出さない（見せ方を決める）②参照をズラさない③どこを触ればいいか明確にするの3つです。
1) エラーは「消す」のではなく「意味のある表示」にする
XLOOKUPやFILTERは便利な反面、未登録データや該当なしでエラーが出やすい。放置すると集計表が読めなくなるので、エラー時の表示ルールを決めておきます。
- マスタに存在しない＝「不明」（データ整備が必要だと分かる）
- 条件に該当なし＝0（集計結果として自然） or 「該当なし」（一覧として自然）
例：担当者→等級の付与（未登録は「不明」）
=XLOOKUP(A5#, MstStaff[担当者], MstStaff[等級], "不明")例：FILTERで明細を抜く（0件なら「該当なし」）
=FILTER(Sales, (Sales[エリア]=$B$2)*(Sales[部門]=$C$2)*(Sales[年月]=$E$1), "該当なし")さらに「計算式の都合で出るエラー」はIFERRORでまとめて潰せますが、乱用はNG。不明（マスタ漏れ）と該当なし（正常）を同じ見え方にしないのが、後々の修正コストを下げます。
2) 絶対参照で「コピーしても壊れない式」にする
集計表は横展開・縦展開が前提。ここで参照が動くと一発で壊れます。基本は条件セル＝絶対参照、集計対象＝テーブル参照に寄せること。
例：エリア(B2)・部門(C2)は固定し、月(E1)だけ列に合わせて動かす
=SUMIFS(Sales[売上], Sales[エリア], $B$2, Sales[部門], $C$2, Sales[年月], E$1)- $B$2：どこにコピーしても固定（条件の軸）
- E$1：列だけ動く（横にコピーして月が切り替わる）
この「どこを固定して、どこを動かすか」を先に決めると、式の事故が激減します。
3) 命名で「式の説明書」を埋め込む
20代のサラリーマンが引き継ぎで詰むのは、式が読めないことより“どのセルが設定値なのか分からない”こと。そこで、条件セルに名前を付けて「触っていい場所」を明確にします。
例：B2を selArea、C2を selDept、E1を selYm などに命名（数式→名前の管理）。すると式はこうなります。
=SUMIFS(Sales[売上], Sales[エリア], selArea, Sales[部門], selDept, Sales[年月], selYm)文字通り“読める日本語”に近づき、見直し・修正が速くなります。あわせて、マスタ表・元データ表は1章で触れた通りテーブル化＋分かる列名を徹底すると、参照切れも起きにくいです。
エラー表示のルール、参照固定、命名。この3点を押さえるだけで、あなたの集計は作って終わりのExcelから、更新して使い続けられるExcelに変わります。
コメント