Excelで特殊カウント: 特定の倍数を持つセルを見つけ出そう

Excelで特定の条件を満たすデータを素早く見つけ出すことは、効率的な分析には欠かせない技術です。一般的にはCOUNTIF関数やCOUNTIFS関数を用いるのが簡単でしょう。これらの関数には「検索条件」という引数があり、その引数「で以上・以下・含む・含まない……」、といった条件を設定して適合するセルを数えることができます。しかしながらCOUNTIF関数やCOUNTIFS関数では使用できない論理条件でカウントした場合には別の方法を模索する必要があります。

今回は、ある範囲内で特定の倍数を持つセルを数える方法について解説します。以下は解説用のサンプル表です(A1:A21)。

基本的な計算式

まずは基本から始めます。特定の倍数を持つセルを数える基本的な計算式は以下の通りです。

=SUMPRODUCT(--(MOD(範囲,倍数)=0))

この計算式では、SUMPRODUCT関数とMOD関数を利用しています。範囲内の各セルが指定した倍数で割り切れるかどうかを判定し、割り切れるセルの数をカウントします。

--(ダブル単項マイナス)は、TRUE/FALSEの値を1/0に変換するために使用されます。そして、SUMPRODUCT関数は、1の値を合計します。

具体例を見ましょう。例えば、A2:A21の範囲で7の倍数を持つセルを数えたい場合、以下の計算式を使います。

=SUM(--(MOD(A2:A21,7)=0))

ここで、MOD関数は各セルの値を7で割った余りを計算し、その結果が0であるかどうかをチェックしています「MOD(A2:A21,7)」。余りが0である場合、そのセルは7の倍数であると言えます。--は、TRUE/FALSEの値を1/0に変換するために使用されます。そして、SUMPRODUCT関数は、1の値を合計して、7の倍数であるセルの数を返します。最終集計にはSUM関数を用いることもできますが、SUMPRODUCT関数ならば次節のように条件を複数にすることが可能になります。

なお、条件を反転させたい場合は「–」のあとに「NOT」を付けて論理式をNOT関数で囲んでいる状態にしてください。

=SUM(--NOT(MOD(A2:A21,7)=0))

複数の条件を満たすセルを数える

次に、複数の条件を満たすセルを数える計算式について見てみましょう。例えば、7の倍数でかつ正の値を持つセルを数えたい場合、以下の計算式を使います。

=SUMPRODUCT(--(MOD(範囲,7)=0),--(範囲>0))

この計算式では、2つの条件を同時にチェックし、両方の条件を満たすセルの数をカウントします。

では、実際の表を使ってこの計算式の利用例を見てみましょう。以下の表では、セルA2:A21の範囲で7の倍数でかつ正の値を持つセルを数えます。

=SUMPRODUCT(--(MOD(A2:A21,7)=0),--(A2:A21>0))

この計算式を利用することで、指定した条件を満たすセルの数を瞬時に見つけ出すことができます。

ExcelのSUMPRODUCT関数と「—」の組み合わせは、データ分析の現場で非常に役立ちます。特定の条件を満たすデータを素早く見つけ出す技術をマスターし、日々の業務をさらに効率化しましょう！