Las tablas dinámicas son una de las funciones 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.

001

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

002

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

003

Inserte 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

004

  1. Vaya a la pestaña Insertar

005

  1. Haga clic en el icono de tabla dinámica

006

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

007

  1. 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.

  2. 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.

  3. 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.

008

Insertar acceso directo de tabla dinámica (Alt> N> V)

Este es un atajo de teclado secuencial para abrir el cuadro de opción Crear tabla dinámica.

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

009

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 de las cosas que más me gustan de Microsoft Excel es que en cada nueva versión de Excel introducen nuevas funciones, pero no descartan las antiguas (como hizo MS con win 8. Fue patético). Esto * permite que los usuarios mayores trabajen normalmente en versiones nuevas como solían 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.

0010

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.

0011

Presiona Enter nuevamente.

0012

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

0013

Cree informes usando 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 del pedido estacionario.

0014

Los campos de la columna son:

Fecha de pedido: Fecha de pedido (Obviamente)

Región: La región de pedido en el país Cliente: Nombre del cliente (_ qué más puede ser) _ Artículo: Artículo pedido Unidad: Número de unidades de un artículo pedido Costo unitario: Costo unitario Total: Costo total del pedido (unidad unidad costo).

0015

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

0016

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)

0017

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.

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.

0018

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é Elemento (seleccione cualquier columna, solo asegúrese de que no tenga una celda en blanco en el medio) en los campos de valor.

0019

Seleccione Elemento de la lista de campos y arrástrelo al campo Valor.

0020

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.

Información: 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 OrderDate en Valuesarea, muestra * 42.

Esto significa que OrderDate tiene una celda en blanco, ya que sabemos que el número total de pedidos es 43 *.

0021

Identifique datos irregulares usando 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 al Área de filas y Elemento al Área de valores.

0022

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 espere, observe que la región Central y Centrle. 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.

0023

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 los 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.

0024

Ahora, puede ver que, de hecho, solo hay 3 regiones.

Formato de informe dinámico con filas categorizadas.

0025

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.

Mueva la región y el elemento al área FILAS. 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.

0026

Arrastre el elemento para el área de valor.

0027

Como resultado, obtendrá este informe.

0028

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

Siga estos pasos:

  1. Haga clic en cualquier lugar de su tabla dinámica.

  2. Vaya a la pestaña Diseño.

0029

  1. Haga clic en el menú Subtotales.

0030

  1. Haga clic en No mostrar subtotales.

0031

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éstralos 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.

0032

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

0033

Ahora sabemos el número total de pedidos realizados para cada artículo de cada región. Se muestra en las columnas Recuento de elementos.

Cambie el nombre de la columna a Pedidos.

0034

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? Les mostraré lo último.

0035

Configuración del campo de valor de la tabla dinámica

Q4: ¿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.

0040

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.

0041

No queremos la suma del costo unitario, queremos el 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. Haga clic en Configuración del campo de valor. Según las Opciones disponibles, seleccione Promedio y presione Aceptar.

0036

Finalmente, tendrá este Informe dinámico:

0042

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 columnas disponibles.

Entendamos cómo insertar campos calculados en una tabla dinámica con un ejemplo:

Con base en nuestros datos, preparamos este informe.

0043

Aquí, tenemos la suma de unidades y el costo total. Acabo de mover la columna total al campo Valores y luego le cambié el nombre a Costo total. Ahora quiero saber el precio promedio de una unidad de cada artículo para cada región.

Y ese sería:

Average Price = Total Cost / Total Units

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 la pestaña Analizar

0044

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

0045

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

0047

  1. En el cuadro de entrada del nombre, escriba el costo promedio o cualquier cosa que desee, a Excel no le importará. En el cuadro de entrada de la fórmula, escriba y presione Aceptar.

=Total / Units

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.