Excelの小計(サブトータル)をマスター:正確なデータ集計に役立つ実証済み7つのテクニック

はじめに

大量のデータを扱う際、「グループごとの合計」を素早く把握したい場面は多いものです。Excelには、そんなニーズに応える小計(Subtotal)機能が標準搭載されており、数回のクリックだけで集計作業を自動化できます。

本記事では、基本的な自動挿入からSUBTOTAL関数、VBA、ピボットテーブル、Power Queryまで、実務ですぐ使える7つの小計テクニックを順番に解説します。さらに、小計の削除方法やよくある質問もあわせてご紹介しますので、ぜひ最後までお読みください。

方法1:Excelで小計を自動挿入する

まずは、Excelの組み込み機能(小計ショートカット)を使って、小計を自動的に挿入する基本の手順を見ていきましょう。

ステップ1:基準となる列でデータを並べ替える

• 表内の任意のセルを選択し、リボンの[データ]タブをクリックして[並べ替え]を選択します。

• [並べ替え]ダイアログボックスが表示されます。
• [最優先されるキー]で、小計を挿入したい基準の列(営業担当者など)を選択します。
• [並べ替えキー]は「セルの値」のままでOKです。
• 順序は昇順(A→Z)を選択します。
• [OK]をクリックします。

これで「営業担当者」列がアルファベット順に並べ替えられました。小計を挿入する前に必ず並べ替えを行うのがポイントです。並べ替えていないと、同じ担当者のデータが分散し、正しく集計されません。

ステップ2:小計機能を実行する

• 範囲内の任意のセルをクリックし、[データ]タブの[アウトライン]グループにある[小計]を選択します。

• [小計]ダイアログボックスが表示されます。ここではSUM関数を使って合計を求めます。
• [グループ化の基準]には、小計を挿入する単位となる列(営業担当者)を指定します。
• [集計方法]に「SUM」を選択して合計値を計算します。
• [集計する項目]で、合計したい列(数量・金額など)にチェックを入れます。
• [OK]をクリックします。

これで各担当者ごとの小計と総計(グランドトータル)が自動的に挿入されました。

• シート左上にある「1・2・3」のアウトライン記号をクリックすると、グループの表示・非表示を切り替えられます。「2」を選ぶと小計だけの一覧表示になり、「3」を選ぶと詳細データまで展開されます。

方法2:複数の小計を追加する

2.1. 異なる列に複数の小計を挿入する

前のセクションでは「営業担当者」列に小計を適用しました。ここでは、さらに「カテゴリー」を基準とした小計を追加してみましょう。

• [データ]タブから[並べ替え]ダイアログを開きます。
• 1つ目のキーで「営業担当者」を指定します。
• [レベルの追加]をクリックし、2つ目のキーで「カテゴリー」を選択します。
• [OK]をクリックします。

• 方法1と同じ手順で[小計]ダイアログを開きます。
• [グループ化の基準]で「営業担当者」を選択し、最初の小計を挿入します。

• 続けて再度[小計]ダイアログを開きます。
• 今度は[グループ化の基準]で「カテゴリー」を選択します。
• [現在の小計と置き換える]のチェックを必ず外してください。チェックが入ったままだと、先ほどの小計が消去され、1種類の小計だけになってしまいます。
• [OK]をクリックします。

これで、担当者×カテゴリーという多層的なグループ化と小計が完成しました。

さらに別の基準で小計を追加したい場合は、同じ操作を繰り返すだけです。

2.2. 同じ列に複数の小計(平均・標準偏差)を表示する

小計は合計(SUM)だけでなく、平均(AVERAGE)や標準偏差(StdDev)など、複数の統計量を同じ列に重ねて表示することもできます。

方法1で作成したSUMの小計がある状態から始めます。

• [小計]ダイアログをさらに2回開きます。
• 1回目はAVERAGE関数、2回目はStdDev関数を選択します。どちらの場合も「現在の小計と置き換える」のチェックは外しておきます。

• AVERAGE関数は選択した列の平均値を算出します。
• StdDev関数は標準偏差を計算します。

