Блог

У меня есть список мест для проведения мероприятий, а также время и дата их проведения (например, начало: с 1 марта до 9 утра, окончание: с 1 марта до 10 утра). Я пытаюсь создать «тепловую карту» в Excel / Google Sheets, чтобы я мог узнать, как часто событие бронируется за каждые полчаса исторически.

Я пытаюсь ответить на вопрос: «Как часто пространство арендуется с 9 утра до 9:30 утра по воскресеньям». Но распространите это на каждый получасовой блок в каждый день недели. Затем я буду использовать некоторое условное форматирование для создания тепловой карты с помощью градиента.

             S   M   T   W   T   F   S                       
9:00 AM     X   X   X   X   X   X   X
9:30 AM     X   X   X   X   X   X   X
10:00 AM    X   X   X   X   X   X   X
10:30 AM    X   X   X   X   X   X   X
 

В приведенном выше примере я хотел бы иметь строку для каждого получаса (например, 9 утра, 9:30 утра, 10 утра и т. Д.) И Столбец с днем недели (например, воскресенье, понедельник, вторник и т. Д.). Внутри таблицы я хотел бычтобы найти формулу для замены Xs, подсчитайте, сколько раз пространство арендовалось для мероприятия в это время и день недели. Если он арендуется дважды с 9 утра до 9:30 утра в воскресенье, тогда X будет равно 2.

Я попробовал следующую формулу, но она не совсем правильно вычисляет вещи.

 =COUNTIFS($N:$N,">="amp;$A2,$O:$O,"<"amp;$A3,$P:$P,"="amp;B$1)
 

В приведенном выше примере / формуле таблица с необработанными данными выглядит следующим образом:

 Column N      Column O
Start Time    End Time
6:00:00 PM    10:00:00 PM
11:30:00 AM   1:30 PM
8:00:00 PM    8:30 PM
5:30:00 PM    6:00 PM
6:00:00 PM    8:00 PM
 

• A2 = 9:00 утра
• A3 = 9:30 утра
• B1 = воскресенье

вставьте в ячейку F1, перетащите вправо, а затем перетащите вниз:

 =COUNTA(IFERROR(FILTER($A$2:$A, $E2 >= $B$2:$B, 
                                $E2 <= $C$2:$C, 
                                F$1  = TEXT($A$2:$A, "ddd"))))