【Excel】SUM関数で非表示行も合計されてしまう!Excelの可視セルだけ集計する方法と関数選び

Excelでデータを集計する際、SUM関数は非常に便利です。しかし、非表示になっている行や列も合計に含まれてしまうため、意図しない結果になることがあります。

特に、特定の期間のデータだけを表示して集計したい場合や、分析のために一時的にデータを非表示にしている場合に、このSUM関数の挙動は問題となります。

この記事では、Excelで非表示行を無視して表示されているセル(可視セル)のみを合計する方法を、具体的な関数と操作手順を交えて解説します。

SUM関数で非表示行が計算に含まれてしまう原因と、それを回避するための「SUBTOTAL関数」や「AGGREGATE関数」の使い方を理解することで、より正確なデータ集計が可能になります。

SUM関数が非表示行を合計する仕組み

ExcelのSUM関数は、指定されたセル範囲に含まれる全ての数値データを単純に足し合わせる機能です。そのため、セルの表示・非表示の状態は考慮されません。

非表示になっているセルも、Excelの内部的にはデータが存在するため、SUM関数はそれを計算対象として扱います。これは、SUM関数が「セルの値」そのものを参照する仕様になっているためです。

この仕様は、単純なデータ集計には便利ですが、表示されているデータのみを集計したい場合には、意図しない結果を招く原因となります。

可視セルだけを合計するSUBTOTAL関数

非表示行やフィルターで隠された行を無視して集計するには、SUBTOTAL関数を使用するのが一般的です。

SUBTOTAL関数は、合計、平均、個数など、様々な集計機能を提供します。これらの機能は、第1引数に指定する「集計の種類」によって切り替わります。

非表示行を無視して合計するには、集計の種類として「9」(合計)を指定します。

SUBTOTAL関数での合計手順

SUBTOTAL関数を使って可視セルのみを合計する手順は以下の通りです。

1. 合計を表示したいセルを選択する
   集計結果を表示したいセルをクリックします。
