今回は、Excel(エクセル)のSUBTOTAL(サブトータル)関数を使って、小計や平均値を算出してみます。SUM関数やAVERAGE関数を組み合わせて使うと、正しい合計値が出ない場合もありますが、SUBTOTAL関数を使えば問題が解決します。

本連載では、「よく使うけど忘れやすいMicrosoft Officeの操作」をキーワードに、Officeアプリケーションの使い方を解説していきます。記事の制作には、執筆時点で最新の状態にアップデートしたMicrosoft Office 2016を使用しています

小計や平均の入った表ではSUBTOTAL関数を活用

Excelには、合計値を求めるSUM関数をはじめ、平均値を求めるAVERAGE関数や最大値や最小値を求めるMAX関数/MIN関数など、複数のセルの値を計算するための関数がたくさん用意されています。ただし厄介なことに、これらの関数を使っても、合計値を正しく求められない場合があります。

小計→合計ならばSUM関数だけでOK

たとえば、SUM関数を使って小計値を表に入れたとします(図1)(図2)。この場合、複数の小計も含めてセルを範囲指定し、SUM関数で合計を求めてみると(図3)、小計のセルだけを合計した値が計算されます(図4)。今回の例では小計を求めたC6、C12、C18セルの値が合計されており、正しい値を得ることができています。

  • (図1)C1～C5セルを選択して「ホーム」タブの「合計」アイコンをクリックします

  • (図2)同じ方法で、C6、C12、C18セルに小計を表示させます

  • (図3)C1～C18セルを選択して「合計」アイコンをクリックします

  • (図4)選択範囲内にあるSUM関数を使った小計セル(C6、C12、C18)を合計する数式「=SUM(C18,C12,C6)」が入力され、正しい合計値が表示されました

AVERAGE関数と組み合わせると問題発生

ところが、AVERAGE関数を使って月ごとの平均値を入力した表においては(図5)、結果に難が生じます。先ほどと同じように、AVERAGE関数で求めた平均値も含めてセルを範囲指定し、SUM関数で合計を求めてみると(図6)、平均値だけでなく個別の数値も含めて合計されてしまい、正しい値になりませんでした(図7）。

  • (図5)AVERAGE関数を使ってC6、C12、C18セルに、各支店の平均値を表示させます

  • (図6)C1～C18セルを選択して「合計」アイコンをクリックします

  • (図7)平均値を算出したC6、C12、C18セルの数値も合計されてしまい、正しい合計値になりません

このように、SUM関数やAVERAGE関数を使って小計や平均値の入った表を作成する際には、合計値の計算などに注意する必要があります。そこでオススメなのが、1つの関数で合計、平均など複数の集計方法が使えるSUBTOTAL関数です。

SUBTOTAL関数を活用するのが正解

平均を求める場合は、=SUBTOTAL(1,参照セル範囲)

それでは、SUBTOTAL関数を使って、平均値を表示している表の合計値を求めてみましょう。まずは、平均値を表示させたいセル(画面の例ではC6)を選択して、数式バーに「=SUBTOTAL(1,」と入力します(図8)。数式の「1」の部分で集計方法を指定しており、「1」は平均(AVERAGE)となっています。

続いて、平均を求めたいセル(画面の例ではC1～C5)を範囲選択して数式に適用し、カッコで閉じて「=SUBTOTAL(1,C1:C5)」という数式を完成させます(図9)。同じように、C12セルにC7～C11セルの平均、C18セルにC13～C17セルの平均を表示させましょう。

  • (図8)C6セルの数式バーに「=SUBTOTAL(1,」と入力します

  • (図9)C1~C5セルを選択して「=SUBTOTAL(1,C1:C5)」という数式を完成させます

  • (図10)同様に、C12セルに「=SUBTOTAL(1,C7:C11)」、C18セルに「=SUBTOTAL(1,C13:C17)」の数式を作成して平均値を表示。C19セルを選択して数式バーに「=SUBTOTAL(9,」と入力します

合計を求める場合は、=SUBTOTAL(9,参照セル範囲)

次は、SUBTOTAL関数を使って合計値を算出してみます。合計値を表示させたいセル(画面の例ではC19)を選択して、数式バーに「=SUBTOTAL(9,」と入力します(図10)。「9」で指定する集計方法は合計(SUM)になります。続けて合計したいセル範囲(C1～C18)までをドラッグして数式に反映させ、カッコで閉じて「=SUBTOTAL(9,C1:C18)」という数式を完成させます(図11)。

[Enter]キーを押して確定させると、SUBTOTAL関数を使ったセル(C6、C12、C18)の数値を省いた正しい合計値が表示されます(図12)。このように、SUBTOTAL関数で統一すれば、小計(SUM)や平均値(AVERAGE)、最大値(MAX)、最小値(MIN)を入れた表でも正しい合計値を簡単に算出できます。

  • (図12)SUBTOTAL関数が使われているC6、C12、C18セルの数値を省いた正しい合計値が表示されます

  • (図11)C1～C18セルをドラッグして選択し「=SUBTOTAL(9,C1:C18)」の数式を完成させます

ちなみに、AVERAGE関数を使って平均値を求めている場合は、SUBTOTAL関数で合計値を求めてもセルが除外されず、正しい合計値が表示されません(図13)。平均や小計を求めるセルはすべてSUBTOTAL関数を使って数式を作成するようにしましょう。

  • (図13)AVERAGE関数を使って表示したセルの数値はSUBTOTAL関数を使っても除外されません