【Excel】COUNTIFS関数で「以上〜未満」の範囲条件を指定!Excelの区間集計テクニック

Excelで特定の条件に合致するデータの個数を数えたい場面は多いです。特に、数値の範囲を指定して集計する区間集計は、売上分析や顧客データ集計などで頻繁に利用されます。COUNTIFS関数を使えば、複数の条件を組み合わせて集計できます。しかし、「以上〜未満」といった区間条件を正確に指定する方法は、意外と迷うポイントです。

この記事では、COUNTIFS関数を使って「以上〜未満」の範囲条件を指定し、効率的な区間集計を行う方法を解説します。数式の書き方から、よくある間違いまでを網羅するため、この関数を使いこなせるようになります。

COUNTIFS関数で区間集計を行う基本

COUNTIFS関数は、指定した複数の条件すべてを満たすセルの個数を返します。基本的な構文は次のとおりです。

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

ここで、criteria_rangeは条件を指定する範囲、criteriaは条件そのものを指します。例えば、「A列が10以上」という条件で個数を数える場合、COUNTIFS(A:A, “\>=10”)のように記述します。

「以上〜未満」の区間条件を指定する仕組み

「以上〜未満」といった区間、つまりある数値A以上かつ、ある数値B未満のデータの個数を数える場合、COUNTIFS関数を直接1つで指定することはできません。これは、COUNTIFS関数が「AND条件」のみを処理するためです。

