Cómo crear una tabla dinámica en Excel en línea

Las hojas de cálculo son excelentes para catalogar grandes grupos de datos, pero se necesita una característica como las tablas dinámicas para realmente sacar conclusiones de los datos. Excel Online facilita la creación de tablas dinámicas que lo ayudarán a resumir sus datos y le brindarán más información sobre lo que significan sus números sin procesar.

Aquí, le mostraremos cómo crear una tabla dinámica en Excel Online. Puedes usar nuestro hoja de demostración para practicar: Abra la hoja en Excel en líneay haga clic en Guardar en OneDrive para empezar a trabajar en su propia copia. Los ejemplos de este tutorial provienen de esa hoja de demostración y puede usarla para experimentar más una vez que esté listo.

¿Qué es una tabla dinámica?

Cuando las hojas de cálculo comienzan a expandirse más allá de unas pocas filas y columnas, resulta difícil realizar un seguimiento de los datos. Y cuando pierde el rastro de los datos, pierde el rastro del significado detrás de ellos. Ahí es donde entra una tabla dinámica: filtra y resume sus datos según los criterios que elija.

Digamos que registra diligentemente todos sus gastos durante todo el año en una sola hoja de cálculo. Al final del año, cuando se siente a revisar, tendrá dificultades para clasificar cientos de entradas. Esto es lo que se denomina datos planos: todo lo que ve es un mar de filas y columnas. Para obtener más información y significado de los datos, debe verlos dinámicamente. Por ejemplo, es posible que desee ver cuánto dinero gastó en alquiler y servicios públicos en el último trimestre. Con una tabla dinámica, puede hacer precisamente eso.

Y ese es solo un ejemplo simple. Se puede utilizar una tabla dinámica para analizar una variedad ilimitada de datos. Eso significa que no tendrá que crear una nueva hoja de cálculo para cada análisis; puede usar los mismos datos y manipularlos en la tabla dinámica para obtener nuevos conocimientos cada vez.

Cómo crear una tabla dinámica en Excel en línea

Aquí hay una descripción general rápida de cómo usar las tablas dinámicas (profundizaremos en la siguiente sección).

Paso 1: Abra la hoja de Excel en línea y seleccione todas las celdas que contienen los datos que desea ver. Paso 2: Seleccione Insertar > Tabla dinámica. Paso 3: En la ventana emergente, seleccione Nueva hoja de trabajo y haga clic en OK. Paso 4: En el editor de tablas dinámicas, arrastre las filas y columnas que desea resumir al cuadro correspondiente. Paso 5: En el Valores sección, seleccione los campos que tienen los valores que desea agregar o calcular. Paso 6: Si solo desea mostrar valores que cumplan con ciertos criterios, utilice el Filtros sección.

La mejor manera de aprender herramientas complejas es usándolas. Así que cargue la hoja de demostración y siga a continuación.

Crear la tabla dinámica

Lo primero que debemos hacer es convertir los datos planos sin procesar en una tabla dinámica. Antes de comenzar, asegúrese de que todas las columnas en la parte superior de su hoja tengan el nombre correcto.

Luego, seleccione todas las celdas que contienen datos y, en la barra de herramientas, seleccione Insertar > Tabla dinámica.

Aparecerá un cuadro de creación de tabla dinámica. Enumerará el rango de la tabla (el que ya ha determinado al seleccionar los datos) y le dará la opción de crear una tabla dinámica en la misma hoja de trabajo o en una nueva. Para simplificar las cosas, elija el Nueva hoja de trabajo opción y haga clic en OK.

Esto creará una nueva hoja de cálculo en la que creará sus informes de tabla dinámica dinámica.

Cómo crear un informe de tabla dinámica

Una tabla dinámica comienza vacía. Todo lo que verá en el borde derecho de la hoja es el editor de tabla dinámica, donde encontrará todas las opciones para construir su tabla dinámica.

El editor está dividido en dos secciones horizontales.

• La sección superior enumera todos los campos; estas son todas las columnas de los datos de su tabla.

• En la sección inferior, encontrará el área real para manipular la tabla dinámica. Está dividido en cuatro partes: Filtros, Columnas, Filas, y Valores.

El proceso de creación de una tabla dinámica en Excel Online utiliza la funcionalidad de arrastrar y soltar. Agrega un campo a un área simplemente arrastrándolo allí. ¿Ya no quieres un campo en una caja? Arrástrelo y desaparecerá. Más allá de eso, aprenderá a usar estas herramientas a medida que avancemos.

Para empezar, aquí está el análisis que vamos a hacer como ejemplo de la hoja de demostración:

Para cada uno de nuestros clientes, en diferentes tipos de proyectos, ¿cuánto facturamos en 2017?

En este caso, buscamos cuatro cosas.

• Para cada cliente

• en todos los tipos de proyectos

• monto total facturado

• en 2017

Este análisis se basa en cuatro partes y cubrirá las cuatro secciones de los campos de la tabla dinámica: filas, columnas, valores y filtros.

• La Filas y Columnas le ayudará a construir los datos bidimensionales básicos a partir de los cuales calculará la tercera dimensión de valores. Aquí, nuestros datos básicos de filas y columnas son Nombre del cliente y Tipo de proyecto, respectivamente.

