この記事ではエクセルのSUBTOTALの9の使い方や、合わない・計算されない原因と直し方、さらにSUBTOTALの9と109の集計方法の違いと使い分けについて解説していきます。
・SUBTOTALの9の基本的な使い方
・SUBTOTALの9で合わない・計算されない原因と対処法
・SUBTOTALの9と109の集計方法の違いと使い分け
エクセル操作は1つ1つ追っていけば必ずうまくできるため、ぜひ本記事を参考にスキルアップにつなげてくださいませ♪
エクセルのSUBTOTALの9の使い方の基礎
それでは以下のサンプルを用いてSUBTOTALの9の基本的な使い方を確認していきます。
SUBTOTAL関数はフィルタ―をかけた場合でも自動でフィルタにかかったもののみに対して処理できる便利な関数です（通常のSUM関数では、フィルタをかけてもすべての範囲に対して合計がかかったまま）。
A列に地域を記載（後にフィルタにかける）し、B列に数値を入力します。
そしてB10セルに以下の数式を入力します。
この数式の意味は次の通りです。
9はSUBTOTALの関数の種類を表し、SUMを意味します。つまり合計を計算します。
B1:B8は集計対象の範囲を指定しています。この例ではB1からB8までのセルを合計計算の対象としているわけですね(^^)/
ENTERで処理を確定させます。
現状では、フィルタがかかっていないすべての地域の合計2800が表示されました。
その後、B10セルをダブルクリックしてオートフィルにかけることで、B列全体の合計を計算できます。
次にA列の地域でフィルタをかけてみましょう。
1行目をまず選択します。
その後で、フィルタを適用させます。
ここでは東京のみに範囲を絞ってみましょう。
するとフィルタで非表示にしたセルを除いた合計がB10セルに表示されます。これがSUBTOTALの9の基本的な使い方です。
通常のSUMではフィルタが考慮されずに2800のままとなるので、フィルタをかけ合計を脱す際にはおすすめですね♪
エクセルのSUBTOTALの9で合わない・計算されない原因と直し方
続いては、SUBTOTALの9を使っているのに合わない・計算されない原因と直し方を確認していきます。
考えられる原因は主に以下の2点です。
・フィルタでなく非表示にした（これは集計対象外）
・途中の小計などが除外されると思いそのまま範囲に入れていた（普通にSUBTOTALはフィルタで見えているすべての合計を出す）
フィルタでなく非表示にした（これは集計対象外）
SUBTOTAL関数ではフィルタでの非表示は自動で対応してくれるものの、手動で非表示にした場合は検出してくれません。
以下3,4行目を非表示にしてみます。
合計値が見えているセルと一致していないのがわかるでしょう。
この解決策としては（手動での非表示も反映させたい）、SUBTOTAL 109の方を使用すればＯＫです。こちらは手動非表示も考慮された可視セルの合計となります。
途中の小計などが除外されると思いそのまま範囲に入れていた（普通にSUBTOTALはフィルタで見えているすべての合計を出す）
なお、SUBTOTAL 9 関数では、途中の数式を範囲にいれても合計に反映されないと思う人もたまにいます。
ただ、仕様としてフィルタにかけた可視セルすべての合計が表示されるため、注意しましょう！
途中の小計などは元から列を別にしておくのがおすすめですね♪
エクセルのSUBTOTALの9と109の集計方法の違いと使い分け
最後に、SUBTOTALの9と109の集計方法の違いと使い分けを見ていきましょう。
上にも記載のよう、
・SUBTOTALの9は、フィルタで非表示にしたセルのみを除いて合計します（手動非表示は反映されず）
・ 一方、SUBTOTALの109は、フィルタの状態に関係なく、非表示のセルも含めてすべてのセルを集計します。
使い分けとしては、フィルタで絞り込んだ結果の小計を求めたい場合はSUBTOTALの9を使い、フィルタに関係なく常に可視セル全体の合計を求めたい場合はSUBTOTALの109を使うといいですね(^^)
