下図の「個数A」「個数B」には、表の [項目] 列の「データの個数」が、「合計A」
「合計B」には、表の [数量] 列の「合計」が、数式によって算出されていますが、
[項目] 列の「A」でフィルターを実行すると、個数も合計も A と B に違いがでます。
「個数A」と「合計A」に変化はありませんが、「個数B」や「合計B」は、いま見えている (フィルターで抽出されている) セルだけを対象とした計算結果に更新されています。
言い方をかえると、見えていないセルは計算から除外されている、ということです。
これは、データの個数と合計を求めるときに使用している関数に違いがあるからです。
「個数A」は COUNTA 関数を、「合計A」は SUM 関数を使用していますが、
「個数B」と「合計A」は SUBTOTAL 関数を使用しています。
ということで、カンの良い方は、「あー、SUBTOTAL を使えばフィルターの抽出結果だけを計算できるのね」ということで結論がでてしまいますね。
ここでは、この SUBTOTAL 関数のお話を。
Step 1 SUBTOTAL 関数の特徴
SUBTOTAL 関数は、リスト (一覧形式の表) の値を集計した結果を返す関数です。
構文は「=SUBTOTAL(集計方法,セル範囲)」で、指定した集計方法によって、特定したセル範囲のデータを集計してくれます。「この範囲の合計を求めてね」「この範囲のデータの個数を教えてね」というイメージでしょうか。
■ 集計方法を指定する
この関数にはいくつかの特徴がありますが、複数の集計方法を使い分けられる、というのがその 1 つです。
たとえば、普通は合計なら SUM、データの個数なら COUNTA というように集計方法によって関数そのものを使い分けるのですが、SUBTOTAL 関数は引数に集計方法を指定できるため、1 つの関数で集計方法を選択して利用できます。
下図のとおり、集計方法に対応した引数で指定する値が決められており、これを第 1 引数に指定します。
たとえば、合計を求めるのなら「9」、データの個数を求めるのなら「3」です。
ということで、こうなります↓。「個数B」は集計方法として「3」が、「合計B」は「9」が指定されています。
■ 見えているデータだけを対象とする（非表示を除外する）
もう 1 つ、オートフィルターによって抽出されたデータ (セル) だけを対象に計算をしてくれる、という特徴もあります。
オートフィルターは条件に一致しないデータを非表示にします。
SUBTOTAL 関数では、オートフィルターによって非表示になっているデータ (セル) を計算から除外してくれるので、結果的に見えているデータだけを対象にした計算結果を得ることができます。
ということで、下図のようになります。
数式で指定したセル範囲の中で、オートフィルターによって非表示になったセル (項目が B と C のデータ) は計算に含まれない＝見えているデータ (項目が A のデータ) だけを対象とした結果が表示されています。
Step 2 SUBTOTAL 関数の数式を作る
数式を作るときに、セルに「=SUBTOTAL(」までが入力されると、集計方法の一覧が表示されます。
さきほどの表や、表示された一覧の関数名を参考に、自分が行いたい集計方法を選びます。
キーボードの [↓] キーを使って使用する集計方法にフォーカスをあてて、[Tab] キーを押すと数字が選択されます。
あとは、第 2 引数に計算対象とするセル範囲を指定して完成です。
Step 3 オートフィルターではない
「非表示」のデータについて
画像でちらっと見えているのですが、同じ AVERAGE でも「1」と「101」の 2 種類が選べそうです。
これはなんでしょうね？
オートフィルターとの組み合わせだけで使う場合は、「1」でも「101」でも結果は同じですので、どちらを使ってもよいです (気にしなくてよいです。)
気にしなきゃいけない、使い分けなきゃいけないのは、手動で行の非表示を行ったり、グループ化によってデータを折りたたんだりするリストを使うときです。
Step2 のところでご紹介した集計の種類の一覧には、1 ～ 11 だけがまとめられていましたが、実は、引数として指定する計算の種類を表す数字は、101 ～111 という桁数の異なる種類も存在します。
そして、下図でいうところの「非表示」というのは、手動で設定した行の非表示やグループ化の折りたたみによって非表示になっているデータのことを指します。
しつこいようですが、オートフィルターとの組み合わせだけで使用するならどっちでも結果は同じです。
下図のように一部の行を非表示にするとします。
「103」を使っているとき (3 から 103 に変更したとき)、範囲内の非表示は計算から除外されるので「個数B」は 7 という結果が返ります。
「9」を使っているとき、範囲内の非表示も計算に含まれるので「合計B」には 150 という結果が返ります。
SUBTOTAL 関数はリスト (一覧形式の表) との組み合わせで利用する関数です。
SUM や COUNTA を使う / SUBTOTAL を使う は、どちらがよい、悪いということではなく、特徴を知ったうえで使い分けましょうということです。
オートフィルターでデータを抽出して、該当するデータ件数や数値合計などをセルに表示したい、なんていう方は覚えておくとよい関数です。
石田 かのこ
