SUBTOTAL（サブトータル関数）の使い方

SUBTOTAL（サブトータル）関数は、表の各小計から合計を出すときや、フィルターを使った表に対して合計値を変動させたりすることができます。

集計は合計（SUM）だけではなく、引数によって指定することができます。

【構文】

＝SUBTOTAL(集計方法, 参照1)

【使用例】

※ ちょっとややこしいで、ゆっくり上から読み解いていってください

• ＝SUBTOTAL(B2:B5,9)
  セルB2～B5までの数値データを合計します。挙動はSUM関数と変わりありませんが、フィルターを設定して、B2とB5だけの表示になった場合、その２セルの合計を出力します。
• ＝SUBTOTAL(B7:B18,9)
  前述の使用例と、使い方は同じです。
• ＝SUBTOTAL(B2:B19,9)
  セルB2～B5の「小計」をSUBTOTAL関数でセルB6に求め、セルB7～B18までの「小計」をSUBTOTAL関数でセルB19に求めた場合、セルB20に入力したこの数式は、SUBTOTAL関数で求めた合計値のみを合算を出します。

[char no=”1″ char=”さえちゃん１”]使用例を読み解くのが難しいのですが、SUBTOTAL関数を知っていると、正確な小計・合計の表を作成することができます。合計は何でもSUM関数で出力しがちなのですが、SUBTOTAL関数を知っていると表の管理が楽になりますよね。[/char]

関数説明

使用例をサンプルデータにすると、このような表を思い描いてください。まず、エリアごとに小計を求めていきます。

SUBTOTAL関数は、[数式タブ]→関数ライブラリの[数学／三角]の中にあります。

まずは第１引数、集計方法を「1～9」の番号で指定します。番号によって、このような関数を使うことができるんですね。一覧をキャプチャしました。

今回は合計を使うので「9」と指定します。合計はよく使うので「9」と覚えてしまってください。

参照１、参照２というのは、SUM関数でいう数値１、数値２と同じで、離れたところをあわせて計算したいときに使用します。

今回は参照１のみ使っていきます。

SUM関数同様、小計を求めました。首都圏Bの小計もSUBTOTAL関数で求めます。

最後に、合計をSUBTOTAL関数で求めます。

ほかの数値データは無視されて、SUBTOTAL関数で出した小計の合計を出すことができました。

また、フィルターをかけて「原宿」を取り除いてみると……

現在見えているセルに足して、数式を再計算処理させることができました。

以上がSUBTOTAL関数の説明になります。

さえちゃんのSUBTOTAL関数ワンポイントアドバイス

[char no=”4″ char=”さえちゃん３”]SUBTOTAL関数は、テーブル機能の中にも含まれています。ただ、テーブル機能は職場全員のExcelスキルがテーブル要素を知っていないと、特別な効果が逆にオペレーション効率を下げてしまうので、使いどころはそういう意味で難しいです。また、テーブルの場合、集計行で使われるSUBTOTAL関数は100番台で出力されます。この違いを説明しておきます。[/char]

テーブル設定をします。

[テーブルデザイン]タブより、集計行にチェックを入れます。

集計行が出てきました。

初期設定では「合計」ですが、アクティブセルを置くとセルの右側に表示されるリストボタンより変更が可能です。

数式では、このようになっています。

参照１は９月のみの合計のため、テーブルに設定した見出し列の合計という意味で、

[９月]

となっております。

SUBTOTAL関数で「9」と「109」はどちらも合計を意味しており、テーブルで出力されるSUBTOTAL関数は、自動的に100番台のものが採用されています。

9と109の違いは、非表示行を含めるか含めないか、です。フィルターの非表示ではなく、通常の非表示をした場合の動きになります。

100番台は、通常の非表示に対しても、フィルターと同じように計算式を連動させます。

単純な「9」にすると、非表示行に対して再計算はされません。

行の非表示はあまり使わないようにしましょう。

なお、デフォルトでは右下のセルのみが自動で出てくるので、それ以外は手動で設定します。オートフィルで一括出力してみました。

集計行を他のカラムでも使うときは参考にされてください。

関数ステータス

関数ライブラリの種類

数学／三角

数式の構文

＝SUBTOTAL(集計方法, 参照1)　

引数ダイアログ

Microsoft 公式サポート

SUBTOTAL関数 – Office サポート

榊 裕次郎

Excel講師

1981年10月生まれのてんびん座、東京都出身。趣味は、旅行と料理とワイン。2024年は、佐賀県に行って「呼子のイカ」を思いっきり食べたいです。

引き続き、青森・秋田・岩手でのお仕事ご依頼、お待ちしております！