Autor: Oscar Cronquist Artículo actualizado en octubre 13, 2018

Este artículo explica los conceptos básicos de las tablas dinámicas de Excel. He incluido el código vba para las acciones más comunes.

¿Qué es una tabla dinámica de Excel?

Una tabla dinámica le permite resumir grandes cantidades de valores increíblemente rápido en los grupos y subgrupos que especifique. Luego puede analizar los datos con facilidad, comparar valores por fecha o por grupo y ver tendencias importantes. Es una de las mejores y más potentes funciones de Excel y también una de las menos conocidas.

mesa pivotante2

Los gráficos de tablas dinámicas son una gran herramienta para visualizar sus datos.

mesa pivotante3

Slicers le permite filtrar datos rápidamente, sin embargo, el filtro de informe tiene la misma funcionalidad pero quizás no tan elegante.

rebanadores de mesa pivotante

El siguiente artículo muestra cómo analizar los datos de la tabla dinámica:

Analizar tendencias utilizando tablas dinámicas.

Tabla de contenido Introducción a las tablas dinámicas Crear tabla dinámica Datos de grupo Analizar datos (tabla dinámica) Comparar rendimiento, año a [...]

Analizar tendencias utilizando tablas dinámicas.

Volver arriba

Prepare sus datos de origen

Antes de crear su primera tabla dinámica, asegúrese de que su tabla de origen de datos sigue algunas reglas simples.

  1. Use nombres de encabezado únicos para todas sus columnas.
  2. Asegúrate de no tener celdas en blanco.
  3. Corrige tu ortografía. Si tiene una celda "West" y otra "Westt", ambas aparecerán en la tabla dinámica. Puedes corregir esto más tarde.
  4. Convierta su fuente de datos a una tabla definida de Excel (opcional).

Volver arriba


Reorganizar valores

https://www.youtube.com/watch?v=xmqTN0X-AgY

Esta imagen a continuación le muestra una tabla con una estructura de datos incorrecta, no puede usarla en una tabla dinámica.

tabla dinámica

La tabla a continuación es mucho mejor, todos los valores en esta tabla no se muestran por razones obvias. Sin embargo, faltan un par de cosas, ¿puedes verlo? Los nombres de encabezado de tabla únicos y la tabla de datos no es una tabla definida por Excel.

No tiene que usar una tabla definida por Excel, pero será mucho más fácil si agrega más valores a su tabla más adelante. Una tabla definida por Excel es dinámica y le ahorrará tiempo al no necesitar ajustar el rango de origen de la tabla dinámica.

mesa pivotante1

He creado una macro / udf que puede ayudarte a reorganizar tus datos, consulta estas publicaciones:

Normalizar datos [VBA]

Para poder utilizar una tabla dinámica, los datos de origen que tiene deben estar ordenados de manera que un […]

Normalizar datos [VBA]

Preparar datos para la tabla dinámica: ¿cómo dividir los valores concatenados?

Este artículo muestra una macro que le permite reorganizar y distribuir valores concatenados en varias filas para […]

Preparar datos para la tabla dinámica: ¿cómo dividir los valores concatenados?

Volver arriba


Utilice una tabla de Excel definida como fuente de datos.

¿Por qué desea utilizar una tabla de Excel como fuente de datos para su tabla dinámica? No tiene que hacerlo, pero le facilitará la vida cada vez que desee agregar o eliminar registros a su conjunto de datos.

La tabla de Excel se ajusta automáticamente a los nuevos datos y esto le ahorra tiempo ya que no necesita actualizar la referencia de la celda de origen de datos cada vez.

https://www.youtube.com/watch?v=03ysshhXZk8

Aquí es cómo convertir un conjunto de datos genéricos en una tabla definida de Excel:

  1. Seleccione una celda en su tabla de datos
  2. Ir a la pestaña "Insertar" en la cinta
  3. Haga clic en el botón "Tabla"

También puede utilizar estas teclas de método abreviado: CTRL + T

Aparece este cuadro de diálogo.

