Cómo crear una tabla dinámica en Excel: un tutorial paso a paso

La tabla dinámica es una de las funciones más poderosas e intimidantes de Microsoft Excel. Potente porque puede ayudarlo a resumir y dar sentido a grandes conjuntos de datos. Intimidante porque no eres exactamente un experto en Excel, y las tablas dinámicas siempre han tenido la reputación de ser complicadas.

La buena noticia: aprender a crear una tabla dinámica en Excel es mucho más fácil de lo que te han hecho creer.

Pero antes de guiarlo a través del proceso de creación de una, demos un paso atrás y asegurémonos de que comprende exactamente qué es una tabla dinámica y por qué es posible que necesite usar una.

En otras palabras, las tablas dinámicas extraen el significado de ese revoltijo aparentemente interminable de números en su pantalla. Y más específicamente, le permite agrupar sus datos de diferentes maneras para que pueda sacar conclusiones útiles más fácilmente.

La parte de "pivote" de una tabla dinámica se deriva del hecho de que puede rotar (o pivotar) los datos en la tabla para verlos desde una perspectiva diferente. Para ser claros, no está agregando, restando o cambiando sus datos cuando hace un pivote. En cambio, simplemente está reorganizando los datos para que pueda revelar información útil a partir de ellos.

¿Para qué se utilizan las tablas dinámicas?

Si todavía se siente un poco confundido acerca de lo que realmente hacen las tablas dinámicas, no se preocupe. Esta es una de esas tecnologías que son mucho más fáciles de entender una vez que la has visto en acción.

El propósito de las tablas dinámicas es ofrecer formas fáciles de usar para resumir rápidamente grandes cantidades de datos. Se pueden usar para comprender, mostrar y analizar mejor los datos numéricos en detalle, y pueden ayudar a identificar y responder preguntas imprevistas que los rodean.

Aquí hay siete escenarios hipotéticos en los que una tabla dinámica podría ser una solución:

1. Comparar los totales de ventas de diferentes productos.

Supongamos que tiene una hoja de cálculo que contiene datos de ventas mensuales de tres productos diferentes (producto 1, producto 2 y producto 3) y desea averiguar cuál de los tres ha generado más dinero. Por supuesto, puede revisar la hoja de trabajo y agregar manualmente la cifra de ventas correspondiente a un total acumulado cada vez que aparece el producto 1. Luego podría hacer lo mismo para el producto 2 y el producto 3 hasta que tenga los totales para todos ellos. Pan comido, ¿verdad?

Ahora, imagine que su hoja de cálculo de ventas mensuales tiene miles y miles de filas. Clasificarlos manualmente podría llevar toda una vida. Con una tabla dinámica, puede agregar automáticamente todas las cifras de ventas del producto 1, producto 2 y producto 3, y calcular sus sumas respectivas, en menos de un minuto.

2. Mostrar las ventas de productos como porcentajes de las ventas totales.

Las tablas dinámicas muestran naturalmente los totales de cada fila o columna cuando las crea. Pero esa no es la única figura que puede producir automáticamente.

Supongamos que ingresó números de ventas trimestrales para tres productos separados en una hoja de Excel y convirtió estos datos en una tabla dinámica. La tabla le daría automáticamente tres totales en la parte inferior de cada columna, después de haber sumado las ventas trimestrales de cada producto. Pero, ¿qué pasaría si quisiera encontrar el porcentaje que estas ventas de productos contribuyeron a todas las ventas de la empresa, en lugar de solo los totales de ventas de esos productos?

Con una tabla dinámica, puede configurar cada columna para obtener el porcentaje de la columna de los totales de las tres columnas, en lugar de solo el total de la columna. Si las ventas de tres productos totalizaron $ 200,000 en ventas, por ejemplo, y el primer producto ganó $ 45,000, puede editar una tabla dinámica para decir que este producto contribuyó con el 22.5% de todas las ventas de la compañía.

Para mostrar las ventas de productos como porcentajes de las ventas totales en una tabla dinámica, simplemente haga clic con el botón derecho en la celda que contiene un total de ventas y seleccione Mostrar valores como > % del total general .

3. Combinar datos duplicados.

