漫才で覚える Excel SUBTOTAL関数のポイント
「どうもーどうも みるくブラザーズですー」
「お願いしますー ありがとうございますー」
げんじ「あー ありがとうございますー ねっ 今 四次元ポケット をいただきましたけどもね」
ボケ＆げんじ「ありがとうございますー」
げんじ「こんなん なんぼあっても良いですからね」
ゆめた「いきなりですけどね うちのオカンがね 好きな Excel関数 があるらしいんやけど」
げんじ「そーなんや」
ゆめた「その名前をちょっと忘れたらしくてね」
げんじ「好きなExcel関数を忘れて、どうなってんの。なんか特徴言うてなかった？」
ゆめた「特徴は フィルタ適用中のデータだけで計算できるって言うねんな」
げんじ「おー SUBTOTAL関数やないかい その特徴はもう完全に SUBTOTAL関数 やがな」
ゆめた「 SUBTOTAL関数 なぁ。いや俺も SUBTOTAL関数 と思うてんけどな」
げんじ「いやそうやろ？」
ゆめた「オカンが言うには 空を飛べる だって言うねんな」
げんじ「あー ほな SUBTOTAL関数 と違うかぁ SUBTOTAL関数では、空は飛べへんし、そんな魔法のような機能はないわな。惜しいけど全然違う話や」
ゆめた「そやねん」
げんじ「 SUBTOTAL関数では、フィルタリングされたデータに対して色々な計算ができるけど、未来を予知したり空を飛ぶことはできへんからな」
ゆめた「そやねんな」
げんじ「あれほなもう一度詳しく教えてくれる？」
ゆめた「 合計から平均、最大値まで色々な計算が可能 らしいねん」
げんじ「 SUBTOTAL関数 やないかい SUBTOTAL関数は、その通り合計や平均など様々な計算を一つの関数でこなせるんや それにボケてみせて？」
ゆめた「まあねー」
げんじ「 SUBTOTAL関数で色々計算できるけど、うちのオカンが使えるかどうかは別問題やな」
ゆめた「分からへんねんでも」
げんじ「何が分からへんのこれで」
ゆめた「俺も SUBTOTAL関数 と思うてんけどな」
げんじ「そうやろ」
ゆめた「オカンが言うには 未来予知ができる って言うねんな」
げんじ「ほな SUBTOTAL関数 ちゃうやないかい SUBTOTAL関数で未来予知なんかできひんし、それは完全に別の話や 困ったときにSUBTOTAL関数を使っても、未来は変えられへんがな」
ゆめた「そやねんそやねん」
げんじ「 SUBTOTAL関数 ちゃうがな ほな もうちょっとなんか言ってなかった？」
ゆめた「 隠れている行のデータを無視して計算する らしいねん」
げんじ「 SUBTOTAL関数 やないかい SUBTOTAL関数は確かに隠れた行を無視して計算できるんや。これで分かるやろ」
ゆめた「分からへんねんだから」
げんじ「なんで分からへんのこれで」
ゆめた「俺も SUBTOTAL関数 と思うてんけどな」
げんじ「そうやろ」
ゆめた「オカンが言うには SUBTOTAL関数 ではないって言うねん」
げんじ「ほな SUBTOTAL関数 ちゃうやないかい オカンが SUBTOTAL関数 ではないと言うんやから SUBTOTAL関数 ちゃうがな」
ゆめた「そやねん」
げんじ「先ゆえよ 最高のボケをひとつ選んでくれてん」
ゆめた「んでオトンが言うにはな」
げんじ「オトン？」
ゆめた「計算機ちゃうか？って言うねん」
げんじ「いや絶対ちゃうやろ もうええわー」
はじめに
- SUBTOTAL関数はExcelで非常に便利な集計ツールです。
- リストやデータベース内のサブトータルを計算でき、フィルターされたデータにも対応しています。
- この関数を使いこなせば、データ分析やレポート作成の効率が大幅に向上します。
関数の概要と一般的な使い方の例
数字で集計方法をしていします。合計は9です。
左の2つの画像のように、SUBTOTAL関数には11個の集計メニューがあります。
- SUBTOTAL関数は、指定した範囲の合計、平均、最大値、最小値などを計算するために使用します。
- 例：
SUBTOTAL(関数番号, 範囲)。関数番号は1～111まで、集計の種類を指定します。 - サンプルコード：
SUBTOTAL(9, A2:A100)この例では、A2からA100までの合計を計算します。 - 出力結果：サンプルコードの実行結果として、指定した範囲の数値の合計が表示されます。
なぜこの関数を学ぶのか？
- データがフィルターされたときに、隠れた行を除外して集計を行いたい場合に非常に役立ちます。
- 複数の集計を一つのシートに簡単に表示できるため、データ分析が容易になります。
- 「月次レポートを作成しているが、特定の条件に合致するデータのみで合計や平均を出したい」というシーンで活躍します。
一般的な書き方のサンプル
- 合計を求める：
SUBTOTAL(9, A2:A100)→ A2:A100の合計を計算します。 - 平均を求める：
SUBTOTAL(1, A2:A100)→ A2:A100の平均を計算します。 - 最大値を求める：
SUBTOTAL(4, A2:A100)→ A2:A100の最大値を見つけます。
SUBTOTAL関数を使用した簡単な例：商品売上データのテーブル
| 商品名 | 売上日 | 売上金額 |
|---|---|---|
| 商品A | 2024/01/01 | 500 |
| 商品B | 2024/01/02 | 300 |
| 商品A | 2024/01/03 | 450 |
| 商品C | 2024/01/04 | 600 |
| 商品B | 2024/01/05 | 350 |
| 商品A | 2024/01/06 | 700 |
このテーブルには、複数の商品の売上データが含まれています。各商品の売上金額の合計を求めたい場合、特に商品Aの売上のみを対象として計算したいとします。
SUBTOTAL関数の使用
商品名の列をフィルタリングして、商品Aのデータのみを表示させた状態で、SUBTOTAL関数を使用して売上金額の合計を計算します。
- まず、データのリストにフィルターを適用します。
- 商品名で「商品A」のみをフィルタリングして表示します。
- 別のセルに、以下のSUBTOTAL関数を入力します。scssCopy code
=SUBTOTAL(9, C2:C7)
ここで、9は合計を求める操作を指定し、C2:C7は売上金額が入力されている範囲を指します。
結果
フィルタリングされたデータ（商品Aのみの売上）の合計が計算され、SUBTOTAL関数を入力したセルに表示されます。この例では、商品Aの売上金額の合計が1650（500 + 450 + 700）となります。
このようにSUBTOTAL関数を使用すると、フィルタリングされたデータに基づいて動的に集計を行うことができ、分析やレポート作成の作業を効率的に進めることが可能です。
関数の基本構文
- SUBTOTAL関数の基本的な構文は
SUBTOTAL(関数番号, 範囲1, [範囲2], ...)です。関数番号によって計算の種類（合計、平均、最大値など）を選択します。 - この関数はフィルタリングされたデータや非表示の行を無視して計算を行う点が特徴です。そのため、データを分析する際に非常に便利です。
関数の定義と主な引数
- 関数番号: 集計の種類を指定します。例えば、1は平均、9は合計を計算します。101～111の範囲を使用すると、非表示の行を含む計算が可能です。
- 範囲: 集計を行うデータの範囲を指定します。
サンプルコードと出力結果:
- データの合計（非表示行を除く）:
SUBTOTAL(9, A2:A100)このコードは、A2からA100までの合計を計算し、非表示の行は無視します。 - データの平均（非表示行を除く）:
SUBTOTAL(1, A2:A100)このコードは、A2からA100までの平均を計算し、非表示の行は無視します。 - 最小値の検出（非表示行を除く）:
SUBTOTAL(5, A2:A100)このコードは、A2からA100までの最小値を見つけ出します。
これらのサンプルコードを通じて、SUBTOTAL関数がどのように使われるか、そしてその強力な機能をより深く理解することができます。
具体的な使用例
基本的な使用方法のデモ
- データフィルタリング時の合計計算: データがフィルタリングされたとき、非表示になったデータを除外して合計を計算します。例えば、特定の条件に一致する商品の売上合計を求めたい場合、
SUBTOTAL(9, B2:B100)を使用して、B2:B100範囲内のフィルタリングされたデータの合計を計算できます。
一般的な計算や操作の例
- 条件付き平均計算: 条件を満たすデータのみを対象に平均値を計算したい場合、まず対象データをフィルタリングし、
SUBTOTAL(1, C2:C100)を使用して、C2:C100範囲のフィルタリングされたデータの平均を計算します。 - 最大値・最小値の検出: データセット内で最大または最小の値を見つけたいが、特定の条件を満たすデータに限定したい場合、データをフィルタリングした上で
SUBTOTAL(4, D2:D100)やSUBTOTAL(5, D2:D100)を使い、それぞれ最大値と最小値を求めることができます。 - 表示データの個数カウント: フィルタリングにより表示されているデータの件数を知りたい場合、
SUBTOTAL(3, E2:E100)を使用して、E2:E100範囲に表示されているデータの数をカウントします。
これらの例は、SUBTOTAL関数がデータ分析においていかに柔軟に使用できるかを示しています。フィルタリングされたデータに対する操作を効率的に行うことができ、データ集計作業の精度と速度を向上させます。
初歩的なテクニック
- 簡単な使い方やコツ: SUBTOTAL関数を使用する際は、関数番号を正しく選択することが重要です。1〜11は非表示の行を無視し、101〜111では非表示の行を含めた計算を行います。使用するデータがどのように表示されているかに応じて適切な関数番号を選びましょう。
- 他の基本関数との組み合わせ: SUBTOTAL関数は、フィルタリングされたデータに対して動作するため、フィルター機能と組み合わせて使うことで、よりダイナミックなデータ分析が可能になります。
- 相性の良い他の関数をリストで表示:
- IF関数: 条件に基づいた計算を行いたい場合に便利です。
- SUMIF関数: 特定の条件を満たすデータのみを合計したい場合に使用します。
- AVERAGEIF関数: 特定の条件を満たすデータの平均を計算する際に役立ちます。
- COUNTIF関数: 特定の条件を満たすセルの数をカウントするのに適しています。
これらの初歩的なテクニックと関数の組み合わせを理解することで、Excelでのデータ処理がより効率的かつ効果的になります。
高等的なテクニック
- 複雑な条件やデータ構造との連携サンプルと出力結果を1つ: データが複数の条件に基づいてフィルタリングされている場合、SUBTOTAL関数を使って特定の条件を満たすデータのみから平均値を計算することができます。例えば、
SUBTOTAL(1, A2:A100)を使用し、フィルタリングされた範囲の平均を求めることができます。この技術は、データセット内の特定のグループに焦点を当てた分析に役立ちます。 - 他の高度な関数との組み合わせアイデアサンプルコードを3つ提示:
- SUMPRODUCTとの組み合わせ: 条件に一致するデータの合計を計算する際に、SUMPRODUCT関数を使用して重み付けを行い、その結果に基づいてSUBTOTALで最終的な集計を行います。
=SUBTOTAL(9, SUMPRODUCT((条件範囲="条件")*(値範囲), 値範囲)) - INDEXとMATCHの組み合わせ: 特定の条件を満たす行を見つけ出し、その行のデータに基づいてSUBTOTAL関数で集計を行います。
=SUBTOTAL(9, INDEX(データ範囲, MATCH("条件", 条件範囲, 0), 1)) - ARRAYFORMULAの使用: 複数の条件に基づく集計を行いたい場合、ARRAYFORMULAを使用して一度に複数の計算を実行し、その結果をSUBTOTALで集計します。
=SUBTOTAL(9, ARRAYFORMULA(IF(条件範囲="条件", 値範囲, 0)))
- SUMPRODUCTとの組み合わせ: 条件に一致するデータの合計を計算する際に、SUMPRODUCT関数を使用して重み付けを行い、その結果に基づいてSUBTOTALで最終的な集計を行います。
これらの高等的なテクニックは、Excelのデータ分析機能を最大限に活用するための方法を提供します。複雑なデータセットや特定の分析要件に対応するために、これらのテクニックを適切に組み合わせて使用することができます。
便利なシーンでの事例
- ビジネスでの実用的なケーススタディ:
- 売上データの月次集計: 月ごとに異なる商品やサービスの売上をフィルターし、SUBTOTAL関数を使ってそれぞれの合計や平均を計算。これにより、特定の月のパフォーマンスを迅速に評価し、戦略を調整することが可能になります。
- 従業員のパフォーマンス追跡: 従業員ごとに設定されたKPI（重要業績評価指標）を元に、期間ごとの達成度をSUBTOTAL関数を使って集計。個々のパフォーマンスを可視化し、必要に応じてフィードバックやトレーニングを行うための基礎データとして活用。
- 学業での実用的なケーススタディ:
- 成績データの分析: 学生ごと、科目ごとの成績データをフィルタリングして、SUBTOTAL関数で平均点、最高点、最低点を計算。これにより、教育プログラムの有効性を評価したり、特定の学生や科目に対するサポートが必要かどうかを判断します。
- タイムセーブや効率向上の具体的な例:
- レポート作成の時間短縮: 定期的なレポート作成作業で、データを手動で集計する代わりにSUBTOTAL関数を活用することで、計算作業の自動化が可能になり、作業時間を大幅に削減できます。
- 分析の精度向上: データをフィルタリングした際に、フィルタリングされたデータに基づいて自動的に集計が行われるため、集計ミスを防ぎ、分析の精度を向上させることができます。
これらの事例は、SUBTOTAL関数がビジネスや学業のさまざまなシーンでどのように役立つかを示しています。時間の節約、効率の向上、そして分析の精度向上は、この関数を学ぶことの大きなメリットです。
注意点
- 使用する際の落とし穴や制約:
- SUBTOTAL関数は非表示の行を無視して計算を行いますが、セルがフィルターで非表示になっている場合のみです。手動で行を隠した場合は、関数番号101～111を使用して計算する必要があります。
- SUBTOTAL関数内で使用する範囲に他のSUBTOTAL関数が含まれていると、その関数の結果も集計対象になり得るため、重複計算に注意する必要があります。
- めちゃくちゃよくやりがちなミス・ERRORが出る例:
- 間違った関数番号を使用すること。例えば、合計を求めたい場合に関数番号1（平均）を使用してしまうと、期待した結果が得られません。
- 範囲指定の誤り。範囲が不適切であるか、期待した範囲を正しく指定していない場合、エラーが発生することがあります。
- 類似の関数や代替の関数との違い:
- SUM、AVERAGE、MAX、MINなどの基本的な集計関数も似たような結果を提供しますが、これらはフィルタリングされたデータや非表示の行を無視する機能はありません。SUBTOTALはこの点でユニークです。
- AGGREGATE関数はSUBTOTAL関数と似た機能を提供しますが、より多くの集計方法と、エラー値や隠れたセルを無視するオプションを提供します。AGGREGATEはSUBTOTALの拡張版と考えることができますが、使用する関数やオプションによってはSUBTOTALより複雑になる場合があります。
これらの注意点を理解し、適切に対処することで、SUBTOTAL関数を効果的に使用し、エラーを避けることができます。
使いこなし例
- 悩める登場人物例: 山田さんは中小企業の営業マネージャーで、毎月の売上報告と分析を担当しています。しかし、Excelでのデータ集計に時間がかかりすぎて、他の重要な業務に十分な時間を割けていません。
- 具体的な悩み: 山田さんは、売上データがフィルタリングされたときに、非表示になったデータを除外して正確な合計や平均を計算したいと考えています。また、特定の条件を満たすデータのみを迅速に分析したいと思っていますが、複雑な関数を使うことに抵抗があります。
- 使いこなせることによるビフォーアフターのストーリー:
- Before: 従来、山田さんはExcelの基本的なSUMやAVERAGE関数を使ってデータ集計を行っていました。しかし、これらの方法ではフィルタリングされたデータのみを対象にした計算ができず、手動でデータを調整する必要がありました。これにより、エラーが発生しやすく、集計作業に多くの時間を要していました。
- After: SUBTOTAL関数の使い方を学んだことで、山田さんはフィルタリングされたデータのみを対象にした正確な集計が簡単にできるようになりました。特定の条件に基づくデータ分析が容易になり、報告書の作成時間が大幅に短縮されました。これにより、山田さんは他の業務にもっと時間を割くことができるようになり、業務の効率が全体的に向上しました。
このストーリーは、Excelの集計関数を使いこなすことで、仕事の効率化だけでなく、職場での立場や精神的な健康にもプラスの影響を与えることができることを示しています。
