Retorna dados com base no período correspondente

Question

Nosso grupo tem uma planilha do Excel que é usada para rastrear responsabilidades rotativas. Um exemplo simples seria apresentado assim:

• A coluna A: START tem datas que definem o início do período de tempo representado por cada item de linha.
• Coluna B: END tem datas que definem o final do período de tempo representado por cada item de linha.
• Coluna C: ASSIGNEE possui strings representando a pessoa atribuída à responsabilidade dada durante o período de tempo definido por START e END na mesma linha.

A lista em si é normalmente em ordem crescente por data de início. No entanto, é possível que a folha possa ser reorganizada.

Não deve haver nenhuma sobreposição entre os períodos de tempo definidos na lista.

O que eu quero adicionar é uma seção informativa com três valores (em células separadas):

• PREVIOUS seria o ASSIGNEE correspondente ao período de tempo imediatamente anterior ao atual.
• CURRENT seria a ASSIGNEE responsável no momento.
• NEXT seria o ASSIGNEE correspondente ao período de tempo após o período atual.

Supondo que a planilha eletrônica esteja na ordem correta (aumentando por START), retornar PREVIOUS e NEXT deve ser fácil depois que a fórmula de CURRENT for descoberta. Mas também não sei exatamente por onde começar isso.

    

Answer 1

Eu acho que isso funcionará para encontrar corrente. Eu testei isso até certo ponto, mas estou supondo que ignorar uma característica é, neste caso, um problema que não é.

Primeiro, adicione uma coluna entre B e C (o Responsável se torna D daqui em diante). Nesta coluna, coloque esta fórmula:

=IF(NOW()-A2>0,IF(NOW()-B2<0,"Yes","No"),"No")

Isso deve render uma linha com Sim e o restante com Não (para o intervalo atual).

Agora, no seu celular para o destinatário atual, você coloca:

=INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0), 2)

Por anterior: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) - 1, 2)
Para o próximo: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) + 1, 2)

Normalmente, a CORRESP quer que a coluna de pesquisa seja classificada, mas como devemos ter apenas um Sim na coluna, acho que (essa é a parte do palpite) que podemos ignorar essa restrição.

Minha planilha de teste tinha apenas 3 linhas, então YMMV.

Você precisará de uma verificação de erros para anterior quando a corrente é a primeira linha, etc., e nomear seu intervalo de origem provavelmente é uma boa ideia.

Você também pode querer ocultar a coluna extra.

    

Answer 2

Entrada ordenada:

Destinatário atual: currRow=match(now(),A:A,1) - conforme seus dados são solicitados, isso localizará a linha atual, sem necessidade de uma coluna auxiliar. Defina o nome da célula contando isso para currRow apenas para facilitar a referência.
Atribuído: =indirect("C"& currRow + x) - x: -1, 0, 1 para as linhas anterior, atual e seguinte, respectivamente.

Entrada não solicitada:

Linha atual: currRow=match(max(if(A:A>now(),"",A:A)),A:A,0) - esta é uma fórmula de matriz, portanto, é necessário inseri-la com CTRL + SHIFT + ENTER.
Destinatário atual: mesma fórmula de antes.
Anterior: =indirect("C" & match(max(if(A:A>=indirect("A" & currRow),"",A:A)),A:A,0)) - também uma fórmula de matriz.
Próximo: =indirect("C" & match(min(if(A:A<=indirect("A" & currRow),"",A:A)),A:A,0)) - e ainda uma fórmula de matriz.