Cómo usar fórmulas de matriz en hojas de cálculo de Google

A principios de 2023, Google introdujo varias funciones nuevas para Sheets, incluidas ocho para trabajar con matrices. Con estas funciones, puede transformar una matriz en una fila o columna, crear una nueva matriz a partir de una fila o columna, o agregar una matriz actual.

Con más flexibilidad para trabajar con matrices y más allá de la función básica ARRAYFORMULA, veamos cómo usar estas funciones de matriz con fórmulas en Hojas de cálculo de Google.

Sugerencia : algunas de estas funciones pueden resultarle familiares si también utiliza Microsoft Excel.

Transformar un Array: TOROW y TOCOL

Si tiene una matriz en su conjunto de datos que desea transformar en una sola fila o columna, puede usar las funciones TOROW y TOCOL.

La sintaxis para cada función es la misma, TOROW(matriz, ignorar, escanear) y TOCOL(matriz, ignorar, escanear) donde solo se requiere el primer argumento para ambas.

• Matriz : la matriz que desea transformar, formateada como "A1: D4".
• Ignorar : de forma predeterminada, no se ignora ningún parámetro (0), pero puede usar 1 para ignorar espacios en blanco, 2 para ignorar errores o 3 para ignorar espacios en blanco y errores.
• Escanear : este argumento determina cómo leer los valores en la matriz. De forma predeterminada, la función escanea por fila o usando el valor False, pero puede usar True para escanear por columna si lo prefiere.

Veamos algunos ejemplos usando las funciones TOROW y TOCOL y sus fórmulas.

En este primer ejemplo, tomaremos nuestra matriz A1 a C3 y la convertiremos en una fila usando los argumentos predeterminados con esta fórmula:

=TORO(A1:C3)

Como puede ver, la matriz ahora está en una fila. Debido a que usamos el argumento de escaneo predeterminado, la función se lee de izquierda a derecha (A, D, G), hacia abajo, luego de izquierda a derecha nuevamente (B, E, H) hasta completar el escaneo por fila.

Para leer la matriz por columna en lugar de fila, podemos usar True para el argumento de exploración . Dejaremos el argumento de ignorar en blanco. Aquí está la fórmula:

=TORROW(A1:C3,,VERDADERO)

Ahora ve que la función lee la matriz de arriba a abajo (A, B, C), de arriba a abajo (D, E, F) y de arriba a abajo (G, H, I).

La función TOCOL funciona de la misma manera pero transforma la matriz en una columna. Usando el mismo rango, A1 a C3, aquí está la fórmula usando los argumentos predeterminados:

=TOCOL(A1:C3)

Nuevamente, usando el valor predeterminado para el argumento de escaneo , la función lee de izquierda a derecha y proporciona el resultado como tal.

Para leer la matriz por columna en lugar de por fila, inserte True para el argumento de exploración como este:

=TOCOLO(A1:C3,,VERDADERO)

Ahora ves que la función lee la matriz de arriba a abajo.

Crear una nueva matriz a partir de filas o columnas: CHOOSEROWS y CHOOSECOLS

Es posible que desee crear una nueva matriz a partir de una existente. Esto le permite crear un nuevo rango de celdas con solo valores específicos de otro. Para ello, utilizará las funciones CHOOSEROWS y CHOOSECOLS Google Sheets.

La sintaxis de cada función es similar, CHOOSEROWS (matriz, núm_fila, núm_fila_opción) y CHOOSECOLS (matriz, núm_columna, núm_columna_opción), donde los dos primeros argumentos son necesarios para ambas.

• Matriz : la matriz existente, formateada como "A1:D4".
• Row_num o Col_num : El número de la primera fila o columna que desea devolver.
• Row_num_opt o Col_num_opt : los números de filas o columnas adicionales que desea devolver. Google sugiere que use números negativos para devolver filas de abajo hacia arriba o columnas de derecha a izquierda.

Veamos algunos ejemplos usando CHOOSEROWS y CHOOSECOLS y sus fórmulas.

En este primer ejemplo, usaremos la matriz A1 a B6. Queremos devolver los valores de las filas 1, 2 y 6. Esta es la fórmula:

=ELEGIR(A1:B6,1,2,6)

Como puede ver, recibimos esas tres filas para crear nuestra nueva matriz.

Para otro ejemplo, usaremos la misma matriz. Esta vez, queremos devolver las filas 1, 2 y 6 pero con la 2 y la 6 en orden inverso. Puede usar números positivos o negativos para obtener el mismo resultado.

Usando números negativos, usarías esta fórmula:

=ELEGIR(A1:B6,1,-1,-5)

Para explicar, 1 es la primera fila a devolver, -1 es la segunda fila a devolver, que es la primera fila que comienza desde abajo, y -5 es la quinta fila desde abajo.

Usando números positivos, usaría esta fórmula para obtener el mismo resultado:

=ELEGIR(A1:B6,1,6,2)

La función CHOOSECOLS funciona de manera similar, excepto que la usa cuando desea crear una nueva matriz a partir de columnas en lugar de filas.

