Estoy intentando crear un cronograma para un número limitado de capacitaciones (alrededor de 50) que se distribuyen en un número limitado de lugares (alrededor de 15) y estoy buscando un enfoque más inteligente que el que estoy usando actualmente.
El cronograma consta de dos hojas de cálculo:
La hoja de cálculo 1 contiene los datos de las capacitaciones. Una fila por entrenamiento. Con columnas para el nombre del lugar de capacitación, fecha de inicio y fecha de finalización.
Se supone que la hoja de cálculo 2 es una representación visual de los datos de la hoja de cálculo 1 que se adapta a los cambios en los datos originales. Hasta ahora contiene una columna para cada día del año y una fila para cada lugar de capacitación. Entonces, cada celda representa una fecha específica en un lugar de capacitación específico.
El objetivo es que si la hoja 1 dice que se está llevando a cabo una capacitación en el lugar AZdesde 1 April 2017hasta 27 April 2017, las celdas que representan las fechas desde 1 Aprilhasta 27 Aprilen la fila del lugar de capacitación AZen la hoja 2 se marquen mediante formato condicional.
La función de formato condicional que estoy usando actualmente para la fila que representa el lugar de capacitación AZsería:
=IF(OR(AND(Sheet1!$C$5="AZ";Sheet1!$F$5>=B$6;Sheet1!$E$5<=B$6);AND(Sheet1!$C$6="AZ";Sheet1!$F$6>=B$6;Sheet1!$E$6<=B$6); ...... AND(Sheet1!$C$50="AZ";Sheet1!$F$50>=B$6;Sheet1!$E$50<=B$6));TRUE;FALSE)
Entonces, la parte repetitiva AND verifica para cada celda si la fecha de su columna (contenida en la fila 6) se encuentra dentro de la fecha de inicio y finalización de cualquiera de las capacitaciones en la hoja 1 y si esta capacitación se lleva a cabo en el lugar de capacitación AZ. Si esto es cierto para cualquiera de los entrenamientos en la hoja 1, la función SI es verdadera y la celda está marcada.
Sorprendentemente, esto está funcionando hasta ahora. Sin embargo, es una solución poco elegante y que consume mucho tiempo. Así que ahora estoy buscando una manera de dejar de tener que repetir la parte Y de la función 50 veces para cada uno de los 15 lugares de entrenamiento.
Una búsqueda exhaustiva en Google sugirió que las fórmulas de matriz podrían ser un camino a seguir, y que el formato condicional se trata por defecto como una fórmula de matriz, así que probé:
=IF(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54));TRUE;FALSE)
y
=IF(OR(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54)));TRUE;FALSE)
Pero hasta ahora sin suerte. Creo que es posible que no haya comprendido completamente cómo funcionan las fórmulas matriciales. Entonces, si ha detectado algún error obvio en mi función, tiene alguna idea sobre lo que podría intentar o puede sugerir algún enfoque alternativo a lo que estoy tratando de hacer, hágamelo saber.
De lo contrario, podría perder la cabeza copiando y adaptando esta maldita sección AND hasta el infinito.