【Excel】エクセルでの非表示で計算しない設定方法（SUBTOTAL関数・集計への影響・設定も）

Excelを使って集計作業を行う中で、「非表示にした行のデータを計算から除外したい」と感じたことはないでしょうか。

通常のSUM関数などを使っていると、行を非表示にしても計算結果には反映されてしまい、思い通りの集計ができないケースがあります。

そこで活躍するのがSUBTOTAL関数です。

この関数を使えば、非表示の行を計算から除外することが可能になり、フィルタリングや手動での行非表示にも柔軟に対応できます。

本記事では、【Excel】エクセルでの非表示で計算しない設定方法（SUBTOTAL関数・集計への影響・設定も）について、詳しく解説していきます。

SUBTOTAL関数の基本から、集計への影響、具体的な設定方法まで順を追って確認していきましょう。

非表示行を計算から除外するにはSUBTOTAL関数が最適解

それではまず、非表示行を計算から除外するための方法について解説していきます。

Excelで非表示にした行を計算に含めたくない場合、最も効果的な解決策はSUBTOTAL関数を使うことです。

通常のSUM関数やAVERAGE関数は、非表示になっている行のデータもすべて計算対象に含めてしまいます。

一方、SUBTOTAL関数は非表示の行を自動的に除外して計算できるため、フィルターをかけた際や手動で行を隠した際にも正確な集計が可能です。

また、SUBTOTAL関数は集計方法を数値で指定することで、合計・平均・件数など複数の計算に対応している点も大きな魅力です。

引数の違いによって「手動非表示も除外するか」「フィルターのみ除外するか」を切り替えられるため、用途に応じた細かい設定も可能でしょう。

SUBTOTAL関数の基本的な構文

SUBTOTAL関数の構文は以下のとおりです。

「集計方法」には数値を指定し、どのような計算を行うかを決定します。

「参照1」以降には、集計したいセル範囲を指定します。

シンプルな構文ながら、集計方法の番号によって動作が大きく変わるため、番号の意味をしっかり理解しておくことが重要です。

集計方法番号の一覧と違い

SUBTOTAL関数で使用する集計方法の番号には、1〜11と101〜111の2種類があります。

1〜11は手動で非表示にした行も計算に含めるのに対し、101〜111は手動非表示の行も除外して計算するという違いがあります。

集計方法（含む）	集計方法（除外）	機能
1	101	AVERAGE（平均）
2	102	COUNT（数値の個数）
3	103	COUNTA（空白以外の個数）
4	104	MAX（最大値）
5	105	MIN（最小値）
9	109	SUM（合計）

最もよく使われるのは合計を求める9（または109）でしょう。

フィルターだけに対応させたい場合は9、手動の非表示行も除外したい場合は109を選ぶのがポイントです。

SUBTOTAL関数の入力例

実際にSUBTOTAL関数を入力する例を見てみましょう。

このように記述するだけで、非表示行を考慮した集計が自動的に行われます。

通常のSUM関数からの置き換えも簡単なため、既存の表にも取り入れやすいでしょう。

非表示行が集計に与える影響とSUM関数との違い

続いては、非表示行が集計に与える影響とSUM関数との違いを確認していきます。

Excelで行を非表示にしても、SUM関数はその行のデータを引き続き計算対象とします。

これは多くの方が直感的に思う「見えないなら計算されないはず」という感覚と異なるため、集計ミスの原因になりやすいポイントです。

SUBTOTAL関数との動作の違いを正しく理解しておくことで、意図しない計算ミスを防ぐことができます。

SUM関数は非表示行を無視しない

SUM関数を使って合計を出している場合、行を手動で非表示にしても結果は変わりません。

たとえばA1からA5の合計がSUM(A1:A5)で求められている状態で、A3の行を非表示にしても、A3のデータは引き続き合計に含まれます。

この違いが集計業務においては非常に重要です。

見た目では非表示になっているのに、合計値が想定より多くなってしまう場合は、SUM関数を使っていることが原因かもしれません。

フィルター使用時の集計への影響

Excelのオートフィルター機能を使って特定のデータだけを表示させた場合も、SUM関数はフィルターで非表示になった行のデータを計算に含めてしまいます。

一方、SUBTOTAL関数を使えばフィルターで非表示になった行のデータは自動的に除外されるため、表示中のデータのみを正確に集計できます。

売上データや在庫管理など、フィルターを多用する業務では特にこの違いが大きく影響するでしょう。

集計行のズレが起きるケース

非表示行による集計のズレが発生しやすい具体的なシーンを整理しておきましょう。

