Cómo usar ARRAYFORMULA en Google Sheets (ejemplos de fórmulas de matriz)

Como su nombre indica, ARRAYFORMULA convierte una fórmula normal en una fórmula de matriz en Google Sheets. Te permite añadir fórmulas que se expandan automáticamente; se aplican a medida que se introducen nuevos datos en la hoja de cálculo. Esto significa que no tendrás que copiar y pegar fórmulas manualmente a medida que aparezcan nuevas filas de datos.

En un nivel fundamental, toma los valores de un rango de celdas y los muestra en múltiples filas y columnas. Esto significa que le permite utilizar matrices en fórmulas que normalmente no las aceptan, incluyendo SUMIF, VLOOKUP o FILTER.

Esta guía explica cómo funciona ARRAYFORMULA, por qué te conviene usarla, nueve ejemplos de fórmulas de matriz (con IF, SUMIF, VLOOKUP y FILTER) y los errores con los que es más probable que te encuentres.

¿Qué es la ARRAYFORMULA?

La ARRAYFORMULA es una función de matriz ampliable. Esto significa que sólo tiene que insertar la función en una celda y, a continuación, la función se aplica automáticamente a las filas siguientes a lo largo de toda una columna.

Como resultado, puede devolver varios valores de un rango de celdas con una sola ARRAYFORMULA, en lugar de devolver un único valor de una fórmula que no sea una matriz.

Veamos la sintaxis para entender cómo funciona y cómo combinarla con otras funciones.

Sintaxis

La sintaxis de la ARRAYFORMULA es la siguiente:

array_formula - este parámetro puede ser:

• una gama
• una expresión matemática que utiliza un rango de celdas o varios rangos del mismo tamaño
• una función que devuelve un resultado mayor que una celda

También puedes añadir la ARRAYFORMULA a una función que no sea de matriz en tu hoja de cálculo. Simplemente pulsa el atajo de teclado Ctrl/Cmd + Mayús + Intro, y Google Sheets envolverá automáticamente ARRAYFORMULA() alrededor de tu fórmula.

Como puede ver, la sintaxis de ARRAYFORMULA es más sencilla de lo esperado. Exploremos más ventajas de esta función.

¿Por qué utilizar ARRAYFORMULA en Google Sheets?

Además de ahorrar tiempo y esfuerzo manual al ofrecer una alternativa a los cálculos manuales, la ARRAYFORMULA también ofrece otras ventajas.

Veamos sólo algunas:

• Cálculo del tamaño de los lotes: En lugar de tener que utilizar funciones similares que calculan valores individualmente, puede utilizar una única fórmula para todo su conjunto de datos, independientemente de su tamaño. Esto evita el riesgo de pesadas hojas de cálculo que contienen múltiples fórmulas en un solo cálculo.
• Flexibilidad: Un solo cambio o modificación en cualquiera de las celdas afectará automáticamente al valor del cálculo de la ARRAYFORMULA. Esto significa que puede editar fácilmente los valores de las celdas, y cualquier valor que dependa de estas celdas se ajustará automáticamente en consecuencia.
• Dinámico: Cuando añada una nueva fila dentro de su rango de celdas, la ARRAYFORMULA se le aplicará automáticamente, por lo que no tendrá que introducir manualmente otra función.

Aunque Google Sheets puede ofrecer “Autorrelleno sugerido” para aplicar la función al resto de tus filas, el autorrelleno no podrá ofrecer las mismas ventajas que el anterior. Por ejemplo, si modificas los valores, existe el riesgo de que la función de autorrelleno no pueda actualizar correctamente todos los datos de las celdas. Esto le dejaría con información desactualizada e inexacta.

Ahora que comprende las numerosas ventajas de utilizar la ARRAYFORMULA en los cálculos de masa, exploremos cómo utilizar la ARRAYFORMULA de Google Sheets en los siguientes ejemplos.

Cómo utilizar la ARRAYFORMULA de Google Sheets

Supongamos que soy profesor y tengo un conjunto de datos con una lista de alumnos y sus notas en diferentes proyectos. Quiero calcular la nota media de cada alumno en todos los proyectos.

