Domina el poder de BYCOL() y BYROW() para analizar datos en Excel

La mayoría de las funciones de Microsoft Excel son autónomas, lo que significa que devuelven un solo valor resultante por cada función o fórmula. Por ejemplo, puedes utilizar la función SUMA() para obtener la suma de todos los valores en una sola columna. Si deseas hacer lo mismo para columnas adyacentes, tienes que copiar la función SUMA() o ingresar manualmente la función para cada columna que estés evaluando. Utilizando las funciones BYCOL() y BYROW() de Excel, puedes ingresar una sola función y obtener un conjunto de resultados en forma de matriz. ¿Cuál es la ventaja de esto? En su mayoría, utilizar una sola función en lugar de varias será más eficiente. Sin embargo, como verás, cuando se combinan con otras funciones de Excel, estas dos funciones pueden hacer mucho trabajo pesado.

Estoy utilizando Microsoft 365 en un sistema Windows 10 de 64 bits. Recomiendo que no actualices a Windows 11 hasta que se solucionen todos los problemas. Estas funciones solo están disponibles en Microsoft 365 y Excel para la web. Para tu comodidad, puedes descargar el archivo .xlsx de demostración.

Lo primero: la función LAMBDA() de Excel

Tanto BYCOL() como BYROW() utilizan la nueva función LAMBDA() de Excel. Según Microsoft, una función LAMBDA es una función anónima, pero esa descripción no es muy útil. Una función LAMBDA() de Excel es similar a una función definida por el usuario, pero no necesitas utilizar VBA. En resumen, la función LAMBDA() de Excel te permite crear funciones personalizadas y reutilizables y nombrarlas mediante nombres significativos utilizando el siguiente formato:

LAMBDA([parámetro1, parámetro2, ...,] cálculo)

donde los argumentos de parámetro opcionales son un valor que le pasas a la función. Puede ser una referencia de celda, una cadena literal o un número. El argumento de cálculo es la lógica que deseas ejecutar. Una vez que todo eso es correcto, lo guardas todo usando el Administrador de nombres para darle un nombre. Para utilizar la función, simplemente ingresas el nombre de la función a nivel de hoja, como lo harías con cualquier otra función.

¡Descubre las maravillas de GNOME 42!

Es bueno saber que esta nueva capacidad existe, pero no la utilizaremos para crear una nueva función. En cambio, la utilizaremos para devolver un conjunto de resultados en forma de matriz desde las funciones BYCOL() y BYROW().

Sobre la función BYCOL() de Excel

BYCOL() es una de las nuevas funciones de matriz dinámica de Excel que combina los valores en las columnas de referencia y cambia la estructura de columna/fila aplicando LAMBDA() a cada columna.

Esta función de Excel utiliza la siguiente forma:

BYCOL(matriz, LAMBDA(columna))

donde matriz es el conjunto de datos fuente y LAMBDA() es el cálculo que se utilizará para evaluar cada columna en la matriz. LAMBDA() solo puede devolver un valor para cada columna.

Lo importante de tener en cuenta es que BYCOL() de Excel evalúa los datos fuente por columnas pero devuelve una fila de resultados. Veamos un ejemplo rápido. La Figura A muestra los resultados del uso de BYCOL() en la celda H3:

Cómo anclar imágenes en Word para mantenerlas en su lugar

=BYCOL(Tabla1[[Fecha]:[Comisión]],LAMBDA(columna,MÁXIMO(columna)))

Figura A

Si no estás trabajando con un objeto de Tabla para tus datos fuente, tus referencias a Tabla1 serán tradicionales, como C3:E13 para la matriz.

Lo importante a recordar es que esta configuración evalúa cada columna gracias a LAMBDA(), que evalúa una columna a la vez para devolver el conjunto de resultados. Cada valor resultante es el valor más grande de su columna.

Puedes hacer lo mismo agregando una fila de totales al objeto de Tabla (Figura A). Al hacerlo, obtienes valores individuales; al utilizar BYCOL() se devuelve una matriz.

Las mejores soluciones de respaldo en la nube para proteger tus datos

