Excel - Buscarv o Indice+Coincidir. Valor Exacto o Proximo (Fecha+Hora)

Buenos días,

Tengo un excel con 3 columnas, ( Fecha | Hora | Valor ) , de las que tengo que extraer, en unas nuevas columnas, la fecha, hora y el valor cada 15 minutos.


Es decir, tengo esto en la tabla origen:

Fecha | Hora | Valor
28/07/2021 | 12:00 | 125
28/07/2021 | 12:12 | 130
28/07/2021 | 12:15 | 142
28/07/2021 | 12:25 | 180
28/07/2021 | 12:39 | 163


Y me tendría que quedar tal que así en la tabla destino:

Fecha | Hora | Valor
28/07/2021 | 12:00 | 125
28/07/2021 | 12:15 | 142
28/07/2021 | 12:30 | 180



Si os fijáis, el valor 130 se omite, puesto que ya tengo el valor de las 12:00 y el valor de las 12:15.

En los casos en los que no haya un valor a esa hora exacta, debería coger el superior o inferior (el más cercano). En el ejemplo, para las 12:30, al no tener un valor exacto, cogeríamos el de las 12:25 que es el mar cercano.


En la tabla origen, tengo los campos
- Fecha
- Hora
- Cadena (fecha+hora)

Lo primero que hago es rellenar el campo "Cadena" en el que uno la fecha y hora en una cadena de texto con el formato deseado:



En la tabla destino, tengo los campos
- Fecha (se repite 96 veces [24horas/15minutos])
- Hora (cada 15min)
- Cadena (fecha+hora cada 15)



Ahora, comparo la cadena.destino con cadena.origen, para buscar el valor y rellenar el campo Valor de la tabla destino.

Para hacer esta comparación he intentado utilizar estas dos formulas:

**Esto es para los cuadros de la derecha, para realizar comprobaciones introduciendo el parametro a buscar en una casilla específica.

BuscarV

Valor Exacto:

Valor Cercano:


Indice + Coincidir

Valor Exacto:

Valor Cercano:



Para la tabla destino, utilizo la misma formula pero cogiendo los valores de la tabla en vez de una casilla especifica:

Valor Exacto:

Valor Cercano:

He probado también cogiendo el valor directamente de la columna Cadena, en la que ya tengo unida la fecha y la hora, Pero el resultado es el mismo:





Para algunas lineas funciona correctamente.. Me encuentra el valor y me lo escribe en su celda.

Pero en algunos casos, no se por que motivo exactamente, no me encuentra el valor y me muestra #N/D o cuando intento sacar un valor cercano, me muestra uno que no tiene nada que ver.

Hay que tener en cuenta que a veces el valor mas cercano, es unos minutos atrás, unas horas o incluso el día anterior.

Necesito solucionar esto, puesto que el archivo contiene 407567 líneas y es demasiado extenso como para solucionar una por una las casillas que no encuentran el valor correspondiente...



Os adjunto un excel, con valores ejemplo y las dos formulas utilizadas.




Creo que lo he dejado mas o menos claro, si algo no se ha entendido bien, preguntadme.


Muchas gracias de antemano
Saludos.

• Test.rar(15,6 MB)

Hola Jorge:
Te lo he solucionado con Tablas dinámicas que agrupan automáticamente por minutos, horas, días, meses, trimestres y años. Lo único que le falta son los cuartos de hora y te lo he hecho con múltiplo inferior en la base de datos.
Tienes la estructura completa, puedes hacerlo por meses, días, como quieras.
El archivo es muy grande y no cabe este es el enlace a mi OneDrive: https://1drv.ms/x/s!Ah7fIrE9mm7Wpy4UCNEnjDEGjdWV?e=zG1ppL


https://wordexperto.com/

Buenos días wordexperto,

Lo primero agradecerte la ayuda prestada, eres un genio.

No consigo descargarme el excel que me proporcionaste.
Cuando entro a tu enlace de onedrive intenta abrírmelo directamente y me da error porque pesa mucho. Entonces no puedo abrirlo ni descargarlo, me obliga a cerrar la ventana.



Respecto a la solución propuesta, en la primera imagen, veo resultado a la 1:15, a la 1:30, pero no para la 1:45 ni las 2:00.
¿Entiendo que es porque a esa hora no hay un valor exacto y no está seleccionando el más aproximado?
Es uno de los requerimientos que necesito, que cuando no haya un valor exacto para esa hora, coja el más próximo.

