ひとつの集計表の中に「小計」と「合計」を同居させないといけない、計算式が面倒そう、ついつい「小計」計算と「合計」計算を別々にやってしまいそうですね。
EXCELには「オートSUM」ボタンが用意されていますが、何か予期しない動きをしないか？と心配される方が多いようなので解説いたします。
意外と「やってほしい事」をズバリとこなしてくれるので、ぜひご愛用ください。
↓ 簡単動画からご覧ください。
「小計」「合計」のある表
名前別・科目別点数表があります、「小計」と「合計」の式をいれてみましょう。
よく見る２つの例
「オートSUM」の機能を全く知らない場合、SUM を使わず、いちいち「小計」だけを指定して「合計」の計算式としている、といった例を見ることがあります。
「オートSUM」が 小計 と 合計のどちらにも使える事を知っていても、ついついやってしまう使い方は、①小計セルを選択 → ②「オートSUM」 → ③次の小計セル → ④「オートSUM」‥ と、小計毎に「オートSUM」ボタンを小刻みに使ってしまうやりかた。
ここで「オートSUM」ならではの機能を発揮するのは ⑦ の「合計」計算です。⑦の計算式は「=SUM(C13,C9,C5)」と、各「小計」だけを自動的に指定してくれます。
「オートSUM」１回で完了する方法
最初の「小計」を選択後、[Ctrl] を押しながらすべての「小計」「合計」セルを選択後、「オートSUM」を押すと、操作が大幅に軽減でき、ミスもなくなります。
オートSUMの「オート」の価値も少しは発揮できました。
オートフィルタと「オートSUM」
オートフィルタを設定していても、どのボタンも OFF の場合「オートSUM」を押して得られる数式は「=SUM」となります。
フィルタが ON なら SUBTOTAL
オートフィルタを設定していて、いづれかのボタンが ON の場合「オートSUM」を押して得られる数式は「=SUBTOTAL」となります。
①「科目」のフィルタが ON になっています。
②この例では「国語」のチェックをはずしています。
③式は「=SUBTOTAL(9,C2:C10)」となりました。
SUBTOTAL はフィルタと連動する
この表では「合計」行は１行だけですが、フィルタで選んだ内容の合計を表示してくれるため、シンプルで動的な表として使えます。
①科目を「算数」に絞り込んだ。
②氏名を「山田」に絞り込んだ。
集計方法が多彩な SUBTOTAL
SUBTOTAL(集計方法, 範囲1, 範囲2 …)
「オートSUM」から作成された SUBTOTAL の集計方法は自動的に「9＝合計」を指定していますが、「1＝平均」を入力すれば、フィルタと連動して「平均点」を表示させることも可能です。
上図赤の矢印「引数」を変更する際、ウィザードが開きますので適切な集計方法を選択することで、多彩な集計メニューを作成することもできます。
