SUBTOTAL関数で101以上を指定したときの仕様

SUBTOTAL関数の第1引数に「109」を指定すると、行を非表示にしたときに可視セルだけの合計を計算することができることをご紹介しました。

この仕様については、補足しておかなければならないことがあります。
SUBTOTAL関数の第1引数に「109」を指定しても、列を非表示にしたときには可視セルだけの合計を計算することはできないということです。

文章だけを読むよりも、実際のファイルをご確認いただくほうがわかりやすいでしょう。

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

サンプルファイルには、「折りたたみ」「行&列の非表示」という2つのシートを作成してあります。

いずれのシートにも、
　B2:E5セルに「1」という値
　C7セルに「=SUBTOTAL(9,C2:C5)」
　E7セルに「=SUBTOTAL(109,E2:E5)」
　G3セルに「=SUBTOTAL(9,B3:E3)」
　G4セルに「=SUBTOTAL(109,B5:E5)」
という数式をそれぞれ入力してあります。

「折りたたみ」シートでは、グループ化を行うことで、B列・D列・2行目・4行目の表示・非表示を簡単に切り替えられるようにしてあり、「行&列の非表示」シートではB列・D列・2行目・4行目を非表示にしてあります。

「=SUBTOTAL(109,E2:E5)」という数式の入力されているE7セルには、
　2行目・4行目を非表示にしたときに「2」
　2行目・4行目を表示したときには「4」
という値がそれぞれ表示されます。

しかし、
「=SUBTOTAL(109,B5:E5)」という数式の入力されているG4セルの場合、B列・D列を表示しようが非表示にしようが「4」という計算結果が表示されます。

これが、SUBTOTAL関数の第1引数に「109」といった値を指定したときの注意点です。

このことをご存知ない方がこの状態をご覧になると、
「エクセルのバグだ」
と思われるかもしれませんが、これはバグでもなんでもありません。

SUBTOTAL関数の仕様です。

SUBTOTAL 関数では、列のデータ、つまり縦方向の範囲が集計されます。行のデータ、つまり横方向の範囲を集計するための関数ではありません。たとえば、集計方法として 101 以上の値を指定し、SUBTOTAL(109,B2:G2) のように横方向の範囲を集計する場合、いずれかの列を非表示にしても集計結果は変わりません。縦方向の範囲を集計する場合は、いずれかの行を非表示にすると集計に影響します。

SUBTOTAL関数の第1引数に「109」を指定しても、列を非表示にしたときには可視セルだけの合計を計算することはできないのは、仕様ですので。

最終更新日時：2018-09-22 3:22