可視セル・見えているセルだけ合計−SUBTOTAL関数

オートフィルタでデータの絞り込みをしたときに、その抽出されたデータだけの合計を求める方法をご紹介しました。

よく似た要望で、行を非表示にしたときに、見えているセル・可視セルだけの合計を求めたいという要望をいただくこともあります。

このお話をすると誤解をする方が時々いらっしゃいます。

SUM関数で合計を計算しているときに、いずれかの行を非表示にすれば、非表示になった部分を無視した合計になると考えている方がいらっしゃるのです。

その理解は間違いです。

非表示になった行を無視してSUM関数で合計が計算されると考えていらした方は、是非ご自分の手を動かして確認することをおすすめします。

SUM関数では非表示になった行も含めて合計が計算されます。

結論として、SUBTOTAL関数を使うと、非表示になった行を無視して、可視セル・見えているセルだけの合計を計算することができます。

ただし、オートフィルタのときのように、第1引数に「9」を指定してはいけません。

SUBTOTAL関数の第1引数に「9」を指定した場合は、行を非表示にしても、非表示の行を含んだ合計になってしまいます。SUM関数と同じ結果になります。

しかし上記のように第1引数に「109」を指定すれば、非表示になった行を無視した合計が計算されるのです。

▼サンプルファイル（003596.xls 33KByte）ダウンロード

サンプルファイルのA6セルには上記の例、B6セルにはSUBTOTAL関数の第1引数に「9」を指定した「=SUBTOTAL(9,A1:A5)」という数式、C6セルにはSUM関数を使った「=SUM(A1:A5)」という数式が入力してあります。

1:5行目のいずれかの行を非表示にしたときに、A6:C6セルにどのような値が表示されるかご確認ください。

なお、非表示の値を無視する「109」はExcel 2003で指定できるようになった引数ですから、Excel 2002などで上記の計算式を入力してもエラーとなります。