01 из 02
Суммировать ячейки, которые попадают между двумя значениями
Lifewire
Функция СУММПРОИЗВ в Excel - очень универсальная функция, которая дает разные результаты в зависимости от того, как вводятся аргументы функции.
Обычно, как следует из названия, СУММПРОИЗВ умножает элементы одного или нескольких массивов, чтобы получить их произведение, а затем складывает или суммирует произведения.
Однако, изменив синтаксис функции, ее можно использовать для суммирования только тех данных в ячейках, которые соответствуют определенным критериям.
Начиная с Excel 2007, программа содержит две функции - СУММЕСЛИ и СУММЕСЛИМН, которые суммируют данные в ячейках, которые соответствуют одному или нескольким заданным критериям.
Иногда, однако, с СУММПРОИЗВ легче работать, когда нужно найти несколько условий, относящихся к одному и тому же диапазону, как показано на изображении выше.
SUMPRODUCT Синтаксис функции для суммирования ячеек
Синтаксис, используемый для СУММПРОИЗВ для суммирования данных в ячейках, отвечающих определенным условиям:
=СУММПРОИЗВ([условие1][условие2][массив])
условие1, условие2 - условия, которые должны быть выполнены, прежде чем функция найдет произведение массива.
массив - непрерывный диапазон ячеек
Пример. Суммирование данных в ячейках, удовлетворяющих нескольким условиям
В примере на изображении выше добавляются данные в ячейках в диапазоне от D1 до E6, которые находятся между 25 и 75.
Вход в функцию СУММПРОИЗВ
Поскольку в этом примере используется неправильная форма функции СУММПРОИЗВ, диалоговое окно функции нельзя использовать для ввода функции и ее аргументов. Вместо этого функция должна быть введена вручную в ячейку листа.
- Нажмите на ячейку B7 на листе, чтобы сделать ее активной ячейкой;
- Введите следующую формулу в ячейку B7: =СУММПРОИЗВ(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- Ответ 250 должен появиться в ячейке B7
- Ответ был получен путем сложения пяти чисел в диапазоне (40, 45, 50, 55 и 60), находящихся между 25 и 75. Итого 250
Расшифровка формулы СУММПРОИЗВ
Когда в качестве аргументов используются условия, СУММПРОИЗВ оценивает каждый элемент массива по условию и возвращает логическое значение (ИСТИНА или ЛОЖЬ).
Для расчетов Excel присваивает значение 1 тем элементам массива, которые ИСТИНА (соответствуют условию), и значение 0для элементов массива, которые имеют значение FALSE (не удовлетворяют условию).
Например, число 40:
- равно TRUE для первого условия, поэтому в первом массиве присваивается значение 1;
- равно TRUE для второго условия, поэтому во втором массиве присваивается значение 1.
Число 15:
- равно FALSE для первого условия, поэтому в первом массиве присваивается значение 0;
- равно TRUE для второго условия, поэтому во втором массиве присваивается значение 1.
Соответствующие единицы и нули в каждом массиве перемножаются:
- Для числа 40 - 1 x 1 возвращает значение 1;
- Для числа 15 - 0 x 1 возвращает значение 0.
Умножение единиц и нулей на диапазон
Эти единицы и нули затем умножаются на числа в диапазоне A2: B6
Это сделано для того, чтобы получить числа, которые будут суммироваться функцией.
Это работает, потому что:
- 1 раз любое число равно исходному числу
- 0 раз любое число равно 0
Итак, мы получаем:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
Подведение итогов
СУММПРОИЗВ затем суммирует приведенные выше результаты, чтобы найти ответ.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