En este escenario, acaba de completar el rediseño de un blog y tuvo que actualizar un montón de URL. Desafortunadamente, el software de informes de su blog no lo manejó muy bien y terminó dividiendo las métricas de "vista" para publicaciones individuales entre dos URL diferentes. Entonces, en su hoja de cálculo, tiene dos instancias separadas de cada publicación de blog individual. Para obtener datos precisos, debe combinar los totales de vista para cada uno de estos duplicados.

Ahí es donde entra en juego la tabla dinámica. En lugar de tener que buscar y combinar manualmente todas las métricas de los duplicados, puede resumir sus datos (a través de una tabla dinámica) por título de publicación de blog y listo: las métricas de vista de esas publicaciones duplicadas se agregarán automáticamente.

4. Obtener un recuento de empleados para departamentos separados.

Las tablas dinámicas son útiles para calcular automáticamente cosas que no puede encontrar fácilmente en una tabla básica de Excel. Una de esas cosas es contar filas que tienen algo en común.

Si tiene una lista de empleados en una hoja de Excel, por ejemplo, y junto a los nombres de los empleados están los respectivos departamentos a los que pertenecen, puede crear una tabla dinámica a partir de estos datos que muestre el nombre de cada departamento y la cantidad de empleados. que pertenecen a esos departamentos. La tabla dinámica elimina efectivamente su tarea de ordenar la hoja de Excel por nombre de departamento y contar cada fila manualmente.

5. Agregar valores predeterminados a celdas vacías.

No todos los conjuntos de datos que ingrese en Excel llenarán todas las celdas. Si está esperando que ingresen nuevos datos antes de ingresarlos en Excel, es posible que tenga muchas celdas vacías que parezcan confusas o que necesiten más explicaciones al mostrar estos datos a su gerente. Ahí es donde entran las tablas dinámicas.

Puede personalizar fácilmente una tabla dinámica para llenar las celdas vacías con un valor predeterminado, como $0 o TBD (para "por determinar"). Para tablas de datos grandes, poder etiquetar estas celdas rápidamente es una característica útil cuando muchas personas revisan la misma hoja.

Para formatear automáticamente las celdas vacías de su tabla dinámica, haga clic derecho en su tabla y haga clic en Opciones de tabla dinámica. En la ventana que aparece, marque la casilla denominada Celdas vacías como e ingrese lo que desea que se muestre cuando una celda no tiene otro valor.

Cómo crear una tabla dinámica

1. Ingrese sus datos en un rango de filas y columnas.
2. Ordene sus datos por un atributo específico.
3. Resalta tus celdas para crear tu tabla dinámica.
4. Arrastre y suelte un campo en el área "Etiquetas de fila".
5. Arrastre y suelte un campo en el área "Valores".
6. Afina tus cálculos.

Ahora que tiene una mejor idea de para qué se pueden usar las tablas dinámicas, entremos en el meollo de la cuestión de cómo crear una.

Paso 1. Ingrese sus datos en un rango de filas y columnas.

Cada tabla dinámica en Excel comienza con una tabla básica de Excel, donde se alojan todos sus datos. Para crear esta tabla, simplemente ingrese sus valores en un conjunto específico de filas y columnas. Use la fila superior o la columna superior para categorizar sus valores por lo que representan.

Por ejemplo, para crear una tabla de Excel de datos de rendimiento de publicaciones de blog, puede tener una columna que enumere cada "Páginas principales", una columna que enumere los "Clics" de cada URL, una columna que enumere las "Impresiones" de cada publicación, etc. (Usaremos ese ejemplo en los pasos que siguen).

Paso 2. Ordene sus datos por un atributo específico.

Cuando tenga todos los datos que desea ingresar en su hoja de Excel, querrá ordenar estos datos de alguna manera para que sea más fácil de administrar una vez que los convierta en una tabla dinámica.

Para ordenar sus datos, haga clic en la pestaña Datos en la barra de navegación superior y seleccione el ícono Ordenar debajo de ella. En la ventana que aparece, puede optar por ordenar sus datos por cualquier columna que desee y en cualquier orden.

Por ejemplo, para ordenar su hoja de Excel por "Vistas hasta la fecha", seleccione el título de esta columna en Columna y luego seleccione si desea ordenar sus publicaciones de menor a mayor o de mayor a menor.

