Contenido
Tablas dinamicas es una de las herramientas más poderosas en Excel. Le permiten analizar y resumir varios resúmenes de grandes cantidades de datos con solo unos pocos clics del mouse. En este artículo, nos familiarizaremos con las tablas dinámicas, comprenderemos qué son, aprenderemos a crearlas y personalizarlas.
Este artículo se escribió con Excel 2010. El concepto de tablas dinámicas no ha cambiado mucho a lo largo de los años, pero la forma de crearlas es ligeramente diferente en cada nueva versión de Excel. Si tiene una versión de Excel que no es 2010, prepárese para que las capturas de pantalla de este artículo difieran de lo que ve en su pantalla.
Un poco de historia
En los primeros días del software de hojas de cálculo, la bola de reglas Lotus 1-2-3. Su dominio fue tan completo que los esfuerzos de Microsoft por desarrollar su propio software (Excel) como alternativa a Lotus parecían una pérdida de tiempo. ¡Ahora avance rápido hasta 2010! Excel domina las hojas de cálculo más de lo que lo ha hecho el código de Lotus en su historia, y la cantidad de personas que aún usan Lotus es cercana a cero. ¿Cómo pudo pasar esto? ¿Cuál fue la razón de un giro tan dramático de los acontecimientos?
Los analistas identifican dos factores principales:
- Primero, Lotus decidió que esta novedosa plataforma GUI llamada Windows era solo una moda pasajera que no duraría mucho. Se negaron a crear una versión para Windows de Lotus 1-2-3 (pero solo durante unos años), prediciendo que la versión DOS de su software sería todo lo que los consumidores necesitarían. Microsoft naturalmente desarrolló Excel específicamente para Windows.
- En segundo lugar, Microsoft introdujo una herramienta en Excel denominada tablas dinámicas que no estaba disponible en Lotus 1-2-3. Las tablas dinámicas, exclusivas de Excel, demostraron ser tan abrumadoramente útiles que la gente tendía a quedarse con el nuevo paquete de software de Excel en lugar de continuar con Lotus 1-2-3, que no las tenía.
Las tablas dinámicas, además de subestimar el éxito de Windows en general, jugaron la marcha de la muerte de Lotus 1-2-3 y marcaron el comienzo del éxito de Microsoft Excel.
¿Qué son las tablas dinámicas?
Entonces, ¿cuál es la mejor manera de caracterizar lo que son las tablas dinámicas?
En términos simples, las tablas dinámicas son resúmenes de algunos datos, creados para facilitar el análisis de estos datos. A diferencia de los totales creados manualmente, las tablas dinámicas de Excel son interactivas. Una vez creadas, puedes modificarlas fácilmente si no dan la imagen que esperabas. Con solo un par de clics del mouse, los totales se pueden voltear para que los encabezados de columna se conviertan en encabezados de fila y viceversa. Puedes hacer muchas cosas con las tablas dinámicas. En lugar de tratar de describir con palabras todas las características de las tablas dinámicas, es más fácil demostrarlo en la práctica...
Los datos que analiza con tablas dinámicas no pueden ser aleatorios. Deben ser datos sin procesar, como una lista de algún tipo. Por ejemplo, podría ser una lista de las ventas realizadas por la empresa en los últimos seis meses.
Observa los datos que se muestran en la siguiente figura:
Tenga en cuenta que estos no son datos sin procesar, ya que ya se han resumido. En la celda B3 vemos $30000, que es probablemente el resultado total que obtuvo James Cook en enero. ¿Dónde están los datos originales entonces? ¿De dónde salió la cifra de $30000? ¿Dónde está la lista original de ventas de la que se derivó este total mensual? Está claro que alguien ha hecho un gran trabajo al organizar y clasificar todos los datos de ventas de los últimos seis meses y convertirlos en la tabla de totales que vemos. ¿Cuánto tiempo crees que tomó? ¿Hora? ¿Diez?
El hecho es que la tabla de arriba no es una tabla dinámica. Fue hecho a mano a partir de datos sin procesar almacenados en otros lugares y tomó al menos un par de horas procesarlo. Tal tabla de resumen se puede crear usando tablas dinámicas en solo unos segundos. Averigüemos cómo…
Si volvemos a la lista de ventas original, se vería así:
Puede que te sorprenda que a partir de esta lista de transacciones con la ayuda de tablas dinámicas y en solo unos segundos, podemos crear un informe de ventas mensual en Excel, que analizamos anteriormente. ¡Sí, podemos hacer eso y más!
¿Cómo crear una tabla dinámica?
Primero, asegúrese de tener algunos datos de origen en una hoja de Excel. La lista de transacciones financieras es la más típica que se presenta. De hecho, podría ser una lista de cualquier cosa: datos de contacto de los empleados, una colección de CD o los datos de consumo de combustible de su empresa.
Entonces, iniciamos Excel... y cargamos esa lista...
Después de haber abierto esta lista en Excel, podemos comenzar a crear una tabla dinámica.
Seleccione cualquier celda de esta lista:
Luego en la pestaña Inserción (Insertar) comando de selección Tabla dinámica (Tabla dinámica):
Un cuadro de diálogo aparecerá Crear tabla dinámica (Creando una tabla dinámica) con dos preguntas para usted:
- ¿Qué datos usar para crear una nueva tabla dinámica?
- ¿Dónde poner la mesa dinámica?
Dado que en el paso anterior ya hemos seleccionado una de las celdas de la lista, la lista completa se seleccionará automáticamente para crear una tabla dinámica. Tenga en cuenta que podemos seleccionar un rango diferente, una tabla diferente e incluso alguna fuente de datos externa, como una tabla de base de datos de Access o MS-SQL. Además, debemos elegir dónde colocar la nueva tabla dinámica: en una hoja nueva o en una de las existentes. En este ejemplo, elegiremos la opción – Nueva hoja de trabajo (a una hoja nueva):
Excel creará una nueva hoja y colocará una tabla dinámica vacía en ella:
Tan pronto como hagamos clic en cualquier celda de la tabla dinámica, aparecerá otro cuadro de diálogo: Lista de campos de tabla dinámica (Campos de tabla dinámica).
La lista de campos en la parte superior del cuadro de diálogo es una lista de todos los títulos de la lista original. Las cuatro áreas vacías en la parte inferior de la pantalla le permiten decirle a la tabla dinámica cómo desea resumir los datos. Mientras estas áreas estén vacías, tampoco hay nada en la tabla. Todo lo que tenemos que hacer es arrastrar los encabezados desde el área superior a las áreas vacías de abajo. Al mismo tiempo, se genera automáticamente una tabla dinámica, de acuerdo con nuestras instrucciones. Si nos equivocamos, podemos eliminar los encabezados de la zona inferior o arrastrar otros para reemplazarlos.
Área Valores (Significados) es probablemente el más importante de los cuatro. El encabezado que se coloca en esta área determina qué datos se resumirán (suma, promedio, máximo, mínimo, etc.). Estos son casi siempre valores numéricos. Un excelente candidato para un lugar en esta área son los datos bajo el encabezado Cantidad (Costo) de nuestra mesa original. Arrastre este título al área Valores (Valores):
Tenga en cuenta que el título Cantidad ahora está marcado con una marca de verificación, y en el área Valores (Valores) ha aparecido una entrada Suma de la cantidad (campo Importe Importe), indicando que la columna Cantidad resumió.
Si miramos la tabla dinámica en sí, veremos la suma de todos los valores de la columna Cantidad mesa original.
Entonces, ¡nuestra primera tabla dinámica está creada! Conveniente, pero no particularmente impresionante. Probablemente queramos obtener más información sobre nuestros datos de la que tenemos actualmente.
Volvamos a los datos originales e intentemos identificar una o más columnas que se puedan usar para dividir esta suma. Por ejemplo, podemos formar nuestra tabla dinámica de tal manera que la cantidad total de ventas se calcule para cada vendedor individualmente. Aquellos. Se agregarán filas a nuestra tabla dinámica con el nombre de cada vendedor de la empresa y su monto total de ventas. Para lograr este resultado, simplemente arrastre el título vendedor (representante de ventas) a la región Etiquetas de fila (Instrumentos de cuerda):
¡Se vuelve más interesante! Nuestra tabla dinámica está empezando a tomar forma...
¿Ves los beneficios? Con un par de clics, creamos una tabla que hubiera llevado mucho tiempo crear manualmente.
¿Qué más podemos hacer? Bueno, en cierto sentido, nuestra tabla dinámica está lista. Hemos creado un resumen útil de los datos originales. ¡Información importante ya recibida! En el resto de este artículo, veremos algunas formas de crear tablas dinámicas más complejas y aprenderemos a personalizarlas.
Configuración de tabla dinámica
Primero, podemos crear una tabla dinámica bidimensional. Hagamos esto usando el encabezado de la columna. Método de Pago (Método de pago). Simplemente arrastre el título Método de Pago a la zona Etiquetas de columna (Columnas):
Obtenemos el resultado:
¡Se ve muy bien!
Ahora hagamos una tabla tridimensional. ¿Cómo sería una mesa así? Vamos a ver…
Arrastre encabezado Contenido del Paquete (Complejo) a la zona filtros de informes (Filtros):
Fíjate dónde está...
Esto nos da la oportunidad de filtrar el informe sobre la base de "Qué complejo de vacaciones se pagó". Por ejemplo, podemos ver un desglose por vendedores y por métodos de pago para todos los complejos, o en un par de clics del mouse, cambiar la vista de la tabla dinámica y mostrar el mismo desglose solo para aquellos que ordenaron el complejo. amantes del sol.
Entonces, si entiende esto correctamente, entonces nuestra tabla dinámica puede llamarse tridimensional. Sigamos configurando...
Si de repente resulta que en la tabla dinámica solo se debe mostrar el pago con cheque y tarjeta de crédito (es decir, el pago sin efectivo), entonces podemos desactivar la visualización del título efectivo (Dinero). Para ello, junto a Etiquetas de columna haga clic en la flecha hacia abajo y desmarque la casilla en el menú desplegable efectivo:
Veamos cómo se ve nuestra tabla dinámica ahora. Como puede ver, la columna efectivo desapareció de ella.
Dar formato a tablas dinámicas en Excel
Las tablas dinámicas son obviamente una herramienta muy poderosa, pero hasta ahora los resultados parecen un poco simples y aburridos. Por ejemplo, los números que sumamos no parecen cantidades en dólares, son solo números. Arreglemos esto.
Es tentador hacer lo que está acostumbrado en tal situación y simplemente seleccionar toda la tabla (o toda la hoja) y usar los botones de formato de número estándar en la barra de herramientas para establecer el formato deseado. El problema con este enfoque es que si alguna vez cambia la estructura de la tabla dinámica en el futuro (lo que sucede con un 99 % de probabilidad), se perderá el formato. Lo que necesitamos es una forma de hacerlo (casi) permanente.
Primero, busquemos la entrada. Suma de la cantidad in Valores (Valores) y haga clic en él. En el menú que aparece, seleccione el elemento Configuración del campo de valor (Opciones de campo de valor):
Un cuadro de diálogo aparecerá Configuración del campo de valor (Opciones de campo de valor).
Нажмите кнопку Formato de los números (Formato de número), se abrirá un cuadro de diálogo. Formato de celdas (formato de celda):
De la lista Categoría (Formatos de número) seleccionar Contabilidad (Financiero) y establezca el número de decimales en cero. Ahora presiona varias veces OKpara volver a nuestra tabla dinámica.
Como puede ver, los números están formateados como cantidades en dólares.
Mientras estamos en eso con el formato, configuremos el formato para toda la tabla dinámica. Hay varias maneras de hacer esto. Nosotros usamos el que es más simple…
Haga clic en el Herramientas de tabla dinámica: Diseño (Trabajando con tablas dinámicas: Constructor):
A continuación, expanda el menú haciendo clic en la flecha en la esquina inferior derecha de la sección Estilos de tabla dinámica (Estilos de tabla dinámica) para ver la extensa colección de estilos en línea:
Elija cualquier estilo adecuado y mire el resultado en su tabla dinámica:
Otras configuraciones de tabla dinámica en Excel
A veces es necesario filtrar los datos por fechas. Por ejemplo, en nuestra lista de operaciones hay muchas, muchas fechas. Excel proporciona una herramienta para agrupar datos por día, mes, año, etc. Veamos cómo se hace.
Primero elimine la entrada. Método de Pago de la región Etiquetas de columna (Columnas). Para hacer esto, arrástrelo de regreso a la lista de títulos y, en su lugar, mueva el título Fecha reservada (fecha de reserva):
Como puede ver, esto inutilizó temporalmente nuestra tabla dinámica. Excel creó una columna separada para cada fecha en la que se realizó una transacción. Como resultado, ¡obtuvimos una mesa muy amplia!
Para solucionar esto, haga clic con el botón derecho en cualquier fecha y seleccione del menú contextual (Grupo):
Aparecerá el cuadro de diálogo de agrupación. Nosotros elegimos Meses (Meses) y haga clic en OK:
¡Voila! Esta tabla es mucho más útil:
Por cierto, esta tabla es casi idéntica a la que se muestra al principio del artículo, donde los totales de ventas se compilaron manualmente.
¡Hay otro punto muy importante que debes saber! Puede crear no uno, sino varios niveles de encabezados de fila (o columna):
… y se verá así …
Lo mismo se puede hacer con los encabezados de las columnas (o incluso con los filtros).
Volvamos a la forma original de la tabla y veamos cómo mostrar promedios en lugar de sumas.
Para comenzar, haga clic en Suma de la cantidad y del menú que aparece selecciona Configuración del campo de valor (Opciones de campo de valor):
La lista Resumir campo de valor por (Operación) en el cuadro de diálogo Configuración del campo de valor (Opciones de campo de valor) seleccionar Normal (Promedio):
Al mismo tiempo, mientras estemos aquí, cambiemos Nombre personalizado (Nombre personalizado) con Cantidad promedio (Cantidad del campo Cantidad) a algo más corto. Introduzca en este campo algo como Promedio:
Prensa OK y mira lo que pasa. Tenga en cuenta que todos los valores han cambiado de totales a promedios, y el encabezado de la tabla (en la celda superior izquierda) ha cambiado a Promedio:
Si lo desea, puede obtener inmediatamente la cantidad, el promedio y el número (ventas) colocados en una tabla dinámica.
Aquí hay una guía paso a paso sobre cómo hacer esto, comenzando con una tabla dinámica vacía:
- Arrastre encabezado vendedor (representante de ventas) a la región Etiquetas de columna (Columnas).
- Arrastra el título tres veces Cantidad (Costo) al área Valores (Valores).
- Para el primer campo Cantidad cambiar el titulo a Total (Cantidad), y el formato de número en este campo es Contabilidad (Financiero). El número de lugares decimales es cero.
- segundo campo Cantidad nombre Promedioe, establezca la operación para ello Normal (Promedio) y el formato de número en este campo también cambia a Contabilidad (Financiero) con cero decimales.
- Para el tercer campo Cantidad establecer un título Contar y una operación para él – Contar (Cantidad)
- En Etiquetas de columna (Columnas) campo creado automáticamente Valores Σ (Σ Valores) – arrástrelo al área Etiquetas de fila (Líneas)
Esto es con lo que terminaremos:
Importe total, valor medio y número de ventas: ¡todo en una tabla dinámica!
Conclusión
Las tablas dinámicas en Microsoft Excel contienen muchas características y configuraciones. En un artículo tan pequeño, no están ni cerca de cubrirlos a todos. Se necesitaría un libro pequeño o un sitio web grande para describir completamente todas las posibilidades de las tablas dinámicas. Los lectores audaces e inquisitivos pueden continuar su exploración de las tablas dinámicas. Para hacer esto, simplemente haga clic derecho en casi cualquier elemento de la tabla dinámica y vea qué funciones y configuraciones se abren. En la cinta de opciones encontrará dos pestañas: Herramientas de tabla dinámica: Opciones (análisis) y Diseño (Constructor). No tenga miedo de cometer un error, siempre puede eliminar la tabla dinámica y comenzar de nuevo. Tiene una oportunidad que los usuarios antiguos de DOS y Lotus 1-2-3 nunca tuvieron.