Estou tentando criar um cronograma para um número limitado de treinamentos (cerca de 50) distribuídos entre um número limitado de locais (cerca de 15) e procuro uma abordagem mais inteligente do que a que estou usando atualmente.
O cronograma consiste em duas planilhas:
A planilha 1 contém os dados dos treinamentos. Uma linha por treino. Com colunas para nome do local de treinamento, data de início e data de término.
A planilha 2 deve ser uma representação visual dos dados da planilha 1 que se adapta às alterações nos dados originais. Até agora contém uma coluna para cada dia do ano e uma linha para cada local de treinamento. Portanto, cada célula representa uma data específica em um local de treinamento específico.
O objetivo é que se a planilha 1 indicar que um treinamento está acontecendo no local AZde 1 April 2017até 27 April 2017, as células que representam as datas de 1 Aprilaté 27 Aprilna linha do local de treinamento AZda planilha 2 sejam marcadas por meio de formatação condicional.
A função de formatação condicional que estou usando atualmente para a linha que representa o local de treinamento AZseria:
=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)
Portanto, a parte repetitiva AND verifica para cada célula se a data de sua coluna (contida na linha 6) está dentro da data de início e término de algum dos treinamentos na planilha 1 e se esse treinamento ocorre no local de treinamento AZ. Se isso for verdade para qualquer um dos treinamentos da planilha 1, a função SE é verdadeira e a célula é marcada.
Surpreendentemente, isso está funcionando até agora. No entanto, é uma solução muito demorada e deselegante. Então agora estou procurando uma maneira de parar de repetir a parte AND da função 50 vezes para cada um dos 15 locais de treinamento.
Uma extensa pesquisa no Google sugeriu que as fórmulas de matriz podem ser um caminho a percorrer e que a formatação condicional é tratada por padrão como uma fórmula de matriz, então tentei:
=IF(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54));TRUE;FALSE)
e
=IF(OR(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54)));TRUE;FALSE)
Mas até agora sem sorte. Acho que talvez não tenha compreendido totalmente como funcionam as fórmulas de matriz. Portanto, se você detectou algum erro óbvio em minha função, tem alguma ideia sobre o que eu poderia tentar ou pode sugerir abordagens alternativas para o que estou tentando fazer, por favor me avise.
Caso contrário, posso perder a cabeça copiando e adaptando esta maldita seção AND ad infinitum.