ピボットを使わずに関数だけで小計・総合計を計算する│Excel

2022年11月13日

Excelで複数の小計項目を持つデータシートの計算では、よくピボットテーブルが使われます。

一方、それほど複雑なデータシートではなく、また社内で共有しているファイルで、「使いやすさ」を重視したい場合には、ピボットテーブルを使わずに関数だけで小計・総合計を計算することもできます。

Excelの小計を含めた総合計を計算するときには、SUBTOTAL関数が便利です。

この記事では、ピボットを使わずに関数だけで小計・総合計を計算する方法を実例を交えて紹介します。

小計・総合計を計算するSUBTOTAL関数の使い方

この章では、ExcelのSUBTOTAL関数を使って列の小計を含む総合計を計算する方法を紹介します。

その前に小計を含む総合計を計算することについて説明します。

この文言だけでは、何を目的にした関数かイメージできない方もいらっしゃるかと思います。SUBTOTALの便利さと目的を明確にするために、まずは小計を含む総合計について理解しておきましょう。

下記のイメージ図で説明します。D列には売上金額が入力されており、それぞれ月ごとの小計（D6,D11,D17）も同列に含んでいます。

このデータ列ですべての月の総合計を計算したい場合、たとえばセルD18に通常のSUM関数を使ってしまうと、それぞれの小計も含んでしまい、二重計算になってしまうのです。

この記事で紹介するSUBTOTALは計算範囲に小計を含んでいても、二重計算にならずに正しい総合計を算出してくれる機能があるのです。

数式

=SUBTOTAL(集計方法,計算範囲)
例：=SUBTOTAL(9,D2:D5)

前節で説明したように、総合計を求める際に小計を除いて計算させるためには、「これは小計です」ということが分からなければなりません。そのため、小計を計算するセルでも通常のSUMではなく、SUBTOTAL関数を使う必要があるのです。

なお、数式内の「9」については集計方法を表す引数であり、合計計算の場合は「9」となります。他にも平均値を計算する場合の引数などもありますが、詳しくは後述します。

小計にSUBTOTAL関数を使う場合には、SUM関数とほとんど変わりません。集計方法に「9」を指定すること以外は同じ記述方法になります。つまり、計算の範囲は人が判断して記述する必要があります。

くり返しますが、小計にSUBTOTALを使うことで総合計を計算する際に、小計を除くことができます。

数式

=SUBTOTAL(集計方法,計算範囲)
例：=SUBTOTAL(9,D2:D17)

総合計を求めるSUBTOTAL関数では、わざわざ小計のセルを避ける必要はありません。小計を含めて、データ列の頭から末尾までを計算範囲として指定することができます。小計が二重計算されることはありません。

以上がSUBTOTAL関数を使った総合計の計算方法です。この記事で紹介したSUBTOTALの使い方は、列内に小計セルを含んでいても二重計算されないことがメリットであり、小計をSUBTOTALで計算していない場合や、そもそも小計セルがない場合には、SUBTOTALを使う必要はありません。

前節までに、SUBTOTALを使った小計や総合計の計算方法を説明しました。SUBTOTAL関数では、計算範囲の前に集計方法を表す引数が必要なのですが、その引数についてこの節で説明します。

引数とは・・Excelの関数で使うカッコの中の記述文のことです。小数点を切り上げるROUNDUP関数を例にすると=ROUNDUP(A1,0)で、セルA1の値を小数点第一位で切り上げしますが、カッコ内の（範囲,桁数）がそれぞれ引数です。※この例では引数は2つ

まずは、当記事で紹介したSUBTOTALを使った数式を引用します。

総合計を求めた数式
=SUBTOTAL(9,D2:D17)

カッコの直後に書かれた「9」という数字は『合計』を指示するための引数です。SUBTOTAL関数では引数を指定することで、次のような計算を行うことができます。

7,8,10,11は割愛