表から小計と集計を計算する
一部分を合算した小計と、小計が混ざった表で全体の合計を、１つの表情で全部まとめて集計する方法についてです。
SUM関数でなんとかしようとすると
合計値を出す関数といえばSUM関数ですね。
これだと、小計を出すまではいいのですが、合計を計算するときに問題が出てきます。
もちろん言うまでもなく、SUM(B1:B49)のような範囲設定をしてしまえば、小計行までいっしょに合算してしまいます。
範囲が狭いうちはまだいいですが、表が大きくなるにつれて、メンテナンスしきれなくなってきますので、できるだけ避けたほうがいい方法ですね。
SUBTOTAL関数を使う
次は、SUMの代わりにSUBTOTALを使ってみましょう。
=SUBTOTAL(集計方法,範囲)
今回は集計方法に9（SUMとして集計する）を使用する。
小計も全部SUBTOTALで計算します。
そして最終行にある合計では、SUBTOTAL(9,B1:B49)とし、小計行もまきこんで範囲選択します。
すると、SUBTOTAL関数は、同じSUBTOTAL関数で計算されている行を読み飛ばしてくれるため、小計をまきこんでいるにも関わらず、正確に合計を計算してくれます。
ただし、読み飛ばしてくれるのはSUBTOTALで計算されている行のみなので、小計をSUMで計算してしまうと誤った結果になってしまうので注意してください。
エクセルの小計機能を使う
エクセルには自動で小計・集計してくれる機能があります。
少し準備が必要なので、順を追って解説していきます。
小計をとりたいグループの設定
エクセルの小計機能では、同じ値をグループとして自動設定してくれるので、先にグループ分けをしておきます。
今回は月ごとに小計を取りたいので、左に列を挿入して、月だけ（日なし）のセルを用意します。
データ→小計
表内のデータがあるセルを選択している状態で、データタブ内になる、小計を選択します。
設定変更していなければ、右端のほうにあると思います。
集計の設定
小計を取りたいグループはどこか、どの数値を計算するのかなどを設定します。
今回は月ごとにエサ代を計算したいので、月のみをセットしたA列をグループ基準、集計フィールドにエサ代を選択します。
完成
名前こそ集計になってますが、しっかり各月で小計をとりつつ、最後に全体の合計を計算してくれてます。
集計という名前が嫌ならば、直接セル編集で直せるので、自由に手直ししましょう。
ただこの小計機能、やってることはSUBTOTAL関数を自動で作ってくれてるだけなので、自分で式作る場合と結局は同じなんですよね。
アウトライン機能を使っているため、左の方に余計な情報が出てきてしまうこともあり、個人的には苦手です。
まとめ
手動だろうが自動だろうが結局はSUBTOTALに落ち着くっていうおはなし。
そしてこのSUBTOTAL、小計に使う以外でも便利な機能があるので、興味があれば関連記事も合わせて参考にしてみてください。
コメント