in

Cómo usar BYCOL() y BYROW() para evaluar datos en columnas y filas en Excel

istock-881365070-datos-de-excel.jpg

istock-881365070-datos-de-excel.jpg
Imagen: iStockfoto

La mayoría de las funciones de Microsoft Excel son autónomas: 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 la ventaja? En su mayor parte, usar una función en lugar de muchas será más eficiente. Sin embargo, como verá, cuando se combinan con otras funciones de Excel, estas dos funciones pueden hacer mucho trabajo duro.

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

Uso 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 .xlsx de demostración.

Lo primero es lo primero: LAMBDA() de Excel

Tanto BYCOL() como BYROW() utilizan la nueva función LAMBDA(). A LAMBDA La función es una función anónima, según Microsoft, pero esa 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 usando 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 o una cadena o número literal. El argumento de cálculo es la lógica que desea ejecutar. Una vez que todo esté correcto, guárdelo todo usando el Administrador de nombres para darle un nombre. Para usar la función, simplemente ingrese 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 existe esta nueva capacidad, pero no usaremos LAMBDA() para crear una nueva función. En cambio, lo usaremos para devolver un conjunto de resultados de matriz desde las funciones BYCOL() y BYROW().

Acerca de BYCOL() de Excel

BYCOL() es una de las nuevas funciones de matrices dinámicas de Excel que combina los valores en las columnas a las que se hace referencia y cambia la estructura de columna/fila al aplicar 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() puede devolver solo 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. Veamos 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.

Podría hacer lo mismo agregando una Fila de totales al objeto Tabla (Figura A). Si lo hace, le da valores individuales; usar BYCOL() devuelve una matriz.

El verdadero poder aquí es que puede sustituir 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] para [Value]:[Commission] para eliminar la columna Fecha y luego cambió MAX a SUM. (No tiene sentido agregar fechas dentro de este contexto, pero sobre todo, 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 ejecutar cualquier cantidad de cálculos en varias columnas con una sola función.
Con la función LAMBDA() de Excel, puede ejecutar cualquier cantidad de cálculos en varias columnas con una sola función.

Hemos usado 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 a 200:

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

Como puedes ver en Figura Clos 11 valores de Valor son superiores a 200 y los 9 valores de Comisión son superiores a 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 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, por lo que SUM() puede sumar la matriz resultante de 1 y 0. También podría usar COUNTIF():

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

pero fue una oportunidad divertida para presentarles la lógica.

En este punto, probablemente se esté preguntando por qué usaría BYCOL(). Da un poco de miedo y, en estos ejemplos, en su mayoría es innecesario, 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 hemos ingresado 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 genial, ¡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. Son cuatro: febrero, abril, mayo y junio. Es muy bueno realmente. 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 igual 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 usará para evaluar cada fila de la matriz. LAMBDA() puede devolver solo 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 función de Excel:

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

devuelve el valor más grande de cada fila, excluyendo los valores de Personal. Los resultados requieren un poco de explicación. El conjunto de matriz devuelto es un conjunto de valores de serie de fecha (el formato es General). Como puede ver, todos son mucho más grandes que los valores de Valor o Comisión. Nuevamente, en este ejemplo artificial, no tiene mucho sentido dentro del 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, podría hacer esto con varias 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 el número de valores superiores a 200 en cada fila, incluidos solo el valor y la comisión. Es igual 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 una matriz de meses. Ahora, devolvamos un conjunto de matrices de empleados que tienen 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; Cambié la matriz FILTER() a los nombres en la columna B y luego cambié todas las funciones BYCOL() a funciones BYROW(). ¡Eso es todo! También cambié el valor de referencia en C1 (porque todos los empleados han alcanzado el valor de 1750). Cuatro empleados tienen un valor mayor o igual a 1.900: James, June, Luke y Martha.

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

Por supuesto, las funciones reales son complejas, ¡pero hacen mucho!

He mantenido los ejemplos del tutorial simples a propósito, 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 Figuras D y GRAMO.

Fuente

Característica de descuento de AirPods Pro Amarillo

Ofertas: AirPods Pro con MagSafe Drop to 2022 Precio bajo de $ 174.98

No se proporciona subtítulo

Mario Kart 8 Deluxe Booster Pass Wave 1 Reseña