Tablas en Excel: 5 problemas, 5 soluciones

Tablas en Excel: 5 problemas, 5 soluciones

Los 5 problemas que más veo al trabajar con tablas en Excel y cómo resolverlos: arrastras una fórmula y salen ceros, el acumulado deja de cuadrar al insertar una fila, la numeración se desordena al ordenar o filtrar, Power Query no encuentra «Tabla1» y un SCAN que dentro de la tabla da #¡DESBORDAMIENTO!.

📥 Descarga el archivo: lleva el libro para practicar (la tabla tVentas con 120 pedidos de Voltabici) y el libro con todas las soluciones.

Las 5 soluciones

1. Arrastrar sin que se mueva la columna: tVentas[[Provincia]:[Provincia]]. El doble corchete fija la columna aunque arrastres la fórmula a la derecha.
2. Un acumulado sin $ que aguanta filas nuevas: =SUMA(INDICE([Importe];1):[@Importe]). INDICE([Importe];1) es siempre la primera celda de la columna, insertes lo que insertes.
3. Una numeración que no se rompe al ordenar: =FILA([@Provincia])-FILA(tVentas[#Encabezados]). Y si además filtras: =SUBTOTALES(103;INDICE([Provincia];1):[@Provincia]), que solo cuenta las filas visibles.
4. Ponle nombre a la tabla al crearla (Ctrl+T ya lo pide). Las fórmulas se actualizan solas si la renombras; Power Query no, y la consulta deja de encontrarla.
5. Lo que desborda, fuera de la tabla. Un SCAN dentro de una tabla da #¡DESBORDAMIENTO!. Calcúlalo fuera, empezando en la misma fila que la tabla, y en la columna de la tabla usa la intersección implícita: =@$M$5#.

Las tablas de Excel (las que creas con Ctrl+T) no pasan de moda: crecen solas, sus fórmulas se rellenan en toda la columna y las referencias se leen por nombre en lugar de por dirección. Pero se comportan distinto que un rango normal, y eso provoca errores que desconciertan: una fórmula que al arrastrarla da ceros, un acumulado que deja de cuadrar, una consulta de Power Query que de repente no encuentra la tabla.

En el vídeo repaso los cinco problemas que más me encuentro con las tablas y cómo resolver cada uno. Todo se practica con la tabla tVentas, 120 pedidos de una tienda de bicis eléctricas, que va en el fichero de descarga junto con el libro resuelto.

1. Arrastras la fórmula y salen ceros

En una hoja de resumen quieres la cantidad, las ventas y el importe de cada provincia. Escribes la primera con SUMAR.SI.CONJUNTO, da bien, y al arrastrarla a la derecha para las otras dos columnas aparecen ceros.

La razón: al arrastrar, Excel desplaza todas las columnas de la tabla a las que apunta la fórmula, también la de la condición. La columna de la provincia se convierte en otra y ya no encuentra nada. Copiar y pegar tampoco sirve: deja todas las referencias quietas y las tres columnas repiten la cantidad.

La solución es poco intuitiva pero muy útil: escribir la columna de la condición como un rango de una sola columna, con doble corchete.

=SUMAR.SI.CONJUNTO(tVentas[Cantidad];tVentas[[Provincia]:[Provincia]];$A5)

tVentas[[Provincia]:[Provincia]] queda fija al arrastrar, mientras que tVentas[Cantidad] avanza a Ventas e Importe. Es la forma de "poner dólares" a una columna de tabla.

2. El acumulado se rompe al insertar una fila

El acumulado típico es =SUMA($H$5:[@Importe]): desde la primera celda fija hasta el importe de la fila. Funciona hasta que alguien inserta una fila arriba. Entonces la referencia fija se desplaza a $H$6 en el resto de filas y el acumulado pierde el primer importe sin avisar.

En lugar de una dirección, pide la primera celda de la columna con INDICE:

=SUMA(INDICE([Importe];1):[@Importe])

INDICE([Importe];1) es siempre la primera celda de la columna Importe, insertes las filas que insertes. Sin un solo dólar, y a prueba de compañeros que no están acostumbrados a trabajar con tablas.

3. La numeración se desordena al ordenar o filtrar

Si numeras las filas a mano (1, 2, 3…), el número es un valor pegado a la fila: en cuanto ordenas por otra columna, viaja con ella y la secuencia se desordena. Mejor calcularlo:

=FILA([@Provincia])-FILA(tVentas[#Encabezados])

La fila actual menos la fila de los encabezados da 1, 2, 3… y se recalcula al ordenar. Si además filtras, esa fórmula enseña los números originales de las filas visibles, no una secuencia. Para que cuente solo lo que se ve, usa SUBTOTALES con el código 103 (contar valores, ignorando las filas ocultas):

=SUBTOTALES(103;INDICE([Provincia];1):[@Provincia])

Filtras por Málaga y la numeración vuelve a ser 1, 2, 3…

4. Power Query no encuentra «Tabla2»

Si cargas en Power Query una tabla que se llama Tabla2 y más tarde la renombras como Objetivos, al actualizar salta el error: no encuentra la tabla. Las fórmulas del libro se actualizan solas al renombrar una tabla; Power Query no, porque el nombre está escrito dentro de la consulta y hay que cambiarlo a mano.

La prevención es sencilla: ponle nombre a la tabla al crearla. Las versiones recientes de Excel ya lo piden en el propio cuadro de Ctrl+T, así que no hay excusa para dejar Tabla1, Tabla2, Tabla3.

5. Una fórmula que desborda dentro de la tabla

Es la duda que más me han pedido. Un SCAN que recorre la columna de importes y va sumando uno si sube y restando uno si baja devuelve una matriz entera. Dentro de una tabla da #¡DESBORDAMIENTO!: la tabla calcula la fórmula en cada fila y en cada fila recibe muchos resultados.

La solución tiene dos pasos:

  1. Haz el cálculo fuera de la tabla, empezando en la misma fila que la primera fila de datos (por ejemplo, en M5).
  2. En la columna de la tabla, refiérete a ese resultado desbordado con la arroba delante:
=@$M$5#

Sin la arroba sigue dando desbordamiento. Con ella entra en juego la intersección implícita: de todo el rango desbordado, Excel devuelve solo el valor que está en la misma fila. Un valor por fila, que es justo lo que la tabla necesita.

¿Las tablas admiten filas por debajo de la fila de totales? Verdadero o falso

Verdadero para la tabla: la fila de totales es siempre su última fila, y las filas nuevas entran por encima de ella. Si insertas una fila dentro de la tabla, aparece encima de los totales y los empuja hacia abajo (junto con lo que haya debajo en la hoja). Si redimensionas la tabla hacia abajo, los totales se mueven a la nueva última fila.

Lo que sí puedes es escribir en la hoja debajo de los totales, pero esos datos quedan fuera de la tabla: escribir justo debajo de la fila de totales no añade una fila a la tabla. Para añadir una fila con los totales activados:

  • Inserta una fila dentro de la tabla (botón derecho sobre una fila de datos → Insertar), o
  • Colócate en la última celda de datos (la de la fila anterior a los totales) y pulsa Tab: Excel abre una fila nueva encima de los totales.

Funciones y referencias clave

  • tVentas[[Columna]:[Columna]]: referencia a una columna que no se desplaza al arrastrar.
  • INDICE([Columna];1): la primera celda de una columna de tabla, estable aunque se inserten filas.
  • FILA y tVentas[#Encabezados]: numeración que se recalcula al ordenar.
  • SUBTOTALES(103;…): cuenta solo las filas visibles al filtrar.
  • SCAN: calcula un valor acumulado fila a fila y devuelve una matriz.
  • @ y el operador #: intersección implícita sobre un rango desbordado.

Conclusión

Ninguno de estos problemas es un fallo de Excel: son las reglas de las tablas, que no son las de los rangos. Doble corchete para fijar columnas, INDICE en vez de dólares, numeración calculada, nombre de la tabla desde el primer momento y lo que desborda, fuera. Descarga el fichero, rompe cada ejemplo a propósito y comprueba la solución: es la forma más rápida de que se quede. Y si te encuentras un sexto problema, tráelo al grupo de la comunidad de InflueXcel: ahí salen muchas de las soluciones que acaban en vídeo.

Más casos con estas funciones

Más contenido de Excel en InflueXcel