Al no haberlo podido descargar y probar, no se si está haciendo esto o no, simplemente me baso en las imágenes.

En realidad no necesitaba que se agruparan los valores, pero bueno, tener esa función a mayores está genial.

La lista que mandé será el formato final, y de ahí saldrán gráficos, por eso en principio no necesitaba agrupaciones ni filtros.
Simplemente recoger los valores a las horas indicadas.

Sí que hay soluciones para todas las horas. Desdela 1:00 hasta la 1:15 son 1037,552 y para la 1:45 hasta las 2 son 1558,25.
Voy a quitar la fórmula de la base de datos y la mitad de los registros, a ver si cabe. Ahí lo tienes, al final me he pasado con los recortes.
https://www.youtube.com/channel/UCxgRcbeR2q4PpE1Pgft74Iw

• Test.rar(5,6 MB)

Buenos días wordexperto.com,

No entiendo la solución que me facilitaste. Lo siento pero soy un torpe con esto del excel.. jaja

No se si es que no esta realizando lo que yo necesito realmente, no lo sé interpretar o no se hacerlo funcionar.


En el libro "New2" la columna que creaste llamada "Cuartos de hora", lo que va cada 15 son los segundos, no los minutos.
Date cuenta que el primer registro que aparece "0:01:18" , serían las 00horas 01 minutos 18 segundos.
Es decir, se han modificado los segundos, pero no los minutos que son los que tienen que guardar el valor que corresponda con una frecuencia de 15 minutos.

Además, Si no me equivoco, tu me estás devolviendo la cantidad de registros que hay para una fecha, hora, trimestre.. etc. Y la suma de esto.


Como comenté en un primer momento lo que necesito es la tabla igual que la que mandé en el primer excel (la de color azul).
Es decir, rellenar esa misma tabla pero correctamente.
Que los #N/D que me muestra por algún motivo en la columna "Valor Exacto" (no hace bien la comparación o no lo entiendo..) los sustituya por el valor exacto, o en su defecto si no lo hay, por el valor más próximo en su columna correspondiente.

Tener en cuenta también, que a la hora de comparar solo lo hago por HH:MM, los segundos no me interesan, es una parte del dato de la que podemos prescindir.

En ningún momento necesito, agrupar, sumar, etc.... Solo buscar, comparar y rellenar la celda correspondiente.


Muchas gracias de antemano,
Saludos.

No entendí lo que querías hacer.
En tu tabla azul, el primer error N/D (No disponible) es porque no está. Por eso no lo encuentra. Mira el resultado de Evaluar fórmula.
el 1-1-2020 0:00 no está en la columna C
https://wordexperto.com/

No te preocupes.

Correcto, el primer dato no lo coge porque realmente no está. En este caso entonces, lo que me gustaría es que cogiese el más próximo (ya sea inferior o superior, aunque en este caso debería ser superior puesto que inferior no hay, pero bueno esto me daría relativamente igual, con tener uno me vale, independientemente de si es por encima o por debajo).

Pero si sigues bajando, verás ejemplos en los que no coge dato cuando realmente están 30 segundos por debajo de la hora exacta..

O por ejemplo, en la columna de "Valor Proximo" , llega un momento en el que muestra un valor, que ni es el exacto ni ninguno de los contiguos.. y además empieza a repetirse para todos los registros de ese día.




Por ejemplo:

Como ves en esta primera imagen, para el día 05/01/2021 a las 16:45 no encuentra un valor exacto, por tanto tendríamos que utilizar el valor aproximado.


El valor aproximado, como puedes ver en esta segunda imagen, sería el dato del día 05/01/2021 a las 16:44 (16:44:55), el cual tiene un valor de 133,7306, que no se porque motivo no es el que está mostrando.


De hecho, el valor que nos muestra (que ademas se viene repitiendo en toda la tira de registros de ese día) buscándolo manualmente correspondería al registro del 04/11/2020 a las 23:59:22, que poco o nada tiene que ver con el registro que estábamos buscando.
Te muestro el registro



Si necesitas comprobar algo o hacer pruebas, son registros que están en el propio documento.

A ver si podemos dar con la solución,
Muchisimas gracias,
Un saludo.

Por si te sirve para tu propósito la columna de los cuartos de hora solo tienes que cambiar el formato.
https://wordexperto.com/

Perdón, adjunte mal las fotos:

Primera imagen:


Segunda imagen:


Tercera imagen:



Te agradezco la modificación de los minutos por los segundos.
Aunque esa solución para este caso no me sirve, me he apuntado la función porque seguro que en otra ocasión la necesito.


Gracias

Sigue sin estar 16:45, el que está es 16:44:55. Por eso te sugerí que usaras la columna cuarto de hora para las búsquedas.
https://wordexperto.com/

Sigo sin entender qué necesitas en la columna azul si no es la agrupación por cuartos de hora.
https://wordexperto.com/

A ver si me explico.


Tabla naranja = ORIGEN
Tabla azul = DESTINO


Inicialmente yo tengo la tabla ORIGEN, que tiene las columnas Fecha, Hora y Valor



El primer paso que hago, es crear la columna CADENA en la tabla de origen, juntando fecha + hora.
Esta cadena nos servirá después para comparar la fecha y buscar el valor deseado.

En este momento la tabla queda tal que así:





La tabla DESTINO, en principio estaría vacía.



Creamos las columnas FECHA y HORA.

- Fecha, se repetirá durante 96 veces antes de pasar al día siguiente. (24 horas en periodos de 15 minutos, da un resultado de 96 registros).

- Hora, empieza a las 00:00 y va aumentando en periodos de 15 minutos, hasta completar las 24h y vuelta a empezar.


Creamos la columna CADENA que una vez más vuelve a ser la fecha + hora.

De esta manera conseguimos tener todas las fechas con sus 24 horas en periodos de 15 minutos.
Este campo nos servirá para saber que fecha queremos buscar en la tabla ORIGEN y que esta nos pueda devolver su valor asociado para guardarlo en la tabla DESTINO..


Creamos las columnas VALOR EXACTO y VALOR PRÓXIMO.

- Valor Exacto: se rellenará cuando la comparación entre Cadena.ORIGEN y Cadena.DESTINO sean iguales.
Es decir, cuando haya un registro que coincida la fecha y la hora teniendo en cuenta en la hora que solo nos quedamos con HH:MM, los segundos los descartamos (para que de esta manera sea mas fácil encontrar valores exactos).

- Valor Próximo: se rellenará con el dato más próximo (sea superior o inferior) a Cadena.DESTINO tras la comparación con Cadena.ORIGEN.
Es decir, cuando la comparación de las cadenas no encuentre esa fecha a esa hora no nos podrá devolver un valor exacto, por lo tanto cogeremos la fecha y hora más próxima a la que estamos evaluando y nos quedaremos con ese valor.

Una vez rellena la tabla debería quedar tal que así:




Una vez detallado el esquema con el que partimos (la tabla ORIGEN llena de registros y la tabla DESTINO vacía) empezamos con el crudo trabajo de filtrar estos datos.

Como comenté antes, lo que necesitamos es rellenar la tabla DESTINO.
Para rellenar esta tabla buscamos un valor cada 15 minutos a lo largo de todo el día y vamos rellenando le tabla DESTINO.

Cuando para una fecha y una hora no hay un registro (P.E: 05/01/2021 16:45) cogemos el registro más cercano encontrado para esa fecha y esa hora.

De esta manera conseguiríamos rellenar la tabla DESTINO con un valor (ya sea exacto o aproximado) para cada 15 minutos de cada día.



* Lo ideal además sería tener solo una columna VALOR, y que el mismo buscase el valor exacto y en caso de no encontrarlo (mostraría #N/D) automaticamente buscar el valor aproximado (sustituyendo el #N/D por el valor aproximado).

Pero esto es complicarlo más, entonces, con hacerlo funcionar así, de momento me vale.
Despues intentaría profundizar en controlar el N/D y sustituirlo.
Que si no me equivoco tendría que hacerlo con un =SI()..



La finalidad es, una vez hecho esto, realizar gráficos que nos irán mostrando la tendencia que sigue el valor cada cuarto de hora.

Espero haberme explicado bien ahora, porque creo que no puedo especificar más.
Si hay alguna duda, intenta concretarme más que es lo que no entiendes.

A ver si es esto.

Buenos días,

Lo primero gracias por tu tiempo.

En segundo lugar, creo que si a esa tabla dinámica, le añadimos un tercer campo, que nos mostrase el valor entonces sí sería lo que estoy buscando.

Un saludo

No he conseguido hacerlo desde la tabla dinámica, así que he pegado los valores de G en H, lo he ocultado, y voilà

https://www.youtube.com/channel/UCxgRcbeR2q4PpE1Pgft74Iw

No puedo subir el archivo, es muy grande, pero ya ves la estructura de la TD,