これらの関数に基づいて、合計・平均・標準偏差の小計がデータに挿入されます。

方法3:Excelテーブルに小計を挿入する

残念ながら、Excelテーブル(テーブルとして書式設定された範囲)には、小計機能を直接適用できません。[データ]タブの[小計]ボタンがグレーアウトして使えないのです。

テーブルに小計を挿入したい場合は、一度範囲に変換する必要があります。

• テーブル内の任意のセルを選択し、[テーブルデザイン]タブの[ツール]グループにある[範囲に変換]をクリックします。

• 「テーブルを範囲に変換しますか?」という確認メッセージが表示されるので、[はい]をクリックします。

これでテーブルが通常の範囲に戻るため、小計機能が使えるようになり、問題なく小計を挿入できます。

方法4:フィルターとSUBTOTAL関数を組み合わせる

これまで使ってきたのは「小計機能」でしたが、ExcelにはSUBTOTAL関数も用意されています。この関数の大きな特徴は、フィルターで抽出した行だけを対象に集計できることです。

SUBTOTAL関数の書式

=SUBTOTAL(function_num, ref1, [ref2], …)

第1引数の function_num には、実行する集計方法を示す番号(1〜9 または 101〜109)を指定します。

4.1. フィルター抽出時の小計

次のようなSUBTOTAL数式を使用して小計を挿入します。

第1引数に「9」を指定しているため、フィルターで除外されたセルは集計から除外されます。

• 「営業担当者」列のフィルター矢印をクリックし、特定の名前(例：Adam)を選択して絞り込みます。

すると、フィルターで表示されているセルのみが集計対象となり、それ以外の行は無視されます。これは通常のSUM関数にはできない、SUBTOTAL関数ならではの強力な機能です。

下の画像は、引数9と109それぞれのSUBTOTAL関数の出力を比較したものです。

4.2. 手動で非表示にした行の扱い

次に、行を手動で非表示にした場合の挙動を確認しましょう。行番号を右クリックし、ショートカットメニューから[非表示]を選択すると行を隠せます。

引数9の場合は、手動で非表示にした行のセルも集計に含まれますが、引数109の場合はこれらの行が無視されます。用途に応じて使い分けましょう。

方法5:VBAコードで小計を求める

定型業務を自動化したい方には、VBAマクロで小計を挿入する方法がおすすめです。

• Alt + F11キーを押して、Visual Basic Editor(VBE)を起動します。
• メニューバーの[挿入]から[標準モジュール]を選択します。

• 開いたモジュールウィンドウに以下のVBAコードを貼り付けます。

コード:

Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
    If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
    Rows(iValue + 1).Insert
    Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
    For iColumn = 7 To 8 'Columns to Calculate Sum
    Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
    Next iColumn
    Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
    iValue = iValue + 2
    xValue = iValue
    Else
    iValue = iValue + 1
    End If
    Loop
Application.ScreenUpdating = True
End Sub

• [実行](F5キー)をクリックしてコードを実行します。

マクロが走ると、Excelが「営業担当者」ごとにグループ化しながら、自動的に小計行を挿入します。

方法6:ピボットテーブルに小計を追加する

ピボットテーブルでも小計を簡単に表示できます。ここでは、以下のフィールド構成で作成したピボットテーブルを例にします。

1. フィルター:営業担当者
2. 列:なし(空白)
3. 行:カテゴリー、製品
4. 値:数量、単価、合計金額、利益

• リボンの[デザイン]タブを開き、[レイアウト]グループにある[小計]のドロップダウンをクリックします。

• ドロップダウンメニューから[グループ内すべての小計をアイテムの下に表示する]を選択します。

これで、行ラベルに基づいて各グループの下部に小計が挿入され、テーブルの最下部には総計が表示されます。

方法7:Power Queryで小計を挿入する

最後に紹介するのは、Power Queryを使った高度なグループ集計の方法です。データのクレンジングから集計まで一括で自動化できるため、定期的なレポート作成に非常に便利です。

• 範囲内の任意のセルをクリックし、[データ]タブの[データの取得と変換]グループから[テーブルまたは範囲から]を選択します。

