☰

Retornar dados com base no período correspondente

tag-icon • tag-icon microsoft-excel worksheet-function
Retornar dados com base no período correspondente

Nosso grupo possui uma planilha Excel que é usada para monitorar responsabilidades rotativas. Um exemplo simples seria apresentado assim:

  • Coluna A:COMEÇARpossui datas que definem o início do período representado por cada item de linha.
  • Coluna B:FIMpossui datas que definem o final do período representado por cada item de linha.
  • Coluna C:CESSIONÁRIOpossui strings que representam a pessoa designada para determinada responsabilidade durante o período definido por START e END na mesma linha.

A lista em si égeralmenteem ordem crescente por data de INÍCIO. Porém, é possível que a folha acabe reorganizada.

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

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

  • ANTERIORseria o CESSIONÁRIO correspondente ao período imediatamente anterior ao atual.
  • ATUALseria o CESSIONÁRIO atualmente responsável.
  • PRÓXIMOseria o CESSIONÁRIO correspondente ao período de tempo posterior àquele em que nos encontramos atualmente.

Supondo que a planilha permaneça na ordem correta (crescente por START), retornar ANTERIOR e PRÓXIMO deve ser fácil depois que a fórmula para ATUAL for descoberta. Mas também não sei exatamente por onde começar.

Responder1

EUpensarisso funcionará para encontrar a corrente. Eu testei até certo ponto, mas estouadivinhaçãoque ignorar uma característica não é, neste caso, um problema.

Primeiro, adicione uma coluna entre B e C (o Cessionário passa a ser D daqui em diante). Nesta coluna, coloque esta fórmula:

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

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

Agora, na sua célula do cessionário atual, você coloca:

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

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

Normalmente, MATCH deseja que a coluna de pesquisa seja classificada, mas como deveríamos ter apenas um Sim na coluna, eupensar(esta é a parte do palpite) que podemos ignorar essa restrição.

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

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

Você também pode ocultar a coluna extra.

Responder2

Entrada ordenada:

Destinatário atual: currRow=match(now(),A:A,1)- à medida que seus dados são ordenados, ele encontrará a linha atual, sem necessidade de uma coluna auxiliar. Defina o nome da célula contando como currRow apenas para facilitar a referência.
Destinatário: =indirect("C"& currRow + x)- x: -1, 0, 1 para as linhas anterior, atual e seguinte, respectivamente.

Entrada não ordenada:

Linha atual: currRow=match(max(if(A:A>now(),"",A:A)),A:A,0)- esta é uma fórmula de matriz, então você precisa 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.

informação relacionada