Tabla Dinámica | Diseña y modifica datos según lo requieras

Optimiza tus informes laborales y ahorra tiempo con Tablas Dinámicas

Excel ofrece una herramienta invaluable: las Tablas Dinámicas. Aunque a menudo son pasadas por alto debido a su aparente complejidad, en realidad son bastante accesibles.

En este tutorial, que lo he dividido en dos partes, desmitificaré su uso y te mostraré cómo aplicarlas en tu trabajo de manera efectiva, permitiéndote maximizar tu eficiencia.

El desempeño y la utilidad de las Tablas Dinámicas están estrechamente ligados a la organización y disposición de los datos de origen.

La forma en que estructuras estos datos influye directamente en la funcionalidad y capacidad analítica de las Tablas Dinámicas. Por lo tanto, es esencial prestar atención a la disposición y calidad de los datos subyacentes para aprovechar al máximo esta potente herramienta de análisis en Excel.

Origen de Datos

Voy a comenzar con información adicional que debes tener en cuenta antes de insertar una tabla dinámica. Esto ya lo he explicado de manera diferente en las Tablas Dinámicas | Nivel Básico, ahora te explico cómo trabajar un rango de datos:

Si vas a trabajar con un rango de datos, estos deben estar de forma ordenada y cumplir con estás condiciones:

1. 1. 1. Cada columna debe estar identificada, evita los encabezados o columnas vacías.
      2. Lo ideal es una sola fila de encabezado y sin celadas combinadas.
      3. Cada encabezado debe ser diferente, sin duplicados.
      4. Todos los datos deben estar en un mismo rango continuo, no debe tener ninguna fila vacía. Excel trata una columna o fila vacía como el final del rango de datos.
      5. Una celda vacía no es lo mismo que cero. Las celdas vacías Excel las obvia para muchos cálculos, por ejemplo, el promedio será diferente dependiendo de si hay un cero en la celda o si está vacía.
      6. Cuando se combina un grupo de celdas, no se tendrían en cuenta las celdas siguientes durante la búsqueda de cálculo.
      7. Cada dato debe ser coherente en cada columna.
      8. Excel reconoce cuando los valores son números, pero hay ocasiones que los datos son importados de sistemas internos de la empresa y están en formato de texto. con el fin de comprobar rápidamente si todos los datos son números, es más fácil. Sombrea las columnas con estos valores y observa la barra de estado allí aparece recuento y no verás la suma como en la columna de al lado.

Formato de Tabla

Excel ofrece un comando llamado Dar Formato como Tabla con atributos especiales que facilita la organización de los datos, al utilizar este comando verás un menú en la cinta de opciones que aparecerá al ubicarte sobre ella, son atributos y condiciones especiales que facilitan el tratamiento de datos.

Para más información en Dar formato de tablas.

Datos 1 | Inserta una Tabla Dinámica en la misma hoja de trabajo

Los conceptos básicos de las tablas dinámicas los explico en el vídeo con ejemplos simples comenzamos con un rango de datos con tres columnas que contienen 5 categorías que contienen 500 ítems. En este supuesto podemos calcular las ventas totales de cada categoría.

Para crear una tabla dinámica seleccionar cualquier celda del rango de datos o tabla y debajo de la pestaña insertar, haz clic en Tabla dinámica.

Excel intenta automáticamente averiguar dónde están los datos, luego está la opción para insertar una tabla dinámica en una nueva hoja de trabajo, siendo esta la más común en utilizar y es la selección predeterminada, voy a cambiar para hacerla en la misma hoja y es necesario indicarle en donde vas a insertarla, se confirma presionando Aceptar.

Después de agregar la tabla dinámica aparece en la cinta de opciones analizar y diseñar. La pestaña analizar en los últimos tres iconos son para elegir qué elementos de la tabla dinámica es visible. La primera: Lista de campos se activa y desactiva al hacerle clic sobre el botón.

Hay dos formas de crear y modificar tablas dinámicas. Te muestro ambas.

Diseño de Tabla Dinámica clásica (permite arrastrar campos directamente en la cuadrícula)

Voy a empezar con campos de arrastre en la vista clásica. en la pestaña de análisis selecciona opciones, de nuevo, en la pestaña Mostrar se marca diseño de tabla dinámica clásica y presionar Aceptar.

La tabla dinámica se ve diferente ahora, se puede arrastrar los campos desde la Lista de campos a la tabla dinámica directamente.

