=СЧЁТЕСЛИ(rng;»<>X»)
Для подсчета количества ячеек, содержащих значения не равных определенному значению, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, а Х представляет собой значение, которое вы не хотите рассчитывать. Все остальные значения будут учитываться.
В примере, активная ячейка содержит следующую формулу:
=СЧЁТЕСЛИ(D5:D11;»<>Готово»)
СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые отвечают критериям.
В примере, мы используем «<>» (логический оператор «не равно») для подсчета ячеек в диапазоне D5:D11, которые не равны «Готово». СЧЁТЕСЛИ возвращает число в качестве результата.
СЧЁТЕСЛИ не чувствительна к регистру. В этом примере слово «готово» может появиться в любой комбинации прописных / строчных букв.
Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:
= СЧЁТЕСЛИ (rng;»<>»&А1)
Если значение в ячейке A1 равно «100», критерии будут «<> 100» после конъюнкции, и СЧЁТЕСЛИ будет считать ячейки не равные 100.
Количество ячеек, не равных нескольким критериям
= СЧЁТЗ (диапазон) — СУММПРОИЗВ (СЧЁТЕСЛИ (диапазон; значения))
Для подсчета ячеек, не равных многим критериям (т.е. не равны х, у, z, и т.д.), вы можете использовать формулу, основанную на СЧЁТЕСЛИ, СУММПРОИЗВ и СЧЁТЗ.
В показанном примере, формула в Н5:
=СЧЁТЗ(B4:C9)-СУММПРОИЗВ(СЧЁТЕСЛИ(B4:C9;E5:E7))
Если у вас есть всего лишь пару значений, которые вы не хотите рассчитывать, вы можете использовать функцию СЧЁТЕСЛИМН следующим образом:
= СЧЁТЕСЛИМН (диапазон; «<> яблоко»; диапазон; «<> оранжевый»)
Но это не удобно, когда у вас есть список многих значений, потому что вам придется добавить еще пару диапазонов / критериев, чтобы для каждого значения не рассчитывалось, что вы хотите. Было бы намного проще, создать список и передать ссылку на этот список в качестве части критериев. Это именно то, что делает формула на этой странице.
Эта формула использует именованный диапазон «Критерии» (E5: E7) для хранения значений, которые мы не хотим рассчитывать.
Мы начинаем путем подсчета всех значений в диапазоне с функцией СЧЁТЗ:
=СЧЁТЗ(B4:C9)
Далее, мы генерируем подсчет всех значений, которые мы не хотим считать с помощью СЧЁТЕСЛИ, так:
СЧЁТЕСЛИ(B4:C9;E5:E7)
Поскольку диапазон содержит несколько значений, СЧЁТЕСЛИ будет возвращать несколько результатов. В примере, мы получаем обратно массив значений, как этот:
{1;2;2}
и мы используем СУММПРОИЗВ, чтобы суммировать все элементы в массиве, получаем 5. Это число затем вычитается из первоначальной общей суммы с получением конечного результата.
Использование СУММПРОИЗВ вместо СУММ избавляет от необходимости использовать формулу массива.
Количество ячеек, не равных х или у
=СЧЁТЕСЛИМН(rng;»<>x»;rng;»<>y»)
Для подсчета клеток не равных тому или иному, вы можете использовать функцию СЧЁТЕСЛИМН с несколькими критериями.
В приведенном примере есть простой список цветов в столбце B. Есть всего 6 клеток с цветом, некоторые из них являются дубликатами.
Для того, чтобы подсчитать количество ячеек, которые не равны «красный» или «синий», нужна формула в Е5:
=СЧЁТЕСЛИМН(B5:B10;»<>красный»;B5:B10;»<>синий»)
В этом примере «rng» именованный диапазон, который равен B5:B10.
Функция СЧЁТЕСЛИМН подсчитывает клетки, которые удовлетворяют одному или нескольким условиям. Все условия должны быть удовлетворены, чтобы подойти для подсчета ячеек.
Ключевым в данном случае является использование оператора «не равно», который пишется <>.
Чтобы добавить еще критерии, нужно просто добавить другой диапазон / критерии пару аргументов.
Альтернатива с СУММПРОИЗВ
Функция СУММПРОИЗВ может также рассчитывать клетки, которые отвечают нескольким условиям.
Для приведенного выше примера, синтаксис для СУММПРОИЗВ является:
=СУММПРОИЗВ((rng<>»синий»)*(rng<>»зеленый»))