合計を計算する関数第三弾はSUBTOTALです。
SUBTOTAL関数は色々な視点で集計できる関数で、利用頻度は結構高い関数だと思います。
合計や、平均、最小や最大など11種類の集計方法を使うことができます。
例えばこのような表があり、売上合計を事業所別や、種類別に色々とフィルターを変えて確認したいようなときに活用できる関数です。
SUBTOTAL関数は以下のように使います。
SUBTOTAL関数：色々な集計方法を使いたい
フィルターを変えていろんな視点で確認したい
SUBTOTAL関数の使い方はこちらの通りです。
=SUBTOTAL(集計方法,参照1,参照2・・・)
集計方法：1～11/101～111まで選択
参照：集計したい数値が入ったセルを指定します。
集計方法の種類はこちらになります。
| 集計方法 | 説明 |
|---|---|
| 1 or 101 | [AVERAGE]平均値を求める |
| 2 or 102 | [COUNT]個数を求める |
| 3 or 103 | [COUNTA]データ個数を求める |
| 4 or 104 | [MAX]最大値を求める |
| 5 or 105 | [MIN]最小値を求める |
| 6 or 106 | [PRODUCT]積を求める |
| 8 or 108 | [STDEV.P]母集団の標準偏差を求める |
| 7 or 107 | [STDEV.S]標準偏差の推定値を求める |
| 9 or 109 | [SUM]合計を求める |
| 11 or 111 | [VAR.P]母集団の分散 (標本分散) |
| 10 or 110 | [VAR.S]母集団の分散の推定値 (不偏分散) |
平均値や、合計などはよく使うので、集計方法の値を覚えておくと便利です。
ちなみに、上の例でSUM関数を使った場合はどのようになるでしょうか？
SUM関数で合計を出す場合は =SUM(G4:G21) で合計が出ますね。この関数をH2セルに入れておきます。
比較のためにG2セルには =SUBTOTAL(9,G4:G21) の関数が入力してあります。
この状態で種類の行で”ぶき”でフィルターをかけると、SUBTOTAL関数で計算した結果は"ぶき"でフィルターした結果の合計が表示されますが、SUM関数で計算した方は合計売上金額はフィルターをかける前の状態のままです。
このようにSUBTOTAL関数で計算しておくと、事業所名でフィルターをかけ、ローレシア支店の売上合計をみたり、品物別に見たりすることができます。
ここで、注目ポイントです。 普通合計を出すときには、下の表のように一番下に合計と入れることが多いですが上の例では一番上に合計を入れています。
これは、売上など日々レコードが追加されていくような表の場合、いちいち下の行に関数を入れていくのは手間ですよね。
そこで、表の一番上にあらかじめ集計するための関数を入れておき、レコードを追加するときには何も気にせずに下にどんどん追加することができます。
会社の販売管理のシステムなどからExcelでデータ抽出をした時などは毎回データの行数が異なると思います。
そんな時に、1行目に集計の関数をいれた計算用のシートを一つ準備しておき、システムから出力したデータを2行目以降に張り付ければ毎回計算式を作らずに済みます。
今回はSUBTOTAL関数の紹介でした。