Как рассчитать средневзвешенное значение в Google Таблицах (простая формула)

Содержание:

Расчет среднего значения — обычная задача для людей, работающих с данными в Google Таблицах. И когда количества в наборе данных не имеют такой же важности, поиска простого среднего арифметического недостаточно. В таких случаях взвешенное среднее арифметическое может быть лучшим представлением данных.

В этом руководстве мы покажем вам два простых способа вычисления средневзвешенного значения в Google Таблицах :

Использование функции СУММПРОИЗВ (SUMPRODUCT)
Использование функции СРЕДНИЙ ВЕС (AVERAGE.WEIGHTED)

Итак, приступим!

Что такое средневзвешенное значение?

Средневзвешенное это среднее арифметическое рассчитывается, принимая во внимание важность элементов в данных. Таким образом, оно дает более точную картину данных, чем обычное среднее арифметическое.

Средневзвешенное значение часто используется при анализе эффективности класса, бухгалтерского учета и статистических аналитических операций.

Практическое применение средневзвешенного значения

Возьмем пример.

Учащийся может пройти три разных теста (скажем, классный, промежуточный и финальный).

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

В таких случаях простое суммирование баллов и деление на 3 не учитывает важность (или вес) каждого экзамена. Следовательно, это не даст точного представления об успеваемости учащегося.

С другой стороны, если мы рассчитаем средневзвешенное значение баллов, оно будет учитывать вес отдельных тестов. Вы рассчитываете это по:

Умножение каждой оценки за тест на соответствующий вес.
Добавление каждого из этих продуктов
Разделив эту сумму на сумму всех весов

Итак, в случае этого студента средневзвешенное значение рассчитывается следующим образом:

Средневзвешенное значение = [(75 x 20) + (80 x 30) + (60 x 50)] / (20 + 30 +50)

= 6900 / 100

= 69

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

Как рассчитать средневзвешенное значение в Google Таблицах

В таблицах Google есть несколько хороших функций, которые могут помочь вам рассчитать средневзвешенное значение ваших данных:

Функция СУММПРОИЗВ
Функция СРЕДНИЙ.ВЕС

Мы рассмотрим, как использовать эти две функции по отдельности. Для обоих методов мы будем использовать следующий набор данных:

Нахождение средневзвешенного значения с помощью функции СУММПРОИЗВ в Google Таблицах

Первый способ предполагает использование функции СУММПРОИЗВ Google Таблиц.

Функция СУММПРОИЗВ позволяет найти сумму произведений набора значений переменных.

Синтаксис функции СУММПРОИЗВ

Синтаксис функции СУММПРОИЗВ следующий:

СУММПРОИЗВ (массив1; массив2; ....)

Здесь array1, array2, array3 и т. д. Являются отдельными переменными. Это может быть диапазон ячеек, список значений или имя отдельного столбца.

Например, если у нас есть два диапазона A1: A5 и B1: B5, функция: СУММПРОИЗВ (A1: A5, B1: B5) будет принимать каждое значение первого диапазона (A1: A5) и умножать его на соответствующее значение в второй диапазон (B1: B5).

Затем он складывает все эти отдельные продукты, чтобы получить окончательный результат.

Итак, если у вас есть формула ниже

= СУММПРОИЗВ (A1: A5; B1: B5)

это то, что он делает в бэкэнде в Google Таблицах

= (A1 * B1) + (A2 * B2) + (A3 * B3) + (A4 * B4) + (A5 * B5)

Функция СУММПРОИЗВ отлично подходит для нахождения средневзвешенного значения, поскольку большая часть вычислений связана с нахождением суммы произведений.

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

Затем вы можете разделить результат на сумму весов!

Использование функции СУММПРОИЗВ для нахождения средневзвешенного значения

В нашем примере мы хотим найти произведения отдельных результатов тестов с их соответствующим весом, просуммировать все продукты и затем разделить эту сумму на сумму весов.

Вот шаги, которые необходимо выполнить, чтобы применить функцию СУММПРОИЗВ к приведенному выше примеру:

Выберите ячейку, в которой вы хотите отобразить средневзвешенное значение (C8 в нашем примере).

Введите формулу: = СУММПРОИЗВ (B2: B7, C2: C7) / СУММ (C2: C7).

Нажмите клавишу возврата.

Вы должны увидеть результирующее средневзвешенное значение в выбранной ячейке.

Нахождение средневзвешенного значения с помощью функции СРЗНАЧ.ВЕС в Google Таблицах

Функция СРЕДНИЙ.ВЕС (AVERAGE.WEIGHTED) — это функция, которую вы получите только в Google Таблицах.

Вы не найдете в Excel такой функции, которая предназначена исключительно для определения средневзвешенного значения.

Эта функция значительно упрощает вычисление средневзвешенного значения по сравнению с методом СУММПРОИЗВ.

Синтаксис функции AVERAGE.WEIGHTED

Синтаксис функции СРЕДНИЙ.ВЕС следующий:

AVERAGE.WEIGHTED (значения, веса, [дополнительные значения], [дополнительные веса])

Здесь,

значения — это значения данных, для которых требуется найти средневзвешенное значение
веса — это диапазон ячеек, содержащих соответствующие веса.

Как видно из синтаксиса, также можно использовать несколько наборов значений данных и соответствующих им наборов весов.

Функция AVERAGE.WEIGHTED делает вычисление средневзвешенного значения намного проще, чем функция SUMPRODUCT, поскольку вам нужно только указать диапазоны для значений и весов, без необходимости выполнять какие-либо последующие операции.

Например, если у нас есть набор значений в диапазоне A1: A5 и соответствующие веса в B1: B5, функция: AVERAGE.WEIGHTED (A1: A5, B1: B5) будет принимать каждое значение из первого диапазона (A1: A5) и умножьте его на соответствующее значение во втором диапазоне (B1: B5).

Затем он суммирует все эти отдельные продукты. Наконец, он разделит эту окончательную сумму на сумму весов, чтобы получить окончательный результат.

Итак, если у вас есть формула, показанная ниже:

= СРЕДНИЙ.ВЕС (A1: A5, B1: B5)

это то, что он делает в бэкэнде в Google Таблицах

= [(A1 * B1) + (A2 * B2) + (A3 * B3) + (A4 * B4) + (A5 * B5)] / (B1 + B2 + B3 + B4 + B5)

Использование функции СРЕДНИЙ.ВЕС для поиска средневзвешенного значения

В нашем примере мы можем напрямую использовать функцию AVERAGE.WEIGHTED без необходимости использовать ее в сочетании с какой-либо другой функцией.

Вот шаги, которые необходимо выполнить, чтобы применить функцию AVERAGE.WEIGHTED к приведенному выше примеру:

Выберите ячейку, в которой вы хотите отобразить средневзвешенное значение (C8 в нашем примере).
Введите формулу: = СРЕДНИЙ.ВЕС (B2: B7, C2: C7).
Нажмите клавишу возврата.

Вы должны увидеть результирующее средневзвешенное значение в выбранной ячейке.

Обратите внимание на простоту формулы. Все, что мы используем, — это диапазон значений и диапазон весов.

Примечание. Важно, чтобы все ячейки были заполнены числовыми значениями при использовании функции СРЕДНИЙ.ВЕС. Если у вас есть ячейка, оставленная пустой, формула возвращает ошибку. Итак, убедитесь, что вы заполнили все пустые ячейки как минимум «0». Однако при использовании функции СУММПРОИЗВ такого барьера нет.

В этом руководстве я показал вам два способа расчета средневзвешенного значения в Google Таблицах. Один использует общую функцию СУММПРОИЗВ, а другой — специальную функцию Google Таблиц, ВЕС.СРЕДНЕЕ. Последняя функция специфична для Google Таблиц и предназначена специально для расчета средневзвешенного значения.

Надеюсь, этот урок был вам полезен.

Вас могут заинтересовать следующие статьи: