La función BYROW, aplica una función LAMBDA a cada fila del rango de datos, y, devuelve una matriz.
Cuando usamos BYROW, la matriz se divide en filas, es decir, en las filas que tenga el rango de datos, y, trabaja de forma independiente, con cada fila.
Sintaxis
= BYROW (matriz, lambda (fila))
La sintaxis de la función BYROW tiene los siguientes argumentos:
matriz Una matriz que se va a separar por filas.
lambda LAMBDA que toma una fila como un solo parámetro y calcula un resultado.
Veamos un ejemplo, tenemos los siguientes datos.
Queremos calcular los totales para cada fila de datos, para ello, en la celda E2, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=BYROW (
El primer argumento de la función BYROW, es array, pues seleccionamos el rango de los datos.
=BYROW (A2:C4;
Es decir, BYROW, va a aplicar la función LAMBDA, al rango A2 a C2, aplicara otra función LAMBDA al rango A3:C3, y, otra al rango A4:C4.
Como argumento LAMBDA, vamos a definir una variable, la llamamos, por ejemplo, matriz.
=LAMBDA (matriz;
Como argumento calculo, de la función LAMBDA, usamos la función SUMA, para que nos sume la variable matriz.
LAMBDA (matriz; SUMA (matriz)
La sintaxis completa, quedaría:
=BYROW(A2:C4;LAMBDA(matriz;SUMA(matriz)))
Aceptamos, y, podemos ver una matriz desbordada en vertical, con los totales de cada fila.
Y si tenemos los siguientes datos, donde entre los datos, tenemos un par de columnas sin datos.
Si usamos la siguiente expresión, que es la misma que la anterior, pues no pasa nada, ya que la función SUMA, toma los valores en blanco como cero.
=BYROW(A9:E11;LAMBDA(matriz;SUMA(matriz)))
Volviendo al primer ejemplo.
Queremos que, si la suma es mayor a 100, haga la suma, en caso contrario, que no la haga.
Debemos de modificar, el argumento cálculo de la función LAMBDA, donde vamos a añadir un condicional SI, donde vamos a preguntar, que si la suma de la variable matriz es mayor a 100, pues que nos realice la suma, en caso contrario, que ponga un texto en blanco.
=BYROW (A2:C4; LAMBDA (matriz; SI(SUMA (matriz)>100;SUMA(matriz);"")))
Podemos ver, solo, aquellos valores que son mayores a 100.
Veamos otro ejemplo, tenemos los siguientes datos:
Donde, no tenemos el total calculado, que es la suma de subtotal más los impuestos, pues vamos a calcularlo con BYROW.
Para ello, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=BYROW(
El primer argumento es matriz, que es el rango desde F2 a G2, al ser un rango, como separador, debemos de poner los dos puntos (:), que son las columnas de subtotal e impuestos.
=BYROW(F2:G2;
Ahora, ponemos la función LAMBDA, como argumento parámetro, creamos la variable matriz, y, como argumento calculo, sumamos la variable matriz, y, cerramos los paréntesis.
=BYROW(F2:G2;LAMBDA(matriz;SUMA(matriz)))
Aceptamos.
Vemos que tenemos el total para la primera celda, lo siguiente es arrastrar, ya, tenemos el total para cada celda.
Si no queremos arrastrar, en el argumento matriz de BYROW, podemos hacerlo de forma matricial, para ello, en vez de poner F2:G2, especificar el rango completo, y, nos devolverá una matriz desbordada, con los mismos resultados.
=BYROW(F2:G968;LAMBDA(matriz;SUMA(matriz)))
Pero, si lo que quiero es saber, la diferencia porcentual del total de cada fila respecto al total general, tenemos que dividir la función anterior:
=BYROW(F2:G968;LAMBDA(matriz;SUMA(matriz)))
Entre la suma de todos los datos, es decir, a la función anterior, la englobamos en la función SUMA, quedaría la expresión completa, como sigue:
=BYROW(F2:G968;LAMBDA(matriz;SUMA(matriz)))/SUMA(BYROW(F2:G968;LAMBDA(aa;SUMA(aa))))
Lo ponemos en porcentaje, y, le damos dos decimales, y, tenemos la diferencia porcentual, de cada celda, respecto al total.
Si sumamos todos los decimales deberá de darnos el 100%.
Pero, lo que quiero ahora, es poder filtrar por un año con BYROW.
Primero, vamos a copiar los años, y, los transponemos, para ello, usamos la función TRANSPONER, lo haremos en la celda S1.
=TRANSPONER(A1:G1)
Vamos a usar un cuadro combinado, pero, para usar un cuadro combinado, debemos de tener activa la pestaña programador, o desarrollador, si tenemos la versión 2013.
Si no la tenemos, debemos de activarla, para ello, vamos a la pestaña archivo, y, seleccionamos opciones.
Se abre la ventana, opciones de Excel, en la ventana de la izquierda, seleccionamos personalizar cinta de opciones.
Vamos a ir a la pestaña de programador, dentro del grupo controles, desplegamos insertar, y, dentro de controles de formulario, elegimos cuadro de lista.
En la ventana central, tenemos un grupo llamado, personalizar la cinta de opciones, pues, en la ventana inferior, buscamos programador, o, desarrollador, la marcamos, y, aceptamos.
Ya nos debe de aparecer en la cinta de opciones.
Pues, vamos a la pestaña de programador, dentro del grupo controles, desplegamos insertar, y, dentro de controles de formulario, elegimos cuadro de lista, o, cuadro combinado.
Lo posicionamos en la hoja de cálculo.
Hacemos clic con el botón secundario del ratón sobre el cuadro de lista, y, elegimos formato de control.
Se abre la ventana, formato de control, en la ventana rango de entrada, debemos de seleccionar los años, ¿Por qué lo primero que hemos hecho, ha sido transponer los años?, pues, porque en un cuadro combinado, no podemos seleccionar un rango en horizontal, en ese caso, solo nos devolverá el primer valor, pues, seleccionamos los años en vertical.
Decir, que cuando tenemos un cuadro combinado, y, seleccionamos un elemento, lo que nos devuelve es su posición, no el valor, nos devuelve un número, que indica su posición, pues ese número, debemos de indicar donde debe de colocarlo en la hoja de cálculo, y, lo debemos de indicar en la ventana vincular con la celda, por ejemplo, voy a poner la celda R1.
Por ejemplo, vamos a seleccionar, el año 2017, podemos ver en la celda vinculada el valor 3.
Con la siguiente función, lo que conseguimos, es los totales por fila.
=BYROW (A2:G18; LAMBDA (matriz; SUMA (matriz)))
Pero, lo que queremos es el total del año que seleccionemos.
¿Qué es lo que debe de cambiar?
Pues, el argumento matriz de la función BYROW, es decir, el rango de datos.
En Excel, tenemos dos funciones que son DIRECCION, e, INDIRECTO.
La función DIRECCION, especificando un numero de fila, y, un numero de columna, nos devuelve la referencia, pero en formato de texto.
Por ejemplo, si escribo la siguiente expresión, donde como argumento fila, pongo la celda vinculada, del cuadro combinado, teniendo seleccionado el año 2017.
=DIRECCION(1;S1)
Me devuelve C1, en absoluto, pero nos lo devuelve en formato de texto.
Con la función INDIRECTO, capturamos el valor del valor devuelto por la función DIRECCION.
=INDIRECTO(DIRECCION(1;S1))
Obtengo el valor de la celda C1, que es el nombre del encabezado del año, que hemos seleccionado.
Pues, estas son las funciones, que debemos de usar para que el rango dentro de la función BYROW, sea dinámico.
Para especificar el argumento, array, de la función BYROW, vamos a usar las funciones DIRECCION, e, INDIRECTO, que hemos usado anteriormente.
=BYROW(INDIRECTO(DIRECCION(1;S1))
Pero, en el argumento array, debemos de especificar un rango, y, de momento, solo hemos puesto, el inicio del rango, pues, para el segundo valor, va a ser la misma función, lo único que va a cambiar es la fila, que va a ser la 18, que es donde termina.
=BYROW(INDIRECTO(DIRECCION(1;S1)):INDIRECTO(DIRECCION(18;S1))
Con esto, ya tenemos el rango dinámico, es decir, el argumento matriz de la función BYROW, el resto de la función es igual, es decir, la función LAMBDA, donde definimos una variable, que va a almacenar el resultado de la función INDIRECTO, y, como argumento, calculo, sumamos la variable.
=BYROW(INDIRECTO(DIRECCION(1;S1)):INDIRECTO(DIRECCION(18;S1));LAMBDA(matriz;SUMA(matriz)))
Obtenemos una matriz desbordada, con los valores del año seleccionado.
Pero, como, lo que queremos es el total, englobamos la función dentro de la función SUMA.
=SUMA(BYROW(INDIRECTO(DIRECCION(1;S1)):INDIRECTO(DIRECCION(18;S1));LAMBDA(matriz;SUMA(matriz))))
Ya lo tenemos.
Con esto, la fórmula es un poco grande, pero podemos crear una formula personalizada, para ello, seleccionamos las funciones INDIRECTO, y, DIRECCION.
=SUMA(BYROW(INDIRECTO(DIRECCION(1;S1)):INDIRECTO(DIRECCION(18;S1));LAMBDA(matriz;SUMA(matriz))))
Pulsamos CTRL mas C, para copiar.
Vamos a la ficha de fórmulas, dentro del grupo nombres definidos, hacemos clic en asignar nombre.
Le ponemos un nombre, en la ventana nombre, y, en la ventana se refiere a:, pegamos la función, pulsando CTRL más V, y, aceptamos.
Si, ahora, modificamos la función, borramos las funciones DIRECCION e INDIRECTO, y, ponemos, el nombre de rango creado, veremos que funciona exactamente igual, pero la sintaxis es más corta.
=SUMA(BYROW(rango;LAMBDA(matriz;SUMA(matriz))))
Ahora, vamos a poner un encabezado a los datos, que va a ser el año seleccionado.
Como ya tenemos la celda vinculada con el valor devuelto por el cuadro combinado, podemos usar la función INDICE, para rescatar el valor del encabezado, para ello, el argumento número de fila, lo omito, porque no solo nos vamos a ver en columnas, para ello, pulso, punto y coma, y, paso al siguiente argumento, que es número de columna, donde marcamos la celda vinculada del cuadro combinado, y, veremos, como cuando seleccionemos un año, aparece en el encabezado del año seleccionado.
=INDICE(A1:G1;;S1)
Pero, si lo que queremos, es ver los totales, desde el año que seleccionemos hasta el último año, lo único que debemos de cambiar es el segundo rango, y, poner fila 18, y, columna 7.
=SUMA(BYROW(INDIRECTO(DIRECCION(1;S1)):INDIRECTO(DIRECCION(18;7));LAMBDA(matriz;SUMA(matriz))))
Kommentare