Calcular promedio ponderado en tablas dinámicas

Si queremos calcular un promedio ponderado en una tabla dinámica de Excel, lo más probable es que intentemos hacerlo creando un campo calculado. Esto es lo que intentó uno de mis lectores sólo para descubrir que SUMAPRODUCTO no da los resultados esperados cuando se usa en un campo calculado.

Vamos a describir el problema (y luego mostrar la solución). Supongamos esta tabla de datos

Queremos calcular la venta promedio por operación de cada vendedor, es decir, el promedio de las ventas ponderado por la cantidad de operaciones que cada uno efectuó. La idea es obtener los resultados que aparecen en la columna “ponderado” (nótese la diferencia en relación al promedio simple)

Para realizar el cálculo en una tabla dinámica (lo que es muy conveniente si el tamaño y los componentes de nuestros datos cambian con frecuencia) podríamos estar tentados a crear un campo calculado con esta fórmula

=SUMAPRODUCTO('Total de Ventas','Nro. De Ventas' )/SUMA('Nro. De Ventas' )

Pero, como señalo más arriba, esta fórmula no funciona en campos calculados de tablas dinámicas.

La solución es crear un campo auxiliar (promedio ponderado) con esta fórmula


=(D3*E3)/SUMAR.SI($C$3:$C$11,C3,$E$3:$E$11)

Ahora podemos crear la tabla dinámica, donde el promedio ponderado para cada vendedor será calculado en el campo Promedio Ponderado usando la operación Suma

Como ven, la tabla no muestra la fila de totales. Esto se debe a que el total acumulado que mostraría la tabla dinámica para el campo “promedio ponderado” sería incorrecto:

Si queremos mostrar los totales para las columnas “Ventas” y “Nro. De Ventas”, que están calculados correctamente, podemos ocultar el total de “Promedio Ponderado” dándole a la fuente el mismo color que el fondo de la celda volviéndolo de esta manera “invisible”