ExcelのSUBTOTAL関数を使用して集計する際に、「9」や「109」を指定しても計算されないという問題に直面したことはありませんか？これにドハマりすると思考停止してしまいますよね。どうしてこんな簡単な関数で・・・？と絶望的な気持ちになります。
Excelで数式や関数が正常に反映されないとき、次のような問題が発生します。
- 想定していた正しい答えが反映されない
- 計算結果が0になってしまう
- 数式がそのまま表示される
本記事では、SUBTOTAL関数の動作を詳しく解説し、「計算されない原因」と「解決策」を分かりやすく説明します。
SUBTOTAL関数とは？
SUBTOTAL関数は、Excelで特定の範囲に対して合計や平均などの集計を行う関数です。
SUBTOTAL関数の基本構文
=SUBTOTAL(集計方法, 範囲)ここでの「集計方法」には、一例として以下のような数値を指定します
| 集計方法の指定 | オートフィルタで非表示になったセル | 手動で非表示にしたセル | 意味 |
|---|---|---|---|
| 9 | 除外する | 含める | 合計（オートフィルタで非表示になったセルを除外） |
| 109 | 除外する | 除外する | 合計（オートフィルタや手動で非表示にしたセルを除外） |
SUBTOTAL 9 / 109 が計算されない原因
1. 範囲に「非数値データ」が含まれている
SUBTOTAL関数は数値データを対象とするため、文字列や空白セルがあると正しく計算されないことがあります。
2. フィルターの適用状態により動作が異なる
SUBTOTAL 109 を使った場合、フィルターで非表示になったセルと手動で非表示にしたセルは計算に含まれません。
3. セルに「数式エラー」が発生している
SUM関数などと組み合わせた場合に、エラー（`#VALUE!` など）があると計算がうまくいかないことがあります。
詳しく説明します
SUBTOTAL(9, A1:A10) や SUBTOTAL(109, A1:A10) を使っても計算が正しく行われない場合、データ範囲内にエラー値（#VALUE!、#DIV/0! など）が含まれている可能性 があります。
特に、SUM 関数などと組み合わせた場合、範囲内にエラーが含まれると計算がうまくいかず、結果が「0」になったり、エラー表示が出たりする ことがあります。
🔍 エラーが発生しているかを関数で確認する方法
エラーが発生しているかを調べるには、ISERROR 関数と SUMPRODUCT 関数を組み合わせると便利です。
▼ データ範囲（A1:A10）内にエラーがあるかをチェックする
✅ この式の意味
ISERROR(A1:A10)→ 各セルがエラーならTRUE（1）、正常ならFALSE（0）--ISERROR(A1:A10)→TRUEを1、FALSEを0に変換SUMPRODUCT(--ISERROR(A1:A10))→1の数を合計（=エラーの個数）
✅ 結果の見方
0→ エラーなし（正常）1以上→ エラーあり
SUBTOTAL関数の動作を理解する
例：SUBTOTAL(9,範囲)とSUBTOTAL(109,範囲)の違い
次のデータを例に考えてみましょう：
| A | B |
|---|---|
| 商品 | 売上 |
| リンゴ | 100 |
| バナナ | 200 |
| オレンジ | 300 |
ここで、以下の数式を使用します：
=SUBTOTAL(9, B2:B4)→ 結果は 600（すべての行を合計）
=SUBTOTAL(109, B2:B4)→ フィルターで「バナナ」を非表示にすると結果は 400（非表示行を除く）
SUBTOTAL関数が計算されない場合の解決策
1. 範囲を確認する
数式の範囲に不要な空白や文字列が含まれていないかチェックしましょう。
こんなパターンもあります。
上記の例だと該当セルが文字列扱いになっています。このセルは数字として認識されませんので、標準になおす必要があります。
2. フィルターの状態を確認する
SUBTOTAL 109 はフィルターの影響を受けるため、正しくデータが表示されているかを確認します。
3. セルのエラーを修正する
もしエラーが発生している場合は、以下のようにIFERRORを使うことで回避できます。
=IFERROR(SUBTOTAL(109, B2:B4), 0)
さらに詳しく：基本的な数式設定ミス
- エラー①：数式や関数に「＝」を書いていない
- エラー②：セルの表示形式が「文字列」になっている（データの区切り位置も考慮）
- エラー③：計算方法が「手動」になっている
- エラー④：「数式の表示」モードがONになっている
- エラー⑤：絶対参照になっていない
- エラー⑥：循環参照になっている
- まとめ
エラー①：数式や関数に「＝」を書いていない
Excelの関数や数式を使うには、必ず「＝」をつける必要があります。
例えば、SUBTOTAL(9, A1:A10) を使いたい場合、「＝」がないとExcelは関数として認識しません。
エラー②：セルの表示形式が「文字列」になっている（データの区切り位置も考慮）
セルの表示形式が「文字列」になっていると、Excelは数式をただの文字として扱うため計算が行われません。
修正方法（基本）
- 該当のセルを選択
- [ホーム]タブ → [数値]グループで「標準」または「数値」に変更
- セルをダブルクリックし、Enterを押して再計算
修正方法（データの区切り位置が影響している場合）
セルの表示形式を変更しても計算されない場合、データの区切り位置タブで「文字列」に設定されている可能性があります。
- 該当のセルまたは範囲を選択
- [データ]タブをクリック
- [データの区切り位置]をクリック
- [次へ]を2回クリックし、「列のデータ形式」で「標準」を選択
- [完了]をクリック
エラー③：計算方法が「手動」になっている
Excelでは、計算方法を「手動」に設定していると、自動で再計算が行われません。
修正方法
- [数式]タブを開く
- [計算方法の設定] → [自動] を選択
エラー④：「数式の表示」モードがONになっている
Excelの「数式の表示」モードがONになっていると、セルに計算結果ではなく数式そのものが表示されてしまいます。
修正方法
- [数式]タブをクリック
- [ワークシート分析]グループの「数式の表示」をOFFにする
エラー⑤：絶対参照になっていない
相対参照になっていると、オートフィル時に参照範囲がズレてしまい、正しく計算できないことがあります。
修正方法
- セルを選択して編集モード（ダブルクリック）
- 参照するセルに「$」を追加（例：
=C5*$C$2） - [F4]キーを押すと自動で絶対参照に変換可能
エラー⑥：循環参照になっている
循環参照とは、数式が自分自身のセルを参照してしまうエラーのことです。
修正方法
- [数式]タブをクリック
- [ワークシート分析]グループ → [エラーチェック] → [循環参照] を選択
- Excelが示したセルを確認し、数式を修正
まとめ
この方法を試せば、SUBTOTAL関数が正常に動作するはずです！これでも解決しないとなるともうPCかExcel自体がおかしくなっているかもしれません。
Follow me!
コメント