今回は、Excelの小計機能を使って、グループごとの集計行を自動で挿入する方法と、使いこなすためのコツを紹介します。
売上の一覧を担当者別や地域別にまとめたいとき、グループの切れ目に行を挿入してSUM関数を1つずつ入力していくのは手間がかかります。データが増えたり並び順が変わったりすると、数式の範囲を直す作業も発生します。［小計］コマンドを使うと、グループの区切りを自動で判断して集計行と総計行をまとめて追加でき、さらにアウトラインで詳細を折りたたんで全体像を確認できます。
対象環境
この記事は、Windows版のExcel（Microsoft 365）を前提に説明します。基本的な操作はExcel 2021などの買い切り版でも共通ですが、リボンの表示はバージョンや画面の幅によって少し異なることがあります。
小計機能でできること
［小計］コマンドは、指定した列の値が切り替わるたびに集計行を挿入する機能です。主な特徴は次のとおりです。
- 集計にはSUBTOTAL関数が使われ、合計・平均・個数・最大・最小などから計算方法を選べます。
- グループごとの集計行に加えて、総計の行も自動で追加されます。
- 総計は小計の値を集めたものではなく、元の詳細データから計算されます。たとえば平均を選んだ場合、総計行には小計の平均ではなく、すべての明細行の平均が表示されます。
- 自動計算が有効なブックでは、明細データを修正すると小計と総計も再計算されます。
- 集計と同時にアウトラインが作成され、明細の表示・非表示を切り替えられます。
小計を使う前の準備
小計をうまく使えるかどうかは、事前の準備で決まります。次の点を確認してから操作を始めましょう。
データの形を整える
- 各列の1行目に見出し（ラベル）が入っていること
- 1つの列には同じ種類のデータが入っていること
- 範囲の途中に空白の行や列がないこと
空白行があると、Excelが範囲を途中で区切ってしまい、意図した範囲に小計がかからない原因になります。
グループにしたい列で並べ替える
小計は「値が変わるたびに」集計行を入れる仕組みです。そのため、同じ担当者や地域のデータがばらばらに並んでいると、同じグループが何度も分かれて集計されてしまいます。集計の基準にする列で先に並べ替えておくことが重要です。［データ］タブの［並べ替えとフィルター］グループにある［昇順］または［降順］で並べ替えられます。
テーブルになっている場合は範囲に変換する
Excelのテーブルでは小計を使えません。テーブル内のセルを選んでいると、［小計］ボタンが灰色で選択できない状態になります。小計を使いたい場合は、テーブルを選択して［テーブルデザイン］タブの［範囲に変換］で通常のセル範囲に戻してから操作します。範囲に変換すると、書式以外のテーブルの機能は使えなくなる点に注意してください。テーブルのまま集計したい場合は、ピボットテーブルを使う方法もあります。
グループごとの集計を挿入する手順
ここでは、「担当者」「商品」「売上金額」の列がある売上一覧を例に、担当者ごとの売上合計を求める流れを説明します。
- 「担当者」列のセルを選び、［データ］タブの［昇順］で並べ替えます。
- データ範囲内の任意のセルを選択します。
- ［データ］タブの［アウトライン］グループにある［小計］をクリックします。
- 表示されたダイアログボックスの［グループの基準］で「担当者」を選びます。
- ［集計の方法］で「合計」を選びます。
- ［集計するフィールド］で「売上金額」にチェックを入れます。集計しない列のチェックは外しておきます。
- ［OK］をクリックします。
担当者が切り替わるたびに集計行が挿入され、最後に総計の行が追加されます。
ダイアログボックスのオプションを使い分ける
- ［グループごとに改ページを挿入する］：グループごとに印刷ページを分けたいときにオンにします。担当者ごとに資料を配りたい場合に便利です。
- ［集計行をデータの下に挿入する］：オンにすると明細の下に集計行が入ります。オフにすると、集計行が明細の上に表示されます。見出しのように先に合計を見せたい場合はオフにします。
- ［現在の小計をすべて置き換える］：オンのまま実行すると、すでにある小計が新しい設定に置き換わります。小計を追加で重ねたいときはオフにします。
アウトラインで集計結果を見やすくする
小計を挿入すると、行番号の左側にアウトライン記号が表示されます。
- 上部の番号のボタンをクリックすると、表示するレベルを切り替えられます。たとえば「2」を選ぶと、明細が折りたたまれてグループごとの集計行と総計だけが表示されます。
- 各グループの横にある＋と－のボタンで、特定のグループだけ明細を開いたり閉じたりできます。
会議でグループ別の合計だけを示したいときは、レベル2に切り替えた状態で画面共有や印刷をすると、要点がまとまった資料になります。
応用テクニック
合計と平均など複数の集計を並べる
1つのグループに対して、合計と平均の両方を表示したい場合があります。その場合は、まず合計で小計を挿入し、続けてもう一度［小計］を開いて集計の方法を「平均」に変更します。このとき［現在の小計をすべて置き換える］のチェックを外してから［OK］を押すと、既存の合計を残したまま平均の行が追加されます。
入れ子の小計で階層的に集計する
「地域ごと」の集計の中に「担当者ごと」の集計を入れるといった、入れ子の小計も作れます。
- 外側のグループ（地域）を優先し、内側のグループ（担当者）を次の優先順位にして並べ替えます。複数列で並べ替える場合は、［データ］タブの［並べ替え］ダイアログで優先順位を指定すると確実です。
- ［小計］を開き、グループの基準を「地域」にして集計を挿入します。
- もう一度［小計］を開き、グループの基準を「担当者」に変更します。
- ［現在の小計をすべて置き換える］のチェックを外して［OK］をクリックします。
外側の集計から順に作ることがポイントです。順序が逆になると、意図しない位置に集計行が入ることがあります。
集計結果だけを別の場所に貼り付けたいとき
アウトラインで集計行だけを表示した状態でコピーすると、非表示の明細行まで貼り付けられることがあります。集計行だけを取り出したい場合は、範囲を選択してから［ホーム］タブの［検索と選択］→［条件を選択してジャンプ］で［可視セル］を選んでからコピーすると、表示されている行だけを貼り付けられます。キーボードではAlt＋;（セミコロン）でも可視セルを選択できます。
小計を削除して元に戻す
集計行が不要になったときは、手作業で行を削除する必要はありません。
- 小計が含まれる範囲内のセルを選択します。
- ［データ］タブの［小計］をクリックします。
- ダイアログボックスの［すべて削除］をクリックします。
集計行とアウトラインがまとめて取り除かれ、元の一覧に戻ります。
注意したいポイント
- 小計を含むデータにフィルターをかけると、集計行が非表示になることがあります。表示されない場合は、フィルターをすべてクリアして確認してください。
- 明細の追加や並べ替えをした後は、いったん［すべて削除］してから小計を挿入し直すと、集計の区切りがずれにくくなります。
- 集計行に入る数式はSUBTOTAL関数のため、別の場所でさらに範囲全体をSUBTOTAL関数で集計しても、小計の値は二重に数えられません。
まとめ
Excelの小計機能を使うと、関数を手入力しなくてもグループごとの集計と総計をまとめて作成できます。
- 集計の基準にする列で先に並べ替える
- テーブルの場合は範囲に変換してから使う
- ［グループの基準］［集計の方法］［集計するフィールド］の3つを指定する
- アウトライン記号で集計行だけの表示に切り替える
- 複数の集計や入れ子の小計は、［現在の小計をすべて置き換える］をオフにして追加する
担当者別や月別の集計を手早く確認したいときに、小計は手軽で頼りになる機能です。定期的に作る集計表から取り入れてみてください。