Tabla dinámica en Excel: cómo crear una, paso a paso

Una tabla dinámica agrupa las filas de una tabla por una categoría, como Region, y suma un número, como Sales, para cada grupo, sin fórmulas. Para crear una, haz clic en una celda de los datos, ve a Insertar > Tabla dinámica, pulsa Aceptar y arrastra Region a Filas y Sales a Valores. La tabla de abajo no es una tabla dinámica: construye el mismo resumen con fórmulas, para que veas cómo cambian los totales. Usa UNICOS (UNIQUE en inglés) y SUMAR.SI (SUMIF en inglés), y también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México): =SUMAR.SI($A$2:$A$9;E2;$C$2:$C$9).

FórmulasRestablecer

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUMAR.SI($A$2:$A$9;E2;$C$2:$C$9)

UNICOS lista cada región una vez y SUMAR.SI la suma: North 455, South 305 y East 170, que son el 49%, el 33% y el 18% de los 930 totales. Cambia C3 a 185 y el total de South y los tres porcentajes lo siguen al instante. Una tabla dinámica mostraría los mismos números, pero solo después de actualizarla.

Cómo crear una tabla dinámica

Antes de empezar, revisa los datos de origen: una fila de encabezado con un nombre en cada columna, un registro por fila, sin filas ni columnas vacías en medio y sin filas de subtotal.

1. Haz clic en cualquier celda de los datos.
2. Ve a Insertar > Tabla dinámica (en algunas versiones, Insertar > Tabla dinámica > De una tabla o rango).
3. Excel completa el rango. Elige Nueva hoja de cálculo y pulsa Aceptar.
4. Aparece una tabla dinámica vacía con el panel Campos de tabla dinámica a la derecha, que lista los encabezados de tus columnas.
5. Arrastra Region al cuadro Filas y Sales al cuadro Valores. La tabla dinámica muestra cada región una vez con la Suma de Sales al lado, y una fila de Total general.
6. Para cambiar lo que se muestra, arrastra campos entre los cuadros o fuera del panel.

Si no sabes por dónde empezar, Insertar > Tablas dinámicas recomendadas muestra algunos diseños listos para tus datos. En Mac el menú es el mismo: Insertar > Tabla dinámica.

Filas, Columnas, Valores y Filtros

El panel Campos de tabla dinámica tiene cuatro cuadros, y cada tabla dinámica es una elección de qué columna va en cada cuadro:

• Filas: las categorías del lado izquierdo, una fila por cada valor distinto (Region).
• Columnas: categorías a lo ancho de la parte superior, una columna por cada valor distinto (Product).
• Valores: los números que se calculan para cada combinación. Suma es lo predeterminado para una columna numérica; Cuenta, Promedio, Máx, Mín y otras están en Configuración de campo de valor.
• Filtros: un campo por el que se filtra toda la tabla dinámica, que aparece como una lista desplegable encima de ella.

Con Region en Filas, Product en Columnas y Sales en Valores, la tabla dinámica de los datos de arriba queda así (con los rótulos de Excel en inglés):

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

La versión con fórmulas de ese diseño lista las regiones hacia abajo con UNICOS, los productos a lo ancho con TRANSPONER(UNICOS()), y calcula cada celda de la cuadrícula con un solo SUMAR.SI.CONJUNTO (SUMIFS en inglés) que recibe las dos listas como criterios:

FórmulasRestablecer

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUMAR.SI.CONJUNTO(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1)

E2 derrama North, South y East hacia abajo, F1 derrama Apple y Pear a lo ancho, y el SUMAR.SI.CONJUNTO de F2 llena la cuadrícula de 3 por 2 entre ellos: un total por cada pareja de región y producto. Cambia B5 de Apple a Pear y cambian las dos celdas de East. El orden aquí es el orden en que aparecen los valores por primera vez; una tabla dinámica ordena sus rótulos alfabéticamente.

Contar, promediar o porcentaje en lugar de sumar

En la tabla dinámica, haz clic en el campo del cuadro Valores y elige Configuración de campo de valor. La pestaña Resumir valores por cambia entre Suma, Cuenta, Promedio, Máx y Mín; la pestaña Mostrar valores como convierte los números en % del total general, % del total de columna, un total acumulado y otros. Cada fórmula tiene un equivalente directo:

FórmulasRestablecer

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =UNICOS(A2:A9)

North tiene 3 pedidos con un promedio de 151.7, South 3 con un promedio de 101.7, East 2 con un promedio de 85.0. Aquí las fórmulas son CONTAR.SI (COUNTIF en inglés) y PROMEDIO.SI (AVERAGEIF en inglés). La columna de % del total general está en la primera tabla de esta página.

Filtrar el resumen por un producto

El cuadro Filtros pone una lista desplegable encima de la tabla dinámica. La versión con fórmulas es una celda con una lista desplegable y SUMAR.SI.CONJUNTO, que añade una condición más a SUMAR.SI. Elige un producto en F1:

FórmulasRestablecer

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Con Apple elegido, North muestra 215, South 150 y East 60. Elige Pear y cambian a 240, 155 y 110. Consulta SUMAR.SI.CONJUNTO para más condiciones y la página de lista desplegable para añadir la lista en Excel.

Actualizar una tabla dinámica

Una tabla dinámica guarda una copia de los datos de origen (la caché de la tabla dinámica) y no se recalcula cuando cambia una celda del origen. Después de editar los datos:

• Haz clic derecho en cualquier parte de la tabla dinámica y elige Actualizar, o pulsa Alt+F5 en Windows.
• Datos > Actualizar todo (Ctrl+Alt+F5) actualiza todas las tablas dinámicas del libro.
• Para actualizar cada vez que se abre el archivo, haz clic derecho en la tabla dinámica, elige Opciones de tabla dinámica y, en la pestaña Datos, marca Actualizar al abrir el archivo.

Las filas nuevas añadidas bajo el rango de origen no se incluyen, ni siquiera después de actualizar. Cambia el rango en Analizar tabla dinámica > Cambiar origen de datos, o, mejor, convierte el origen en una tabla antes de crear la tabla dinámica: selecciona los datos y usa Insertar > Tabla (o Ctrl+T). Una tabla crece cuando añades filas, y la tabla dinámica las recoge en la siguiente actualización.

Sumar todas las regiones con una sola fórmula

FórmulasRestablecer

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Tu turno: En F2, suma las ventas de cada región listada en E2:E4 con una sola fórmula.

ComprobarVer respuesta

La respuesta derrama 455, 305 y 170. Darle a SUMAR.SI toda la lista E2:E4 como criterio devuelve un total por región, así que no hay nada que copiar hacia abajo. Excel 2019 y anteriores no tienen UNICOS ni desbordamiento: escribe las regiones en E2:E4 y copia hacia abajo =SUMAR.SI($A$2:$A$9;E2;$C$2:$C$9). Sin los signos $ los rangos bajan con cada fila y los totales salen mal.

AGRUPARPOR y PIVOTARPOR: una tabla dinámica en una fórmula

Excel para Microsoft 365 tiene dos funciones que construyen todo un resumen con una sola fórmula y se recalculan como cualquier fórmula, sin actualizar. Necesitan una suscripción actual a Microsoft 365. Para los datos de arriba (en su forma inglesa):

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

En Excel en español: =AGRUPARPOR(A2:A9;C2:C9;SUMA) y =PIVOTARPOR(A2:A9;B2:B9;C2:C9;SUMA).

AGRUPARPOR (GROUPBY en inglés) recibe el campo de filas, los valores y la función (SUMA, CONTARA, PROMEDIO, MAX, PERCENTOF). PIVOTARPOR (PIVOTBY en inglés) añade un campo de columnas entre ellos. Las dos ordenan los rótulos y añaden filas de total, como una tabla dinámica.

Tabla dinámica o fórmulas: cuál usar

Usa una tabla dinámica para explorar datos y responder una pregunta una vez; usa fórmulas para un resumen que vive en un informe, alimenta otras fórmulas y tiene que estar siempre al día. Para comprobar los números de una tabla dinámica, reconstruye una de sus celdas con SUMAR.SI.CONJUNTO: si no coinciden, la tabla dinámica suele necesitar una actualización o su rango de origen se quedó corto.