いまさら聞けないExcelの使い方講座

【Excel】SUMIF関数いらず！エクセルのオートフィルターとSUBTOTAL関数で特定条件のデータを集計するテク

トップスタジオ

2019年9月11日 06:55

「商品ごと」や「顧客ごと」に集計したいけれどSUMIF関数が難しそう！

　売上表などを作成する際に「商品ごと」や「顧客ごと」に金額を集計したい時ってありますよね。そんな時、どのように対応しますか？　SUMIF関数を使って、指定した条件ごとに金額を集計する方法を思いつく読者は多いかもしれません。このSUMIF関数は、Excelの参考書などでは覚えておくべき関数として紹介されていることが多いですが、なんだか難しそうだと思っていませんか。

　ここでSUMIF関数について少しおさらいしておきましょう。SUMIF関数は、指定した条件に当てはまるデータの合計を求める関数で、以下の書式で記述します。1つ目の引数の「範囲」（①）で指定したセル範囲の中から、2つ目の「検索条件」（②）で指定したデータを検索し、この検索条件を満たすデータの合計（合計範囲）（③）を求めます。

　ここまでの説明を読んだだけでも、関数に馴染みのない読者の中には、尻込みしてしまう人もいるのではないでしょうか。そこで今回は、特定の条件を満たすデータの合計を求める際に、オートフィルターとSUBTOTAL関数を組み合わせて行う方法を解説します。このやり方なら、オートフィルターを使ってさまざまな条件でデータを絞り込むたびに、抽出されたデータだけの合計を求められるのでとても便利です。なお、SUBTOTAL関数も馴染みのない読者は多いかもしれませんが、書式は簡単で、使いやすい関数なので安心してください。ではさっそくやってみましょう。

オートフィルターとSUBTOTAL関数を組み合わせ、特定条件を満たすデータの合計を求める

　冒頭でも触れたように、オートフィルターとSUBTOTAL関数を組み合わせて使用することの利点は、SUBTOTAL関数を使って合計を求める数式を記述することで、オートフィルターで絞り込んだデータだけの合計を自動的に表示できるということです。オートフィルターで別の抽出条件に変えれば、変更された抽出データの合計が自動的に表示されるのです。

　ではまず、次の「9月売上表」（①）の例を使って、オートフィルターを設定するところから始めましょう。表内のいずれかのセルをクリックした状態で、［ホーム］タブ（②）→［並べ替えとフィルター］（③）→［フィルター］（④）をクリックします。

　すると、各列の表見出しの右端に下向きの三角形の矢印が表示され（⑤）、オートフィルターが設定されました（［データ］タブの［フィルター］をクリックしても設定できます。リボンのメニューから操作する代わりに、［Ctrl］＋［Shift］＋［L］キーを押すことでオートフィルターを設定することも可能です）。

　次に、この表の一番下のセル（セルG11）に、集計した金額を表示する欄を作成します（⑥）。

　このセルG11に、SUBTOTAL関数を使って数式を入力していきます。SUBTOTAL関数を使うことで、オートフィルターで絞り込んだデータの合計を求められることは先ほど説明したとおりです。

　実際に式を入力する前に、SUBTOTAL関数の書式について説明しましょう。SUBTOTAL関数は、合計や平均、個数などのさまざまな集計を行える関数で、次の書式で記述します。

※集計方法として1～11を指定した場合、［ホーム］タブ→［書式］→［非表示/再表示］→［行を表示しない］を使用して非表示にした行の値も含められます。一方、オートフィルターの結果では、集計方法で指定する値にかかわらず、非表示になった行はすべて無視されます。

　1つ目の引数で指定する「集計方法」には、平均（AVERAGE関数）、最大値（MAX関数）、最小値（MIN関数）、合計（SUM関数）など、集計を行う時に使いたい関数を1～11（または101～111）の数字で指定します。2つ目の引数「セル範囲」には集計対象のセル範囲を指定します。

　では数式を入力してみましょう。セルG11をクリックして「=SUBTOTAL(9,G3:G10)」（⑦）と入力します。1つ目の引数には、SUM関数に対応する「9」（⑧）を入力し、2つ目の引数には集計の対象となるセル範囲G3:G10（⑨）を指定します。

　［Enter］キーを押して数式を確定すると、集計結果が表示されます（⑩）。

　これで、オートフィルターの設定と数式の入力は完了です。次の項では、実際にオートフィルターで絞り込んだ時に合計金額が正しく表示されるかどうか試してみましょう。

オートフィルターで抽出したデータだけの合計を表示する

　前項で設定した表を使って、店舗名が「品川支店」のデータを抽出してみましょう。まず、「店舗名」と表示された見出し（セルB2）のフィルターボタン（①）をクリックして表示されるメニューから、［（すべて選択）］（②）をクリックしてすべてのチェックマークを外します。

　続けて、［品川支店］（③）をクリックしてチェックマークを付け、［OK］（④）をクリックします。

　すると、店舗名が「品川支店」のデータだけが抽出されて表示されます（⑤）。ここで表の1番下にある集計欄に注目してください（⑥）。抽出されたデータのみの金額の合計になっていますね。

　また別の条件でデータを抽出して集計欄がどのように変化するかを試してみましょう。担当者が「田中」さんと「山口」さんのデータを抽出し、2人が担当した商品の合計金額を表示してみましょう。「店舗名」のフィルターは解除しておきます（「店舗名」のフィルターボタンをクリックして表示されるメニューから、［"店舗名"からフィルターをクリア］をクリックすると解除できます）。

　「担当者（セルC2）」のフィルターボタン（⑦）をクリックします。表示されるメニューから、まず［（すべて選択）］をクリックしてすべてのチェックマークを外したあとで［田中］（⑧）と［山口］（⑨）にチェックマークを付けます。

　抽出条件を設定できたら、［OK］（⑩）をクリックします。

　すると、担当者が「田中」さんと「山口」さんのデータだけが抽出されて表示されます（⑪）。そして表の1番下にある集計欄（⑫）は、新たに抽出されたデータだけの合計金額が表示されていますね。

　オートフィルターとSUBTOTAL関数を組み合わせることの利点をわかってもらえたでしょうか。オートフィルターで手軽に条件を絞り込むことができ、SUBTOTAL関数で絞り込んだデータだけの合計を自動で求められるので便利ですよね。

特定の条件を満たすデータの合計を求めるならオートフィルターとSUBTOTAL関数を組み合わせよう

　今回は、特定の条件を満たすデータの合計を求める際に、オートフィルターとSUBTOTAL関数を組み合わせて行う方法を解説しました。SUBTOTAL関数を使って合計を算出する数式を書いておけば、オートフィルターでデータを絞り込むたびに抽出データの合計がわかるので便利ですよね。ぜひ、使ってみてくださいね。