バックオフィスから経営を強くする

新規登録
  1. 「マネーフォワード クラウド」
  2. 業務効率化の基礎知識
  3. SUBTOTAL関数とは？使い方や利用シーンをわかりやすく解説
  • 作成日 : 2024年12月27日

SUBTOTAL関数とは？使い方や利用シーンをわかりやすく解説

SUBTOTAL関数は、Excelなどのスプレッドシートソフトで使用できる便利な関数の一つです。この関数は、データの集計を行う際に役立ち、合計や平均などの計算を簡単に実行できます。他の集計関数と異なり、フィルターを適用したデータに対しても正確に計算ができるため、特定の条件に基づいた分析に非常に有用です。本記事では、SUBTOTAL関数の詳しい使い方や活用シーンについて解説していきます。

SUBTOTAL関数の使い方

SUBTOTAL関数は、Excelにおいてデータの合計や平均、カウントなどを求める際に非常に便利な関数です。特にフィルターを使用してデータを抽出する場合に役立ちます。この章では、SUBTOTAL関数の基本的な使い方について解説します。

SUBTOTAL関数の基本構文

SUBTOTAL関数の基本的な構文は以下の通りです：

SUBTOTAL(function_num, 引数1, [引数2], …)

ここで、各部の意味は以下のようになります：

  • function_num: 使用する関数の種類を指定します。合計や平均など、各種統計関数の番号があります。
  • 引数1, 引数2, …: 集計するデータの範囲を指定します。複数の範囲や数値を指定することも可能です。

function_numの指定方法

function_numは、SUBTOTAL関数が実行する操作を示す番号です。以下に代表的な番号を示します：

番号機能
1AVERAGE
2COUNT
3COUNTA
9SUM
101AVERAGE（フィルター無視）
102COUNT（フィルター無視）
109SUM（フィルター無視）

番号を正しく選ぶことで、必要な集計方法を選定できます。フィルターで隠されたデータを含めるかどうかも、選択した番号によって変わります。

SUBTOTAL関数の実際の使い方例

具体例を挙げてSUBTOTAL関数の使い方を見ていきましょう。

  1. セルA1からA10に数値データがあるとします。
  2. これらの合計を求めたい場合、以下のように入力します：
    =SUBTOTAL(9, A1:A10)
  3. フィルターを適用して一部のデータを表示した場合でも、表示されているデータのみが合計されます。

これにより、動的なデータ集計が可能となり、業務での利用が非常に効率的になります。

SUBTOTAL関数の利用シーン

SUBTOTAL関数は、データの集計や分析に非常に便利な機能を提供します。特に、フィルターを使用している場合や、特定の条件に基づいた集計を行いたいときにその効果を発揮します。ここでは、具体的な利用シーンを幾つか紹介します。

データフィルタリング時の利用

SUBTOTAL関数は、データをフィルターした際にのみ可視化されているセルを対象に集計を行うため非常に便利です。これにより、ユーザーは特定の条件に一致するデータだけを簡単に処理できます。

集計の種類による使い分け

集計の種類に応じて、SUBTOTAL関数の引数を使い分けることができます。以下のように、集計方法を指定することが可能です。

  • 1〜11: 平均、合計、最大値、最小値などの基本的な統計情報
  • 101〜111: フィルター後のデータに対する統計情報

このため、データの特性に応じて適切な集計方法を選ぶことが重要です。

データのサマリー作成

SUBTOTAL関数を使用して、複数のデータセットのサマリーを作成することができます。例えば、各商品の売上データを集計し、異なるカテゴリごとの合計を求める場合に便利です。これによって、各カテゴリのパフォーマンスを一目で把握できます。

動的なダッシュボードの作成

SUBTOTAL関数を利用することで、動的なダッシュボードを簡単に作成することができます。ユーザーが特定のフィルターを適用した際に、連動して集計結果が反映されるため、リアルタイムでのデータ分析が可能です。

これらの利用シーンを通じて、SUBTOTAL関数がどのように役立つのかを理解し、実際の業務に活かしていただければと思います。

SUBTOTAL関数とSUM関数の違いと使い分け

Excelでは数式を使ってデータを処理することが容易ですが、特に合計を求める際にはSUBTOTAL関数とSUM関数のどちらを使用すべきか迷うことがあります。それぞれの関数には独自の特徴があり、使用する状況に応じて選択が必要です。ここでは、SUBTOTAL関数とSUM関数の違いを詳しく見ていきましょう。

SUBTOTAL関数の特徴

SUBTOTAL関数は主にデータの集計を行うための関数であり、以下のような特徴があります。

  • フィルタリング機能：フィルタリングや非表示にした行を自動的に除外して、可視のデータのみを集計します。
  • 集計方法の選択：合計や平均、最大値、最小値など、さまざまな集計方法から選ぶことができるため、柔軟性があります。

SUM関数の特徴

