Подсчет в списках содержащих значение Пустой текст ("") в MS EXCEL

Ячейка, содержащая значение Пустой текст (""), обладает замечательным свойством: ячейка выглядит пустой. К сожалению, значение Пустой текст несколько усложняет подсчет значений.

В статье Подсчет пустых ячеек было показано, что некоторые функции и средства EXCEL интерпретирует ячейку, содержащую Пустой текст (две кавычки ("") между которыми ничего нет), как пустую ячейку, а другие, как содержащую текстовое значение. Вообще, значение Пустой текст считается текстом с нулевой длиной.

При подсчете значений в диапазонах с ячейками содержащих Пустой текст могут возникнуть непредвиденные сложности. Приведем пример.

Задача

Предположим, имеется диапазон с текстовыми значениями и необходимо в соседнем столбце с помощью формулы =ЕСЛИ(ЛЕВСИМВ(A2;7)="Красный";A2;"") вывести только те значения, которые начинаются со слова Красный (см. файл примера).

Теперь подсчитаем с помощью функции СЧЁТЗ(), сколько значений мы отобрали в правом столбце. Оказывается 8! Дело в том, что функция СЧЁТЗ() воспринимает значение Пустой текст, возвращаемый формулой =ЕСЛИ(ЛЕВСИМВ(A2;7)="Красный";A2;""), как текстовую строку.

Как же подсчитать текстовые значения с ненулевой длиной строки? Для этого можно использовать, например, формулу
=СУММПРОИЗВ(--(C2:C11>""))

Теперь немного о сравнении значений в EXCEL. Что больше: число 5 или текстовая строка «Пять»? По правилам математики – сравнение некорректно, по версии EXCEL - любая текстовая строка больше любого числа!

Итак, формула =СУММПРОИЗВ(--(C2:C11>"")) подсчитает текстовые значения с ненулевой длиной строки; формула =СУММПРОИЗВ(--(C2:C11<"")) подсчитает все числа (и даты); формула =СУММПРОИЗВ(--(C2:C11="")) подсчитает все пустые ячейки и ячейки, содержащие Пустой текст.

Естественно, этими формулами стоит пользоваться, только когда в диапазоне могут быть ячейки, содержащие Пустой текст. Если таких ячеек гарантировано нет, то для подсчета текстовых значений используйте идеи из статей Подсчет текстовых значений, а для подсчета чисел – из статьи Подсчет ячеек содержащих числа.

Чтобы закрыть тему, рассмотрим следующие ситуации:

1. Диапазон (С2:С11) содержит текстовые значения, пустые ячейки и ячейки со значением Пустой текст.

Функция

Подсчет ячеек с текстом ненулевой длины

Подсчет ячеек со значением Пустой текст

Подсчет пустых ячеек

=СУММПРОИЗВ(
--(C2:C11>""))

да

нет

нет

=СЧЁТЕСЛИ(C2:C11;"*?")

да

нет

нет

=СЧЁТЗ(C2:C11)

да

да

нет

=СУММПРОИЗВ(
--ЕТЕКСТ(C2:C11))

да

да

нет

=СЧЁТЕСЛИ(C2:C11;"*")

да

да

нет

=СУММПРОИЗВ(
--(C2:C11=""))

нет

да

да

=СЧЁТЕСЛИ(
C2:C11;"<>"&"*?")

нет

да

да

=СЧИТАТЬПУСТОТЫ(C2:C11)

нет

да

да

2. Диапазон (С2:С11) содержит только числа, пустые ячейки и ячейки со значением Пустой текст

Функция

Подсчет ячеек с числами

Подсчет ячеек со значением Пустой текст

Подсчет пустых ячеек

=СЧЁТ(C2:C11)

да

нет

нет

=СУММПРОИЗВ(
--(C2:C11<""))

да

нет

нет

=СЧЁТЗ(C2:C11)

да

да

нет

=СЧЁТЕСЛИ(C2:C11;"*")

нет

да

нет

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)

Комментарии

Максимp (не проверено)

Спасибо за инфо, полезное сравнение