=СЧЁТЕСЛИ(rng;»<>*txt*»)
Для подсчета количества ячеек, которые не содержат определенный текст, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), rng является диапазон ячеек, TXT представляет собой текст, который клетки не должны содержать, и «*» является подстановочным знаком, соответствующим любому количеству символов.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(B5:B12;»<>*a*»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые не содержат «а» путем сопоставления содержимого каждой ячейки с шаблоном «<> *a*», который поставляется в качестве критерия. Символ «*» (звездочка) является подстановочным в Excel, что означает «совпадают с любым количеством символов» и «<>» означает «не равно», так что эта модель будет считать любую ячейку, которая не содержит «а» в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитываются как число.
Вы можете легко настроить эту формулу, чтобы использовать содержимое другой ячейки, которая содержит текст, который вы не хотите рассчитывать. Общий вид формулы выглядит следующим образом:
С ссылкой на ячейку
=СЧЁТЕСЛИ(rng;»<>*»&A1&»*»)
Исключить пробелы
Для исключения пустых ячеек, вы можете переключиться на СЧЁТЕСЛИМН и добавить еще один критерий:
=СЧЁТЕСЛИМН(range;»<>*a*»;range;»?*»)
Количество ячеек, которые не содержат ошибки
=СУММПРОИЗВ(—НЕ(ЕОШ(rng)))
Для подсчета количества ячеек, содержащих ошибки, вы можете использовать функции ЕОШ и НЕ, завернутые в функции СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон, в котором вы хотели бы рассчитывать ячейки без ошибок.
В примере, активная ячейка содержит следующую формулу:
=СУММПРОИЗВ(—НЕ(ЕОШ(B5:B9)))
СУММПРОИЗВ принимает один или несколько массивов и вычисляет сумму произведений соответствующих чисел. Если только один массив поставляется, он просто суммирует элементы в массиве.
Функция ЕОШ вычисляется для каждой ячейки в диапазоне. Без функции НЕ, результат представляет собой массив значений, равным ИСТИНА или ЛОЖЬ:
{ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ}
С помощью функции НЕ результат:
{ЛОЖЬ, ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА}
Это соответствует ячейкам, которые не содержат ошибок в диапазоне.
— Оператор (так называемый двойной унарный) превращает ИСТИНА / ЛОЖЬ значения в 0 и 1. Полученный массив выглядит следующим образом:
{0; 1; 0; 1; 1}
СУММПРОИЗВ затем суммирует элементы в этом массиве и возвращает общее число, которое в данном примере является числом 3.
Вы можете также использовать функцию СУММ для подсчета ошибок. Структура формулы такая же, но она должна быть введена как формула массива (нажмите Ctrl + Shift + Enter, а не просто Enter). После ввода, формула будет выглядеть следующим образом:
{=СУММ(—НЕ(ЕОШ(B5:B9)))}
Не вводите фигурные скобки {}, они вводятся автоматически при нажатии Ctrl + Shift + Enter.
Количество ячеек, которые не содержат цифры
Для подсчета количества ячеек в диапазоне, которые не содержат числа, можно использовать формулу СУММПРОИЗВ:
=СУММПРОИЗВ(—НЕ(ЕЧИСЛО(B5:B9)))