Excelで売上、件数、工数、顧客情報などを管理していると、条件ごとに数値を集計したい場面が増えていきます。
たとえば担当者別の売上合計、商品別の販売件数、部署別の平均単価を求める作業です。
手作業でフィルターを切り替えて計算する方法もありますが、データが追加されるたびに再計算する必要があり、転記ミスも起こりやすくなります。
SUMIF関数、COUNTIF関数、AVERAGEIF関数を使えば、指定した条件に合うデータだけを自動で集計できます。
条件別集計で中心になる関数はSUMIF、COUNTIF、AVERAGEIFです。
複数の条件を指定する場合はSUMIFS、COUNTIFS、AVERAGEIFSを使います。
サンプルデータでは、1行目を見出し行として2行目以降を集計対象にします。
この記事では、Excel関数による条件別の合計、件数、平均の求め方を、数式の意味と実務での注意点を交えながら解説します。
数式をコピーして使えるように、セル参照の固定方法やエラーを防ぐ考え方も確認していきましょう。
条件別集計の基本手順
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 日付 | 担当者 | 商品 | 売上 |
| 4月1日 | 佐藤 | A商品 | 12000 |
| 4月2日 | 田中 | B商品 | 8500 |
| 4月3日 | 佐藤 | B商品 | 15000 |
それではまず、条件別に合計、件数、平均を集計する基本手順について解説していきます。
条件付き関数では、どの列を条件として調べるか、どの列の数値を計算するかを分けて考えることが大切です。
集計表と元データの対応関係
集計を始める前に、元データの表と結果を表示する集計表を分けて配置します。
上のサンプルでは、B列が担当者、C列が商品、D列が売上です。
たとえば佐藤さんの売上を調べる場合、条件を探す範囲はB2からB100、合計する範囲はD2からD100という構成になります。
条件範囲と集計範囲は、必ず同じ開始行と終了行にそろえることが基本です。
条件範囲がB2からB100なのに、合計範囲がD3からD100となっていると、行が一つずれて誤った結果になります。
表の途中に空白行があっても関数自体は計算できますが、後からデータを追加する運用では連続した表にしておくと安全です。
元データをテーブル形式に変換しておけば、行が増えた場合に参照範囲を広げる作業も減らせます。
集計式を作るときは、条件列、集計列、条件として入力するセルの三つを確認します。
担当者別集計なら条件列は担当者列、売上集計なら集計列は売上列です。
単一条件と複数条件の使い分け
条件が一つだけなら、SUMIF、COUNTIF、AVERAGEIFのように末尾がIFの関数を使います。
担当者が佐藤という条件だけで売上を合計したい場合が、単一条件の例です。
一方で、担当者が佐藤かつ商品がA商品というように、二つ以上の条件を同時に指定する場合はSUMIFS、COUNTIFS、AVERAGEIFSを使用します。
複数条件の関数では、すべての条件に一致した行だけが集計対象になります。
月別かつ部署別、商品別かつ担当者別、受注済みかつ金額が一定以上といった実務的な集計では、複数条件が役立ちます。
関数名の末尾にSが付くかどうかで迷ったときは、条件が一つならIF、二つ以上ならIFSと覚えると判断しやすくなります。
セル参照と絶対参照の設定
集計式を横や下にコピーする場合は、セル参照がどのように変化するかを意識します。
たとえば担当者名をF2に入力し、G2に集計結果を表示する場合、条件となるF2はコピー先に応じて変化してよい参照です。
しかし元データのB2からB100やD2からD100は、数式を下へコピーしても動かないように固定する必要があります。
=SUMIF($B$2:$B$100,F2,$D$2:$D$100)
ドル記号を付けた参照は絶対参照となり、数式をコピーしても範囲が変わりません。
データ範囲には絶対参照、検索したい条件の入力セルには相対参照を使うと、集計表を効率よく作成できます。
F4キーを押すと、選択中のセル参照にドル記号を付ける切り替えができます。
【操作のポイント】数式を一件だけ入力して確認してから、オートフィルで他の担当者へコピーすると参照ずれを見つけやすくなります。
SUMIF関数による条件別合計
| F列 | G列 |
|---|---|
| 担当者 | 売上合計 |
| 佐藤 | 27000 |
| 田中 | 8500 |
続いては、条件に一致する数値を合計するSUMIF関数を確認していきます。
売上金額、経費、作業時間、在庫数のように、数値を条件別に合算したいときの基本関数です。
SUMIF関数の構文と入力例
SUMIF関数の構文は、SUMIF関数の中に条件範囲、条件、合計範囲の順で指定します。
=SUMIF(条件範囲,条件,合計範囲)
担当者名がF2セルにあり、佐藤さんの売上合計をG2セルで求めるなら、次の式を入力します。
=SUMIF($B$2:$B$100,F2,$D$2:$D$100)
この数式は、B2からB100の中からF2と同じ担当者を探し、該当する行のD列の数値だけを合計します。
条件範囲には文字列を調べる列、合計範囲には実際に足し合わせる数値列を指定します。
合計範囲を省略すると、条件範囲そのものに入った数値を合計しますが、担当者名のような文字列列では通常は省略しません。
売上の列に文字列として保存された数値が混ざると期待どおりに合計されないため、数値形式になっているかも確認しましょう。
数値条件と比較演算子の指定
SUMIF関数では、文字列だけでなく数値に対する条件も指定できます。
たとえば売上が10000円以上の行だけを合計したい場合、条件には10000以上を表す比較演算子を入力します。
=SUMIF($D$2:$D$100,”>=10000″,$D$2:$D$100)
ダブルクォーテーションで囲んだ部分は、10000以上という条件を意味します。
比較演算子を使う条件は、原則としてダブルクォーテーションで囲みます。
基準値をセルに入力している場合は、文字列とセル参照をアンパサンドでつなぎます。
=SUMIF($D$2:$D$100,”>=”&F2,$D$2:$D$100)
この式なら、F2セルの金額を変更するだけで集計基準を切り替えられます。
条件の数値を式の中へ直接書くよりも、基準値を別セルに置くほうが、利用者にも条件が分かりやすい表になります。
ワイルドカードを使う部分一致集計
商品名や部署名に特定の文字を含むデータをまとめたい場合は、ワイルドカードを使えます。
アスタリスクは任意の文字列、疑問符は任意の一文字を表します。
たとえば商品名に商品という文字を含む売上を合計する場合は、次のような式になります。
=SUMIF($C$2:$C$100,”*商品*”,$D$2:$D$100)
この式では、A商品、B商品、季節商品など、商品という文字を含む項目が集計されます。
部分一致は表記ゆれを吸収しやすい一方で、想定外の文字列まで含める可能性があります。
正確な商品別売上が必要な場面では、商品コードを条件にするほうが安定します。
集計結果が想定より大きいときは、部分一致の範囲が広すぎないかを最初に確認するとよいでしょう。
【操作のポイント】売上合計がゼロになる場合は、担当者名の余分なスペース、全角と半角の違い、売上列の数値形式を確認します。
COUNTIF関数による条件別件数
| F列 | G列 |
|---|---|
| 商品 | 販売件数 |
| A商品 | 1 |
| B商品 | 2 |
続いては、条件に一致したデータの行数を数えるCOUNTIF関数を確認していきます。
受注件数、出勤日数、対応件数、在庫の該当数などを求めるときに便利です。
COUNTIF関数の構文と件数集計
COUNTIF関数の構文は、条件範囲と条件の二つです。
=COUNTIF(条件範囲,条件)
商品名がF2セルに入力されている場合、G2セルでその商品の販売件数を数える式は次のとおりです。
=COUNTIF($C$2:$C$100,F2)
この関数はC2からC100の中でF2と一致するセルの数を返します。
COUNTIF関数は金額の合計ではなく、条件に合うセルの個数を返す関数です。
売上表の一行が一件の受注を意味しているなら、COUNTIF関数の結果は受注件数になります。
ただし一つの注文が複数行に分かれているデータでは、行数と注文数が一致しないことがあります。
重複しない注文番号の件数を求めたい場合は、重複を除く集計方法を別途検討する必要があります。
空白以外と空白セルの数え方
データ入力の進捗を確認するときは、空白以外のセル数や空白セル数を数えることがあります。
空白以外を数える条件には、不等号と空文字列を組み合わせます。
=COUNTIF($D$2:$D$100,”<>”)
この式はD列に売上金額が入力されている行数を数えます。
反対に、未入力のセルを数える場合は、空文字列を条件にします。
=COUNTIF($D$2:$D$100,””)
数式の結果が空文字列になっているセルは、見た目が空欄でもCOUNTIFの判定に影響することがあります。
完全に何も入力されていないセルと、数式で空欄表示にしているセルを区別したい場合は、実際のデータ構造を確認しましょう。
進捗表では、入力済みの条件を数える列を明確に決めることが、正しい件数把握につながります。
COUNTIFS関数による複数条件の件数
担当者と商品を組み合わせて件数を数える場合は、COUNTIFS関数を使います。
COUNTIFS関数は、条件範囲と条件をセットで複数指定できる仕組みです。
=COUNTIFS($B$2:$B$100,F2,$C$2:$C$100,G2)
この式では、B列がF2の担当者であり、かつC列がG2の商品である行だけを数えます。
COUNTIFS関数では、条件範囲と条件を必ず対にして入力します。
日付条件を追加すれば、特定の月における担当者別の件数も求められます。
条件を増やすほど式は長くなりますが、どの範囲が何の条件なのかを順番に読むと理解しやすくなります。
複数条件で結果がゼロになる場合は、各条件を単独のCOUNTIF関数で試し、どの条件で絞り込みが外れているかを確認する方法が有効です。
【操作のポイント】件数を数える対象が行なのか、入力済みセルなのか、重複を除いた番号なのかを先に決めてから関数を選びます。
AVERAGEIF関数による条件別平均
| F列 | G列 |
|---|---|
| 担当者 | 平均売上 |
| 佐藤 | 13500 |
| 田中 | 8500 |
続いては、条件に一致する数値の平均を求めるAVERAGEIF関数を確認していきます。
顧客別の平均購入額、担当者別の平均売上、商品別の平均単価を比較したいときに活用できます。
AVERAGEIF関数の構文と計算対象
AVERAGEIF関数の構文は、SUMIF関数と同様に条件範囲、条件、平均範囲の順です。
=AVERAGEIF(条件範囲,条件,平均範囲)
F2セルに担当者名があり、G2セルに平均売上を表示する場合は、次の数式を使います。
=AVERAGEIF($B$2:$B$100,F2,$D$2:$D$100)
この式は、B列でF2と一致する行を探し、その行のD列だけを平均します。
AVERAGEIF関数は、条件に合う行の数値を合計し、その件数で割った平均値を自動で返します。
売上が未入力の行や文字列が含まれる行は、平均の計算対象から外れる場合があります。
平均値を見る際は、何件のデータをもとに計算した数値なのかも、COUNTIF関数で併記すると判断しやすくなります。
AVERAGEIFS関数による複数条件平均
複数の条件に一致するデータだけで平均を求める場合は、AVERAGEIFS関数を使用します。
AVERAGEIFS関数は、最初に平均範囲を指定し、その後に条件範囲と条件の組み合わせを入力する点が特徴です。
=AVERAGEIFS($D$2:$D$100,$B$2:$B$100,F2,$C$2:$C$100,G2)
この式は、担当者がF2であり、商品がG2である売上の平均を求めます。
AVERAGEIFS関数では、SUMIFS関数と同じく集計対象の範囲を先頭に入力します。
月別平均を求めるなら日付列に開始日以上、終了日未満という二つの条件を指定すると、月をまたぐデータでも安定して集計できます。
日付が見た目だけ日付で、実際には文字列として保存されていると条件判定に失敗することがあります。
日付条件を扱う前には、セルの表示形式だけでなく、Excelが日付を数値として認識しているか確認しましょう。
平均計算で起こりやすいエラー
条件に一致する数値が一件もない場合、AVERAGEIF関数ではDIV0エラーが表示されます。
これはゼロで割る計算ができないためであり、関数が壊れたわけではありません。
集計表を見やすくするには、IFERROR関数でエラー時の表示を指定します。
=IFERROR(AVERAGEIF($B$2:$B$100,F2,$D$2:$D$100),0)
この式では、平均を計算できないときに0を表示します。
0と表示することが誤解を招く業務では、空欄や該当なしという表示に変更する方法もあります。
平均が低い理由が実際にゼロなのか、対象データが存在しないのかは意味が異なるため、用途に合わせて表示を決めましょう。
【操作のポイント】平均値は件数とセットで確認し、少ない件数の平均を過大評価しないようにします。
複数条件集計と数式コピー
| F列 | G列 | H列 |
|---|---|---|
| 担当者 | 商品 | 売上合計 |
| 佐藤 | A商品 | 12000 |
| 佐藤 | B商品 | 15000 |
続いては、複数条件の集計と、作成した数式を正しくコピーする方法を確認していきます。
同じ関数を何度も手入力せず、参照を整えてオートフィルを使うことが効率化の鍵です。
SUMIFS関数の入力順序
SUMIFS関数では、合計範囲を最初に指定します。
その後に、条件範囲と条件を一組ずつ追加します。
=SUMIFS($D$2:$D$100,$B$2:$B$100,F2,$C$2:$C$100,G2)
この式は、D列を合計範囲とし、B列がF2、C列がG2の両方を満たす売上だけを合計します。
SUMIF関数とSUMIFS関数では、引数の並び順が異なるため注意が必要です。
SUMIF関数は条件範囲から始まりますが、SUMIFS関数は合計範囲から始まります。
条件を増やすほど入力ミスが起こりやすいため、数式バーを見ながら、条件範囲と条件が正しく組になっているか確認します。
範囲の行数がそろっていないとエラーや意図しない結果につながるため、開始セルと終了セルも点検しましょう。
オートフィルによる数式の展開
集計式を最初の行に入力したら、セル右下の小さな四角を下へドラッグしてオートフィルを実行します。
絶対参照にした元データ範囲は固定され、F2やG2のような条件セルはF3やG3へ自動的に変化します。
赤枠のH2セルに数式を入力し、右下のフィルハンドルを下方向へ操作すると、下の行にも式を展開できます。
数式をコピーする前に、データ範囲のドル記号が付いていることを確認しましょう。
表の右側へコピーする場合は、担当者列だけ固定する混合参照が必要になるケースもあります。
参照の仕組みを理解すると、月別の横展開や部門別の縦展開にも応用できます。
条件に日付を指定する集計式
月ごとの売上を集計するときは、開始日以上と翌月の開始日未満という二つの条件で指定する方法が安定します。
=SUMIFS($D$2:$D$100,$A$2:$A$100,”>=”&F2,$A$2:$A$100,”<“&EDATE(F2,1))
F2セルに集計したい月の初日を入力すると、この式はF2以上かつ翌月初日より前の日付を対象に売上を合計します。
月末日を直接指定するよりも、翌月の初日未満とするほうが、月末日の扱いで迷いにくい方法です。
時刻を含む日時データを集計する場合にも、この考え方は役立ちます。
集計式の結果が期待と異なるときは、日付列に時刻が含まれていないか、入力値が文字列になっていないかを確認しましょう。
【操作のポイント】複数条件の式は、条件を一つずつ追加して途中の結果を確かめると、修正箇所を特定しやすくなります。
集計結果の確認とエラー対策
| 確認項目 | 確認内容 |
|---|---|
| 参照範囲 | 条件範囲と集計範囲の行数 |
| データ形式 | 数値、日付、文字列の状態 |
| 条件文字列 | スペースや表記ゆれ |
続いては、条件別集計の結果を確認する方法と、よくあるエラーへの対処を確認していきます。
関数が入力できていても、元データの表記や参照範囲によって結果が変わるため、検算の習慣が重要です。
合計値と件数による検算
集計結果を確認するときは、SUMIF関数の結果だけを見るのではなく、件数も並べて表示します。
担当者別の売上合計が大きい場合でも、件数が多いためなのか、一件あたりの金額が高いためなのかを区別できます。
合計、件数、平均を横に並べると、平均に件数を掛けた値がおおむね合計に近いかを確認できます。
合計、件数、平均の三つを組み合わせることで、単一の集計値だけでは見えない入力ミスを発見しやすくなります。
たとえば平均だけが極端に高い場合は、分母となる件数が少ない可能性があります。
合計がゼロなのに件数がある場合は、売上列が空欄またはゼロでないかを調べます。
検算はデータが少ない段階で行うほど容易であり、集計表を配布する前の品質確認にもなります。
文字列と数値の表記ゆれ
条件別集計で一致しない原因として多いのが、文字列の表記ゆれです。
佐藤と佐藤の後ろにスペースがある値は、Excelでは別の文字列として扱われます。
全角スペースと半角スペース、全角数字と半角数字、部署名の略称などにも注意が必要です。
元データの入力規則を設定し、担当者名や商品名をリストから選択できるようにすると、表記ゆれを減らせます。
集計の精度は関数の正しさだけでなく、元データを統一して入力できているかに左右されます。
数字が左寄せになっている場合は文字列として保存されている可能性があります。
数値へ変換した後にSUMIF関数を再計算し、結果が変化するか確認しましょう。
参照範囲の拡張とテーブル機能
毎月データが増える表では、B2からB100のように固定した範囲では、101行目以降が集計漏れになることがあります。
余裕を持って大きい範囲を指定する方法もありますが、Excelのテーブル機能を使うと、追加行を自動的に集計対象へ含められます。
元データの任意のセルを選択し、挿入タブのテーブルを使って表をテーブル化します。
テーブル名を売上データに変更した場合、列名を使った分かりやすい数式を作成できます。
=SUMIF(売上データ[担当者],F2,売上データ[売上])
列記号ではなく見出し名で数式を読めるため、引き継ぎ時にも意図が伝わりやすくなります。
継続的に更新する集計表では、テーブル機能を使うと参照範囲の管理負担を抑えられます。
【操作のポイント】集計結果が古いままに見える場合は、計算方法が自動になっているか、データ更新後に再計算されているかを確認します。
まとめ エクセル関数で集計する方法（平均・件数・条件別の合計）
| 集計目的 | 主な関数 |
|---|---|
| 条件別の合計 | SUMIF、SUMIFS |
| 条件別の件数 | COUNTIF、COUNTIFS |
| 条件別の平均 | AVERAGEIF、AVERAGEIFS |
Excelで条件別に集計するには、目的に応じてSUMIF、COUNTIF、AVERAGEIFを使い分けます。
条件が一つならIFの付く関数を選び、担当者と商品のように複数の条件を組み合わせるならIFSの付く関数を選びましょう。
SUMIF関数は条件に合う売上や金額の合計、COUNTIF関数は該当する件数、AVERAGEIF関数は該当データの平均を求める関数です。
複数条件ではSUMIFS、COUNTIFS、AVERAGEIFSを使い、条件範囲と条件の組み合わせを正しく指定します。
元データ範囲には絶対参照を設定し、条件セルはコピー先に応じて動く参照にすると、集計表を効率よく完成させられます。
結果が合わないときは、参照範囲のずれ、文字列と数値の違い、スペースを含む表記ゆれ、日付形式を順番に確認します。
データが増え続ける表ではテーブル機能も活用し、集計漏れを防ぎましょう。
合計、件数、平均を組み合わせれば、売上管理や進捗管理の状況を多角的に把握できるようになります。
