COUNT、COUNTIFS関数でOR条件が使用できないため代替手段を検討する

2023.09.09

ExcelでOR条件を使ったデータ集計！COUNTIFSではできないこともこれで解決

ExcelのCOUNTIFS関数・COUNTIF関数は非常に便利である一方で、OR条件を直接扱うことはできません。COUNTIFS関数では複数の検索条件が自動的にAND条件で処理されます。

しかし、OR条件処理をさせるために代わりになる方法がいくつかあります。今回はDCOUNTA関数とFILTER関数を組み合わせたCOUNTA関数を使った2つの解決策をご紹介します。

テーマ・サンプル表

以下の表(A1:F15)において、「国語のスコアがA、または英語のスコアがA、または数学のスコアがA、または理科のスコアがA、または社会のスコアがAである行数をカウントする」という操作を行います。

受験番号国語英語数学理科社会
10001BDBDA
10002BEACC
10003CDBBD
10004EDAAC
10005BAECE
10006ADDED
10007EEDCD
10008DCEEA
10009DBCBD
10010BACBD
10011CBADE
10012AAAAA
10013DCADD
10014DEADC

「または」ではなく「かつ」ならばAND条件ですので、以下のようなCOUNTIFS関数で処理が可能です。

=COUNTIFS(B2:B15,"A",C2:C15,"A",D2:D15,"A",E2:E15,"A",F2:F15,"A")

「または」、の「OR条件処理」ならば代替手段を検討します。

方法1: DCOUNTA関数を使う

DCOUNTA関数は、「データベース関数」の一種です。特定の条件に一致するセルの数をカウントするために使用されます。この方法では、いくつかの検索条件範囲を設定してデータセットからフィルタリング集計します。

検索条件の準備

まず、以下のように空いているスペース(例：J2:N7)へ検索条件範囲を設定します。データベース関数では空いているスペースに検索条件を入力しておくことでフィルタリング集計が可能です。検索条件は「列見出し+条件値」とします。行を変えるとOR条件処理になります。同じ行だとAND条件です。以下はOR条件の検索条件表になります。

国語英語数学理科社会
A
A
A
A
A

ちなみにAND条件の検索条件表は以下のようになります。

AND条件の場合
国語英語数学理科社会
AAAAA

計算式の作成

次に、以下の式を使用してデータをカウントします。フィルタリング処理をしたあと、何個の「受験番号」が存在するかを集計します。「11」が返ります。中央の引数「フィールド」は空白セルがない列ならどれでもよいです。ここでは「受験番号」を使っています。

=DCOUNTA(A1:F15,A1,J2:N7)

「A1:F15」のリストから「J2:N7」の条件でフィルタリング処理をし、フィルタリング後の「受験番号」をカウントする計算式となります。

長所と短所

この方法は視覚的に検索条件を確認できる点で優れています。

ただし検索条件範囲が大きくなると、要求される空きスペースが必要になるためシートが煩雑になり、管理が困難になる可能性があります。

方法2: COUNTA関数とFILTER関数を組み合わせる

この方法はMicrosoft 365や最新のOfficeで利用可能な新しい関数を使用しています。

計算式の完成例

以下の式を使用して、条件に一致する行の数をカウントします。範囲A2からA15までのセルのうち、少なくとも一つの条件を満たす行の数をカウントします。

=COUNTA(FILTER(A2:A15,(B2:B15="A")+(C2:C15="A")+(D2:D15="A")+(E2:E15="A")+(F2:F15="A")))

この式はCOUNTA関数とFILTER関数を組み合わせて使用しています。以下で詳しく解説します。

各部分の解説

  1. COUNTA関数：COUNTA関数は、指定した範囲内の非空のセルの数をカウントします。
  2. FILTER関数：FILTER関数は、指定した範囲から特定の条件を満たすセルをフィルタリングして返します。
  3. 条件式：以下の5つの条件式は、「OR」条件を表しています。これは、任意の列（B, C, D, E, F）のいずれかで”A”と等しい値を持つ行を見つけるためです。FILTER関数では「(論理式)+」の計算式はOR条件処理を意味します。「(論理式)*」はAND条件処理です。
                (B2:B15="A")
            + (C2:C15="A")
            + (D2:D15="A")
            + (E2:E15="A")
            + (F2:F15="A")

動作の仕組み

  1. まず、各列で”A”と等しい値を持つセルを見つけるための条件式を作成します。
  2. これらの条件式を”+”（プラス）演算子で結合することで、論理OR条件を作成します。この条件は、任意の列で”A”と一致するセルを見つけるために使用されます。なお、”*”（アスタリスク）演算子は論理AND条件となります。
  3. 次に、FILTER関数を使用して、A2からA15までの範囲でこれらの条件を満たす行にあるセルをフィルタリングします。
  4. 最後に、COUNTA関数を使用して、フィルタリングされたA列のセルの数をカウントします。

結論

この式は、範囲A2からA15までのセルのうち、少なくとも一つの列（BからF）で”A”という値を持つ行の数をカウントします。このようにして、OR条件を用いてデータを集計することができます。

長所と短所

この方法は単一の式のみでOR条件を表現できる点で優れています。空きスペースは不要です。

Excelの古いバージョン(Office2016依然のバージョン)では利用できない点がデメリットとなります。また複雑な条件の場合、式が非常に長くなる可能性があります。

コメント