crear una tabla de Excel definida

Excel encuentra la tabla de datos completa automáticamente = $ A $ 1: $ C $ 48, asegúrese de que esto sea correcto.

Mi tabla no tiene encabezados de tabla, así que no selecciono la casilla de verificación "Mi tabla tiene encabezados".

Haga clic en el botón Aceptar.

crear una tabla definida excel 1

Excel ahora ha convertido su tabla de datos en una tabla definida por Excel. También ha insertado nuevos encabezados de columna, vea la imagen de arriba. No quiero usar estos nombres de encabezado de tabla, ya que no son descriptivos de cada columna.

crear una tabla definida excel 2

Esta tabla es mucho mejor, más adelante en estos nombres de encabezado de tabla nos será mucho más fácil al trabajar con la tabla dinámica.

Aprenda a cambiar la fuente de datos con una lista desplegable:

Cambiar la fuente de datos de la tabla dinámica utilizando una lista desplegable

En este artículo, le mostraré cómo cambiar rápidamente el origen de datos de la tabla dinámica mediante un menú desplegable [...]

Cambiar la fuente de datos de la tabla dinámica utilizando una lista desplegable

Aprenda más sobre las tablas de Excel:

Sea más productivo - Aprenda las tablas definidas en Excel

Una tabla de Excel le permite ordenar, filtrar y sumar fácilmente valores en un conjunto de datos donde los valores están relacionados.

Sea más productivo - Aprenda las tablas definidas en Excel

Volver arriba

Cómo construir una tabla dinámica

https://www.youtube.com/watch?v=peNTp5fuKFg
  1. Seleccione una celda en su tabla de datos
  2. Ir a la pestaña "Insertar" en la cinta
  3. Haga clic en el botón "Tabla de pivote"

Aparece este cuadro de diálogo.

crear tabla pivo

Utiliza el nombre de la tabla de Excel definido en el primer campo, muy bueno. También te permite elegir dónde quieres tu nueva tabla dinámica. Voy a colocar la mesa pivotante en una hoja nueva. Haga clic en el botón Aceptar.

crear tabla pivote1

Excel crea una tabla dinámica en blanco para nosotros en una nueva hoja de trabajo, vea la imagen de arriba.

Esto es lo que devuelve la grabadora de macros al insertar una tabla dinámica.

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Source data!R1C1:R365C7", Version:=6).CreatePivotTable TableDestination:= _
"Sheet17!R3C1", TableName:="PivotTable7", DefaultVersion:=6
Sheets("Sheet17").Select
Cells(3, 1).Select

El siguiente artículo le muestra cómo actualizar una tabla dinámica automáticamente:

Actualizar automáticamente una tabla dinámica

En una publicación anterior: cómo crear una tabla dinámica y actualizar automáticamente, demostré cómo actualizar un [...]

Actualizar automáticamente una tabla dinámica

Volver arriba

Configurando una tabla dinámica

La tabla dinámica en la hoja de trabajo está en blanco y nos dice "Para crear un informe, elija los campos de la Lista de campos de tabla dinámica".

Puede encontrar nuestros campos en el cuadro azul llamado 1, vea la imagen a continuación. Los campos son Región, Fecha y Cantidad iguales a sus nombres de encabezado en su tabla de fuente de datos, ahora entiende por qué es importante nombrar sus encabezados de fuente de datos.

crear tabla pivote2

La forma en que funciona es que puede hacer clic y mantener presionado con el botón izquierdo del mouse en uno de los campos y luego arrastrarlo a un área. Las áreas son Filtros, Columnas, Filas y Valores y he dibujado un recuadro azul a su alrededor con el nombre 2, vea la imagen de arriba.

Arrastre la región a los filtros, la fecha a las filas y la cantidad a los valores, vea la imagen a continuación. Excel está tratando de ayudarme aquí, agrupó automáticamente mis fechas en meses en la columna "Etiquetas de fila". No se preocupe, más adelante le mostraré cómo agrupar y desagrupar fechas.

crear tabla pivote3

