Что нужно знать
- Синтаксис: =СУММПРОИЗВ(Массив1, Массив2, Массив3, …Массив255).
- При расчете средневзвешенного значения некоторые числа в диапазоне стоят больше, чем другие.
- Формула взвешивания СУММПРОИЗВ - это нестандартная формула, которую необходимо ввести в ячейку, чтобы она работала.
В этой статье объясняется, как использовать функцию СУММПРОИЗВ для вычисления средневзвешенного значения в формулах Microsoft Excel.
Введите формулу взвешивания
Как и большинство других функций в Excel, СУММПРОИЗВ можно ввести на лист с помощью Библиотеки функций на вкладке Формулы. Поскольку формула взвешивания в этом примере использует СУММПРОИЗВ нестандартным образом (результат функции делится на весовой коэффициент), формула взвешивания должна быть введена в ячейку рабочего листа.
Пример, показанный в этой статье, вычисляет средневзвешенное значение итоговой оценки учащегося с помощью функции СУММПРОИЗВ.
Эта функция выполняет:
- Умножение различных оценок на их индивидуальный весовой коэффициент.
- Сложение произведений этих операций умножения вместе.
-
Деление вышеуказанной суммы на весовой коэффициент 7 (1+1+2+3) для четырех оценок.
Чтобы ввести формулу СУММПРОИЗВ для вычисления средневзвешенного значения, откройте пустой рабочий лист, введите данные в строки с 1 по 6 из изображение выше, и выполните следующие действия:
- Выберите ячейку C7, чтобы сделать ее активной (это место, где будет отображаться итоговая оценка учащегося).
- Введите формулу =СУММПРОИЗВ(B3:B6, C3:C6)/(1+1+2+3) в ячейку. Формула появится в строке формул.
- Нажмите клавишу Enter на клавиатуре.
- Ответ 78.6 появляется в ячейке C7 (ваш ответ может содержать больше знаков после запятой).
Невзвешенное среднее для тех же четырех оценок будет 76,5. Поскольку у студента были лучшие результаты на промежуточных и выпускных экзаменах, взвешивание среднего значения помогло улучшить общую оценку.
Как работает функция СУММПРОИЗВ
Обычно, когда вы вычисляете среднее или среднее арифметическое, каждое число имеет одинаковую ценность или вес. Среднее значение рассчитывается путем сложения диапазона чисел и последующего деления этой суммы на количество значений в диапазоне. С другой стороны, средневзвешенное значение предполагает, что одно или несколько чисел в диапазоне имеют большую ценность или больший вес, чем другие числа.
СУММПРОИЗВ перемножает элементы двух или более массивов, а затем складывает произведения для вычисления средневзвешенных значений. Например, определенные оценки в школе, такие как промежуточные и выпускные экзамены, обычно стоят больше, чем обычные тесты или задания. Если для расчета итоговой оценки учащегося используется усреднение, промежуточным и выпускным экзаменам будет придан больший вес.
В ситуации, когда в качестве аргументов для функции СУММПРОИЗВ вводятся два массива по четыре элемента в каждом:
- Первый элемент массива1 умножается на первый элемент массива2.
- Второй элемент массива1 умножается на второй элемент массива2.
- Третий элемент массива1 умножается на третий элемент массива2.
- Четвертый элемент массива1 умножается на четвертый элемент массива2.
Далее произведения четырех операций умножения суммируются и возвращаются функцией как результат.
SUMPRODUCT Синтаксис и аргументы
Синтаксис функции относится к структуре функции и включает в себя имя функции, квадратные скобки и аргументы. Синтаксис функции СУММПРОИЗВ:
=СУММПРОИЗВ(Массив1, Массив2, Массив3, … Массив255)
Аргументы функции СУММПРОИЗВ:
- Array1: Первый аргумент массива (обязательный).
- Array2, Array3, … Array255: Дополнительные (необязательные) массивы, до 255. С двумя или более массивами функция перемножает элементы каждого массива вместе, а затем складывает результаты.
Элементами массива могут быть ссылки на ячейки с расположением данных на листе или числа, разделенные арифметическими операторами, такими как знаки плюс (+) или минус (-). Если вы вводите числа, не разделенные операторами, Excel обрабатывает их как текстовые данные.
Аргументы массива должны иметь одинаковое количество элементов в каждом массиве. Если нет, СУММПРОИЗВ возвращает ЗНАЧ! значение ошибки. Если какие-либо элементы массива не являются числами, например текстовые данные, СУММПРОИЗВ рассматривает их как нули.
Варианты формулы СУММПРОИЗВ
Чтобы подчеркнуть, что результаты функции СУММПРОИЗВ делятся на сумму весов для каждой оценочной группы, делитель (часть, выполняющая деление) вводится как:
(1+1+2+3)
Общую формулу взвешивания можно упростить, введя число 7 (сумма весов) в качестве делителя. Тогда формула будет такой:
=СУММПРОИЗВ(B3:B6, C3:C6)/7
Этот выбор удобен, если количество элементов в массиве весов невелико и их можно легко складывать вместе, но он становится менее эффективным по мере увеличения количества элементов в массиве весов, что затрудняет их добавление.
Другой вариант и, вероятно, лучший выбор, учитывая, что он использует ссылки на ячейки, а не числа при суммировании делителя, - это использовать функцию СУММ для суммирования делителя. Тогда формула будет такой:
=СУММПРОИЗВ(B3:B6, C3:C6)/СУММ(B3:B6)
Обычно лучше вводить в формулы ссылки на ячейки, а не фактические числа. Это упрощает их обновление при изменении данных формулы.
Например, если весовые коэффициенты для заданий изменены на 0,5 в примере, а тесты изменены на 1,5, первые две формы формулы необходимо отредактировать вручную, чтобы исправить делитель.
В третьем варианте нужно обновить только данные в ячейках B3 и B4, а формула пересчитывает результат.
