エクセルSUBTOTAL関数で集計方法によってさまざまな集計をする使い方-marusblog

エクセルのSUBTOTAL関数は、集計方法の条件を指定することでさまざまな集計を求めることができます。合計はもちろん平均や積や数字のカウントなどSUBTOTAL関数で求めることができます。とくに小計やフィルター機能を使ってデータを絞る際に、絞ったデータに限った計算結果を表示してくれます。SUM関数やSUMIF関数でなくSUBTOTAL関数でならではの場面での使い方のご紹介です。

SUBTOTAL関数

SUBTOTAL関数は、リストまたはデータベースの集計値を返す関数です。

SUBTOTAL関数の書式

SUBTOTAL関数の書式を確認しましょう。

 

（SUBTOTAL関数の書式）

=SUBTOTAL（集計方法,参照1,参照2,…）

 

SUBTOTAL関数は、集計方法値と関数の種類を番号で指定します。

SUBTOTAL関数の集計方法と関数の指定方法

集計方法と関数は次の通り。

SUBTOTAL関数は上記のように11パターンの集計方法が用意されています。

集計の目的	集計方法（非表示含む）	集計方法（非表示無視）	同等の集計関数
平均値を求める	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関数の使い方例をご紹介します。SUBTOTAL関数で合計と平均を求めてみます。引数の使い方を確認しましょう。

表データを用意する

• 下図は、店舗、食品、販売価格が入力されています。
• Ｄ14に販売実績の合計を求めるセルがあります。
• SUBTOTAL関数を使って合計値を計算しましょう。

集計方法「９」を指定する

• Ｄ14セルに直接「=sub」まで入力をしたところで関数の候補が表示されます。
• 「SUBTOTAL」を選択し「TAB」キーをおします。

 

