Es posible que haya introducido una fórmula y haya visto que llena automáticamente varias celdas hacia abajo o hacia la derecha. Este comportamiento se debe al motor de matrices dinámicas de Excel. Una fórmula de derrame calcula un resultado que es demasiado grande para una sola celda. Este artículo explica cómo funcionan las fórmulas de derrame y cómo usarlas de manera efectiva.
Puntos clave: conceptos básicos de las fórmulas de derrame de Excel
- El rango de derrame: el grupo de celdas que se llenan automáticamente con los resultados de una sola fórmula.
- El error #SPILL!: aparece cuando algo bloquea la escritura del rango de derrame calculado.
- Funciones de matrices dinámicas: funciones como FILTER, SORT y UNIQUE están diseñadas para devolver resultados de derrame.
¿Qué son las fórmulas de derrame y las matrices dinámicas?
Las fórmulas de derrame son una característica central de las matrices dinámicas de Excel. Cuando una fórmula devuelve varios valores, derrama esos resultados en las celdas vecinas. La celda original contiene la fórmula principal, y las celdas llenadas son el rango de derrame. Este comportamiento es automático y no requiere una configuración especial como Ctrl+Mayús+Entrar, que se usaba en versiones anteriores.
La característica funciona con un conjunto de nuevos motores de cálculo. Cualquier función que pueda devolver una matriz de valores puede potencialmente derramar resultados. Esto incluye funciones nuevas como SEQUENCE y XLOOKUP, así como funciones tradicionales usadas de nuevas maneras. El rango de derrame es dinámico y se expandirá o contraerá según el resultado de la fórmula.
Requisitos previos para usar fórmulas de derrame
Necesita una versión compatible de Excel. Las matrices dinámicas están disponibles en Microsoft 365 y Excel 2021 para Windows y Mac. También están en Excel para la web. La característica no está disponible en Excel 2019 ni en versiones perpetuas anteriores. El libro debe guardarse en formato .xlsx para conservar el comportamiento de derrame.
Pasos para crear y administrar fórmulas de derrame
Puede crear una fórmula de derrame usando cualquier función que devuelva varios resultados. Los siguientes pasos muestran un ejemplo común.
- Introduzca una función de matriz dinámica
Seleccione una sola celda, como A1. Escriba =SEQUENCE(5) y presione Entrar. La fórmula genera los números del 1 al 5, derramándolos hacia abajo desde la celda A1 en A2:A5. - Haga referencia a todo el rango de derrame
Para usar los resultados en otro lugar, haga referencia a la celda superior izquierda del rango de derrame con el símbolo de número (#). En la celda C1, escriba =SUM(A1#). El # indica a Excel que sume todo el rango de derrame desde A1, que es A1:A5. - Edite o borre la fórmula de derrame
Solo puede editar la fórmula en la celda original con el borde azul. Haga clic en esa celda, cambie la fórmula en la barra de fórmulas y presione Entrar. El rango de derrame se actualiza automáticamente. Para eliminar los resultados, elimine la fórmula solo de la celda original.
Usar el derrame con fórmulas tradicionales
También puede crear el comportamiento de derrame sin funciones nuevas. Por ejemplo, seleccione la celda D1 y escriba =A1:A5*2. Esta fórmula multiplica por 2 cada valor del rango A1:A5. Como la operación se realiza en una matriz, derrama cinco resultados en D1:D5. Esto reemplaza la necesidad de las fórmulas matriciales anteriores.
Errores comunes y solución de errores de derrame
Excel muestra un error #SPILL!
Este es el problema más común. El error significa que algo está bloqueando el rango de derrame previsto. Haga clic en el icono de error junto a la celda para ver los detalles. La causa habitual es una celda no vacía dentro del área de derrame, como texto, un número o una fórmula. Borre la celda que obstruye para resolver el error.
El rango de derrame es demasiado grande
Si una fórmula intenta derramar más allá del borde de la hoja de cálculo, obtiene un error #SPILL!. Por ejemplo, =SEQUENCE(1000) en la celda A1 de una hoja con solo 500 filas debajo fallará. Mueva la fórmula a una ubicación con suficientes filas y columnas vacías para acomodar todos los resultados.
El rango de derrame hace referencia a una tabla
Las tablas de Excel tienen referencias estructuradas que no admiten rangos de derrame dinámicos. Si su fórmula hace referencia a una columna de tabla y derrama, puede convertirse en un rango estático. Para obtener mejores resultados, convierta primero la tabla en un rango normal usando Diseño de tabla > Convertir en rango.
Fórmulas de derrame frente a fórmulas matriciales heredadas
| Elemento | Fórmulas de derrame (matrices dinámicas) | Fórmulas matriciales CSE heredadas |
|---|---|---|
| Método de entrada | Presione Entrar normalmente | Requería Ctrl+Mayús+Entrar |
| Ubicación del resultado | Se derrama en varias celdas automáticamente | Limitado a un rango de varias celdas preseleccionado |
| Editabilidad | Edite solo la celda de la fórmula principal | Debe editar todo el rango de matriz como una sola unidad |
| Indicación de error | Error #SPILL! con diagnósticos auxiliares | Errores de fórmula generales sin orientación específica |
| Compatibilidad de funciones | Todas las funciones nuevas de matrices dinámicas y muchas anteriores | Limitado a las funciones que admitían la entrada CSE |
Las fórmulas de derrame se actualizan automáticamente cuando cambian los datos de origen. No puede editar celdas individuales dentro de un rango de derrame. Para un uso avanzado, combine la referencia de rango derramado A1# con funciones como INDEX para extraer valores específicos. Pruebe usar la función UNIQUE en una lista para ver cómo las fórmulas de derrame eliminan duplicados de forma dinámica.