Una lectora hace unos días me planteaba la forma de mostrar en una agenda (construida en Excel) las diferentes tareas programadas, según hora y sala de reunión.
Después de darle unas vueltas, conseguí solucionar el problema (sin macros), empleando unas fórmulas matriciales en columnas auxiliares.
Comencemos el desarrollo viendo cual es el origen de datos del que partimos: Una tabla de información con cinco campos (tabla que he llamado TblRegistros):
Nuestro objetivo es llegar a la siguiente imagen, esto es, a conseguir se complete la distribución de las tareas programadas según sala, día y hora:
Para ello crearemos las siguientes columnas auxiliares.
Comenzaremos por la columna G, con la siguiente matricial (seleccionando el rango completo G5:G25):
=INDICE(TblRegistros;COINCIDIR(Hoja1!$C$3+Hoja1!$B$5:$B$25&sala;TblRegistros[FECHA Y HORA]&TblRegistros[SALA];0);4)
De igual forma seleccionando H5:H25 introducimos la matricial:
=INDICE(TblRegistros;COINCIDIR(Hoja1!$C$3+Hoja1!$B$5:$B$25&sala;TblRegistros[FECHA Y HORA]&TblRegistros[SALA];0);5)
(recuerda ejecutarlas matricialmente!!!)
Con estas funciones obtenemos el Inicio y Final de cada tarea desde el origen de datos, en concreto la información registrada para la Tarea concreta en las columnas Hora Inicio y Hora Término
En la siguiente etapa completamos la columna E, escribimos la función BUSCARV en su forma de búsqueda aproximada en la celda E5 para luego arrastrar hasta E25:
=BUSCARV(B5;$G$5:$G$25;1;1)
conseguimos, entonces, detallar para cada hora del día la Hora de Inicio:
Como última columna auxiliar obtendremos, para cada hora del día, su correspondiente Hora de Término, lo que conseguimos incluyendo en F5 y luego arrastrando hasta F25 la fórmula:
=INDICE($H$5:$H$25;COINCIDIR(E5;$G$5:$G$25;1))
Con todas estas columnas auxiliares de cálculo tenemos lo que necesitamos, ya que hemos identificado para cada hora del día, si tiene asignada una Tarea (por sala de reunión y día) y por ende su Hora Inicio y Hora Término. Sólo nos queda realizar una comparativa de horas y rellenar con la Tarea su bloque horario definido.
Para ello, y por último insertamos en la celda C5 la siguiente fórmula matricialmente ejecutada:
=SI.ERROR(SI(Y($B5>=E5;$B5>F5);INDICE(TblRegistros;COINCIDIR(C$3+$E5&sala;TblRegistros[FECHA Y HORA]&TblRegistros[SALA];0);3);"");"")
Lo primero que aplicamos con esta fórmula es un condicional que nos confirme si la hora del día está o no dentro del intervalo horario de la Tarea (del Día y Sala elegida en C1 y C3), en caso afirmativo, buscamos la combinación de 'Día_Hora_Sala' en la Tabla de Registros (TblRegistros), obteniendo con la función INDICE la descripción de la Tarea...
Esta fórmula la copiaremos y pegaremos al resto del rango C6:C25 (ojo es una fórmula matricial!).
Listo, hemos finalizado el trabajo.. ahora somos capaces de ver la distribución de tareas por todo el tramado horario de nuestra Agenda en Excel.
Después de darle unas vueltas, conseguí solucionar el problema (sin macros), empleando unas fórmulas matriciales en columnas auxiliares.
Comencemos el desarrollo viendo cual es el origen de datos del que partimos: Una tabla de información con cinco campos (tabla que he llamado TblRegistros):
Nuestro objetivo es llegar a la siguiente imagen, esto es, a conseguir se complete la distribución de las tareas programadas según sala, día y hora:
Para ello crearemos las siguientes columnas auxiliares.
Comenzaremos por la columna G, con la siguiente matricial (seleccionando el rango completo G5:G25):
=INDICE(TblRegistros;COINCIDIR(Hoja1!$C$3+Hoja1!$B$5:$B$25&sala;TblRegistros[FECHA Y HORA]&TblRegistros[SALA];0);4)
De igual forma seleccionando H5:H25 introducimos la matricial:
=INDICE(TblRegistros;COINCIDIR(Hoja1!$C$3+Hoja1!$B$5:$B$25&sala;TblRegistros[FECHA Y HORA]&TblRegistros[SALA];0);5)
(recuerda ejecutarlas matricialmente!!!)
Con estas funciones obtenemos el Inicio y Final de cada tarea desde el origen de datos, en concreto la información registrada para la Tarea concreta en las columnas Hora Inicio y Hora Término
En la siguiente etapa completamos la columna E, escribimos la función BUSCARV en su forma de búsqueda aproximada en la celda E5 para luego arrastrar hasta E25:
=BUSCARV(B5;$G$5:$G$25;1;1)
conseguimos, entonces, detallar para cada hora del día la Hora de Inicio:
Como última columna auxiliar obtendremos, para cada hora del día, su correspondiente Hora de Término, lo que conseguimos incluyendo en F5 y luego arrastrando hasta F25 la fórmula:
=INDICE($H$5:$H$25;COINCIDIR(E5;$G$5:$G$25;1))
Con todas estas columnas auxiliares de cálculo tenemos lo que necesitamos, ya que hemos identificado para cada hora del día, si tiene asignada una Tarea (por sala de reunión y día) y por ende su Hora Inicio y Hora Término. Sólo nos queda realizar una comparativa de horas y rellenar con la Tarea su bloque horario definido.
Para ello, y por último insertamos en la celda C5 la siguiente fórmula matricialmente ejecutada:
=SI.ERROR(SI(Y($B5>=E5;$B5>F5);INDICE(TblRegistros;COINCIDIR(C$3+$E5&sala;TblRegistros[FECHA Y HORA]&TblRegistros[SALA];0);3);"");"")
Lo primero que aplicamos con esta fórmula es un condicional que nos confirme si la hora del día está o no dentro del intervalo horario de la Tarea (del Día y Sala elegida en C1 y C3), en caso afirmativo, buscamos la combinación de 'Día_Hora_Sala' en la Tabla de Registros (TblRegistros), obteniendo con la función INDICE la descripción de la Tarea...
Esta fórmula la copiaremos y pegaremos al resto del rango C6:C25 (ojo es una fórmula matricial!).
Listo, hemos finalizado el trabajo.. ahora somos capaces de ver la distribución de tareas por todo el tramado horario de nuestra Agenda en Excel.
Excelente!!!, me funcionó de maravilla Ismael =) . En serio muchas gracias por el apoyo y el interés prestado, eres un gran tipo. Un saludo.
ResponderEliminarGracias a tí Erika!
EliminarSaludos cordiales
AMIGO ME SALE #VALOR?
ResponderEliminartienes una foto de la formula de la columna g y h que no entiendo que toma como sala
ResponderEliminarHoja1!$B$5:$B$25&sala; ese sala a que pertenece?
ResponderEliminarJorge,
Eliminarrecuerda ejecutar las fórmulas matricialmente (presiona Ctrl+Mayusc+Enter en lugar de enter).
las columnas E,F, G y H son columnas auxiliares que toman la sala determinada por la celda C1 y el día de C3.
Saludos
Muchas gracias por tu aporte, funciono bien claro yo le agregue una sala mas, sin embargo me encontré con un problema, cuando coloco un registro nuevo en la tabla que se encuentre repetido pero con tareas distintas en una misma sala, me toma solo en primero que encuentra, mi pregunta podemos colocar una formula al lado del registro para que me indique que la sala a esa hora se encuentra ocupada o disponible?
ResponderEliminarHola Jorge,
Eliminaren teoría si sería posible.. pero habría que trabajarla bastante a partir de las matriciales descritas.
....