• D14セルに「=SUBTOTAL(」と入力されたと同時に、計算方法のリストが表示されます。
• 今回は「合計」ですので、「９」を選択し「Tab」キーを押します。

 

• D14セルに「=SUBTOTAL(9」と入力されました。
• 引き続き、カンマ「,」を入力します。
• 「=SUBTOTAL(9,」となります。

 

• D14セルに「=SUBTOTAL(9,」に続いて、引数の「参照」を入力します。
• ここでは、「D5:D13」が参照範囲となります。
• マウスでドラッグして範囲を指定します。
• 「）」を入力せずに「Enter」キーを押します。

 

• Ｄ14セルに「=SUBTOTAL(9,D5:D13)」と入力されました。
• 結果、合計値が計算され表示されました。

集計方法「１」（平均）を指定する

• D14に「=SUBTOTAL(9,D5:D13)」と入力し、集計方法「9」の「合計」を求めました。
• 入力した数式から集計方法「1」の「平均」に変更して平均値を求めてみましょう。

 

• D14セルを選択します。
• ファンクションキー「F2」を押します。
• セルがアクティブ状態になり数式を変更できる状態になりました。
• 集計方法「9（合計）」を「１（平均）」に変更します。

 

• 「1」に変更すると集計方法の候補が表示されます。
• キーボードの矢印を動かして「1-AVERAGE」を選択。
• 「Tab」キーを押してセルに入力。
• 「Enter」キーを押して確定となります。

 

• セルD14に平均値が求められました。
• 数式バーで「=SUBTOTAL(1,D5:D13)」と確認できます。

フィルター機能でデータを絞込みしたところでの合計結果

• ①表内の任意の箇所にセルを配置します。
• ②「データ」タブをクリック。
• ③「並べ替えとフィルター」グループの「フィルター」をクリック。
• ④集計表のタイトルに「▼」マークが付きフィルター機能が用意されました。

 

• ①集計表の項目の「▼」マークをクリック。
• ②店舗の中から「B店舗」のチェックのみ残して「B店舗」のみとします。

 

• 店舗が「Ｂ店舗」のみ選択となります。
• フィルターで絞った「B店舗」のみの販売実績が求められました。

 

• D14セルに合計関数「=SUM(D5:D13)」で求めると全店舗の販売実績が計算されたままになります。
• これが、「SUM関数」と「SUBTOTAL関数」の大きな違い、使い分ける理由がここにあります。

行の非表示の値を含める場合含めない場合

集計表の必要な部分とそうでない部分がある場合、不要な部分の行を非表示にする場合があります。このときSUBTOTAL関数では集計を切り替えることができるのでご紹介します。

（集計方法を「合計値を求める」の場合）

集計の目的	集計方法（非表示含む）	集計方法（非表示無視）	同等の集計関数
合計値を求める	9	109	SUM関数

非表示の値を含める場合

• 集計表の店舗の中「Ｂ店舗」を非表示にします。
• 「Ｂ店舗」が並ぶ行番号をドラッグして選択し右クリック。
• メニューが表示される中、「非表示」をクリック。

 

• 「B店舗」が非表示になっていますので、店舗列には「A店舗」と「B店舗」のみ表示されています。
• D14セルには「=SUBTOTAL(9,D5:D13)」の式が入力されています。
• 数式バーで数式を確認できます。
• 集計方法が「9」ですので、非表示の値も含めての計算結果になります。

非表示の値を含めない場合

• 非表示の値を含まない合計方法は「109」で指定します。
• D14セルには「=SUBTOTAL(109,D5:D13)」と入力になります。
• 数式バーで数式が確認できます。

 

• Ｂ店舗の並ぶ行を「非表示」にします。
• 行番号「7～9」をドラッグして選択します。
• 右クリック
• 「非表示」クリック。

 

• 集計範囲は「D5:D13」となっていますが、合計の値の中に非表示にしたB店舗の値は指定通り含まれていません。

私の感想

SUBTOTAL関数はとてもユニークな関数に思います。範囲指定は変わらないのに集計方法の数字を変えるだけで、非表示の値を含む合計か含まない合計かを切り替えてくれるのです。この切り替えはSUBTOTAL関数に用意されている集計方法はいずれもできるので便利です。

今回ご紹介したとおり、SUM関数では式を書き直さねばなりませんから、定形で大きい集計表をアウトプットする機会が多い場合などとても役立ちます。あらかじめ数種類の集計方法をセルに入力して用意しておけば、合計値、数、平均、MAXなど、選択したデータによって数値が把握できるのでおススメです。

 

SUBTOTAL関数はユニークで頻度も多く役に立ちます。

 

（marusblog関連記事紹介）

http://marus.info/%ef%bd%85xcel-input-function/

http://marus.info/excel-vlookup-blank-marusblog/

http://marus.info/excel-count-counta-marusblog/

今回のまとめ

今回はエクセル関数の「SUBTOTAL関数」をご紹介しました。SUBTOTAL関数の書式の解説、関数の基本の使い方、集計表を用意しての集計方法など実例を用意しての解説でした。

 

「SUBTOTAL関数の書式」は、「=SUBTOTAL（集計方法,参照1,参照2,…）」。

 

集計方法と関数は次の通り。

集計の目的	集計方法（非表示含む）	集計方法（非表示無視）	同等の集計関数
平均値を求める	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

実際に表を用意して、集計方法「9」の「合計」や「1」の「平均」を求めて、その入力方法をご紹介。また、フィルター機能を使ってデータを絞り込んだ場合の絞り込んだ部分だけの集計、絞り込みを無視して全体の集計を求める方法なども解説しました。

データの中から必要な部分だけを把握したいと言った場合、集計方法をあらかじめセルに指定することで、速やかに集計結果を確認することができますので、便利で役立ちます。

 

「SUBTOTAL関数」の使い方のご紹介でした。

 

いかがでしたでしょうか？

最後まで読んでくださりありがとうございます。人気ブログランキングに参加中。こちらクリックして頂けましたらうれしく思います。
↓↓↓

Microsoft Officeランキング