Excelを使っていると、「合計を出したい」「平均を求めたい」といった集計作業が頻繁にありますよね。しかし、SUMやAVERAGEなどの基本関数だけでは、フィルターをかけた場合や小計を含めた場合に正確な集計が難しいことがあります。そんな時に役立つのが、SUBTOTAL関数とAGGREGATE関数です。これらの関数を使いこなすことで、より柔軟で正確な集計が可能になります。
SUBTOTAL関数の基本と活用法
Excelのイメージ
SUBTOTAL関数とは？
SUBTOTAL関数は、Excelにおいて合計や平均、カウントなどの集計を行う関数です。特に、フィルターを使用してデータを抽出した際に、表示されているデータのみを集計することができるため、非常に便利です。
SUBTOTAL関数の書式と使用例
SUBTOTAL関数の基本的な書式は以下の通りです
=SUBTOTAL(集計方法, 範囲)
ここで、「集計方法」は1～11または101～111の番号で指定します。例えば、合計を求める場合は「9」、平均を求める場合は「1」を指定します。後者の「101～111」は、非表示の行を集計対象から除外する場合に使用します。
例えば、セルA1からA10の合計を求める場合、以下のように入力します
=SUBTOTAL(9, A1:A10)
この関数は、フィルターで非表示にした行を自動的に除外して集計を行います。
SUBTOTAL関数の注意点
フィルターとの連携フィルターをかけると、表示されているデータのみを集計対象とするため、非表示のデータは自動的に除外されます。
小計の無視SUBTOTAL関数は、集計範囲内に他のSUBTOTAL関数が含まれている場合、その小計を集計から除外します。これにより、重複した集計を防ぐことができます。
AGGREGATE関数の特徴と活用法
AGGREGATE関数とは？
AGGREGATE関数は、SUBTOTAL関数の上位互換として、より多機能な集計を提供する関数です。非表示の行やエラー値を無視して集計を行うことができ、さらに順位や分位数などの高度な統計処理も可能です。
AGGREGATE関数の書式と使用例
AGGREGATE関数の基本的な書式は以下の通りです
=AGGREGATE(集計方法, オプション, 範囲, , ...)
* 「集計方法」1～19の番号で指定します。例えば、合計を求める場合は「9」、平均を求める場合は「1」を指定します。
* 「オプション」集計対象から除外するデータを指定します。例えば、「6」を指定するとエラー値を無視します。
例えば、セルA1からA10の合計を求め、エラー値を無視する場合、以下のように入力します
=AGGREGATE(9, 6, A1:A10)
この関数は、エラー値を含むセルを自動的に除外して合計を計算します。
AGGREGATE関数の特徴
エラー値の無視エラー値を含むセルを集計から除外することができます。
非表示行の無視非表示にした行を集計対象から除外することができます。
高度な統計処理順位や分位数など、より高度な統計処理を行うことができます。
SUBTOTALとAGGREGATEの使い分け
| 機能 | SUBTOTAL関数 | AGGREGATE関数 |
|---|---|---|
| フィルターとの連携 | 可能 | 可能 |
| 非表示行の無視 | 可能 | 可能 |
| エラー値の無視 | 不可能 | 可能 |
| 高度な統計処理 | 不可能 | 可能 |
| 使用の簡便さ | 簡単 | やや複雑 |
SUBTOTAL関数は、基本的な集計作業に適しており、特にフィルターを使用して表示されているデータのみを集計したい場合に便利です。一方、AGGREGATE関数は、エラー値を無視した集計や高度な統計処理を行いたい場合に適しています。
よくある疑問とその解決策
Q1: SUBTOTAL関数とAGGREGATE関数の違いは何ですか？
A1: SUBTOTAL関数は、フィルターを使用して表示されているデータのみを集計することができますが、エラー値を無視することはできません。AGGREGATE関数は、エラー値を無視した集計や高度な統計処理を行うことができます。
Q2: フィルターを使用している場合、どちらの関数を使用すべきですか？
フィルターを使用して表示されているデータのみを集計したい場合は、SUBTOTAL関数を使用することをおすすめします。
Q3: エラー値を含むデータを集計する場合、どちらの関数を使用すべきですか？
エラー値を含むデータを集計する場合は、AGGREGATE関数を使用することをおすすめします。
今すぐパソコンやスマホの悩みを解決したい！どうしたらいい？
いま、あなたを悩ませているITの問題を解決します！
「エラーメッセージ、フリーズ、接続不良…もうイライラしない！」
あなたはこんな経験はありませんか？
✅ ExcelやWordの使い方がわからない💦
✅ 仕事の締め切り直前にパソコンがフリーズ💦
✅ 家族との大切な写真が突然見られなくなった💦
✅ オンライン会議に参加できずに焦った💦
✅ スマホの重くて重要な連絡ができなかった💦
平均的な人は、こうしたパソコンやスマホ関連の問題で年間73時間（約9日分の働く時間！）を無駄にしています。あなたの大切な時間が今この悩んでいる瞬間も失われています。
LINEでメッセージを送れば即時解決！
すでに多くの方が私の公式LINEからお悩みを解決しています。
最新のAIを使った自動応答機能を活用していますので、24時間いつでも即返信いたします。
誰でも無料で使えますので、安心して使えます。
問題は先のばしにするほど深刻化します。
小さなエラーがデータ消失や重大なシステム障害につながることも。解決できずに大切な機会を逃すリスクは、あなたが思う以上に高いのです。
あなたが今困っていて、すぐにでも解決したいのであれば下のボタンをクリックして、LINEからあなたのお困りごとを送って下さい。
ぜひ、あなたの悩みを私に解決させてください。
まとめ
SUBTOTAL関数とAGGREGATE関数は、Excelにおいてデータの集計を行う際に非常に便利な関数です。SUBTOTAL関数は、フィルターを使用して表示されているデータのみを集計することができ、AGGREGATE関数は、エラー値を無視した集計や高度な統計処理を行うことができます。これらの関数を適切に使い分けることで、より効率的なデータ集計が可能になります。ぜひ、実際の業務で活用してみてください。
コメント