【Excel関数】SUBTOTALの集計方法と引数を解説
Excelで便利な合計や集計の計算ですが、SUM以外にもSUBTOTALという便利な関数があります。
しかしSUMに比べると知名度が低く、集計にも効率的に時間がかかる方法をしている方が多いです。
そこでこの記事ではSUBTOTAL関数を使った合計やと引数とSUMの違いを解説します。
仕事で使用するデータ管理や数値の計算など様々な業務効率化に繋がりますので、この記事を読んで是非参考にしていきましょう。
目次
SUBTOTAL関数とは?
SUBTOTALは引数を指定することで、合計や最大値、平均などを使い分けすることができる関数です。
通常であればフィルター機能で除外した部分も一緒に集計されてしまいますが、SUBTOTALを使用すればSUBTOTALで表示されている部分のみの合計や平均を出すことができます。
また、SUBTOTALの引数を指定することで非表示にした集計も反映もしくは除外など応用することができます。
SUBTOTAL関数の使い方
SUBTOTAL関数を使用するときは以下の引数を記述します。
| 集計方法 | 集計内容に対応する番号を指定。1～11の定数の場合は行を非表示にした部分も含めて集計。101～111の定数の場合は非表示のデータは除外されて集計される |
| 参照 | 集計対象のセル範囲を指定 |
引数については以下の記事でも解説しています。
SUBTOTALで使用できる引数一覧
集計方法に入力する引数は以下の表を参考にしましょう。
| 集計方法 | 集計内容 | 似た関数 |
| 1または101 | 平均 | AVERAGE |
| 2または202 | 数値の個数 | COUNT |
| 3または303 | 数値以外も含めた個数 | COUNTA |
| 4または404 | 最大値 | MAX |
| 5または505 | 最小値 | MIN |
| 6または606 | 積 | PRODUCT |
| 7または707 | 不偏標準偏差値 | STDEV |
| 8または808 | 標本標準偏差値 | STDEVP |
| 9または909 | 合計 | SUM |
| 10または110 | 不偏分散 | VAR |
| 11または111 | 標準分散 | VARP |
多くの引数がありますが、実際に一番よく使うのは合計、次に使うとしても個数か平均ぐらいでしょう。
集計元のフィルターも合計する方法
ここからは画像を元に実際に解説します。
まずは集計を出したいセルを選択し、fxと書いているアイコン「関数の挿入」をクリックします。
次に、「関数の挿入」に「集計」と入力し、Enterを押します。SUBTOTALを選択し、OKをクリックしましょう。
続いて、集計方法を9にし、参照1は集計したいセルを入力します。
セルを非表示にして確認してみましょう。今回は7行目と8行目を非表示にしましたが、非表示にしても合計は変わらず9になります。
ちなみにフィルター機能で見えなくした場合は、現在表示されている部分のみの集計となるため合計が変更になります。
集計元のフィルターは除外して合計する方法
SUBTOTAL関数を使用する手順はほぼ同じです。
違いとしては引数となる「集計方法」を109にします。
非表示して見てみると分かりやすいでしょう。
関数としては以下の入力となりました。
SUBTOTALで平均値を出す方法
SUBTOTALで平均を出すときは、引数で集計方法のところを1もしくは101にすることで出すことができます。
SUBTOTALとSUMの違い
SUBTOTALとSUMの大きな違いは、フィルター機能を使用した時にフィルター部分を合計するかしないかです。
下の画像を見ても分かるように、SUMの場合はフィルターで見えなくなっているものも合計しているのに対し、SUBTOTALは除外して見えている部分のみ合計しています。
集計機能での違い
また、集計するときにもSUBTOTALとSUMとで違いがでます。
下の図で見ても分かるように、SUMを使用した場合は一番下の合計を出すときに、それぞれの小計と各数値を全て合計してしまいます。
一方、SUBTOTALを使用することで計算式も簡単で、なおかつ小計の数値は除外して合計を出すことができます。
関数を入力するときの注意点
SUBTOTAL以外の関数でも共通することですが、何か関数や計算式を入れて表示する場合、データを入れた表に対して上に入れることをおすすめします。なぜなら、右に入力した場合は、元データの列が増えた場合に邪魔になります。また、表の下に入れると、データが増えたときに関数が邪魔になってしまうからです。
ひこ関数や計算式を入れるときは、データが今後増えるなどメンテナンスを考えて作るのがコツ
SUBTOTAL関数のまとめ
今回はSUBTOTAL関数を使った合計やと引数とSUMの違いを解説しました。
合計はSUMで出すことが多いと思いますが、フィルターを使用した時や小計を使った合計を出すときにSUBTOTALはとても便利です。
Excelの機能を使いこなすと、仕事の効率化から大きな時間短縮に繋がります。
仕事術のスキルが増えるとプライベートの時間を時間を増やすことができますので、当サイトで得た知識を是非活用していきましょう。
以下のExcel記事もおすすめです。
