- ホーム >
- FMVサポート >
- Q&A・パソコン活用情報をコーナーから探す >
- エクセル関数塾 >
- 表の間の小計を一発で合計できる！SUBTOTAL関数で小計や合計を求めよう
第27回表の間の小計を一発で合計できる！SUBTOTAL関数で小計や合計を求めよう
塾長、先日の関数塾では大変学びが多く、仕事にプライベートに数多く参考にさせていただきました。基本レベルは習得できたと思うのですが、またお知恵を拝借したいと思い、足を運びました。
次の表をご覧ください。SUM関数やAVERAGE関数は使えても苦戦しているのです。
クラスごとの数値の合計や平均などを集計したいのですが、関数を利用して、合計や平均などを瞬時に求める方法はありませんでしょうか？
わしが、エクセル関数塾塾長！関 数之進（せきかずのしん）である！
関数を使いこなせるようになってきたようじゃな！これからますます便利な関数を紹介していくぞ。しっかり学んでいきなさい。
さて、表を見せてごらん？この表なら「SUBTOTAL関数」を使って、小計や合計、その他の集計結果を簡単に求められるぞ。その技を極意書を使って説明しよう！
関数塾 極意書
SUBTOTAL関数で小計や合計を求めてみよう
エクセルでは、SUBTOTAL（サブトータル）関数を使って、合計や平均などの集計を行うことができます。
SUM関数で複数の小計から合計を求める場合、小計を足し合わせるか、SUM関数の引数で各小計のセルを選択しなければならず、小計の数が多くなればなるほどめんどうな操作となります。
SUBTOTAL関数を利用して小計を求めれば、単純な式で簡単に合計が求められます。
ここでは、SUBTOTAL関数の基本的な書式と機能について紹介します。
SUBTOTAL関数の書式
SUBTOTAL関数は次のように入力します。
=SUBTOTAL（集計方法,参照範囲）
SUBTOTAL関数は、参照範囲内のセルの値を「集計方法」の計算方法にしたがって集計します。
SUBTOTAL関数は、［関数の挿入］ボタンをクリックして［関数の分類］で［数学/三角］を選択すると、関数が簡単に入力できます。
また、Excel2007と2010では関数ライブラリからも選択できます。
今回の例では、C列にクラスごとの合計欄があり、最後に総合計を求めるようになっています。次のように関数を入力してみましょう。
SUBTOTAL関数を入力する、セルC9をアクティブにし、［数式］タブの［関数の挿入］をクリックします。
［関数の分類］で「数学/三角」を選択し、［関数名］で「SUBTOTAL」を選択して、［OK］ボタンをクリックします。
［集計方法］に「9」、［参照1］に「C4﹕C8」を入力し、［OK］ボタンをクリックします。
クラスAの合計点が求められました。
同じ方法で、クラスBの合計点を求めます。セルC15をアクティブにし、［集計方法］に「9」、［参照1］に「C10﹕C14」を指定します。
同様の方法で、セルC21にクラスCの合計点を求めます。
最後に、総合計を求めます。セルC22をアクティブにして、SUBTOTAL関数を入力します。［集計方法］に「9」、［参照1］に「C4﹕C21」を指定します。
SUBTOTALで求めたクラスごとの合計を合わせた、総合計がC22に求められます。
ステップアップ：集計方法って何？
SUBTOTAL関数では、引数に「集計方法」を指定します。合計の場合は「9」ですが、それ以外にも、次のような集計方法があります。
| 引数 | 1 | 2 | 3 | 4 | 5 | 9 |
|---|---|---|---|---|---|---|
| 集計方法 | 平均 | 数値の個数 | データの個数 | 最大値 | 最小値 | 合計 |
例えば、クラスの平均点を出したいときは、SUM関数を入力している場合は、すべての小計セルの関数をAVERAGE関数に変更しなければいけませんが、SUBTOTAL関数の場合、各小計の「引数」だけを「9」（合計）から「1」（平均）に変更するだけです。
SUBTOTAL関数で、各クラスの平均値を求めてみます。
セルC9をアクティブにして、数式バーをクリックします。
引数の「9」を［Delete］キーなどで削除し、「1」と入力して、［Enter］キーを押します。
クラスAの平均値が求められました。
クラスB、クラスCおよび全クラスのSUBTOTAL関数の引数を「9」から「1」に変更します。
各クラスの平均点、および全体の平均点が求められました。
SUBTOTAL関数は、表の間に小計が複数含まれ、最後に総合計を求めるような表を作成する場合に便利な関数です。小計を求めるときもSUBTOTAL関数を利用するのがポイントになります。引数を変更すれば、平均値や最大値なども簡単に計算できるので値の集計に利用すると便利な関数です。
問題
次のような売上一覧を作成してみましょう。
次の表は、売上の一覧です。商品名ごとに小計を求め、さらに小計を足し合わせた総合計を求めてみます。
サンプルをご利用になる場合は、パソコンにて本ページをご覧ください。
問題に必要なエクセルのファイルをダウンロードしてください。
ダウンロードするには、下記のファイル名を右クリックして、メニューから「対象をファイルに保存」を選択してください。
本講座では、ファイルの保存先を《ドキュメント》としています。
ダウンロード方法がわからない場合は、下記ページをご参照ください。
