集計方法を指定し、様々な集計ができる多機能なSUBTOTAL関数。ここではSUBTOTAL関数の基本的な使い方と使用例について解説していきます。
SUBTOTAL関数について
SUBTOTAL関数とは集計方法と参照する範囲を指定し、指定した集計方法で結果を出力する関数となります。まず書式を見てみましょう。
SUBTOTAL関数の書式
- 集計方法：リストの集計方法を番号で指定します。対応表に関しては下部に記載します。
- 参照1〜254：集計するリストの範囲または参照を1～254個まで指定します。
SUBTOTAL関数の集計方法対応表
1つ目の設定で指定する集計方法の対応表です。番号を指定し集計方法を設定します。同じ目的でも設定できる数値は２種類あり、1~11が非表示のデータを含めたい場合に設定し、101~111が非表示のデータを含めない場合に指定します。
| 設定数値 （非表示のデータを含める場合） | 設定数値 （非表示のデータを含めない場合） | 同等の関数 | 説明 |
|---|---|---|---|
| 1 | 101 | AVERAGE | 平均値を出力 |
| 2 | 102 | COUNT | 数値の個数を出力 |
| 3 | 103 | COUNTA | データの個数を出力 |
| 4 | 104 | MAX | 最大値を出力 |
| 5 | 105 | MIN | 最小値を出力 |
| 6 | 106 | PRODUCT | 積を出力 |
| 7 | 107 | STDEV | 不偏標準偏差を出力 |
| 8 | 108 | STDEVP | 標本標準偏差を出力 |
| 9 | 109 | SUM | 合計値を出力 |
| 10 | 110 | VAR | 普遍分散を出力 |
| 11 | 111 | VARP | 標本分散を出力 |
SUBTOTAL関数の活用例１：非表示を含めない合計を計算
合計値を出す関数としてはSUM関数が有名ですが、SUBTOTAL関数でも同じように合計値を出すことができます。さらにSUBTOTAL関数では非表示データを含めない設定もできるので、こちらの機能を利用した合計を求め方を解説します。
GIFアニメーションでまずはざっくり手順確認
まずは下記GIFアニメーションをざっくりと確認し全体の流れを掴んでください。
手順1：空白セルを選択
総計を出力する空白セルを選択します。
手順2：「=SUBTOTAL(」と入力
「=SUBTOTAL(」と入力します。入力途中でサジェストが表示されるのでENTERキーを押下すると自動入力されます。
手順3：「109,」と入力
集計方法として「109」を設定します。この数値は「非表示のデータを含めない合計値を出力」する集計方法となります。SUM関数とほぼ動作は同じですが、この場合非表示のデータを含めないところがSUM関数との違いとなります。
| 設定数値 （非表示のデータを含める場合） | 設定数値 （非表示のデータを含めない場合） | 同等の関数 | 説明 |
|---|---|---|---|
| 9 | 109 | SUM | 合計値を出力 |
手順4：総計を出したい数値の範囲を指定
次に合計したい数値をドラッグして選択し、セル参照させます。
手順5：「)」で関数を閉じる
「)」で関数を閉じます。
手順6：ENTERキーを押下する
手順7：一部行を非表示にしてみる
一部の行を非表示にすると、表示になった分、合計値が減っているのを確認できるはずです。
SUBTOTAL関数の活用例２：小計を含めず合計を計算
SUBTOTAL関数では小計を自動で除外して合計を計算してくれます。活用例を見ながら、手順を解説します。
GIFアニメーションでまずはざっくり手順確認
まずは下記GIFアニメーションをざっくりと確認し全体の流れを掴んでください。
手順1：小計を入力する空白セルを選択
小計を入力するための空白セルを選択します。
手順2：「=SUBTOTAL(」と入力
「=SUBTOTAL(」と入力します。入力途中でサジェストが表示されるのでENTERキーを押下すると自動入力されます。
手順3：「9,」と入力
集計方法として「9」を設定します。この数値は「非表示のデータを含めた合計値を出力」する集計方法となります。SUM関数動作は同じですが、小計部分は自動的に除外した合計を出してくれるという点がSUM関数と異なります。 「,」の入力も忘れずに。
| 設定数値 （非表示のデータを含める場合） | 設定数値 （非表示のデータを含めない場合） | 同等の関数 | 説明 |
|---|---|---|---|
| 9 | 109 | SUM | 合計値を出力 |
手順4：小計を出したい数値の範囲を指定
小計を出したい数値をドラッグして選択し、セル参照させます。
手順5：「)」で関数を閉じる
「)」で関数を閉じます。他の小計箇所も同じようにSUBTOTAL関数を入れ込みます。
手順6：ENTERキーを押下する
手順7：総計を出したい空白セルを選択
小計の次は総計です。総計を出したい空白セルを選択します。
手順8：「=SUBTOTAL(」と入力
「=SUBTOTAL(」と入力します。入力途中でサジェストが表示されるのでENTERキーを押下すると自動入力されます。
手順9：「9,」と入力
小計と同じく、集計方法として「9」を設定します。この数値は「非表示のデータを含めた合計値を出力」する集計方法となります。SUM関数動作は同じですが、小計部分は自動的に除外した合計を出してくれるという点がSUM関数と異なります。
手順10：総計を出したい数値の範囲を小計含めて指定
小計も含め総計を出したい数値をドラッグして選択します。
手順11：「)」で関数を閉じる
手順12：ENTERキーを押下し完成
無事小計を除外し、自動で計算する表が完成しました。
おすすめの解説動画
SUBTOTAL関数についてのおすすめの解説動画です。小計を含めた表の総計を出す方法を紹介しています。SUM関数との違いもわかりやすく解説しています。
ショートカットキーの練習できます
当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。
※推奨ブラウザ Google Chrome
