Calcular Porcentaje de Subtotales en tablas dinámicas de Excel

jueves, marzo 27, 2008

Un lector me pregunta como podemos calcular porcentajes de subtotales en una tabla dinámica de Excel.
Excel permite mostrar porcentajes del total de una tabla con facilidad, pero no tiene ningún método incorporado para calcular los porcentajes de subtotales. Veamos a qué me refiero.

Supongamos esta lista de datos



Con facilidad podemos crear una tabla dinámica que totalice las ventas por región y agente



Luego podemos usar el menú de opciones de campo para mostrar los resultados como porcentaje del total



Todo esto con algunos clics del Mouse!

Pero qué pasa si queremos calcular el porcentaje de cada agente sobre el total de cada región? Para hacer esto tendremos que dar un rodeo.

Empezamos por crear un campo auxiliar en nuestra lista de datos, que llamamos "% de Región"



Los valores de este campo los calculamos con la fórmula:

=C2/SUMAR.SI($B$2:$B$13,B2,$C$2:$C$13)

que calcula el porcentaje de cada valor sobre el total de los valores de la región. Este nuevo campo lo arrastramos al área de datos



Para evitar que Excel totalice los subtotales y muestre un total del 400%, cancelamos la opción Total de Columnas en el menú de opciones de tabla.

Technorati Tags:

11 comments:

Alejandro 27 marzo, 2008 23:59  

HOLA JORGE, JUNTO CON FELICITARTE POR TU BLOG QUE ES BUENISIMO.

QUISIERA PREGUNTARTE SI ES POSIBLE AL HACER LO DEL EJEMPLO, EL PORCENTAJE DE CADA REGION SEA CON RESPECTO AL TOTAL GENERAL Y NO QUE MUESTRE LA SUMA DE LOS PORCENTAJES DEL AGENTE CON RESPECTO A LA REGION (EL 100%)

ESPERO SE ENTIENDA MI INQUIETUD...

BUENO SALUDOS, Y GRACIAS

ALEJANDRO

Jorge L. Dunkelman 28 marzo, 2008 08:17  

Alejandro,

es lo que hace Excel por defecto, como muestro en la nota.
En el menú de configuración de campo, apretás el botón Opciones y en la ventanilla Mostrar datos como elegís % de la columna.
En tu caso también tendrías que quitar el campo Agente.

Anónimo,  28 abril, 2008 17:42  

¿Se podrián tener en una misma tabla dinámica columas que hagan distintas cuentas, por ejemplo, una columna que sume y otra columna que cuente?

Jorge L. Dunkelman 28 abril, 2008 18:40  

Si. Tienes que arrastrar el campo una segunda vez al área de datos, y luego cambiar la función con el menú de configuración de campo.
Excel le agrega un "2" al nombre del campo, pero puedes cambiar el nombre del campo a lo que te parezca.

Anónimo,  28 abril, 2008 21:53  

Muchas gracias!!!

Anónimo,  01 julio, 2008 17:45  

Primero felicitarte por el contenido tan útil del blog.
Una consulta, tengo un archivo de texto extenso desde el cual mediante la herramienta para crear cubos olap en excel 2003 genero un cubo que luego utilizo mediante una tabla dinámica, el caso es que cuando quiero hacer operaciones con esos datos utilizando un campo calculado no me ofrece esta opcion, que puedo hacer para tener esas operaciones?

Saludos,

Jorge L. Dunkelman 01 julio, 2008 22:01  

Hola

la cuestipon es que no se pueden crear campos o elementos calculados en una tabla dinámica que se basa en un cubo OLAP.
Lo que se puede intentar es crear esos campos en la base datos sobre la que se construye el cubo.

Anónimo,  17 septiembre, 2013 19:24  

Y que pasa si queremos sacar el 5% de cada agente. gracias

Jorge Dunkelman 18 septiembre, 2013 09:13  

Si te refieres a descontar el 5%, habría que incluir la operación en la fórmula de la columna auxiliar sencillamente restando 0.05 al resultado de la fórmula original.

José Orosco 09 abril, 2014 00:08  

Gracias por el aporte, muy instructivo, pero si la tabla {Agente, Region, Ventas}, se le agrega un campo mas {Mes,Agente,Región,Ventas}, me gustaria saber como calculo el porcentaje por region segun cada mes.

Jorge Dunkelman 09 abril, 2014 11:30  

Hola José, estaré publicando una nota sobre el tema en breve.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP