Cómo crear tablas dinámicas en Excel

Tabla de contenido

Las tablas dinámicas son una de las características más poderosas de Excel. Incluso si es un novato, puede convertir grandes cantidades de datos en información útil. La tabla dinámica puede ayudarlo a realizar informes en minutos. Analice sus datos limpios fácilmente y si los datos no están limpios, puede ayudarlo a limpiar sus datos. No quiero aburrirte, así que saltemos y exploremos.

Cómo crear una tabla dinámica

Es simple. Simplemente seleccione sus datos. Vaya a Insertar. Haga clic en la tabla dinámica y listo.

Pero espera. Antes de crear una tabla dinámica, asegúrese de que todas las columnas tengan un encabezado.

Si algún encabezado de columna se deja en blanco, la tabla dinámica no se creará y mostrará un mensaje de error.

Requisito 1: todas las columnas deben tener un encabezado para comenzar con las tablas dinámicas en Excel

Debe tener sus datos organizados con el encabezado adecuado. Una vez que lo tenga, puede insertar la tabla dinámica.

Insertar la tabla dinámica desde la cinta
Para insertar una tabla dinámica desde el menú, siga estos pasos:
1. Seleccione su rango de datos

2. Vaya a la pestaña Insertar

3. Haga clic en el icono de la tabla dinámica.

4. Aparecerá el cuadro de opción Crear una tabla dinámica.

5. Aquí puede ver el rango de datos que seleccionó. Si cree que este no es el rango que desea seleccionar, cambie directamente el tamaño de su rango desde aquí en lugar de volver atrás y seleccionar datos nuevamente.
6. A continuación, puede seleccionar dónde desea su tabla dinámica. Recomiendo usar la nueva hoja de trabajo, pero también puede usar la hoja de trabajo actual. Simplemente defina la ubicación en el cuadro de ubicación.
7. Ahora, si ha terminado con la configuración, presione el botón Aceptar. Tendrás tu tabla dinámica en una nueva hoja. Simplemente seleccione sus campos para resúmenes. Veremos cómo creamos un resumen de datos usando la tabla dinámica, pero primero aclaremos los conceptos básicos. En este tutorial de tablas dinámicas de Excel, aprenderá más de lo que espera.

Insertar acceso directo de tabla dinámica (Alt> N> V)
Este es un atajo de teclado secuencial para abrir el Crear Tabla dinámica cuadro de opción.

Presione el botón Alt y suéltelo. Pegar norte y suéltalo. Pegar V y suéltalo. Se abrirá el cuadro de opción Crear una tabla dinámica.

Ahora simplemente siga el procedimiento anterior para crear una tabla dinámica en Excel.
Insertar acceso directo de tabla dinámica usando el acceso directo de Excel antiguo (Alt> D> P)
Una cosa que más me gusta de Microsoft Excel es que en cada nueva versión de Excel introducen nuevas funciones y no descartan las funciones antiguas (como hizo MS con Win 8. Fue patético). Esta permite al usuario mayor trabajar normalmente en nuevas versiones como solía trabajar en versiones anteriores.
Si presiona secuencialmente ALT, D y P en el teclado, Excel se abrirá para crear un asistente de tabla dinámica.

Seleccione la opción adecuada. La opción seleccionada en la captura de pantalla anterior nos llevará a crear una tabla dinámica como la creamos antes.
Presione Enter o haga clic en Siguiente si desea verificar el rango seleccionado.

Presiona Enter nuevamente.

Seleccione una nueva hoja de trabajo o donde desee que su tabla dinámica presione Enter. Y esta hecho.

Cree informes utilizando tablas dinámicas.
Ahora sabe cómo insertar una tabla dinámica. Preparémonos para hacer informes usando tablas dinámicas en minutos.
Tenemos los datos para el pedido estacionario.

Los campos de las columnas son:

Fecha de orden: Fecha de pedido (Obviamente)

Región: La región de orden en el país

Cliente: Nombre del cliente (Qué puede ser)

Artículo: Artículo pedido

Unidad: Número de unidades de un artículo pedido

Costo unitario: Costo por unidad

Total: Costo total del pedido (unidad * costo unitario).

Áreas de pivote: Estas 4 áreas se utilizan para mostrar sus datos de manera educada.

FILTROS: Coloque aquí los campos a los que le gustaría aplicar filtros en su informe.

COLUMNAS: Coloque aquí los campos que desee en sus columnas del informe: (es mejor mostrar que explicar)

FILAS: Arrastre los campos que desea mostrar sin procesar como en la imagen de arriba, he mostrado Región en FILAS.

VALORES: Seleccione un campo para obtener el recuento, la suma, el promedio, el porcentaje (y muchos más), etc., que querrá ver.

Ahora, utilizando la información anterior, hemos preparado este informe dinámico rápido que muestra desde qué región cuántos pedidos se realizan para cada artículo.

Ahora que comprende sus datos y los campos dinámicos (después de todo, es inteligente), respondamos algunas preguntas relacionadas con estos datos utilizando la tabla dinámica rápidamente.
Q1. ¿Cuántos pedidos hay?

La tabla dinámica está inicialmente en blanco, como se muestra en la imagen siguiente.

Debe seleccionar campos (nombres de columna) en las áreas apropiadas para ver el resumen o los detalles de ese campo.
Ahora, para responder a la pregunta anterior, seleccionaré Artículo (seleccione cualquier columna, solo asegúrese de que no tenga una celda en blanco en el medio) en los campos de valor.

Seleccione Artículo de la lista de campos y arrástrelo al campo Valor.

Tenemos nuestra respuesta. Las tablas dinámicas me dicen que hay un total de 43 pedidos. Esto es correcto.
Consejo profesional: Debe verificar sus datos si son correctos o no. Si este número no coincide con los datos, significa que ha seleccionado un rango incorrecto o que el campo tiene celdas en blanco.
Info: El campo de valor por defecto cuenta el número de entradas en una columna si contiene texto y sumas si el campo solo contiene valores. Puede cambiar esto en la configuración del campo de valor. ¿Cómo? Lo veremos más adelante en este tutorial de tabla dinámica.
Ahora que he seleccionado Fecha de orden en la Valuesarea, se nota 42. Este medio Fecha de orden tiene una celda en blanco ya que sabemos que el número total de pedidos es 43.

Identifique datos irregulares mediante tablas dinámicas y límpielos.
La tabla dinámica puede ayudarlo a encontrar información incorrecta en los datos.

La mayoría de las veces, nuestros datos son preparados por operadores de entrada de datos o por usuarios que generalmente son irregulares y necesitan algo de limpieza para preparar informes y análisis precisos.

Siempre debe limpiar y preparar sus datos de manera educada, antes de preparar cualquier informe. Pero a veces solo después de preparar el informe nos damos cuenta de que nuestros datos tienen alguna irregularidad. Ven, te mostraré cómo …
Q2: Indique el número de pedidos de cada región
Ahora para responder a esta pregunta:
Seleccione Región y arrástrelo a Filas Área y Artículo para Valores Zona.

Obtendremos datos divididos según la región. Podemos responder de qué región han llegado cuántos pedidos.

Hay un total de 4 regiones en nuestros datos según la tabla dinámica. Pero espera, nota que Central y Centrle región. Sabemos que Centrle debería ser Central. Hay una irregularidad. Necesitamos ir a nuestros datos y hacer una limpieza de datos.

Para limpiar los datos en el campo de región, filtramos el nombre de región incorrecto (Centrle) y lo corregimos (Central).

Ahora regrese a sus datos dinámicos.

Haga clic derecho en cualquier lugar de la tabla dinámica y haga clic en Actualizar.

Su informe ahora se ha actualizado.
INFO: Independientemente de los cambios que realice en sus datos de origen, la tabla dinámica seguirá funcionando con datos antiguos hasta que lo actualice. Excel crea un caché dinámico y una tabla dinámica se ejecuta en ese caché. Cuando se actualiza, la caché anterior se cambia con datos nuevos.

Ahora, puede ver que, de hecho, solo hay 3 regiones.
Formato de informe dinámico con filas categorizadas.

A veces, necesitará informes como el de la imagen de arriba. Esto facilita la visualización de sus datos de forma estructurada. Puede saber fácilmente desde qué región cuántos artículos se ordenan. Veamos cómo puede hacer esto.
Moverse Región y Artículo para FILAS zona. Asegúrese de que la región esté en la parte superior y los elementos en la parte inferior, como se muestra en la imagen.

