Сумма ячеек, которые попадают между двумя значениями
Суммирование ячеек данных, которые соответствуют нескольким критериям с помощью Excel SUMPRODUCT.
LifeWire
Функция SUMPRODUCT в Excel — очень универсальная функция, которая будет давать разные результаты в зависимости от способа ввода аргументов функции .
Обычно, как следует из названия, SUMPRODUCT умножает элементы одного или нескольких массивов, чтобы получить их продукт, а затем складывает или суммирует продукты.
Однако, регулируя синтаксис функции , ее можно использовать для суммирования только тех данных в ячейках, которые соответствуют определенным критериям.
Начиная с Excel 2007, программа содержала две функции — SUMIF и SUMIFS — которые будут суммировать данные в ячейках, которые соответствуют одному или нескольким установленным критериям.
Однако иногда с SUMPRODUCT легче работать, когда речь идет о поиске нескольких условий, относящихся к одному и тому же диапазону, как показано на рисунке выше.
SUMPRODUCT Синтаксис функции для суммирования ячеек
Синтаксис, используемый для получения SUMPRODUCT для суммирования данных в ячейках, которые удовлетворяют определенным условиям:
= SUMPRODUCT ([условие1] * [условие2] * [массив])
условие1, условие2 — условия, которые должны быть выполнены, прежде чем функция найдет произведение массива.
массив — непрерывный диапазон клеток
Пример: суммирование данных в ячейках, которые удовлетворяют нескольким условиям
Пример на изображении выше добавляет данные в ячейки в диапазоне от D1 до E6, которые находятся между 25 и 75.
Ввод функции SUMPRODUCT
Поскольку в этом примере используется неправильная форма функции SUMPRODUCT, диалоговое окно функции нельзя использовать для ввода функции и ее аргументов. Вместо этого функция должна быть введена вручную в ячейку листа .
- Нажмите на ячейку B7 на рабочем листе, чтобы сделать ее активной ;
- Введите следующую формулу в ячейку B7: = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75) * (A2: B6))
- Ответ 250 должен появиться в ячейке B7
- Ответ был получен путем сложения пяти чисел в диапазоне (40, 45, 50, 55 и 60) между 25 и 75. Всего 250
Разрушение формулы SUMPRODUCT
Когда в качестве аргументов используются условия, SUMPRODUCT оценивает каждый элемент массива в соответствии с условием и возвращает логическое значение (ИСТИНА или ЛОЖЬ).
Для целей вычислений Excel назначает значение 1 для тех элементов массива, которые имеют значение ИСТИНА (соответствуют условию), и значение 0 для элементов массива, которые имеют значение ЛОЖЬ (не соответствуют условию).
Например, число 40:
- TRUE для первого условия, поэтому значение 1 присваивается в первом массиве;
- TRUE для второго условия, поэтому значение 1 присваивается во втором массиве.
№ 15:
- ЛОЖЬ для первого условия, поэтому значение 0 назначается в первом массиве;
- TRUE для второго условия, поэтому значение 1 присваивается во втором массиве.
Соответствующие единицы и нули в каждом массиве умножаются вместе:
- Для числа 40 — у нас есть 1 x 1, возвращающий значение 1;
- Для числа 15 — у нас есть 0 x 1, возвращая значение 0.
Умножение единиц и нулей на диапазон
Эти единицы и нули затем умножаются на числа в диапазоне A2: B6.
Это сделано, чтобы дать нам числа, которые будут суммироваться функцией.
Это работает, потому что:
- 1 раз любое число равно оригинальному
- 0 раз любое число равно 0
Итак, мы заканчиваем с:
- 1 * 40 = 40
0 * 15 = 0
0 * 22 = 0
1 * 45 = 45
1 * 50 = 50
1 * 55 = 55
0 * 25 = 0
0 * 75 = 0
1 * 60 = 60
0 * 100 = 0
Подводя итоги
Затем SUMPRODUCT суммирует приведенные выше результаты, чтобы найти ответ.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250