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

Предположим, у вас есть список покупок, как показано на скриншоте ниже. Хотя функция СРЗНАЧ Excel даст вам среднюю цену без учета веса или количества, более точным подходом в таких случаях будет расчет взвешенного среднего. Это лучше отражает реальную стоимость за единицу, давая товарам с большим весом или частотой большее влияние на конечный результат.

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

Выберите пустую ячейку, например F2, и введите следующую формулу:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

и нажмите клавишу Enter, чтобы получить результат.

Примечание. В этой формуле C2:C18 относится к столбцу Вес, а D2:D18 — к столбцу Цена. Настройте эти диапазоны в зависимости от вашей структуры данных. Функция СУММПРОИЗВ умножает каждый вес на соответствующую цену и суммирует результаты, тогда как СУММ подсчитывает общую сумму весов, что дает правильное взвешенное среднее. Убедитесь, что используете диапазоны одинаковой длины и проверьте, что в ваших данных нет несоответствующих или пустых ячеек, поскольку это может привести к ошибкам в расчетах.

Если рассчитанное взвешенное среднее отображает слишком много или слишком мало знаков после запятой, выберите ячейку, затем нажмите кнопку Увеличить разрядность кнопка или Уменьшить разрядность кнопка на вкладке Главная чтобы настроить отображаемые десятичные разряды.

Если вы столкнулись с ошибкой, такой как #ЗНАЧ!, дважды проверьте, что каждая указанная ячейка содержит числовое значение и что диапазоны согласованы. Также избегайте включения строки заголовков в диапазон расчетов, чтобы обеспечить точные результаты. При работе с большими наборами данных рекомендуется использовать именованные диапазоны для ясности и удобства обслуживания.

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

Для этого выберите пустую ячейку, например F8, и введите следующую формулу:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

Затем нажмите клавишу Enter, чтобы вычислить взвешенное среднее, соответствующее вашим конкретным условиям. Эта формула перемножает каждый вес и цену только если товар соответствует условию (в данном случае «Яблоко»), суммирует их и делит на сумму весов только для этого товара.

Примечание. Здесь B2:B18 — это столбец Фрукты, C2:C18 — Вес, а D2:D18 — Цена. Замените «Яблоко» на другой товар по необходимости. Этот метод хорошо работает для фильтрации по одному условию; если нужно фильтровать по нескольким критериям (например, тип фруктов и поставщик), может потребоваться дополнительный столбец или более сложная формула.

После применения формулы, возможно, захотите скорректировать десятичные разряды для ясности. Выберите ячейку с результатом и используйте кнопки Увеличить разрядность или Уменьшить разрядность на вкладке Главная чтобы изменить отображаемые десятичные разряды.

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

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

Вот как создать и использовать макрос VBA для взвешенных средних:

1. Нажмите Разработчик > Visual Basic (или нажмите Alt + F11) для открытия окна редактора Microsoft Visual Basic for Applications. Затем нажмите Вставить > Модуль, затем вставьте следующий код в новое окно модуля:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. Нажмите F5 (или нажмите кнопку Выполнить) для выполнения.
Вам будет предложено выбрать диапазоны по шагам (диапазон критериев — этот шаг можно пропустить, если он не нужен, диапазон весов и диапазон значений). Вы также можете указать конкретные критерии для фильтрации ваших расчетов или оставить поле пустым, чтобы учесть все данные. Макрос поддерживает динамические диапазоны данных, что практично, если ваша таблица часто растет или изменяется.

В конце вы получите окно сообщения с перечислением результата взвешенного среднего.

Советы:

• Этот подход автоматизирует повторяющийся анализ взвешенных средних и может быть расширен для обработки дополнительной фильтрации или вариантов вывода.
• Убедитесь, что выбранные диапазоны имеют одинаковую длину, и что типы данных согласованы.
• Включите базовую обработку ошибок, как показано (например, в случаях, когда не найдены действительные веса или сумма весов равна нулю).
• Если вы хотите применить только к отфильтрованным/видимым строкам, вы можете дополнительно улучшить код с помощью специального перечисления ячеек.

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

Среднее значение диапазона с округлением в Excel

Средняя скорость изменения в Excel

Расчет средней/сложной годовой скорости роста в Excel

Подвижное/скользящее среднее в Excel

Накопленная сумма / среднее значение в Excel