Te doy la bienvenida a este nuevo tutorial en el que vas a aprender a hacer porcentajes sobre tablas dinámicas.
A simple vista, parece sencillo, ¿verdad?
Una división por aquí, un formato porcentual por allá y ¡listo!
Pues ¡NO! Si fuera así de simple…yo no estaría aquí para explicártelo.
A ver, que tampoco es que sea nada complicado, pero como todo en la vida, hay que saber hacerlo, y las tablas dinámicas tienen sus truquitos.
Así pues,
- si lo has intentado y ya te has encontrado con algunos problemillas
- o no lo has probado nunca porque no tienes ni idea de cómo hacerlo…
¡No sufras más!
En menos de 10 minutos te explico varias formas de hacerlo para que no tengas que preocuparte por ello nunca más.
Y además vas a poder hacerlo de una forma:
- Flexible
- Dinámica
- Y que te permita hacer más tipos de análisis a la vez.
Un tutorial completito y directo al grano.
Así que, si estás preparado, ¡vamos a sacarle jugo a las tablas dinámicas!
Espero que te haya gustado y servido de ayuda.
¿Qué dices? ¿Qué no puedes verlo ahora mismo? Es una lástima porque hoy me he puesto mi mejor camisa…¡pero no te preocupes!
Te dejo aquí abajo el paso a paso para que no te pierdas nada.
Cómo hacer porcentajes sobre tablas dinámicas
Dispones de un libro Excel en el que tienes una hoja con datos en formato tabla.
Crea una tabla dinámica sencilla.
Si no sabes cómo se hacen las tablas dinámicas te aconsejo que primero eches un vistazo a este tutorial.
Ahora, imagínate que necesitas sacar el porcentaje de ventas totales por provincia respecto del total de ventas.
Crea la tabla con Provincias y Suma de Ventas.
Primera opción: parche
Antes de empezar con la primera opción, déjame explicarte que una división normal no te servirá, porque las celdas a las que tienes que hacer referencia pertenecen a una tabla dinámica, por lo que ya arrastran sus funciones.
A esta primera opción, yo la llamo parche, porque es un recurso fácil si no sabes usar la opción correcta.
Y ahora sí, empecemos:
1. Colócate en la celda donde quieres obtener los resultados.
2. Ahora toca hacer la división, pero OJO, en lugar de pinchar en las celdas que quieres dividir, escríbelo manualmente: =B2/B10
3. Aplica formato porcentual
4. Inmoviliza la celda que no quieres que se mueva cuando arrastres la fórmula con una referencia absoluta (atajo F4)
Pues esta sería la forma más básica de hacerlo.
Pero ya que estamos con tablas dinámicas, vamos a exprimir un poco más su potencial y a hacerlo cómo se debe.
Segunda opción para crear porcentajes sobre tablas dinámicas: la correcta.
Recuerda que en los valores has bajado Provincias y Suma de Ventas.
1. Baja de nuevo el campo Suma de Ventas a los valores.
2. Abre el desplegable del segundo campo Suma de Ventas
3. Clica en Configuración de campo de valor
4. Abre la segunda pestaña Mostrar valores como
5. Escoge la opción % del total general
6. Dale a aceptar y listo!
Mira como quedan tus porcentajes. ¡Valor en estado puro!
Tercera opción: vamos más allá
No te conformas con el porcentaje de ventas, también quieres añadir el % de beneficio respecto del total.
1. Añade a la tabla dinámica el valor Suma de Beneficio.
2. En la barra de herramientas, pestaña Analizar, abre el desplegable Campos, elementos y conjuntos.
3. Escoge la opción Campo calculado
4. En el nombre escribe: Margen de Beneficio
5. En Fórmulas, pinchando el los campos que aparecen abajo introduce Beneficio/Ventas
6. Ahora ya tienes el nuevo campo creado y como ves sale algo raro y parece que está mal, pero solo es porque el formato de celda no es el correcto.
7. Para arreglarlo y que todo esté correcto, solo tienes que ir a la pestaña Inicio y cambiar el formato a %.
Y ahora sí, ya sabes la mejor manera de crear porcentajes sobre una tabla dinámica.
Como ves las tablas dinámicas dan muchísimo juego a la hora de crear porcentajes.
Puedes quedarte con la primera opción, pero sabiendo estas otras dos, yo no lo dudaría ni un segundo, porque las posibilidades que tienes son inmensas.
Aprovecha a sacarle todo el rendimiento a estas tablas y consigue que Excel trabaje por ti, y además en un abrir y cerrar de ojos!
Y si quieres aprender más y sacarle el verdadero jugo a todo esto, apúntate a la academia.
Aquí tendrás:
- Acceso a muchísimos cursos completos.
- Platillas y recursos premium.
- Un webinar al mes.
- Un curso nuevo al mes.
- Podrás darte de alta y de baja cuando quieras.
- Y además tendrás mi ayuda para todas las dudas que puedan surgirte.
Excelente muchas gracias me ayudo mucho
Me alegra que haya sido de utilidad para ti Luis.
Cualquier duda aquí me tienes compañero.
A darle caña!!!
Un abrazo,
Excelente y muchas gracias!!
Gracias Raúl!!
Hola Juan Guillermo,
En el ejemplo Miguel explica como sacar el porcentaje de venta de cada ciudad sobre el total general, tu pregunta es un poco confusa, quieres el porcentaje de venta por ciudad sobre que? no se si me explico, puedes aclararme este punto? si no también puedes pasarnos un correo y seguro que por esa vía le damos solución.
Espero haber ayudado.
Saludos!!
Miguel, gracias por tu tutorial que me parece excelente. Y te explicas muy bien. La dificultad la tengo a la hora de ponerlo en la práctica, con una tabla dinamica mia sencilla. Para el porcentaje sobre el total: -> si pongo, (por ejemplo) dos columnas de ventas 2023 y 2022, la fórmula me calcula el porcentaje no del total de cada columna (sería sobre el total de cada año que es lo que busco automatizar) si no del total de la suma de las dos columnas… Y otro caso que no me sale, es a la hora de añadir una tercera y cuarta columna -> la terdera sería la diferencia de ventas entre 2023 y 2022 y la cuarta lo mismo solo que en porcentaje de diferencia. ¿Tienes algún tutorial sobre estos ejemplos, o equivalentes? Muchas gracias. Agustín
Hola Agustin,
Si claro en el canal hay otros tutoriales mios que hablan sobre este tema.
Si te quedas con ganas de más, te veo dentro de la Escuela que es donde tenemos el contenido más amplio.
Un abrazo,
Miguel
Hola Miguel como estas? te hago la siguiente consulta,
Necesito informar en un grafico (que se alimenta de una tabla dinámica) el porcentaje de las ventas de los 10 principales clientes de los últimos 5 años. Informando por año, el tema es que en la tabla dinámica, que ya la tengo en porcentaje en base a la suma del total de la columna, cuando filtro por los 10 principales clientes, me cambia el porcentaje de cada cliente porque me lo toma sobre el total de la columna filtrada por los 10 principales clientes, entonces la información que me llega al grafico no es correcta.
Espero ser claro.
HOla Juan
. El reto que estás enfrentando es que al filtrar los 10 principales clientes en la tabla dinámica, Excel recalcula los porcentajes con base en el subtotal de esos clientes, en lugar de mantener los porcentajes relativos al total de las ventas de todos los clientes. Esto puede distorsionar los datos en el gráfico.
Solución:
Lo que necesitas es una manera de mostrar los 10 principales clientes pero conservando los porcentajes calculados sobre el total de las ventas (sin importar el filtro). Aquí te propongo dos posibles soluciones:
1. Agregar una columna calculada en la tabla de origen:
Puedes agregar una columna calculada en los datos de origen que calcule el porcentaje de ventas de cada cliente sobre el total global de las ventas (sin filtro). Así, aunque filtres por los 10 principales clientes en la tabla dinámica, los porcentajes seguirán reflejando el total general.
Los pasos serían:
Agregar una columna adicional en los datos originales que calcule el porcentaje de cada cliente. Puedes usar una fórmula como esta en la tabla de datos:
=[@Ventas] / SUM([Ventas])
Luego, crea una tabla dinámica y asegúrate de que la columna que representa el porcentaje se utilice en lugar del porcentaje recalculado por la tabla dinámica.
2. Usar un campo calculado en la tabla dinámica:
Otra opción es crear un campo calculado dentro de la tabla dinámica para que el porcentaje siempre se calcule en función del total general, incluso cuando aplicas filtros.
Los pasos son:
Ve a la tabla dinámica.
En el menú de herramientas de la tabla dinámica, selecciona Análisis de tabla dinámica > Campos, elementos y conjuntos > Campo calculado.
En la fórmula del campo calculado, divide las ventas de cada cliente por el total de ventas (sin ningún filtro aplicado):
= SUM(Ventas) / SUM(TotalVentas)
Esto te permitirá que el porcentaje sea relativo al total global, no solo a los 10 principales.
3. Usar un gráfico sin tabla dinámica (opcional):
Si no necesitas usar una tabla dinámica, otra opción es crear un gráfico directamente desde los datos originales, donde puedes calcular los porcentajes y aplicar los filtros necesarios a mano para seleccionar los 10 principales clientes.
Saludos.
Muuuuuuchas gracias… aprendí un truco más. Excelente explicación.
Encantados de que te haya servido, abrazoo!
Buen día Miguel
Necesito de tu ayuda urgentemente.
Tengo una tabla dinámica de un listado de tiendas que cada una tiene un objetivo diferente, y esas tiendas están ordenadas es grupos y quisiera sacar el objetivo de cada grupo, intento aplicar el apartado de promedio, pero me he dado cuenta que esa no es la forma correcta de aplicarlo. Me podrías ayudar
Si deseas calcular el objetivo del grupo, probablemente necesitas sumar los objetivos individuales de las tiendas en cada grupo o calcular un promedio ponderado (si aplica), dependiendo de cómo se define el objetivo del grupo en tu caso.
A continuación, te doy algunas opciones y pasos para resolverlo correctamente:
1. Si el objetivo del grupo es la suma de los objetivos de las tiendas:
En la tabla dinámica:
Asegúrate de que la columna “Objetivo” está en el área de Valores.
Cambia el tipo de cálculo:
Haz clic en la flecha junto al campo en el área de valores.
Selecciona Configuración de campo de valor.
Escoge Suma como el cálculo (en lugar de promedio).
Esto sumará los objetivos de todas las tiendas dentro de cada grupo.
2. Si el objetivo del grupo es un promedio ponderado:
Un promedio ponderado es necesario si los objetivos individuales de las tiendas dependen de algún otro factor, como el tamaño de la tienda, las ventas, etc. En este caso:
Asegúrate de que tienes una columna en tu tabla de origen que represente el peso (por ejemplo, ventas o tamaño de la tienda).
Agrega una columna calculada en tu tabla de origen con la fórmula:
excel
Copiar código
= [Objetivo] * [Peso]
Cambia [Peso] por el nombre de la columna que determina la importancia relativa de cada tienda.
En la tabla dinámica:
Agrega esta nueva columna (“Objetivo ponderado”) al área de valores y selecciona Suma.
También agrega la columna de pesos al área de valores y selecciona Suma.
Crea un campo calculado (en la tabla dinámica) que divida la suma de los objetivos ponderados entre la suma de los pesos:
= ‘Suma de Objetivo Ponderado’ / ‘Suma de Peso’
3. Si solo necesitas un promedio simple por grupo:
En este caso:
Asegúrate de que la columna “Objetivo” está en el área de valores.
Cambia el tipo de cálculo a Promedio:
Haz clic derecho en cualquier celda del área de valores.
Selecciona Mostrar valores como > Promedio.
Esto dará un promedio simple de los objetivos individuales dentro de cada grupo.
Saludos.
Estimado muy buenos días.
En la PARTE QUE MENCIONAS COMO VAMOS MÁS ALLA
El campo calculado funciona correctamente porque los campos usan la configuración de SUMA_DE, una consulta ¿Cómo podría realizar el campo calculado si uno de los campos no tuviese la configuración SUMA_DE sino, por ejemplo, PROMEDIO_DE? Muchas gracias de antemano
Hola Luis
En las tablas dinámicas, los campos calculados siempre toman los valores base del origen de datos antes de que se apliquen los agregados como SUMA, PROMEDIO, etc. Esto significa que, aunque visualmente estés viendo un campo con “PROMEDIO_DE”, el campo calculado internamente seguirá operando sobre los valores individuales, no sobre el promedio. Por eso, si necesitas un cálculo que realmente use el promedio (o cualquier otro agregado distinto de la suma), no puedes lograrlo con un campo calculado tradicional. En su lugar, te recomendaría transformar los datos en el origen o utilizar Power Pivot, donde puedes crear medidas DAX que sí permiten trabajar directamente con agregados como PROMEDIO, MAX, MIN, etc., logrando así una mayor flexibilidad en tus análisis.