Excelでフィルターをかけた数値の合計や平均を求める方法って？

▼関連記事
Excelのフィルター機能の便利な使い方【基礎編】

Excelの関連記事はこちら

SUBTOTAL関数について

SUBTOTAL関数は、定められた集計方法を指定した範囲内で用い、その集計結果を算出する関数です。
集計方法は数字で指定します。下の表であれば、「SUM（合計）」を求める場合には数字の「９」で指定します。
SUBTOTAL関数のよく利用される集計方法は、次の表のとおりです。

「=SUBTOTAL(集計方法指定,範囲,…)」という形で指定します。
フィルターをかける対象を表中で範囲指定しすると、フィルター結果が示され、さらにそれをもとに集計が行なわれます。これにより、フィルターの結果としての数値の合計や平均などを求めることが可能になります。

フィルターをかけた数値結果を合計する

フィルターをかけた数値結果を合計する手順を解説します。

（1）合計を表示したいセルを選択し、「関数の挿入」から「SUBTOTAL」を選択します。

（2）集計方法に「9（合計）を入力し、参照1に合計したいデータの範囲を選択してOKボタンをクリックします。

（3）合計値が表示されました。

（4）フィルターをかけることにより絞り込まれた数値のみが示され、さらにそれらの合計値が表示されます。

フィルターをかけた数値結果の平均を求める

フィルターをかけた数値結果の平均を求める手順を解説します。

（1）平均を表示したいセルを選択し、「関数の挿入」から「SUBTOTAL」を選択します。

（2）集計方法に「1（平均）を入力し、参照1に平均したいデータの範囲を選択してOKボタンをクリックします。

（3）平均値が表示されました。

（4）フィルターをかけると、絞り込まれた数値のみが示され、さらにそれらの平均値が表示されます。

フィルターをかけて絞られたセルの件数をカウントする

フィルターをかけて絞られたセルの件数をカウントする手順を解説します。

（1）カウント結果を表示したいセルを選択し、「関数の挿入」から「SUBTOTAL」を選択します。

（2）集計方法に「2（カウント）を入力し、参照1にカウントしたいデータの範囲を選択してOKボタンをクリックします。

（3）カウント結果が表示されました。

（4）フィルターをかけると、絞り込まれたセルの数が表示されます。

非表示でもフィルター機能は作動することに注意

単純に行を非表示にしても、フィルター機能の作動範囲が変化することはありません。

たとえば、行番号4～8の行を非表示にしてみましょう。

フィルター機能を使って絞り込み結果が表示された場合と同じように、一部の行が表示されなくなっています。しかし、SUBTOTAL関数の結果には、非表示は反映されません。

紛らわしいので、隠されている行を再表示し、フィルター機能を使ってデータを絞り込むようにしましょう。

ほかにも、図のようにフィルター範囲に小計行が含まれていると、フィルターをかけた後に、合計行が消えてしまうことがあります。

フィルタリングの条件によっては、小計行が表示されなくなってしまいます。

対処法としては、フィルターの範囲外で合計値を表示するようにしましょう。

まとめ

SUBTOTAL関数を利用することで、フィルターで絞り込んだセルの数値の合計や平均、件数を自動でカウントすることができます。合計や平均を計算で求めると非常に手間がかかりますので、SUBTOTAL関数をうまく使って、業務の効率化を図りましょう。

（学生の窓口編集部）