Trabajar con tablas dinámicas en Microsoft Excel

Las tablas dinámicas son una de las características más poderosas de Microsoft Excel. Permiten analizar grandes cantidades de datos y resumirlos con solo unos pocos clics del mouse. En este artículo, exploramos las tablas dinámicas, entendemos qué son y aprendemos cómo crearlas y personalizarlas..

Nota: este artículo está escrito en Excel 2010 (Beta). El concepto de una tabla dinámica ha cambiado poco a lo largo de los años, pero el método para crear una ha cambiado en casi todas las versiones de Excel. Si está utilizando una versión de Excel que no es 2010, espere diferentes pantallas de las que ve en este artículo..

Una pequeña historia

En los primeros días de los programas de hojas de cálculo, Lotus 1-2-3 determinó el lugar. Su dominio fue tan completo que la gente pensó que era una pérdida de tiempo para que Microsoft se molestara en desarrollar su propio software de hoja de cálculo (Excel) para competir con Lotus. Pasamos a 2010, y el dominio de Excel en el mercado de las hojas de cálculo es mayor que el de Lotus, mientras que el número de usuarios que todavía ejecutan Lotus 1-2-3 se está acercando a cero. ¿Cómo pasó esto? Lo que causó un cambio tan dramático de fortunas?

Los analistas de la industria lo atribuyen a dos factores: en primer lugar, Lotus decidió que esta nueva y elegante plataforma GUI llamada "Windows" era una moda pasajera que nunca despegaría. Se negaron a crear una versión de Lotus 1-2-3 para Windows (de todos modos, por algunos años), prediciendo que su versión de DOS del software era todo lo que cualquier persona necesitaría. Microsoft, naturalmente, desarrolló Excel exclusivamente para Windows. En segundo lugar, Microsoft desarrolló una función para Excel que Lotus no proporcionó en 1-2-3, a saber: Tablas dinamicas. La característica de tablas dinámicas, exclusiva de Excel, se consideró tan útil que las personas estaban dispuestas a aprender un paquete de software completamente nuevo (Excel) en lugar de seguir con un programa (1-2-3) que no lo tenía. Esta característica, junto con el error de juicio sobre el éxito de Windows, fue la sentencia de muerte para Lotus 1-2-3, y el comienzo del éxito de Microsoft Excel..

Entendiendo las tablas dinámicas

Entonces, ¿qué es una tabla dinámica, exactamente?

En pocas palabras, una tabla dinámica es un resumen de algunos datos, creados para permitir un análisis fácil de dichos datos. Pero a diferencia de un resumen creado manualmente, las tablas dinámicas de Excel son interactivas. Una vez que haya creado uno, puede cambiarlo fácilmente si no ofrece la información exacta sobre sus datos que estaba esperando. En un par de clics, el resumen puede ser "pivotado", rotado de tal manera que los encabezados de las columnas se conviertan en encabezados de fila, y viceversa. Hay mucho más que se puede hacer, también. En lugar de intentar describir todas las características de las tablas dinámicas, simplemente las demostraremos ...

Los datos que analiza utilizando una tabla dinámica no pueden ser simplemente alguna datos - tiene que ser crudo datos, previamente no procesados (no resumidos) - típicamente una lista de algún tipo. Un ejemplo de esto podría ser la lista de transacciones de ventas en una compañía durante los últimos seis meses.

Examine los datos que se muestran a continuación:

Note que esto es no datos sin procesar De hecho, ya es un resumen de algún tipo. En la celda B3 podemos ver $ 30,000, que aparentemente es el total de las ventas de James Cook para el mes de enero. Entonces, ¿dónde están los datos en bruto? ¿Cómo llegamos a la cifra de $ 30,000? ¿Dónde está la lista original de transacciones de ventas que generó esta cifra? Está claro que en algún lugar, alguien debe haberse tomado la molestia de recopilar todas las transacciones de ventas de los últimos seis meses en el resumen que vemos arriba. ¿Cuánto tiempo supones que esto tomó? ¿Una hora? Diez?

