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

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

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

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

Задача

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

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

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

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

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

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

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

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

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