Muchos expertos de Excel creen que las tablas dinámicas son la herramienta más poderosa en Excel. Ninguna otra herramienta en Excel le brinda la flexibilidad y el poder analítico de una tabla dinámica. Las tablas dinámicas son entretenidas y poderosas. Así que, sin ningún orden en particular, aquí hay algunas cosas que debes saber sobre ellas.
1.Puedes hacer tablas dinámicas en excel en menos de un minuto
Mucha gente tiene la idea de que hacer una tabla dinámica es complicado y requiere mucho tiempo, pero simplemente no es cierto. En comparación con el tiempo que llevaría crear un informe equivalente de forma manual, las tablas dinámicas se pueden hacer increíblemente rápida. Si dispone de una fuente de datos bien organizados, puedes crear una tabla dinámica de menos de un minuto. Así es cómo debe hacer:
- Seleccione cualquier celda en la fuente de datos
- En la pestaña "Insertar" de la cinta de opciones, haga clic en el botón de "Tabla dinámica".
- En el cuadro de diálogo Crear tabla dinámica, seleccione los datos y haga clic en Aceptar.
- Arrastre un campo "Etiqueta" en el área Etiquetas de fila (por ejemplo, Región)
- Arrastre un campo numérico al área Valores (por ejemplo, Vendidas)
Seleccione cualquier celda en los datos para comenzar.
Una tabla dinámica muy simple en unos 30 segundos
2. Perfeccione sus datos de origen
Para minimizar los problemas en el futuro, siempre use datos de origen de buena calidad, organizados en un diseño tabular. Los datos de origen "perfectos" no tendrán filas o columnas en blanco ni subtotales. Cada columna tendrá un nombre único (en una fila solamente), cada campo tendrá un valor en cada fila y las columnas no contendrán grupos repetidos de datos.
¡Datos perfectos para una tabla dinamica!
3. Primero cuente los datos
Cuando cree por primera vez una tabla dinámica, úsela primero para generar un recuento simple, para asegurarse de que la tabla dinámica está procesando los datos como usted lo 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, ya estás listo. 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.
19 regiones significa que tenemos 19 registros en la tabla.
4. Planifique antes de construir
Aunque es muy divertido arrastrar los campos alrededor de una tabla dinámica, y ver a Excel producir otra representación inusual de los datos, usted puede caer en una situación compleja e improductiva con mucha facilidad. Una hora más tarde, ya no será tan divertido. Antes de comenzar a construir, anote lo que está tratando de medir o entender, y esboce algunos informes simples en una libreta. Estas simples notas lo ayudarán a guiarlo 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. Use una tabla con sus datos para crear un "rango dinámico"
Si utiliza una tabla de Excel para los datos fuente de su tabla dinámica, obtiene un beneficio muy bueno: su rango de datos se vuelve "dinámico". Un rango dinámico se expandirá y contraerá automáticamente 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 utiliza una tabla de excel para su tabla dinámica, la tabla dinámica siempre estará sincronizada con sus datos.
Para usar una tabla para su tabla dinamica:
- Seleccione cualquier celda en los datos, use el atajo de teclado Ctrl-T para crear una Tabla
- Haga clic en el botón Resumir con tabla dinámica (Diseño >Herramientas de tabla)
- Genere su tabla dinámica normalmente
- Beneficio: los datos que agregue a su Tabla aparecerán automáticamente en su tabla dinámica al actualizar.
Crear una tabla simple a partir de los datos usando (Ctrl-T)
Ahora que tenemos una tabla, podemos usar la opción Resumir con tabla dinámica
6. Use 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 tiene una lista de empleados y desea obtener un recuento por departamento. Para obtener un desglose por departamento, siga estos pasos:
- Cree una tabla dinámica normalmente
- Agregue el departamento como una etiqueta de fila
- Agregue el campo Numero del empleado como un valor
- La tabla dinámica mostrará un recuento de empleados por Departamento
Distribución de empleados por departamento.
7. Mostrar totales como un porcentaje
En muchas tablas dinámicas, querrá mostrar un porcentaje en lugar de un conteo. 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:
- Agregue el producto a la tabla dinámica como una etiqueta de fila
- Agregue las ventas a la tabla dinámica como valor
- Haga clic con el botón derecho en el campo Ventas y configure "Mostrar valores como" en "% del total general"
Cambiar la visualización del valor a % del total general
Suma de empleados por departamento visualizada como % del total general
8. Use una tabla dinámica para generar 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 normalmente
- Agregue el producto como una etiqueta de fila
- Agregue 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 sin repetir ninguno.
Cada producto que aparece en los datos está listado.
9. Crea una tabla dinámica autocontenida
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 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.
- Actualice la tabla dinámica para asegurarse de que la memoria caché esté actualizada (Herramientas de tabla dinámica>Analizar> Actualizar)
- Eliminar la hoja de trabajo que contiene los datos.
- Usa tu tabla dinámica normalmente.
10. Agrupe 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 tiene una tabla dinámica que muestra la distribución de empleados por departamento. Supongamos que desea agrupar los departamentos de administración, ingeniería y soporte en el Grupo 1 y Marketing y Ventas 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 especificos, Grupo 1 y Grupo 2:
- Ctrl+clic para seleccionar cada elemento en el primer grupo
- Haga clic derecho en uno de los elementos y elija Agrupar en el menú
- Excel crea un nuevo grupo, "Grupo1"
- Seleccione Marketing y Ventas en la columna B, y agrupe como arriba
- Excel crea otro grupo, "Grupo2"
Comenzamos 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 tiene una lista de resultados de examenes de Ingles que incluye la edad, Nivel y desea resumir los resultados por grupo de edad:
- Crea tu tabla dinámica normalmente
- Agregue la edad como una etiqueta de fila, Nivel como una etiqueta de columna y el nombre como un valor
- Haga clic derecho en cualquier valor en el campo Edad y elija Grupo
- Ingrese 10 como el intervalo en el área de entrada "Por:"
- Al hacer clic en Aceptar, verá los datos de nivel agrupados por edad en intervalos de 10 años.
La fuente de datos para los resultados de los Niveles de Ingles
Agrupe el campo de edad en intervalos de 10 años
Resultados de los niveles de ingles agrupados por rango de edad
12. Cambie el nombre de los campos para una mejor 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 "Cuenta de" cuando se agreguen a una tabla dinámica. Por ejemplo, verá la suma de las ventas, el recuento de la región, y así sucesivamente. Sin embargo, simplemente puede sobrescribir este nombre con el suyo. Simplemente seleccione la celda que contiene el campo que desea cambiar el nombre y escriba un nuevo nombre.
Cambie el nombre de un campo escribiendo el nombre original
13. Agregue un espacio a los nombres de los campos cuando Excel arroja error
Cuando intente cambiar el nombre de los campos, podría encontrarse con un problema si trata de usar exactamente el mismo nombre de campo que aparece en los datos. Por ejemplo, supongamos que tiene un campo llamado Ventas en sus datos de origen. Como campo de valor, aparece como Suma de ventas, pero (sensatamente) desea que diga Ventas. Sin embargo, cuando intenta utilizar Ventas, Excel se queja de que el campo ya existe y arroja un mensaje de error "El nombre de tabla dinámica ya existe".
A Excel no le gusta su nombre nuevo de campo
Como solución simple, simplemente agregue un espacio al final de su nombre nuevo de campo. No puede 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 la tabla dinámica
Hay muchas situaciones en las que tiene sentido añadir el mismo campo a una tabla pivot más de una vez. Puede parecer extraño, pero puede añadir el mismo campo a una tabla pivotante más de una vez. Por ejemplo, supongamos que tiene una tabla dinámica que muestra el número 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 sencilla es añadir el mismo campo dos veces como campo Valor:
- Añada un campo de texto al área de valor (por ejemplo, Nombres, Apellidos, etc.).
- De forma predeterminada, obtendrá un recuento de los campos de texto.
- Vuelva a añadir el mismo campo al área de valor.
- Haga clic con el botón derecho del ratón en la segunda instancia y cambie Mostrar valores como "% del total general".
- Cambie el nombre de ambos campos como desee
Configuración de un campo para que muestre el porcentaje del total
El campo Nombres se ha añadido dos veces.
15. Formatear automáticamente todos los campos de valor
Cada vez que agrega un campo numérico como valor en una tabla dinámica, debe establecer el formato numérico directamente en el campo. Puede sentir 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, suponga que hay una tabla dinámica que muestra la distribución 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í:
- Haga clic derecho en el campo Ventas y seleccione Configuración de campo de valor en el menú
- Haga clic en el botón Formato de número en el cuadro de diálogo Configuración de campo de valor que aparece
- Establezca el formato en Contabilidad y haga clic en Aceptar para salir
Configuración de formato directamente en un campo de valor
16. Examinar a fondo (o extraer) los datos detrás de un total
Cada vez que vea un total mostrado en una tabla dinámica, puede ver y extraer fácilmente los datos que componen el total al "profundizar". Por ejemplo, suponga que está mirando una tabla dinámica que muestra el recuento de empleados por departamento. Puede ver que hay 5 empleados en el departamento de ingeniería, pero desea ver los nombres reales. Para ver las 5 personas que componen este número, haga doble clic directamente en el número 5 y Excel agregará una nueva hoja a su libro de trabajo que contiene los datos exactos utilizados para calcular 5 empleados del departamento de ingeniería. Puede usar 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 "examinar a fondo"
Los 5 de Administracion seran extraídos en una nueva hoja automáticamente
17. Clone sus tablas dinámicas cuando necesite otra vista
Una vez que tenga configurada una tabla dinámica, es posible que desee ver una vista diferente de los mismos datos. Por supuesto, puede reorganizar su tabla dinámica existente para crear la nueva vista. Pero si está 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 trabajo que contiene la tabla dinámica. Si tiene una tabla dinámica configurada en una hoja de cálculo con un título, etc., puede hacer clic 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 actualice cualquiera de los clones (o el original) se actualizarán todas las tablas dinámicas relacionadas.
18. Desclone una tabla dinámica para actualizar de forma independiente
Después de haber clonado una tabla dinámica, es posible que se encuentre con una situación en la que realmente no desea 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 otra tabla dinámica que no tenía 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 los 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:
- Corte toda la tabla dinámica al portapapeles
- Pegue la tabla dinámica en un nuevo libro de trabajo
- Actualiza la tabla dinámica
- Copie nuevamente en el portapapeles la tabla dinámica
- Pegue nuevamente en el libro de trabajo original
- Descartar el libro de trabajo temporal
- Su tabla dinámica ahora usará su propio caché dinámico y no se actualizará con las otras tablas dinámicas en el libro de trabajo, ni 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 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 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.
Tenga en cuenta los títulos de campo inútiles y confusos
Cambie el diseño de Compacto a Esquema
Los encabezados de campo en el diseño del esquema son mucho más adecuados.
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 espacio a la disposición. Después de crear una tabla dinámica, inserte una columna adicional a la izquierda y una o dos filas adicionales en la parte superior. Esto le dará a su tabla dinámica algo de espacio para respirar y creará un mejor diseño. En la mayoría de los casos, también recomendamos que desactive 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 ruido visual.
- Seleccione el campo que desea dejar en blanco.
- Presione Ctrl+Shift+F
- Dirijase a relleno y haga clic en el color blanco debajo de "color de fondo".
- Haga clic en Aceptar.
Un pequeño espacio en blanco hace que sus tablas dinámicas se vean más pulidas
21. Deshágase 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 quiere. En la pestaña Diseño de la cinta Tabla dinámica, solo haga clic en el botón Totales generales y elija las opciones que desee.
Puede eliminar totales generales para filas y columnas
22. Formatee 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. Por defecto, las celdas vacías no mostrarán nada en absoluto. Para configurar su propio carácter, haga clic derecho dentro de la tabla dinámica y seleccione opciones de tabla dinámica. Luego, asegúrese de que "Para celdas vacías, mostrar:" esté marcado 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.
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 actualiza 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 sacarlo de quicio si tiene otras cosas en la hoja de trabajo junto con la tabla dinámica, o si ha ajustado cuidadosamente el ancho de las columnas manualmente y no desea que se modifiquen. Para desactivar esta función, haga clic 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), desmarque "Autoajustar anchos de columnas al actualizar".
Opción de autoajuste de columna de tabla dinámica para Windows
