Суммарные ячейки, которые соответствуют нескольким критериям с помощью Excel SUMPRODUCT

Сумма ячеек, которые попадают между двумя значениями

Функция SUMPRODUCT в Excel – очень универсальная функция, которая будет давать разные результаты в зависимости от способа ввода аргументов функции.

Обычно, как следует из названия, SUMPRODUCT умножает элементы одного или нескольких массивов, чтобы получить их продукт, а затем складывает или суммирует продукты.

Однако, регулируя синтаксис функции, ее можно использовать для суммирования только тех данных в ячейках, которые соответствуют определенным критериям.

Начиная с Excel 2007, программа содержала две функции – SUMIF и SUMIFS – которые будут суммировать данные в ячейках, которые соответствуют одному или нескольким установленным критериям.

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

SUMPRODUCT Синтаксис функции для суммирования ячеек

Синтаксис, используемый для получения SUMPRODUCT для суммирования данных в ячейках, которые удовлетворяют определенным условиям:

= SUMPRODUCT ([условие1] * [условие2] * [массив])

условие1, условие2 – условия, которые должны быть выполнены, прежде чем функция найдет произведение массива.

массив – непрерывный диапазон клеток

Пример: суммирование данных в ячейках, которые удовлетворяют нескольким условиям

Пример на изображении выше добавляет данные в ячейки в диапазоне от D1 до E6, которые находятся между 25 и 75.

Ввод функции SUMPRODUCT

Поскольку в этом примере используется неправильная форма функции SUMPRODUCT, диалоговое окно функции нельзя использовать для ввода функции и ее аргументов. Вместо этого функция должна быть введена вручную в ячейку листа.

  1. Нажмите на ячейку B7 на рабочем листе, чтобы сделать ее активной;
  2. Введите в ячейку B7 следующую формулу: = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75) * (A2: B6))
  3. Ответ 250 должен появиться в ячейке B7
  4. Ответ был получен путем сложения пяти чисел в диапазоне (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

Оцените статью
Solutics.ru
Добавить комментарий