Cálculos Ponderados y Lógica Condicional
Desliza para mostrar el menú
SUMIFS funciona bien para totales condicionales estándar, pero algunos cálculos analíticos requieren ponderaciones y lógica incorporada. En este capítulo, se amplía el sistema de agregación utilizando SUMPRODUCT.
Estructura de SUMPRODUCT
=SUMPRODUCT(array1, [array2], [array3], ...)
array1: primer arreglo de cálculo;[array2]: segundo arreglo opcional;[array3]: arreglos adicionales opcionales.
SUMPRODUCT multiplica los valores correspondientes y luego suma los resultados.
Las condiciones lógicas dentro de la fórmula se convierten automáticamente en:
TRUE = 1;FALSE = 0.
Estructura de Promedio Ponderado
=SUMPRODUCT(values * weights) / SUM(weights)
values: métrica evaluada;weights: factor de importancia.
Los promedios ponderados otorgan mayor influencia a las transacciones de mayor valor en el resultado final.
Escribe:
=SUMPRODUCT(Sales_Data[Revenue]) / COUNTA(Sales_Data[Revenue])
SUMPRODUCT(...): total Revenue;COUNTA(...): número de filas de Revenue.
La fórmula devuelve el promedio de Revenue por pedido.
Escribe:
=SUMPRODUCT((Sales_Data[Region]="North")*Sales_Data[Revenue]) / SUMPRODUCT((Sales_Data[Region]="North")*1)
(Sales_Data[Region]="North"): condición lógica;*Sales_Data[Revenue]: valores de Revenue filtrados;SUMPRODUCT(...*1): conteo condicional de filas.
La fórmula calcula el promedio de Revenue solo para la región North.
Escribe:
=SUMPRODUCT(Sales_Data[Discount_Rate]*Sales_Data[Revenue]) / SUM(Sales_Data[Revenue])
Sales_Data[Discount_Rate]: porcentajes de descuento;Sales_Data[Revenue]: factor de ponderación.
Las transacciones con mayor Revenue ahora influyen más en el promedio de descuento.
Dentro de la tabla Summary, escribe:
=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],A2,Sales_Data[Performance tier],"High")
Sales_Data[Region],A2: filtro de región;Sales_Data[Performance tier],"High": filtro de desempeño.
La fórmula devuelve el Revenue generado solo por transacciones de alto desempeño.
Escribe:
=COUNTIFS(Sales_Data[Region],A2,Sales_Data[Performance tier],"High")
La fórmula cuenta todos los pedidos de alto nivel para la región seleccionada.
Escribe:
=E2/C2
E2: Revenue de alto nivel;C2: Revenue total.
La fórmula mide la proporción de Revenue generado por transacciones de alto desempeño.
Aplicar:
- Formato de moneda a las métricas de Revenue;
- Formato de porcentaje a los ratios y cálculos de descuento.
Agregar filas adicionales en Sales_Data.
Confirmar que:
- El promedio de Revenue se recalcula automáticamente;
- Los cálculos de descuento ponderado se actualizan automáticamente;
- Las métricas de alto nivel se actualizan automáticamente.
1. ¿Por qué es útil SUMPRODUCT para cálculos ponderados?
2. ¿Cuál es una limitación clave de SUMIFS en comparación con SUMPRODUCT?
3. En una fórmula de promedio ponderado usando SUMPRODUCT, ¿qué representa el denominador?
¡Gracias por tus comentarios!
Pregunte a AI
Pregunte a AI
Pregunte lo que quiera o pruebe una de las preguntas sugeridas para comenzar nuestra charla