Esto es lo que devuelve la grabadora de macros cuando arrastro Región al área de Filtros.

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Region")
.Orientation = xlPageField
.Position = 1
End With

Aquí está la salida cuando arrastro la región al área de columnas.

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Region")
.Orientation = xlColumnField
.Position = 1
End With

Esto sucede mientras se graba la fecha en el área Filas.

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").AutoGroup

Por último, el área de Montos a Valores.

ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Amount"), "Sum of Amount", xlSum

Aprende a usar hipervínculos en una tabla dinámica:

Usar hipervínculos en una tabla dinámica.

Sean pregunta: Básicamente, cuando hago una actualización de los datos en la hoja de cálculo "pivotdata", necesito reconocerla [...]

Usar hipervínculos en una tabla dinámica.

Volver arriba

Filtros de informe

El filtro de informe le permite seleccionar un subconjunto de sus datos. En este caso quiero trabajar con valores en la región "Oeste". Simplemente haga clic en la flecha y seleccione una Región. Haga clic en Aceptar. Vea la imagen animada a continuación.

tabla dinámica - filtro de informe

Si estás interesado en el código vba para esta acción aquí está.

ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").CurrentPage = _
"West"

Volver arriba

Etiquetas de columna

Haga clic y arrastre Región al área Columnas.

tabla dinámica - área de columnas

Los valores únicos de la columna Región en la tabla de datos de origen se muestran horizontalmente en la tabla dinámica (rango de celdas C4: E4, imagen de arriba).

Volver arriba

Etiquetas de fila

La siguiente imagen muestra Región en el área Filas y Fecha en el área Filtros.

tabla dinámica - etiquetas de fila

Volver arriba

Valores

La última área es Valores. No está limitado a números (Cantidad) aquí, puede arrastrar Fechas o Región aquí también. Tenga en cuenta que no puede sumar valores de texto, pero puede contarlos.

Aprenda a vincular un cuadro combinado con una tabla dinámica:

Rellene un cuadro combinado con valores de una tabla dinámica [VBA]

En esta publicación voy a demostrar dos cosas: Cómo llenar un cuadro combinado basado en encabezados de columna de […]

Rellene un cuadro combinado con valores de una tabla dinámica [VBA]

Volver arriba

Características de la mesa pivote

Aquí hay una lista de las funcionalidades más utilizadas.

Resume y analiza

La tabla dinámica es fenomenal al procesar grandes cantidades de datos muy rápidamente. Dado que los cálculos se realizan casi en ningún momento, puede desglosar fácilmente cada detalle muy rápidamente.

Le mostré anteriormente en este artículo que si arrastra un campo al área de Valores, la tabla dinámica suma sus valores en grupos dependiendo de los campos específicos que tenga en el área de Columnas y Filas.

También te permite ver tendencias en tus números, sigue este ejemplo. Arrastre "Cantidad" una vez más al área de Valores, ahora debería tener dos "Cantidad" allí.

tabla pivote - analizar

Haga clic izquierdo en la flecha que apunta hacia abajo al lado del segundo "Monto" y luego haga clic en "Configuración del campo de valor ...". Haga clic en la pestaña "Mostrar valores como"

tabla pivote - analisis2

Seleccione Fecha en el campo Base: y (anterior) en Elemento base :, vea la imagen de arriba. Haga clic en Aceptar

tabla pivote - analisis3

Ahora hay una nueva columna al lado de las sumas. Muestra el% de diferencia en comparación con los valores anteriores. El número en la celda B8 en comparación con el número en la celda B7 es -4.86% menos. (8410 / 8840) -1 = -4.86%

Aprende a construir un calendario de tabla dinámica:

Calendario de tabla dinámica

Este artículo muestra cómo crear un calendario en Excel. El calendario se crea como una tabla dinámica que hace que […]

Calendario de tabla dinámica

Volver arriba

Contar

La siguiente imagen muestra la región en el área de valores y en el área de filas. Fecha en el área Columnas agrupadas por mes.