2. SUBTOTAL関数を入力する
   数式バーに「=SUBTOTAL(9, 」と入力します。ここで、「9」は合計を指定する集計の種類コードです。
3. 集計対象のセル範囲を指定する
   合計したい可視セルが含まれる範囲をマウスでドラッグするか、セル番地を入力します。例えば、A1セルからA10セルまでを対象にする場合は「A1:A10」と入力します。
4. 関数を確定する
   数式バーの「fx」ボタンをクリックするか、Enterキーを押して数式を確定します。

この手順で入力したSUBTOTAL関数は、フィルター機能などで非表示にした行のデータは無視され、表示されているセルのみを合計します。

SUBTOTAL関数の集計の種類コード

SUBTOTAL関数では、集計の種類を指定する第1引数に、特定の数値を入力します。非表示行を無視するかどうかは、この数値の1桁目が「1」から「11」の場合(無視する)か、「101」から「111」の場合(無視しない)で決まります。

集計の種類	コード(非表示行を無視)	コード(非表示行も含む)
合計	9	109
平均	1	101
個数	2	102
最大値	4	104
最小値	5	105

合計を行いたい場合は、「9」または「109」を使用します。「9」は非表示行を無視し、「109」は非表示行も合計に含めます。SUM関数と同じ動作をさせたい場合は「109」を使用しますが、可視セルのみを合計したい場合は「9」を指定してください。

さらに高度な集計:AGGREGATE関数

AGGREGATE関数は、SUBTOTAL関数よりもさらに柔軟な集計が可能です。エラー値やネストされたSUBTOTAL関数などを無視するオプションも備えています。

AGGREGATE関数は、第1引数で集計の種類を、第2引数で無視するオプションを指定します。

可視セルのみを合計し、エラー値も無視したい場合は、集計の種類として「9」(合計)、無視するオプションとして「7」(エラー値とネストされたSUBTOTAL関数を無視)を指定します。

AGGREGATE関数での合計手順

AGGREGATE関数を使って可視セルのみを合計する手順は以下の通りです。

1. 合計を表示したいセルを選択する
   集計結果を表示したいセルをクリックします。
2. AGGREGATE関数を入力する
   数式バーに「=AGGREGATE(9, 7, 」と入力します。ここで、「9」は合計を指定する集計の種類、「7」はエラー値とネストされたSUBTOTAL関数を無視するオプションです。
3. 集計対象のセル範囲を指定する
   合計したい可視セルが含まれる範囲をマウスでドラッグするか、セル番地を入力します。
4. 関数を確定する
   数式バーの「fx」ボタンをクリックするか、Enterキーを押して数式を確定します。

AGGREGATE関数は、SUBTOTAL関数よりも多くのオプションを持っているため、より複雑な条件での集計に適しています。例えば、計算結果にエラー値が含まれる場合に、そのエラー値を無視して集計したい場合などに有効です。

AGGREGATE関数の集計の種類と無視するオプション

AGGREGATE関数は、SUBTOTAL関数よりも多くの集計機能と無視するオプションを提供します。

集計の種類	コード
合計	9
平均	1
個数	2
最大値	4
最小値	5

無視するオプション	コード	説明
なし	0	何も無視しない
エラー値	4	エラー値を無視する
ネストされたSUBTOTAL関数	8	ネストされたSUBTOTAL関数を無視する
エラー値とネストされたSUBTOTAL関数	12	エラー値とネストされたSUBTOTAL関数を無視する
非表示行(フィルター)	16	フィルターで非表示の行を無視する
エラー値、ネストされたSUBTOTAL関数、非表示行	24	エラー値、ネストされたSUBTOTAL関数、フィルターで非表示の行を無視する

可視セルのみを合計し、エラー値も無視したい場合は、「9」(合計)と「12」(エラー値とネストされたSUBTOTAL関数を無視)を組み合わせることも可能です。ただし、フィルターで非表示にした行を無視するには、「9」と「16」または「24」を組み合わせる必要があります。

フィルター機能との連携

SUBTOTAL関数やAGGREGATE関数は、Excelのフィルター機能と連携させることで、より強力な集計ツールとなります。

フィルター機能を使用すると、特定の条件に合致するデータのみを表示させることができます。この状態でSUBTOTAL関数やAGGREGATE関数を実行すると、表示されているデータのみを集計対象とします。

例えば、ある月の売上データだけを表示し、その合計をSUBTOTAL関数で計算すれば、その月の売上合計を簡単に得ることができます。

フィルター機能とSUBTOTAL関数を組み合わせる手順

1. データ範囲にフィルターを設定する
   集計したいデータ範囲のいずれかのセルを選択し、「データ」タブの「フィルター」をクリックします。
2. フィルターでデータを絞り込む
   各列のヘッダーに表示される▼ボタンをクリックし、表示したい条件を選択します。
3. SUBTOTAL関数で合計を計算する
   合計を表示したいセルに「=SUBTOTAL(9, 対象範囲)」と入力します。

この方法で、フィルターで表示されているデータのみを効率的に集計できます。フィルターを解除すれば、元の全データに対する集計結果に戻ります。

SUBTOTAL関数とAGGREGATE関数の使い分け

SUBTOTAL関数とAGGREGATE関数は、どちらも可視セルのみを集計できますが、それぞれ得意とする場面が異なります。

SUBTOTAL関数は、シンプルに非表示行やフィルターで隠された行を無視して集計したい場合に適しています。操作が比較的簡単で、多くの場面で利用できます。

AGGREGATE関数は、エラー値やネストされたSUBTOTAL関数など、さらに複雑な条件を無視して集計したい場合に強力です。より高度な分析や、複雑なデータ構造を持つ表の集計に適しています。

SUBTOTAL関数とAGGREGATE関数の機能比較

機能	SUBTOTAL関数	AGGREGATE関数
集計の種類	14種類	19種類
非表示行の無視	○	○
エラー値の無視	×	○
ネストされたSUBTOTAL関数の無視	×	○
最大数	11個	23個

集計したい内容が単純であればSUBTOTAL関数で十分ですが、データにエラーが含まれていたり、他の集計関数がネストされている場合はAGGREGATE関数を検討すると良いでしょう。

よくある誤解と注意点

SUM関数で非表示行が合計されてしまう問題は、Excelの仕様を理解していない場合に発生しやすい誤解です。

ここでは、よくある誤解や注意点について解説します。

「セルの書式設定」での非表示と「行の非表示」の違い

Excelでは、セルの書式設定で表示形式を「;;;」(セミコロン3つ)に設定することで、セルの値はそのままに表示だけを消すことができます。しかし、この方法で非表示にしたセルも、SUM関数は計算対象として扱います。

SUM関数で非表示行を無視したい場合は、セルの書式設定ではなく、行全体または列全体を非表示にする機能を使用する必要があります。具体的には、「ホーム」タブの「検索と選択」から「非表示/再表示」を選択するか、行番号や列番号を右クリックして「非表示」を選択します。

フィルターで「空白行」を非表示にした場合

フィルター機能で空白行を非表示にした場合、SUBTOTAL関数(集計の種類コードに「1」〜「11」を使用)は、その空白行を無視します。これは、空白セルは数値ではないため、SUM関数でも合計されないからです。

しかし、SUBTOTAL関数で集計の種類コードに「101」〜「111」を指定した場合、空白行も合計に含まれる可能性があります。意図しない集計結果にならないよう、集計の種類コードの選択には注意が必要です。

数式にエラー値が含まれる場合

集計対象のセル範囲に、#N/Aや#DIV/0!などのエラー値が含まれている場合、SUM関数はエラーを返します。

このような場合、SUBTOTAL関数(集計の種類コードに「9」を指定)も同様にエラーを返します。エラー値を無視して集計したい場合は、AGGREGATE関数を使用し、無視するオプション(コード「4」または「12」)を指定する必要があります。

例えば、「=AGGREGATE(9, 4, A1:A10)」と入力すれば、A1からA10の範囲にあるエラー値以外の数値を合計できます。

Excelで正確な集計を行うためには、使用する関数がどのようにデータを扱っているかを理解することが重要です。SUM関数、SUBTOTAL関数、AGGREGATE関数の違いを把握し、状況に応じて適切な関数を選択しましょう。

この記事では、Excelで非表示行を無視して可視セルのみを合計する方法として、SUBTOTAL関数とAGGREGATE関数を中心に解説しました。

SUM関数で非表示行が合計されてしまう問題は、SUBTOTAL関数(集計の種類コード9)やAGGREGATE関数(集計の種類コード9、無視するオプション16または24)を使うことで解決できます。

これらの関数をフィルター機能と組み合わせることで、より効率的かつ正確なデータ集計が可能になります。

今後は、集計したいデータの特性や、どのような条件で非表示にするかに応じて、これらの関数を使い分けてみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】