参照範囲の選択が簡単に！SUBTOTAL関数の便利な使い方

「参照範囲を一つ一つ確かめながら入力するのが大変…」と思ったことはありませんか？そこで、Excelと生成AIの組み合わせで、データ分析の効率を高める方法をご紹介します。
今回は「追加計算や部分集計が結構多い」という方に特にピッタリとくる、ExcelのSUBTOTAL関数をピックアップし、具体例を交えて解説します。

SUBTOTAL関数とは？

SUBTOTAL関数はExcelで部分的な集計を行うのに特化した関数です。少し簡単にいえば、「表示されているデータのみを参照した集計が可能」になるのが特徴です。この関数を使えば、フィルター操作やソート機能と互換性よく効率的な分析ができます。

SUBTOTAL関数の基本書式は以下の通りです。

=SUBTOTAL(集計方法, 参照)

copy

集計方法：適用する集計方法を選択します。例：1は平均（AVERAGE）9は合計（SUM）を表示。
参照：集計したいデータの範囲を選びます。

集計が簡単になるSUBTOTAL関数の使い方

例として、店舗ごとに製品の売上を集計したデータを考えてみましょう。

例：店舗ごとの売り上げと合計売上の表を作成

目的
上記の売上表の小計と合計を計算する。

手順
C4には次の数式を入力します。

=SUBTOTAL(9,C2:C3)

copy

C7には次の数式を入力します。

=SUBTOTAL(9,C5:C6)

copy

C8には次の数式を入力します。

=SUBTOTAL(9,C2:C7)

copy

結果
店舗ごとの売り上げと合計売上を計算できました。合計売上では小計も参照範囲に含まれていますが、SUBTOTAL関数による小計の計算結果であるため、計算に含めずに合計金額を計算出来ています。

SUBTOTAL関数の便利な特性

例で作成した売上表をテーブルにして（挿入タブのテーブルをクリックして、A1:C7を範囲選択）、店舗や製品名でフィルタをかけると表示されているデータに合わせて再計算されます。

SUBTOTALを生成AIと連携するコツ

生成AIを使用して、SUBTOTAL関数を付加したExcelファイルをサクッと作成することも可能です。これは、Excelのマクロを使っても良し、Pythonのopenpyxlを利用するも良しです。他にも、データ解析を詳しい解説記事やChatGPTなどに書かせる方法などがあります。

まとめ

SUBTOTAL関数は、表示されているデータのみを集計できるため、消し込みが多いデータに有用。
部分の集計を見えやすくするので、経営の意思決定を助ける。
生成AIを使うと、大量のデータの初期設定が高速化できる。

生成AI活用やExcel業務の効率化についてのご相談は、ぜひビジー・ビーにお任せください！

当社では、生成AIを活用したコメントデータ分析や、Excel業務の効率化を支援するツールやソリューションを提供しています。データ活用のプロフェッショナルが、貴社のニーズに合わせた最適な方法をご提案します。

ご質問、ご相談は、以下のフォームからお問い合わせください。

本記事は、[データ分析ラボ]の許諾を得て転載しています。この記事以外にも生成AI・Excel・データ分析など多くの興味深い記事を掲載しています。ぜひ[データ分析ラボ]を訪れて、幅広い情報をお楽しみください。[https://note.com/dataanalysislabo/]