ExcelのSUBTOTAL関数で集計しているのに、「9」や「109」を指定しても正しく計算されない──。こんな場面に出くわすと、「こんな基本的な関数でなぜ？」と手が止まってしまいますよね。
SUBTOTAL関数が「計算されない」ときの症状は、実は次の4パターンに分かれます。
- 結果が0になってしまう
- #VALUE! などのエラーが返ってくる
- 数式がそのまま文字として表示される
- 行を非表示にしたのに合計が変わらない（または変わってほしくないのに変わる）
本記事では、Microsoft公式の仕様をもとに、症状別の原因の切り分け方とそれぞれの解決策を解説します。「設定を直したのに計算されない」ケースや、エラー混じりのデータを集計するAGGREGATE関数への切り替えまでカバーしていますので、この記事だけで解決できます。
まず結論：「9」と「109」の違いは”手動で非表示にした行”の扱いだけ
SUBTOTAL関数の基本構文は次のとおりです。
=SUBTOTAL(集計方法, 範囲)「集計方法」に9または109を指定すると合計（SUM相当）になりますが、両者の違いは次の1点だけです。
| 集計方法 | オートフィルターで非表示になった行 | 手動で非表示にした行 |
|---|---|---|
| 9 | 除外する | 含める |
| 109 | 除外する | 除外する |
Microsoft公式ドキュメントにも、「集計方法の値にかかわらず、フィルターの結果に含まれていない行はすべて無視される」と明記されています。つまりオートフィルターに対しては9も109も同じ動きをします。差が出るのは、行番号を右クリックして「非表示」にしたときだけです。
『フィルターなら9でも109でも結果は同じ』──ここを誤解していると『計算されない！』と勘違いしやすいんです
この仕様を押さえたうえで、「計算されない」原因を症状別に切り分けていきましょう。
症状別チェック：あなたの「計算されない」はどのタイプ？
まずは下の表で、いま起きている症状から原因の見当をつけてください。
| 症状 | 考えられる原因 | 解説箇所 |
|---|---|---|
| 結果が0になる | 範囲内の数値が「文字列」扱いになっている | 原因① |
| #VALUE! などのエラーになる | 範囲内にエラー値がある／3-D参照を指定している | 原因② |
| 行を非表示にしても合計が変わらない | 「9」を使っている（仕様どおりの動作） | 原因③ |
| 合計が大きすぎる（二重計上） | 範囲内の小計がSUM関数で作られている | 原因④ |
| 数式がそのまま表示される／再計算されない | 表示形式・計算方法などExcelの基本設定 | 原因⑤ |
原因①：セルが「文字列」扱いになっていて結果が0になる
SUBTOTAL関数は数値データを集計する関数です。範囲内の数値が「文字列」として保存されていると、その値は集計から除外され、すべてが文字列なら結果は0になります。数式は間違っていないのに0が返ってくる場合、まずここを疑ってください。
文字列になっているかの見分け方
- 数値なのにセル内で左寄せになっている
- セルの左上に緑色の三角マーク（エラーインジケーター）が付いている
- セルを選択すると「数値が文字列として保存されています」と警告が出る
基幹システムからダウンロードしたCSVや、他システムから貼り付けたデータでよく起こるパターンです
修正方法（基本）
- 該当のセル範囲を選択
- [ホーム]タブ → [数値]グループで表示形式を「標準」または「数値」に変更
- 各セルをダブルクリックしてEnter（表示形式の変更だけでは既存データは数値に戻らないため）
修正方法（まとめて数値に戻す：データの区切り位置）
セルが大量にある場合、1つずつダブルクリックするのは現実的ではありません。「データの区切り位置」機能を使うと一括で数値に変換できます。
- 該当の列（1列ずつ）を選択
- [データ]タブ → [データの区切り位置]をクリック
- [次へ]を2回クリック
- 「列のデータ形式」で「G/標準」を選択し、[完了]をクリック
原因②：範囲内にエラー値があり、SUBTOTALごとエラーになる
集計範囲の中に #VALUE! や #DIV/0!、#N/A などのエラー値が1つでも含まれていると、SUBTOTAL関数の結果自体もエラーになります。VLOOKUPやXLOOKUPの結果列を集計しているときに起こりがちなパターンです。
範囲内にエラーがあるかを関数で確認する
目視で探しにくいときは、次の式でエラーの個数を数えられます。
=SUMPRODUCT(--ISERROR(A1:A10))ISERROR(A1:A10)→ 各セルがエラーなら TRUE、正常なら FALSE--→ TRUE を 1、FALSE を 0 に変換SUMPRODUCT→ 1 の個数を合計＝エラーの個数
結果が1以上ならエラーありです。[ホーム]タブ → [検索と選択] → [条件を選択してジャンプ] → 「数式」の「エラー値」にチェックを入れれば、エラーセルの場所も一括で特定できます。
解決策A：エラーの発生源を修正する（基本）
本来はこちらが正攻法です。たとえばVLOOKUPの#N/Aであれば、参照元のセル側を次のように修正します。
=IFERROR(VLOOKUP(A2,マスタ!A:B,2,FALSE),0)
解決策B：エラーを無視して集計できるAGGREGATE関数に切り替える
エラーの発生源を直せない（直す権限がない・件数が多すぎる）場合は、AGGREGATE関数が便利です。Excel 2010以降で使え、オプション指定でエラー値を無視して集計できます。
=AGGREGATE(9, 7, A1:A10)- 第1引数 9 → 合計（SUMに相当。SUBTOTALと同じ番号体系）
- 第2引数 7 → 「非表示の行とエラー値を無視」（エラーだけ無視したい場合は 6）
つまり AGGREGATE(9,7,範囲) は、「SUBTOTAL(109,範囲)のエラー無視版」として使えます。
原因③：「9」と「109」の使い分けを誤解している（仕様どおりの動き）
「計算されない」と思っていたら、実はExcelは仕様どおりに動いていた、というケースも非常に多いです。冒頭の表のとおり、9と109の差は「手動で非表示にした行」の扱いだけなので、実際の数値で確認しておきましょう。
| 商品 | 売上 |
|---|---|
| リンゴ | 100 |
| バナナ | 200 |
| オレンジ | 300 |
売上の範囲をB2:B4として、パターン別の結果は次のとおりです。
| 状態 | =SUBTOTAL(9,B2:B4) | =SUBTOTAL(109,B2:B4) |
|---|---|---|
| 全行表示 | 600 | 600 |
| オートフィルターでバナナを非表示 | 400 | 400 |
| バナナの行を右クリックで手動非表示 | 600のまま | 400 |
「行を非表示にしたのに合計が変わらない！」という場合、原因は不具合ではなく「9」を使っていることです。表示中の行だけを常に集計したいなら「109」に変更してください。逆に、非表示行も含めて集計したい場面ではあえて「9」を使います。
知っておきたい2つの補足仕様
Microsoft公式ドキュメントに記載されている、見落としやすい仕様が2つあります。
- 横方向の範囲には非表示除外が効かない：SUBTOTAL関数は縦方向（列のデータ）の集計用です。
=SUBTOTAL(109,B2:G2)のように横方向の範囲を指定した場合、列を非表示にしても結果は変わりません。 - 3-D参照は使えない：
Sheet1:Sheet3!B2のような3-D参照を指定すると#VALUE!エラーになります。
なお、表をテーブル化して「集計行」を表示すると、Excelが自動で =SUBTOTAL(109,[列名]) という数式を挿入します。テーブルの集計行が「表示中の行だけ」を合計するのは、この109の仕様によるものです。
原因④：範囲内の小計がSUM関数で作られている（合計が大きすぎる）
SUBTOTAL関数には「範囲内にあるほかのSUBTOTAL関数（およびAGGREGATE関数）の結果を自動的に除外する」という便利な性質があり、小計行を含んだ範囲をまるごと指定しても総計を二重計上しません。
ただし除外されるのはSUBTOTAL・AGGREGATEで計算された小計だけです。小計行がSUM関数で作られていると除外されず、二重計上になります。総計が明らかに大きすぎる場合は、範囲内の小計セルの数式を確認し、小計側もSUBTOTALに統一してください。
原因⑤：Excelの基本設定が原因（SUBTOTAL以外の関数でも起こる）
ここまでの原因に当てはまらない場合は、SUBTOTALに限らずどの関数でも起こる、Excel側の基本設定を確認します。
「＝」を書いていない
SUBTOTAL(9,A1:A10) と入力しても、先頭に「＝」がなければExcelは数式として認識しません。単なる文字として表示されている場合は、まず先頭の「＝」を確認してください。
計算方法が「手動」になっている
計算方法が「手動」だと、データを変更しても自動で再計算されず、古い結果が表示されたままになります。
- [数式]タブを開く
- [計算方法の設定] → [自動]を選択
すぐに再計算だけしたい場合はF9キーでも実行できます。
「数式の表示」モードがONになっている
セルに計算結果ではなく数式そのものが表示される場合は、このモードがONになっています。
- [数式]タブをクリック
- [ワークシート分析]グループの「数式の表示」をOFFにする
ショートカットキー Ctrl + Shift + @ でも切り替えられます（知らないうちに押してしまっているケースが多いです）。
循環参照になっている
SUBTOTAL関数を入力したセル自身が集計範囲に含まれていると循環参照になり、正しく計算されません。合計行のすぐ上までを範囲にするつもりが、合計行自身まで含めてしまうミスが典型例です。
- [数式]タブをクリック
- [ワークシート分析]グループ → [エラーチェック]の▼ → [循環参照]を選択
- 表示されたセルの数式を確認し、範囲を修正
コピー時に参照範囲がズレている（絶対参照）
数式をオートフィルでコピーしたときに結果がおかしい場合は、相対参照のまま範囲がズレている可能性があります。範囲を $B$2:$B$4 のように絶対参照（数式編集中にF4キー）にしてからコピーしてください。
まとめ：チェックの順番はこの5ステップ
SUBTOTAL関数が計算されないときは、次の順番で確認すれば原因にたどり着けます。
- 結果が0 → 範囲内の数値が文字列になっていないか（左寄せ・緑三角）を確認し、「データの区切り位置」で一括変換
- エラーが返る →
=SUMPRODUCT(--ISERROR(範囲))でエラー混入を確認し、発生源を修正するか=AGGREGATE(9,7,範囲)に切り替え - 非表示行の扱いがおかしい → フィルターは9・109とも除外、手動非表示は109のみ除外という仕様を確認
- 合計が大きすぎる → 範囲内の小計がSUMになっていないか確認し、SUBTOTALに統一
- それ以外 → 先頭の「＝」、計算方法（手動/自動）、数式の表示モード、循環参照、絶対参照を順に確認
とくに「9」と「109」の違いは、フィルターではなく手動非表示のときだけ差が出るという点さえ押さえておけば、もう迷うことはありません。エラー混じりのデータを日常的に集計するなら、上位互換にあたるAGGREGATE関数への切り替えもぜひ検討してみてください。
コメント