1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. 小計の関数(SUBTOTAL関数)の使い方

小計の関数(SUBTOTAL関数)の使い方:Excel関数

• 小計の関数SUBTOTAL関数の使い方を解説しています。合計だけではなく平均やカウントなどの計算も可能です。
  なお、SUBTOTAL関数は縦方向の計算に使うもので、縦方向の計算では非表示の行があったら無視して計算することができますが、横方向の計算では非表示の列があっても無視して計算はできません。
• SUBTOTAL関数の強化版といった感じで、　Excel2010では　AGGREGATE関数　が追加されました。

更新:2023/5/22;作成:2013/4/12

小計の関数(表示されているセル範囲の合計)など　　　topへ
　サブトータル
=SUBTOTAL(集計方法,範囲)
　　集計方法はExcel2002以前とExcel2003以降とで若干異なります。集計方法についてをご覧ください

SUBTOTAL関数の集計の種類について

• Excel2003以降では「非表示の値を無視する」ことが指定できるようになりました。
  Excel2003以降では引数の集計方法で3ケタの数値を指定すると、非表示の値を無視して計算されます。
  集計の引数には下表の引数を指定することができます。
  

  非表示の行も含める	非表示の行を無視する	関数	集計の方法
  Excel2002以前	Excel2003以降
  1	101	AVERAGE	平均値を求める
  2	102	COUNT	数値の個数を求める
  3	103	COUNTA	データの個数を求める
  4	104	MAX	最大値を求める
  5	105	MIN	最小値を求める
  6	106	PRODUCT	積を求める
  7	107	STDEV	不偏標準偏差を求める
  8	108	STDEVP	標本標準偏差を求める
  9	109	SUM	合計値を求める
  10	110	VAR	不偏分散を求める
  11	111	VARP	標本分散を求める

SUBTOTAL関数を関数のヒントを利用して入力する方法

• SUBTOTAL関数の引数は1〜11、101〜111と22個もあり覚えるのが大変・・・
  しかし、数式のオートコンプリート機能や引数のヒントを利用すると便利です。

1. =su と入力すると、suを含む関数がリスト表示されます。
   [↓]キーを押して　SUBTOTAL を選択して、[Tab]キーを押します。
   