Recuerda que la tarea es calcular los valores de cada categoría así que primero arrastra el campo de categoría en el campo de fila, luego mueve el campo de ventas a los campos de valor.

Las descripciones del área de campo sólo son visibles si estos campos están vacíos. Como en el caso del campo de filtros que no se ha utilizado aún.

Sin hacer mucho trabajo ya hemos completado la tarea que se estableció al principio de este ejemplo, las sumas de las ventas por categoría son en la columna J , ahora le doy formato a los números, en la columna de la tabla hago doble clic izquierdo en la celda del encabezado de la 1ª columna, en la ventana de configuración del campo de valor, haz clic en la categoría de formato de número, no hay decimales y con el separador aceptar y luego aceptar.

Sugiero hacer un pequeño cambio en los datos de origen vamos a cambiar las ventas para el primer producto a 2345 después de volver a la tabla parece que nada ha cambiado porque en tablas dinámicas no se actualizan automáticamente, para actualizar la tabla dinámica haz clic derecho en ella y selecciona actualizar los nuevos valores se han calculado ahora es correcta.

Actualiza la Tabla Dinámica luego de cambiar algún dato en el Origen de datos

También dar un diseño a la tabla dinámica utilizando uno de los estilos preexistentes de la pestaña de Diseño, el nuevo estilo se previsualizará al mover el cursor del ratón sobre el icono de estilo. Ahora podemos ver la tabla final.

Un estilo de Diseño le dará una mejor presentación a la Tabla Dinámica, escoge el que más te guste.

Tabla Dinámica 1 | Resumen de valores con varios cálculos básicos

Usando la misma data, además de calcular las ventas totales de cada categoría, se requiere también, el promedio de las ventas para cada categoría, contar cuántos productos diferentes se vendieron en cada categoría y las ventas del mejor del producto.

Crearé otra tabla, pero esta vez la haré diferente. Escogeré la opción para insertar una tabla dinámica en una nueva hoja de trabajo, confirmo con Aceptar.

La dejo como se presenta por defecto.

La tarea es calcular los valores de cada categoría así que primero arrastra el campo de categoría en el campo de fila, dentro del panel de Lista de Campo en la parte de abajo están los cuadrantes. Luego mueve el campo de ventas a el campo de valor, aquí lo pongo tres veces, también arrastra el campo nombre del producto al campo de valor.

De la misma manera que el ejemplo anterior, en la columna de la tabla hago doble clic izquierdo en la celda del encabezado de la 1ª columna en la ventana de configuración del campo de valor, haz clic en la categoría de formato de número, no hay decimales, y con el separador Aceptar y Aceptar.

Todavía tenemos totales de ventas en columnas BC&D doble clic en el encabezado de la segunda columna que contiene datos esta vez además de cambiar el formato, también cambiaré el campo de valor en Resumir campo de valor en:  promedios y cambio el formato de los números de la misma manera que lo hice antes, como resultado, ahora tenemos el promedio calculado por categoría.

Doble clic en el encabezado de la celda de la cuarta columna para seleccionar Max y luego darle formato a los números de la manera habitual. Ahora tenemos una tabla dinámica que contiene todos los datos que estábamos buscando.

Los encabezados añadidos automáticamenbte, como el promedio de ventas, no suelen ser profesionales, así que sugiero cambiar la celda de activación antes y en la barra de fórmulas deshacerse de todo excepto las ventas, a continuación, presiona entrar y saldrá un mensaje de error, ha aparecido diciendo que el nombre del campo de la tabla dinámica ya existe se puede conseguir pasar de esto mediante la adición de un espacio al final de las ventas o haciendo otro cambio menor, por lo que el nombre se acepta y luego cambiar todo el nombre encabezados de columna

Si deseas cambiar el orden de los campos, por ejemplo, para poner la cantidad antes de la columna promedio, sólo tienes que agarrar el lado del encabezado de columna y arrastrarlo al lugar correcto, mientras arrastras aparece una línea mostrando dónde caería la columna si liberamos el botón del ratón al final de este ejemplo.

Ya sabes cómo cambiar el estilo de la tabla dinámica en la pestaña de Diseño. Ahora podemos ver la tabla final.

Para copiar una Tabla dinàmica a otra lugar es necesario sobrearla completa para que se respete su configuración de Tabla Dinámica.

