Cómo crear una tabla dinámica en Excel para dividir y dividir sus datos
Microsoft Excel es posiblemente la mejor aplicación de hojas de cálculo de Redmond, y hay una buena razón por la que muchos analizadores de números lo utilizan para todas sus necesidades de procesamiento de números. Si bien usar Microsoft Excel está bien para hojas de cálculo simples para realizar un seguimiento de gastos o crear calendarios, resulta útil cuando necesita dividir y luego presentar datos complejos. Aquí le mostramos cómo crear una tabla dinámica en Excel para aprovechar una de las herramientas más poderosas de la aplicación.
Antes de comenzar, ¿para qué sirven exactamente las tablas dinámicas? En pocas palabras, las tablas dinámicas le permiten observar los mismos datos de diferentes maneras y desde diferentes ángulos, para facilitar la realización de análisis en profundidad y detectar tendencias importantes. Cuando evalúa los resultados de ventas, por ejemplo, es posible que desee observar a una persona individual, un producto específico o un período de tiempo específico. Con una tabla dinámica, puede crear un conjunto de información y luego cambiar fácilmente su enfoque de una cosa a otra, un análisis que sería tedioso realizar manualmente.
Las tablas dinámicas ahora son funcionales en todas las versiones actuales de Excel, ya sea que pague por el software o use Microsoft Office/365 de forma gratuita .
Paso 1: Prepare sus datos
Quizás el paso más importante al utilizar tablas dinámicas de Excel sea organizar cuidadosamente los datos. La forma más sencilla de hacerlo es utilizar tablas de Excel , que le permiten agregar filas que se incluirán en su tabla dinámica cada vez que presione actualizar. Pero, como mínimo, desea que sus datos estén en forma tabular con encabezados de columna informativos y con datos consistentes dentro de las columnas. Considere agregar una nueva columna de datos si están disponibles y mejorará la tabla dinámica resultante.
Por ejemplo, digamos que desea analizar la información de ventas de su empresa. Tiene seis vendedores que venden tres productos en varias regiones. Sus productos se adaptan a cada cliente y, por lo tanto, los precios varían. Aquí hay una tabla de muestra con información ficticia para darle una idea de cómo se pueden organizar los datos teniendo en cuenta una tabla dinámica.
La tabla se creó simplemente ingresando primero los datos, luego seleccionando todo el rango y luego yendo a Insertar > Tabla. Nuevamente, no es necesario que realice este paso, pero se recomienda si desea agregar más filas de datos más adelante y facilitar la actualización de su tabla dinámica.
Paso 2: prueba una recomendación
Excel está lleno de trucos ingeniosos para facilitar el trabajo con datos y, siempre que sea posible, intentará adivinar lo que desea lograr y luego ejecutará automáticamente algunos pasos. Esta naturaleza útil se demuestra aquí con la herramienta Tablas dinámicas recomendadas, que analiza sus datos y ofrece algunas opciones lógicas sobre cómo analizar y presentar cosas.
Para utilizar una tabla dinámica recomendada, simplemente vaya a Insertar > Tablas dinámicas recomendadas. Excel le presentará algunas opciones para que las considere. En nuestro ejemplo, Excel ofrece crear 10 tablas dinámicas diferentes que analizan varios ángulos diferentes de nuestros datos de ventas. Tenga en cuenta que la forma en que etiquete sus columnas es importante; Excel lee estos encabezados y ofrece las recomendaciones que tienen más sentido. Si desea consultar los precios de venta, por ejemplo, no utilice el término "costo", porque Excel basará su recomendación en consecuencia.
Una recomendación es "Suma de las ventas totales por cliente". Si elegimos esa opción, entonces Excel procederá a crear la tabla dinámica.
Observe que la tabla dinámica muestra solo los datos que son pertinentes para nuestro análisis actual. En el lado derecho, encontrará los criterios que utilizó Excel para crearlo en el cuadro de diálogo Campos de tabla dinámica. Cubriremos lo que significa cada uno de estos campos en la siguiente sección sobre personalización.
Paso 3: personaliza tu tabla dinámica
Cada uno de los elementos de este cuadro de diálogo es importante para determinar cómo funcionará su tabla dinámica. Haga clic en el engranaje de configuración para modificar la apariencia de este cuadro de diálogo y adaptarlo a lo que funcione mejor para usted.
Selección de campos
Aquí, usted elige qué columnas incluir en su tabla dinámica. La forma en que esos datos se completarán en la tabla dinámica está determinada por el tipo de datos que representan: Excel determinará por usted si desea agregar una columna a su tabla dinámica o agregar los datos del campo dentro de la tabla. Por ejemplo, si selecciona "Artículos", Excel supone que desea agregar la cantidad de artículos para cada cliente.
Por otro lado, si selecciona "Fecha", Excel coloca los datos en la tabla y organiza las ventas según el momento en que ocurrieron.
Mientras trabaja en sus propias tablas dinámicas, puede experimentar para ver cómo la adición y eliminación de campos afecta los datos que se muestran. Descubrirá que Excel hace un gran trabajo al realizar selecciones que tienen sentido, pero también puede cambiar las cosas si las opciones de Excel no tienen sentido.
Filtros
Arrastre elementos de la lista de selección de campos a la sección "Filtros" si desea limitar los datos que se muestran. Por ejemplo, si arrastra "Cliente" a la sección "Filtros", puede mostrar fácilmente solo los datos de uno o una selección de clientes.
columnas
Al arrastrar campos a la sección "Columnas", puede ampliar la forma en que se informan sus datos. Nuevamente, cuando arrastra un campo a esta sección, Excel intentará determinar cómo desea que se presenten los datos. Por ejemplo, si arrastra "Fecha" a la sección "Columnas", Excel mostrará las ventas resumidas para el período de tiempo más lógico, que en este caso es por mes. Esto sería útil si su principal preocupación fuera cuánto se vende mensualmente teniendo en cuenta los patrones de compra de los clientes.
Filas
De manera similar, puede arrastrar campos a la sección "Filas" para incrustar diferentes datos en las filas de la tabla dinámica. Nuevamente, si arrastramos "Fecha" a la sección "Filas", Excel desglosará las ventas por cliente por mes, pero los datos se resumirán por cliente y no por mes como en el ejemplo anterior. En este caso, lo que más le preocupa es cuánto vendió a cada cliente, pero también desea detectar tendencias basadas en el tiempo.
Valores
Finalmente, la sección "Valores" determina cómo analiza sus datos. En todos nuestros ejemplos hasta ahora, hemos analizado las ventas totales. Si hace clic en la tecla de flecha hacia abajo en esta sección, puede configurar los ajustes del campo de valor para ver un cálculo numérico diferente.
Por ejemplo, digamos que desea ver los promedios de ventas en lugar de las ventas totales. Simplemente seleccionaría "Promedio" en el cuadro de diálogo de configuración del campo de valor. También puede configurar el formato del número para que los resultados tengan más sentido.
Ahora, en lugar de considerar las ventas totales por cliente y luego calcular un total general, analizamos las ventas promedio por empresa y luego las ventas promedio en toda la empresa. Esto sería útil para evaluar qué clientes están por encima o por debajo del promedio en ventas, por ejemplo, y por lo tanto cuáles merecen mayor (o menor) atención. En nuestro ejemplo, quizás Acme Engineering y Jones Roofing no merezcan tanta atención de ventas como los demás.
Claramente, las tablas dinámicas ofrecen una gran cantidad de opciones para facilitar la división de datos. El truco para utilizar tablas dinámicas de forma eficaz es decidir exactamente qué quiere ver antes de empezar a aplicar opciones. También desea asegurarse de que sus datos correspondan con la forma en que diseñó su tabla y cómo nombró sus encabezados. Cuanto más cuidadoso sea al configurar las cosas, más efectivas serán sus tablas dinámicas.
Paso 4: crea tus propias tablas dinámicas desde cero
Una vez que haya jugado con la función de tabla dinámica y haya comprendido un poco cómo las diversas opciones afectan sus datos, podrá comenzar a crear una tabla dinámica desde cero. El proceso es similar a usar una recomendación, solo que va a Insertar > Tabla dinámica y luego selecciona manualmente sus datos como primer paso.
En nuestro caso, eso significa seleccionar Tabla1, pero también podemos seleccionar un rango de datos o extraerlos de una fuente de datos externa. También podemos decidir si queremos crear una nueva hoja de trabajo o colocar la tabla dinámica junto a nuestros datos en una ubicación determinada de la hoja de trabajo existente.
Una vez que hayamos hecho nuestra selección, se nos presentará una tabla dinámica en blanco y nuestro cuadro de diálogo Campos de tabla dinámica.
Crear nuestra propia tabla dinámica es entonces simplemente una cuestión de seleccionar campos y determinar cómo queremos que se calculen y muestren los datos. Digamos que queremos ver el desempeño de los vendedores por mes, con un total general de ventas para el año. Seleccionaríamos los campos "Vendedor", "Fecha" y "Ventas totales", arrastraríamos el campo "Vendedor" a la sección "Filtros" y configuraríamos los valores para que se muestren como moneda. Excel agrega automáticamente los elementos de fecha relevantes a la sección "Filas" y asume que queremos ver sumas.
De forma predeterminada, Excel mostrará todos los datos de todos los vendedores. También podemos seleccionar un único comercial para ver solo sus datos. En este caso, vemos que Judy French tuvo ventas en sólo tres meses, aunque sus ventas totales fueron significativas. Eso podría significar que su proceso de ventas fue más largo porque buscaba ballenas en lugar de peces, una conclusión valiosa, si es precisa. Quizás sería una buena idea invertir en un asistente para ayudar a Judy a cerrar sus ventas más rápidamente.
Paso 5: invertir en algo de aprendizaje
Si desea volverse realmente bueno en el uso de las tablas dinámicas de Excel, invierta algo de tiempo en aprender un poco más sobre cómo utiliza los distintos tipos de datos. Microsoft ofrece sus propios recursos de capacitación y hay una gran cantidad de capacitadores externos a considerar.
Sin embargo, en general, esto significa profundizar en Excel de una manera que está más allá del alcance de esta guía. Sin embargo, es de esperar que ahora vea cómo las tablas dinámicas pueden ser una herramienta poderosa para analizar sus datos, y es relativamente fácil comenzar siempre que tenga sus datos configurados en el tipo de tabla correcto. Y no podemos enfatizar lo suficiente lo importante que es saber qué desea lograr con su tabla dinámica antes de comenzar.
Las tablas dinámicas son una de las funciones más avanzadas de Excel. Si desea obtener más información sobre el software, consulte nuestra lista de los mejores consejos y trucos de Microsoft Excel .