【Excel】SUBTOTALにSUMIFを組合せるように複数条件で計算できないか？

当サイトでは記事内に広告を掲載しています。

Table of Contents

フィルター後のデータから指定した条件の合計値を算出する方法

SUMIF関数は指定した条件の合計値を算出することができる便利な関数です。

しかし、例えば表で別の条件でフィルターをかけて、絞り込まれたデータの中からSUMIF関数を使用しても、絞り込まれたデータの中から指定した条件の合計値を算出することはできません。

例え、戻り値が正しかったとしてもSUMIF関数を使用した結果は、フィルターで絞り込む前のデータの中で指定した条件の合計値を算出しています。

そこで今回は、その代替策としてSUBTOTAL関数やSUMIFS関数、SUMPRODUCT・SUBTOTAL・INDIRECTを組み合わせる方法について解説します。

この記事を読むと、これらの方法と使い分けについて理解することができます。

SUMIF関数が使えるケース

以下の表から「分類」の「その他」を除く「先進国」の割合の合計を計算するにはどのように関数を入力すれば良いでしょうか？

上記のケースではSUMIF関数を使用すると良いでしょう。

SUMIF関数の構文

SUMIF(範囲, 検索条件, [合計範囲])

SUMIF関数の活用事例

先程の例について、SUMIF関数を使用すると以下のようになります。

=SUMIF(C2:C15,”先進国”,E2:E15)

E2:E12の合計範囲に対応するC2:C12の範囲のセルが”先進国”の値のみを合計します。

結果は84%となりました。

SUBTOTAL関数を使えば、フィルターで表示をさせた数値だけ計算できる

SUBTOTAL関数の構文

SUBTOTAL(集計方法, 参照1, [参照2],・・・)

集計方法は以下の番号で指定します。

今回は9番の合計値を使います。

集計方法の番号	集計方法の関数	関数の意味
1または101	AVERAGE	平均値
2または102	COUNT	数値の個数
3または103	COUNTA	データの個数
4または104	MAX	最大値
5または105	MIN	最小値
6または106	PRODUCT	積
7または107	STDEV.S	不偏標準偏差
8または108	STDEV.P	標本標準偏差
9または109	SUM	合計値
10または110	VAR.S	不偏分散
11または111	VAR.P	標準分散

SUBTOTAL関数の活用事例

先程の表を用いてSUBTOTAL関数を使用して以下のように入力してみました。

=SUBTOTAL(9,E2:E15)

今は何もフィルターをかけていないので、E列に表示されている割合の合計である「100%」が表示されています。

次にフィルターを使って先進国に絞り込んでみます。

フィルターを先進国で絞り込んだ結果、D19に入力したSUBTOTLの結果が100%から84%に変わりました。

このようにSUBTOTAL関数では、フィルターで絞り込んだ結果の数値を計算することができます。

SUMIF関数は、フィルターで表示をさせた数値だけ計算することはできない

次にフィルターで先進国を絞り込んだ状態から、先進国かつアジア・パシフィックの合計を出すにはどのようにすればよいでしょうか。

今回の例では、先進国かつアジア・パシフィックは日本とオーストラリアなので6%+2%＝8％ぐらいになるはずです。

SUMIF関数で次のように入力してみます。

=SUMIF(D2:D15,”アジア・パシフィック”,E2:E15)

結果は16%になりました。

これはSUMIF関数がフィルターで絞り込んだ先進国の中からアジア・パシフィックを合計したのではなく、非表示にした新興国も含めたアジア・パシフィックを合計しているからです。

フィルターではなくSUMIFS関数を用いて複数条件の合計値を求める

解決策の一つとして、SUMIFS関数を使用するのが一つのやり方になります。

SUMIFS関数の構文

SUMIFS(合計対象範囲, 条件範囲1, 条件1,[条件範囲2, 条件2],[条件範囲3, 条件3],・・・)

SUMIFS関数の活用事例

先程の表のフィルターを外してSUMIFS関数を使用して以下のように入力してみました。

=SUMIFS(E2:E15,C2:C15,”先進国”,D2:D15,”アジア・パシフィック”)

先進国のアジア・パシフィックだけを合計することができました。

条件付きSUBTOTALでフィルターで表示をさせた数値だけ計算

最後に、フィルターで先進国や新興国を切り替えても、常にフィルターで絞り込んだ中でアジア・パシフィックだけを合計した結果を一つのセルに表示する方法について解説します。

筆者の場合、イメージとしてSUBTOTALとCOUNTIFや、SUBTOTALとSUMIFを組み合わせることができないだろうかと疑問を持った際に、今回のやり方にたどり着きました。

以下の様に入力します。

=SUMPRODUCT((D2:D15=”アジア・パシフィック”)*(SUBTOTAL(9,INDIRECT(“E”&ROW(D2:D15)))))

SUMPROUCT関数の中の1つ目の括弧（D2:D15=”アジア・パシフィック”）はD2からD15の範囲のセル一つ一つについて“アジア・パシフィック”と一致するかどうか検証をしています。

実際にこの部分だけ書き出してみると、次のようになります。

D2からD15の範囲のセルについて“アジア・パシフィック”と一致する場合、TURE、一致しない場合FALSEになっています。

ExcelではTRUE=1、FALSE=0として計算されます。

試しにF列のTRUEもしくはFALSEに1をかけてみます。

結果は以下のようになりました。

SUMPROUCT関数の中の2つ目の括弧(SUBTOTAL(9,INDIRECT(“E”&ROW(D2:D15))))は以下の概念図のように、INDIRECT関数でD2からD15の範囲の行でE列の値を返し、SUMPROUCT関数でフィルターの結果のみを合計しています。

こちらもSUBTOTAL(9,INDIRECT(“E”&ROW(D2:D15)))の部分だけ書き出してみます。

書き出した結果、表の割合データと同じものが書き出されました。

こうして、“アジア・パシフィック”と一致するかどうか検証した地域の配列と割合の配列が出来上がりました。

SUMPROUCT関数は行数と列数が等しい配列の積を合計する関数になりますので、以下のような計算をする式であることが分かります。

SUMPROUCT関数は以下の記事で解説していますので参考にしてください。

2022年6月29日【Excel】SUMPRODUCT関数は複数条件を満たすものの合計も計算可能

関数の結果も16%になりました。

この式の便利なところは、フィルターで先進国に絞った場合、先進国の中でのアジア・パシフィックの合計を計算し、

フィルターで新興国に絞った場合、新興国の中でのアジア・パシフィックの合計を計算するということを1つのセルでできる点です。

Excelを一度体系的に学ぶと、目に見えて生産性が飛躍的に向上します。

MOS Excel※は資格の取得、及び資格取得までのプロセスを通じて体系的にExcelスキルを身につけることができますのでコスパが良くおすすめです。

※Microsoft Office Specialist（MOS）ExcelはExcelの利用能力を証明する世界的な資格です。

筆者は「スペシャリスト（一般）」と「エキスパート（上級）」両方を取得して体系的にスキルを身に着けました。

MOS試験対策でどのテキストを使うべきか悩まれる方も多いのではないかと思いますが、以下がおすすめです。

今回の内容が参考になれば幸いです。