En este artículo entramos al maravilloso mundo del ArrayFormula, lo que es un hito y puede separar a los usuarios intermedios de los avanzados en Google Sheets
¿Que es ArrayFormula?
ArrayFormula es una forma de arrastrar formulas sin arrastarlas. En otras palabras, es una formula que reemplaza muchas formulas.
Y en otras palabras distintas, es una formula que se aplica en una celda pero “magicamente” repite la formula en las celdas que yo le diga
ArrayFormula, como su nombre en ingles lo indica, es una formula “tipo arreglo” o matricial.
Una formula matricial es una formula que se escribe en una celda, pero arroja resultados en muchas otras celdas
Algunos ejemplos de formulas matriciales son
- UNIQUE
- FILTER
- QUERY
- Algunos usos de INDIRECTO
- Algunos usos de INDICE
- Algunos usos de DESREF
¿Cuando aplicar ArrayFormula?
El ArrayFormula es extremadamente útil para tablas muy largas con funciones o formulas sencillas
Hay unos ejemplos extremos bien complejos de su uso, pero esto se los dejo para otro articulo
¿Cómo aplicar ArrayFormula?
En teoria, cualquier funcion o formula se puede convertir en un ArrayFormula
Simplemente “envolvemos” la formula en un ArrayFormula y ya esta.
El atajo de teclado es Ctrl + Shift + Enter
Sin embargo, antes de aplicarla, tenemos que hacer un par de modificaciones a nuestra fórmula o función.
Para que el ArrayFromula funcione tengo que indicarle desde que celda hasta que celda debe “correr”
Veamos un ejemplo sencillo:
Tengo una formula de división y quiero arrastrarla a toda mi tabla
Normalmente, la arrastrariamos con doble click en el cuadro azul o arrastrandola desde este mismo punto hacia abajo
La opción que les propongo es modificar la formula de la siguiente forma
=A2:A11/B2:B11
Aquí lo que le estoy diciendo es “divida la columna a y la b desde la fila 2 hasta la 11”
Si simplemente damos “Enter”, no va a pasar nada. Ni error ni nada. Es como si no hubiéramos hecho ningún cambio, ya que la fórmula solo va a tomar el primer valor del rango
Pero si nos situamos en la formula y le damos Ctrl + Shift + Enter, la formula se convierte a esto
=ArrayFormula(A2:A11/B2:B11)
Y nuestro ejemplo queda así:
Ahora intenten pararse en la celda C3 o C4; es como si se hubiera escrito el valor manual. Para el ojo no entrenado no hay formula alguna. Como ven, un efecto secundario de ArrayFormula (positivo o negativo, como lo quieran ver) es que nos “esconde” las formulas.
Ahora escriban algun valor en cualquier celda de la columna C
Me da el siguiente error
Lo que me lleva a una de las primeras limitaciones de ArrayFormula y es que si no somos cuidadosos podemos insertar datos en el rango que abarca el ArrayFormula e “interponernos en su camino” sin saberlo.
Esto lo podemos solucionar protegiendo las columnas
ArrayFormula en Funciones
ArrayFormula funciona no solo en operaciones sencillas matemáticas como la división que ya vimos.
Tambien podemos hacer un BUSCARV por ejemplo.
Un BUSCARV “normal” sería así
=BUSCARV(E2;$L$1:$N$4;2;0)
Ahora, al igual que en el ejemplo anterior, incrementamos el rango de las filas donde queremos que aplique
=BUSCARV(E2:E;$L$1:$N$4;2;0)
Para que funcione tenemos que dar Ctrl + Shift + Enter y quedaría asi
=ArrayFormula(BUSCARV(E2:E;$L$1:$N$4;2;0)
¿Dónde no funciona?
ArrayFormula no funciona con otras formulas tipo arreglo o matriciales, tales como:
- UNIQUE
- QUERY
- FILTER
- SUMAR.SI.CONJUNTO
Hay algunas formas de usarla con estas funciones, pero lo dejo para otro artículo
Conclusión
ArrayFormula nos evita tener que arrastrar formulas, y nos quita el problema de que no haya formula en una nueva fila insertada
Así mismo tiene el problema que para los usuarios novatos no es evidente, y pueden dañarlo. Nada que no se pueda arreglar con protecciones
Hay muchísimos usos prácticos que estaremos viendo de esta maravillosa fórmula ¿Alguna idea?
Hola Juan !! , como seria para usar ARRAYFORMULA con CONCATENAR. Gracias!! Sldos
Hola!
En vez de CONCATENAR, usa &
Saludos!
Gracias
Muchísimas gracias Juan, la verdad es que tus videos son de una gran ayuda, me gustaría saber si la Arrayformula se puede aplicar en una fórmula que combine Indice y coincidir para buscar cualquier valor en un rango determinado. Lo he intentado y no me funciona, no sé si es que no funciona o lo estoy haciendo mal.
Mil gracias y un fuerte abrazo.
Hola Fernando
Desafortunadamente no funciona con INDICE
Si funciona con COINCIDIR y con BUSCARV
Saludos!
Porque la función arrayformula, cuando contiene una función AND (Y) u OR , no funciona, por ejemplo esta: =ArrayFormula(SI(Y(B1:B>A1:A,F1:F””),B1:B-A1:A,””))
y como puedo solucionarlo. Desde ya muchas gracias.
Hola Adolfo
Desafortunadamente, hay funciones que no son compatibles con AF como las que mencionas. La única solución que he encontrado por ahora es reestructurar tus condicionales con SI anidados o con la función IFS
Saludos!
Buen día las funciones y y o toca manejarlas con los símbolos * (and)y + (or)
Y la condición se encierra entre paréntesis
=ArrayFormula(SI(((B1:B>A1)*(A,F1:F»»)),B1:B-A1:A,»»))
Saludos
Excelente aporte! Gracias Adrian!
Hola Juan, un placer escribirte; He estado siguiendo tus video y la verdad aportas muchísimo a la comunidad, eres un grande.
Te quería hacer una consulta, ¿ArrayFormula funciona de forma horizontal?, es decir, así como completa una formula infinitamente en una columna, se puede aplicar el mismo resultado con una fila?
Hola Juan
Mil gracias por tus palabras!
Si, lo puedes hacer en las dos direcciones
Un gran saludo!
Muchas gracias Juan!
He intentado usar ArrayFormula con SUMAR.SI.CONJUNTO y con MAXIFS y no tuve éxito, como lo puedo solucionar?
Hola Maria
Desafortunadamente hay algunas formulas incompatibles con ArrayFormula
SUMAR.SI.CONJUNTO no funciona pero SUMAR.SI
Nos toca volvernos un poco creativos con ArrayFormula
Saludos!
Gracias Juan. El usuario siempre puede borrar esa fila 2 sin querer, con consecuencias serias. Estaría bien que la fórmula se pudiera poner en el encabezado
Hola Mario
Si se puede. Me falta ponerlo en este artìculo
Aquì te dejo un video
https://www.youtube.com/watch?v=0O-HB5o1wKA
Saludos!