Probablemente, sí. Ya ves, la hoja de cálculo de arriba es en realidad no una tabla dinámica. Se creó manualmente a partir de datos sin procesar almacenados en otros lugares, y de hecho, tomó un par de horas para compilar. Sin embargo, es exactamente el tipo de resumen que podría Puede crearse utilizando tablas dinámicas, en cuyo caso solo habría tardado unos segundos. Averigüemos cómo ...

Si tuviéramos que rastrear la lista original de transacciones de ventas, podría ser algo como esto:

Es posible que se sorprenda al saber que, mediante la función de tabla dinámica de Excel, podemos crear un resumen de ventas mensuales similar al anterior en unos pocos segundos, con solo unos pocos clics del mouse. Podemos hacer esto - y mucho más también!

Cómo crear una tabla dinámica

Primero, asegúrese de tener algunos datos en bruto en una hoja de cálculo en Excel. Una lista de transacciones financieras es típica, pero puede ser una lista de casi cualquier cosa: detalles de contacto del empleado, su colección de CD o cifras de consumo de combustible para la flota de automóviles de su compañía.

Así que empezamos Excel ... y cargamos una lista así ...

Una vez que tengamos la lista abierta en Excel, estamos listos para comenzar a crear la tabla dinámica.

Haga clic en cualquier celda individual dentro de la lista:

Entonces, desde el Insertar pestaña, haga clic en el Tabla dinámica icono:

los Crear tabla dinámica Aparece un cuadro que le hace dos preguntas: ¿En qué datos debería basarse su nueva tabla dinámica y dónde debería crearse? Debido a que ya hicimos clic en una celda dentro de la lista (en el paso anterior), la lista completa que rodea a esa celda ya está seleccionada para nosotros ($ A $ 1: $ G $ 88 sobre el Pagos hoja, en este ejemplo). Tenga en cuenta que podríamos seleccionar una lista en cualquier otra región de cualquier otra hoja de trabajo, o incluso alguna fuente de datos externa, como una tabla de base de datos de Access, o incluso una tabla de base de datos de MS-SQL Server. También debemos seleccionar si queremos que nuestra nueva tabla dinámica se cree en un nuevo hoja de trabajo, o en una existente uno. En este ejemplo seleccionaremos un nuevo uno:

La nueva hoja de trabajo se crea para nosotros y se crea una tabla dinámica en esa hoja de trabajo:

También aparece otra casilla: Lista de campos de tabla dinámica. Esta lista de campos se mostrará cuando hagamos clic en cualquier celda dentro de la tabla dinámica (arriba):

La lista de campos en la parte superior del cuadro es en realidad la colección de encabezados de columna de la hoja de trabajo de datos sin procesar original. Los cuatro cuadros en blanco en la parte inferior de la pantalla nos permiten elegir la forma en que nos gustaría que nuestra tabla dinámica resumiera los datos en bruto. Hasta ahora, no hay nada en esas casillas, por lo que la tabla dinámica está en blanco. Todo lo que necesitamos hacer es arrastrar los campos hacia abajo desde la lista anterior y soltarlos en los cuadros inferiores. Una tabla dinámica se crea automáticamente para que coincida con nuestras instrucciones. Si nos equivocamos, solo tenemos que arrastrar los campos de regreso a donde vinieron y / o arrastrar nuevo campos abajo para reemplazarlos.

los Valores La caja es posiblemente la más importante de las cuatro. El campo que se arrastra a este cuadro representa los datos que deben resumirse de alguna manera (sumando, promediando, encontrando el máximo, el mínimo, etc.). Es casi siempre numérico datos. Un candidato perfecto para este cuadro en nuestros datos de muestra es el campo / columna "Cantidad". Vamos a arrastrar ese campo a la Valores caja:

Observe que (a) el campo "Cantidad" en la lista de campos ahora está marcado, y "Suma de cantidad" se ha agregado a la Valores casilla, indicando que la columna de cantidad ha sido sumada.

Si examinamos la propia tabla dinámica, de hecho, encontramos la suma de todos los valores de "Cantidad" de la hoja de trabajo de datos sin procesar:

¡Hemos creado nuestra primera tabla dinámica! Práctico, pero no particularmente impresionante. Es probable que necesitemos un poco más de información sobre nuestros datos que eso.

