-
Home »
-
エクセル関数一覧表 »
-
Excel関数の目次 »
-
小計の関数(SUBTOTAL関数)の使い方
- 小計の関数SUBTOTAL関数の使い方を解説しています。合計だけではなく平均やカウントなどの計算も可能です。
なお、SUBTOTAL関数は縦方向の計算に使うもので、縦方向の計算では非表示の行があったら無視して計算することができますが、横方向の計算では非表示の列があっても無視して計算はできません。 - SUBTOTAL関数の強化版といった感じで、 Excel2010では AGGREGATE関数 が追加されました。
作成:2013/4/12,
更新:2023/5/22
小計の関数(表示されているセル範囲の合計)など topへ
サブトータル
=SUBTOTAL(集計方法,範囲)
集計方法はExcel2002以前とExcel2003以降とで若干異なります。集計方法についてをご覧ください
- 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) では無視されません。
- 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個もあり覚えるのが大変・・・
しかす、数式のオートコンプリート機能や引数のヒントを利用すると便利です。
- =su と入力すると、suを含む関数がリスト表示されます。
[↓]キーを押して SUBTOTAL を選択して、[Tab]キーを押します。
- セルには =SUBTOTAL( と入力され、引数(集計方法)はリストに表示されます。
- [↓]キーを数回押して、 9-SUM を選択して、[Tab]キーを押して入力します。
- =SUBTOTAL(9 と入力されますので、,(カンマ)を入力して参照範囲を指定します。
非表示といっても、オートフィルターで非表示にしたら109でも9でも同じ結果になります。
- オートフィルタで非表示セルはどちらを使っても計算されません。「非表示の値を含める」引数(一桁の引数)でも非表示のセル値は計算されません。
よって、オートフィルタで絞り込んだデータの計算にはどちらを使っても同じ結果になります。
- 7〜11行を非表示にしています。
引数に 9 を指定しても、非表示の行の値も含めて計算されます。ちなみに、SUM関数と同じ結果になります。
引数に 109 を指定すると、非表示の行の値は無視して計算されます。
- オートフィルターで クラスが 1 の行のデータが非表示になっています。
引数に 9 を指定しても非表示の行の値は無視して計算されます。
- 非表示セルの値を無視する(3桁の集計方法)が有効なケースは、行を非表示とした場合や、アウトライン表示で折りたたんだ時の計算になります。
- 【例】セル範囲の合計をする場合、集計方法は「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』を使った例です。
小計の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) と複数のセル範囲を指定する必要があります。
- 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) と入力してあります。
C11セルに =SUBTOTAL(109,C2:C8)、C12セルには =SUM(C2:C8) と入力しています。
=SUBTOTAL(9,C2:C8)と=SUBTOTAL(109,C2:C8)では同じ結果になりますが、=SUM(C2:C8) では非表示のセルの値も含めて計算されます。 - オートフィルタで合計行が含まれないように、表と合計行の間に空白行(9行目)を挟みます。
C10〜C12セルの計算結果は同じです。
- リストA1:C8のいずれかのセルを選択し、[データ]タブの[フィルター]をクリックします。
- Excel2003以前では、リストA1:C8のいずれかのセルを選択し、[データ]→[フィルタ]→[オートフィルタ]を選択します。
- クラスで「1」を選択するとそれぞれ抽出されたデータの合計が表示されます。
C10セルとC11セルは表示されているセルの値の合計が計算されます。
C12セルには非表示のセルの値も計算されています。
- クラスで「2」を選択するとそれぞれ抽出されたデータの合計が表示されます。
C10セルとC11セルは表示されているセルの値の合計が計算されます。
C12セルには非表示のセルの値も計算されています。
オートフィルタで抽出したデータに、SUBTOTAL関数を使って連続Noを付ける例です
- A2セルに =SUBTOTAL(3,$C$2:C2) と入力し、A8セルまでフィルハンドルをダブルクリックして、コピーします。
集計方法に「3=COUNTA」を使って、C列の文字列のセル数を数えています。
- クラスで「1」を選択。またはクラスで「2」を選択するとそれぞれ抽出されたデータに連続したNoが表示されます。
クラスで「1」を選択した例
- クラスで「2」を選択した例
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数一覧表 »
小計の関数(SUBTOTAL関数)の使い方
PageViewCounter
Since2006/2/27