Arrastrar Artículo por Valor Zona.

Como resultado, obtendrá este informe.

Servirá. Pero a veces su jefe quiere informar en forma tabular sin subtotales. Para hacer esto, necesitamos formatear nuestra tabla dinámica.
Eliminar subtotales de la tabla dinámica
Sigue estos pasos:
1. Haga clic en cualquier lugar de su tabla dinámica.
2. Vaya a la Diseño Pestaña.

3. Haga clic en el menú Subtotales.

4. Haga clic en No mostrar subtotales.


Puede ver que ahora no hay subtotales.

Multa. Pero todavía no está en forma de tabla. Las regiones y los elementos se muestran en una sola columna. Muéstrelos por separado.
Hacer tabular una tabla dinámica
Ahora, para mostrar regiones y elementos en diferentes columnas, siga estos pasos:
1. Haga clic en cualquier lugar de la tabla dinámica.
2. Vaya a la pestaña Diseño
3. Haga clic en Diseño de informe.


4. Haga clic en Mostrar para la forma tabular opción. Finalmente, tendrá esta vista sofisticada de su informe.

Ahora sabemos el número total de pedidos realizados para cada artículo de cada región. Se muestra en el recuento de artículos columnas.
Cambie el nombre de la columna a Pedidos.

Ahora luce mejor.

P3: ¿Cuántas unidades de cada artículo se piden?

Para responder a esta pregunta, necesitamos una suma de Unidades. Para hacerlo, simplemente mueva el campo Unidades a Valores. Resumirá automáticamente el número de unidades de cada artículo. Si una columna de la tabla dinámica solo contiene valores, la tabla dinámica muestra de forma predeterminada la suma de esos valores. Pero se puede cambiar desde una configuración de campo de valor. ¿Cómo? Te mostraré lo último.

Configuración del campo de valor de la tabla dinámica
P4: ¿El precio medio de cada artículo?
En nuestros datos de muestra, el precio de un artículo es diferente para diferentes pedidos. Por ejemplo, consulte Carpetas.

Quiero saber el costo promedio de cada artículo en la tabla dinámica. Para averiguarlo, arrastre Costo unitario al campo Valor. Mostrará la suma del costo unitario.

No queremos Suma del costo unitario, queremos Promedio del costo unitario. Para hacerlo …

  1. Haga clic con el botón derecho en cualquier lugar de la suma de la columna Costo unitario en la tabla dinámica
  2. Hacer clic Configuración del campo de valor
  3. Según las Opciones disponibles, Seleccione Promedio y presione OK.


Finalmente, tendrá este Informe dinámico:

Campos calculados de tabla dinámica
Una de las funciones más útiles de la tabla dinámica son sus campos calculados. Los campos calculados son campos que se obtienen mediante algunas operaciones en las columnas disponibles.

Entendamos cómo insertar campos calculados en una tabla dinámica con un ejemplo:
Con base en nuestros datos, preparamos este informe.

Aquí tenemos Suma de unidades y Coste total. Acabo de mover la columna total al campo Valores y luego le cambié el nombre a Coste total. Ahora quiero saber el precio promedio de una unidad de cada artículo para cada región. Y eso seria:

Precio promedio = costo total / unidades totales

Insertemos un campo en la tabla dinámica que muestre el precio promedio de cada artículo por región:

Siga estos pasos para insertar un campo calculado en la tabla dinámica
1. Haga clic en cualquier lugar de la tabla dinámica y vaya a Pestaña Analizar

2. Haga clic en Campos, elementos y conjuntos en el grupo de cálculo.

3. Haga clic en Campos calculados.
Verá este cuadro de entrada para su campo de cálculo:

4. En el cuadro de entrada del nombre, escriba el promedio Costo o lo que quieras, a Excel no le importará. En el fórmula cuadro de entrada, escribir y pulsar OK.

= Total / Unidades

Puede escribir esto manualmente desde el teclado o puede hacer doble clic en los nombres de campo enumerados en el área Campos para realizar operaciones.

Va a ayudar al desarrollo del sitio, compartir la página con sus amigos

wave wave wave wave wave