Calcular promedio ponderado en tablas dinámicas

viernes, mayo 27, 2011

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”






10 comments:

Samuel 27 mayo, 2011 21:11  

Excelente aporte Jorge.

Esta duda, al igual que a un servidor, le surgio a una persona de nombre Juan que anduvo por todos los foros que hablaban sobre el tema en la red y no encontro una solucion. Cabe señalar que esta solucion que se ofrece en este Blog resulta ser muy sencilla.

Muchas gracias por este aporte que sin duda beneficiara a muchos que necesitan obtener el promedio ponderado por saber la importancia del mismo.

Samuel 27 mayo, 2011 21:14  

Excelente aporte Jorge.

Esta duda, al igual que a un servidor, le surgio a una persona de nombre Juan que anduvo por todos los foros que hablaban sobre el tema en la red y no encontro una solucion. Cabe señalar que esta solucion que se ofrece en este Blog resulta ser muy sencilla.

Muchas gracias por este aporte que sin duda beneficiara a muchos que necesitan obtener el promedio ponderado por saber la importancia del mismo.

equiman 01 junio, 2011 00:52  

Lo intente igual que tu lector y finalmente no me dio. Ahora veo que en campos calculados también se puede hacer referencia a celdas y no solo a nombres de campos.

Gracias por la info. Lo que si no me queda claro es si luego adicionas columnas toca volver a modificar la "formula" del campo calculado?

Jorge L. Dunkelman 02 junio, 2011 06:25  

Equiman,
no, en campos calculados no se puede hacer referencia a celdas como tampoco usar nombres definidos.

Anónimo,  17 mayo, 2012 18:04  

Hola! Gracias por este tip. Pero, como podemos hacer para poder seguir viendo el promedio ponderado si en nuestra dinamica aplicamos un filtro? Para el ejemplo, como podemos seguir viendo el promedio ponderado correcto si ademas de por persona lo queremos ver solo para enero?

Jorge L. Dunkelman 19 mayo, 2012 11:41  

Hmmm... no me queda clara la consulta. Si filtramos la tabla el promedio mostrado será el correcto para los datos filtrados. ¿Tal vez te refieres a filtrar los datos por mes pero ver el promedio ponderado de todo el año?

Ursula 19 mayo, 2012 20:53  

Hola, una consulta. Tengo en una sola columna precios, unos en dólares ($) y otros en soles (S/.) ambos con el formato moneda. Quiero que me sume en una celda sólo los dólares y en otra sólo los soles. Estoy utilizando SUMAR.SI (=SUMAR.SI(F5:F16,"=$*",F5:F16)) pero me sale 0. Ayúdame por favor. Gracias


pago 1 $994,857.00
pago 2 S/. 663,847.00
pago 3 S/. 9,937,476.00
pago 4 $88,373.00
pago 5 $3,948,757.00
pago 6 S/. 22,838.00
pago 7 $2,092,947.00
pago 8 S/. 220,120.00
pago 9 $5,549,384.00
pago 10 $7,773,874.00
pago 11 $8,823,459.00
pago 12 S/. 11,102,838.00

Jorge L. Dunkelman 22 mayo, 2012 14:50  

Ursula,
no puedes usar el símbolo del formato como criterio para sumar condicionalmente. Si te fijas en la barra de las fórmulas, verás que el símbolo (a sea $ o soles) no aparece. El formato sólo es la forma en que Excel muestra el número en pantalla. Excel "ve" sólo el número.
Estaré publicando una nota sobre el tema en breve.

Anónimo,  30 agosto, 2012 18:55  

Hola, Muchas gracias, por tus post tan detallados. Tengo una duda con el uso de tablas dinamicas.
Tengo una tabla dinamica que cree, y este tiene 3 tablas. en una de las tablas tengo codigos de productos (la cual tiene la clave principal hacia la de ventas) y en la otra una de ventas.
En la tabla de productos tiene un campo que contiene un descuento dependiendo del cliente (tercera tabla).
Para darte una mejor idea:
cliente 1 0001 9%
cliente 2 0002 5%.
.
.
El punto es que tengo que crear una tabla dinamica y a la hora de querer crear un campo calculado (total_ventas_producto * porcentaje) no me da el resultado esperado, ya que me suma los valores del porcentaje.
Ya que me interesa saber cuanto $ se vendio por producto y cuanto aplicandole descuento.

Espero haberme explicado bien

Jorge L. Dunkelman 01 septiembre, 2012 09:32  

En lugar de usar un campo calculado te sugiero que agregues ese campo en la tabla de origen de los datos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP