Как правило, когда вы вычисляете среднее или среднее арифметическое, каждое число имеет равное значение или вес. Среднее значение рассчитывается путем сложения диапазона чисел, а затем деления этой суммы на количество значений в диапазоне. Средневзвешенное значение, с другой стороны, считает, что одно или несколько чисел в диапазоне стоят больше или имеют больший вес, чем другие числа.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010, 2007; Excel для Office 365, Excel Online, Excel для Mac, Excel для iPad, Excel для iPhone и Excel для Android.
Как работает функция SUMPRODUCT
SUMPRODUCT умножает элементы двух или более массивов, а затем добавляет продукты для вычисления взвешенных средних. Например, определенные оценки в школе, такие как промежуточные и итоговые экзамены, обычно стоят больше, чем обычные тесты или задания. Если для подсчета итоговой оценки учащегося используется усреднение, промежуточные и итоговые экзамены будут иметь больший вес.
В ситуации, когда два массива с четырьмя элементами вводятся в качестве аргументов для функции SUMPRODUCT:
- Первый элемент array1 умножается на первый элемент в array2.
- Второй элемент array1 умножается на второй элемент array2.
- Третий элемент array1 умножается на третий элемент array2.
- Четвертый элемент массива 1 умножается на четвертый элемент массива 2.
Затем произведения четырех операций умножения суммируются и возвращаются функцией как результат.
SUMPRODUCT Синтаксис и Аргументы
Синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы. Синтаксис для функции SUMPRODUCT:
= SUMPRODUCT ( Array1 , array2 , Array3 , ... Array255 )
Аргументы для функции SUMPRODUCT:
- Array1: первый аргумент массива (обязательно).
- Array2 , Array3 , … Array255 : дополнительные (необязательные) массивы, до 255. При использовании двух или более массивов функция умножает элементы каждого массива вместе, а затем добавляет результаты.
Элементами массива могут быть ссылки на ячейки для расположения данных на листе или числа, разделенные арифметическими операторами, такими как знаки плюс (+) или минус (-). Если вы вводите числа, которые не разделяются операторами, Excel обрабатывает их как текстовые данные.
Аргументы массива должны иметь одинаковое количество элементов в каждом массиве. Если нет, SUMPRODUCT возвращает # ЗНАЧЕНИЕ! значение ошибки. Если какие-либо элементы массива не являются числами, такими как текстовые данные, SUMPRODUCT рассматривает их как нули.
Введите формулу взвешивания
Пример, показанный в этой статье, вычисляет средневзвешенное значение для итоговой оценки учащегося с помощью функции SUMPRODUCT.
Функция выполняет это путем:
- Умножение различных марок на их индивидуальный весовой коэффициент.
- Добавление продуктов этих операций умножения вместе.
- Разделив вышеуказанную сумму на сумму весового коэффициента 7 (1 + 1 + 2 + 3) для четырех оценок.
Как и большинство других функций в Excel, SUMPRODUCT можно вводить в лист с помощью библиотеки функций, которая находится на вкладке Формулы. Поскольку весовая формула в этих примерах использует SUMPRODUCT нестандартным образом (результат функции делится на весовой коэффициент), весовая формула должна быть введена в ячейку рабочего листа.
Чтобы ввести формулу SUMPRODUCT для вычисления средневзвешенного значения, откройте чистый лист, введите данные в строки с 1 по 6 на изображении выше и выполните следующие действия:
-
Выберите ячейку C7 , чтобы сделать ее активной (это место, где будет отображаться итоговая отметка учащегося).
-
Введите в ячейку формулу = SUMPRODUCT (B3: B6, C3: C6)/(1 + 1 + 2 + 3) . Формула появится в строке формул.
-
Нажмите клавишу Enter на клавиатуре.
-
Ответ 78,6 отображается в ячейке C7 (в вашем ответе может быть больше десятичных знаков).
Невзвешенное среднее для тех же четырех марок будет 76,5. Поскольку у ученика были лучшие результаты для его промежуточных и выпускных экзаменов, взвешивание среднего помогло улучшить общую оценку.
SUMPRODUCT Варианты формул
Чтобы подчеркнуть, что результаты функции SUMPRODUCT делятся на сумму весов для каждой оценочной группы, делитель (часть, делающая деление) вводится как:
(1 + 1 + 2 + 3)
Общая формула весов может быть упрощена путем ввода числа 7 (сумма весов) в качестве делителя. Формула будет тогда:
= SUMPRODUCT (В3: В6, С3: С6)/7
Этот выбор подходит, если количество элементов в массиве весов мало, и их легко можно сложить вместе, но оно становится менее эффективным по мере увеличения количества элементов в массиве весов, что затрудняет их добавление.
Другой вариант, и, вероятно, лучший выбор, учитывая, что для суммирования делителя используются ссылки на ячейки, а не числа, будет использовать функцию SUM для суммирования делителя. Формула тогда:
= SUMPRODUCT (В3: В6, С3: С6)/СУММ (В3: В6)
Обычно лучше вводить ссылки на ячейки, а не фактические числа в формулы. Это упрощает их обновление при изменении данных формулы.
Например, если в этом примере весовые коэффициенты для Присвоений изменены на 0,5, а в Тестах – на 1,5, первые две формы формулы необходимо отредактировать вручную, чтобы исправить делитель.
В третьем варианте необходимо обновить только данные в ячейках B3 и B4, и формула пересчитывает результат.
