Как правило, когда вы вычисляете среднее или среднее арифметическое, каждое число имеет равное значение или вес. Среднее значение рассчитывается путем сложения диапазона чисел, а затем деления этой суммы на количество значений в диапазоне . Средневзвешенное значение, с другой стороны, считает, что одно или несколько чисел в диапазоне стоят больше или имеют больший вес, чем другие числа.
Инструкции в этой статье относятся к 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.
- Третий элемент массива1 умножается на третий элемент массива2.
- Четвертый элемент массива 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) . Формула появится в строке формул.
-
Нажмите клавишу ввода на клавиатуре.
-
Ответ 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, а формула пересчитывает результат.