El verdadero poder aquí es que puedes sustituir MÁXIMO() con casi cualquier función de Excel. Por ejemplo, Figure B muestra la suma de las columnas D y E. Cambié la referencia de matriz de [Fecha]:[Comisión] a [Valor]:[Comisión] para eliminar la columna de fechas y luego cambié MÁXIMO por SUMA. (No tiene sentido sumar fechas en este contexto, pero principalmente quiero que veas lo fácil que es modificar la función. Si quieres practicar un poco, intenta aplicar MÍN() y PROMEDIO().

Figura B

Hemos utilizado algunos cálculos simples, pero gracias a LAMBDA(), podemos especificar cálculos más complejos. Por ejemplo, la siguiente función cuenta el número de valores en Valor y Comisión que son mayores que 200:

=BYCOL(Tabla1[[Valor]:[Comisión]],LAMBDA(columna,SUMA(--(columna>200))))

Como puedes ver en Figura C, los 11 valores en Valor son mayores que 200 y 9 valores en Comisión son mayores que 200.

Google Domains: Automatiza la configuración de tu dominio y DNS

Figura C

Tal vez te preguntes qué hacen los guiones. Convierten los resultados de la expresión, columna>200, en una serie de 1 y 0, en lugar de VERDADERO y FALSO, respectivamente, para que SUMA() pueda sumar la matriz de resultados de 1 y 0 resultante. También podrías utilizar CONTAR.SI():

=BYCOL(Tabla1[[Valor]:[Comisión]],LAMBDA(columna,CONTAR.SI(columna,">=" & 200)))

pero fue una oportunidad divertida para presentarte la lógica de los guiones.

En este punto, probablemente te estés preguntando por qué utilizarías BYCOL(). Parece un poco intimidante y en estos ejemplos, en su mayoría innecesario, así que veamos un ejemplo más adecuado y complejo. Figura D muestra un conjunto simple de valores mensuales para seis personas diferentes. Queremos listar todos los meses que tienen un valor mayor que 1,750, que hemos ingresado en C1. Al utilizar una celda de entrada, podemos cambiar el valor de referencia sin tener que modificar la función real:

Cómo crear tu tablero kanban en Aha! para la gestión de proyectos

=FILTRAR(Tabla2[[#Encabezados],[Ene]:[Jun]],BYCOL(Tabla2[[Ene]:[Jun]],LAMBDA(columna,CONTAR.SI(columna, ">=" & C1))))

Figura D

Vale, esta función en particular es complicada, ¡pero mira lo que logra! Devuelve una matriz de resultados filtrados que contiene los meses por nombre que incluyen un valor igual o mayor que el valor de referencia en C1. Hay cuatro: Feb, Abr, May y Jun. Es bastante genial en realidad. Si no sabes cómo utilizar FILTRAR(), lee Cómo utilizar la función de matriz dinámica FILTRAR() en Excel.

Has aprendido mucho, pero aún necesitamos revisar BYROW().

Sobre la función BYROW() de Excel

Una vez que comprendas BYCOL() y cómo funciona LAMBDA() con él para devolver un conjunto de resultados en forma de matriz, agregar BYROW() es fácil. Funciona de la misma manera que BYCOL(), pero, como podrías sospechar a estas alturas, evalúa filas en lugar de columnas.

Beneficios de usar un tablero Kanban: visualización

Esta función de Excel utiliza la siguiente forma:

BYROW(matriz, LAMBDA(fila))

donde matriz es el conjunto de datos fuente y LAMBDA() es el cálculo que se utilizará para evaluar cada fila en la matriz. LAMBDA() solo puede devolver un valor para cada fila. Todo lo que has aprendido sobre BYCOL() se aplica a BYROW(), por lo que podemos pasar directamente a un ejemplo. Como puedes ver en Figura E, la función de Excel:

=BYROW(Tabla1[[Fecha]:[Comisión]],LAMBDA(fila,MÁXIMO(fila)))

devuelve el valor más grande en cada fila, excluyendo los valores de Personal. Los resultados requieren un poco de explicación. El conjunto de arreglos devuelto es un conjunto de valores de fecha serial (el formato es General). Como puedes ver, todos son mucho mayores que los valores de Valor o Comisión. Nuevamente, en este ejemplo ficticio, no tiene mucho sentido dentro del contexto de los datos, ¡pero es interesante ver algo inesperado! Si formatearas los valores, serían las mismas fechas que ves en la columna C.

Figura E

Cómo instalar y configurar TeamSpeak en un servidor Ubuntu 20.04

Al igual que con BYCOL() de Excel, podrías hacer esto con varias funciones MÁXIMO(), pero eso requeriría 11 funciones y devolvería 11 valores resultantes. BYROW() de Excel solo requiere una función y devuelve un conjunto de resultados en forma de matriz. Ahora veamos algo más complejo.

Figura F muestra el resultado de la función de Excel:

=BYROW(Tabla1[[Valor]:[Comisión]],LAMBDA(fila,CONTAR.SI(fila,">=" & 200)))

Esta función devuelve el número de valores mayores que 200 en cada fila, incluyendo solo Valor y Comisión. Es lo mismo que el ejemplo anterior con BYCOL(), pero evalúa filas en lugar de columnas.

Figura F

Datadog vs. Dynatrace: Cuál es la mejor herramienta para tus necesidades de DevOps

Como antes, apliquemos esta función de Excel a una situación más compleja utilizando el conjunto de datos en Figura D. En el ejemplo de BYCOL(), devolvimos un conjunto de arreglos de meses. Ahora, devolvamos un conjunto de arreglos de empleados que tienen un valor mayor o igual al valor de referencia en C1. Figura G muestra la función BYROW() en acción:

=FILTRAR(Tabla2[Columna1],BYROW(Tabla2[[Ene]:[Jun]],LAMBDA(fila,CONTAR.SI(fila, ">=" & C1))))

Figura G

BYROWS(), combinado con FILTER(), hace mucho trabajo por nosotros. Esta función es casi idéntica a la función BYCOL() utilizada anteriormente; cambié la matriz de FILTER por los nombres en la columna B y luego cambié todas las funciones BYCOL por funciones BYROW. ¡Eso es todo! También cambié el valor de referencia en C1 (porque todos los empleados han alcanzado el valor de 1,750). Cuatro empleados tienen un valor mayor o igual a 1,900: James, June, Luke y Martha.

Cómo agrupar registros por hora en Microsoft Excel usando una tabla dinámica

Concedido, las funciones reales son complejas, ¡pero hacen mucho trabajo!

He mantenido los ejemplos del tutorial simples a propósito, pero ahora que sabes cómo funcionan estas funciones de Excel, puedes comenzar a aplicarlas en tu propio trabajo con requisitos más complejos como los mostrados en Figuras D y G.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Domina el poder de BYCOL() y BYROW() para analizar datos en Excel , tenemos lo ultimo en tecnología 2023.