Диапазон дат в Excel Query для значения

Question

Пытаясь запросить документ Excel, чтобы определить, сколько раз появляется значение, я могу включить условное форматирование и тепловую карту, чтобы определить, сколько раз ресурс занят в течение даты.

У меня есть январь-декабрь 31 в отдельных ячейках, мне нужно запросить данные, разделенные на три столбца, следующим образом

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

В формуле необходимо использовать дату начала в столбце A и дату окончания в столбце B и подсчитать вхождение имени ресурса в столбец C.

Таким образом, мой вывод будет помещен в мою тепловую карту следующим образом.

        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

Если бы можно было расширить без необходимости делать 365 формул для каждого ресурса, это было бы здорово.

Answer 1

Я думаю, что правильно понимаю ваш вопрос. Я интерпретирую это так, что вы ищете способ расширить свой вклад в "тепловую карту", но, возможно, я неправильно его читаю. Дайте мне знать, если я ошибаюсь.

Это классический случай, когда SUMPRODUCT потрясающий.

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

Который вы можете копировать снова и вниз (см. Изображение). Вам, очевидно, нужно будет соответствующим образом отредактировать диапазоны, чтобы они соответствовали вашим реальным данным.

Это работает так. Рассмотрим Боба 01.01.2016. Он берет дату и сравнивает дату с начальной датой и создает массив значений true/false, основываясь на том, больше ли дата совпадает с начальной датой в массиве начальной даты или равна ей. Затем он делает то же самое, меньше или равно конечным датам в массиве конечных дат. Затем он проверяет массив ресурсов для Бобса. В конце у вас есть три массива:

{TRUE, FALSE, FALSE} * {TRUE, TRUE, TRUE} * {TRUE, FALSE, TRUE} -> {TRUE, FALSE, FALSE}. * Является оператором И, поэтому любое место, где встречаются ИСТИНА, ИСТИНА, ИСТИНА, равно 1, а если любое ЛОЖЬ - нулю. Затем он приводит ваш массив ИСТИНА, ЛОЖЬ, ЛОЖЬ к 1, 0, 0 и суммирует результат!

Изменить: Вот способ решения того, о чем мы говорим в комментариях.