Função do Excel / VBA para encontrar valor com base no tempo de data e dois critérios

Question

Medi dados que contêm dados de ocupação de um prédio. Existem vários quartos e várias áreas de quartos dentro de cada quarto em cada andar. O número do quarto contém o piso, por isso não é necessário pesquisar por andar.

E os dados de ocupação têm um carimbo de tempo que foi registrado sempre que havia atividade naquela área específica.

Basicamente, quero que o Excel veja e veja se o número do quarto e a área do quarto coincidem e o horário coincide e, em seguida, informe se a área foi ocupada ou não.

Eu só posso encontrar INDEX MATCH onde eles pesquisam vários critérios exatos, o que não é aplicável aqui, pois eu preciso da correspondência mais próxima para os valores de tempo.

Qualquer ajuda é muito apreciada! Eu estaria mais interessado em uma função que pudesse realizar isso, mas também estou aberta a soluções VBA.

Obrigado!

Aqui está o link para os dados

link

| 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

Considere a imagem abaixo. Para facilitar a compreensão, a fórmula foi dividida em várias células auxiliares, mas pode ser construída para funcionar em uma única célula (ou com intervalos nomeados).

Classifique os dados primeiro por data / hora, depois por área de laboratório e depois por laboratório.

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 estabelece a primeira linha onde a combinação de laboratório e área é encontrada. Adicionar uma contagem da combinação de laboratório e área ajuda a estabelecer a última linha do intervalo a ser usada para uma pesquisa aproximada no valor de tempo. Com 1 como o último parâmetro, Match retornará o próximo valor menor se nenhuma correspondência exata for encontrada. Essa é a linha em que a correspondência de horário é encontrada. Finalmente, usando um índice na coluna de ocupação, adicionando a primeira linha e a linha de correspondência de tempo e ajuste por um.