エクセルで売上表や在庫表を管理していると、部署別、商品別、担当者別など、項目ごとの合計をすばやく確認したい場面があります。
単純な合計ならSUM関数で計算できますが、表示中の行だけを集計したい場合や、並べ替えたグループごとに合計行を作りたい場合には、用途に合った方法を選ぶことが重要です。
この記事では、SUBTOTAL関数を使う自動集計、エクセルに備わる小計機能、SUMIF関数による条件別の合計などを、サンプル表と操作例を交えて解説します。
小計を出す代表的な方法は、SUBTOTAL関数、データタブの小計機能、SUMIF関数の3種類です。
フィルター後の合計を出すならSUBTOTAL関数、グループごとの集計行を挿入するなら小計機能、一覧を崩さず条件別の集計表を作るならSUMIF関数が向いています。
1行目に見出しがある売上データを例に、実務で迷いやすいポイントも確認していきましょう。
SUBTOTAL関数による項目別自動集計
それではまず、フィルターで絞り込んだ結果にも連動するSUBTOTAL関数について解説していきます。
| 部署 | 商品 | 売上金額 |
|---|---|---|
| 営業部 | 商品A | 120,000 |
| 営業部 | 商品B | 85,000 |
| 企画部 | 商品A | 96,000 |
基本の数式入力
SUBTOTAL関数は、指定した範囲を集計しつつ、フィルターで非表示になった行を自動的に計算対象から外せる関数です。
売上金額がC2からC100に入力されている場合、集計結果を表示したいセルに次の数式を入力します。
＝SUBTOTAL(9,C2:C100)
先頭の9は合計を表す集計方法の番号であり、C2:C100は集計したい売上金額の範囲です。
フィルターで営業部だけを表示すると、SUBTOTAL関数は営業部の表示行だけを合計します。
通常のSUM関数では非表示の行も含めて計算されるため、絞り込み中の合計を画面上で確認したいときにはSUBTOTAL関数が便利です。
数式を入力したセルは、元データの表の直下や右側など、集計範囲に含まれない位置に置きましょう。
集計番号の選び方
SUBTOTAL関数では、9以外にも平均、件数、最大値、最小値などを選択できます。
売上の合計には9、平均単価には1、入力済み件数には3を使うというように、確認したい内容に合わせて番号を変えます。
合計は9、平均は1、数値が入ったセルの件数は2、空白以外のセルの件数は3です。
集計方法の番号を変えるだけで、同じ範囲から異なる集計結果を取り出せます。
また、101から111の番号を使うと、フィルターだけでなく、手動で非表示にした行も除外できます。
手動で行を隠したデータまで集計から外したい場合は、9ではなく109を指定することがポイントです。
共同で表を扱う場合は、誰が行を非表示にしたかによって結果が変わらないよう、運用ルールも決めておくと安心です。
フィルターと集計セルの配置
フィルターを利用するには、まず1行目の見出しを含めて表内のいずれかのセルを選択します。
データタブにあるフィルターボタンを選ぶと、各列見出しに絞り込み用の矢印が表示されます。
部署列の矢印から営業部だけを選択すると、企画部などの行は一時的に非表示になります。
この状態でSUBTOTAL関数の結果を見ると、表示されている営業部の金額だけが合計されます。
集計セルをテーブルの最終行のすぐ下に置く場合は、後からデータを追加した際に範囲がずれないよう注意が必要です。
【操作のポイント】データが増える表では、集計範囲を余裕のある行まで指定するか、テーブル機能と組み合わせて参照範囲の更新漏れを防ぎましょう。
小計機能によるグループ別集計行
続いては、並べ替えたデータにグループごとの合計行を自動挿入する小計機能を確認していきます。
| 部署 | 担当者 | 売上金額 |
|---|---|---|
| 営業部 | 田中 | 120,000 |
| 営業部 | 佐藤 | 85,000 |
| 企画部 | 鈴木 | 96,000 |
集計前の並べ替え
小計機能は、同じ項目が連続して並んでいることを前提にして、項目が切り替わる場所へ集計行を挿入します。
そのため、最初に部署列を基準として昇順または降順に並べ替える作業が必要です。
表内のセルを選択し、データタブの並べ替えから、最優先されるキーを部署に設定します。
営業部と企画部の行が混在したまま小計を実行すると、同じ部署が複数のまとまりに分かれてしまいます。
部署ごとの小計を正確に出すには、部署名が途切れない状態に整えることが基本です。
担当者別に小計を出したい場合も同様に、担当者列で並べ替えてから操作します。
データタブの小計設定
並べ替えが完了したら、表内の任意のセルを選択し、データタブにある小計を選びます。
小計ダイアログでは、グループの基準を部署、集計の方法を合計、集計するフィールドを売上金額に設定します。
設定を確定すると、営業部の最終行の下には営業部の集計行が入り、企画部の最終行の下にも企画部の集計行が入ります。
グループの基準は部署、集計の方法は合計、集計するフィールドは売上金額を選択します。
小計行にはSUBTOTAL関数が自動で設定されるため、左端のアウトライン記号を使って明細を折りたたむことも可能です。
明細を隠して部署別の小計だけを表示できるため、会議用の一覧を短時間で作成できます。
小計の削除と再設定
集計対象やグループの基準を変更したいときは、古い小計行を残したまま再設定しないようにしましょう。
データタブの小計を開き、すべて削除を選択すると、挿入された小計行とアウトラインをまとめて解除できます。
元の明細データは削除されないため、並べ替え直してから新しい条件で小計を作成できます。
小計機能は、すでにエクセルのテーブルとして設定されている範囲では利用できないことがあります。
その場合はテーブルを範囲に変換するか、ピボットテーブルやSUMIFS関数を使う方法を検討しましょう。
【操作のポイント】小計機能を使う前には、集計の単位となる列で必ず並べ替えを行い、既存の小計がある場合はすべて削除してから設定します。
フィルター連動と集計範囲の管理
続いては、フィルターとSUBTOTAL関数を組み合わせて、表示中のデータだけを確実に集計する方法を確認していきます。
| 月 | 部署 | 売上金額 | 集計結果 |
|---|---|---|---|
| 4月 | 営業部 | 120,000 | |
| 4月 | 企画部 | 96,000 | |
| 合計 | 216,000 |
オートフィルターの設定
表の1行目に項目名があることを確認し、見出しを含むデータ範囲内のセルを一つ選択します。
データタブからフィルターを選択すると、部署、商品、月などの見出しにドロップダウン矢印が付きます。
部署列で営業部にチェックを残し、他の部署のチェックを外すと、営業部の行だけが画面に残ります。
SUBTOTAL関数は、フィルターの状態を変更するたびに自動で再計算されるため、担当者が条件を切り替えても数式を書き換える必要がありません。
月と部署のように複数条件で絞り込んでも、表示された行だけが対象になります。
数式の作成とオートフィル
数式を入力するセルが複数ある場合は、最初のセルにSUBTOTAL関数を入力し、その後にオートフィルで下方向へコピーできます。
たとえばD2に集計式を作成し、同じ形式の集計欄が下に並ぶ場合は、D2の右下に表示される小さな四角を下へドラッグします。
最初のセルに入力する数式は、＝SUBTOTAL(9,C2:C100)です。
オートフィル前に、集計範囲C2:C100が意図どおり固定されているかを確認しましょう。
行方向にコピーする場合は、範囲の列を固定したいケースもあります。
そのときはC2:C100を$C$2:$C$100のように絶対参照にすると、コピー先でも売上金額列を参照し続けられます。
数式のコピーでは、相対参照と絶対参照の違いが集計ミスの原因になりやすい部分です。
集計範囲の追加対応
毎月明細が増える表では、あらかじめC2:C1000のように余裕を持たせた範囲を指定する方法があります。
空白セルは合計に影響しないため、一定の範囲まで広く指定しても通常は問題ありません。
ただし、別の集計行やメモ欄まで範囲に含めると二重計上につながるため、最終行の構成には注意しましょう。
データを追加する頻度が高い場合は、表をテーブル化して列名を使った参照にすると、行の追加に追従しやすくなります。
テーブル化するには、データ範囲を選択して挿入タブからテーブルを選択します。
集計式を置く場所や小計機能との相性は変わるため、一覧の更新方法に合わせて採用する仕組みを決めるとよいでしょう。
【操作のポイント】フィルター連動の合計にはSUBTOTAL関数を使い、数式の範囲に集計セル自身を含めないように設定します。
SUMIF関数による条件別小計
続いては、元データを並べ替えずに、項目別の小計を別の集計表へ表示するSUMIF関数を確認していきます。
| 部署 | 売上金額 | 集計部署 | 部署別小計 |
|---|---|---|---|
| 営業部 | 120,000 | 営業部 | 205,000 |
| 営業部 | 85,000 | 企画部 | 96,000 |
| 企画部 | 96,000 |
部署名を条件にした集計
SUMIF関数は、指定した条件に一致する行だけの数値を合計する関数です。
A列に部署、C列に売上金額があり、E2に営業部という文字が入力されている場合は、F2に次の数式を入力します。
＝SUMIF(A2:A100,E2,C2:C100)
最初のA2:A100は部署名を探す範囲であり、E2は営業部という条件、C2:C100は合計する売上金額の範囲です。
条件を数式内へ直接書かず、E2のようなセルを参照すると、部署名を変更するだけで集計結果を切り替えられます。
集計表に営業部、企画部、総務部と部署名を縦に並べれば、各行の数式をコピーして部署別小計を作れます。
文字列の表記ゆれ対策
SUMIF関数で期待した合計にならない場合は、条件となる部署名の表記が一致しているかを確認します。
営業部と営業部の後ろに半角スペースが入った文字列は、見た目が近くても別の値として扱われます。
全角と半角、不要な空白、部署名の略称などが混在すると、集計漏れが起きるかもしれません。
入力規則のリストを使って部署名を選択式にすると、同じ文字列を統一して入力しやすくなります。
集計式を複雑にする前に、元データの表記をそろえることが正確な小計への近道です。
データの先頭や末尾に余分な空白がある場合は、TRIM関数で整形した列を作る方法もあります。
複数条件への拡張
部署だけでなく、月や商品区分も指定して小計を出したいときはSUMIFS関数を使います。
たとえば営業部かつ4月の売上を集計する場合は、売上範囲の後ろに条件範囲と条件を追加します。
＝SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2)
この数式では、C列の売上金額を合計し、A列がE2の部署で、B列がF2の月である行だけを対象にします。
条件が増えても、合計範囲と各条件範囲の開始行と終了行をそろえることが大切です。
範囲の行数が一致していないと、計算エラーや意図しない結果につながります。
【操作のポイント】別表へ項目別小計を作る場合はSUMIF関数を使い、集計条件はセル参照にして数式をコピーしやすくします。
小計計算のエラー回避と表の整え方
続いては、小計が合わないときに見直したい数値形式、範囲指定、重複集計の防止について解説していきます。
| 確認項目 | 正しい状態 | 注意する状態 |
|---|---|---|
| 売上金額 | 数値として右寄せ表示 | 文字列として左寄せ表示 |
| 集計範囲 | 明細行のみ | 合計行まで含む |
| 条件列 | 表記が統一済み | 空白や表記ゆれが混在 |
数値が文字列になっている場合
金額セルに数字が見えていても、文字列として保存されているとSUM関数やSUBTOTAL関数で合計されない場合があります。
セルの左上に緑色の三角が表示される、または数値が左寄せになっている場合は、文字列の可能性を疑いましょう。
警告アイコンが表示されていれば、数値に変換を選ぶことで修正できることがあります。
コピー元に円記号や不要な空白が含まれていると、見た目は金額でも計算対象にならないことがあります。
区切り位置機能やVALUE関数を使う方法もありますが、まず元データの形式を確認することが優先です。
二重計上を防ぐ範囲設定
集計結果が実際より大きい場合は、数式の範囲内に小計行や総計行が含まれていないかを確認します。
特に小計機能を使った表でさらにSUM関数を設定すると、明細と小計を同時に合計してしまうことがあります。
明細の売上がC2からC50で、小計や総計がC51以降にある場合は、明細だけのC2:C50を対象にします。
SUBTOTAL関数はネストされたSUBTOTALを重複して集計しない仕組みがありますが、SUM関数では注意が必要です。
小計のある表を再集計するときは、どの行が明細でどの行が計算結果なのかを先に区別しましょう。
表示形式と計算結果の確認
数式の結果が正しくても、セルの表示形式によって小数点以下が丸められ、合計が違って見えることがあります。
ホームタブの数値グループから桁区切りや小数点以下の表示桁数を調整すると、実際の値を確認しやすくなります。
金額を扱う表では、円単位で表示するのか、千円単位で表示するのかを表の見出しに明記すると誤読を防げます。
表示上の数値と数式バーの実際の値を見比べる習慣が、集計表のチェック精度を高めます。
大きなデータでは、部署別の小計を合計した値と全体の総計を比較する検算も有効です。
【操作のポイント】小計が合わないときは、数値が文字列になっていないか、集計範囲に合計行を含めていないか、条件の表記がそろっているかを順に確認します。
まとめ エクセルで小計を出す方法（小計機能・項目ごとの自動集計）
エクセルで小計を出す方法は、集計したい場面によって使い分けることが大切です。
フィルターで表示されている行だけを合計したいときは、SUBTOTAL関数を使います。
部署や担当者など、並べ替えたグループごとに合計行を入れたいときは、データタブの小計機能が便利です。
元の一覧を変更せず、別の場所に項目ごとの集計表を作りたいときは、SUMIF関数やSUMIFS関数が役立ちます。
集計結果が合わないときは、数値が文字列になっていないか、範囲に小計行を含めていないか、項目名に表記ゆれがないかを確認しましょう。
用途に合う関数と機能を選べば、毎月の集計作業を短縮し、確認しやすいエクセル表を作れます。