tabla dinámica - área de valores2

Volver arriba

Lista distinta única

Puede usar una tabla dinámica para extraer una lista distinta única de una columna en una lista grande. La imagen muestra países, simplemente arrastre País al área Filas.

tabla dinámica - valores distintos únicos

Volver arriba

Extraer registros distintos únicos

Aquí hay una tabla con muchos registros duplicados.

  1. Seleccione una celda en la tabla de arriba.
  2. Vaya a la pestaña "Insertar" y haga clic en el botón "Tabla dinámica"
  3. Coloque la tabla dinámica en algún lugar de su hoja de trabajo / libro de trabajo.
  4. Arrastre "Nombre", "Ciudad" y "Estado" al campo "Etiquetas de fila"
  5. También arrastre "Nombre" al campo "Valores"

  6. Los valores no están en la misma fila, lo cual es confuso. Vaya a la pestaña "Diseño" en la cinta, haga clic en el botón "Diseño de informe" y luego "Mostrar en forma de tabla"

  7. Para mostrar todos los valores, vaya a la pestaña "Diseño" y haga clic en "Diseño de informe". Luego haga clic en "Repetir todas las etiquetas de artículos"
  8. Lo último que debe hacer es ocultar "Subtotales", haga clic en el botón "No mostrar subtotales".

Aquí hay una imagen de la mesa final, que muestra registros únicos únicos. En otras palabras, los registros duplicados se eliminan de la tabla de datos de origen.

Es posible extraer registros distintos únicos mediante una fórmula, lea esta publicación:

Filtrar registros distintos únicos

Tabla de contenido Filtrar registros únicos de filas distintas Filtrar registros únicos de filas distintas pero no espacios en blanco Filtrar filas únicas y distintas […]

Filtrar registros distintos únicos

Volver arriba

Contar valores distintos únicos

Excel 2013 y versiones posteriores le permiten contar valores distintos únicos. La siguiente imagen muestra la tabla de datos, el escenario es este:

¿Cuántos productos distintos y únicos vendió Jennifer en la región "Sur" y en enero 2011?

He resaltado los valores a continuación que coinciden con los criterios anteriores.

Para que esto funcione, debe habilitar una casilla de verificación antes de crear una nueva tabla dinámica.

Haz clic en el botón Aceptar. Para poder filtrar el mes de enero y no solo las fechas individuales, arrastre "Fecha" al área Filas.

Ahora hay dos campos "Fecha" en el área Filas, el otro es "Fecha (Mes)". Arrastre "Fecha (Mes)" desde el área Filas al área Filtros y arrastre "Fecha" de regreso al área superior. Ahora se ve así:

Arrastre "Región" y "Vendedor" al área de Filtros. Arrastre "Producto" al área Filas y valores.

Haga clic en "Recuento de productos" y luego haga clic en "Configuración del campo de valor ..."

Seleccione "Distinct Count" y haga clic en el botón "OK"

Ahora filtremos la tabla dinámica, haga clic en el filtro "flecha" al lado de Todos y seleccione "Enero", "Sur" y "Jennifer". Haz clic en el botón Aceptar.

Aquí está la tabla pivote final:

Hay dos productos distintos que se muestran al lado de "Total general".

Artículo relacionado:

Cuente valores distintos únicos en una tabla dinámica de Excel

ExcelBeginner pregunta: Tengo un pequeño problema que no estoy seguro de cómo resolver. Ahora tengo un […]

Cuente valores distintos únicos en una tabla dinámica de Excel

Volver arriba

Ordenar datos

Es posible ordenar casi cualquier cosa con datos en una tabla dinámica. Esta imagen muestra una tabla dinámica y quiero ordenar la columna Este de menor a mayor. Haga clic derecho en una celda en una columna que desea ordenar. Haga clic en "Ordenar" y luego ordene de menor a mayor.

tabla pivote - ordenar

Macro crea enlaces a todas las hojas, tablas, tablas dinámicas y rangos con nombre