Refiriéndonos a nuestros datos de muestra, necesitamos identificar uno o más encabezados de columna que podríamos usar para dividir este total. Por ejemplo, podemos decidir que nos gustaría ver un resumen de nuestros datos donde tenemos una encabezado de fila para cada uno de los diferentes vendedores en nuestra empresa, y un total para cada uno. Para lograr esto, todo lo que tenemos que hacer es arrastrar el campo "Vendedor" a la Etiquetas de fila caja:

Ahora, ¡Finalmente, las cosas empiezan a ponerse interesantes! Nuestra tabla dinámica comienza a tomar forma ... .

Con un par de clics hemos creado una tabla que habría tardado mucho tiempo en hacerlo manualmente.

Entonces, ¿qué más podemos hacer? Bueno, en cierto sentido, nuestra tabla dinámica está completa. Hemos creado un resumen útil de nuestros datos de origen. ¡Lo importante ya está aprendido! Para el resto del artículo, examinaremos algunas formas en que se pueden crear tablas dinámicas más complejas y formas en que se pueden personalizar esas tablas dinámicas..

Primero, podemos crear un dos-mesa dimensional Hagamos eso usando "Método de pago" como encabezado de columna. Simplemente arrastre el título "Método de pago" a la Etiquetas de columna caja:

Que se parece a esto:

Empezando a llegar muy guay!

Vamos a hacerlo un Tres-mesa dimensional ¿Cómo podría ser una mesa así? Bien, veamos…

Arrastre la columna / encabezado “Paquete” al Informe de filtro caja:

Fíjate dónde termina ... .

Esto nos permite filtrar nuestro informe según el "paquete de vacaciones" comprado. Por ejemplo, podemos ver el desglose del vendedor frente al método de pago para todos paquetes, o, con un par de clics, cambiarlo para mostrar el mismo desglose para el paquete "Sunseekers":

Y así, si lo piensa de la manera correcta, nuestra tabla dinámica ahora es tridimensional. Sigamos personalizando ...

Si resulta, digamos, que solo queremos ver. cheque y tarjeta de credito transacciones (es decir, no transacciones en efectivo), entonces podemos deseleccionar el elemento "Efectivo" de los encabezados de las columnas. Haga clic en la flecha desplegable junto a Etiquetas de columna, y desmarque "efectivo":

Veamos cómo se ve ... Como se puede ver, "Cash" se ha ido.

Formateo

Este es obviamente un sistema muy poderoso, pero hasta ahora los resultados se ven muy simples y aburridos. Para empezar, los números que estamos sumando no parecen montos en dólares, simplemente números antiguos. Vamos a rectificar eso.

Una tentación podría ser hacer lo que estamos acostumbrados a hacer en tales circunstancias y simplemente seleccionar toda la tabla (o toda la hoja de trabajo) y usar los botones de formato de número estándar en la barra de herramientas para completar el formato. El problema con este enfoque es que si alguna vez cambia la estructura de la tabla dinámica en el futuro (que es 99% probable), esos formatos de número se perderán. Necesitamos una forma que los haga (semi) permanentes..

Primero, ubicamos la entrada “Suma de Monto” en el Valores cuadro, y haga clic en él. Aparece un menú. Seleccionamos Configuración del campo de valor ... desde el menú:

los Configuración del campo de valor aparece el cuadro.

Haga clic en el Formato numérico botón, y el estándar Formato de caja de celdas aparece:

Desde el Categoría lista, seleccione Contabilidad, y suelte el número de decimales a 0. Haga clic en DE ACUERDO Un par de veces para volver a la tabla dinámica ...

Como puede ver, los números se han formateado correctamente como importes en dólares.

Mientras estamos en el tema del formato, vamos a formatear la tabla dinámica completa. Hay algunas maneras de hacer esto. Vamos a usar uno simple ...

Haga clic en el Herramientas de tabla dinámica / Diseño lengüeta:

Luego suelta la flecha en la parte inferior derecha de la Estilos de tabla dinámica Lista para ver una vasta colección de estilos incorporados:

