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.
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 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 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.
Presiona el botón Alt y suéltalo. 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 pero no descarte las funciones antiguas (como hizo MS con la victoria 8. Fue patético). Esta permite a los usuarios antiguos trabajar normalmente en versiones nuevas como solían trabajar en versiones anteriores.
Si presiona secuencialmente ALT, D y P en el teclado, Excel 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.
Crear 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 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é más puede ser)
Artículo: Artículo pedido
Unidad: Número de unidades de un artículo pedido
Costo unitario: Costo por unidad
Total: Coste total del pedido (unidad * coste unitario).
Para crear informes usando tablas dinámicas usaremos
Campos de tabla dinámica: Contiene la lista de nombres de columnas en sus datos.
Á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 en el 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é Elemento (seleccione cualquier columna, solo asegúrese de que no tenga una celda en blanco en el medio) en los campos de valor.
Seleccione Elemento 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.
Ahora que he seleccionado Fecha de orden en el área de Valores, muestra 42. Esta 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.
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 los subtotales menú.
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 opción Formulario tabular. 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 columnas de artículos.
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. Haga clic en Configuración del campo de valor
3. Basado en 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 la 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 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 / 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.
5. Tiene su campo calculado agregado ahora a su tabla dinámica. Se denomina como la suma del costo promedio, pero no es una suma. Excel simplemente ejecuta la función predeterminada para nombrar la columna (como un ritual). Cambie el nombre de esta columna y limite los dígitos decimales que se muestran.
Y ahí tienes un campo calculado. Puedes hacerlo tan complejo como quieras. A modo de ejemplo, tomé esta simple operación promedio.
Agrupación en la tabla dinámica
Tiene este informe dinámico preparado.
Ahora quiero que este informe se divida anualmente. Vea la instantánea a continuación.
Tenemos una columna sobre la fecha del pedido. Mueva el campo OrderDate a Rows on the Top.
No se parece en nada al informe requerido. Necesitamos tener fechas grupales por año.
Ahora, para agrupar un campo en la tabla dinámica de Excel, siga estos pasos:
1. Haga clic derecho en el campo que desea agrupar.
2. Haga clic en Grupo. Tendrá este cuadro de opción para personalizarlo. Dado que se trata de una columna de datos, Excel nos muestra la agrupación en consecuencia. Puede elegir su fecha de inicio y finalización.
3. Elija por años y presione Aceptar. Ha realizado agrupaciones anuales en la tabla dinámica de Excel.
Rebanadores de mesa pivote
Las rebanadoras se introdujeron en Excel 2010 como complemento. En Excel 2013 y 2016 está disponible de forma predeterminada al igual que los filtros.
Las rebanadoras no son más que filtros. A diferencia de Filtros, Slicers muestra todas las opciones disponibles justo enfrente de usted. Hace que su tablero sea más interactivo.
Cómo agregar rebanadores en una tabla dinámica, Excel 2016 y 2013
Las cortadoras de mesa pivotantes son fáciles de agregar. Sigue estos pasos:
1. Haga clic en cualquier lugar de la tabla dinámica y vaya a la pestaña Analizar.
2. Haga clic en Insertar rebanador. Tendrá una lista de campos para sus datos. Seleccione tantos como desee.
3. Para este ejemplo, seleccione Región y presione Aceptar.
Ha agregado Slicer a su informe. Ahora aplique el filtro con un solo clic.
Insertar cronograma en Excel 2016 y 2013
Esta es una de mis tablas dinámicas de Excel con funciones favoritas. Esta nueva funcionalidad solo funciona con fechas. Con esto, puede seleccionar visualmente un período de tiempo para filtrar sus datos.
Para insertar un cursor de línea de tiempo, siga estos pasos:
Cómo agregar una línea de tiempo en una tabla dinámica, Excel 2016 y 2013
1. Haga clic en cualquier lugar de la tabla dinámica y vaya a la pestaña Analizar.
2. Haga clic en Insertar línea de tiempo desde el grupo de filtros. Todas las columnas que contienen valores de tiempo en datos de origen se enumerarán en un cuadro de opción para elegir. Aquí solo tenemos uno. Así que sí…
2. Elija sus opciones y presione Entrar o haga clic en Aceptar. Está hecho y tiene la línea de tiempo de la tabla dinámica justo frente a usted.
Puede elegir mostrarlo diariamente, mensualmente, trimestralmente o anualmente. He elegido Monthly aquí.
En este artículo, he cubierto las funcionalidades más importantes y útiles de la tabla dinámica. Exploramos las nuevas características de la tabla dinámica en Excel 2016 y 2013. Aprendimos sobre el uso clásico de una tabla dinámica que se ejecutaba en Excel 2007, 2010 y versiones anteriores. Siguen siendo útiles. Si no encontró su respuesta relacionada con su tabla dinámica aquí, pregunte en la sección de comentarios.
Hay muchas otras funciones que aún no se han explicado. Aprendemos la función avanzada de tabla dinámica en el próximo artículo. Hasta entonces Excel en todo.
