Общая формула
=COUNTIF(range,""&high)
Резюме
Чтобы подсчитать значения ячеек, которые не находятся между двумя числами, вы можете использовать функцию СЧЁТЕСЛИ. В показанном примере формула в ячейке K5, скопированная вниз, имеет следующий вид:
=COUNTIF(C5:G5,""&J5)
В каждой новой строке эта формула возвращает количество значений не между нижним и верхним значениями в столбцах I и J.
Объяснение
Цель этого примера - подсчитать числовые значения, записанные за 5 дней, которые не попадают между двумя числами, низким значением и высоким значением. Другими словами, для подсчета значений, находящихся «вне допустимого диапазона». Обратите внимание, что каждая строка, помеченная как AG, имеет свой собственный нижний и верхний предел в столбцах I и J.
Сначала вы можете подумать об использовании функции СЧЁТЕСЛИМН с двумя критериями. Однако, поскольку СЧЁТЕСЛИМН объединяет критерии с логикой И, в этом сценарии его нельзя использовать с двумя критериями. Логика «меньше меньшего значения И больше большего значения» всегда терпит неудачу, и результат всегда будет нулевым. Вместо этого нам нужна логика ИЛИ.
Одно простое решение - использовать функцию СЧЁТЕСЛИ дважды следующим образом:
=COUNTIF(C5:G5,""&J5)
Первый СЧЁТЕСЛИ подсчитывает значения ниже значения в I5, а второй СЧЁТЕСЛИ подсчитывает значения выше значения в J5. При сложении эти два результата правильно обрабатывают требуемую логику: меньше I5 ИЛИ больше J5. Обратите внимание на то, что операторы «больше» («>») и «меньше» («<») связаны со ссылками на ячейки с помощью оператора амперсанда (&), что является особенностью функций RACON.
С SUMPRODUCT
Чуть более элегантное решение - использовать функцию СУММПРОИЗВ с двумя логическими выражениями, подобными этому:
=SUMPRODUCT((C5:G5J5))
Обратите внимание, что нам не нужно использовать конкатенацию со ссылками на ячейки, как с COUNTIF выше; стандартные выражения работают нормально.
Это пример использования логической алгебры с добавлением (+), которое создает логику ИЛИ. Когда эти выражения вычисляются, у нас есть два массива значений ИСТИНА и ЛОЖЬ, например:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,TRUE)+(FALSE,FALSE,TRUE,FALSE,FALSE))
Математическая операция автоматически переводит значения ИСТИНА и ЛОЖЬ в 1 и 0. Результат можно визуализировать так:
=SUMPRODUCT((0,0,0,0,1)+(0,0,1,0,0))
В результате получается один массив, содержащий две единицы:
=SUMPRODUCT((0,0,1,0,1))
Имея только один массив для обработки, SUMPRODUCT суммирует элементы в массиве и возвращает окончательный результат 2.
