Calderhay Wholesale reclama un rápel por volumen a nueve proveedores. Durante tres trimestres lo reclamó a ocho. Nada en la hoja estaba mal, nada salía en rojo, y 24.587,50 € no se pidieron nunca.
La hoja de reclamación son dos columnas. La columna H derrama la lista de proveedores que sale del libro mayor de compras, y la columna I suma al lado las compras netas de cada uno.
La columna H es una fórmula que escribe tantas filas como necesite. La columna I eran ocho fórmulas, escritas una sola vez. En enero se añadió un noveno proveedor, la lista creció una fila y la columna de al lado no.
| Rápel del 3T 2026 | Importe |
|---|---|
| Ganado sobre las compras | 67.257,75 € |
| Reclamado a los proveedores | 58.724,75 € |
| Diferencia | 8.533,00 € |
| Trimestres afectados | 3 |
| Aún dentro del plazo | 8.533,00 € |
Alcance. Rangos de desbordamiento, el operador
#,#¡DESBORDAMIENTO!, la@de la intersección implícita yUNICOS,ORDENAR,FILTRARySUMAR.SI.CONJUNTOen Microsoft 365 y Excel 2021 y posteriores.Excel 2019 y anteriores no tienen matrices dinámicas. La sección 11 cuenta qué hacen esas versiones con un archivo escrito así.
En todo el artículo,
Lineases la tabla del libro mayor de compras, la columna H guarda la lista de proveedores y la columna I los totales de al lado.
01Un Proveedor se Cayó por Debajo de la Reclamación
Calderhay compra a una lista corta de proveedores y reclama a cada uno un rápel por volumen al cerrar el trimestre. Los porcentajes están en los contratos de suministro; las compras están en una tabla llamada Lineas.
La hoja de reclamación se montó en 2023 con ocho proveedores. H2 tenía =ORDENAR(UNICOS(Lineas[Proveedor])), que derramaba ocho nombres hasta H9, e I2 tenía =SUMAR.SI.CONJUNTO(Lineas[Neto];Lineas[Proveedor];H2), arrastrada hasta I9.
En enero de 2026 el equipo de compras abrió una cuenta con Calderbrook Organics. La lista la ordenó en tercer lugar, todos los nombres de debajo bajaron una fila, y la lista pasó a terminar en H10.
La columna I seguía acabando en I9. Cada una de sus ocho fórmulas releyó el nombre que había entrado en su fila, así que los ocho totales eran correctos: los ocho primeros de una lista de nueve.
Harlings Dry Goods es el noveno nombre, y ninguna fórmula apunta a él. Llevaba nueve años suministrando a Calderhay, con el mejor porcentaje de la casa, y se le reclamó cada trimestre hasta el trimestre en que se dio de alta a otro.
Nueve Proveedores, Ocho Totales y un Rápel que Nadie Pidió
La reclamación trimestral de rápel de Calderhay Wholesale, en A1:F13. La columna B es la celda de la hoja de reclamación que contiene el nombre de cada proveedor, escrita por una sola fórmula en H2 que se derrama tan abajo como haga falta. La columna D es el rápel que ganaron las compras de cada proveedor según su contrato de suministro; la columna E es lo que realmente se reclamó. La columna de totales que hay al lado de la lista acaba en I9, así que ninguna fórmula apunta al noveno nombre, y las tres filas finales son el trimestre, los dos trimestres anteriores y la parte que sigue dentro del plazo de noventa días.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
02Una Lista Derramada Es una Fórmula, No Nueve Celdas
=ORDENAR(UNICOS(Lineas[Proveedor])) vive en una sola celda. Todo lo que hay debajo es resultado: Excel escribe la respuesta en tantas celdas como necesite, y esas celdas no tienen fórmula propia.
Haz clic en H5 y la barra de fórmulas muestra la fórmula en gris, con H2 como única celda editable. Borra H2 y desaparece la lista entera. Ese bloque es el rango de desbordamiento, y su tamaño lo decide el dato.
Una columna de fórmulas arrastradas es lo contrario. Cada celda es su propia fórmula, cada una la puso una persona, y la columna mide exactamente lo que medía el día que alguien la arrastró.
El fallo es tener esas dos cosas una al lado de la otra. Una crece sola y la otra necesita a alguien, así que coinciden solo hasta que cambian los datos.
Escenario: Haz clic en una celda que esté por debajo del inicio de una lista derramada en tu libro. Si la barra de fórmulas sale en gris, estás dentro de un derrame; escribe =FILAS(H2#) en una celda libre y mira cuántas filas escribe hoy.
03H2# Es el Rango que Crece con Ella
Excel tiene una referencia para todo el derrame: la celda superior con una # detrás. H2# son todas las filas que escribe ahora mismo la fórmula de H2: nueve hoy, diez mañana, sin que nadie edite nada.
Así que la columna de totales también debería haber sido una sola fórmula:
=SUMAR.SI.CONJUNTO(Lineas[Neto];Lineas[Proveedor];H2#)
Esa derrama su propia respuesta desde I2, un total por nombre, haya los nombres que haya. El total de la reclamación pasa a ser =SUMA(I2#), que lee lo que la columna haya escrito.
| Escrito así | Filas que cubre | Qué pasa con un noveno proveedor |
|---|---|---|
SUMAR.SI.CONJUNTO arrastrada por I2:I9 | Ocho, para siempre | Ocho totales y nueve nombres |
SUMAR.SI.CONJUNTO con H2# como criterio | Las que escriba H2 | Nueve totales |
=SUMA(I2:I9) | Ocho, para siempre | Se deja fuera el noveno |
=SUMA(I2#) | Todo el derrame | Lo incluye |
Escenario: Pon =FILAS(H2#) al lado de =FILAS(I2:I9) en tu propia hoja. Cada vez que esos dos números discrepen, algo del lado derecho se está quedando fuera de un total.
04Qué Significa #¡DESBORDAMIENTO! y Cómo Quitarlo
#¡DESBORDAMIENTO! es el problema contrario, y es el ruidoso. La fórmula quiere escribir en celdas que no están vacías, así que en lugar de pisarlas no escribe nada y devuelve un error.
Pulsa el triángulo de aviso y Excel nombra el motivo. El rango de desbordamiento no está en blanco es el habitual, con Seleccionar celdas obstructivas debajo, que lleva el cursor a lo que estorba: casi siempre un espacio suelto o un encabezado viejo.
Los otros cuatro conviene conocerlos por su nombre, porque cada uno se arregla de otra forma.
| Lo que dice el triángulo | Qué pasa en realidad |
|---|---|
| El rango de desbordamiento no está en blanco | Hay un valor, un espacio o una nota en la salida |
| El rango contiene celdas combinadas | Una matriz dinámica no escribe en una celda combinada |
| El rango de desbordamiento es desconocido | El tamaño depende de algo volátil, como ALEATORIO.ENTRE |
| El rango de desbordamiento es demasiado grande | El resultado se sale de la última fila o columna |
| Rango de desbordamiento en tabla | La fórmula está dentro de una tabla, que no admite derrames |
Escenario: Escribe cualquier cosa en la celda justo debajo de una lista derramada y mira cómo la lista entera se convierte en #¡DESBORDAMIENTO!. Pulsa Ctrl+Z y cuenta las filas libres que tienes de verdad por debajo.
05La @ que Escribe Excel para Evitar el Derrame
A veces Excel impide que una fórmula derrame en lugar de dar #¡DESBORDAMIENTO!, escribiendo una @ delante. =@ORDENAR(UNICOS(Lineas[Proveedor])) devuelve un nombre en vez de una lista, y lo devuelve sin ningún error.
Esa @ es la intersección implícita: coge el único valor que está en la misma fila que la fórmula y tira el resto. Es como leía un rango cualquier versión anterior a 2019 cuando el argumento esperaba un solo valor.
Aparece al abrir en 365 un libro escrito en una versión antigua, al pegar una fórmula dentro de una tabla, y cada vez que alguien copia una fórmula de una hoja hecha en una versión anterior.
La respuesta peligrosa es un número. #¡DESBORDAMIENTO! frena la reclamación; la @ suma una fila, deja el resto de la lista sin pedir, y nadie revisa una celda que tiene un número.
Escenario: Pulsa Ctrl+B, pon Buscar dentro de en Fórmulas y busca @ en tu libro. Borra una y comprueba si esa celda estaba contestando con una lista o con una sola fila de ella.
06Los Derrames No Funcionan Dentro de una Tabla
Una tabla de Excel gestiona sus propias filas: inserta una y todas las columnas calculadas se rellenan solas. Una matriz dinámica también gestiona sus filas, y las dos no pueden mandar sobre las mismas celdas.
Por eso una fórmula que derrama, escrita en una columna de tabla, devuelve #¡DESBORDAMIENTO! con Rango de desbordamiento en tabla como motivo. Excel suele ofrecer la @, que la convierte en una respuesta de una fila y sin error.
La hoja de reclamación no es una tabla, y solo por eso funciona H2. El libro mayor que lee sí lo es, y ese es el orden correcto: tablas para los datos, celdas normales para todo lo que derrame.
Volver atrás. Diseño de tabla → Convertir en rango deja la tabla como celdas corrientes, y las referencias estructuradas que apuntaban a ella pasan a ser rangos normales.
Hazlo con la hoja que contiene el derrame, nunca con la que contiene los datos. Que el libro mayor sea una tabla es lo que permite que
Lineas[Proveedor]crezca al añadir una fila.
Escenario: Escribe =UNICOS(A2:A20) en una columna libre de cualquier tabla de tu libro y lee el error. Después escribe la misma fórmula fuera de la tabla y mira cómo se derrama.
07Nunca Leas un Derrame por Número de Fila
Una lista ordenada no tiene direcciones fijas. H4 fue Carrowdale Bakery durante tres años y pasó a ser Calderbrook Organics en enero, sin que nadie cambiara nada en el libro.
Cualquier fórmula que apunte a un derrame con un número de fila es una apuesta a que la ordenación no se moverá. =H4 e =INDICE(H2#;4) son dos formas de hacer esa apuesta, y las dos se pierden en silencio.
Léelo por el nombre:
=BUSCARX("Harlings Dry Goods";H2#;I2#)
Eso sigue al proveedor allá donde lo coloque la ordenación, y devuelve #N/A si el proveedor deja de aparecer en el libro mayor, que es una pregunta que alguien debería recibir.
Escenario: Cuenta las celdas de tu libro que apuntan a un bloque derramado con un número de fila. Sustituye una por un BUSCARX contra la columna de nombres y comprueba que la cifra no se mueve hoy.
08Deja Sitio Debajo de Todo lo que Derrame
Un derrame necesita su sitio antes de necesitarlo. Una lista de nueve filas tiene que poder ser de diez en la misma hoja, y eso significa que la celda bajo H10 tiene que estar vacía el día que ocurra.
Así que debajo de un bloque derramado no va nada. Ni un total, ni una nota, ni una segunda tabla. El total de la reclamación va encima de la lista o en una columna propia, donde el crecimiento no lo alcance.
Un total general debajo de una lista dinámica es el #¡DESBORDAMIENTO! más común de un libro de finanzas, porque debajo de la columna es donde ha ido siempre un total.
| Dónde va el total | Qué pasa cuando crece la lista |
|---|---|
| Justo debajo de la lista | La lista entera pasa a #¡DESBORDAMIENTO! |
| Dos filas por debajo | Bien hasta que la lista crece dos filas |
| Encima de la lista, en H1 | Nunca estorba |
Una columna más allá, =SUMA(I2#) | Nunca estorba, y lee todo el derrame |
Escenario: Mueve un total general de debajo de una lista derramada a la fila de encima y cambia su fórmula para que lea el derrame con #. La cifra no debería moverse; el riesgo sí.
09Tres Comprobaciones que Detectan una Columna Corta
La reclamación salió ocho veces y cuadró ocho veces, porque todas sus cifras eran correctas. Lo que faltaba era una fila, y ningún total puede informar de una fila que nunca se le sumó.
Cuenta las filas de los dos lados. =FILAS(H2#)-CONTAR(I2:I9) da cero mientras coinciden y distinto de cero en cuanto dejan de hacerlo. Ponlo al lado de la reclamación, donde alguien lo vea.
Concilia con el origen. =SUMA(Lineas[Neto])-SUMA(I2#) debería dar cero cada trimestre. Cada euro del libro mayor pertenece a un proveedor y solo a uno, así que son dos caminos al mismo número.
Cuenta los nombres. =CONTARA(UNICOS(Lineas[Proveedor])) lee el libro mayor y no la hoja de reclamación, así que un proveedor que aparece por primera vez lo cambia la misma semana.
Escenario: Pon la conciliación en una celda al lado de tu resumen y dale un formato que ponga en rojo cualquier resultado distinto de cero. Es una fórmula, y es la que habría detectado esto en enero.
Pruébalo en la cuadrícula10Cuándo un Rango Fijo Es la Respuesta Correcta
Un derrame no siempre es lo que quieres. Un impreso de reclamación con ocho líneas numeradas, un informe pensado para caber en una página, una plantilla en la que alguien escribe: todos necesitan forma fija, y un bloque que crece los rompería.
La pregunta es qué lado decide el tamaño. Si lo deciden los datos, usa un derrame y léelo con #. Si lo decide el diseño, usa celdas fijas y comprueba que los datos siguen cabiendo dentro.
Lo que no puede pasar es tener una de cada. Una lista que crece sola, leída por una columna que no, es el fallo del que trata todo este artículo, y mientras lo comete devuelve un número de aspecto razonable.
Escenario: Elige un resumen de tu libro y decide en voz alta cuál de los dos es. Después haz que sus dos columnas respeten esa decisión y pon =FILAS(H2#) en algún sitio para demostrarlo.
11Excel Antiguo, la Web y los Archivos Compartidos
Los rangos de desbordamiento piden Microsoft 365 o Excel 2021. Abre esta hoja en Excel 2019 y UNICOS, ORDENAR y el operador # son nombres desconocidos, así que la lista es una columna de #¿NOMBRE? y cada total de al lado es cero.
Excel para la web derrama con normalidad y sirve para ver lo que ve un compañero con 365. Google Sheets tiene su propio comportamiento de matrices y su propia lista de funciones, así que pasar por ahí no es una ida y vuelta.
Un libro compartido convierte el crecimiento en cosa de otro. Alguien añade una fila al libro mayor, la lista crece en la pantalla de todos, y quien lleva la hoja de reclamación no es quien la ha dejado corta.
Escenario: Abre el libro en Excel para la web y lee el número de filas del derrame. Si no coincide con el escritorio de un compañero, la diferencia está en su versión y no en los datos.
Pruébalo en la cuadrícula12Nueve Trampas
- Una lista derramada al lado de una columna arrastrada. Un lado crece solo y el otro no, y el hueco entre los dos es silencioso.
=SUMA(I2:I9)sobre un bloque dinámico. El total acierta con las filas que le dieron y no dice absolutamente nada del resto.- Un total general justo debajo de un derrame. La lista no crece una fila: se convierte en
#¡DESBORDAMIENTO!y desaparece entera. - Leer una lista ordenada por número de fila.
H4es el proveedor que hoy quede cuarto, y ordenar es justo para lo que está la fórmula. - Tomar la
@por un signo de puntuación. Es una llamada que tira todo menos el valor que está en su propia fila. - Escribir una fórmula que derrama dentro de una tabla. Las tablas y las matrices dinámicas mandan sobre sus filas, así que sobra una de las dos.
- Celdas combinadas en cualquier punto de la salida. Un derrame no escribe en ellas de ninguna manera, pase lo que pase en la hoja.
- Dar por hecho que
#¡DESBORDAMIENTO!es el único fallo. El error es la versión de este fallo que se anuncia sola. - Mandar el libro a un Excel 2019. Ni derrames, ni
#, y una columna de #¿NOMBRE? donde estaban los proveedores.
13Cinco Ejercicios
- Reprodúcelo. Derrama ocho nombres con
=ORDENAR(UNICOS(A2:A40)), arrastra unSUMAR.SI.CONJUNTOocho filas a su lado y añade un noveno nombre que se ordene por el medio. Lee el total. - Arréglalo con
#. Sustituye las ocho fórmulas por un soloSUMAR.SI.CONJUNTOconH2#de criterio, añade otra vez el noveno nombre y mira crecer la columna. - Rómpelo a propósito. Escribe un espacio en la celda de debajo de la lista, lee el motivo del triángulo y usa Seleccionar celdas obstructivas para encontrarlo.
- Conoce la
@. Escribe=@ORDENAR(UNICOS(A2:A40))y compara lo que devuelve con la misma fórmula sin la@delante. - Concílialo. Escribe
=SUMA(Lineas[Neto])-SUMA(I2#)al lado de tu resumen y confirma que da cero antes y después de cada cambio anterior.
Qué Llevarse
Todas las cifras de todas aquellas reclamaciones eran correctas. Ocho proveedores, ocho totales, ocho rápeles, y cada uno coincidía con el libro mayor al céntimo.
La hoja tenía nueve proveedores. Una fórmula había crecido para decirlo y la columna de al lado no, y 24.587,50 € de rápel se quedaron sin reclamar en tres trimestres.
Un rango de desbordamiento decide su propio tamaño. A todo lo que lo lea hay que decirle que haga lo mismo, con # y no con un número de fila, y todo lo que esté debajo tiene que moverse.
8.533,00 € de los 24.587,50 € siguen dentro del plazo de noventa días. El resto caducó en silencio, en un libro que no mostró ni un solo error.