Imagen: iStockfoto

La mayoría de las funciones de Microsoft Excel son independientes: un valor de resultado para cada función o fórmula. Por ejemplo, puede usar SUM() para devolver la suma de todos los valores en una sola columna. Si desea hacer lo mismo para las columnas adyacentes, copie la función SUM() o ingrese manualmente la función para cada columna que esté evaluando. Con las funciones BYCOL() y BYROW() de Excel, puede ingresar una función y devolver un conjunto de resultados de matriz. ¿Cuál es el beneficio? En la mayoría de los casos, usar una función en lugar de varias será más eficiente. Sin embargo, como verá, cuando se combinan con otras funciones de Excel, estas dos funciones pueden hacer mucho trabajo tedioso.

VER: 83 trucos de Excel que todo usuario debe dominar (TechRepublic)

Estoy usando Microsoft 365 en un sistema Windows 10 de 64 bits. Le recomiendo que no actualice a Windows 11 hasta que se resuelvan todos los problemas. Estas funciones solo están disponibles en Microsoft 365 y Excel para la web. Para su comodidad, puede descargar el archivo de demostración .xlsx.

Índice
  • Primero: LAMBDA() de Excel
  • Acerca de BYCOL() de Excel
  • Acerca de BYROW() de Excel
  • Primero: LAMBDA() de Excel

    BYCOL() y BYROW() utilizan la nueva función LAMBDA(). A LAMBDA La función es una función anónima, según Microsoft, pero esta descripción no es útil. Una función Excel LAMBDA() es similar a una función definida por el usuario, pero no necesita VBA. En resumen, LAMBDA() de Excel le permite crear funciones personalizadas y reutilizables y nombrarlas con nombres significativos usando el formulario:

    LAMBDA([parameter1, parameter2, …,] calculation)

    donde los argumentos de parámetros opcionales son un valor que pasa 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 desea ejecutar. Una vez que todo está correcto, guardas todo usando el administrador de nombres para darle un nombre. Para usar la función, solo necesita ingresar el nombre de la función en el nivel de la hoja, como lo haría con cualquier otra función.

    Es bueno saber que esta nueva característica existe, pero no usaremos LAMBDA() para crear una nueva función. En su lugar, lo usaremos para devolver un conjunto de resultados de matriz de las funciones BYCOL() y BYROW().

    Acerca de BYCOL() de Excel

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

    Esta función de Excel utiliza el siguiente formulario:

    BYCOL(array, LAMBDA(column))

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

    Lo que hay que tener en cuenta es que BYCOL() de Excel evalúa los datos de origen por columnas pero devuelve una fila de resultados. Tomemos un ejemplo rápido. Figura A muestra los resultados de usar BYCOL() en la celda H3:

    =BYCOL(Table1[[Date]:[Commission]],LAMBDA(column,MAX(column)))

    Figura A

    Use BYCOL() de Excel para devolver una fila de valores máximos de cada columna.
    Use BYCOL() de Excel para devolver una fila de valores máximos de cada columna.

    Si no está trabajando con un objeto Table para sus datos de origen, sus referencias a Table1 serán tradicionales, como C3:E13 para matriz.

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

    Puede hacer lo mismo agregando una fila de Totales al objeto Tabla (Figura A). Esto le da valores individuales; usar BYCOL() devuelve una matriz.

    El verdadero poder aquí es que puede reemplazar MAX() con casi cualquier función de Excel. Por ejemplo, Figura B muestra la suma de las columnas D y E. Cambié la referencia de matriz de [Date]:[Commission] a [Value]:[Commission] para eliminar la columna Fecha, luego reemplazó MAX con SUM. (No tiene sentido agregar fechas en este contexto, pero lo que es más importante, quiero que vea lo fácil que es modificar la función. Si quiere practicar un poco, intente aplicar MIN() y AVERAGE()).

    Figura B

    Con la función LAMBDA() de Excel, puede realizar cualquier cantidad de cálculos en varias columnas con una sola función.
    Con la función LAMBDA() de Excel, puede realizar cualquier cantidad de cálculos en varias columnas con una sola función.

    Usamos 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 mayores a 200:

    =BYCOL(Table1[[Value]:[Commission]],LAMBDA(column,SUM(--(column>200))))

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

    Figura C

    La función LAMBDA() de Excel puede manejar algunos cálculos complejos.
    La función LAMBDA() de Excel puede manejar algunos cálculos complejos.

    También te estarás preguntando para qué sirven 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 SUM() pueda sumar la matriz resultante de 1 y 0. También puede usar COUNTIF() :

    =BYCOL(Table1[[Value]:[Commission]],LAMBDA(column,COUNTIF(column,">=" & 200)))

    pero fue una oportunidad divertida para introducirte a la lógica.

    En este punto, probablemente se esté preguntando por qué usaría BYCOL(). Parece un poco aterrador y en estos ejemplos en su mayoría inútil, así que veamos un ejemplo más razonable y complejo. Figura D muestra un conjunto simple de valores mensuales para seis personas diferentes. Queremos enumerar todos los meses que tienen un valor superior a 1750, que ingresamos en C1. Al usar una celda de entrada, podemos cambiar el valor de referencia sin tener que modificar la función real:

    =FILTER(Table2[[#Headers],[Jan]:[Jun]],BYCOL(Table2[[Jan]:[Jun]],LAMBDA(column,COUNTIF(column, ">=" & C1))))

    Figura D

    Use BYCOL() de Excel para analizar datos.
    Use BYCOL() de Excel para analizar datos.

    Bien, esta función en particular es increíble, ¡pero mira lo que hace! Devuelve una matriz de resultados filtrados que contiene meses por nombre que incluyen un valor igual o mayor que el valor de referencia en C1. Son cuatro: febrero, abril, mayo y junio. Es bastante genial. Si no sabe cómo usar FILTER(), lea Cómo usar la función de matriz dinámica FILTER() en Excel.

    Has aprendido mucho, pero todavía tenemos que revisar BYROW().

    Acerca de BYROW() de Excel

    Una vez que comprenda BYCOL() y cómo funciona LAMBDA() para devolver un conjunto de resultados de matriz, agregar BYROW() es fácil. Funciona de la misma manera que BYCOL(), pero como ya sospechará, evalúa filas en lugar de columnas.

    Esta función de Excel usa la forma:

    BYROW(array, LAMBDA(row))

    donde matriz son los datos de origen y LAMBDA() es el cálculo que se utilizará para evaluar cada fila de la matriz. LAMBDA() solo puede devolver un valor para cada fila. Todo lo que aprendió sobre BYCOL() se aplica a BYROW(), por lo que podemos pasar directamente a un ejemplo. Como puedes ver en Figura Ela funcion excel:

    =BYROW(Table1[[Date]:[Commission]],LAMBDA(row,MAX(row)))

    devuelve el valor más grande de cada fila, excluyendo los valores personales. Los resultados requieren alguna explicación. El conjunto de matrices devuelto es un conjunto de valores de intervalo de fechas (el formato es general). Como puede ver, todos están muy por encima de los valores de Valor o Comisión. Nuevamente, en este ejemplo artificial, no tiene mucho sentido en el contexto de los datos, ¡pero es interesante ver algo inesperado! Si formateó los valores, serían las mismas fechas que ve en la columna C.

    Figura E

    Utilice BYROW() de Excel para evaluar por filas.
    Utilice BYROW() de Excel para evaluar por filas.

    Al igual que con BYCOL() de Excel, puede hacer esto con múltiples funciones MAX(), pero eso requeriría 11 funciones y devolvería 11 valores de resultado. BYROW() de Excel requiere una función y devuelve un conjunto de resultados de matriz. Ahora veamos algo más complejo.

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

    =BYROW(Table1[[Value]:[Commission]],LAMBDA(row,COUNTIF(row,">=" & 200)))

    Esta función devuelve la cantidad de valores mayores a 200 en cada fila, incluidos solo el valor y la comisión. Es lo mismo que el ejemplo anterior de BYCOL(), pero evalúa filas en lugar de columnas.

    Figura F

    La función LAMBDA() de Excel puede manejar cálculos complejos.
    La función LAMBDA() de Excel puede manejar cálculos complejos.

    Como antes, apliquemos esta función de Excel a una situación más compleja usando el conjunto de datos en Figura D. En el ejemplo de BYCOL(), devolvimos un conjunto de matrices de meses. Ahora devuelva un conjunto de matrices de empleados que tengan un valor mayor o igual que el valor de referencia en C1. figura g muestra la función BYROW() en el trabajo:

    =FILTER(Table2[Column1],BYROW(Table2[[Jan]:[Jun]],LAMBDA(row,COUNTIF(row, ">=" & C1))))

    figura g

    BYROWS() combinado con FILTER() hace mucho trabajo por nosotros.

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

    VER: Office 365: una guía para líderes tecnológicos y empresariales (PDF gratuito) (TechRepublic)

    Es cierto que las funciones reales son complejas, ¡pero hacen mucho!

    Deliberadamente simplifiqué los ejemplos del tutorial, pero ahora que sabe cómo funcionan estas funciones de Excel, puede comenzar a aplicarlas a su propio trabajo con requisitos más complejos como los que se muestran en dígitos D y gramo.