En este tutorial te voy a mostrar cómo evitar que tus fórmulas tipo arreglo (ARRAYFORMULA) se dañen al aplicar filtros o al insertar nuevas filas en Google Sheets. Esto es especialmente útil si trabajas con datos dinámicos y quieres mantener tus cálculos intactos sin importar cómo se modifiquen las filas. Además, aprenderás a integrar los encabezados dentro de la fórmula, usar condicionales para evitar errores por celdas vacías y estructurar correctamente tus fórmulas para que sean más flexibles y resistentes a cambios inesperados.
Si prefieres ver el tutorial en video, te lo dejo a continuación:
Para seguir este tutorial, solo necesitas una hoja de cálculo de Google Sheets. Vamos a trabajar con una tabla sencilla de tres columnas: dos columnas con números y una columna para mostrar la suma de cada fila. Este ejemplo es perfecto para entender cómo funcionan las fórmulas tipo arreglo y cómo protegerlas.
| Número 1 | Número 2 | Suma |
|---|---|---|
| 5 | 3 | |
| 2 | 4 | |
| 7 | 1 |
¿Qué es una fórmula tipo arreglo (ARRAYFORMULA)?
Una fórmula tipo arreglo permite aplicar una fórmula a un rango completo sin necesidad de arrastrarla manualmente por cada fila. Esto no solo ahorra tiempo, sino que también mantiene la fórmula dinámica y actualizada automáticamente cuando agregas o eliminas datos.
Por ejemplo, si quieres sumar las columnas A y B fila por fila, puedes usar esta fórmula sencilla:
=ARRAYFORMULA(A2:A + B2:B)Esta fórmula suma cada fila de las columnas A y B y muestra el resultado en la columna donde la coloques. Pero ojo, si aplicas un filtro o insertas filas, esta fórmula puede romperse o mostrar errores. Por eso, vamos a ver cómo mejorarla.
Evitar errores con celdas vacías
Cuando usas ARRAYFORMULA, si alguna celda en el rango está vacía, la fórmula puede devolver errores o resultados inesperados. Para evitar esto, es buena idea usar una condición que verifique si la celda está vacía antes de hacer la operación.
Así es como construyo yo la fórmula para evitar errores:
=ARRAYFORMULA(SI(A2:A = ""; ""; A2:A + B2:B))Esta fórmula dice: «Si la celda en A está vacía, deja el resultado vacío; si no, haz la suma». Así evitas errores y mantienes la hoja limpia y ordenada.
Problemas al aplicar filtros
Cuando aplicas un filtro en Google Sheets, las filas se reordenan y esto puede romper la fórmula tipo arreglo porque la fórmula puede perder la referencia correcta o dejar de mostrar resultados. Para evitarlo, una técnica que uso es mover la fórmula al encabezado, combinando el título con el cálculo en un solo arreglo vertical usando corchetes.
Así lo hago:
={"Suma"; ARRAYFORMULA(SI(A2:A = ""; ""; A2:A + B2:B))}Este método junta el encabezado «Suma» con la fórmula, para que el encabezado no se vea afectado por el filtro y la fórmula siga funcionando sin problemas.
Limitaciones al insertar filas
Sin embargo, esta técnica tiene un pequeño inconveniente: si insertas una fila encima del encabezado, la fórmula puede desfasarse y dejar de funcionar correctamente. Para solucionar esto, te muestro dos opciones para detectar la fila del encabezado y proteger la fórmula.
Opción 1: Comparar con el valor del encabezado
Esta fórmula verifica si el valor en la columna A es igual al texto del encabezado, y en ese caso muestra el título; si no, hace la suma o deja vacío si la celda está vacía:
=ARRAYFORMULA(SI(A1:A = "Número 1"; "Suma"; SI(A1:A = ""; ""; A1:A + B1:B)))Es importante que el texto «Número 1» coincida exactamente con el encabezado de tu tabla para que funcione correctamente.
Opción 2: Usar la función FILA para mayor flexibilidad
Esta opción es más robusta porque no depende del texto del encabezado. Usa la función FILA para detectar si estás en la primera fila y mostrar el encabezado, y en las demás filas aplica la fórmula:
=ARRAYFORMULA(SI(FILA(A1:A) = 1; "Suma"; SI(A1:A = ""; ""; A1:A + B1:B)))Con esta fórmula, aunque cambies el texto del encabezado o insertes filas, la fórmula seguirá funcionando sin problemas. Por eso, es la opción que recomiendo para hojas con datos dinámicos.
Consejos adicionales para trabajar con ARRAYFORMULA
Para que tus fórmulas tipo arreglo sean más eficientes y fáciles de mantener, te recomiendo:
- Evitar usar rangos infinitos como A:A, mejor usa rangos amplios pero limitados, por ejemplo A1:A1000, para mejorar el rendimiento.
- Usar condicionales para manejar celdas vacías o datos no numéricos y evitar errores.
- Combinar encabezados y datos en una sola fórmula para evitar desajustes al filtrar o insertar filas.
- Probar tu fórmula en una copia de la hoja antes de aplicarla en datos reales para evitar perder información.
- Documentar tus fórmulas con comentarios o notas para que tú o tu equipo entiendan su funcionamiento en el futuro.
Resumen de métodos para proteger tus fórmulas tipo arreglo
En resumen, estos son los pasos que yo sigo para evitar que mis ARRAYFORMULA se dañen al aplicar filtros o insertar filas:
- Usar ARRAYFORMULA con condicionales para evitar errores por celdas vacías.
- Incluir el encabezado dentro de la fórmula usando arreglos verticales con corchetes.
- Detectar la fila del encabezado usando el valor de la celda o la función FILA para mayor flexibilidad y robustez.
- Limitar los rangos para mejorar el rendimiento y evitar procesamiento innecesario.
- Probar y documentar tus fórmulas para facilitar su mantenimiento.
Conclusión
Con estos métodos puedes hacer que tus fórmulas tipo arreglo sean mucho más robustas y resistentes a cambios como filtros o inserción de filas. Personalmente, me gusta usar la función FILA porque es más flexible y no depende del texto del encabezado, lo que me evita dolores de cabeza cuando modifico la estructura de mis hojas.
Estos trucos te ahorrarán tiempo y errores cuando trabajes con grandes cantidades de datos en Google Sheets, y te permitirán crear hojas más profesionales y confiables.
Si quieres profundizar más en cómo usar ARRAYFORMULA y otras fórmulas avanzadas en Google Sheets, te recomiendo mi curso avanzado de Google Sheets, donde te enseño paso a paso cómo dominar estas herramientas.
Si quieres aprender a usar Google Forms y Google Sheets desde cero, te invito a tomar un curso gratis de 2 horas.