Seleccione Aceptar en la parte inferior derecha de la ventana Ordenar y reordenará con éxito cada fila de su hoja de Excel por la cantidad de visitas que recibió cada publicación de blog.

Paso 3. Resalta tus celdas para crear tu tabla dinámica.

Una vez que haya ingresado los datos en su hoja de cálculo de Excel y los haya ordenado a su gusto, resalte las celdas que le gustaría resumir en una tabla dinámica. Haga clic en Insertar en la barra de navegación superior y seleccione el icono de tabla dinámica . También puede hacer clic en cualquier parte de su hoja de trabajo, seleccionar "Tabla dinámica" e ingresar manualmente el rango de celdas que desea incluir en la tabla dinámica.

Esto abrirá un cuadro de opciones donde, además de configurar su rango de celdas, puede seleccionar si iniciar o no esta tabla dinámica en una nueva hoja de trabajo o mantenerla en la hoja de trabajo existente. Si abre una nueva hoja, puede navegar hacia ella y alejarse de ella en la parte inferior de su libro de Excel. Una vez que haya elegido, haga clic en Aceptar.

Como alternativa , puede resaltar sus celdas, seleccionar Tablas dinámicas recomendadas a la derecha del icono de tabla dinámica y abrir una tabla dinámica con sugerencias preestablecidas sobre cómo organizar cada fila y columna.

Nota: si está utilizando una versión anterior de Excel, "Tablas dinámicas" puede estar debajo de Tablas o Datos en la navegación superior, en lugar de "Insertar". En Hojas de cálculo de Google, puede crear tablas dinámicas desde el menú desplegable Datos en la parte superior de navegación.

Paso 4. Arrastre y suelte un campo en el área "Etiquetas de fila".

Una vez que haya completado el Paso 3, Excel creará una tabla dinámica en blanco para usted. Su próximo paso es arrastrar y soltar un campo, etiquetado de acuerdo con los nombres de las columnas en su hoja de cálculo, en el área de etiquetas de fila . Esto determinará qué identificador único (título de la publicación del blog, nombre del producto, etc.) la tabla dinámica organizará sus datos.

Por ejemplo, supongamos que desea organizar una gran cantidad de datos de blogs por título de publicación. Para hacerlo, simplemente haga clic y arrastre el campo "Páginas principales" al área "Etiquetas de fila".

Nota: Su tabla dinámica puede verse diferente según la versión de Excel con la que esté trabajando. Sin embargo, los principios generales siguen siendo los mismos.

Paso 5. Arrastre y suelte un campo en el área "Valores".

Una vez que haya establecido por qué va a organizar sus datos, su siguiente paso es agregar algunos valores arrastrando un campo al área Valores .

Siguiendo con el ejemplo de datos de blogs, supongamos que desea resumir las vistas de publicaciones de blog por título. Para hacer esto, simplemente arrastre el campo "Vistas" al área de Valores.

Paso 6. Afina tus cálculos.

La suma de un valor en particular se calculará de manera predeterminada, pero puede cambiarla fácilmente a algo como promedio, máximo o mínimo, según lo que desee calcular.

En una Mac, puede hacer esto haciendo clic en la pequeña i junto a un valor en el área "Valores", seleccionando la opción que desea y haciendo clic en "Aceptar". Una vez que haya hecho su selección, su tabla dinámica se actualizará en consecuencia.

Si está utilizando una PC, deberá hacer clic en el pequeño triángulo invertido junto a su valor y seleccionar Configuración de campo de valor para acceder al menú.

Cuando haya categorizado sus datos a su gusto, guarde su trabajo y utilícelo como desee.

Profundizando más con las tablas dinámicas

Ahora ha aprendido los conceptos básicos de la creación de tablas dinámicas en Excel. Con este entendimiento, puede descubrir lo que necesita de su tabla dinámica y encontrar las soluciones que está buscando.

Por ejemplo, puede notar que los datos en su tabla dinámica no están ordenados de la manera que le gustaría. Si este es el caso, la función Ordenar de Excel puede ayudarlo. Alternativamente, es posible que deba incorporar datos de otra fuente en sus informes, en cuyo caso la función BUSCARV podría ser útil.

Nota del editor: esta publicación se publicó originalmente en diciembre de 2018 y se actualizó para que sea más completa.