¿Cómo se calcula el promedio ponderado en Excel?

Imagine que tiene una lista de la compra como la que se muestra en la siguiente captura de pantalla. Mientras que la función PROMEDIO de Excel le daría el precio medio sin considerar el peso o la cantidad, un enfoque más preciso en estos casos consiste en calcular el promedio ponderado, ya que refleja mejor el coste real por unidad al otorgar a los artículos con pesos o cantidades mayores una influencia proporcionalmente mayor en el resultado final.

Para calcular el precio promedio ponderado, utilice una combinación de las funciones SUMAPRODUCTOy SUMAde la siguiente manera:

Seleccione una celda en blanco, como F2, e introduzca la siguiente fórmula:

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

y pulse la tecla Intro para obtener el resultado.

Nota: En esta fórmula, C2:C18 hace referencia a la columna Peso y D2:D18 hace referencia a la columna Precio. Ajuste estos rangos según su disposición de datos. La función SUMAPRODUCTO multiplica cada peso por el precio correspondiente y suma los resultados, mientras que SUMA totaliza los pesos, obteniendo así el promedio ponderado correcto. Asegúrese de utilizar rangos de igual longitud y de que no haya valores incompatibles ni celdas vacías en sus datos, ya que esto podría provocar errores de cálculo.

Si el promedio ponderado calculado muestra demasiados o demasiados pocos decimales según sus preferencias, seleccione la celda y haga clic en el botón Aumentar decimales o Disminuir decimales en la pestaña Inicio para ajustar los decimales mostrados según sea necesario. tab to adjust the displayed Decimales as needed.

Si encuentra un error como #¡VALOR!, compruebe que cada celda referenciada contenga un valor numérico y que los rangos sean coherentes. Además, evite incluir cualquier fila de encabezado en su rango de cálculo para garantizar resultados precisos. Al trabajar con conjuntos de datos más grandes, considere la posibilidad de utilizar rangos con nombre para mayor claridad y facilidad de mantenimiento.

La fórmula anterior calcula el precio promedio ponderado de todos los artículos. En análisis prácticos, es posible que desee obtener el promedio ponderado de categorías específicas, como el precio promedio ponderado exclusivamente para manzanas. En estos casos, puede perfeccionar la fórmula incorporando una condición basada en sus criterios.

Para ello, seleccione una celda en blanco, como F8, e introduzca la siguiente fórmula:

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

A continuación, pulse la tecla Intro para calcular el promedio ponderado que cumpla sus criterios específicos. Esta fórmula multiplica cada par de peso y precio únicamente si el artículo coincide con la condición («Manzana» en este caso), suma los resultados y los divide entre la suma de los pesos correspondientes a dicho artículo.

Nota: Aquí, B2:B18 es la columna Fruta, C2:C18 es el Peso y D2:D18 es el Precio. Sustituya «Manzana» por otro artículo según sea necesario. Este método funciona perfectamente para filtrar con un solo criterio; si necesita aplicar varios filtros (por ejemplo, tipo de fruta y proveedor), puede que necesite usar una columna auxiliar o una fórmula más avanzada.

Tras aplicar la fórmula, es posible que desee ajustar los decimales para mayor claridad. Seleccione la celda del resultado y utilice los botones Aumentar decimales o Disminuir decimales en la pestaña Inicio para modificar los decimales mostrados.

Si la fórmula devuelve un resultado inesperado, asegúrese de que el criterio coincida con valores dentro del rango objetivo y verifique si hay celdas vacías o entradas de texto en columnas que deberían contener valores numéricos.

En ciertas situaciones, es posible que necesite calcular con frecuencia promedios ponderados sobre rangos de tamaño variable, que incluyan valores ausentes o que requieran un filtrado flexible —por ejemplo, aplicar varios criterios simultáneamente—. En lugar de actualizar manualmente fórmulas o rangos, automatizar este cálculo mediante una macro de VBA le ahorra tiempo y reduce el riesgo de errores, lo que resulta especialmente útil al trabajar con grandes volúmenes de datos o conjuntos que se actualizan de forma regular.

A continuación se explica cómo crear y utilizar una macro de VBA para promedios ponderados:

1. Haga clic en Programador > Visual Basic(o pulse)Alt + F11) para abrir la ventana del editor de Microsoft Visual Basic para Aplicaciones. A continuación, haga clic en Insertar > Módulo y pegue el siguiente código en la nueva ventana del módulo:

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. Pulse F5(o haga clic en el) botón Ejecutar) para ejecutarla.
Se le pedirá que seleccione los rangos paso a paso (rango de criterios —que puede omitir si no es necesario—, rango de pesos y rango de valores). También puede introducir criterios específicos para filtrar el cálculo o dejarlo en blanco para incluir todos los datos. La macro admite rangos dinámicos, lo que la hace ideal si su tabla crece o cambia con frecuencia.

Por último, recibirá un cuadro de mensaje con el resultado del promedio ponderado.

Consejos:

• Este enfoque automatiza el análisis repetitivo de promedios ponderados y puede ampliarse aún más para gestionar opciones adicionales de filtrado o salida.
• Asegúrese de que los rangos seleccionados tengan la misma longitud y que sus tipos de datos sean coherentes.
• Incluya un control básico de errores, como el que se muestra (por ejemplo, para los casos en que no se encuentren pesos válidos o la suma de los pesos sea cero).
• Si desea aplicarlo únicamente a las filas filtradas o visibles, puede optimizar aún más el código mediante una enumeración especial de celdas.

Si experimenta problemas relacionados con permisos o la seguridad de las macros, asegúrese de que las macros estén habilitadas en la configuración de Excel antes de ejecutar el código.

Promedio de un rango con redondeo en Excel

Tasa media de cambio en Excel

Calcular la tasa media/anual de crecimiento compuesto en Excel

Promedio móvil en Excel

Total acumulado / promedio acumulado en Excel