2. セルには　=SUBTOTAL( と入力され、引数(集計方法)はリストに表示されます。
   
3. [↓]キーを数回押して、　9-SUM を選択して、[Tab]キーを押して入力します。
   
4. =SUBTOTAL(9　と入力されますので、,(カンマ)を入力して参照範囲を指定します。
   

非表示といっても、オートフィルターで非表示にしたら109でも9でも同じ結果になります。

• オートフィルタで非表示セルはどちらを使っても計算されません。
  「非表示の値を含める」引数(一桁の引数)でも非表示のセル値は計算されません。
  よって、オートフィルタで絞り込んだデータの計算にはどちらを使っても同じ結果になります。
  • 7〜11行を非表示にしています。
    引数に　9　を指定しても、非表示の行の値も含めて計算されます。ちなみに、SUM関数と同じ結果になります。
    引数に 109 を指定すると、非表示の行の値は無視して計算されます。
    
  • オートフィルターで クラスが 1 の行のデータが非表示になっています。
    引数に　9 を指定しても非表示の行の値は無視して計算されます。
    
• 非表示セルの値を無視する(3桁の集計方法)が有効なケースは、行を非表示とした場合や、アウトライン表示で折りたたんだ時の計算になります。
  • 【例】セル範囲の合計をする場合、集計方法は「9」または「109」
    • 非表示の値も含める場合：=SUBTOTAL(9,A1:A10)
      非表示の値を無視する場合：　=SUBTOTAL(109,A1:A10)
• 集計機能(小計 コマンド)を使うと集計にはSUBTOTAL関数が挿入されます。その場合の引数は 一桁の方で非表示の値も計算します。
  • 下図では『諸費』のデータを折りたたんでいますが、『諸費』の非表示の値を集計した結果が表示されています。
    つまり「小計」機能では非表示の行も集計した結果を表示する必要があるため引数は 9 が使用されています。
  

SUBTOTAL関数の集計方法について

• SUBTOTAL関数は小計を求めることができる関数ですが、集計方法の指定で平均やカウント、最大値、最小値なども計算できます。

SUBTOTAL関数の引数の範囲の中にあるSUBTOTAL関数の値は無視されます

• もっとも特徴的な点は、SUBTOTAL関数の範囲内にSUBTOTAL関数で求めた値のセルが含まれるときは無視して計算してくれます。
• 下図の小計と合計はSUBTOTAL関数で求めています。
  計算しているセルのイメージ
  合計の=SUBTOTAL(9,F3:F13)　の参照範囲にある(途中にある)SUBTOTAL関数で求めている小計は無視して計算されます。
  
  ↓
  数式の参照セルのイメージ
  

SUBTOTAL関数はフィルターで非表示となった値は計算しません

• SUBTOTAL関数はオートフィルターなどで非表示の行を集計しないといった計算が可能です。
• 下図のように、SUBTOTAL関数とSUM関数とでの集計(合計)の違いを確認してください。
  C15セルに　=SUBTOTAL(9,C3:C13) と入力してあります。
  すべてのセルが表示されているので、　C15セルのSUBTOTAL関数で求めた合計と、C16セルで求めたSUM関数での合計と一致しています。
  
  ↓
  フィルターで商品が「りんご」と「みかん」を抽出しました。
  SUBTOTAL関数では表示された数量だけが計算できますが、SUM関数では非表示の数量も含めて計算されています。
  よって、C15セルは480、C16セルは660と計算結果が異なっています。
  

行を非表示にした時のSUBTOTAL関数の集計方法の違い

• ややこしいのですが、非表示セルの値を無視するが有効なケースは、行を非表示とした場合や、アウトライン表示で折りたたんだ時の計算になります。
  • 【例】セル範囲の合計をする場合、集計方法は「9」または「109」
    非表示の値も含める場合：=SUBTOTAL(9,範囲)
    非表示の値を無視する場合：　=SUBTOTAL(109,範囲)
• 6〜9行目を選択して、[データ]タブのアウトライン グループの[グループ化]でアウトラインを作成して折りたたみます。
  
  ↓
  =SUBTOTAL(109,C3:C13) と引数が 109 の時に非表示になったセルの値が無視されて計算されます。
  =SUBTOTAL(9,C3:C13) では無視されません。
  
• 6〜9行目を選択して、[ホーム]タブのセル グループの[書式]→[非表示/再表示]→[行を表示しない]で非表示としています。
  
  ↓
  =SUBTOTAL(109,C3:C13) と引数が 109 の時に非表示になったセルの値が無視されて計算されます。
  =SUBTOTAL(9,C3:C13) では無視されません。
  

【問題1】表内の小計と合計を SUBOTOTAL関数を使って求めなさい。

• 	B	C	D	E	F
  2	月　日	項目名	適用	収入金額	支出金額
  3	4月19日	会費	会費(19人分)	7,600
  4	4月25日	会費	会費(63人分)	25,200
  5	5月20日	会費	会費(2人分)	800
  6			小計	33,600	0
  7	5月10日	諸費	コピー代		4,800
  8	5月18日	諸費	工具代		14,200
  9			小計	0	19,000
  10	4月20日	図書費	新聞代		3,000
  11	4月26日	図書費	新聞代		5,000
  12	5月12日	図書費	本代		12,000
  13			小計	0	20,000
  14			合計	33,600	39,000

【解答1例】

• SUBTOTAL関数で合計『9』を使った例です。
  小計のE6セルは　=SUBTOTAL(9,E3:E5)
  E9セルは　=SUBTOTAL(9,E7:E8)
  E13セルは　=SUBTOTAL(9,E10:E12)
  とします。
• ポイントは 14行目の合計を求めるときの引数の範囲の指定の仕方です。
  SUBTOTALは範囲内に含まれるSUBTOTALの値は無視しますので、
  合計のセルE14は=SUBTOTAL(9,E3:E13)、セルF14は=SUBTOTAL(9,F3:F13)　と指定します。
• 
• なお、SUM関数で合計を求める場合は　E14セルには =SUM(E3:E5,E7:E8,E10:E12)　、F14セルには =SUM(F3:F5,F7:F8,F10:F12)　と複数のセル範囲を指定する必要があります。

[関数の挿入]ボタンを使って数式を入力する　　　topへ

1. F6セルを選択します。数式バーの[関数の挿入]ボタンをクリックします。
   
2. 関数の挿入ダイアログが表示されます。
   関数の分類で　すべて表示　を選択します。（数学/三角を選択してもOKです。)
   関数名で　SUBTOTAL　を選択します。
   [OK]ボタンをクリックします。
   
3. 【関数の引数】ダイアログが表示されます。
   集計方法で 9 を入力します。
   参照1に　F3:F5 を指定します。
   [OK]ボタンをクリックします。
   F6セルの数式は　=SUBTOTAL(9,F3:F5)　となります。
   
4. F9セルを選択して、上記と同様に、SUBTOTAL関数の関数の引数ダイアログボックスを表示し、
   集計方法で 9 を入力します。
   参照1に　F7:F8 を指定します。
   [OK]ボタンをクリックします。
   F9セルの数式は　=SUBTOTAL(9,F7:F8)　となります。
   
5. F13セルを選択して、上記と同様に、SUBTOTAL関数の関数の引数ダイアログボックスを表示し、
   集計方法で 9 を入力します。
   参照1に　F10:F12 を指定します。
   [OK]ボタンをクリックします。
   F13セルの数式は　=SUBTOTAL(9,F10:F12)　となります。
   
6. F14セルを選択して、上記と同様に、SUBTOTAL関数の関数の引数ダイアログボックスを表示し、
   集計方法で 9 を入力します。
   参照1に　F3:F13 を指定します。
   [OK]ボタンをクリックします。
   数式は　=SUBTOTAL(9,F3:F13)　となります。
   

オートフィルタとの組み合わせで表示セルのみの合計を計算する例です

• C10セルに　=SUBTOTAL(9,C2:C8)　と入力してあります。
  C11セルに　=SUBTOTAL(109,C2:C8)、C12セルには =SUM(C2:C8) と入力しています。
  =SUBTOTAL(9,C2:C8)と=SUBTOTAL(109,C2:C8)では同じ結果になりますが、=SUM(C2:C8) では非表示のセルの値も含めて計算されます。
• オートフィルタで合計行が含まれないように、表と合計行の間に空白行(9行目)を挟みます。
  C10〜C12セルの計算結果は同じです。
  

1. リストA1:C8のいずれかのセルを選択し、[データ]タブの[フィルター]をクリックします。
   
   • Excel2003以前では、リストA1:C8のいずれかのセルを選択し、[データ]→[フィルタ]→[オートフィルタ]を選択します。
2. クラスで「1」を選択するとそれぞれ抽出されたデータの合計が表示されます。
   C10セルとC11セルは表示されているセルの値の合計が計算されます。
   C12セルには非表示のセルの値も計算されています。
   
3. クラスで「2」を選択するとそれぞれ抽出されたデータの合計が表示されます。
   C10セルとC11セルは表示されているセルの値の合計が計算されます。
   C12セルには非表示のセルの値も計算されています。
   

オートフィルタで抽出したデータに、SUBTOTAL関数を使って連続Noを付ける例です

• A2セルに　=SUBTOTAL(3,$C$2:C2)　と入力し、A8セルまでフィルハンドルをダブルクリックして、コピーします。
  集計方法に「3=COUNTA」を使って、C列の文字列のセル数を数えています。
  

1. クラスで「1」を選択。またはクラスで「2」を選択するとそれぞれ抽出されたデータに連続したNoが表示されます。
   クラスで「1」を選択した例
   
2. クラスで「2」を選択した例
   

スポンサードリンク

PageViewCounter

Since2006/2/27