【エクセル】SUBTOTAL関数がSUM関数の代わりに勝手に挿入される２つの原因と対策

数値の合計を求めるためにSUM関数を挿入したはずが、SUBTOTAL関数が勝手に挿入されていることは無いでしょうか。

その原因は、①表に『フィルター』が設定されている、もしくは➁『テーブル』機能を利用している、のどちらかの可能性が高いです。

今回は、SUBTOTAL関数がどういった関数なのかを理解していただいた上で、SUBTOTAL関数が勝手に挿入される２つの原因と対策を図解を含めながら解説します。

SUBTOTAL関数とは？

SUBTOTAL関数は、指定した集計方法でセル範囲の数値を集計する方法です。

集計とは、「合計」だけでなく「平均値」「最大値」「最小値」「セルの個数」など様々な集計をこのSUBTOTAL関数で可能となります。

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

＝SUBTOTAL(集計方法,参照１,参照２,参照３,・・・)

SUBTOTAL関数の第１引数「集計方法」は１桁コードもしくは３桁コードの２種類があり、下の計２２種類があります。

この２２種類のコードを選択することで、様々な集計方法が可能となります。

表．SUBTOTAL関数の集計方法のコード

集計方法で１桁コード(例:１)を使うと、非表示のセルを含める集計となります。

集計方法で３桁コード(例:１０９)を使うと、非表示のセルを含めない集計となります。　

SUBTOTAL関数の集計方法が「合計」の引数である「9」と「109」が良く見かける値かと思います。以下の見出しで、SUBTOTALの引数「9」と「109」を使った例を１つずつ紹介します。

SUBTOTAL関数の集計方法「9」

集計方法を”合計”の引数「9」を使用することで、非表示のセルも含んだ合計を算出することが出来ます。

SUM関数と同じでは？と思う方が多いかと思いますが、SUBTOTAL関数は集計範囲にSUBTOTAL関数が含まれる場合、そのセルを除外して集計が行われるというメリットがあります。

下の表のD13セルには、「=SUBTOTAL(9,D5:D12)」を挿入していて、D5:D12のセル範囲の中にも小計を算出するためのSUBTOTAL関数が２つ挿入されています。

しかし、D13セルのSUBTOTAL関数は、集計範囲内のSUBTOTAL関数を自動で集計から除外して正しい合計を算出することが出来ます。

SUBTOTAL関数は、集計範囲内のSUBTOTAL関数を自動で集計から除外します。

SUBTOTAL関数の集計方法「109」

集計方法が合計の引数「109」を使用することで、非表示のセルを含まず合計を算出することが出来ます。

下の表では、C11セルに「=SUBTOTAL(9,C5:C10)」が挿入されていて、C5:C10のセル範囲の合計「2,100」が算出されています。

この状態で、８行目の非表示にすると、SUBTOTAL関数の引数は変更しなくても、C8セルの「400」が合計から省かれて、SUBTOTAL関数の結果は「2,100」から「1,700」に変わりました。

SUBTOTAL関数は、非表示のセルの値を集計から除外することが出来る。

SUBTOTAL関数が勝手に挿入される原因その１「フィルター」

それでは、今回の本題であるSUM関数を挿入したつもりがSUBTOTAL関数が勝手に挿入される原因の１つ目を紹介します。

それは、「フィルター」機能で一部が非表示になっている表に対して、「オートSUM」ボタンでSUM関数を挿入すると、自動でSUBTOTAL関数が挿入されます。

SUM関数のショートカットキー「Ctrl」＋「Alt」＋「＝」キーを用いても、同様にSUBTOTAL関数が挿入されます。

下の表で実際にSUBTOTAL関数が挿入される仕組みを図解で説明します。

下の食品売上表にはフィルターが設定されています。フィルターの設定や使い方はこちらの記事を参考にしてください。

次に、フィルターの▼を押して、「商品名」列から「パスタ」を非表示にします。そうすると、８行目が非表示になります。

非表示が含まれる表の最下行に、SUM関数を挿入するために、「ホーム」タブの「オートSUM」ボタンを押します。そうすると、下の図のように自動でSUBTOTAL関数が挿入されてしまいます。ＳＵＭ関数のショートカットキー「「Ctrl」＋「Alt」＋「＝」キー」を押しても同様です。

また、SUBTOTAL関数の集計方法は非表示も含む「9」が挿入されていますが、フィルター機能の影響で、非表示の値は合計されません。

C12セルに挿入されたSUBTOTAL関数を横のセルにコピペしても、全てのセルがSUBTOTAL関数が挿入され、非表示にした「パスタ」の数は集計されていません。

SUBTOTAL関数が勝手に挿入される原因その２「テーブル」

続いて、SUBTOTAL関数が勝手に挿入される２つ目の原因を紹介します。

それは、「テーブル」機能が使われている表に対して、「オートSUM」ボタンでSUM関数を挿入すると、自動でSUBTOTAL関数が挿入されます。

SUM関数のショートカットキー「Ctrl」＋「Alt」＋「＝」キーを用いても、同様にSUBTOTAL関数が挿入されます。

下の表で実際にSUBTOTAL関数が挿入される仕組みを図解で説明します。

表のどのセルでもいいのでクリックしてください。「テーブルデザイン」タブが「ヘルプ」タブの横に表示された場合、その表には「テーブル」という機能が設定されています。

この「テーブル」機能が設定されている表の最下行をクリックしてから、「ホーム」タブの「オートSUM」ボタンを押します。そうすると、下の図のように自動でSUBTOTAL関数が挿入されてしまいます。ＳＵＭ関数のショートカットキー「「Ctrl」＋「Alt」＋「＝」キー」を押しても同様です。

非表示のセルも合計する方法

SUBTOTAL関数がSUM関数の代わりに挿入されるのは、「非表示になっているセルは合計しない方がいいよね？」とExcelが自動で判断している、ということです。

非表示のセルも合計する方法とSUM関数を挿入する方法を紹介します。

セルにSUM関数を直接入力して挿入

「オートSUM」ボタンやSUM関数のショートカットキーを使わずに、セルに直接「＝SUM(C5:C10)」と入力して「Enter」キーを押してください。

そうすると、下の図のようにSUM関数を挿入することが出来て、非表示の値も合計することが出来ます。

SUBTOTAL関数の下にSUM関数を挿入

SUBTOTAL関数は非表示の値を合計から省くというメリットがあります。

そのため、SUBTOTAL関数は消さずに、SUBTOTAL関数の下の空きスペースに、上の見出しで紹介した方法で「SUM関数」行を挿入しましょう。

そうすることで、表の合計を表示する行と、非表示の値を除いた合計行の２行を表示させておくと集計結果を２つ確認することが出来るので便利です。

「テーブル」機能を解除

「テーブル」機能が設定された表は、集計しやすくデザインも変更しやすいので便利ですが、使い方が分からない方にとってはハードルが高いかもしれません。

SUM関数を挿入するために、「テーブル」機能を解除する場合は、「テーブルデザイン」タブの「範囲に変換」をクリックしてください。

SUBTOTAL関数の便利な使い方

一見、避けられがちに思えるSUBTOTAL関数ですが、便利な使い方を１つ紹介します。

それは、SUBTOTAL関数を使った自動ナンバリングです。

表に連番でナンバリングする際、行を非表示にしたりすると番号を付け直す必要がありますが、その際にSUBTOTAL関数を使うと自動でナンバリングしてくれるので便利です。

以下の記事で使い方を紹介していますので、興味のある方は参考にしてみてください。