jueves, marzo 27, 2008

Calcular Porcentaje de Subtotales en tablas dinámicas de Excel

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:

16 comentarios:

  1. 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

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. ¿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?

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. Muchas gracias!!!

    ResponderBorrar
  6. 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,

    ResponderBorrar
  7. 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.

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

    ResponderBorrar
  9. 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.

    ResponderBorrar
  10. 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.

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

    ResponderBorrar
  12. Hola Jorge, esta muy interesante el dato, pero quiero saber si tengo un ejemplo similar, en mi caso para la parte industrial un numero x de instrumentos hacen parte de un lazo. sucede que en varios lazos se intervienen semanalmente unos inst. x ejemplo

    en el lazo X-T-101
    SE Tienen los instrumentos
    X-T-101-1
    X-T-101-2
    X-T-101-3

    Pero se intervinieron 2 de los 3 instrumentos, y quiero ver reportado el avance de la actividad por porcentaje que en este caso seria el 66,666%

    que formula podria aplicar si tengo x numero de lazos y asi mismo de instrumentos

    ResponderBorrar
  13. Riki, sinceramente, no entendí el planteo. Te sugiero que me mandes un archivo con el ejemplo y una explicación un poco más clara (¿que quiere decir "intervenir lazos"?).

    ResponderBorrar
  14. Buenas tardes si requiero insertar un campo calculado : ejemplo : las ventas * % Región, como lo puedo realizar?, puesto que insertar campo calculado no me muestra el campo %Región.
    Muchas Gracias!

    ResponderBorrar
  15. Al agregar un campo a la base de datos tienes que asegurarte de redefinir el rango de la base de datos.

    ResponderBorrar
  16. Muy bueno, gracias por el aporte.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.