Microsoft Officeで仕事力アップ【Excelで仕事力アップ】 SUBTOTAL関数を活用。フィルター機能と組み合わせて条件付き集計がすばやくできる！

SUBTOTAL関数を使ったことがありますか？
グループごとの小計、中計と総計を求めるときに、Excelの《データ》タブ→《アウトライン》グループの《小計》ボタンで実行する集計機能を使うとSUBTOTAL関数が自動的に入力され集計されます。このように集計を行う際には、《小計》ボタンを使うのが簡単ですが、SUBTOTAL関数を手入力して集計することもできます。
今回はSUBTOTAL関数を一から入力して行う集計と、SUBTOTAL関数とフィルター機能を組み合わせて使う便利な活用方法についてご紹介します。

SUBTOTAL関数で合計を表示

例題の表を使って、「商品名」別の合計を表示しましょう。
各商品の小計と、全商品の合計を表示するセルにSUBTOTAL関数を入力します。

「携帯電話」の合計を表示しましょう。
SUBTOTAL関数では、次のように引数を指定して入力します。

「携帯電話」の小計を表示するセルを選択し、《関数の挿入》ボタンをクリックします。

《関数の引数》ダイアログボックスが表示されます。集計方法を指定します。《集計方法》のボックスにカーソルがあることを確認し、今回は合計を求めるので「9」と入力します。
次に《参照1》のボックスをクリックし、「携帯電話」の売上金額が入力されているセル範囲を選択して、《OK》ボタンをクリックします。

携帯電話の小計が表示されました。

各商品の小計が表示できました。
最後に全商品の合計を表示しましょう。
全商品の合計を求めるセルを選択し、《関数の挿入》ボタンをクリックします。

《関数の挿入》ダイアログボックスが表示されます。
《関数の分類》が《最近使った関数》になっていることを確認します。
《関数名》の一覧から《SUBTOTAL》を選択して《OK》ボタンをクリックします。
《集計方法》のボックスにカーソルがあることを確認し、「9」と入力します。
《参照1》のボックスをクリックし、売上金額の行を小計行も含めすべて選択して、《OK》ボタンをクリックします。

全商品の合計が表示されました。
SUBTOTAL関数では、SUBTOTAL関数が入力されているセルを無視して集計してくれますので、二重に合計されてしまうことはありません。
SUM関数の場合は、同じ操作を行うと二重に合計されてしまうので、小計のセルのみを範囲選択する必要がありますが、SUBTOTAL関数の場合は気にせず全部の行を範囲選択できるので操作しやすいですね。

いかがでしょうか？このようにSUBTOTAL関数を直接入力して集計ができます。 項目が多い表でアウトラインを作成する場合は、《データ》タブ→《アウトライン》グループの《小計》ボタンを使うなど使い分けができますね。

比較例 《小計》ボタンを使った集計結果

SUBTOTAL関数とフィルターの活用

SUBTOTAL関数とフィルターを組み合わせて活用すると、フィルターによって絞り込み表示された結果のみを合計することができます。
例題の表では、全売上データの「売上数量」と「売上金額」をSUBTOTAL関数で合計しています。
フィルターで条件を設定し、SUBTOTAL関数の結果を確認しましょう。

「売上数量」と「売上金額」のセルをクリックし、数式バーでSUBTOTAL関数が入力されていることを確認します。

表内のセルをクリックし、《データ》タブ→《並べ替えとフィルター》グループの《フィルター》ボタンをクリックします。
表の項目名にボタンが表示され、フィルターモードになります。

「店舗」を指定してみましょう。
項目「店舗」の下向き三角ボタンをクリックし、表示したい店舗のチェックボックスのみをオンの状態にして《OK》ボタンをクリックします。

フィルターにより「店舗」が「青山」のデータが絞り込まれて表示され、SUBTOTAL関数による合計も表示されているデータのみの集計に変更されます。SUM関数による合計の場合は、このようにフィルターの条件に合わせて集計することができませんので状況に応じて使い分けをすると便利ですね。

いかがでしたでしょうか？このように、SUBTOTAL関数を使いこなして集計の幅を広げてみてはいかがでしょうか？ぜひ試してみてください。

• 【Excelで仕事力アップ】 グラフの目盛りの設定や複合グラフの作成。グラフの見せ方を工夫！