SUM関数は、選択したセル範囲の数値を単純に合計するのが得意です。こちらの特徴も確認しておきましょう。

  • 単純明快な集計：選んだ範囲内の全ての数値を加算するため、使い方が非常にシンプルです。
  • フィルタリング機能なし：非表示の行やフィルターで隠したデータも含めて集計されるため、総合的な合計値が欲しい場合に使います。

使い分けのポイント

この二つの関数をどのように使い分けるかは、求めるデータの性質や状況に依存します。具体的には以下のように考えられます。

条件推奨関数
フィルタリングされたデータの集計が必要な場合SUBTOTAL関数
全データの総合的な合計が必要な場合SUM関数
特定の集計方法（平均、最大、最小など）を指定したい場合SUBTOTAL関数

SUBTOTAL関数で平均を求める方法

SUBTOTAL関数は、データを集計する際に非常に便利な関数ですが、特に平均を求める際にも有効な手段です。この章では、SUBTOTAL関数を使って平均を求める具体的な方法について詳しく解説します。

平均を求めるための具体例

ここでは、具体的な例を通じて、SUBTOTAL関数を使用した平均の求め方を見ていきます。

例えば、以下のようなデータがあるとします。

商品売上
商品A100
商品B200
商品C150

このデータから売上の平均を求める場合、次のようにSUBTOTAL関数を使用します。

=SUBTOTAL(1, B2:B4)

ここで、範囲B2:B4は売上データを含むセル範囲です。この関数では、値を非表示にした場合でも集計されることなく、フィルタリングされたデータの平均を計算します。

関数番号の選択

SUBTOTAL関数には、関数番号として「1」と「101」を使うことができます。以下にその違いを示します。

  • 1：AVERAGE。フィルタリングされたデータの平均を計算します。
  • 101：AVERAGE。ただし、非表示の行を含めた全データの平均を計算します。

そのため、データの状況に応じて適切な関数番号を選択することが重要です。一般的には、フィルタリングをかけている場合は「1」を使用することが推奨されます。

SUBTOTAL関数とCOUNTIF関数を組み合わせる応用

SUBTOTAL関数は subtotals を計算するための非常に便利なツールですが、COUNTIF関数と組み合わせることで、さらに強力な分析が可能になります。この章では、SUBTOTAL関数とCOUNTIF関数を使ってデータの集計や条件付きのカウントを行う方法について詳しく解説します。

COUNTIF関数の基本的な利用法

次に、COUNTIF関数の基本的な使い方を確認しておきましょう。この関数は、指定した条件を満たすセルの数をカウントします。形式は以下の通りです。

=COUNTIF(範囲, 条件)

範囲にはセルの範囲が入り、条件にはカウントしたい値や条件を指定します。例えば、特定の文字列を持つセルの数を数えることができます。

SUBTOTALとCOUNTIFの組み合わせ

では、SUBTOTAL関数とCOUNTIF関数を組み合わせると、どのような利点があるのでしょうか？以下に、具体的な例を通じてその利用法を説明します。

例: フィルターされたデータの集計

フィルターを適用したデータリストがあり、特定の条件を満たす行の合計とカウントを同時に求めたいとします。

例として、売上データの一覧がある場合、以下のような数式を使用できます。

  • 合計: =SUBTOTAL(9, B2:B100) （B列の売上データの合計）
  • 条件付きカウント: =COUNTIF(B2:B100, “>1000”) （売上が1000を超える件数のカウント）

ここで、SUBTOTAL関数とCOUNTIF関数を組み合わせることで、データセット全体ではなく、フィルター後のデータだけを対象に計算を行うことが可能です。

実際のシートでの活用方法

Excelシートを使用して、実際にSUBTOTAL関数とCOUNTIF関数を組み合わせてみましょう。以下の手順でフィルターを適用し、データを分析します。

  1. 売上データを含む範囲を選択し、フィルターを設定します。
  2. 特定の条件（例えば、地域や売上額に基づく）でデータをフィルターします。
  3. フィルターされた範囲を基に、上記のSUBTOTALとCOUNTIFの数式を適用します。

これにより、リアルタイムで条件に適したデータの集計が行え、効率的に分析が進められます。


※ 掲載している情報は記事更新時点のものです。

  • 監修：青井 真吾（AOIS Consulting株式会社代表取締役）

    大学卒業後はIT企業に入社。システムエンジニアとして大手企業向けのERPシステム開発を経験。その後ITコンサルタントとして独立。人材派遣会社の基幹システムの開発、不動産会社の商業施設での販促システムの導入、自動車メーカーでコネクティッドカー開発のプロジェクト管理、SIerでのSalesforceの導入、ファッション業界の企業でのDX推進プロジェクトを経験。現在はAOIS Consulting株式会社を設立し、コンサルティングの傍らIT記事の監修および執筆を手掛ける。

※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容（テンプレートを含む）の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。

関連記事

  1. 「マネーフォワード クラウド」
  2. 業務効率化の基礎知識
  3. SUBTOTAL関数とは？使い方や利用シーンをわかりやすく解説