Функция Excel /VBA для поиска значения по дате и времени по двум критериям

Question

Я измерил данные, которые содержат данные о занятости здания. Есть несколько комнат и несколько комнатных зон в каждой комнате на каждом этаже.Номер комнаты содержит этаж, поэтому мне не нужно искать по этажам.

А данные о занятости имеют отметку времени, которая записывается, когда в этой конкретной области была активность.

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

Я могу найти INDEX MATCH, где они ищут несколько точных критериев, что здесь не применимо, так как мне нужно самое близкое совпадение для значений времени.

Любая помощь очень ценится! Меня больше всего интересовала бы функция, которая может выполнить это, но я также открыт для решений VBA.

Спасибо!

Вот ссылка на данные

http://jmp.sh/v/D907ef9W3055ThlR1ssa

| Date / Time    | DATE     | TIME  | FLOOR | LAB    | LAB AREA | Occupancy (0 = unoccupied) |
|----------------|----------|-------|-------|--------|----------|----------------------------|
| 9/3/2015 10:30 | 9/3/2015 | 10:30 | 9     | 99400  | A        | 0                          |
| 9/3/2015 10:30 | 9/3/2015 | 10:30 | 10    | 109400 | D        | 0                          |
| 9/3/2015 10:30 | 9/3/2015 | 10:30 | 8     | 89400  | A        | 0                          |
| 9/3/2015 10:31 | 9/3/2015 | 10:31 | 10    | 109400 | D        | 1                          |
| 9/3/2015 10:36 | 9/3/2015 | 10:36 | 8     | 89400  | B        | 0                          |
| 9/3/2015 10:38 | 9/3/2015 | 10:38 | 8     | 89400  | A        | 1                          |
| 9/3/2015 10:40 | 9/3/2015 | 10:40 | 10    | 109410 | A        | 1                          |
| 9/3/2015 10:41 | 9/3/2015 | 10:41 | 15    | 159400 | A        | 1                          |
| 9/3/2015 10:52 | 9/3/2015 | 10:52 | 8     | 89400  | B        | 1                          |
| 9/3/2015 10:57 | 9/3/2015 | 10:57 | 8     | 89400  | E        | 1                          |
| 9/3/2015 11:20 | 9/3/2015 | 11:20 | 10    | 109410 | A        | 0                          |
| 9/3/2015 11:21 | 9/3/2015 | 11:21 | 17    | 179400 | D        | 1                          |
| 9/3/2015 11:21 | 9/3/2015 | 11:21 | 12    | 129400 | E        | 1                          |
| 9/3/2015 11:23 | 9/3/2015 | 11:23 | 10    | 109410 | A        | 1                          |
| 9/3/2015 11:26 | 9/3/2015 | 11:26 | 8     | 89400  | E        | 0                          |
| 9/3/2015 11:54 | 9/3/2015 | 11:54 | 8     | 89400  | C        | 0                          |
| 9/3/2015 14:10 | 9/3/2015 | 14:10 | 17    | 179400 | E        | 0                          |
| 9/3/2015 14:16 | 9/3/2015 | 14:16 | 12    | 129410 | B        | 0                          |
| 9/3/2015 14:17 | 9/3/2015 | 14:17 | 14    | 149410 | A        | 0                          |
| 9/3/2015 14:31 | 9/3/2015 | 14:31 | 17    | 179400 | E        | 1                          |
| 9/3/2015 14:38 | 9/3/2015 | 14:38 | 9     | 99400  | A        | 0                          |
| 9/3/2015 14:43 | 9/3/2015 | 14:43 | 14    | 149410 | A        | 1                          |
| 9/3/2015 14:47 | 9/3/2015 | 14:47 | 17    | 179400 | D        | 1                          |
| 9/3/2015 14:51 | 9/3/2015 | 14:51 | 9     | 99400  | A        | 1                          |
| 9/3/2015 14:56 | 9/3/2015 | 14:56 | 14    | 149410 | A        | 0                          |
| 9/3/2015 15:01 | 9/3/2015 | 15:01 | 10    | 109410 | A        | 0                          |
| 9/3/2015 15:04 | 9/3/2015 | 15:04 | 10    | 109410 | A        | 1                          |
| 9/3/2015 15:07 | 9/3/2015 | 15:07 | 14    | 149410 | A        | 1                          |
| 9/3/2015 15:15 | 9/3/2015 | 15:15 | 8     | 89400  | C        | 1                          |
| 9/3/2015 15:18 | 9/3/2015 | 15:18 | 12    | 129400 | E        | 1                          |
| 9/3/2015 15:22 | 9/3/2015 | 15:22 | 8     | 89400  | E        | 1                          |
| 9/3/2015 15:27 | 9/3/2015 | 15:27 | 17    | 179400 | D        | 0                          |
| 9/3/2015 15:27 | 9/3/2015 | 15:27 | 17    | 179400 | E        | 0                          |

Answer 1

Посмотрите на скриншот ниже. Для простоты понимания формула разбита на несколько вспомогательных ячеек, но ее можно сконструировать так, чтобы она работала в одной ячейке (или с именованными диапазонами).

Сначала отсортируйте данные по дате / времени, затем по площади лаборатории, затем по лаборатории.

firstRow       =MATCH(lab&Area,INDEX(FullTable[LAB]&FullTable[LAB AREA],0),0)
lastRow        =COUNTIFS(FullTable[LAB],lab,FullTable[LAB AREA],Area)+firstRow
TimeMatchRow   =MATCH(time,INDEX(FullTable[Date / Time],firstRow):INDEX(FullTable[Date / Time],lastRow),1)
occupancyValue =IF(INDEX(FullTable[Occupancy (0 = unoccupied)],firstRow+TimeMatchRow-1),"occupied","not occupied")

firstRow устанавливает первый ряд, где находится комбинация лаборатории и области. Добавление счетчика для лаборатории и области помогает установить последний ряд диапазона, который будет использоваться для приблизительного поиска значения времени. Если 1 - последний параметр, Match вернет следующее наименьшее значение, если точное совпадение не найдено. Это строка, где найдено совпадение времени. Наконец, используя индекс для столбца занятости, добавьте firstRow и строку соответствия времени и настройте ее на единицу.