• [テーブルの作成]ダイアログで[OK]をクリックします。

• ExcelがPower Queryエディター画面に切り替わります。
• グループ化したい列(例：「営業担当者」と「カテゴリー」)をCtrlキーで複数選択し、[変換]タブの[グループ化]アイコンをクリックします。

• [グループ化]ダイアログで、新しい列名を入力します。
• 実行する操作の種類(例：SUM)を選択します。
• 計算対象の列(例：合計金額)を選択します。
• さらに集計を追加したい場合は、[集計の追加]をクリックします。

• たとえば「利益」の小計も求めたい場合は、[列]に「利益」、[操作]に「合計」を指定し、新しい列名(例：Profit)を付けます。
• [OK]をクリックします。

• 「営業担当者」と「カテゴリー」に基づいた小計が挿入されていることを確認できます。
• [ホーム]タブの[閉じて読み込む]のドロップダウンから、[閉じて読み込む]を選択します。

Power Queryエディターが、グループ化と小計を施したデータをExcelワークシートへ読み込みます。

Excelで小計を削除する方法

挿入した小計が不要になった場合は、以下の手順ですべて削除できます。

• [データ]タブから[小計]ダイアログボックスを開き、[すべて削除]をクリックします。

• グループと小計が取り除かれ、データが通常の範囲に戻ります。

注意： 小計を削除しても、並べ替えた状態はそのまま維持されます。元の並び順には戻らない点に留意してください。

よくある質問(FAQ)

Q1. Excelで小計が削除できないのはなぜですか?

いくつかの原因が考えられます。

i) シートが保護されている場合： パスワードで保護されたシートでは、先に保護を解除する必要があります。

ii) 共有ワークブックの場合： 複数ユーザーで共有しているブックでは、共有を解除するか、設定を確認してください。

iii) カスタム数式やマクロで追加された小計の場合： 数式やマクロによって挿入された小計は、該当する数式やマクロを修正・削除しないと除去できません。

Q2. Excelで小計を折りたたんだり展開したりするには?

グループ化のアウトライン機能を使います。小計を挿入すると、Excelが自動的にアウトライン記号を追加します。グループ行の横にあるマイナス(-)記号をクリックすれば折りたたみ、プラス(+)記号をクリックすれば展開できます。

Q3. データ変更時に小計を自動更新することはできますか?

はい、可能です。Excelテーブルや動的範囲名を利用すれば、データの変更に合わせて小計が自動的に更新されます。手動で更新したい場合は、小計の行を右クリックしてコンテキストメニューから「更新」を選択します。自動更新されない場合は、ブックの計算方法が「自動」になっているか確認してください。

Q4. AGGREGATE関数とSUBTOTAL関数は同じものですか?

いいえ、書式は似ていますが、両者には違いがあります。AGGREGATE関数はSUBTOTAL関数よりも多くの集計オプション(19種類の機能番号)やエラー値の無視設定などを備えており、より柔軟な集計が可能です。

記事のポイントまとめ

• Excelの「小計」機能を使えば、指定した列を基準にデータグループごとの小計を簡単に挿入できます。
• 小計を挿入するには、データ範囲を選択して[データ]タブ → [アウトライン]グループの[小計]ボタンをクリックします。
• グループ化の基準を複数指定することで、多階層の小計を追加できます(「現在の小計と置き換える」のチェック解除を忘れずに)。
• アウトラインの「+/-」記号で、小計グループの折りたたみ・展開が自由に行えます。
• Excelテーブルや動的範囲名を使えば、データ変更時に小計を自動更新できます。
• SUBTOTAL関数・VBA・ピボットテーブル・Power Queryなど、目的に応じた多彩な手法を選べます。

練習用ワークブックのダウンロード

この記事で紹介した手順は、練習用ワークブックを使って実際に試すことができます。こちらからダウンロードして、ぜひご自身の環境で再現してみてください。

<< Excelの小計に戻る | Excel学習トップへ

無料の高度なExcel演習問題(解答付き)もぜひご活用ください!