Intervalo de datas de consulta do Excel para valor

Question

Estou tentando consultar um documento Excel para determinar o número de vezes que um valor aparece, para que eu possa colocar uma formatação condicional e um mapa de calor para determinar o número de vezes que o recurso está ocupado durante uma data.

Tenho Jan1-Dec31 em células individuais. Preciso consultar dados divididos entre três colunas da seguinte maneira

       A       B       C
      Jan1    Jan12   Bob
      Jan2    Jan10   Roger
      Jan11   Jan14   Bob

A fórmula precisa usar a data de início na coluna A e a data de término na coluna B e contar a ocorrência do nome do recurso na coluna C.

Portanto, minha saída iria para meu mapa de calor da seguinte maneira.

        Bob  Roger
 Jan1    1    0

 Jan2    1    1

 Jan3    1    1

 Jan4    1    1

 Jan5    1    1

 Jan6    1    1

 Jan7    1    1

 Jan8    1    1

 Jan9    1    1

 Jan10   1    1

 Jan11   2    0

 Jan12   2    0

 Jan13   1    0

 Jan14   1    0

Se pudesse ser expandido sem ter que fazer 365 fórmulas para cada recurso, seria ótimo.

Answer 1

EUpensarEstou entendendo sua pergunta corretamente. A forma como estou interpretando é que você está procurando uma maneira de expandir sua entrada de "mapa de calor", mas posso estar lendo errado. Deixe-me saber se estou enganado.

Este é um caso clássico de onde SUMPRODUCT é incrível.

=SUMPRODUCT(--($E2>=$A$2:$A$4)*($E2<=$B$2:$B$4)*(F$1=$C$2:$C$4))

Que você pode copiar e copiar (veja a imagem). Obviamente, você precisará editar seus intervalos de acordo para ajustar seus dados reais.

Funciona assim. Considere o dia 01/01/2016 de Bob. Ele pega a data e a compara com a data de início e cria uma matriz de verdadeiro/falso com base no fato de a data ser ou não maior ou igual às datas de início na matriz de data de início. Em seguida, ele faz o mesmo, menor ou igual às datas de término na matriz de datas de término. Em seguida, ele verifica a matriz de recursos em busca de Bobs. No final, você tem três arrays:

{VERDADEIRO, FALSO, FALSO} * {VERDADEIRO, VERDADEIRO, VERDADEIRO} * {VERDADEIRO, FALSO, VERDADEIRO} -> {VERDADEIRO, FALSO, FALSO}. O * é um operador AND, portanto, qualquer lugar onde ocorrer TRUE, TRUE, TRUE é 1 e se ocorrer algum FALSE, será zero. Em seguida, ele força seu array TRUE, FALSE, FALSE para 1, 0, 0 e soma o resultado!

Editar: esta é uma maneira de abordar o que estamos falando nos comentários.