Consejos para la tabla dinámica

Las tablas dinámicas son un motor de informes integrado en Excel. Son la mejor herramienta en Excel para analizar datos. sin fórmulas . Puede crear una tabla dinámica básica en aproximadamente un minuto y comenzar a explorar sus datos de forma interactiva. A continuación, encontrará más de 20 consejos para aprovechar al máximo esta herramienta flexible y poderosa.

1. Puede crear una tabla dinámica en aproximadamente un minuto

Mucha gente piensa que crear una tabla dinámica es complicado y requiere mucho tiempo, pero simplemente no es cierto. En comparación con el tiempo que le tomaría crear un informe equivalente manualmente, las tablas dinámicas son increíblemente rápidas. Si tiene datos de origen bien estructurados, puede crear una tabla dinámica en menos de un minuto. Comience seleccionando cualquier celda en los datos de origen:

Datos de origen de ejemplo

A continuación, siga estos cuatro pasos:

1. En la pestaña Insertar de la cinta, haga clic en el botón Tabla dinámica
2. En el cuadro de diálogo Crear tabla dinámica, verifique los datos y haga clic en Aceptar
3. Arrastre un campo de 'etiqueta' al área Etiquetas de fila (por ejemplo, cliente)
4. Arrastre un campo numérico al área de valores (por ejemplo, ventas)

Una tabla dinámica básica en unos 30 segundos.

La tabla dinámica anterior muestra las ventas totales por producto, pero puede reorganizar fácilmente los campos para mostrar las ventas totales por región, por categoría, por mes, etc. Mire el video a continuación para una demostración rápida:

Video: Cómo crear rápidamente una tabla dinámica

2. Limpia tus datos de origen

Para minimizar los problemas en el futuro, asegúrese de que sus datos estén en buen estado. Los datos de origen no deben tener filas o columnas en blanco, ni subtotales. Cada columna debe tener un nombre único (solo en una fila) y representar un campo para cada fila / registro en los datos:

si la celda contiene texto, devuelva el valor en otra celda

¡Datos perfectos para una tabla dinámica!

En ocasiones, es posible que deba agregar datos faltantes. Vea este video para obtener sugerencias:

Video: Cómo completar rápidamente los datos faltantes

3. Cuente los datos primero

Cuando cree una tabla dinámica por primera vez, utilícela para generar un recuento simple primero para asegurarse de que la tabla dinámica esté procesando los datos como espera. Para hacer esto, simplemente agregue cualquier campo de texto como un campo de valor. Verá una tabla dinámica muy pequeña que muestra el recuento total de registros, es decir, el número total de filas en sus datos. Si este número tiene sentido para usted, está listo para comenzar. Si el número no tiene sentido para usted, es posible que la tabla dinámica no esté leyendo los datos correctamente o que los datos no se hayan definido correctamente.

300 nombres significa que tenemos 300 empleados. Cheque.

4. Planifique antes de construir

Aunque es muy divertido arrastrar campos alrededor de una tabla dinámica y ver cómo Excel produce otra representación inusual de los datos, puede encontrarse con muchos agujeros de conejo improductivos con mucha facilidad. Una hora después, ya no es tan divertido. Antes de comenzar a construir, anote lo que está tratando de medir o comprender y esboce algunos informes simples en un bloc de notas. Estas sencillas notas le ayudarán a guiarle a través de la gran cantidad de opciones que tiene a su disposición. Mantenga las cosas simples y concéntrese en las preguntas que necesita responder.

5. Utilice una tabla para sus datos para crear un 'rango dinámico'.

Si usa una tabla de Excel para los datos de origen de su tabla dinámica, obtiene un beneficio muy bueno: su rango de datos se vuelve 'dinámico'. Un rango dinámico expandirá y reducirá automáticamente la tabla a medida que agregue o elimine datos, por lo que no tendrá que preocuparse de que a la tabla dinámica le falten los datos más recientes. Cuando usa una tabla para su tabla dinámica, la tabla dinámica siempre estará sincronizada con sus datos.

Para usar una tabla para su tabla dinámica:

1. Seleccione cualquier celda en los datos, use el atajo de teclado Ctrl-T para crear una tabla
2. Haga clic en el botón Resumir con tabla dinámica (Herramientas de tabla> Diseño)
3. Construye tu tabla dinámica normalmente
4. Beneficio: los datos que agregue a su tabla aparecerán automáticamente en su tabla dinámica al actualizar

Video: Use una tabla para su próxima tabla dinámica

Crear una tabla simple a partir de los datos usando (Ctrl-T)

Ahora que tenemos una tabla, podemos usar Resumir con una tabla dinámica

6. Use una tabla dinámica para contar cosas

De forma predeterminada, una tabla dinámica contará cualquier campo de texto. Esta puede ser una característica muy útil en muchas situaciones comerciales generales. Por ejemplo, suponga que tiene una lista de empleados y desea obtener un recuento por departamento. Para obtener un desglose por departamento, siga estos pasos:

1. Cree una tabla dinámica normalmente
2. Agregar el departamento como una etiqueta de fila
3. Agregue el campo Nombre del empleado como Valor
4. La tabla dinámica mostrará un recuento de empleados por departamento

Desglose de empleados por departamento

7. Muestre los totales como porcentaje

En muchas tablas dinámicas, querrá mostrar un porcentaje en lugar de un recuento. Por ejemplo, quizás desee mostrar un desglose de las ventas por producto. Pero, en lugar de mostrar las ventas totales de cada producto, desea mostrar las ventas como un porcentaje de las ventas totales. Suponiendo que tiene un campo llamado Ventas en sus datos, simplemente siga estos pasos:

1. Agregar producto a la tabla dinámica como una etiqueta de fila
2. Agregue ventas a la tabla dinámica como valor
3. Haga clic con el botón derecho en el campo Ventas y establezca 'Mostrar valores como' en '% del total general'

Vea la sugerencia a continuación 'Agregar un campo más de una vez a una tabla dinámica' para aprender cómo mostrar las ventas totales y las ventas como un porcentaje del total al mismo tiempo.

Cambio de visualización de valor a% del total

Suma de empleados mostrada como% del total

8. Utilice una tabla dinámica para crear una lista de valores únicos.

Debido a que las tablas dinámicas resumen los datos, se pueden usar para encontrar valores únicos en un campo. Esta es una buena manera de ver rápidamente todos los valores que aparecen en un campo y también encontrar errores tipográficos y otras inconsistencias. Por ejemplo, suponga que tiene datos de ventas y desea ver una lista de todos los productos que se vendieron. Para crear una lista de productos:

1. Cree una tabla dinámica normalmente
2. Agregar el producto como una etiqueta de fila
3. Agregue cualquier otro campo de texto (categoría, cliente, etc.) como Valor
4. La tabla dinámica mostrará una lista de todos los productos que aparecen en los datos de ventas.

Se enumeran todos los productos que aparecen en los datos (incluido un error tipográfico)

Entrenamiento en video de la tabla dinámica: rápido, limpio y al grano

9. Cree una tabla dinámica autónoma

Cuando haya creado una tabla dinámica a partir de datos en la misma hoja de trabajo, puede eliminar los datos si lo desea y la tabla dinámica seguirá funcionando normalmente. Esto se debe a que una tabla dinámica tiene una escondite pivote que contiene un duplicado exacto de los datos utilizados para crear la tabla dinámica.

1. Actualice la tabla dinámica para asegurarse de que la caché esté actualizada (Herramientas de tabla dinámica> Actualizar)
2. Eliminar la hoja de trabajo que contiene los datos
3. Utilice su tabla dinámica normalmente

Video: Cómo hacer una tabla dinámica autónoma

10. Agrupe una tabla dinámica manualmente

Aunque las tablas dinámicas agrupan datos automáticamente de muchas formas, también puede agrupar elementos manualmente en sus propios grupos personalizados. Por ejemplo, suponga que tiene una tabla dinámica que muestra un desglose de empleados por departamento. Suponga que desea agrupar aún más los departamentos de Ingeniería, Cumplimiento y Soporte en el Grupo 1, y Ventas y Marketing en el Grupo 2. El Grupo 1 y el Grupo 2 no aparecen en los datos, son sus propios grupos personalizados. Para agrupar la tabla dinámica en los grupos ad hoc, Grupo 1 y Grupo 2:

1. Control-clic para seleccionar cada elemento en el primer grupo
2. Haga clic con el botón derecho en uno de los elementos y elija Agrupar en el menú
3. Excel crea un nuevo grupo, 'Grupo1'
4. Seleccione Marketing y Ventas en la columna B y agrupe como se indicó anteriormente.
5. Excel crea otro grupo, 'Grupo2'

Empezando a agrupar manualmente

A mitad de camino de la agrupación manual: el grupo 1 está terminado

Agrupación finalizada manualmente

11. Agrupe los datos numéricos en rangos

Una de las características más interesantes y poderosas que tiene cada tabla dinámica es la capacidad de agrupar datos numéricos en rangos o depósitos. Por ejemplo, suponga que tiene una lista de resultados de votación que incluye la edad de los votantes y desea resumir los resultados por grupo de edad:

1. Cree su tabla dinámica normalmente
2. Agregue la edad como etiqueta de fila, vote como etiqueta de columna y nombre como valor
3. Haga clic con el botón derecho en cualquier valor en el campo Edad y elija Grupo
4. Ingrese 10 como el intervalo en el área de entrada 'Por:'
5. Al hacer clic en Aceptar, verá los datos de votación agrupados por edad en grupos de 10 años

Video: Cómo agrupar una tabla dinámica por rango de edad

Los datos de origen de los resultados de la votación

Agrupar el campo de edad en grupos de 10 años

Terminé de agrupar los resultados de la votación por rango de edad

12. Cambie el nombre de los campos para mejorar la legibilidad

Cuando agrega campos a una tabla dinámica, la tabla dinámica mostrará el nombre que aparece en los datos de origen. Los nombres de los campos de valor aparecerán con 'Suma de' o 'Recuento de' cuando se agreguen a una tabla dinámica. Por ejemplo, verá Suma de ventas, Recuento de región, etc. Sin embargo, simplemente puede sobrescribir este nombre con el suyo. Simplemente seleccione la celda que contiene el campo al que desea cambiar el nombre y escriba un nuevo nombre.

Cambiar el nombre de un campo escribiendo sobre el nombre original

13. Agregue un espacio a los nombres de los campos cuando Excel se queja

Cuando intenta cambiar el nombre de los campos, es posible que tenga un problema si intenta utilizar exactamente el mismo nombre de campo que aparece en los datos. Por ejemplo, suponga que tiene un campo llamado Ventas en sus datos de origen. Como campo de valor, aparece como Suma de ventas , pero (sensatamente) quieres que diga Ventas . Sin embargo, cuando intenta utilizar Sales, Excel se queja de que el campo ya existe y muestra el mensaje de error 'El nombre del campo de tabla dinámica ya existe'.

A Excel no le gusta su nuevo nombre de campo

Como solución alternativa simple, simplemente agregue un espacio al final de su nuevo nombre de campo. No puede ver una diferencia y Excel no se quejará.

Agregar un espacio al nombre evita el problema

14. Agregar un campo más de una vez a una tabla dinámica

Hay muchas situaciones en las que tiene sentido agregar el mismo campo a una tabla dinámica más de una vez. Puede parecer extraño, pero de hecho puede agregar el mismo campo a una tabla dinámica más de una vez. Por ejemplo, suponga que tiene una tabla dinámica que muestra un recuento de empleados por departamento.

El recuento funciona bien, pero también desea mostrar el recuento como un porcentaje del total de empleados. En este caso, la solución más simple es agregar el mismo campo dos veces como un campo de valor:

1. Agregue un campo de texto al área de valor (por ejemplo, nombre, nombre, etc.)
2. De forma predeterminada, obtendrá un recuento de los campos de texto.
3. Agregue el mismo campo nuevamente al área Valor
4. Haga clic con el botón derecho en la segunda instancia y cambie Mostrar valores como a '% del total general'
5. Cambie el nombre de ambos campos como desee

Configurar un campo para mostrar el porcentaje del total

El campo Nombre se ha agregado dos veces

15. Dar formato automáticamente a todos los campos de valor

Siempre que agregue un campo numérico como Valor en una tabla dinámica, debe establecer el formato de número directamente en el campo. Puede tener la tentación de formatear los valores que ve en la tabla dinámica directamente, pero esto no es una buena idea, porque no es confiable a medida que cambia la tabla dinámica. Establecer el formato directamente en el campo garantizará que el campo se muestre con el formato que desee, sin importar cuán grande o pequeña sea la tabla dinámica.

Por ejemplo, suponga una tabla dinámica que muestra un desglose de las ventas por región. Cuando agregue por primera vez el campo Ventas a la tabla dinámica, se mostrará en formato de número general, ya que es un campo numérico. Para aplicar el formato de número de contabilidad al campo en sí:

¿Cómo sombreo filas alternas en Excel?

1. Haga clic con el botón derecho en el campo Ventas y seleccione Configuración del campo de valor en el menú
2. Haga clic en el botón Formato de número en el cuadro de diálogo de configuración del campo Valor que aparece
3. Establezca el formato en Contabilidad y haga clic en Aceptar para salir

Establecer formato directamente en un campo de valor

16. Profundice para ver (o extraer) los datos detrás de cualquier total

Siempre que vea un total que se muestra en una tabla dinámica, puede ver y extraer fácilmente los datos que componen el total 'desglosando'. Por ejemplo, suponga que está mirando una tabla dinámica que muestra el recuento de empleados por departamento. Puede ver que hay 50 empleados en el departamento de Ingeniería, pero desea ver los nombres reales. Para ver las 50 personas que componen este número, haga doble clic directamente en el número 50 y Excel agregará una nueva hoja a su libro de trabajo que contiene los datos exactos utilizados para calcular 50 ingenieros. Puede utilizar este mismo enfoque para ver y extraer datos detrás de los totales dondequiera que los vea en una tabla dinámica.

Haga doble clic en un total para 'profundizar'

Los 50 ingenieros, extraídos en una nueva hoja automáticamente

17. Clone sus tablas dinámicas cuando necesite otra vista

Una vez que haya configurado una tabla dinámica, es posible que desee ver una vista diferente de los mismos datos. Por supuesto, podría simplemente reorganizar su tabla dinámica existente para crear la nueva vista. Pero si está creando un informe que planea usar y actualizar de manera continua, lo más fácil de hacer es clonar una tabla dinámica existente, de modo que ambas vistas de los datos estén siempre disponibles.

Hay dos formas sencillas de clonar una tabla dinámica. La primera forma implicó duplicar la hoja de trabajo que contiene la tabla dinámica. Si tiene una tabla dinámica configurada en una hoja de trabajo con un título, etc., puede simplemente hacer clic con el botón derecho en la pestaña de la hoja de trabajo para copiar la hoja de trabajo en el mismo libro de trabajo. Otra forma de clonar una tabla dinámica es copiar la tabla dinámica y pegarla en otro lugar. Con estos enfoques, puede hacer tantas copias como desee.

Cuando clona una tabla dinámica de esta manera, ambas tablas dinámicas comparten lo mismo escondite pivote . Esto significa que cuando actualice cualquiera de los clones (o el original), se actualizarán todas las tablas dinámicas relacionadas.

Video: Cómo clonar una tabla dinámica

18. Anule la clonación de una tabla dinámica para actualizarla de forma independiente

Después de haber clonado una tabla dinámica, es posible que se encuentre en una situación en la que realmente no desee que el clon esté vinculado al mismo caché dinámico que el original. Un ejemplo común es después de haber agrupado un campo de fecha en una tabla dinámica, actualizar y descubrir que también ha agrupado accidentalmente el mismo campo de fecha en otro tabla dinámica que no tenía la intención de cambiar. Cuando las tablas dinámicas comparten la misma caché dinámica, también comparten la agrupación de campos.

Aquí hay una forma de anular la clonación de una tabla dinámica, es decir, desvincularla de la caché dinámica que comparte con otras tablas dinámicas en la misma hoja de trabajo:

1. Corta toda la tabla pivotante en el portapapeles.
2. Pegue la tabla dinámica en un libro de trabajo completamente nuevo
3. Actualizar la tabla dinámica
4. Cópialo de nuevo en el portapapeles.
5. Péguelo de nuevo en el libro de trabajo original
6. Descartar el libro temporal

Su tabla dinámica ahora usará su propia caché dinámica y no se actualizará con las otras tablas dinámicas en el libro de trabajo, ni compartirá la misma agrupación de campos.

19. Deshazte de los títulos inútiles

El diseño predeterminado para las nuevas tablas dinámicas es el diseño compacto. Este diseño mostrará 'Etiquetas de fila' y 'Etiquetas de columna' como encabezados en la tabla dinámica. Estos no son los encabezados más intuitivos, especialmente para las personas que no suelen utilizar tablas dinámicas. Una forma fácil de deshacerse de estos extraños encabezados es cambiar el diseño de la tabla dinámica de Compacto a Esquema o Diseño tabular. Esto hará que la tabla dinámica muestre los nombres de los campos reales como encabezados en la tabla dinámica, lo cual es mucho más sensato. Para deshacerse de estas etiquetas por completo, busque un botón llamado Encabezados de campo en la pestaña Analizar de la cinta Herramientas de tabla dinámica. Hacer clic en este botón desactivará los títulos por completo.

Tenga en cuenta los títulos de campo inútiles y confusos

Cambiar el diseño de Compacto a Esquema

Los encabezados de campo en el diseño del esquema son mucho más sensibles

20. Agregue un poco de espacio en blanco alrededor de sus tablas dinámicas.

Este es solo un consejo de diseño simple. Todos los buenos diseñadores saben que un diseño agradable requiere un poco de espacio en blanco. El espacio en blanco solo significa espacio vacío reservado para dar un respiro al diseño. Después de crear una tabla dinámica, inserte una columna adicional a la izquierda y una fila o dos adicionales en la parte superior. Esto le dará a su mesa pivotante algo de espacio para respirar y creará un diseño más atractivo. En la mayoría de los casos, también recomiendo que desactive las líneas de cuadrícula en la hoja de trabajo. La tabla dinámica en sí presentará una cuadrícula visual fuerte, por lo que las líneas de cuadrícula fuera de la tabla dinámica son innecesarias y simplemente crearán ruido visual.

Un pequeño espacio en blanco hace que sus tablas dinámicas se vean más pulidas

Inspiración: 5 tablas dinámicas que no has visto antes .

21. Deshazte de los totales generales de filas y columnas

De forma predeterminada, las tablas dinámicas muestran los totales de las filas y las columnas, pero puede deshabilitar fácilmente uno o ambos totales si no los desea. En la pestaña Tabla dinámica de la cinta, simplemente haga clic en el botón Totales y elija las opciones que desee.

Puede eliminar totales generales tanto para filas como para columnas

22. Dar formato a celdas vacías

Si tiene una tabla dinámica que tiene muchas celdas en blanco, puede controlar el carácter que se muestra en cada celda en blanco. De forma predeterminada, las celdas vacías no mostrarán nada en absoluto. Para establecer su propio carácter, haga clic con el botón derecho dentro de la tabla dinámica y seleccione las opciones de la tabla dinámica. Luego, asegúrese de que la casilla 'Vaciar celdas como:' esté marcada e ingrese el carácter que desea ver. Tenga en cuenta que esta configuración respeta el formato de número aplicado. Por ejemplo. Si está utilizando el formato de número de contabilidad para un campo de valor numérico e ingresa un cero, verá un guión '-' en la tabla dinámica, ya que así es como se muestran los valores cero con el formato de contabilidad.

Celdas vacías configuradas para mostrar 0 (cero) y el formato de número de contabilidad le proporciona guiones

23. Apague AutoFit cuando sea necesario

De forma predeterminada, cuando actualiza una tabla dinámica, las columnas que contienen datos se ajustan automáticamente para adaptarse mejor a los datos. Normalmente, esto es algo bueno, pero puede volverlo loco si tiene otras cosas en la hoja de trabajo junto con la tabla dinámica, o si ha ajustado cuidadosamente los anchos de columna manualmente y no quiere que se modifiquen. Para deshabilitar esta función, haga clic con el botón derecho dentro de la tabla dinámica y elija Opciones de tabla dinámica. En la primera pestaña de las opciones (o la pestaña de diseño en una Mac), desmarque 'Autoajustar anchos de columna en la actualización'.

Opción de ajuste automático de columna de tabla dinámica para Windows

Opción de ajuste automático de columna de tabla dinámica para Mac

¿Necesitas aprender las tablas dinámicas? Tenemos entrenamiento de video sólido con hojas de trabajo de práctica.