逆引き Excel 関数辞典。②

〇 SUBTOTAL関数：非表示のレコードを合計の対象から除外したい。

Q：非表示のレコードを合計の対象から除外したいのですが、SUM関数ではうまくいきません。いい方法はありませんか？

A：あります。SUBTOTAL関数を用いて引数「集計方法」を「109」に設定します。

---

SUBTOTAL関数では、11種類の集計方法から、目的のものを「1」から「11」までの番号で指定できるようになっています。合計の場合は「9」です。

ただし、この番号を「101」から「111」のように100台の3桁にすると、非表示にした値を除外して集計します。つまり「集計方法」に「109」を指定すれば、質問にあるように、非表示のレコードを合計の対象から除外できるわけです。

売上集計表に小計欄を作りたい、SUMの代わりに使うべき関数は、店舗の売り上げを計算しています。集計表の途中に小計欄を作りたいのですが、どうすればいいですか？

 

SUBTOTAL関数　「数学／三角」ボタン

＝SUBTOTAL（集計方法, 範囲1,［範囲2］,...）
11種類の集計方法から指定した方法で、指定した範囲を計算する。
集計方法　11種類の集計方法から、目的のものを番号で指定する。集計方法については下表を参照。

範囲1　集計対象の範囲を指定する。［範囲2］以下は省略可。

画1、SUBTOTAL関数で指定する集計方法の一覧。

店舗の売上を示した表を用意しました。B8に「東店」、C8に「西店」の売上合計、D8に両店を併せた総合計を算出します。まず、B8を選んで、

=SUBTOTAL(109,B2:B7)

と入力し、［Enter］キーを押します。

画2、B8に「=SUBTOTAL(109,B2:B7)」と入力して［Enter］キーを押した。東店の売上合計が返った。

「集計方法」が「109」になっている点に注目してください。では、このB8の数式をD8までオートフィルします。

画3、B8の数式をD8までオートフィルする。「西店」の売上合計と両店の総合計を計算できた。

続いて2行目から4行目を選択し、右クリックで「非表示」を選びます。

画4、2行目から4行目を選択し、右クリックで「非表示」を選ぶ。

1月から3月の売上が非表示になるとともに、合計が非表示にしたデータを除外したものに置き換わりました。

画5、1月から3月の売上が非表示になり、合計は非表示にしたデータを除外したものになった。

参考までに、下図ではB8の数式を

=SUBTOTAL(9,B2:B7)

にして、D8までオートフィルしたものです。「集計方法」を「9」にしたため、非表示のデータも含めて合計が計算されているのが分かります。SUM関数を使った場合もこれと同じ結果になります。

画6、B8の数式を「=SUBTOTAL(9,B2:B7)」にし、D8までオートフィルした。今度は非表示のデータも含めて計算されている。