Para el siguiente ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente, algo mas corto, donde tenemos diferentes líneas donde no se realizaron ventas.
Nos solicitan un reporte con la venta realizada que se encuentre encima de una venta de cero, cuando hay mas de un cero seguido, para el primer valor cero, es la venta anterior, y, para el ultimo cero, es la venta siguiente.
El modelo está en formato de tabla, y, se llama Ventas.
Empecemos…
En la celda I2, ponemos la función LET, declaramos una variable, como valor a almacenar en dicha variable, nos traemos la columna total.
=LET(aa;Ventas[Total]
Declaramos otra variable, donde preguntamos (prueba lógica) que si la variable “aa” es igual a cero, en ese caso, (valor si verdadero), usamos la función INDICE, como argumento matriz, seleccionamos la columna G, como argumento numero de fila, usamos la función FILA, y, como argumento es la variable “aa”, pero restamos 1, para acceder a la fila anterior, como argumento número de columna, usamos la función SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 7, que son los encabezados que tenemos, como argumento valor si falso, ponemos 0, probamos la variable.
=LET(aa;Ventas[Total];b;SI(aa=0;INDICE(A:G;FILA(aa)-1;SECUENCIA(;7));0);b)
Aceptamos, y, tenemos una matriz desbordada con el registro anterior a donde hay un valor cero, y, cero donde no hay coincidencia.
Ya hemos rescatado los registros cuya columna total de registro siguiente es igual a cero, ahora, nos queda rescatar el registro después del último cero.
Para conseguirlo, debemos de rescatar los registros, pero, desde el ultimo registro, o, fila al primer registro.
Creamos otra variable, usamos la función SECUENCIA, como argumento filas, usamos la función CONTARA, como argumento valor1, ponemos la variable “aa”, y, sumamos 1, omitimos el argumento columnas, como argumento inicio, vuelve a ser la función CONTARA, como argumento paso, ponemos -1.
Probamos la variable.
Tenemos una matriz desbordada en vertical desde el numero 26, que es la ultima fila ocupada, hasta el número 1.
Vamos a crear otra variable, donde nos vamos a traer la columna total, pero desde la fila 26 a la fila 1, para ello, usamos la función INDICE, como argumento matriz, ponemos la columna G, como argumento numero de fila, es la variable “c”, omitimos el argumento numero de columna, probamos variable.
=LET(aa;Ventas2[Total];b;SI(aa=0;INDICE(A:G;FILA(aa)-1;SECUENCIA(;7));0);c;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1);d;INDICE(G:G;c);d)
Tenemos una matriz desbordada con la columna total desde la fila 26 a la fila 1.
Creamos otra variable, preguntamos si la variable “d” es igual a 0 (prueba lógica), en ese caso, (valor si verdadero), usamos la función INDICE, como argumento matriz, son las columnas A:G, como argumento numero de fila, es la variable “c”, pero, sumamos1, como argumento numero de columna, usamos la función SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 8, probamos variable.
=LET(aa;Ventas2[Total];b;SI(aa=0;INDICE(A:G;FILA(aa)-1;SECUENCIA(;7));0);c;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1);d;INDICE(G:G;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1));e;SI(d=0;INDICE(A:G;c+1;SECUENCIA(;7));0);e)
Hemos obtenido una matriz desbordada desde la fila 26 a la fila 1, donde tenemos el registro siguiente al ultimo cero.
Creamos otra variable, usamos APILARV, como argumento matriz1, es la variable “b”, como argumento matriz2, es la variable “e”), probamos la variable.
=LET(aa;Ventas2[Total];b;SI(aa=0;INDICE(A:G;FILA(aa)-1;SECUENCIA(;7));0);c;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1);d;INDICE(G:G;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1));e;SI(d=0;INDICE(A:G;c+1;SECUENCIA(;7));0);f;APILARV(b;e);f)
Filtramos la función APILARV, siempre que sea diferente a 0.
=LET(aa;Ventas2[Total];b;SI(aa=0;INDICE(A:G;FILA(aa)-1;SECUENCIA(;7));0);c;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1);d;INDICE(G:G;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1));e;SI(d=0;INDICE(A:G;c+1;SECUENCIA(;7));0);f;FILTRAR(APILARV(b;e);INDICE(APILARV(b;e);;1)<>0);f)
Ya lo tenemos.
Aparecen los registros que están debajo del valor 0, después, aparecen los registros que están por encima del valor cero.
Usamos APILARV, y, nos traemos los encabezados.
=LET(aa;Ventas[Total];b;SI(aa=0;INDICE(A:G;FILA(aa)-1;SECUENCIA(;7));0);c;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1);d;INDICE(G:G;SECUENCIA(CONTAR(aa)+1;;CONTAR(aa)+1;-1));e;SI(d=0;INDICE(A:G;c+1;SECUENCIA(;7));0);f;FILTRAR(APILARV(b;e);INDICE(APILARV(b;e);;1)<>0);APILARV(Ventas[#Encabezados];f))
Miguel Angel Franco
Commentaires