【Excel】COUNTIFS関数で空白セルを条件に含める!Excelの空欄カウントと除外の使い分け

Excelで特定の条件に合うデータの個数を数えたい場合、COUNTIFS関数が便利です。しかし、空白セルを条件に含める方法や、逆に空白セルを除外する方法が分からず、困る場面もあるでしょう。この記事では、COUNTIFS関数を使って空白セルを条件に含める方法と、空白セルを除外する方法を具体的に解説します。

COUNTIFS関数を使いこなせば、より詳細なデータ分析が可能になります。この記事を読めば、空白セルを含むデータ集計を正確に行えるようになります。

COUNTIFS関数で空白セルを条件に含める仕組み

COUNTIFS関数は、複数の条件を指定して、すべての条件を満たすセルの個数を数える関数です。空白セルを条件に含める場合、Excelは空白を特定の文字列として認識します。この性質を利用して、COUNTIFS関数で空白セルをカウントすることが可能です。

具体的には、条件として空文字列「=””」を指定します。これにより、指定した範囲内の空白セルのみがカウント対象となります。

COUNTIFS関数で空白セルをカウントする手順

ここでは、COUNTIFS関数を使って空白セルのみをカウントする具体的な手順を説明します。

1. COUNTIFS関数の入力
   結果を表示したいセルを選択し、数式バーに「=COUNTIFS(」と入力します。
2. 最初の条件範囲の指定
   空白セルをカウントしたい列全体、またはセル範囲を指定します。例えば、A列のA1からA100までを対象とする場合は「A1:A100」と入力します。
3. 空白セルの条件指定
   最初の条件範囲に対する条件として、空文字列「=””」を入力します。数式は「=COUNTIFS(A1:A100,””」となります。
4. 関数の終了
   閉じ括弧「)」を入力して、数式を確定します。数式は「=COUNTIFS(A1:A100,””」となります。

この数式を入力すると、指定した範囲A1からA100の中で、空白となっているセルの個数が表示されます。

COUNTIFS関数で空白セルを除外する手順

次に、空白セル以外のセルをカウントする、つまり空白セルを除外する手順を説明します。これは、データが存在するレコード数を数えたい場合などに役立ちます。

1. COUNTIFS関数の入力
   結果を表示したいセルを選択し、数式バーに「=COUNTIFS(」と入力します。
2. 最初の条件範囲の指定
   空白セルを除外してカウントしたい列全体、またはセル範囲を指定します。例えば、A列のA1からA100までを対象とする場合は「A1:A100」と入力します。
3. 空白セルを除外する条件指定
   最初の条件範囲に対する条件として、空文字列ではないことを示す「<>“”」を入力します。数式は「=COUNTIFS(A1:A100,”<>“”」となります。
4. 関数の終了
   閉じ括弧「)」を入力して、数式を確定します。数式は「=COUNTIFS(A1:A100,”<>“”」となります。

この数式を入力すると、指定した範囲A1からA100の中で、空白ではないセルの個数が表示されます。

COUNTIFS関数で複数の条件と空白を組み合わせる

COUNTIFS関数は、複数の条件を指定できるため、空白セルを条件に含めたり除外したりする際に、他の条件と組み合わせることが可能です。これにより、より複雑な集計が行えます。

例1:特定のカテゴリで空白のデータをカウントする

例えば、B列に商品カテゴリ、A列に商品名が入っているとします。「飲料」カテゴリの中で、商品名が空白の数をカウントしたい場合を考えます。

1. COUNTIFS関数の入力
   数式バーに「=COUNTIFS(」と入力します。
2. 1つ目の条件範囲(カテゴリ)の指定
   B列のB1からB100までを対象範囲として指定します。
3. 1つ目の条件(カテゴリ名)の指定
   条件として「”飲料”」を指定します。
4. 2つ目の条件範囲(商品名)の指定
   A列のA1からA100までを対象範囲として指定します。
5. 2つ目の条件(空白)の指定
   条件として空白「=””」を指定します。
6. 関数の終了
   閉じ括弧「)」を入力して数式を確定します。数式は「=COUNTIFS(B1:B100,”飲料”,A1:A100,””」となります。

これにより、「飲料」カテゴリに属し、かつ商品名が空白であるデータの個数を取得できます。

例2:特定のカテゴリで空白でないデータをカウントする

次に、同じく「飲料」カテゴリの中で、商品名が空白でない(つまり商品名が入力されている)データの数をカウントする場合を考えます。

1. COUNTIFS関数の入力
   数式バーに「=COUNTIFS(」と入力します。
2. 1つ目の条件範囲(カテゴリ)の指定
   B列のB1からB100までを対象範囲として指定します。
3. 1つ目の条件(カテゴリ名)の指定
   条件として「”飲料”」を指定します。
4. 2つ目の条件範囲(商品名)の指定
   A列のA1からA100までを対象範囲として指定します。
5. 2つ目の条件(空白でない)の指定
   条件として空白でない「<>“”」を指定します。
6. 関数の終了
   閉じ括弧「)」を入力して数式を確定します。数式は「=COUNTIFS(B1:B100,”飲料”,A1:A100,”<>“”」となります。

これにより、「飲料」カテゴリに属し、かつ商品名が入力されているデータの個数を取得できます。

COUNTIFS関数とCOUNTIF関数の違い

COUNTIFS関数とCOUNTIF関数は、どちらもセルの個数を数える関数ですが、指定できる条件の数に違いがあります。

項目	COUNTIF関数	COUNTIFS関数
条件の数	1つのみ	複数指定可能
空白セルのカウント	COUNTIF(範囲,””)	COUNTIFS(範囲,””)
空白セル除外	COUNTIF(範囲,”<>“”)	COUNTIFS(範囲,”<>“”)
複数条件との組み合わせ	不可	可能

COUNTIF関数は単一の条件でのカウントに限定されますが、COUNTIFS関数は複数の条件をAND(かつ)の論理で組み合わせることができます。空白セルを単独でカウントする場合や除外する場合はCOUNTIF関数でも可能ですが、他の条件と組み合わせて集計したい場合はCOUNTIFS関数を使用する必要があります。

COUNTIF/COUNTIFS関数で空白を扱う際の注意点

COUNTIF関数およびCOUNTIFS関数で空白セルを扱う際には、いくつかの注意点があります。

空白とみなされないケース

数式の結果として表示される「0」や、スペース1文字が入力されているセルは、厳密には空白セルとはみなされません。これらのセルを空白としてカウントしたい場合は、条件を工夫する必要があります。

スペース1文字のセルを空白としてカウントしたい場合

スペース1文字のセルも空白としてカウントするには、OR条件で「=””」と「”=” “」を組み合わせる必要があります。しかし、COUNTIF/COUNTIFS関数では直接OR条件を指定できません。この場合、一度作業列でスペースを削除してからCOUNTIFS関数を使うか、SUMPRODUCT関数などの別の関数を使う必要があります。

数式の結果「0」を空白としてカウントしたい場合

数式の結果として「0」が表示されているセルは、空白セルとは区別されます。もし「0」も空白としてカウントしたい場合は、その数式の結果を空文字列に変換するIFERROR関数などを組み合わせる必要があります。例えば、「=IFERROR(数式,””)」のように設定されたセルをCOUNTIFS関数でカウントします。

Excelのバージョンによる違い

COUNTIF関数およびCOUNTIFS関数は、Excelの比較的古いバージョンから利用可能です。そのため、Excel 2019やMicrosoft 365など、ほとんどの環境で同様の挙動を示します。特別なバージョンによる制限はありません。

空文字列と見なされない場合

条件範囲に指定したセルに、見た目上は空白でも、実際にはスペースや改行コードなどが含まれている場合があります。これらのセルは、COUNTIFS関数では空白(=””)とはみなされません。

見えない文字の確認方法

これらのセルを確認するには、対象のセルを選択し、数式バーを確認します。もしスペースなどが含まれていれば、数式バーに表示されます。また、CLEAN関数やTRIM関数を使って、これらの不要な文字を取り除くことも可能です。

TRIM関数を使った空白の除外

例えば、A列のデータから余分なスペースを取り除き、B列に表示する場合、B1セルに「=TRIM(A1)」と入力し、下にフィルコピーします。その後、B列のデータに対してCOUNTIFS関数で空白でないものをカウントすれば、実質的な空白セルのみをカウントできます。

COUNTIFS関数でのエラー対処

COUNTIFS関数でエラーが発生した場合、原因はいくつか考えられます。

#VALUE!エラー

条件範囲のセル数と条件の数が一致しない場合や、条件に配列数式が指定されている場合に発生することがあります。各条件範囲のセル数が正しく指定されているか確認してください。

#N/Aエラー

これはCOUNTIFS関数では直接発生しませんが、他の関数と組み合わせて使用した場合などに発生する可能性があります。数式全体を見直し、各関数の引数が正しいか確認してください。

計算結果が意図しない値になる場合

条件の指定方法が誤っている可能性があります。特に、文字列を条件にする場合は、必ずダブルクォーテーション(“”)で囲む必要があります。また、ワイルドカード(*や?)の使用方法も確認してください。

まとめ

COUNTIFS関数を使えば、Excelで空白セルを条件に含めたり、逆に空白セルを除外したりする複雑なカウントが可能です。条件に「=””」を指定すれば空白セルのみを、「<>“”」を指定すれば空白以外のセルをカウントできます。

これらのテクニックを習得することで、データ分析の精度が向上します。さらに、SUMPRODUCT関数など他の関数と組み合わせることで、より高度な集計も実現できるでしょう。

📊

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

🏆

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