通常の計算式、関数式だと、フィルターでデータを抽出しても計算結果は変わらずに表示されます。
もちろん、通常の場合はそれでも良いのだけど、時々「フィルターで表示したデータだけで合計数を表示したいなぁ…」なんて思うことも、ありますよね。
今回はそんなことが可能な関数について、ご紹介しますね！
目次
SUBTOTAL関数を使用して合計値を算出
抽出されたデータだけの合計値を求める場合、SUBTOTAL関数を利用するのが便利です。
SUBTOTAL関数の関数式はこちら。
SUBTOTALで抽出したデータを合計する
実際に使ってみましょう。
果物、野菜、肉のカテゴリの販売数が、入り混じった表があります。
これをカテゴリごとに販売合計数を算出したい場合、どうすれば良いでしょうか。
SUBTOTAL関数を利用して計算した場合、オートフィルタ機能で抽出されたデータを自動的に合計してくれるため、大変便利です。
フィルターしてみるとこうなる
まずはデータをフィルターしてみます。
一番上の行で、データが入力されているセルを選択します。 この画像の場合はB1ですね。
データタブを選択し、フィルターボタンを押すすることで、抽出機能がオンになります。
SUBTOTALで計算
続いて、D10にSUBTOTAL関数を記述してみます。
=SUBTOTAL(9,D2:D9)
関数式に入力している「9」の数字は集計方法の指定した番号です。
「９」の場合は範囲内の合計値を算出する(つまり通常のSUM関数と同じ)という指定になります。
SUBTOTAL関数の計算方法の種類
この数値を別の数値に変えることで、計算方法を変えることも可能ですよ。
SUBTOTAL関数を使って、個数のカウントや平均値、最大値、最小値の計算などにも利用可能です。
なかなか便利なので、ぜひ使ってみてくださいな。
- 「1」の場合： 平均値を計算(AVERAGE)
- 「2」の場合： 個数をカウント(COUNT)
- 「3」の場合： 文字列を含めてカウント(COUNTA)
- 「4」の場合： 最大値を表示(MAX)
- 「5」の場合： 最小値を表示(MIN)
- 「6」の場合： 積(掛け算)を求める(PRODUCT)
- 「7」の場合： 不便標準偏差を求める(STDEV)
- 「8」の場合： 標本標準偏差を求める(STDEVP)
- 「9」の場合： 合計値を計算(SUM)
- 「10」の場合： 不偏分散を求める(VAR)
- 「11」の場合： 標本分散を求める(VARP)
フィルターされた数値の合計だけが表示される！
さて、さきほどの図に戻って。
D2:D9のセルが、販売数の数値が入力されているセルの範囲指定です。
SUBTOTAL関数で合計値を表示したら、次にデータをフィルター抽出してみます。
データを抽出すると、自動的に表示されているカテゴリのデータのみの合計数が算出され、表示されているはず！
同様に別カテゴリを選択しても、そのカテゴリの合計数が自動的に反映され、表示されます。
さらに複数カテゴリを選択した場合でも、フィルターで表示されているデータのみで計算された数字が算出されるので、非常に便利な関数です。
スポンサードリンク
まとめ
このように、SUBTOTAL関数は雑多なデータから必要な数値を算出できる便利な機能です。
最終的な計算結果を表示するための関数…というよりも、実際にデータの集まりをフィルターで抽出しながら、計算した値がどのように変化するかを見ながら操作することに適していると思います。
ということで、オートフィルタ機能とSUBTOTAL関数を組みあわせることによって、簡単に必要な数値を算出することができました。
上でも挙げた通り、SUBTOTAL関数は、他にも様々な集計パターンを指定することができます。
場面によって、使い分けてみてくださいな。
それでは！