例えば、「A列が10以上かつ、A列が20未満」という条件をCOUNTIFS関数で直接表現しようとすると、COUNTIFS(A:A, “\>=10”, A:A, “\<20")のように記述することになります。この数式は、指定した範囲で条件をすべて満たすセルの個数を返します。

COUNTIFS関数で「以上〜未満」の範囲条件を指定する数式

「以上〜未満」の区間集計を行うには、主に2つの方法があります。1つは、COUNTIFS関数を2つ使用し、引き算で求める方法です。もう1つは、SUMPRODUCT関数を使用する方法ですが、COUNTIFS関数の方が直感的で分かりやすいでしょう。

COUNTIFS関数を2つ使用して区間集計を行う方法

この方法は、「B列が20以上」の個数から、「B列が30以上」の個数を引くことで、「B列が20以上30未満」の区間に入るデータの個数を求めるという考え方に基づいています。

具体的な数式は以下のようになります。

1. 集計したい範囲と条件を設定する
   まず、集計したい数値が含まれる列(例: B列)と、区間の下限値(例: 20)および上限値(例: 30)をセルに入力します。例えば、B列のデータの個数を集計し、20以上30未満の範囲を求めたいとします。
   
   仮に、下限値「20」をD1セル、上限値「30」をE1セルに入力したとします。
2. 「以上」の条件で個数を数える
   B列が下限値(D1セル)以上のデータの個数をCOUNTIFS関数で求めます。数式は次のようになります。
   
   =COUNTIFS(B:B, “>=”&D1)
3. 「未満」の条件で個数を数える
   次に、B列が上限値(E1セル)未満のデータの個数をCOUNTIFS関数で求めます。数式は次のようになります。
   
   =COUNTIFS(B:B, “<"&E1)
4. 2つの結果を引き算する
   上記2つの結果から、「以上」の個数から「未満」の個数を引くことで、指定した区間(20以上30未満)に入るデータの個数が算出されます。
   
   最終的な数式は以下のようになります。
   
   =COUNTIFS(B:B, “>=”&D1) – COUNTIFS(B:B, “<"&E1)

数式の解説とポイント

この数式では、演算子(“>=” や “<")とセル参照(D1 や E1)を連結するために、アンパサンド(&)を使用しています。これにより、セルの値を変更するだけで、集計する区間を柔軟に変更できます。

例えば、B列のデータが以下のような場合を考えます。

B1: 15
B2: 20
B3: 25
B4: 29
B5: 30
B6: 35

D1セルに「20」、E1セルに「30」を入力した場合、数式「=COUNTIFS(B:B, “>=”&D1) – COUNTIFS(B:B, “<"&E1)」は、以下のように計算されます。

COUNTIFS(B:B, “>=20”) は 4(B2, B3, B4, B5)を返します。

COUNTIFS(B:B, “<30") は 4(B1, B2, B3, B4)を返します。

しかし、この計算では「20以上」かつ「30未満」の範囲を正確に求められません。なぜなら、「20以上」には30も含まれてしまい、「30未満」には20が含まれてしまうからです。

区間集計で「A以上B未満」を正確に求めるには、以下の数式を使用します。

1. 「以上」かつ「未満」の条件を直接指定する
   COUNTIFS関数は複数の条件を指定できるため、「A列が20以上」かつ「A列が30未満」という条件を直接指定できます。数式は以下のようになります。
   
   =COUNTIFS(B:B, “>=”&D1, B:B, “<"&E1)

数式の解説とポイント

この数式は、COUNTIFS関数に2つの条件ペアを指定しています。1つ目のペアは「B:B」範囲に対して「\>=20」という条件、2つ目のペアは同じ「B:B」範囲に対して「\<30」という条件です。COUNTIFS関数は、これらすべての条件を満たすデータのみをカウントします。

上記の例(B1: 15, B2: 20, B3: 25, B4: 29, B5: 30, B6: 35)で、D1セルに「20」、E1セルに「30」を入力した場合、数式「=COUNTIFS(B:B, “>=”&D1, B:B, “<"&E1)」は、以下のデータが条件を満たすと判断します。

B2: 20 (20以上かつ30未満)

B3: 25 (20以上かつ30未満)

B4: 29 (20以上かつ30未満)

したがって、この数式は「3」を返します。これが「20以上30未満」の区間集計として正しい結果となります。

区間集計における注意点とよくある失敗例

COUNTIFS関数で区間集計を行う際には、いくつかの注意点があります。これらを理解しておくことで、意図しない結果になることを防げます。

h3>「以上」と「以下」、「未満」と「以上」の混同

最もよくある間違いは、演算子の指定ミスです。「以上」と「以下」、「未満」と「以上」の区別を曖昧にすると、集計結果がずれてしまいます。

例えば、「20以上30以下」のように上限も「以下」で指定したい場合は、数式を以下のように変更する必要があります。

=COUNTIFS(B:B, “>=”&D1, B:B, “<="&E1)

ここで、D1セルに「20」、E1セルに「30」が入力されていると仮定します。この数式は、B列のデータが20以上であり、かつ30以下であるものの個数を数えます。

h3>条件範囲の指定ミス

COUNTIFS関数では、すべての条件で指定する範囲の行数または列数が一致している必要があります。一致していない場合、Excelはエラー(#VALUE!エラーなど)を返したり、意図しない結果になることがあります。

例えば、COUNTIFS(B1:B10, “>=20”, C1:C10, “<30") のように、範囲のサイズが一致しているか確認してください。もし、B列は10行まで、C列は5行までしかデータがない場合、正しく集計できません。

h3>数値と文字列の混在

集計対象の列に、数値として認識されない文字列が混在していると、正しくカウントされないことがあります。例えば、数値の後に「個」や「円」といった単位が直接入力されている場合、それらは文字列として扱われます。

このような場合は、COUNTIFS関数で条件を指定する前に、データを数値形式に統一するか、あるいはTEXT関数やVALUE関数などを組み合わせて、文字列を数値に変換する前処理が必要になることがあります。

例えば、B列に「25個」のようなデータがある場合、COUNTIFS(B:B, “>=20”) の条件では「25個」はカウントされません。これをカウントするには、B列のデータを数値のみにするか、あるいは集計用の列を設けて、TEXTBEFORE関数などで単位を取り除き、VALUE関数で数値に変換するなどの工夫が必要です。

h3>セル参照の絶対参照・相対参照の誤り

集計範囲や区間の上限・下限値をセル参照で指定した場合、数式をコピー&ペーストすると参照がずれることがあります。これを防ぐためには、絶対参照($マークを使用)や複合参照を適切に使い分ける必要があります。

例えば、区間の下限値がD1セル、上限値がE1セルにある場合、数式を横方向にコピーする際は、D1とE1は固定したいので「$D$1」と「$E$1」のように絶対参照にします。一方、集計対象の範囲(B:B)は、数式をコピーする行によって変えたい場合は相対参照のままで良いですが、列全体を参照する場合は「B:B」で問題ありません。

もし、集計範囲を例えばB1:B10のように指定している場合は、数式をコピーする際に範囲がB2:B11のようにずれないように、B$1:B$10のように行を固定したり、$B$1:$B$10のように列と行を固定したりする必要があります。

SUMPRODUCT関数を使った区間集計(参考)

COUNTIFS関数以外にも、SUMPRODUCT関数を使用して区間集計を行うことも可能です。SUMPRODUCT関数は、配列の対応する要素の積を合計する関数ですが、条件式と組み合わせることで、COUNTIFS関数と同様の集計ができます。

「20以上30未満」の区間集計を行う場合、SUMPRODUCT関数を使った数式は以下のようになります。

=SUMPRODUCT((B:B>=D1)*(B:B

この数式では、条件式 `(B:B>=D1)` と `(B:B

SUMPRODUCT関数は、 COUNTIFS関数が使えない古いExcelバージョンでも利用できるという利点があります。しかし、COUNTIFS関数の方がより直感的で、条件の追加も容易なため、一般的にはCOUNTIFS関数が推奨されます。

機能	COUNTIFS関数	SUMPRODUCT関数
主な用途	複数条件での個数カウント	配列の積の合計、複数条件での集計
区間集計	「以上〜未満」の指定が直感的	条件式を配列として扱い、積算する
可読性	高い(条件が明示的)	やや低い(配列計算の理解が必要)
Excelバージョン	Excel 2007以降	Excel 2003以降(ただし、配列数式として使用する場合)
条件追加の容易さ	容易(引数を追加)	数式の変更が必要

COUNTIFS関数とSUMPRODUCT関数は、どちらも区間集計に有効な手段です。ご自身のExcelバージョンや、数式の分かりやすさを考慮して使い分けると良いでしょう。COUNTIFS関数は、より新しいバージョンで利用可能であれば、その直感的な記述方法からおすすめです。

まとめ

この記事では、COUNTIFS関数を使用して「以上〜未満」の区間条件を指定し、Excelで効率的に区間集計を行う方法を解説しました。COUNTIFS関数で複数の条件を組み合わせることで、複雑な集計も正確に行えます。

数式「=COUNTIFS(対象範囲, “>=”&開始値, 対象範囲, “<"&終了値)」を理解し、演算子とセル参照の連結にアンパサンド(&)を用いることで、柔軟な区間設定が可能になります。今後は、このCOUNTIFS関数を使った区間集計テクニックを、売上データや人事データなどの分析に活用してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】