ExcelのCOUNTIFS・SUMIFS・AVERAGEIFSの使い方を徹底解説!条件付き集計の基本

Excelで最もよく使われる基本的な計算関数といえば、COUNT(個数を数える)、SUM(合計を求める)、AVERAGE(平均を出す)の3つです。Excelで予算管理をしている方も、旅行の費用を記録している方も、一度は使ったことがあるのではないでしょうか。

この記事では、この3つの基本関数に加えて、条件を指定して集計できる便利な応用関数であるCOUNTIFS、SUMIFS、AVERAGEIFSの使い方をわかりやすく解説します。

例として、携帯電話を販売する新しいオンラインショップを始めたとしましょう。最初の2か月間の売上をまとめたシートがあるとします。サンプルのExcelファイルはこちらからダウンロードできます。

ExcelのCOUNT・SUM・AVERAGE関数の基本

何台の携帯電話を販売したのかを知りたい場合は、COUNT関数を以下のように使います。

=COUNT(E2:E16)

売上の合計金額を求めたい場合は、SUM関数を以下のように使います。

=SUM(E2:E16)

すべての携帯電話の平均売上を知りたい場合は、AVERAGE関数を以下のように使います。

=AVERAGE(E2:E16)

実行すると、結果は以下のようになります。

COUNT・SUM・AVERAGEの各関数は、セルの値が数値形式のレコードにのみ有効です。数式の範囲内(この例ではE2:E16)に数値形式以外のデータが含まれている場合、そのデータは自動的に無視されます。

そのため、これらの関数で参照するセルはすべて「数値」形式(「文字列」ではなく)になっていることを必ず確認してください。試しに同じ数式で範囲をE2:E16からE:Eに変更してみましょう。見出し行(販売価格)が文字列形式のため無視されるので、同じ結果が返ります。

では、「アメリカ(USA)で販売された分だけ」の販売台数、売上合計、1台あたりの平均売上を知りたい場合はどうすればよいのでしょうか?ここで活躍するのがCOUNTIFS・SUMIFS・AVERAGEIFSです。以下の数式を見てみましょう。

COUNTIFS関数で条件に合う件数を数える

数式の構成要素は以下の通りです。

1. =COUNTIFS( – セルに入力する数式は「=」から始まります。COUNTIFSは使用するExcel関数の名前です。
2. D2:D16 – 条件に合致するかどうかを判定する対象となるデータ範囲です。
3. "USA" – 指定したデータ範囲(D2:D16)の中から探す条件です。
4. ) – 数式の終わりを示す閉じ括弧です。

この数式は「6」を返します。つまり、USA倉庫から出荷された商品の販売台数が6件であることがわかります。

SUMIFS関数で条件に合う合計額を求める

数式の構成要素は以下の通りです。

1. =SUMIFS( – ここでも数式は「=」から始まります。
2. E2:E16 – 合計したいデータ範囲です。この例では販売価格を指します。
3. D2:D16 – 条件に合致するかどうかを判定する対象となるデータ範囲です。
4. "USA" – 指定したデータ範囲(D2:D16)の中から探す条件です。
5. ) – 数式の終わりを示す閉じ括弧です。

この数式の結果は$6,050となり、USA倉庫から出荷された商品の売上合計を示しています。

AVERAGEIFS関数で条件に合う平均値を求める

数式の構成要素は以下の通りです。

1. =AVERAGEIFS( – 数式は「=」から始まります。
2. E2:E16 – 平均を求めたいデータ範囲です。この例では、USAで販売されたすべての携帯電話の平均売上を求めます。
3. D2:D16 – 条件に合致するかどうかを判定する対象となるデータ範囲です。
4. "USA" – 指定したデータ範囲の中から探す条件です。
5. ) – 数式の終わりを示す閉じ括弧です。

この数式によると、USAでの1台あたりの平均販売価格は約$1,008だったことがわかります。

複数の条件を組み合わせる方法

これら3つの関数は、複数の条件を指定することも可能です。例えば、USAで販売された商品のうち、さらにSamsungブランドに限定して同じ数値(COUNT・SUM・AVERAGE)を知りたい場合は、判定対象のデータ範囲とその条件を追加するだけでOKです。

以下の例では、最初の条件に加えて2つ目の条件を追加しています。(青字が1つ目の条件、赤字が2つ目の条件)

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")

=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

「S」なしのCOUNTIF・SUMIF・AVERAGEIFとの違い

ちなみに、Excelには語尾に「S」が付かないCOUNTIF、SUMIF、AVERAGEIFという関数もあります。これらはCOUNTIFS・SUMIFS・AVERAGEIFSと似たような使い方ができますが、「S」なしのバージョンには、1つの数式につき指定できる条件が1つまでという制限があります。

また、引数の並び順(構文)も若干異なるため、1つの条件でも複数の条件でも柔軟に対応できるCOUNTIFS・SUMIFS・AVERAGEIFSに統一して使うことをおすすめします。ぜひ日々の業務に活用してみてください!