今回のテーマはエクセルの抽出データだけを対象に計算するSUBTOTAL関数。SUBTOTAL関数は集計方法の条件を指定することで、さまざまな集計を求めることができます。合計の他にも平均や積もひとつのSUBTOTAL関数で求めることができます。とくに小計やオートフィルターを使うときは、SUM関数やSUMIF関数ではなくSUBTOTAL関数を使うと便利です。本記事では、抽出データだけを対象に計算をするSUBTOTAL関数の使い方をご紹介します。

抽出したデータだけの計算が求められない

データ抽出前に入力した計算式。

データを抽出すると正しい計算結果が求められない。

データ抽出前

下図表のようにデータ表があります。

表は「フィルター機能」を有効にしています。

 

※Excelで作られたデータリストを特定の条件で絞り込んで表示できる「フィルター機能」は、大量のデータから、条件に合うものだけを一覧化して洗い出したいときに便利です。

 

平均単価と売上金額の合計が表示されています。

平均単価は「AVERAGE関数」、売上は「SUM関数」を使って計算されています。

データ抽出後

ここで、データを抽出してみましょう。

売上日を「11月6日」で抽出します。

 

下図表のように、「11月6日」の売上日でデータが抽出された状態です。

 

ところが、

  • 抽出したデータだけの集計結果は求められません。

 

抽出だけの集計をするには、

  • 「=SUBTOTAL関数」を使います。

SUBTOTAL関数の構成

SUBTOTAL関数の構成はこちら。↓↓↓

  • ＝SUBTOTAL（集計方法,集計範囲）

 

SUBTOTAL関数は「集計方法」を指定することで下記の集計をすることができる点が特徴です。

  • 1-平均（AVERAGE関数）
  • 2-数値の個数（COUNT関数）
  • 3-データの個数（COUNTA関数）
  • 4-最大値（MAX関数）
  • 5-最小値（MIN関数）
  • 6-積（PRODUCT関数）
  • 7-不偏標準偏差（STDEVS関数）
  • 8-標本偏標準偏差（STDEVP関数）
  • 9-合計（SUM関数）
  • 10-不偏分散（VARS関数）
  • 11-標準分散（VARP関数）

SUBTOTAL関数を使って平均値を求める

SUBTOTAL関数を入力して「平均単価」を算出してみましょう。

関数名を入力する

SUBTOTAL関数の構成はこちら。↓↓↓

  • ＝SUBTOTAL（集計方法,集計範囲）

 

C3セルに「=SU」と入力する。

「S」ではじまる関数が表示される中、「SUBTOTAL」を選択する。

  • 「Tab」キーを押す。

引数「集計方法」を指定する

「=SUBTOTAL(」と入力され、集計方法が表示されます。

 

「1-AVERAGE」を選択する。

  • 「Tab」キーを押す。

カンマを入力する

「=SUBTOTAL(1」と入力されます。

 

「,」（カンマ）を入力する。

  • =SUBTOTAL(1,

引数「集計範囲」を入力する

「=SUBTOTAL(1,」と入力されます。

 

次の引数は「集計範囲」です。

「C6:C15」をドラッグして範囲指定し「Enter」キーを押す。

  • =SUBTOTAL(1,C6:C15

平均単価が求められる

C3セルには「=SUBTOTAL(1,C6:C15）」と入力されました。

 

集計範囲の平均値が求められました。

SUBTOTAL関数を使って合計を求める

SUBTOTAL関数を入力して「売上合計」を算出してみましょう。

関数名を入力する

SUBTOTAL関数の構成の確認。

  • ＝SUBTOTAL（集計方法,集計範囲）

 

E3セルに「=SU」と入力する。

「S」ではじまる関数が表示される中、「SUBTOTAL」を選択する。

  • 「Tab」キーを押す。

引数「集計方法」を指定する

「=SUBTOTAL(」と入力され、集計方法が表示されます。

 

「9-SUM」を選択する。

  • 「Tab」キーを押す。

カンマを入力する

「=SUBTOTAL(9」と入力されます。

 

「,」（カンマ）を入力する。

  • =SUBTOTAL(9,

引数「集計範囲」を入力する

「=SUBTOTAL(9,」と入力されます。

 

次の引数は「集計範囲」です。

「E6:E15」をドラッグして範囲指定し「Enter」キーを押す。

  • =SUBTOTAL(9,E6:E15

平均単価が求められる

C3セルには「=SUBTOTAL(9,E6:E15）」と入力されます。

 

集計範囲の合計値が求められました。

抽出したデータだけを集計する

フィルタ―機能を使ってデータを抽出、抽出したデータの集計結果を確認してみましょう。

フィルタ―をつける

フィルタ―機能を使ってフィルタ―をつけます。

 

カーソルが表の任意のセルにあることを確認します。

① 「データ」タブをクリックする
② 「フィルター」をクリックする
③  表にフィルターが表示される

フィルタ―を使って抽出する

ここでは「セット名」の中から「紅茶セット」を抽出します。

① フィルターの「▼」をクリック
② テキストフィルターから「紅茶セット」をチェック
③ 「OK」を押す

抽出結果を確認する

抽出した「紅茶セット」だけが表示されます。

「平均単価」、「売上合計」とも、

  • 抽出したデータだけの計算結果が求められていることが確認できます。

読者の行動

小計が含まれる表を計算するときのSUBTOTAL関数の使い方はこちら。

この機会に、SUBTOTAL関数習得のヒントに。

http://marus.info/excel-subtotal-9-marusblog/

今回のまとめ

というわけで、今回は以上です。

 

小計が含まれる表を計算するときに便利なSUBTOTAL関数。

 

また、SUBTOTAL関数のもうひとつの使い方は、

  • 抽出だけの集計をするに「=SUBTOTAL関数」を使います。

 

本記事では、平均（AVERAGE関数）と合計（SUM関数）の集計をSUBTOTAL関数を使って計算。

実際に入力方法と抽出したデータの集計結果を解説しました。

 

この機会に、特徴的で便利なSUBTOTAL関数習得に役立ててください。

 

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

最後まで読んでくださりありがとうございます。

少しでもヒントになればうれしく思います。