• La Valor, o el cálculo que queremos obtener de las celdas relacionadas con el Nombre del cliente y el Tipo de proyecto, es el monto total facturado (la suma de la columna Monto facturado).

• La Filtrar La opción nos ayudará a filtrar solo los datos del año 2017 y ocultará todo lo demás.

Ahora que sabemos qué tipo de informe estamos creando, comencemos el proceso agregando primero las filas.

Agregar filas

Comenzaremos agregando el campo Nombre del cliente al Filas sección. Hay algunas formas de hacer esto (por ejemplo, haga clic en la marca de verificación junto al nombre del cliente título de la barra lateral, haga clic en el menú desplegable al final de la nombre del cliente sección y seleccione Agregar a etiquetas de fila), pero la mejor forma de proceder es mediante la función de arrastrar y soltar.

Haga clic y mantenga presionado el nombre del cliente campo, arrástrelo al Filas en la mitad inferior de la barra lateral y suéltelo.

Al instante, verá que la primera columna se completa con todos los nombres de sus clientes (sin duplicados). De forma predeterminada, esto se ordenará en orden ascendente, pero puede hacer clic en el menú desplegable junto al Etiquetas de fila celda para cambiar el orden.

Agregar columnas

A continuación, agregaremos el campo Tipo de proyecto como una columna. Ahora que conoce el ejercicio, simplemente arrastre el Tipos de proyectos campo al Columnas en la parte inferior del editor de tablas dinámicas.

Agregar valores

Ahora tiene una tabla bidimensional y es el momento de agregar valores a esa tabla.

Arrastrar el Cantidad cobrada opción a la Valores sección. Al instante, tendrá información y datos útiles en la hoja de cálculo. Notará que una sección de Gran Total se crea automáticamente (tanto para filas como para columnas), y ya puede ver el gran total para un tipo de proyecto en particular y un cliente de forma independiente.

Agregar filtros

Todavía no tenemos nuestra respuesta completa. Ahora necesitamos filtrar los datos para mostrar solo los valores de 2017.

Para hacer esto, arrastre el Año campo al Filtros sección. Verá dos celdas nuevas en la parte superior de la hoja. La celda junto a Year actualmente dice Todas. Haga clic en el menú desplegable junto a la celda, desmarque el botón junto a 2018y haga clic en OK.

Todas las entradas del año 2018 desaparecerán instantáneamente y tendrás tu respuesta a la pregunta original: Para cada uno de nuestros clientes, en diferentes tipos de proyectos, ¿cuánto facturamos en 2017?

Por supuesto, el filtrado no se limita solo al año. Puede filtrar en función de cualquier columna del conjunto de datos original.

Bucear más profundo

Ahora que tenemos toda la información importante en frente a nosotros, podemos usar la tabla dinámica para responder cualquier tipo de pregunta que tengamos sobre los datos. Profundicemos un poco más para solidificar nuestra comprensión de las tablas dinámicas en Excel Online con dos ejemplos más.

¿A qué cliente facturamos más en 2017?

Como esta es una pregunta más simple, primero tendremos que simplificar nuestro informe. Solo necesitamos el nombre de nuestros clientes como filas y la suma del monto facturado como valores.

Así que empieza quitando el Tipo de proyecto campo del Columnas sección: Arrástrelo fuera de la sección (alternativamente, puede hacer clic en el campo y seleccionar Quitar campo).

Ahora haga clic en el menú desplegable junto al Etiquetas de fila celda y seleccione Ordenar por valor. En la ventana emergente, Suma de la cantidad facturada ya estará seleccionado. Puede elegir ordenar de menor a mayor o viceversa, según sus necesidades. Hacer clic OK.

La tabla dinámica ahora muestra exactamente cuánto se facturó a cada cliente en el año 2017, en orden ascendente: con $ 1,700, Questindustries fue el cliente más facturado en 2017.

Respondamos una pregunta más: ¿Qué tipo de proyecto tuvo la tarifa por hora más alta en promedio?

Ahora podemos pasar del análisis de la cantidad total a la tarifa promedio por hora.

Para hacer esto, elimine nombre del cliente desde el Filas sección y reemplácela con Tipo de proyecto. En el Valores sección, quitar Cantidad cobrada y arrastre en el Tarifa por hora en su lugar.

Ahora, haga clic en el Suma de horas gastadas botón y seleccione Configuración del campo de valor. Aquí, cambie de Suma a Promedio.

Entonces ve al Etiquetas de fila menú desplegable, seleccione Ordenar por valory cambia al De mayor a menor opción para ver la tarifa por hora más alta en la parte superior de la lista. Verá instantáneamente la tarifa promedio por hora en todos los tipos de proyectos en 2017. La tarifa por hora más alta en promedio es de $ 68 por hora para la corrección de estilo.

Ahora que comprende los conceptos básicos de las tablas dinámicas en Excel, use nuestra hoja de demostración para probar algunos análisis aún más avanzados. Una vez que se familiarice con todos los parámetros, podrá utilizarlos para generar cualquier informe que desee.