SUM関数ではなく、SUBTOTAL関数を使用しよう
Excelで合計を算出する代表的な関数がSUM関数ですが、オートフィルター中にSUBTOTALを使用するとフィルターされるごとに個別の合計が算出されるのがSUBTOTAL関数です。合計が必要な表を作成した場合に必ず役立ちます。
SUBTOTAL関数を使ってみる
SUM関数やSUBTOTAL関数を使用する場合、合計欄は上部に設置したほうがおすすめです。
合計欄を上部に設置することで連続性のある表に対して下方向に行を追加できます。
下部に合計欄を設置すると毎回計算式の範囲を変更する必要があります。
上記表を作成してみました。
リボンにあるオートSUMを押下すると関数が表示され範囲を指定すると合計が表示されます。
SUBTOTAL関数を使用した場合
SUBTOTAL関数はフィルターで抽出した結果に対して合計を算出できるとてもかしこい関数です。
フィルターでいちごだけを抽出しました。列番号が青くなっている場合、フィルター機能が有効になっている状態です。
フィルターがかかった状態でE2を選択してからオートSUMを押下します。SUM関数ではなく、自動的にSUBTOTAL関数が表示されます。ここで範囲していますが、指定する場合に注意があります。
SUBTOTALを使用時の注意点
SUBTOTAL関数を選択する場合の注意点として、抽出されているときの範囲は、作成した表の合計欄がすべて選択されていない状態で範囲指定しまうとフィルターを解除したときの正しく計算されません。
表の金額欄の範囲がE4～E13になっています。
フィルターをかけた状態だと範囲が狭くなってしまいます。SUBTOTALの範囲をE7:E12からE3:E13に変更します。
フィルターでSUBTOTALを使用した合計値
みかんを抽出した合計
いちごを抽出した合計
熊本県を抽出した合計
SUBTOTALの集計方法の値を変更することでさまざまな結果が得られます。ただ個人的に合計のみしかあまり使わないので興味のある方は試してみるといいでしょう。
集計方法：1→平均値を求めます。2→数値の個数を求めます。３データの個数を求めます。
4→最大値を求めます。5→最小値を求めます。9→合計値を求めます
このほかにも特定の値を設定することで求めることができます。
まとめ
オートフィルターで表を作成するときはSUM関数ではなくSUBTOTAL関数を使用することで抽出結果ごとに簡単に合計等を求めることが可能になるとても便利な関数です。棚卸表や買掛未払管理表を作成した場合はオートフィルター+SUBTOTAL関数を使用してみてはいかがでしょう。
コメント