Normalmente, sumaría los valores de las celdas individualmente, como se muestra a continuación:

Sin embargo, este método pronto sería problemático en dos circunstancias recurrentes:

• Aumento de datos y fórmulas: Si decido aumentar el tamaño de los datos o utilizar más fórmulas, mi hoja de cálculo no funcionaría tan bien.
• Actualizaciones periódicas: Cualquier modificación de mis datos implica tener que cambiar todas las fórmulas utilizadas en ellos.

Utilizando la ARRAYFORMULA, puedo sumar rápidamente los resultados de todos los alumnos, independientemente del tamaño del conjunto de datos o del número de fórmulas utilizadas. Puedo añadir, eliminar o editar contenido como desee, y la fórmula se aplicará a los datos automáticamente.

Ejemplo 1: ARRAYFORMULA básica

Siguiendo con el ejemplo anterior, para calcular los totales, puede utilizar la ARRAYFORMULA como se muestra a continuación. Observe que ahora estoy añadiendo los rangos, mientras que antes añadía celdas individuales.

Esta nueva fórmula debería ser más o menos así:

Para que la ARRAYFORMULA funcione correctamente, asegúrese de que todas las celdas situadas debajo están vacías.

Tenga en cuenta lo siguiente: Terminando el rango de cada columna con la letra, (D2:D) puede añadir toda la columna como la matriz. Esto significa que si añade nuevas filas de datos en las columnas, la función ARRAYFORMULA se aplicará automáticamente.

Como puede ver, utilizar la fórmula básica es muy sencillo.

Ejemplo 2: IF con ARRAYFORMULA

Probablemente haya observado una secuencia de ceros al final de la columna tras aplicar la fórmula del ejemplo anterior. Puedes evitarlo añadiendo una función SI a la fórmula.

Esta sentencia IF le dice a la fórmula que deje la columna ‘Total’ en blanco si no hay ningún nombre en la columna A.

Ahora sólo hay celdas en blanco, sin ceros, después de la última fila de datos en la columna A. Incluyamos una fila para otro estudiante después de la fila 50 para probar su naturaleza expandible.

En cuanto empiece a escribir el contenido, la última columna incluirá automáticamente un cero. Esta es una ventaja importante del uso de ARRAYFORMULA. Cuando se trabaja con un conjunto de datos que cambia a menudo, ARRAYFORMULA permite realizar cambios sin ajustar constantemente la hoja de cálculo.

Ejemplo 3: SUMIF con ARRAYFORMULA

Digamos que he agrupado a los estudiantes en 3 grupos diferentes, y quiero encontrar el total del grupo en el ‘Proyecto D’. Puedo utilizar la ARRAYFORMULA junto con la función SUMIF para obtener el total de estos 3 grupos.

La sintaxis para SUMIF y ARRAYFORMULA es:

Como puede ver, ahora tengo el total de puntos por grupo para el Proyecto D, como se muestra en la columna J. Puedo utilizar esta nueva salida para calcular cualquier otra métrica de evaluación de forma rápida y eficaz.

Como antes, aparecen ceros después de la última celda con contenido en la columna 'Total'. Para ocultarlos, envuelve SUMIF en la misma prueba IF que usamos en el ejemplo 2:

=ARRAYFORMULA(IF(I2:I<>"", SUMIF(B2:B, I2:I, H2:H), ""))

Para obtener más información sobre el funcionamiento de la función SUMIF, consulte esta entrada del blog sobre cómo usar SUMIF en Google Sheets.

Ejemplo 4: VLOOKUP con ARRAYFORMULA

Supongamos que quiero buscar datos de rendimiento específicos de un alumno concreto. Normalmente, podría utilizar la función VLOOKUP para hacerlo. Sin embargo, tendría que introducir la función varias veces para cada criterio, ya que VLOOKUP sólo devuelve un único valor. Sin embargo, cuando combino esto con la ARRAYFORMULA, puedo encontrar múltiples valores al mismo tiempo.

La sintaxis para VLOOKUP con ARRAYFORMULA es:

