Para el siguiente ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente, pero algo mas corto, donde aparecen los fines de semana con cero cantidad.
El modelo esta en formato de tabla, y, se llama ventas.
Queremos crear un informe, o, reporte donde no aparezcan los fines de semana.
Primero, tengo que extraer el día de la semana de cada fecha, para ello, uso la función TEXTO donde como argumento valor, seleccionamos la columna de fecha, y, como argumento formato, entre comillas dobles, ponemos “DDDD”.
Cerramos paréntesis.
=TEXTO(Ventas[Fecha];"dddd")
Aceptamos, y, tenemos el día de la semana para cada fecha.
Donde vemos sábados y domingos.
Lo siguiente es usar el operador lógico O, que devuelve VERDADERO con que una de las condiciones se cumpla, pero no podemos usarlo de forma matricial, tendríamos que arrastrar la función, pero tenemos una función matricial llamada BYROW, que nos va a permitir usar el operador lógico O de forma matricial.
Vamos a poner BYROW y abrimos un paréntesis.
Como argumento array es la función TEXTO.
=BYROW(TEXTO(Ventas[Fecha];"dddd")
Punto y coma, ponemos la función LAMBDA, abrimos un paréntesis, y, creamos una variable, dicha variable almacena la función TEXTO.
=BYROW(TEXTO(Ventas[Fecha];"dddd");LAMBDA(a
Punto y coma, como argumento calculo, ponemos el operador lógico O, abrimos un paréntesis, ponemos la variable A y la igualamos a sábado entre comillas dobles.
=BYROW(TEXTO(Ventas[Fecha];"dddd");LAMBDA(a;O(a="sábado"
Punto y coma, ponemos de nuevo la variable A y la igualamos a domingo entre comillas dobles, cerramos paréntesis del operador lógico O.
=BYROW(TEXTO(Ventas[Fecha];"dddd");LAMBDA(a;O(a="sábado";a="domingo")
Cerramos paréntesis de LAMBDA y de BYROW.
=BYROW(TEXTO(Ventas[Fecha];"dddd");LAMBDA(a;O(a="sábado";a="domingo")))
Aceptamos.
Tenemos una matriz desbordada con VERDADERO donde es sábado, o, domingo, y, FALSO donde no lo es.
Los valores FALSOS son los que nos interesan, por lo que vamos a usar la función FILTRAR, donde como argumento array es la tabla ventas.
=FILTRAR(Ventas
Punto y coma, como argumento include, es la función BYROW siempre que sea igual a FALSO.
Cerramos paréntesis.
=FILTRAR(Ventas;BYROW(TEXTO(Ventas[Fecha];"dddd");LAMBDA(a;O(a="sábado";a="domingo")))=FALSO)
Aceptamos, y, ya tenemos nuestro informe sin los fines de semana.
Para traernos los encabezados, vamos a usar la función APILARV (VSTACK), donde como argumento matriz1, son los encabezados.
=APILARV(Ventas[#Encabezados]
Punto y coma, como argumento matriz2, es la expresión anterior.
Cerramos paréntesis.
=APILARV(Ventas[#Encabezados];FILTRAR(Ventas;BYROW(TEXTO(Ventas[Fecha];"dddd");LAMBDA(a;O(a="sábado";a="domingo")))=FALSO))
Aceptamos, y, vemos como tenemos el encabezado dentro de la matriz anterior.
Copiamos el formato de uno de los encabezados de la tabla ventas, y, lo pegamos en el encabezado de la matriz desbordada obtenida.
Seleccionamos todas las celdas de la matriz desbordada.
Vamos a la pestaña inicio, desplegamos formato condicional, y, seleccionamos nueva regla.
Se abre la ventana nueva regla de formato, seleccionamos la ultima opción, que es la que nos permite introducir una formula, o, función.
Vamos a poner bordes de forma dinámica, vamos a la ventana de función, y, ponemos =K3 <> “”.
Hacemos clic en formato.
Vamos a la pestaña borde, y, marcamos contorno.
Aceptamos, y, ya tenemos los bordes dinámicos.
Ahora, vamos a dar formato a las filas impares, vamos a hacerlo en la columna siguiente a la ultima de la matriz desbordada obtenida.
Vamos a poner la siguiente expresión:
=FILA(K2)
Devuelve la fila de la celda K2, arrastramos, y, tenemos el numero de fila de cada fila.
Vamos a dar formato a las filas impares, por lo que después del signo igual, usamos la función ES.IMPAR, y, arrastramos.
=ES.IMPAR(FILA(K2))
Obtenemos VERDADERO cuando es impar, y, FALSO cuando no lo es.
Debemos de dar formato a los valores VERDADEROS, pues esta es la expresión en nueva regla de formato condicional, por lo que la seleccionamos y la copiamos, volvemos a desplegar formato condicional, y, seleccionamos nueva regla, en la ventana que se abre, seleccionamos la ultima opción, y, en la ventana de formula, pegamos dicha formula.
Hacemos clic en formato, vamos a la pestaña de relleno, y, le voy a poner un gris claro.
Aceptamos, y, ya tenemos el formato, también de forma dinámica.
Vamos a ver otra forma de hacerlo.
En este caso, vamos a usar la función DIAS.LAB, que nos devuelve la diferencia de días entre dos fechas, ignorando los fines de semana, y, días festivos, pero en este caso, la fecha de inicio y de fin van a ser las mismas, quiere decir que si la fecha que seleccionamos no es un día de fin de semana, nos va a devolver 1 día, y, si es fin de semana, nos devolverá 0 días, pero, igual que el operador lógico O, no podemos usarlo de forma matricial, por lo que volveremos a usar la función BYROW para poder usar la función DIAS.LAB como matricial.
Ponemos la función BYROW, donde como argumento array es la columna fecha, esta tabla se llama ventas2.
=BYROW(Ventas2[Fecha]
Punto y coma, ponemos la función LAMBDA, declaramos una variable, que almacena la columna fecha.
=BYROW(Ventas2[Fecha];LAMBDA(a
Punto y coma, ponemos la función DIAS.LAB, como argumento fecha inicial es la variable A, y, como argumento fecha final, volvemos a poner la variable A, omitimos el argumento días festivos.
Cerramos paréntesis.
=BYROW(Ventas2[Fecha];LAMBDA(a;DIAS.LAB(a;a)))
Aceptamos, y, tenemos una matriz desbordada con 1 donde el día es laborable, y, 0 donde no lo es.
Ahora, con la función FILTRAR, vamos a filtrar la tabla ventas2, siempre que el resultado de la función DIAS.LAB sea igual a 1.
=FILTRAR(Ventas2;BYROW(Ventas2[Fecha];LAMBDA(a;DIAS.LAB(a;a)))=1)
Aceptamos, y, ya tenemos la tabla ventas2 filtrada excluyendo los fines de semana, vemos que son los mismos resultados que para el ejemplo anterior.
Seguimos los pasos anteriores, para poner los encabezados, bordes y formato dinámicos.
Para el siguiente ejemplo, queremos que, si el día de la semana es sábado, que retrase un día, y, si es domingo que retrase dos días.
El modelo es el mismo, y, la tabla se llama ventas3.
Voy a preguntar que si el resultado de la función TEXTO es igual a sábado (prueba lógica).
=SI(TEXTO(Ventas3[Fecha];"dddd")="sábado"
En ese caso, a la columna fecha se le resta un día (valor si verdadero).
=SI(TEXTO(Ventas3[Fecha];"dddd")="sábado";Ventas3[Fecha]-1
Punto y coma, en caso contrario, pregunto si el resultado de TEXTO es domingo (valor si falso del primer SI).
=SI(TEXTO(Ventas3[Fecha];"dddd")="sábado";Ventas3[Fecha]-1;SI(TEXTO(Ventas3[Fecha];"dddd")="domingo"
En ese caso, a la columna fecha se resta dos días (valor si verdadero del segundo SI).
=SI(TEXTO(Ventas3[Fecha];"dddd")="sábado";Ventas3[Fecha]-1;SI(TEXTO(Ventas3[Fecha];"dddd")="domingo";Ventas3[Fecha]-2
Punto y coma, en caso contrario, que deje la fecha de columna fecha (valor si falso del segundo SI).
Cerramos paréntesis del segundo SI.
Cerramos paréntesis del primer SI.
=SI(TEXTO(Ventas3[Fecha];"dddd")="sábado";Ventas3[Fecha]-1;SI(TEXTO(Ventas3[Fecha];"dddd")="domingo";Ventas3[Fecha]-2;Ventas3[Fecha]))
Aceptamos.
Tenemos una matriz desbordada de fechas, donde vemos que donde era sábado, se le ha restado un día, y, donde era domingo, se le ha restado dos días.
Lo siguiente es traernos la tabla ventas3, pero la columna fecha no debemos de traerla, porque ya la tenemos, vamos a ver cómo hacerlo.
Voy a traerme los encabezados.
=Ventas3[#Encabezados]
Voy a preguntar que, si uno de los encabezados es igual a fecha, que ponga cero, en caso contrario, que ponga uno.
=SI(Ventas3[#Encabezados]="Fecha";0;1)
Obtenemos una matriz desbordada con cero donde es fecha y 1 donde no lo es.
Pues, esta función es el argumento include de la función FILTRAR que vamos a usar para traernos la tabla ventas.
La expresión quedaría:
=FILTRAR(Ventas3;SI(Ventas3[#Encabezados]="Fecha";0;1))
Vemos que se ha traído la tabla ventas, excepto, la columna fecha.
Seleccionamos y copiamos la expresión.
Volvemos a la expresión anterior:
=SI(TEXTO(Ventas3[Fecha];"dddd")="sábado";Ventas3[Fecha]-1;SI(TEXTO(Ventas3[Fecha];"dddd")="domingo";Ventas3[Fecha]-2;Ventas3[Fecha]))
Después del signo igual, ponemos la función APILARH (HSTACK), abrimos un paréntesis, como argumento matriz1, es la expresión anterior.
=APILARH(SI(TEXTO(Ventas3[Fecha];"dddd")="sábado";Ventas3[Fecha]-1;SI(TEXTO(Ventas3[Fecha];"dddd")="domingo";Ventas3[Fecha]-2;Ventas3[Fecha]))
Punto y coma, como argumento matriz2, es la función FILTRAR.
Cerramos paréntesis.
=APILARH(SI(TEXTO(Ventas3[Fecha];"dddd")="sábado";Ventas3[Fecha]-1;SI(TEXTO(Ventas3[Fecha];"dddd")="domingo";Ventas3[Fecha]-2;Ventas3[Fecha]));FILTRAR(Ventas3;SI(Ventas3[#Encabezados]="Fecha";0;1)))
Ya tenemos la tabla ventas3, con la nueva columna de fechas, seguido del resto de encabezados.
Seguimos los pasos anteriores, para poner el encabezado, bordes y formato dinámico.
Miguel Angel Franco Garcia
Comments