Elija cualquiera que apele y observe el resultado en su tabla dinámica:

Otras opciones

Podemos trabajar con fechas también. Ahora generalmente, hay muchas, muchas fechas en una lista de transacciones como la que empezamos. Pero Excel ofrece la opción de agrupar elementos de datos por día, semana, mes, año, etc. Veamos cómo se hace esto..

Primero, eliminemos la columna "Método de pago" de la Etiquetas de columna (simplemente arrástrelo de nuevo a la lista de campos), y reemplácelo con la columna "Fecha de reserva":

Como puede ver, esto hace que nuestra tabla dinámica sea instantáneamente inútil, dándonos una columna por cada fecha en que ocurrió una transacción: una tabla muy amplia!

Para solucionarlo, haga clic con el botón derecho en cualquier fecha y seleccione Grupo… desde el menú contextual:

Aparece el cuadro de agrupación. Seleccionamos Meses y haga clic en Aceptar:

Voila! UNA mucho tabla más útil:

(Por cierto, esta tabla es prácticamente idéntica a la que se muestra al principio de este artículo: el resumen de ventas original que se creó manualmente).

Otra cosa interesante a tener en cuenta es que puede tener más de un conjunto de encabezados de fila (o encabezados de columna):

... que se parece a esto ... .

Puede hacer algo similar con los encabezados de columna (o incluso filtros de informe).

Manteniendo las cosas simples de nuevo, veamos cómo trazar. promediado valores, en lugar de valores sumados.

Primero, haga clic en "Suma de Monto", y seleccione Configuración del campo de valor ... Desde el menú contextual que aparece:

En el Resumir el campo de valor por lista en el Configuración del campo de valor cuadro, seleccione Promedio:

Mientras estamos aquí, cambiemos el Nombre personalizado, de “Promedio de la cantidad” a algo un poco más conciso. Escribe algo como "Avg":

Hacer clic DE ACUERDO, y ver cómo se ve. Observe que todos los valores cambian de totales sumados a promedios, y el título de la tabla (celda superior izquierda) ha cambiado a "Promedio":

Si nos gusta, incluso podemos tener sumas, promedios y conteos (recuentos = cuántas ventas hubo) todos en la misma tabla dinámica!

Aquí están los pasos para poner algo así en su lugar (comenzando desde una tabla dinámica en blanco):

Arrastre "Vendedor" a la Etiquetas de columna
Arrastre el campo "Cantidad" hacia abajo Valores caja tres veces
Para el primer campo "Cantidad", cambie su nombre personalizado a "Total" y su formato de número a Contabilidad (0 decimales)
Para el segundo campo "Cantidad", cambie su nombre personalizado a "Promedio", su función a Promedio y es el formato de número para Contabilidad (0 decimales)
Para el tercer campo "Cantidad", cambie su nombre a "Cuenta" y su función a Contar
Arrastra lo creado automáticamente. campo desde Etiquetas de columna a Etiquetas de fila

Esto es lo que terminamos con:

Total, promedio y cuenta de la misma tabla dinámica.!

Conclusión

Hay muchas, muchas más características y opciones para las tablas dinámicas creadas por Microsoft Excel, demasiadas para enumerarlas en un artículo como este. Para cubrir completamente el potencial de las tablas dinámicas, se requeriría un libro pequeño (o un sitio web grande). Los lectores valientes y / o geek pueden explorar tablas dinámicas con mayor facilidad: simplemente haga clic derecho en casi todo y vea qué opciones están disponibles para usted. También están las dos pestañas de cinta: Herramientas / opciones de tabla dinámica y Diseño. No importa si comete un error, es fácil eliminar la tabla dinámica y comenzar de nuevo, una posibilidad que los antiguos usuarios de DOS de Lotus 1-2-3 nunca tuvieron.

Si está trabajando en Office 2007, le recomendamos que consulte nuestro artículo sobre cómo crear una tabla dinámica en Excel 2007..

Hemos incluido un libro de Excel que puede descargar para practicar sus habilidades de tablas dinámicas. Debería funcionar con todas las versiones de Excel desde 97 en adelante..

Descargue el libro de ejercicios de Excel de nuestra práctica