Vamos a trabajar con el modelo que suelo usar habitualmente, pero solo tenemos ventas para el mes de enero del año 2023, con una hora, dicha hora corresponde a la hora donde se realizó la venta.
El modelo esta en formato de tabla, y, se llama Ventas.
Queremos crear un informe, o, reporte con las horas de ventas para cada fecha.
En la celda I2, nos vamos a traer las fechas únicas, para ello, usamos la función UNICOS, como argumento matriz, usamos la función ENTERO para quedarnos con la parte entera, ya sabemos que una fecha y una hora, corresponde a un numero decimal, donde la parte entera corresponde a la fecha, y, la parte decimal a la hora, como argumento numero de la función ENTERO seleccionamos el rango A2:A20.
=UNICOS(ENTERO(A2:A20))
Aceptamos, y, tenemos las fechas únicas.
En la celda J2, usamos la función ENCONTRAR, como argumento texto buscado, usamos la función ENTERO, y, como argumento numero ponemos la celda I2.
=ENCONTRAR(ENTERO(I2)
Como argumento dentro del texto, seleccionamos el rango A2:A20, fijamos, y, cerramos paréntesis.
=ENCONTRAR(ENTERO(I2);$A$2:$A$20)
Aceptamos, tenemos una matriz desbordada con 1 donde hay coincidencia, 1 hace referencia a la posición donde se encontró coincidencia, y, error donde no hay conciencia.
Vamos a preguntar si es error el resultado de la función ENCONTRAR.
=ESERROR(ENCONTRAR(ENTERO(I2);$A$2:$A$20))
Obtenemos VERDADERO donde es error, y, FALSO donde no lo es.
Si delante de la función ESERROR, ponemos la función NO, invertirá el resultado, es decir, donde era VERDADERO pondrá FALSO, y, donde era FALSO pondrá VERDADERO.
Nos va a interesar los valores VERDADEROS.
Usamos el condicional SI, preguntamos si el resultado de la expresión anterior es iguala VERDADERO, que nos devuelva la fecha menos la parte entera, es decir, la hora, en caso contrario, que nos devuelva un texto en blanco.
=SI(NO(ESERROR(ENCONTRAR(ENTERO(I2);$A$2:$A$20)));Ventas[Fecha]-ENTERO(Ventas[Fecha]);"")
Lo ponemos en formato de hora.
Usamos la función UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, como argumento texto1, es la expresión anterior.
=UNIRCADENAS(" , ";VERDADERO;SI(NO(ESERROR(ENCONTRAR(ENTERO(I2);$A$2:$A$20)));Ventas[Fecha]-ENTERO(Ventas[Fecha]);""))
Aceptamos, y, tenemos las horas unidas separadas por coma, pero, vemos que las horas han perdido su formato.
Para solventar el problema, usamos la función TEXTO, como argumento valor es el condicional SI, como argumento formato, entre comillas dobles, ponemos “hh:mm”.
=UNIRCADENAS(" , ";VERDADERO;TEXTO(SI(NO(ESERROR(ENCONTRAR(ENTERO(I2);$A$2:$A$20)));Ventas[Fecha]-ENTERO(Ventas[Fecha]);"");"hh:mm"))
Aceptamos, arrastramos, y, ya tenemos las horas de ventas de cada fecha.
Veamos otro ejemplo, ahora, tenemos los siguientes datos.
El modelo esta en formato de tabla y se llama Ventas2.
Queremos crear un reporte, o, informe donde cada fecha aparezca dos veces, porque queremos repetir cada registro dos veces.
En la celda I2, usamos la función CONTARA, y, contamos la columna fecha.
=CONTARA(Ventas2[Fecha])
Nos devuelve 8.
Como queremos duplicar, multiplicamos el resultado de la función CONTARA por 2.
=CONTARA(Ventas2[Fecha])*2
Obtenemos 16, quiere decir que el informe estará compuesto de 16 fechas.
Con la función SECUENCIA, creamos una matriz vertical de 16 filas, por lo que solo usamos el argumento filas.
=SECUENCIA(CONTARA(Ventas2[Fecha])*2)
Para que aparezca una fecha dos veces, cada numero debe de aparecer dos veces, por lo que dividimos entre 2.
=SECUENCIA(CONTARA(Ventas2[Fecha])*2)/2
Ahora, empieza desde el numero decimal 0,5, y, va sumando a cada número 0,5.
Ahora, vamos a redondear hacia arriba, para ello, usamos la función REDONDEAR.MAS, como argumento número es la expresión anterior.
=REDONDEAR.MAS(SECUENCIA(CONTARA(Ventas2[Fecha])*2)/2
Como argumento número de decimales, ponemos 0, cerramos paréntesis.
=REDONDEAR.MAS(SECUENCIA(CONTARA(Ventas2[Fecha])*2)/2;0)
Aceptamos, y, vemos como aparece cada numero dos veces.
Tenemos las filas que debemos de recuperar, pues, después del signo igual, usamos la función INDICE, donde como argumento matriz es la tabla Ventas2.
=INDICE(Ventas2
Como argumento numero de fila es la función REDONDEAR.
=INDICE(Ventas2[Fecha];REDONDEAR.MAS(SECUENCIA(CONTARA(Ventas2[Fecha])*2)/2;0))
Como argumento numero de columna, usamos la función SECUENCIA, omitimos el argumento filas, como argumento columnas, contamos el número de encabezados.
=INDICE(Ventas2;REDONDEAR.MAS(SECUENCIA(CONTARA(Ventas2[Fecha])*2)/2;0);SECUENCIA(;CONTARA(Ventas2[#Encabezados])))
Aceptamos, y, vemos los registros duplicados.
Si queremos triplicar, pues, multiplicamos por 3, y, dividimos por 3.
=REDONDEAR.MAS(SECUENCIA(CONTARA(Ventas2[Fecha])*3)/3;0)
Ahora, queremos el informe, pero duplicado, es decir, primero debe de aparecer el informe, una línea de separación, y, de nuevo el informe.
La primera expresión que vamos a usar es la que hemos usado para obtener los números enteros, y, decimales.
=SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2
Usamos LET, creamos una variable, y, almacenamos la expresión.
=LET(a;SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2
Como argumento calculo de LET, usamos la función APILARV, como argumento matriz1, usamos la función FILTRAR, como argumento array, es la variable “a”, como argumento include, es la variable “a” siempre que sea igual a la parte entera de la variable “a”.
=LET(a;SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2;APILARV(FILTRAR(a;a=ENTERO(a))))
Cerramos paréntesis, aceptamos, y, tenemos los números de filas a rescatar.
Como argumento matriz2, ponemos un texto en blanco.
=LET(a;SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2;APILARV(FILTRAR(a;a=ENTERO(a));""))
Como argumento matriz3, volvemos a usar la función FILTRAR, como argumento array, usamos la función REDONDEAR, como argumento número, es la función FILTRAR, donde filtramos la variable “a”, siempre que dicha variable sea diferente, o, indistinto a la parte entera de la variable “a”, como argumento numero de decimales, ponemos cero.
Cerramos paréntesis.
LET(a;SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2;APILARV(FILTRAR(a;a=ENTERO(a));"";REDONDEAR.MAS(FILTRAR(a;a<>ENTERO(a));0)))
Aceptamos, tenemos una matriz desbordada en vertical con los registros a rescatar, un espacio, y, de nuevo los registros a rescatar.
Después del signo igual, usamos la función INDICE, como argumento matriz es el nombre de la tabla, como argumento numero de fila, es la expresión anterior.
=INDICE(Ventas3;LET(a;SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2;APILARV(FILTRAR(a;a=ENTERO(a));"";REDONDEAR.MAS(FILTRAR(a;a<>ENTERO(a));0)))
Como argumento número de columna, podíamos usar la función SECUENCIA, y, como argumento columnas, poner 7, pero no seria dinámico, lo vamos a hacer dinámico, para ello, como argumento columnas, usamos SECUENCIA, omitimos el argumento filas, como argumento columnas, usamos la función CONTARA, como argumento de CONTARA, ponemos el nombre de la tabla, abrimos un corchete, ponemos almohadilla (#), y, seleccionamos encabezados, cerramos corchete, cerramos paréntesis.
=INDICE(Ventas3;LET(a;SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2;APILARV(FILTRAR(a;a=ENTERO(a));"";REDONDEAR.MAS(FILTRAR(a;a<>ENTERO(a));0)));SECUENCIA(;CONTARA(Ventas3[#Encabezados])))
Aceptamos, y, ya tenemos nuestro modelo por duplicado, pero, en el argumento matriz2 de APILARV, vemos que nos devuelve un error.
Lo vamos a solventar con la función SI.ERROR, donde como argumento valor es la expresión anterior, y, como argumento valor si error, ponemos un texto en blanco.
=SI.ERROR(INDICE(Ventas3;LET(a;SECUENCIA(CONTARA(Ventas3[Fecha])*2)/2;APILARV(FILTRAR(a;a=ENTERO(a));"";REDONDEAR.MAS(FILTRAR(a;a<>ENTERO(a));0)));SECUENCIA(;CONTARA(Ventas3[#Encabezados])));"")
Aceptamos, y, ya lo tenemos.
Por último, a la fila en blanco, le vamos a dar un formato condicional, para ello, seleccionamos el rango I2:O18, vamos a la pestaña de inicio, desplegamos formato condicional, y, seleccionamos nueva regla.
En la ventana que se abre, seleccionamos la ultima opción, que es la que nos permite introducir una formula, o, función.
En la ventana de formula, ponemos I2=””, y, damos un formato.
Miguel Angel Franco
Comments