Диапазон дат запроса Excel для значения

Question

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

У меня есть данные с 1 января по 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(--($E2>=$A$2:$A$4)*($E2<=$B$2:$B$4)*(F$1=$C$2:$C$4))

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

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

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

Редактировать: Вот как можно решить то, о чем мы говорим в комментариях.