Usando la matriz A1 a D6, podemos devolver las columnas 1 (columna A) y 4 (columna D) con esta fórmula:

=ELEGIRCOLES(A1:D6,1,4)

Ahora tenemos nuestra nueva matriz con solo esas dos columnas.

Como otro ejemplo, usaremos la misma matriz comenzando con la columna 4. Luego agregaremos las columnas 1 y 2 con 2 (columna B) primero. Puedes usar números positivos o negativos:

=ELEGIRCOLES(A1:D6,4,2,1)

=ELEGIRCOLES(A1:D6,4,-3,-4)

Como puede ver en la captura de pantalla anterior, con las fórmulas en las celdas en lugar de la barra de fórmulas, recibimos el mismo resultado usando ambas opciones.

Nota : Debido a que Google sugiere usar números negativos para invertir la ubicación de los resultados, tenga esto en cuenta si no está recibiendo los resultados correctos usando números positivos.

Envolver para crear una nueva matriz: WRAPROWS y WRAPCOLS

Si desea crear una nueva matriz a partir de una existente pero ajustar las columnas o filas con una cierta cantidad de valores en cada una, puede usar las funciones WRAPROWS y WRAPCOLS.

La sintaxis para cada función es la misma, WRAPROWS (rango, conteo, relleno) y WRAPCOLS (rango, conteo, relleno), donde los dos primeros argumentos son necesarios para ambos.

• Rango : el rango de celdas existente que desea usar para una matriz, con el formato "A1: D4".
• Recuento : el número de celdas de cada fila o columna.
• Pad : puede usar este argumento para colocar texto o un solo valor en celdas vacías. Esto reemplaza el error #N/A que recibirá para las celdas en blanco. Incluya el texto o valor entre comillas.

Veamos algunos ejemplos usando las funciones WRAPROWS y WRAPCOLS y sus fórmulas.

En este primer ejemplo, usaremos el rango de celdas A1 a E1. Crearemos una nueva matriz que envuelva filas con tres valores en cada fila. Aquí está la fórmula:

=ENVOLTURAS(A1:E1,3)

Como puede ver, tenemos una nueva matriz con el resultado correcto, tres valores en cada fila. Debido a que tenemos una celda vacía en la matriz, aparece el error #N/A. Para el siguiente ejemplo, usaremos el argumento pad para reemplazar el error con el texto "Ninguno". Aquí está la fórmula:

=ENVOLTURAS(A1:E1,3,”Ninguno”)

Ahora, podemos ver una palabra en lugar de un error de Hojas de cálculo de Google.

La función WRAPCOLS hace lo mismo al crear una nueva matriz a partir de un rango de celdas existente, pero lo hace envolviendo columnas en lugar de filas.

Aquí, usaremos la misma matriz, A1 a E3, envolviendo columnas con tres valores en cada columna:

=ENVOLVERCOLOS(A1:E1,3)

Al igual que el ejemplo de WRAPROWS, recibimos el resultado correcto pero también un error debido a la celda vacía. Con esta fórmula, puede usar el argumento pad para agregar la palabra "Vacío":

= WRAPCOLS (A1: E1,3, "Vacío")

Esta nueva matriz se ve mucho mejor con una palabra en lugar del error.

Combine para crear una nueva matriz: HSTACK y VSTACK

Dos funciones finales que veremos son para agregar matrices. Con HSTACK y VSTACK, puede agregar dos o más rangos de celdas para formar una sola matriz, ya sea horizontal o verticalmente.

La sintaxis para cada función es la misma, HSTACK (rango1, rango2,…) y VSTACK (rango1, rango2,…), donde solo se requiere el primer argumento. Sin embargo, casi siempre usará el segundo argumento, que combina otro rango con el primero.

• Range1 : el primer rango de celdas que desea usar para la matriz, con el formato "A1: D4".
• Range2,… : El segundo rango de celdas que desea agregar al primero para crear la matriz. Puede combinar más de dos rangos de celdas.

Veamos algunos ejemplos usando HSTACK y VSTACK y sus fórmulas.

En este primer ejemplo, combinaremos los rangos A1 a D2 con A3 a D4 usando esta fórmula:

=HPILA(A1:D2,A3:D4)

Puede ver nuestros rangos de datos combinados para formar una sola matriz horizontal.

Para un ejemplo de la función VSTACK, combinamos tres rangos. Con la siguiente fórmula, usaremos los rangos de A2 a C4, de A6 a C8 y de A10 a C12:

=PILAV(A2:C4,A6:C8,A10:C12)

Ahora, tenemos una matriz con todos nuestros datos usando una fórmula en una sola celda.

Manipule arreglos con facilidad

Si bien puede usar ARRAYFORMULA en ciertas situaciones, como con la función SUM o la función IF, estas fórmulas de matriz adicionales de Google Sheets pueden ahorrarle tiempo. Le ayudan a organizar su hoja exactamente como lo desea y con una única fórmula de matriz.

Para obtener más tutoriales como este, pero con funciones que no son de matriz, vea cómo usar la función CONTAR.SI o SUMAR.SI en Hojas de cálculo de Google.