-
よねさんのWordとExcelの小部屋 »
-
エクセル関数一覧表 »
-
小計の関数(SUBTOTAL関数)の使い方
- 小計の関数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へ
- F6セルを選択します。数式バーの[関数の挿入]ボタンをクリックします。
- 関数の挿入ダイアログが表示されます。
関数の分類で すべて表示 を選択します。(数学/三角を選択してもOKです。)
関数名で SUBTOTAL を選択します。
[OK]ボタンをクリックします。
- 【関数の引数】ダイアログが表示されます。
集計方法で 9 を入力します。
参照1に F3:F5 を指定します。
{OK]ボタンをクリックします。
F6セルの数式は =SUBTOTAL(9,F3:F5) となります。
- F9セルを選択して、上記と同様に、SUBTOTAL関数の関数の引数ダイアログボックスを表示し、
集計方法で 9 を入力します。
参照1に F7:F8 を指定します。
{OK]ボタンをクリックします。
F9セルの数式は =SUBTOTAL(9,F7:F8) となります。
- F13セルを選択して、上記と同様に、SUBTOTAL関数の関数の引数ダイアログボックスを表示し、
集計方法で 9 を入力します。
参照1に F10:F12 を指定します。
{OK]ボタンをクリックします。
F13セルの数式は =SUBTOTAL(9,F10:F12) となります。
- F14セルを選択して、上記と同様に、SUBTOTAL関数の関数の引数ダイアログボックスを表示し、
集計方法で 9 を入力します。
参照1に F3:F13 を指定します。
{OK]ボタンをクリックします。
数式は =SUBTOTAL(9,F3:F13) となります。
オートフィルタとの組み合わせで表示セルのみの合計を計算する例です
- C10セルに「=SUBTOTAL(9,C2:C8)」と入力してあります。
- オートフィルタで合計行が含まれないように、表と合計行の間に空白行を挟みます。
- リストA1:C8のいずれかのセルを選択し、[データ]タブの[フィルター]をクリックします。
- Excel2003以前では、リストA1:C8のいずれかのセルを選択し、[データ]→[フィルタ]→[オートフィルタ]を選択します。
- クラスで「1」を選択。またはクラスで「2」を選択するとそれぞれ抽出されたデータの合計が表示されます。
- クラスで「1」を選択した例
- クラスで「2」を選択した例
オートフィルタで抽出したデータに、SUBTOTAL関数を使って連続Noを付ける例です
- A2セルに「=SUBTOTAL(3,$C$2:C2)」と入力し、A8セルまでフィルハンドルをドラッグして、コピーします。
集計方法に「3=COUNTA」を使って、C列の文字列のセル数を数えています。
- クラスで「1」を選択。またはクラスで「2」を選択するとそれぞれ抽出されたデータに連続したNoが表示されます。
- クラスで「1」を選択した例
- クラスで「2」を選択した例
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数一覧表 »
小計の関数(SUBTOTAL関数)の使い方
PageViewCounter

Since2006/2/27