En este ejemplo, quiero encontrar el nombre, el apellido y los resultados de los proyectos A y B del estudiante número 10. Con ‘ID de estudiante’ como clave, utilizaría la siguiente fórmula:

Para buscar varias claves a la vez, dale a VLOOKUP (BUSCARV) un rango de claves de búsqueda en lugar de una sola celda. Con los ID de alumno que quieres en K2:K4, esta fórmula devuelve una fila de resultados por cada ID:

=ARRAYFORMULA(VLOOKUP(K2:K4, $A$1:$I$52, {1,3,4,5,6}, FALSE))

Para obtener más información sobre cómo funciona la función VLOOKUP, consulte este artículo en Cómo funciona la función VLOOKUP en Google Sheets.

Ejemplo 5: FILTRO con ARRAYFORMULA

Uno de los usos más comunes al combinar FILTRO y ARRAYFORMULA es identificar duplicados. Para que funcione, necesita incorporar dos fórmulas básicas: COUNTIF y UNIQUE.

Supongamos que quiero encontrar contenido duplicado de los apellidos de los alumnos para buscar posibles parientes. Tendría que escribir la siguiente fórmula:

Como puede ver, ‘Dimmer’ ha aparecido en la celda. Esto significa que la fórmula encontró más de una celda en la columna ‘Apellido’ que contenía el nombre Dimmer. Esto significa que tengo dos alumnos con ese mismo apellido.

También puede utilizar esta función para encontrar y eliminar datos duplicados. Para saber más sobre cómo funcionan estas funciones individualmente, lea estas entradas del blog sobre la ÚNICO, CONTAR.SI y FILTRO funciones.

Ejemplo 6: Matrices horizontales

Hasta ahora, todos los ejemplos se han aplicado a matrices verticales. La ARRAYFORMULA puede utilizarse con la misma facilidad para matrices horizontales, lo que permite utilizarla independientemente de la estructura de los datos.

Calculemos el ‘Total’ de todos los proyectos de cada alumno, pero en formato horizontal. Puedo hacerlo introduciendo la siguiente fórmula:

En lugar de decirle a la función que baje por la lista (por ejemplo, de A2:A, lo que significa empezar en A2 y bajar por la columna A), le he dicho que atraviese los datos (por ejemplo, de B5:5, lo que significa empezar en B5 y atravesar la fila 5).

Haciendo este sencillo cambio, la ARRAYFORMULA funciona ahora horizontalmente. Y al igual que en el ejemplo anterior, puedo añadir una sentencia IF para deshacerme de los ceros en las columnas en blanco:

Ejemplo 7: Matrices multidimensionales

Ya ha visto utilizar la ARRAYFORMULA tanto en orientación vertical como horizontal. ¿Pero qué pasa con las tablas que incluyen ambos tipos de array? También puede utilizar la ARRAYFORMULA para estas tablas.

Aquí, he creado una tabla de multiplicar que multiplica la columna A por la fila 1, simplemente escribiendo lo siguiente:

Ejemplo 8: Utilización de ARRAYFORMULA para combinar columnas

La ARRAYFORMULA no sólo ayuda a calcular datos numéricos, sino que también puede ayudar a combinar texto de diferentes columnas.

Supongamos que quiero combinar las columnas ‘Nombre’ y ‘Apellidos’ para crear una columna ‘Nombre completo’. He insertado una columna para ‘Nombre completo’ para incluir la siguiente fórmula:

Tenga en cuenta lo siguiente: Esta fórmula sólo funcionará si las dos matrices tienen el mismo tamaño (por ejemplo.‘C2:C52&" "&D2:D52’). De lo contrario, Google Sheets no podrá realizar el cálculo.

Cómo crear una fórmula de matriz paso a paso

Los ejemplos anteriores muestran fórmulas terminadas. Este crea una fórmula de matriz desde la primera celda y añade ARRAYFORMULA al final con el atajo de teclado. La tabla recoge los agentes de ventas y el importe que vendió cada uno en el mes. Si el importe es igual o superior a 4000 $, el agente recibe una bonificación del 5%. Quiero calcular y devolver la bonificación de las ventas iguales o superiores a 4000 $, y dejar la celda en blanco si el importe es inferior a 4000 $.

