1. よねさんのWordとExcelの小部屋 » 
2. エクセル関数一覧表 » 
3. 小計の関数(SUBTOTAL関数)の使い方

小計の関数(SUBTOTAL関数)の使い方:Excel(エクセル)基本講座

• 小計の関数SUBTOTAL関数の使い方を解説しています。合計だけではなく平均やカウントなどの計算も可能です。
• 似たような関数としては　Excel2010で　AGGREGATE関数　が追加されました。

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

集計方法について

• SUBTOTAL関数は小計を求めることができる関数ですが、集計方法の指定で平均やカウント、最大値、最小値なども計算できます。
• もっとも特徴的な点は、SUBTOTAL関数の範囲内にSUBTOTAL関数で求めた値のセルが含まれるときは無視して計算してくれます。
  • 下図の小計と合計はSUBTOTAL関数で求めています。
    計算しているセルのイメージ
    
    ↓
    数式の参照セルのイメージ
    
• SUBTOTAL関数オートフィルターなど非表示の行を集計しないといった計算が可能です。
  • 下図のように、SUBTOTAL関数とSUM関数とでの集計(合計)の違いを確認してください。
    
    ↓
    フィルターで商品が「りんご」と「みかん」を抽出しました。
    SUBTOTAL関数では表示された数量だけが計算できますが、SUM関数では非表示の数量も含めて計算されています。
    

集計の種類について

• Excel2003以降では「非表示の値を無視する」ことが指定できるようになりました。
  集計の引数には下表の引数を指定することができます。
  

  非表示の値も含める	非表示の値を無視する	関数	集計の方法
  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( とかっこまで手入力すると、集計方法の一覧が表示されますのでうろ覚えでも大丈夫です。
    表示されたリストは　↓ (カーソルキー)で選択して、[Tab:]キーで決定(入力)します。
    
• オートフィルタで非表示セルはどちらを使っても計算されません。「非表示の値を含める」引数(一桁の引数)でも非表示のセル値は計算されません。
  よって、オートフィルタで絞り込んだデータの計算にはどちらを使っても同じ結果になります。
  • 7〜11行を非表示にしています。
    引数に　9　を指定しても、非表示の行の値も含めて計算されます。ちなみに、SUM関数と同じ結果になります。
    引数に 109 を指定すると、非表示の行の値は無視して計算されます。
    
  • オートフィルターで クラスが 1 の行のデータが非表示になっています。
    引数に　9 を指定しても非表示の行の値は無視して計算されます。
    
• 非表示セルの値を無視するが有効なケースはなお、書式→行→非表示とした場合や、アウトライン表示で折りたたんだ時の計算になります。
  • 【例】セル範囲の合計をする場合、集計方法は「9」または「109」
    • 非表示の値も含める場合：=SUBTOTAL(9,A1:A10)
    • 非表示の値を無視する場合：　=SUBTOTAL(109,A1:A10)
• 集計機能(小計 コマンド)を使うと集計にはSUBTOTAL関数が挿入されます。その場合の引数は 一桁の方で非表示の値も計算します。
  • 下図では『諸費』のデータを折りたたんでいますが、『諸費』の非表示の値を集計した結果が表示されています。
    つまり「小計」機能では非表示の行も集計した結果を表示する必要があるため引数は 9 が使用されています。
  

【問題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』を使った例です。
• ポイントは 14行目の引数の範囲の指定の仕方です。
  SUBTOTALは範囲内に含まれるSUBTOTALの値は無視しますので、合計のセルE14は=SUBTOTAL(9,E3:E13)、セルF14は=SUBTOTAL(9,F3:F13)　と指定します。

• 	B	C	D	E	F
  2	月　日	項目名	適用	収入金額	支出金額
  3	4月19日	会費	会費(19人分)	7,600
  4	4月25日	会費	会費(63人分)	25,200
  5	5月20日	会費	会費(2人分)	800
  6			小計	=SUBTOTAL(9,E3:E5)	=SUBTOTAL(9,F3:F5)
  7	5月10日	諸費	コピー代		4,800
  8	5月18日	諸費	工具代		14,200
  9			小計	=SUBTOTAL(9,E7:E8)	=SUBTOTAL(9,F7:F8)
  10	4月20日	図書費	新聞代		3,000
  11	4月26日	図書費	新聞代		5,000
  12	5月12日	図書費	本代		12,000
  13			小計	=SUBTOTAL(9,E10:E12)	=SUBTOTAL(9,F10:F12)
  14			合計	=SUBTOTAL(9,E3:E13)	=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)」と入力してあります。
• オートフィルタで合計行が含まれないように、表と合計行の間に空白行を挟みます。
  

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

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

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

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

スポンサードリンク

PageViewCounter

Since2006/2/27