Excelでは、SUM関数やAVERAGE関数で集計を行うことができますが、参照するデータをフィルターで絞り込んでしまうと正しい計算ができなくなります。そのような場合、SUBTOTAL関数を使うことで絞り込んだデータのみを対象として計算することができます。
「コンサル流実務で役立つExcelテクニック」では、コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。特に次のような業務に従事されている方に役立つテクニックを多く掲載しています。
- データの集計や分析
- 分析したデータの可視化
- 顧客リストの作成・管理
- スケジュールやWBS(Work Breakdown Structure)の作成・管理
- 成績などに基づくランキングの作成
- 従業員の年齢・入社歴の管理
- 事業計画の作成
「デキる社員の100のコンピテンシー」では、マインドセットとスキルセットに分けて、どのような時代、どのような場所においても通用するコンピテンシーを紹介しています。
マインドセットでは、仕事に対する考え方や信念、価値観などの、思考や行動の基盤となる心の持ち方を解説しています。また、スキルセットについては、仕事を遂行するために必要な能力・技術を解説しています。
SUBTOTAL関数とは
SUBTOTAL関数とは、指定した範囲で表示されているセルだけを対象に集計する関数です。集計方法は数字で指定することができます。
関数 | 役割 |
---|---|
SUBTOTAL(集計方法,参照1,[参照2]…) | 参照した範囲の表示されているセルのみを指定した集計方法で計算する。 |
集計方法は以下の通り指定することができます。
集計方法 | 計算 |
---|---|
「1」または「101」 | 平均を計算 |
「2」または「102」 | 数値のセルを数える |
「3」または「103」 | データのあるセルを数える |
「4」または「104」 | 最大値を計算する |
「5」または「105」 | 最小値を計算する |
「9」または「109」 | 合計を計算する |
SUBTOTAL関数を使ってフィルターで絞り込んだデータのみ集計する方法
以下のような営業成績表で、フィルターで絞り込んだデータのみを合計するようにしたいと思います。

合計を表示するセルG3に以下のように数式を入力します。
=SUBTOTAL(9,D4:D23)

数式 | 役割 |
---|---|
SUBTOTAL(9 | 参照した範囲の表示されているセルの合計(指定方法「9」)を計算する。 |
D4:D23 | 参照する範囲はD4~D23の「営業成績」の列。 |
そうすると以下のように営業成績の合計「170」が表示されます。現時点ではフィルターで絞り込んでいないので、すべてのデータが集計されています。

次にこのデータをフィルターで「営業一課」だけに絞り込んでみます。

そうすると以下のように「営業一課」だけの合計「52」になります。

SUBTOTAL関数を使わないと
仮にSUBTOTAL関数を使わないで、SUM関数で合計すると以下のようにフィルターで絞り込んでも合計値は変更されません。
以下はSUM関数を用いて合計した結果で、「170」となります。

フィルターで「営業一課」に絞り込んでも合計値は「170」のままです。

これはフィルターで絞り込んだデータは表示はされていないものの、隠れたまま存在しているからです。
「コンサル流実務で役立つExcelテクニック」では、コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。特に次のような業務に従事されている方に役立つテクニックを多く掲載しています。
- データの集計や分析
- 分析したデータの可視化
- 顧客リストの作成・管理
- スケジュールやWBS(Work Breakdown Structure)の作成・管理
- 成績などに基づくランキングの作成
- 従業員の年齢・入社歴の管理
- 事業計画の作成
「デキる社員の100のコンピテンシー」では、マインドセットとスキルセットに分けて、どのような時代、どのような場所においても通用するコンピテンシーを紹介しています。
マインドセットでは、仕事に対する考え方や信念、価値観などの、思考や行動の基盤となる心の持ち方を解説しています。また、スキルセットについては、仕事を遂行するために必要な能力・技術を解説しています。
コメント