Intervalo de datas da consulta do Excel para Valor

Question

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

Eu tenho Jan1-Dec31 em células individuais Eu preciso consultar dados divididos entre três colunas como segue

       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 final na coluna B e contar a ocorrência do nome do recurso na coluna C.

Então minha saída iria para o meu Mapa de Calor da seguinte forma.

        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 precisar criar 365 fórmulas para cada recurso que seria ótimo.

    

Answer 1

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

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

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

Que você pode copiar para cima e para baixo (veja imagem). Você obviamente precisará editar seus intervalos de acordo com seus dados reais.

Funciona assim. Considere Bob 1/1/2016. Ele pega a data e compara a data com a data de início e cria uma matriz de verdadeiro / falso com base em se a data é 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 data final. Em seguida, ele verifica a matriz de recursos para Bobs. No final, você tem três matrizes:

{VERDADEIRO, FALSO, FALSO} {verdadeiro, verdadeiro, verdadeiro} verdadeiro, falso, verdadeiro {VERDADEIRO, FALSO, FALSO}. O * é um operador AND, portanto, qualquer lugar onde VERDADEIRO, VERDADEIRO, VERDADEIRO ocorre é um 1 e, se algum FALSO ocorrer, é um zero. Então, ele coage sua matriz TRUE, FALSE, FALSE para 1, 0, 0 e soma o resultado!

Edit:Aquiestáumamaneiradeabordaroqueestamosfalandonoscomentários.