更新日：2022/07/04、作成日：2018/04/28

エクセル数値の範囲や条件に一致するセルの合計や数を数える

はじめに

365202120192016：対応

エクセルの数値の範囲や条件に一致するセルの合計や数を数える方法を紹介します。

フィルタ機能を使用して 100 以上 200 未満のデータなどの集計ができます。文字列と一致するや特定の文字を含むなどの条件を指定できます。

「SUMIFS 関数」などの～IFS 関数を使用してもできます。

フィルターを使用する

データの [セル] を 1 つ選択します。

[データ] タブをクリックし、並べ替えとフィルターグループにある [フィルター] をクリックします。

「SUBTOTAL 関数」を使用して集計します。第 1 引数にはとりあえず 109 を入力して、第 2 引数にセルの範囲を指定します。

=SUBTOTAL(109,C3:C10)

見出し行の [▼] をクリックして集計したい範囲や条件を指定します。ここでは [数値フィルター] から [指定の範囲内] をクリックします。

20 ～ 40 の範囲のデータを集計するには、それぞれの [値] を入力して [OK] をクリックします。

条件に一致したデータが抽出されます。「SUBTOTAL 関数」の第 1 引数に 109 を入力すると合計を求められます。

第 1 引数に 102 を入力するとセルを数えられます。

第 1 引数の値を変更して集計方法を指定できます。

第 1 引数	関数	説明
101	AVERAGE 関数	平均を求める
102	COUNT 関数	数値のセルを数える
103	COUNTA 関数	空白でないセルを数える
104	MAX 関数	最大値を取得する
105	MIN 関数	最小値を取得する
106	PRODUCT 関数	掛け算で合計を求める
109	SUM 関数	合計を求める

AGGREGATE 関数を使用する

「SUBTOTAL 関数」の上位互換である「AGGREGATE 関数」を使用しても同様に集計できます。

=AGGREGATE(9,3,C3:C10)

ただし、フィルタとの相性がよくありません。フィルタの範囲に「AGGREGATE 関数」を入力したセルも含まれてしまいます。そのため、フィルタすると集計値もフィルタされてしまいます。上記の画像のようにデータの範囲との間を一行空けるなどの対策が必要です。「SUBTOTAL 関数」ならフィルタに含まれません。

条件の種類

文字列データについては、一致するや含むや始まるなどが指定できます。

数値データについては、以上や未満の他にも上位や下位なども指定できます。

日付データについては、以降や範囲などを指定できます。

1 つの列に対して複数の条件を指定するには「フィルターでデータを抽出する」をご覧ください。

～IFS 関数を使用する

次の～IFS 関数を使用して集計できます。

関数	説明
AVERAGEIFS 関数	平均を求める
COUNTIFS 関数	セルを数える
MAXIFS 関数	最大値を取得する
MINIFS 関数	最小値を取得する
SUMIFS 関数	合計を求める

条件の指定の方法はどの関数でも同じです。

=COUNTIFS(条件範囲1, 条件1, 条件範囲2, 条件2 …)

「COUNTIFS 関数」以外は第 1 引数に集計するセルの範囲を指定します。

=SUMIFS(集計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2 …)

数値の条件

20 ～ 40 の範囲のデータを集計するには次のように入力します。

=COUNTIFS(C3:C10,">=20",C3:C10,"<=40")
=SUMIFS(C3:C10,C3:C10,">=20",C3:C10,"<=40")

条件には次の比較演算子を使用できます。

演算子	演算名	使用例	結果
=	等しい	"=2"	2 と同じ
<>	等しくない	"<>2"	2 以外
>	大きい	">2"	2 より大きい
<	小さい	"<2"	2 より小さい
>=	以上	">=2"	2 以上
<=	以下	"<=2"	2 以下

文字列の条件

名前がエクセルのデータを集計するには次のように入力します。 =エクセルの = はなくてもいいです。

=COUNTIFS(B3:B10,"=エクセル")
=SUMIFS(C3:C10,B3:B10,"=エクセル")

条件には比較演算子の他にワイルドカードを使用できます。

パターン	説明	使用例	一致例
*	任意の長さの文字	"あ*"	あ, あい, あいう
?	任意の 1 文字	"あ?"	あい, あか, あき
~	ワイルドカードの文字 * ? を検索する	"あ~?~*"	あ?*