Este artículo muestra una macro que llena automáticamente una hoja de trabajo con una Tabla de contenido, contiene hipervínculos a hojas de trabajo, […]

Macro crea enlaces a todas las hojas, tablas, tablas dinámicas y rangos con nombre

Volver arriba

Datos de grupo

Una tabla dinámica le permite agrupar fechas. Puede agrupar fechas (y tiempo) por segundos, minutos, horas, días, meses, trimestres y años.

Haga clic derecho en una columna o fila que desea agrupar, luego haga clic en agrupar y aparecerá este cuadro de diálogo.

tabla dinámica - fechas de grupo

Debajo de Grupo está Desagrupar y no creo que tenga que explicar eso.

tabla dinámica - desagrupar

Deshabilite los anchos de columna de ajuste automático para la tabla dinámica

Leí este interesante artículo Truco rápido: Cambiar el tamaño de los anchos de columna en tablas dinámicas en el blog de Microsoft Excel. Está […]

Deshabilite los anchos de columna de ajuste automático para la tabla dinámica

Volver arriba

Niveles múltiples

Puede reorganizar la tabla dinámica para que tenga varios niveles en el área Filas o el área Columnas o ambos. Arrastre más de un campo a un área para crear múltiples niveles.

tabla pivote - niveles múltiples

La imagen muestra los datos agrupados por trimestre y luego por región. También puede arrastrar campos en un área para ordenarlos, el orden es importante.

tabla dinámica - niveles múltiples1

El campo Fecha y Región cambió de lugar.

Volver arriba

Ver datos detrás de una celda de tabla dinámica

Para ver los datos detrás de una celda de tabla dinámica, simplemente haga doble clic en una celda que le interese y Excel crea una nueva hoja con los datos correspondientes que se muestran.

tabla dinámica - datos

Volver arriba

Grandes totales de fila y columna

Si no necesita los totales generales, desactívelos yendo a la pestaña "Diseño", haga clic en el botón "Totales generales" y seleccione la configuración que desee. Las opciones son:

  • Desactivado para filas y columnas
  • Encendido para filas y columnas
  • Solo para Filas
  • Encendido solo para columnas

Volver arriba

Insertar un gráfico de pivote

Un gráfico dinámico le ayuda a visualizar datos.

  1. Asegúrate de tener una celda seleccionada en tu tabla dinámica
  2. Ir a la pestaña "Analizar" en la cinta
  3. Haga clic en el botón "Pivot chart"

Desde aquí puede elegir una variedad de gráficos, la vista previa le ayuda en su decisión.

gráficos dinámicos

El gráfico cambiará si aplica un filtro u ordena una tabla dinámica.

tabla dinámica - filtro

Volver arriba

Rebanadores

Excel 2010 y las versiones posteriores de Excel le permiten insertar segmentaciones, no son diferentes al Filtro de Informe, excepto que se ven diferentes y ocupan más espacio en su hoja de trabajo.

Vaya a la pestaña "Insertar" y haga clic en el botón "Cortadores".

mesa pivote - rebanadoras de inserción

Desde aquí puede seleccionar un campo, seleccionaré la región y luego presionaré el botón OK. La rebanadora aparece en su hoja de trabajo, es posible que necesite una nueva ubicación para ella.

mesa pivote - insertar slicers1

Volver arriba

Actualizar una tabla dinámica

Si agrega, elimina o edita valores en su tabla de datos de origen, debe actualizar la tabla dinámica para reflejar los cambios realizados, cada vez. Esto es fácil de olvidar, aquí está cómo hacerlo.

Haga clic derecho en una celda de tabla dinámica.

tabla dinámica - actualizar

Haga clic en "Actualizar".

Puedes automatizar esto con una macro.

Private Sub Worksheet_Activate()
Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub

Póngalo en el módulo de código de su hoja de trabajo, vea esta publicación para más detalles.

Volver arriba

Copia una tabla dinámica sin

