Rango de fechas de consulta de Excel para valor

Question

Intento consultar un documento de Excel para determinar la cantidad de veces que aparece un valor para poder aplicar formato condicional y un mapa de calor para determinar la cantidad de veces que el recurso está ocupado durante una fecha.

Tengo del 1 de enero al 31 de diciembre en celdas individuales. Necesito consultar datos divididos en tres columnas de la siguiente manera

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

La fórmula debe utilizar la fecha de inicio en la columna A y la fecha de finalización en la columna B y contar la aparición del nombre del recurso en la columna C.

Entonces mi resultado iría a mi mapa de calor de la siguiente manera.

        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

Si pudiera expandirse sin tener que hacer 365 fórmulas para cada recurso, sería genial.

Answer 1

IpensarEstoy entendiendo tu pregunta correctamente. La forma en que lo interpreto es que estás buscando una manera de expandir tu entrada de "mapa de calor", pero es posible que lo esté leyendo mal. Déjame saber si me equivoco.

Este es un caso clásico en el que SUMPRODUCT es asombroso.

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

Que puedes copiar arriba y abajo (ver imagen). Obviamente necesitarás editar tus rangos en consecuencia para que se ajusten a tus datos reales.

Funciona así. Considere el de Bob el 1/1/2016. Toma la fecha y la compara con la fecha de inicio, y crea una matriz de verdadero/falso en función de si la fecha es mayor o igual a las fechas de inicio en la matriz de fechas de inicio. Luego hace lo mismo, menor o igual que las fechas de finalización en la matriz de fechas de finalización. Luego verifica la matriz de recursos en busca de Bobs. Al final, tienes tres matrices:

{VERDADERO, FALSO, FALSO} * {VERDADERO, VERDADERO, VERDADERO} * {VERDADERO, FALSO, VERDADERO} -> {VERDADERO, FALSO, FALSO}. El * es un operador Y, por lo que cualquier lugar donde aparece VERDADERO, VERDADERO, VERDADERO es un 1 y si aparece FALSO es un cero. Luego, obliga a su matriz VERDADERO, FALSO, FALSO a 1, 0, 0 y suma el resultado.

Editar: aquí hay una manera de abordar lo que estamos hablando en los comentarios.