Обычно, когда вы вычисляете среднее или среднее арифметическое, каждое число имеет одинаковое значение или вес. Среднее значение рассчитывается путем добавления диапазона чисел вместе, а затем деления этой суммы на количество значений в диапазоне. С другой стороны, средневзвешенное значение считает одно или несколько номеров в диапазоне более значительными или имеют больший вес, чем другие цифры.
Например, определенные оценки в школе, такие как среднесрочные и заключительные экзамены, обычно стоят больше, чем регулярные тесты или задания. Если для вычисления конечной отметки учащегося используется усреднение, среднесрочные и заключительные экзамены будут иметь больший вес. В Excel вы можете рассчитать средневзвешенные значения, используяSUMPRODUCT функция.
Как работает функция SUMPRODUCT
SUMPRODUCT умножает элементы двух или более массивов, а затем добавляет продукты. Например, в ситуации, когда два массива с четырьмя элементами вводятся в качестве аргументов для SUMPRODUCT функция:
- Первый элемент массива 1 умножается на первый элемент в массиве2.
- Второй элемент массива 1 умножается на второй элемент массива2.
- Третий элемент массива 1 умножается на третий элемент массива2.
- Четвертый элемент массива 1 умножается на четвертый элемент массива2.
Затем продукты четырех операций умножения суммируются и возвращаются функцией в качестве результата.
02 из 04Синтаксис и аргументы SUMPRODUCT
Синтаксис функции относится к компоновке функции и включает имя функции, скобки и аргументы. Синтаксис для SUMPRODUCT функция:
= SUMPRODUCT (array1, array2, array3, … array255)
Аргументами для функции SUMPRODUCT являются:
- Array1: Первый аргумент массива (обязательно)
- Array2, array3, … array255: Дополнительные (необязательные) массивы, до 255. С двумя или несколькими массивами функция умножает элементы каждого массива вместе, а затем добавляет результаты.
Элементы массива могут быть сотовыми ссылками на местоположение данных на листе или номерами, разделенными арифметическими операторами, такими как плюс (+) или минус (-) приметы. Если вы вводите числа, не разделенные операторами, Excel рассматривает их как текстовые данные.
- Все аргументы массива должны быть одного размера. Другими словами, одинаковое количество элементов должно быть в каждом массиве. Если не, SUMPRODUCT возвращает #ЗНАЧЕНИЕ! значение ошибки.
- Если какие-либо элементы массива не являются номерами, такими как текстовые данные, SUMPRODUCT рассматривает их как нули.
Ввод весовой формулы
Наш пример, показанный в этой статье, вычисляет средневзвешенное значение для конечной отметки учащегося с использованием SUMPRODUCT функция; функция выполняет это путем:
- Умножение различных меток на их индивидуальный весовой коэффициент.
- Добавление продуктов этих операций умножения вместе.
- Разделив указанную сумму на общую сумму весового коэффициента 7 (1 + 1 + 2 + 3) для четырех оценок.
Как и большинство других функций в Excel, вы обычно вводите SUMPRODUCT в рабочий лист, используя Formula Builder; однако, поскольку весовая формула использует SUMPRODUCT нестандартным образом, однако - результат функции делится на весовой коэффициент - вы должны ввести формулу взвешивания в ячейку рабочего листа.
= SUMPRODUCT (В3: В6, С3: С6) / (1 + 1 + 2 + 3)
- Нажмите на ячейку C7 чтобы сделать его активной ячейкой (местом, где будет отображаться окончательный знак ученика).
- Введите вышеуказанную формулу в ячейку.
- нажмитеВойти на клавиатуре.
- Ответ78.6 должен появиться в ячейке C7 (у вашего ответа может быть больше десятичных знаков).
Невзвешенное среднее значение для тех же четырех марок будет 76,5. Поскольку у ученика были лучшие результаты для его промежуточных и заключительных экзаменов, взвешивание среднего помогло улучшить его общую оценку.
04 из 04ВАРИАНТЫ ФОРМИРОВАНИЯ 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 необходимо обновить, а формула пересчитает результат.
