Количество ячеек не соответствующих нескольким значениям

От простой задачи, когда необходимо было посчитать количество ячеек в диапазоне, перейдем к следующему этапу. Как посчитать количество ячеек не соответствующих нескольким значениям. То есть теперь будем проверять не одно, а несколько значений.

Исходные данные

Пусть в диапазоне ячеек от А5 до А16 содержатся буквы русского алфавита. Это будет искомый диапазон в котором будем проверять значения. В ячейках D5 – D7 будут содержаться значения, которые будут проверяться в искомом диапазоне.

Количество ячеек не соответствующих нескольким значениям

Формула

Перейдем к формуле, в ячейке F5 введем такую формулу:

=СЧЁТЗ(B5:B16)-СУММПРОИЗВ(СЧЁТЕСЛИ(B5:B16;D5:D7))

Работает эта формула достаточно просто… Хотя сделаем для начала небольшое лирическое отступление. Неважно какую задачу пытаемся решить, да собственно не особо важно где. Практический у любой задачи может быть как минимум два решения. Одно из которых, что называется «в лоб», другое будет более универсальным. Решения в лоб, достаточно просты, но сильно ограниченны рамками текущих значений или условий. Универсальные же решения опираются в первую очередь на условия задачи, исходные данные второстепенны, универсальные решения на то и универсальны, что могут давать правильный результат при любых начальных значениях. Что до данной задачи, то в ней можно было бы опираться на привычную функцию «СЧЁТЕСЛИМН», позволяющую задавать несколько условий, но если понадобится изменять условия, те менять значения для поиска, придется переписывать формулу. Этим функция «СЧЁТЕСЛИМН» сильно ограничивает использование.

Итак, для начала считаем количество ячеек в исходном диапазоне, в которых есть значения, то есть считаем количество не пустых ячеек. Для функции: СЧЁТЗ(B5:B16) – результат будет равен 12. Соответствие значения из набора «Значения», набору «Диапазон» проверяется в функции СЧЁТЕСЛИ(B5:B16;D5:D7). Но если бы наша формула выглядела как

=СЧЁТЗ(B5:B16)- СЧЁТЕСЛИ(B5:B16;D5:D7)

То результат был бы – 10. Дело в том что «СЧЁТЕСЛИ» посчитал бы все значения, но использовалось бы только первое. Первое значение диапазона «Значения» — буква «А», в наборе «Диапазон» она встречается два раза, соответственно 10 получается вычитанием 2 из общего количества значений. Для того что бы использовать все результаты выполнения функции «СЧЁТЕСЛИ» вносим ее как параметр для функции «СУММПРОИЗВ».

Особенность возвращения значений некоторыми функциями

Как выглядит определение функции «СЧЁТЕСЛИ», в документации к Excel приводится такое определение – «СЧЁТЕСЛИ(диапазон; критерий)». Казалось бы «критерий» — это нечто простое, значение или ссылка на одну ячейку. Но в критерии можно указать не только ссылку на одну ячейку, можно сослаться и на диапазон, как в нашем примере.

Чудеса начинаются с возвращаемыми значениями. Если критерий, простое условие или ссылка на одну ячейку, функция вернет простой результат из одного значения. Но если критерий это ссылка на диапазон ячеек, то результат будет представлять из себя массив значений. Где первый результат будет являться результатом выполнения функции для первого значения из критерия. Второй результат будет получен при вычислении второго значения критерия и так далее.

Именно по этому, мы должны использовать дополнительно функцию «СУММПРОИЗВ», которая просуммирует все значения массива результатов выполнения функции «СЧЁТЕСЛИ» и вычтет его из общего количества значений.

Добавить комментарий