Qué significa el desbordamiento

La mayoría de las fórmulas que ha escrito devuelven un solo valor en una sola celda. Las matrices dinámicas cambian esto. Una sola fórmula puede devolver una lista completa o una cuadrícula de resultados, y la hoja de cálculo coloca automáticamente cada resultado en su propia celda.

Esta colocación automática se denomina desbordamiento. Escriba la fórmula una vez, pulse Intro y los resultados se extenderán por las celdas cercanas, como el agua que llena una bandeja.

Suponga que A1:A5 contiene los números del 1 al 5. En la celda C1, escriba =A1:A5*10 y pulse Intro.

En lugar de una sola respuesta, obtiene cinco: 10, 20, 30, 40 y 50, apiladas en C1:C5. Solo ha escrito la fórmula en C1, pero el resultado se desbordó automáticamente en las celdas inferiores.

El bloque de celdas que rellena una fórmula se denomina rango de desbordamiento. Solo la celda superior izquierda, llamada ancla, contiene realmente la fórmula. Las demás celdas se rellenan automáticamente y muestran un borde azul tenue cuando selecciona el ancla.

No puede editar directamente las celdas desbordadas. Si lo intenta, obtiene un error. Para cambiar el resultado, edite la fórmula en la celda ancla.

Como un desbordamiento puede crecer o reducirse, puede hacer referencia al resultado completo mediante el operador de desbordamiento: un signo de almohadilla después de la dirección del ancla.

Si una fórmula en C1 se desborda hasta C5, entonces C1# representa todo el rango desbordado. Úselo en otra ubicación, por ejemplo =SUM(C1#), y el total siempre incluirá todos los valores desbordados, aunque el desbordamiento crezca más adelante.

La verdadera ventaja del desbordamiento es que es dinámico. Si añade un número nuevo al rango de origen, el rango de desbordamiento crece automáticamente para incluirlo. Si elimina un valor, el desbordamiento se reduce.

Ya no tendrá que volver a copiar una fórmula hacia abajo. La única fórmula de la celda ancla ajusta el tamaño de su resultado para adaptarse a los datos que lee.

Muchas funciones modernas están diseñadas para devolver matrices y, por tanto, se desbordan:

• FILTER devuelve las filas que coinciden.
• UNIQUE devuelve una lista sin duplicados.
• SORT y SORTBY reordenan un rango.
• SEQUENCE genera una lista de números.

En este curso conocerá FILTER y UNIQUE. Cada una produce muchos resultados a partir de una sola fórmula.

Imagine que la columna A contiene regiones: Este, Oeste, Este, Norte y Oeste. Para obtener una lista limpia de regiones sin duplicados, escriba una sola fórmula:

=UNIQUE(A2:A6)

Se desbordan tres resultados: Este, Oeste y Norte. Añada otra región al origen y la lista desbordada crecerá por sí sola. Una fórmula y una lista siempre actualizada.

Los desbordamientos no se limitan a extenderse hacia abajo. Una fórmula puede desbordarse por las columnas o incluso rellenar una cuadrícula bidimensional.

Por ejemplo, =SEQUENCE(3,4) desborda un bloque de 3 filas de alto y 4 columnas de ancho, y rellena 12 celdas con los números del 1 al 12. La dirección y la forma dependen por completo de la matriz que produce la fórmula.

Las matrices dinámicas y el desbordamiento funcionan en Excel for Microsoft 365 y Excel 2021 and later. Google Sheets también admite el desbordamiento de matrices desde hace años.

Las versiones anteriores, como Excel 2016 y 2019, no admiten el desbordamiento. En ellas, las fórmulas de matriz debían confirmarse con Ctrl+Shift+Enter y no podían crecer automáticamente. Si sus fórmulas no se desbordan, compruebe primero su versión.

Antes de las matrices dinámicas, debía escribir =B2*C2 en una celda y arrastrarla hacia abajo cientos de filas. Con el desbordamiento, la escribe una sola vez.

La fórmula =B2:B100*C2:C100 desborda un resultado por fila en todo el rango. El mantenimiento se vuelve trivial: hay una sola fórmula que leer y corregir, no cientos de copias que podrían dejar de estar sincronizadas.

Considere una fórmula con desbordamiento como un pequeño programa que produce una forma de datos, no solo un número. A continuación, la hoja de cálculo coloca esa forma en la cuadrícula a partir de la celda ancla.

Su tarea es asegurarse de que haya espacio vacío suficiente para colocarla. En las próximas lecciones verá cómo producir formas útiles con FILTER y UNIQUE, y qué hacer cuando el espacio de destino está bloqueado.

Ha aprendido que una fórmula de matriz dinámica puede devolver muchos resultados y que la hoja de cálculo los distribuye automáticamente en un rango de desbordamiento.

• Solo la celda ancla contiene la fórmula.
• Haga referencia a todo el desbordamiento con el operador #, como en C1#.
• Los desbordamientos crecen y se reducen automáticamente con los datos de origen.
• Funciones como FILTER, UNIQUE, SORT y SEQUENCE se desbordan.

A continuación, usará FILTER para devolver solo las filas que necesita.