1. Abre en Google Sheets la hoja de cálculo con tus datos.

2. En la primera fila de la columna donde quieres los resultados, escribe la Función IF.

3. Añade la condición: el importe de la columna B debe ser de 4000 $ o más (B2:B6>=4000).

4. Después de la coma, añade el cálculo que quieres devolver si la condición es TRUE.

5. Añade otra coma y, a continuación, el valor que quieres devolver si la condición es FALSE.

6. Cierra el paréntesis y usa el atajo de ARRAYFORMULA para añadir la función: pulsa Ctrl + Shift + Enter en Windows o Cmd + Shift + Enter en Mac.

7. Pulsa Intro para ver la matriz resultante.

La fórmula terminada es =ARRAYFORMULA(IF(B2:B6>=4000,B2:B6*0.05,"")). Bob, Janet y Sarah reciben su bonificación del 5%, y las celdas de Jane y Tim se quedan en blanco.

Cómo añadir un nombre de columna aunque contenga una ARRAYFORMULA

A veces puede ser necesario añadir un nombre de columna sobre los datos procesados por una función ARRAYFORMULA. Nombrar las columnas correctamente es clave para la claridad y la colaboración. Afortunadamente, puede hacerlo sin alterar la estructura de datos existente.

Por ejemplo, suponga que tiene dos listas de valores numéricos en las columnas A y B, y quiere sumarlas incluyendo un nombre de columna. Para ello, puede utilizar la siguiente fórmula:

Hay que tener en cuenta algunas cosas al hacer esto:

• Asegúrese de desplazar los rangos de celdas en ARRAYFORMULA a la fila de abajo. De lo contrario, intentará aplicar la fórmula a las cabeceras de las columnas.
• Las llaves contienen matrices, los elementos separados por punto y coma se expandirán verticalmente. En este ejemplo, si utiliza una coma en lugar de un punto y coma, la fórmula se romperá.

Errores comunes de ARRAYFORMULA y cómo corregirlos

Cuando una fórmula de matriz deja de funcionar, la causa suele ser una de estas.

• #REF! No se ha ampliado el resultado de la matriz porque se sobrescribirían datos: una celda del rango de salida ya contiene un valor. Borra las celdas que hay debajo o al lado de la fórmula y volverá a expandirse.
• Los argumentos de matriz de ADD tienen distintos tamaños: los rangos de la fórmula cubren filas distintas, como A2:A10 + B2:B11. Haz que todos los rangos empiecen y terminen en las mismas filas.
• Un solo resultado en lugar de una columna: las funciones que resumen un rango, como SUM, AND y OR, devuelven un único valor incluso dentro de ARRAYFORMULA. Para una prueba fila por fila, usa * en lugar de AND y + en lugar de OR: =ARRAYFORMULA(IF((B2:B>=4000)*(C2:C="Yes"), "Bonus", ""))
• #N/A en algunas filas: una búsqueda que no encuentra coincidencia devuelve #N/A en esa fila. Envuelve la búsqueda en IFERROR para mostrar una celda en blanco: =ARRAYFORMULA(IFERROR(VLOOKUP(K2:K4, $A$1:$I$52, {1,3,4,5,6}, FALSE), ""))
• Una hoja de cálculo lenta: los rangos abiertos como D2:D cubren todas las filas de la hoja. En archivos grandes, termina el rango en la última fila que necesites u omite las filas en blanco con IF, como en el ejemplo 2.

¿Cómo se crea una ARRAYFORMULA en Google Sheets?

¡Y ahí lo tiene! La ARRAYFORMULA es una herramienta extremadamente potente y versátil que puede ayudarle a racionalizar significativamente los datos de su hoja de cálculo.

Utilícela para devolver múltiples valores con una sola función. Combínala también con otras funciones como IF, VLOOKUP y SUMIF para crear fórmulas avanzadas.

¿Quiere limitar el resultado de una matriz a un tamaño específico? Más información en Cómo utilizar la fórmula ARRAY_CONSTRAIN en Google Sheets.