【簡単エクセル/Excel】フィルタで絞り込んだ値だけを集計する方法｜SUBTOTAL｜Excel関数 #001 改訂第2版
運営者・ポテ
いつもありがとうございます。
「わかりやすいExcel関数解説シリーズ」へようこそ。
本稿では、フィルタで絞り込んだ値だけを集計する方法を解説します。
Excelで数値の合計などを行う際、多くの方がSUM関数を利用するでしょう。
しかし、特定の条件で絞り込んだデータだけを集計したい場合、SUM関数ではフィルタで非表示にしたデータも含まれてしまうのが難点です。
そこで役立つのがSUBTOTAL関数です。
この関数は、フィルタで表示されているデータだけを対象にできるほか、合計だけでなく、平均・最大値・最小値など、さまざまな集計方法を一つの関数で使い分けることができます。
このように、SUBTOTAL関数を使えば、状況に応じた柔軟なデータ集計が可能になります。
Excel関数を使いこなして、自分に合ったデータを作成し、仕事量は半分に、成果は2倍にしていきましょう。
初心者の方にも分かりやすいよう、丁寧に解説していきます。ぜひご覧ください。
フィルタで絞り込んだ値だけを集計する方法 ── SUBTOTAL関数の使い方
具体例と解説
次のようなデータを例に取って考えてみましょう。
順を追って解説していきます。
上図のデータに、SUM関数とSUBTOTAL関数で合計を計算した行を追加します。現在のところ同じ値が表示されているのが分かりますね。
両関数の構文は次のようになっています。各画像の右上の数式表示バーに入力されている数式をご確認ください。
このデータにデータフィルタを設定して、指定のデータだけ表示させてみましょう。データ範囲を選択し、「データ」タブ→「フィルタ」をクリックします。
データフィルタが設定されると、次のように各列に「▼マーク」のボタンが表示されます。
例として、「バナナ」と「リンゴ」のみを表示出してみましょう。
「▼マーク」のボタンを押すと、以下のメニューが表示されますので「バナナ」と「リンゴ」のみにチェック入れ、その他のすべての項目のチェックを外してください。
次のように表示されます。
SUM関数では非表示の項目のデータまで集計されていますが、SUBTOTAL関数では”表示されている項目のみの集計”（非表示行を無視した集計）となっていることが分かりますね。
おー！
以上で、SUBTOTAL関数の解説は終了です！
SUBTOTAL関数の構文
ここでは、SUBTOTAL関数の構文を解説します。
SUBTOAL関数は、「合計」以外にもさまざまな項目が集計できます。
基本構文は次の通りです。
SUBTOTAL(集計方法の番号, 範囲1, [範囲2], ...)引数の「集計方法の番号」は、下表の通りです。1~11、101～111まであります。なお、表の右端の「手動による非表示」については、後述します。
| 集計方法の番号 | 集計の種類 | フィルタによる非表示行を無視 | 手動による非表示行を無視 |
|---|---|---|---|
| 1 | 平均 | YES | NO |
| 2 | カウント | YES | NO |
| 3 | 数値のカウント | YES | NO |
| 4 | 最大値 | YES | NO |
| 5 | 最小値 | YES | NO |
| 6 | 積 | YES | NO |
| 7 | 標本標準偏差 | YES | NO |
| 8 | 全体標準偏差 | YES | NO |
| 9 | 合計 | YES | NO |
| 10 | 分散（標本） | YES | NO |
| 11 | 分散（全体） | YES | NO |
| 101 | 平均 | YES | YES |
| 102 | カウント | YES | YES |
| 103 | 数値のカウント | YES | YES |
| 104 | 最大値 | YES | YES |
| 105 | 最小値 | YES | YES |
| 106 | 積 | YES | YES |
| 107 | 標本標準偏差 | YES | YES |
| 108 | 全体標準偏差 | YES | YES |
| 109 | 合計 | YES | YES |
| 110 | 分散（標本） | YES | YES |
| 111 | 分散（全体） | YES | YES |
ここで、右端の列の「手動による非表示行を無視」の「手動による非表示」とは、右クリックメニューでの非表示のことです。これです👇
データフィルタでの非表示とは違いますのでご注意ください。
以上で、SUBTOTAL関数の構文の解説は終了です！
Excel関数スキルアップのための参考情報
Excel関数に関する書籍は数多く出版されています。もし興味がございましたら、チェックしてみてください。入門書に関しては、どの書籍も大きな違いはありません。あまり悩むことに時間をかけるよりは、手頃なものを一冊選んでみると良いでしょう。
＼チェックしてみよう／
＼チェックしてみよう／
＼チェックしてみよう／
【初心者歓迎／無料相談受付中】業務改善アプリケーション作成のご相談を承ります
いつもありがとうございます！
限られた時間をより良く使い、日本の生産性を高めたい──
みんなの実用学を運営するソフトデザイン工房では、業務整理や業務改善アプリケーション作成のご相談を承っております。
お気軽にご相談ください。
こちらの記事でも紹介しております。
おわりに
ご覧いただきありがとうございました。
本稿では、「データフィルタで絞り込んだ項目の値だけを集計する方法」を解説いたしました。
お問い合わせやご要望等ございましたら、「お問い合わせ／ご要望」またはコメントにて、ご連絡いただければ幸いでございます。
皆様の人生がより一層素晴らしいものになるよう、少しでもお役に立てれば幸いでございます。
この記事を書いた人