状況	SUM関数	SUBTOTAL関数
行を手動で非表示	非表示行も計算に含む	101〜111で除外可能
オートフィルター適用	非表示行も計算に含む	自動的に除外
グループ化で折りたたみ	非表示行も計算に含む	101〜111で除外可能
通常表示（非表示なし）	正常に計算	正常に計算

このように、行の非表示方法によって関数の挙動が異なります。

集計の目的に応じて、適切な関数と集計番号を選択することが大切です。

Excelで非表示かつ計算しない設定の具体的な手順

続いては、Excelで非表示行を計算から除外するための具体的な設定手順を確認していきます。

実際の操作ステップを理解することで、日々の業務にスムーズに取り入れることができるでしょう。

ここではフィルターを使う方法と、手動非表示を使う方法の両方を紹介します。

フィルターと組み合わせた設定方法

オートフィルターを使いながらSUBTOTAL関数で集計する手順は以下のとおりです。

この設定を行うことで、フィルターで絞り込むたびに集計値が自動更新されます。

大量データの分析や月次レポートの作成など、繰り返し使える実用的な方法です。

手動非表示に対応した設定方法

手動で行を非表示にしてその行を除外して計算したい場合は、集計方法を101〜111に変更する必要があります。

集計番号を9ではなく109にすることがポイントです。

9のままでは手動非表示の行が計算に含まれてしまうため、意図した集計結果になりません。

SUBTOTAL関数を複数条件で活用する方法

SUBTOTAL関数は1つのシートで複数の集計目的に対応させることも可能です。

たとえば同じデータ範囲に対して、合計・平均・件数をそれぞれ別のセルで管理したい場合は以下のように設定します。

このように集計方法番号を変えるだけで、1つの関数で多様な集計が実現できます。

ダッシュボード的な管理シートを作成する際にも非常に役立つ方法でしょう。

SUBTOTAL関数使用時の注意点とよくあるトラブル

続いては、SUBTOTAL関数を使う際の注意点とよくあるトラブルについて確認していきます。

便利な関数ですが、使い方を誤ると思わぬ集計ミスにつながることがあります。

あらかじめ注意点を把握しておくことで、より正確で安定した集計が実現できるでしょう。

SUBTOTAL関数がネストされた場合の動作

SUBTOTAL関数の参照範囲に、別のSUBTOTAL関数が含まれている場合、内側のSUBTOTALは自動的に無視されます。

これは二重集計を防ぐための仕様であり、合計行と小計行が混在するような表でも正確な集計が可能です。

これにより、カテゴリ別の小計と全体合計を同時に管理する集計表でも、ミスなく活用できます。

よくあるエラーと対処法

SUBTOTAL関数を使用していてトラブルが起きやすいケースをまとめました。

トラブル内容	原因	対処法
非表示行が計算に含まれる	集計番号が1〜11のまま	101〜111に変更する
フィルターしても値が変わらない	SUM関数を使っている	SUBTOTAL関数に置き換える
VALUE!エラーが出る	集計方法番号が無効	1〜11または101〜111の正しい番号を指定
集計結果が0になる	参照範囲がすべて非表示	表示されている行があるか確認する

特に集計番号のミスは起きやすいため、入力後は必ず意図した結果になっているか確認しましょう。

AGGREGATE関数との使い分け

SUBTOTAL関数に近い機能を持つ関数として、AGGREGATE関数もあります。

AGGREGATE関数はSUBTOTALより新しく、エラー値の無視やネストされた関数の除外など、より細かい制御が可能です。

ただし、AGGREGATE関数はExcel 2010以降でのみ使用できるため、古いバージョンとの互換性が必要な場合はSUBTOTAL関数を使うのが安心でしょう。

用途と環境に合わせて、どちらを使うか選択することが大切です。

まとめ

本記事では、【Excel】エクセルでの非表示で計算しない設定方法（SUBTOTAL関数・集計への影響・設定も）について詳しく解説しました。

非表示にした行を計算から除外するには、SUBTOTAL関数を使うことが最も効果的な方法です。

通常のSUM関数では非表示行が計算に含まれてしまいますが、SUBTOTAL関数の集計番号を適切に選ぶことで、フィルター適用時や手動非表示時にも正確な集計が実現できます。

集計番号9はフィルター非表示のみ除外、109は手動非表示も除外という違いを覚えておくと、現場での使い分けがスムーズになるでしょう。

また、SUBTOTAL関数は二重集計を自動回避する仕様になっているため、小計と総計が混在する集計表でも安心して活用できます。

今回の内容を参考に、Excelでの集計作業をより正確かつ効率的に進めてみてください。