Tabla Dinámica 2 | Muestra valores en diferentes modalidades de cálculo preestablecidos

Con el mismo ejemplo con el que hemos trabajado, también se puede calcular la cuota porcentual de las ventas.

La cuota porcentual se calcula dividiendo el valor de ventas de la categoría por el total de las ventas que nos dice lo importante que es la categoría

Inserta otro campo entre ventas y promedio modifica la configuración de campo en la pestaña mostrar valores como selecciona % del total general, puedes aprovechar y cambiar el nombre del encabezado en esta misma venta.

También te mostraré como modificar una columna, cambiaremos la columna de promedio para mostrarla como acumulado. Ubícate en la columna Promedio, en la cinta de opciones en Análisis al comienzo está también el botón de Configuración de campo. Lo activas y aparece la misma ventana que ya conoces.

Cambia la operación a suma y ahora en Mostrar valores como, cliquea en Totales en. Nos mostrará la suma acumulada de las ventas y cambia el nombre del encabezado desde la barra de fórmulas.

El próximo cálculo es encontrar la jerarquía del valor máximo de ventas por categoría, en el panel de control de Campos de tabla dinámica, en el cuadrante de valores cliquea en el botón Máx y aparece también la opción Configuración de campo, vamos a la pestaña Mostrar valores como y busca la opción Clasificar de mayor a menor.

El último ejemplo para de este tutorial para este comando es la opción de mostrar la diferencia de cada producto con el producto A. Creamos una nueva columna, arrastrando de nuevo el campo ventas hasta el final del campo de valor.

Esta vez ubicado en la columna con el botón de la derecha en el menú alternativo que aparece encontrarás la opción de Mostrar valores como y sombréala y aparece todas las opciones que ya conoces. Busca Diferencia de… en la nueva ventana escoge la categoría con la que se va a comparar, en este ejemplo es la categoría A.

Excel nos da la opción de escoger con que campo comparar.

Aprovecha y configura los valores a moneda en configuración de campo y formato de número y cambia el encabezado a <> A.

Para mover una columna de posición, ubícate sobre la columna que deseas mover, el cursor del ratón debe tener una forma diferente, verás cuatro fechas en cruz, en ese momento podrás pinchar y arrastras dentro del área de la tabla y verás una barra que indica en donde caerá la columna al soltar. También puedes hacer los cambios de posición desde la lista de campos, de la misma manera, pincha y arrastra.

Tabla Dinámica 3 | Filtra los datos de varias formas y condiciones

En este ejemplo voy a mostrarte la utilizaciòn de los filtros dentro de la Tabla Dinàmica. Con los datos de la primera Tabla dinàmica que hemos trabajado se muestran las ventas por categorìa, y se requiere que se muestre por producto.

Para eso, en la lista de campos arrastra el campo de Categorìa a Filtros, luego el campo de Nombre de productos se arrastra hasta Filas.

Existen dos formas de hacer filtros:

Primero, En la parte superior de la Tabla Dinàmica se puede ver una flecha que da la opcion de marcar y desmarcar elementos que incluye un campo, en este caso es de categoría. Estos elementos pueden ser uno o varios segun lo que se quiera filtrar.

Segundo, Dentro de la Tabla Dinàmica tambien verás una flecha de Filtro, este filtro separa los valores y filtros de etiqueta. La opciòn de ordenaciòn tambien tiene esta diferenciaciòn.

Vamos a probrar seleccionar los mejores 5 productos de la Categorìa A. En el filtro de valores cliquea en Diez mejores…

En la ventana Excel da la opciòn de modificar y colocar la cantidad que realmente queremos mostrar, en este caso pongo 5. Para borrar el filtro y volver a mostrar todos los elementos, està igualmente en el menù alternativo.

¡Descubre más en la Parte II y lleva tus habilidades al siguiente nivel!

En la próxima entrega sobre Tablas Dinámicas, profundizaremos en técnicas avanzadas que te ayudarán a dominar esta poderosa herramienta de análisis en Excel.

Además, tendrás la oportunidad de descargar el Libro de Excel utilizado en el tutorial, lo que te permitirá practicar y aplicar lo aprendido en tu propio trabajo.

¡No te pierdas esta oportunidad de mejorar tus habilidades y optimizar tu flujo de trabajo!

Accede a la Parte II en este enlace Tabla Dinámica | Agrupar campos de fechas o elementos