Introducción al ArrayFormula en Google Sheets

ArrayFormula en Google Sheets
— by

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?

Boletín

Nuestras últimas noticias en tu correo electrónico

Respuestas

  1. Avatar de gustavo martinez
    gustavo martinez

    Hola Juan !! , como seria para usar ARRAYFORMULA con CONCATENAR. Gracias!! Sldos

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      En vez de CONCATENAR, usa &

      Saludos!

  2. Avatar de Jefreey Mercado
    Jefreey Mercado

    Gracias

  3. Avatar de Fernando
    Fernando

    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.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Fernando
      Desafortunadamente no funciona con INDICE
      Si funciona con COINCIDIR y con BUSCARV
      Saludos!

  4. Avatar de Adolfo
    Adolfo

    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.

    1. Avatar de jsguzmanb
      jsguzmanb

      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!

    2. Avatar de adrian Grisales
      adrian Grisales

      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

      1. Avatar de jsguzmanb
        jsguzmanb

        Excelente aporte! Gracias Adrian!

  5. Avatar de Juan Alejandro
    Juan Alejandro

    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?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Juan

      Mil gracias por tus palabras!

      Si, lo puedes hacer en las dos direcciones

      Un gran saludo!

  6. Avatar de Maria
    Maria

    Muchas gracias Juan!

    He intentado usar ArrayFormula con SUMAR.SI.CONJUNTO y con MAXIFS y no tuve éxito, como lo puedo solucionar?

    1. Avatar de jsguzmanb
      jsguzmanb

      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!

  7. Avatar de Mario
    Mario

    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

    1. Avatar de jsguzmanb
      jsguzmanb

      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!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This