Подсчёт количества ячеек, не находящихся между двумя заданными числами, в Excel

Подсчёт количества ячеек между двумя числами — распространённая задача в Excel. Однако в определённых случаях может потребоваться подсчитать ячейки, не находящиеся между двумя заданными числами. Например, у меня есть список товаров с продажами с понедельника по воскресенье, и сейчас мне нужно получить количество ячеек, значения в которых не попадают в указанный диапазон от минимального до максимального числа, как показано на скриншоте ниже. В этой статье я расскажу о некоторых формулах для выполнения этой задачи в Excel.

• Подсчёт количества ячеек, не находящихся между двумя заданными числами, с помощью функции COUNTIF
• Подсчёт количества ячеек, не находящихся между двумя заданными числами, с помощью функции SUMPRODUCT

Подсчёт количества ячеек, не находящихся между двумя заданными числами, с помощью функции COUNTIF

Для решения этой задачи в Excel используйте функцию COUNTIF. Универсальный синтаксис:

• range: Диапазон данных, содержащий значения, которые необходимо подсчитать;
• low: ячейка, содержащая нижнюю границу интервала;
• high: ячейка, содержащая верхнюю границу интервала;
• &: этот разделитель служит для объединения значения ячейки с условиями.

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку:

2. Перетащите маркер заполнения вниз на ячейки, к которым нужно применить эту формулу. После этого будет рассчитано общее количество ячеек, не входящих в диапазон между двумя заданными числами. См. скриншот:

Пояснение формулы:

• COUNTIF(B2:H2,«<»&J2): эта функция COUNTIF подсчитывает количество ячеек со значениями, меньшими значения в ячейке J2, и возвращает результат: 3.
• COUNTIF(B2:H2,«>»&K2): эта функция COUNTIF подсчитывает количество ячеек со значениями, превышающими значение в ячейке K2, и возвращает результат: 1.
• COUNTIF(B2:H2,«»&K2)=3+1: сложите эти два результата, чтобы получить нужную логику: меньше J2 или больше K2.

Подсчёт количества ячеек, не находящихся между двумя заданными числами, с помощью функции SUMPRODUCT

Здесь также представлена функция SUMPRODUCT, которая поможет подсчитать количество ячеек, не входящих в диапазон между двумя числами. Универсальный синтаксис:

• range: Диапазон данных, содержащий значения, которые необходимо подсчитать;
• low: ячейка, содержащая нижнюю границу интервала;
• high: ячейка, содержащая верхнюю границу интервала;

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку:

2. Затем перетащите маркер заполнения вниз на ячейки, к которым нужно применить формулу, — и результат появится мгновенно. См. скриншот:

Пояснение формулы:

• В этой формуле не нужно использовать амперсанд (&) для объединения ссылок на ячейки — здесь знак плюс (+) задаёт логику ИЛИ.
• Эта формула оценит каждый массив и вернёт значение ИСТИНА или ЛОЖЬ для каждого значения, например: =SUMPRODUCT({FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE}+{TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE})
• Арифметическая операция преобразует значения ИСТИНА и ЛОЖЬ в единицы и нули следующим образом: =SUMPRODUCT({0,1,0,0,1,1,0}+{1,0,0,0,0,0,0})
• Наконец, сложите два массива, чтобы получить результат: =SUMPRODUCT({1, 1, 0, 0, 1, 1, 0}) = 4.

Используемая связанная функция:

Другие статьи: