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

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

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

Выберите пустую ячейку, например 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 для приложений. Затем выберите Вставка > Модуль и вставьте приведённый ниже код в новое окно модуля:

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