Подсчет в списках содержащих значение Пустой текст ("") в 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;"*")

нет

да

нет

Комментарии
Только для авторизованных пользователей

(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved