Muchos expertos de Excel creen que las tablas dinámicas son la herramienta más poderosa en Excel. Ninguna otra herramienta en Excel te brinda la capacidad de analizar datos como una tabla dinámica.
A continuación te voy a nombre los aspectos más improtantes que debes tener en cuenta en cualquier tabla dinamica.
Video: ¿Por qué tablas dinámicas? – fórmulas vs. tablas dinámicas
1. Puedes construir una tabla dinámica en aproximadamente un minuto
Mucha gente tiene la idea de que construir una tabla dinámica es complicado y requiere mucho tiempo, pero no es cierto. En comparación con el tiempo que llevaría crear un informe equivalente de forma manual, las tablas dinámicas son increíblemente rápidas. Si tiene los datos de origen bien organizados, puede crear una tabla dinámica de menos de un minuto. Así es cómo debes hacerlo:
- Selecciona cualquier celda en la fuente de los datos.
- En la pestaña Insertar de la cinta, haz clic en el botón de la tabla dinámica
- En el cuadro de diálogo Crear tabla dinámica, verifica los datos y haz clic en Aceptar
- Arrastra un campo «etiqueta» en el área Etiquetas de fila (por ejemplo, cliente)
Arrastra un campo numérico al área Valores (por ejemplo, ventas)
Video: Cómo crear rápidamente una tabla dinámica
Datos brutos (ventas de chocolate), listos para una tabla dinámica
Selecciona cualquier celda en los datos para comenzar.
Una tabla dinámica rápida de Excel que muestra las ventas de chocolate
Una tabla dinamica muy simple en unos 30 segundos
2. Perfecciona tus datos de origen
Para minimizar los problemas en el futuro, siempre usa un origen de datos de buena calidad, organizados en un diseño tabular. Los datos de origen «perfecto» no tendrán filas o columnas en blanco ni subtotales. Cada columna tendrá un nombre único (en una sola fila), cada campo tendrá un valor en cada fila y las columnas no contendrán grupos repetidos de datos (es decir, nombres de meses, nombres de ubicaciones, nombres de regiones, etc.).
Video: cómo completar rápidamente los datos faltantes
¡Datos perfectos para una tabla dinamica!
3. Cuenta los datos primero
Cuando creas por primera vez una tabla dinámica, úsala para generar un recuento simple primero para asegurarte de que la tabla dinámica está procesando los datos como esperas. Para hacer esto, simplemente agrega 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 ti, ya estás listo. Si el número no tiene sentido para ti, 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. Comprobar.
4. Planifica antes de construir
Aunque es muy divertido arrastrar los campos alrededor de una tabla dinámica, y ver a Excel produce otra representación inusual de los datos. Antes de comenzar a construir, anota lo que estás tratando de medir o comprender, y esboza algunos informes simples en una libreta. Estas simples notas te ayudarán a guiarte a través de la gran cantidad de opciones que tienes a tu disposición. Manten las cosas simples, y concéntrate en las preguntas que necesitas responder.
5. Usa una tabla para tus datos para crear un «rango dinámico»
Si utilizas una tabla de Excel para los datos fuente de tu tabla dinámica, obtienes un beneficio muy bueno: tu rango de datos se vuelve «dinámico». Un rango dinámico se expandirá y contraerá automáticamente a medida que agregues o elimines datos, por lo que no tendrás que preocuparte de que a la tabla dinámica le falten los datos más recientes. Cuando utilizas una tabla para su tabla dinámica, la tabla dinámica siempre estará sincronizada con tus datos.
Para usar una tabla para tu tabla dinamica:
- Selecciona cualquier celda en los datos usa el atajo de teclado Ctrl-T para crear una Tabla
- Haz clic en el botón Resumir con tabla dinámica (TableTools> Diseño)
- Construye tu tabla dinámica normalmente
- Beneficio: los datos que agregues a tu Tabla aparecerán automáticamente en tu tabla dinamica al actualizar
Video: use una tabla para su próxima mesa pivote
Crear una tabla simple a partir de los datos usando (Ctrl-T)
Ahora que tenemos una tabla, podemos usar Resumir con tabla dinámica
6. Usa una tabla dinamica para contar cosas
Por defecto, una tabla dinámica contará cualquier campo de texto. Esta puede ser una característica muy útil en muchas situaciones comerciales generales. Por ejemplo, supongamos que tienes una lista de empleados y deseas obtener un recuento por departamento. Para obtener un desglose por departamento, sigue estos pasos:
- Crea una tabla dinámica de la forma habitual
- Agrega el departamento como una etiqueta de fila
- Agrega el campo Nombre del empleado como un valor
- La tabla dinámica mostrará un recuento de empleados por Departamento
Desglose de empleados por departamento
7. Mostrar totales como un porcentaje
En muchas tablas dinámicas, querrás mostrar un porcentaje en lugar de un conteo. Por ejemplo, quizás deseas mostrar un desglose de las ventas por producto. Pero, en lugar de mostrar las ventas totales de cada producto, deseas mostrar las ventas como un porcentaje de las ventas totales. Suponiendo que tienes un campo llamado Ventas en tus datos, simplemente sigue estos pasos:
- Agrega producto a la tabla dinámica como una etiqueta de fila
- Agrega ventas a la tabla dinámica como valor
- Haz clic con el botón derecho en el campo Ventas y configura «Mostrar valores como» en «% de gran total»
Consulta el consejo a continuación «Agregar un campo más de una vez a una tabla dinámica» para obtener información sobre cómo mostrar las ventas y ventas totales como un porcentaje del total al mismo tiempo.
Cambiar la visualización del valor a % del total
Suma de empleados mostrada como % del total
8. Usa una tabla dinámica para compilar una lista de valores únicos
Debido a que las tablas dinámicas resumen los datos, se pueden usar para buscar valores únicos en un campo. Esta es una buena forma de ver rápidamente todos los valores que aparecen en un campo y también encontrar errores tipográficos y otras incoherencias. Por ejemplo, supongamos que tiene datos de ventas y desea ver una lista de cada producto que se vendió. Para crear una lista de productos:
- Crea una tabla dinámica
- Agrega el producto como una etiqueta de fila
- Agrega cualquier otro campo de texto (categoría, cliente, etc.) como valor
- La tabla dinámica mostrará una lista de todos los productos que aparecen en los datos de ventas
Todos los productos que aparecen en los datos aparecen en la lista (incluido un error tipográfico)
9. Crea una tabla dinámica autocontenida
Cuando hayas creado una tabla dinámica a partir de datos en la misma hoja de cálculo, puedes eliminar los datos si lo deseas y la tabla dinámica continuará funcionando normalmente. Esto se debe a que una tabla dinámica tiene una memoria caché dinámica que contiene un duplicado exacto de los datos utilizados para crear la tabla dinámica.
- Actualiza la tabla dinámica para asegurarte de que la memoria caché esta actualizada (Herramientas de tabla dinámica> Actualizar)
- Elimina la hoja de trabajo que contiene los datos
- Usa tu tabla dinamica de la forma habitual
Ver: Cómo hacer una tabla dinámica autónoma
10. Agrupa una tabla dinámica manualmente
Aunque las tablas dinámicas agrupan datos automáticamente de muchas maneras, también puede agrupar elementos manualmente en sus propios grupos personalizados. Por ejemplo, supongamos que tienes una tabla dinámica que muestra un desglose de empleados por departamento. Supongamos que deseas agrupar 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:
- Control-clic para seleccionar cada elemento en el primer grupo
- Haz clic con el botón derecho en uno de los elementos y elije Agrupar en el menú
- Excel crea un nuevo grupo, «Grupo1»
- Selecciona Marketing y Ventas en la columna B, y agrupa como arriba
- Excel crea otro grupo, «Group2»
Comenzando a agrupar manualmente
A la mitad de la agrupación manual: el grupo 1 está listo
Grupo finalizado manualmente
11. Agrupar datos numéricos en rangos
Una de las características más interesantes y potentes que tiene cada tabla dinámica es la capacidad de agrupar datos numéricos en intervalos o categorías. Por ejemplo, supongamos que tienes una lista de resultados de votación que incluye la edad del votante y deseas resumir los resultados por grupo de edad:
- Crea tu tabla dinámica
- Agrega edad como una etiqueta de fila, votar como una etiqueta de columna y el nombre como un valor
- Haz clic con el botón derecho en cualquier valor en el campo Edad y elije Grupo
- Ingresa 10 como el intervalo en el área de entrada «Por:»
- Al hacer clic en Aceptar, verás los datos de votación agrupados por edad en intervalos de 10 años.
Video: Cómo agrupar una tabla dinámica por rango de edad
La fuente de datos para los resultados de votación
Agrupar el campo de edad en intervalos de 10 años
Listo agrupando los resultados de votación por rango de edad
12. Cambiar el nombre de los campos para una mejor legibilidad
Cuando agregas 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ás la suma de las ventas, el recuento de la región, etc. Sin embargo, simplemente puedes sobrescribir este nombre con el suyo. Simplemente selecciona la celda que contiene el campo que deseas cambiar el nombre y escribe un nuevo nombre.
Cambiar el nombre de un campo escribiendo el nombre original
13. Agregar un espacio a los nombres de los campos cuando Excel protesta
Cuando intentes cambiar el nombre de los campos, podrías encontrarse con un problema si tratas de usar exactamente el mismo nombre de campo que aparece en los datos. Por ejemplo, supongamos que tienes un campo llamado Ventas en tus datos de origen. Como campo de valor, aparece como Suma de ventas, pero (sensatamente) deseas que diga Ventas. Sin embargo, cuando intentas utilizar Ventas, Excel se queja de que el campo ya existe y arroja un mensaje de error «El nombre de campo de tabla dinámica ya existe».
A Excel no le gusta tu nuevo nombre de campo
Como solución, simplemente agrega un espacio al final de tu nuevo nombre de campo. No puedes ver la diferencia, y Excel no se quejará.
Agregar un espacio al nombre evita el problema
14. Agrega 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 puedes agregar el mismo campo a una tabla dinámica más de una vez. Por ejemplo, supongamos que tienes una tabla dinámica que muestra un recuento de empleados por departamento.
El recuento funciona bien, pero también deseas mostrar el conteo 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 Valor:
- Agrega un campo de texto al área de Valor (por ejemplo, Nombre, Nombre, etc.)
- Por defecto, obtendrás un recuento de campos de texto
- Agrega el mismo campo nuevamente al área de Valor
- Haz clic con el botón derecho en la segunda instancia y cambia Mostrar valores como «% de gran total»
- Cambia el nombre de ambos campos como desees
Establecer un campo para mostrar el porcentaje del total
El campo Nombre se ha agregado dos veces
15. Formatear automáticamente todos los campos de valor
Cada vez que agregas un campo numérico como valor en una tabla dinámica, debes establecer el formato numérico directamente en el campo. Es posible que sientas la tentación de formatear los valores que ve en la tabla dinámica directamente, pero esta no es una buena idea, ya que 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, supon una tabla dinámica que muestra un desglose de las ventas por región. Cuando agregues 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í:
- Haz clic con el botón derecho en el campo Ventas y selecciona Configuración de campo de valor en el menú
- Haz clic en el botón Formato de número en el cuadro de diálogo Configuración de campo de valor que aparece
- Establece el formato en Contabilidad y haz clic en Aceptar para salir
Configuración de formato directamente en un campo de valor
16. Bajar para ver (o extraer) los datos detrás de un total
Cada vez que veas un total mostrado en una tabla dinámica, puedes ver y extraer fácilmente los datos que componen el total al «profundizar». Por ejemplo, supon que estás mirando una tabla dinámica que muestra el recuento de empleados por departamento. Puedes ver que hay 50 empleados en el departamento de ingeniería, pero deseas ver los nombres reales. Para ver las 50 personas que componen este número, haz doble clic directamente en el número 50 y Excel agregará una nueva hoja a tu libro de trabajo que contiene los datos exactos utilizados para calcular 50 ingenieros. Puedes usar este mismo enfoque para ver y extraer datos detrás de los totales dondequiera que los vea en una tabla dinámica.
Haz doble clic en un total para «profundizar»
Los 50 ingenieros, extraídos en una nueva hoja de forma automática
17. Clona tus tablas dinámicas cuando necesites otra vista
Una vez que tengas configurada una tabla dinámica, es posible que desees ver una vista diferente de los mismos datos. Por supuesto, puedes reorganizar su tabla dinámica existente para crear la nueva vista. Pero si estás creando un informe que planea usar y actualizar de forma continua, lo más fácil 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 consiste en duplicar la hoja de cálculo que contiene la tabla dinámica. Si tienes una tabla dinámica configurada en una hoja de cálculo con un título, etc., puede 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. Usando estos enfoques, puede hacer tantas copias como desee.
Cuando clona una tabla dinámica de esta manera, ambas tablas dinámicas comparten el mismo caché dinámico. Esto significa que cuando actualices cualquiera de los clones (o el original) se actualizarán todas las tablas pivote relacionadas.
Video: Cómo clonar una tabla dinámica
18. Desconexión de una tabla dinámica para actualizar de forma independiente
Después de haber clonado una tabla dinámica, es posible que te encuentres con una situación en la que realmente no deseas 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 has agrupado accidentalmente el mismo campo de fecha en otra tabla dinámica que no tenía la intención de cambiar. Cuando las tablas dinámicas comparten la misma memoria caché dinámica, también comparten la agrupación de campos.
Esta es una forma de eliminar el clics de una tabla dinámica, es decir, desvincularla de la memoria caché dinámica que comparte con otras tablas dinámicas en la misma hoja de cálculo:
- Cortar toda la tabla dinámica al portapapeles
- Pegue la tabla dinámica en un nuevo libro de trabajo
- Actualiza la tabla dinámica
- Copia de nuevo en el portapapeles
- Pegar de nuevo en el libro de trabajo original
- Descartar el libro de trabajo temporal
Tu tabla dinámica ahora usará su propio caché dinámico y no se actualizará con las otras tablas pivote en el libro de trabajo, o compartirá la misma agrupación de campo.
19. Deshazte de los títulos inútiles
El diseño predeterminado para 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 títulos más intuitivos, especialmente para las personas que no suelen utilizar tablas dinámicas. Una manera fácil de deshacerse de estos encabezados impares 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 reales de los campos como encabezados en la tabla dinámica, lo cual es mucho más sensato. Para deshacerse por completo de estas etiquetas, busque un botón llamado Encabezados de campo en la pestaña Analizar de la cinta Herramientas de tabla dinámica. Al hacer clic en este botón, se desactivarán los títulos por completo.
Ten en cuenta los títulos de campo inútiles y confusos
Cambiar el diseño de compacto a contorno
Los encabezados de campo en el diseño del esquema son mucho más sensibles
20. Agrega 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. Espacio en blanco solo significa espacio vacío reservado para dar espacio a la disposición. Después de crear una tabla dinámica, inserta una columna adicional a la izquierda y una o dos filas adicionales en la parte superior. Esto le dará a tu tabla dinamica algo de espacio para respirar y creará un mejor diseño. En la mayoría de los casos, también recomiendo que desactives las líneas de la cuadrícula en la hoja de trabajo. La tabla dinámica presentará una fuerte cuadrícula visual, por lo que las líneas de la cuadrícula fuera de la tabla dinámica son innecesarias y simplemente crearán un ruido visual.
Agrega un poco de espacio en blanco alrededor de las tablas dinámicas
Un pequeño espacio en blanco hace que tus tablas dinamica se vean más pulidas
21. Deshacerse de los totales generales de filas y columnas
De forma predeterminada, las tablas dinámicas muestran totales para filas y columnas, pero puede desactivar fácilmente uno o ambos de estos totales si no los quieres. En la pestaña Pivot Table de la cinta, solo haz clic en el botón Totals y elije las opciones que desees.
Habilitar y deshabilitar totales generales
Puede eliminar totales generales para filas y columnas
22. Formatea celdas vacías
Si tienes una tabla dinámica que tiene muchas celdas en blanco, puedes controlar el carácter que se muestra en cada celda en blanco. Por defecto, las celdas vacías no mostrarán nada en absoluto. Para configurar su propio personaje, haz clic en el botón derecho dentro de la tabla dinámica y selecciona las opciones de tabla dinámica. Luego, asegúrate de que «Empty cells as:» esté marcado e ingresa el carácter que deseas ver. Tenga en cuenta que esta configuración respeta el formato de número aplicado. Por ejemplo. si estás utilizando el formato de número de cuenta para un campo de valor numérico e ingresa un cero, verás un guión «-» en la tabla dinámica, ya que así es como se muestran los valores cero con el formato de Contabilidad.
La tabla dinámica muestra celdas vacías como 0 (cero) con formato de contabilidad.
Las celdas vacías configuradas para mostrar 0 (cero) y el formato de número de contabilidad le dan guiones.
23. Desactivar Auto ajuste cuando sea necesario
De forma predeterminada, cuando actualizas una tabla dinámica, las columnas que contienen datos se ajustan automáticamente para ajustarse mejor a los datos. Normalmente, esto es algo bueno, pero puede volverse 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 desactivar esta característica, haz clic en el botón derecho dentro de la tabla dinámica y elije Opciones de tabla dinámica. En la primera pestaña de las opciones desmarque «Autoajustar anchos de columna en la actualización».
Opción de autoajuste de columna de tabla dinámica para Windows.
Puedes aprender Tablas Dinámicas por tu cuenta, leer blogs y ver videos de YouTube. Pero también puedes aprender Tablas Dinámicas de una forma más RÁPIDA, con un curso de video, donde te enseñaré paso a paso. Si estás interesado apuntate al curso que empezaremos el 2 de Mayo haciendo clic aquí.
Deja una respuesta
Lo siento, debes estar conectado para publicar un comentario.