ExcelのSUBTOTAL関数（小計）の使い方｜非表示の行やフィルターデータにも対応

Excelのワークシートに非表示の行、フィルターで絞り込んだデータ、あるいはグループ化したデータが含まれているときに活躍するのが、SUBTOTAL関数（小計関数）です。この関数を使えば、計算の際に非表示の値を含めるかどうかを自由にコントロールできます。さらに、データグループの合計を求めるだけでなく、平均値・最大値・最小値・標準偏差・分散なども一度に算出可能です。この記事では、Excelで小計を挿入する具体的な方法をわかりやすく解説します。

※この記事の手順は、Microsoft 365版Excel、Excel 2019、およびExcel 2016に対応しています。

SUBTOTAL関数の構文

SUBTOTAL関数は、ワークシート内の値をさまざまな方法で集計するための関数です。特に、通常なら計算から外れてしまう非表示の行を、あえて計算に含めたい場合に非常に便利です。

SUBTOTAL関数の基本構文は以下のとおりです。

=SUBTOTAL(集計方法, 範囲1, [範囲2], …)

引数①：function_num（集計方法）

function_num（集計方法）は必須の引数で、小計の計算に使う演算の種類を指定します。数値の合計はもちろん、選択範囲の平均値の算出、最大値・最小値の取得、セルの個数のカウントなど、多彩な集計が可能です。

なお、SUBTOTAL関数は空欄のセルや数値以外の値が入ったセルは自動的に無視されるため、安心して範囲指定できます。

この引数には数値を指定しますが、その値によって「非表示の行を結果に含めるか」「除外するか」が決まります。ここでいう非表示の行には、手動で非表示にした行と、フィルターで隠れた行の両方が該当します。

function_num（集計方法）の一覧

注意点として、function_numに1〜11を指定した場合、非表示の行の値が計算に含まれるのは「行を右クリックして非表示にした」場合のみです。フィルター機能で非表示になった行については、1〜11を指定しても計算から除外されます。

引数②：ref1／ref2以降（集計する範囲）

ref1は必須の引数で、function_numで指定した集計の対象となるセルを指定します。値・単一のセル・セル範囲のいずれでも指定可能です。

ref2以降は省略可能な引数で、集計に追加したいセルがある場合に指定します。

非表示の行がある場合のSUBTOTAL関数の使い方

Excelの関数は、手動で入力する方法と「関数の引数」ダイアログボックスを使う方法の2通りがあります。ここでは、数式バーから手動で入力する手順を、COUNT（個数カウント）の例で紹介します。表示中の行だけをカウントするパターンと、非表示の行も含めてカウントするパターンの両方を見ていきましょう。

1. 複数行のデータが入ったワークシートを用意します。
2. 表示中の行のカウント結果を表示させたいセルを選択します。
3. 数式バーに=SUBTOTALと入力します。入力途中でExcelが関数名を候補表示するので、SUBTOTALをダブルクリックします。
   ※「数式」タブ→「数学/三角」→「SUBTOTAL」から、関数の引数ダイアログを使って入力することもできます。
4. 表示されたドロップダウンメニューから「102 – COUNT」（非表示行を除外）をダブルクリックします。
5. カンマ（,）を入力します。
6. ワークシート上で、数式に含めたいセル範囲を選択します。
7. Enterキーを押すと、手順2で選んだセルに結果が表示されます。
8. 次に、非表示の行も含めたカウント結果を表示させたいセルを選択します。
9. 再び数式バーに=SUBTOTALと入力し、候補からSUBTOTALをダブルクリックします。
10. ドロップダウンメニューから「2 – COUNT」（非表示行を含む）をダブルクリックし、カンマ（,）を入力します。
11. 同じくセル範囲を選択してEnterキーを押します。
12. 最後に、データの一部の行を非表示にしてみましょう。この例では、売上が100,000ドル未満の行のみを非表示にしています。2つの数式の結果の違いを確認してください。

フィルターしたデータでのSUBTOTAL関数の使い方

フィルター済みのデータにSUBTOTAL関数を使うと、フィルターで除外された行のデータは自動的に無視されます。そしてフィルター条件を変更するたびに再計算が行われ、常に表示中の行だけの小計が反映されるのが大きな特徴です。

実際にフィルターを操作しながら、計算結果がどう変わるのか確認してみましょう。

1. SUBTOTALの数式を作成します。たとえば、フィルター後のデータの合計（SUM）と平均（AVERAGE）を求める数式を用意しておきます。
   ※このケースでは、非表示行を含む番号（1〜11）と除外する番号（101〜111）のどちらを指定しても、同じ結果になります。
2. データセット内の任意のセルを選択します。
3. 「ホーム」タブ→「並べ替えとフィルター」→「フィルター」を選択します。
4. 見出しに表示されたドロップダウン矢印を使って、データを絞り込みます。
5. フィルター条件を変更するたびに、SUBTOTALの結果が自動的に更新されることを確認しましょう。

グループ化されたデータでのSUBTOTAL関数の使い方

データをグループごとに管理している場合は、各グループに個別の小計を付けて、さらにデータセット全体の総合計を一括で挿入することもできます。これには「小計」機能を利用します。

1. データセット内の任意のセルを選択します。
2. 「データ」タブ→「小計」を選択し、「小計」ダイアログボックスを開きます。
3. 「グループの基準」のドロップダウン矢印から、小計を区切るグループ項目を選びます。
4. 「集計方法」のドロップダウン矢印から、function_num（合計・平均など）を選択します。
5. 「小計の対象」リストで、数式を適用したい列にチェックを入れます。
6. 「OK」を選択します。
7. 各データグループに小計行が挿入され、データセットの末尾には総合計が自動的に追加されます。
8. 後から集計方法を変更したい場合は、データセット内の任意のセルを選択して再度「データ」→「小計」を開き、設定し直せばOKです。