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

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

• Подсчет количества ячеек, не находящихся между двумя заданными числами, с помощью функции СЧЁТЕСЛИ
• Подсчет количества ячеек, не находящихся между двумя заданными числами, с помощью функции СУММПРОИЗВ

Подсчет количества ячеек, не находящихся между двумя заданными числами, с помощью функции СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ в Excel может помочь вам решить эту задачу. Общий синтаксис выглядит следующим образом:

• диапазон: Диапазон данных, содержащий значения, которые вы хотите подсчитать;
• низ: Ячейка, содержащая нижний предел интервала;
• верх: Ячейка, содержащая верхний предел интервала;
• &: Этот разделитель используется для объединения значения ячейки с условиями.

1. Пожалуйста, введите или скопируйте следующую формулу в пустую ячейку:

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

Объяснение формулы:

• СЧЁТЕСЛИ(B2:H2,"<"&J2): Эта функция СЧЁТЕСЛИ получает количество ячеек меньше значения в J2, она возвращает результат: 3.
• СЧЁТЕСЛИ(B2:H2,">"&K2): Эта функция СЧЁТЕСЛИ получает количество ячеек больше значения в K2, она возвращает результат: 1.
• СЧЁТЕСЛИ(B2:H2,"<"&J2)+СЧЁТЕСЛИ(B2:H2,">"&K2)=3+1: Сложите эти два результата, чтобы получить требуемую логику: меньше J2 или больше K2.

Подсчет количества ячеек, не находящихся между двумя заданными числами, с помощью функции СУММПРОИЗВ

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

• диапазон: Диапазон данных, содержащий значения, которые вы хотите подсчитать;
• низ: Ячейка, содержащая нижний предел интервала;
• верх: Ячейка, содержащая верхний предел интервала;

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

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

Объяснение формулы:

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

Связанные функции:

Больше статей: