COUNTBLANKとCOUNTAを足すとセル数を超える理由

LWP | COUNTBLANKとCOUNTAを足すとセル数を超える理由

COUNTBLANKとCOUNTAを足すとセル数を超える理由

図解1|5セルなのに、件数を足すと6になる?

5セルに対するCOUNTBLANKの2とCOUNTAの4で、空文字を返す数式が重なる図

図1|5セルなのに、件数を足すと6になる?

解説1|5セルなのに、件数を足すと6になる?

ストーリー

ワカサギさん「確認表には空欄2、入力あり4とあります。でも、対象は5セルですよね。」

フナさん「COUNTBLANKとCOUNTAは、同じセルを数える場合があります。中身を一つずつ見ましょう。」

今回の対象はA2:A6の5セルである。未入力セル、空文字を返す数式、半角空白1文字、数値0、文字列を一つずつ用意すると、表示が似ていても数え方が違うことを確かめられる。

入力例と集計結果

セル

実際に用意する内容

A2

内容をクリアした未入力セル

A3

数式 =""

A4

半角空白を1文字だけ直接入力

A5

数値の0

A6

文字列「要確認」

別のセルに入れる式

結果

=COUNTBLANK(A2:A6)

2

=COUNTA(A2:A6)

4

=ROWS(A2:A6)

5

読むポイント

2と4を足すと6だが、対象範囲が6セルに増えたわけではない。空文字を返すA3の数式が両方の集計に含まれている。二つの関数を、重ならない「空欄」と「入力済み」の分類だと考えたことが、合計のずれにつながる。

図解2|同じ空欄の表示でも、中身が違う

未入力・空文字を返す数式・直接入力した半角空白の内容と判定を比べる図

図2|同じ空欄の表示でも、中身が違う。

解説2|同じ空欄の表示でも、中身が違う

ストーリー

ワカサギさん「A2もA3も何も見えません。説明には、どちらも未入力と書いていました。」

フナさん「A3には数式があります。表示と数式、文字列の長さを分けて確認しましょう。」

セルに見える表示は、セルに何が入っているかを全部は示さない。未入力セルと、結果が長さ0の文字列になる数式は、表示だけでは区別しにくい。

未入力・空文字・空白文字の比較

セル

内容

確認式

結果

A2

中身なし

=ISBLANK(A2)

TRUE

A3

数式 =""

=ISBLANK(A3)

FALSE

A4

半角空白1文字を直接入力

=LEN(A4)

1

ISBLANKは空のセルを参照したときにTRUEを返す。A3は結果に文字が見えなくても数式が入っているためFALSEである。A4は空文字ではなく、長さ1の文字列で、ISBLANK(A4)もFALSEになる。

読むポイント

A4の空白文字を見やすくするため「[空白]」などと説明する場合も、その記号を実際に入力するわけではない。この例では半角空白を一つ直接入れる。数式バーで内容を確かめ、必要ならLENで文字列の長さを調べる。

何も表示されないセルを全て消去するのは避けよう。必要な計算式まで消すと、件数は合って見えても表の働きが変わってしまう。

図解3|COUNTBLANKは、未入力と空文字の結果を数える

COUNTBLANKが数えるA2とA3を5セルの中で示す図

図3|COUNTBLANKは、未入力と空文字の結果を数える。

解説3|COUNTBLANKは、未入力と空文字の結果を数える

ストーリー

ワカサギさん「COUNTBLANKの2は、未入力の2セルという説明では正確でないのですね。」

フナさん「はい。この例では未入力が1セル、空文字を返す数式が1セルです。」

COUNTBLANKは、未入力セルに加えて、数式が空文字を返すセルも数える。数値0や、半角空白1文字は、この例の空欄には含まれない。

どのセルが数えられるか

セル

内容

COUNTBLANKが数えるか

A2

未入力

数える

A3

数式 =""

数える

A4

半角空白1文字

数えない

A5

数値0

数えない

A6

要確認

数えない

別のセルへ=COUNTBLANK(A2:A6)と入力すると結果は2になる。ここで数えているのは関数が空欄と扱うセルであり、人がまだ入力していない箇所だけを数えた結果ではない。

読むポイント

入力の進み具合を調べるなら、どの列が手入力欄で、どの列が数式欄かも確認する。空文字が正常な計算結果となる列を含めれば、結果2がそのまま未処理2件を意味するとは限らない。集計対象の範囲と数える条件を、報告の説明にも残す。

図解4|COUNTAも、空文字を返す数式を数える

空文字を返す数式A3がCOUNTBLANKとCOUNTAの両方に含まれることを示す図

図4|COUNTAも、空文字を返す数式を数える。

解説4|COUNTAも、空文字を返す数式を数える

ストーリー

フナさん「COUNTAもA3を数えます。二つの件数を同じ表で比べてみましょう。」

ワカサギさん「それなら、足した6を『全セル数』と書いたところを直せますね。」

COUNTAは、空文字を返す数式も値のあるセルとして数える。今回の範囲ではA2だけを数えず、A3・A4・A5・A6が対象になる。

二つの関数の重なり

表の1は数える、0は数えないことを表す。

セル

COUNTBLANK

COUNTA

A2:未入力

1

0

A3:数式 =""

1

1

A4:半角空白1文字

0

1

A5:数値0

0

1

A6:要確認

0

1

合計

2

4

読むポイント

A3が両方の列に1を持つため、件数を足すと一つのセルを二度数えることになる。元データに重複行があるのではなく、二つの集計条件が重なっている。

COUNTAはエラー値も数えるので、「入力あり4」という短い表記から、正しい値が4件そろったとまで読み取らない。何を有効な入力とするかは、項目の目的に合わせた別の条件である。

図解5|数えたい状態を決めて、式と説明を選ぶ

数えたいセルの状態とISBLANK・COUNTBLANK・COUNTAを対応させる図

図5|数えたい状態を決めて、式と説明を選ぶ。

解説5|数えたい状態を決めて、式と説明を選ぶ

ストーリー

ワカサギさん「空欄2セルの内訳を書けば、数式のあるセルも含むと伝わりますね。」

フナさん「必須項目の入力確認なのか、空の計算結果を数えたいのかも、先に決めましょう。」

件数に付ける名前は、関数の判定と対応させる必要がある。未入力セルだけを確認する場合と、空文字の結果も含む場合では、同じ「空欄」という表記でも意味が違う。

目的と確認方法

確認したいこと

今回の確認方法

一つのセルが未入力か

=ISBLANK(A2)でTRUE/FALSEを確認

未入力と空文字の結果を数える

=COUNTBLANK(A2:A6)

空文字を返す数式も含め、値のあるセルを数える

=COUNTA(A2:A6)

報告文は「COUNTBLANKによる空欄2セル。うち1セルは空文字を返す数式」とすれば、今回の内訳まで伝えられる。入力完了を判定する表では、必須項目、数式セル、許容値を決め、その条件を別に照合する。

まとめと今回の仕掛け

同じ5セルでも、二つの関数は重なる条件で数えた。件数が合わないときは、表示だけを眺めず、数式・値・数える定義を一緒に確認しよう。合計を5に合わせるために必要な数式を削除することは、解決にならない。

出典

Microsoft SupportのCOUNTBLANK、COUNTA、IS関数をもとに独立した練習例を構成した。