Суммы, которые падают между двумя значениями
Обзор SUMPRODUCT
Функция SUMPRODUCT в Excel - это очень универсальная функция, которая даст разные результаты в зависимости от способа ввода аргументов функции.
Обычно, как следует из его названия, SUMPRODUCT умножает элементы одного или нескольких массивов для получения своего продукта, а затем добавляет или суммирует продукты вместе.
Однако, регулируя синтаксис функции, он может использоваться для суммирования только данных в ячейках, соответствующих определенным критериям.
Начиная с Excel 2007, программа содержала две функции - SUMIF и SUMIFS - которые будут суммировать данные в ячейках, которые соответствуют одному или нескольким установленным критериям.
Однако время от времени SUMPRODUCT легче работать, когда дело доходит до нахождения нескольких условий, относящихся к тому же диапазону, как показано на изображении выше.
Синтаксис функции SUMPRODUCT для суммирования ячеек
Синтаксис, используемый для получения SUMPRODUCT для суммирования данных в ячейках, отвечающих определенным условиям:
= SUMPRODUCT (состояние1 * состояние2 * массив)
condition1, condition2 - условия, которые должны быть выполнены до того, как функция найдет произведение массива.
array - непрерывный диапазон ячеек
Пример: суммирование данных в ячейках, которые удовлетворяют нескольким условиям
Пример на изображении выше добавляет данные в ячейки в диапазоне от 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 оценивает каждый элемент массива относительно условия и возвращает логическое значение (TRUE или FALSE).
Для целей вычислений Excel присваивает значение 1 для тех элементов массива, которые являются TRUE (отвечают условию) и значения 0 для элементов массива, которые FALSE (не соответствуют условию).
Например, число 40:
- TRUE для первого условия, поэтому значение 1 назначается в первом массиве;
- TRUE для второго условия, поэтому значение 1 назначается во втором массиве.
число 15:
- FALSE для первого условия, поэтому значение 0 назначается в первом массиве;
- TRUE для второго условия, поэтому значение 1 назначается во втором массиве.
Соответствующие и нули в каждом массиве умножаются вместе:
- Для числа 40 - имеем 1 x 1, возвращающее значение 1;
- Для числа 15 - имеем 0 x 1, возвращающее значение 0.
Продолжить чтение ниже
02 из 02Умножение единиц и нулей на диапазон
Затем эти и нули умножаются на числа в диапазоне A2: B6.
Это делается для того, чтобы дать нам числа, которые будут суммированы функцией.
Это работает, потому что:
- 1 раз любое число равно исходному номеру
- 0 раз любое число равно 0
Таким образом, мы получаем:
- 1 * 40 = 400 * 15 = 00 * 22 = 01 * 45 = 451 * 50 = 501 * 55 = 550 * 25 = 00 * 75 = 01 * 60 = 600 * 100 = 0
Подведение итогов
Затем SUMPRODUCT суммирует приведенные выше результаты, чтобы найти ответ.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250