Si desea compartir su tabla dinámica pero no los datos de origen, siga estos pasos.

  1. Seleccione la tabla dinámica que desea copiar
  2. Presione Ctrl + C o presione el botón Copiar en la pestaña "Inicio" en la cinta
    tabla de pivote de desvinculación
  3. Haga clic derecho en una celda donde desea pegar la tabla dinámica
    desvincular tabla pivote1
  4. Haga clic en "Pegado especial ..."
    desvincular tabla pivote2
  5. Seleccione "valores"
  6. Haga clic en el botón Aceptar
  7. Repita los pasos 3 y 4 y luego seleccione "Formatos"
    desvincular tabla pivote3
  8. Haga clic en el botón Aceptar

desvincular tabla pivote4

Puede saber que la copia no tiene un enlace a los datos de origen seleccionando primero una sola celda en la tabla dinámica copiada y luego una única celda en la tabla original. Hay dos pestañas más (Analizar y Diseñar) en la cinta si se selecciona la tabla dinámica original

Volver arriba

Personalizar el diseño

Puede cambiar fácilmente el diseño de la tabla dinámica con uno de los estilos de tabla dinámica o crear uno completamente nuevo.

Seleccione una celda en la tabla dinámica. Ir al diseño de la pestaña. Haga clic en un estilo y la tabla dinámica se cambia instantáneamente. Puede desplazarse con el puntero del mouse sobre diferentes estilos y ver los cambios en el cambio de su tabla dinámica antes de que se decida.

personalizar tabla dinámica

¿Qué sucede si grabo una macro mientras cambio el estilo de la tabla dinámica?

Sub Macro2()
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight14"
End Sub

Para crear un nuevo estilo de tabla dinámica, vaya a la pestaña Diseño. Haga clic en la flecha en la esquina inferior derecha de la ventana de estilos pivotables, vea la imagen a continuación.

nuevo estilo de mesa pivote

Seleccione un elemento de la tabla y haga clic en el botón "Formato".

mesa pivotante nuevo estilo

Cambie el formato y haga clic en el botón Aceptar.

mesa pivotante nuevo estilo1

Repita con los elementos de tabla restantes que desee cambiar.

Volver arriba

Consolidar datos de múltiples rangos de celdas

Excel tiene una característica que le permite consolidar datos de varias tablas dinámicas o rangos de celdas.

Aquí están mis dos tablas dinámicas para 2015 y 2014. Quiero consolidar ambas tablas dinámicas en una sola, tenga en cuenta que comparten la misma estructura

Mesa de pivote 1 - 2015

tabla dinámica - consolidar hojas de trabajo

Mesa de pivote 2 - 2014

tabla dinámica - consolidar hojas de trabajo1

Aquí es cómo hacerlo.

  1. Presione Alt + D + P (Esto abre la tabla dinámica y el asistente de tabla dinámica)
    tabla dinámica - asistente
  2. Haga clic en "Múltiples rangos de consolidación"
  3. Haga clic en Siguiente
    tabla dinámica - wizard1
  4. Seleccione "Crear un solo campo de página para mí"
  5. Haga clic en el botón Siguiente
  6. Seleccione el primer rango de la hoja de trabajo que desea consolidar.
    tabla dinámica - wizard2
    Tenga en cuenta que también selecciono las etiquetas de fila y columna, pero no los totales generales.
  7. Haga clic en el botón Agregar
  8. Repita los pasos 6 y 7 con los rangos restantes.
  9. Haga clic en el botón Siguiente
  10. Seleccione si desea crear una nueva tabla dinámica en una hoja existente o una nueva hoja de trabajo
    tabla dinámica - wizard3
  11. Haga clic en Finalizar
    tabla dinámica - consolidar hojas de trabajo2
  12. Haga clic izquierdo en la flecha al lado de Conteo de valor
    tabla dinámica - consolidar hojas de trabajo3
  13. Haga clic en "Configuración del campo de valor ..."
    tabla dinámica - consolidar hojas de trabajo4
  14. Haga clic en "Suma"
  15. haga clic en Aceptar

tabla dinámica - consolidar hojas de trabajo5

Volver arriba