Как рассчитать средневзвешенное значение в Excel

Excel — очень удобный инструмент для отслеживания прогресса и расчета средних значений. Но данные не всегда просты, и иногда среднее значение не работает. Что делать, если не все ценности одинаково важны?

Вот где вам нужно средневзвешенное значение .

Средневзвешенное значение может придать вашим данным больше смысла, и его легко вычислить с помощью Excel. Вот что вам нужно знать.

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

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

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

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

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

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

В нашем примере мы умножим оценки на их веса и сложим их вместе:

  (5 * 78) + (5 * 82  ) + (10 * 77) + (20 * 87) + (20 * 81) + (40 * 75) = 7930  

Затем мы сложим веса:

  5 + 5 + 10 + 20 + 20 + 40 = 100  

Теперь мы просто делим общие взвешенные значения на общие веса:

  7930/100 = 79,3  

Итак, средневзвешенное значение в этом примере составляет 79,3 процента.. Знать, как рассчитать значение веса вручную, полезно, но отнимает много времени. Вместо этого гораздо проще и быстрее рассчитывать средневзвешенные значения в Excel.

Как рассчитать средневзвешенное значение в Excel

Средневзвешенные значения можно рассчитать в Excel тем же способом, что и ниже:

Столбец D содержит вес, умноженный на оценки. В ячейке D2 есть команда = C2 * B2 , в D3 — = C3 * B3 и т. Д.

Сумма произведений веса и сортов находится в ячейке D8. Мы рассчитали общую сумму с помощью функции суммы = SUM (D2: D7) , которая суммирует все значения между D2 и D7. Точно так же сумма весов находится в ячейке B8, также с использованием функции СУММ.

Наконец, средневзвешенное значение вычисляется путем деления ячейки D8 на ячейку B8.

По теме: 14 советов по экономии времени в Microsoft Excel

Если это все еще кажется любите слишком много работать, вы правы! Excel предлагает множество функций, упрощающих общие вычисления. В этом случае мы можем использовать SUMPRODUCT , чтобы уменьшить объем работы.

Использование ярлыка SUMPRODUCT

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

В нашем примере ячейка B9 содержит формулу: = СУММПРОИЗВ (B2: B7, C2: C7) . СУММПРОИЗВ — это вызов функции, и он требует, чтобы наборы чисел умножались, а затем складывались.

В нашем примере мы дали функции два набора данных: значения от B2 до B7 и значения от C2 до C7. Вы можете использовать столько наборов данных, сколько захотите, при условии, что каждый набор данных имеет одинаковое количество значений.

Если вы предпочитаете вводить свои функции с помощью окна «Аргументы функций», вам необходимо ввести свои наборы данных в пустые поля массива. Щелкните поле, затем выделите данные, которые хотите ввести. Не беспокойтесь, если у вас более трех наборов данных, при добавлении набора данных появится новое поле массива.

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

Отсюда вам нужно только сложить веса и разделить СУММПРОИЗВ на результат. Для расчета общих весов мы использовали СУММ в предыдущем примере.

Наконец, мы разделили ячейку B9 на ячейку B10, чтобы вычислить средневзвешенное значение..

Когда использовать средневзвешенное значение

Наиболее вероятное место, где вы видели средневзвешенные значения, — это школа. Но помимо расчета среднего балла по курсу, вы также можете использовать средневзвешенное значение для расчета среднего балла по нескольким курсам с разными кредитами.

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

Следующее наиболее частое место, где вы, вероятно, столкнетесь со средневзвешенными значениями, — это спортивная статистика. Попробуйте сравнить средние показатели ударов двух бейсболистов. Первый игрок получает много попаданий, но почти не добивается успеха. Второй игрок получает больше хоум-ранов, но и у него больше не попаданий. Какой плеер лучше?

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

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

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

Анализ средневзвешенных значений

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

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

Оцените статью
oilgasindustry.ru
Добавить комментарий