Eu tenho um conjunto de dados como abaixo. Observe que nem todas as datas são representadas.
Estou procurando uma fórmula do Excel 365 que, quando passada uma data de amostra, retornará uma data de dentro da tabela anterior ou igual à data de amostra e o valor correspondente é o primeiro valor maior que 0.
- Portanto, de 1 a 8 de fevereiro, a fórmula retornará 01-fev-21, pois é a primeira data na tabela.
- Para o dia 10 ao dia 16, ele retornará o dia 10, pois é a primeira data em que o valor aumentou acima de 0.
- Do 17º ao final da tabela retornará o 17º.
Uma das minhas tentativas:
=SUMPRODUCT(--(Table1[Date]<=D2),--(Table1[Value]>0),Table1[Date])- para 2 de fevereiro as duas primeiras datas são verdadeiras, assim como os dois primeiros números, então soma as datas e retorna 08-mar-42.
D2na fórmula acima contém uma data que está dentro dos limites de 01 a 25 de fevereiro.
Eu tentei trabalhar com o =MIN(IF((Table1[Date]<=D2)*(Table1[Value]>0),Table1[Date],"-"))que retorna 1º de fevereiro .
Qualquer ajuda seria muito apreciada.
Você pode aninhar uma função de filtro
vlookuppara obter o que deseja, por exemplo:=VLOOKUP(DATEVALUE("02/16/2021"),FILTER(A2:B18,B2:B18>0),2,true)Problema interessante.
Para me ajudar a descobrir isso, eu o enquadrei nestas palavras:
A parte depois da vírgula é a parte complicada, eu acho, porque as datas posteriores na série podem ter mais de uma linha com um zero antes delas.
De qualquer forma, acho que isso vai fazer o truque:
Para separá-lo, primeiro queremos obter uma lista de datas antes do parâmetro data que tem um zero no valor. Fazemos isso com isso:
Mas dessa lista, queremos apenas a linha mais recente com zero, então usamos MAX também:
O problema aqui é que para o primeiro conjunto de linhas (antes de 8 de fevereiro), não há linha com uma data menor com zero, então ele retorna o #CALC! erro:
Para lidar com isso, nós o envolvemos em SEERRO de forma que, se a fórmula acima retornar um erro, façamos com que ela retorne o MIN de toda a série de datas:
O que estamos tentando obter aqui é a data máxima da coleção de (datas de zero linha) e (a maior data antes da primeira data na tabela). Este último ponto significa que precisamos subtrair 1 do segundo parâmetro do SEERRO para que ele não retorne 1º de fevereiro, que é a primeira data com um valor, mas retorna o dia anterior:
Tudo bem, isso é bom. Agora temos para cada linha a data da linha zero mais recente (ou uma data anterior à primeira data na tabela).
Portanto, precisamos filtrar as datas após essa data, mas antes ou na data do parâmetro, e obter o valor mínimo do intervalo dos resultados.
Essa fórmula faria isso:
Então, para obter o menor dessa lista de datas, basta envolver tudo em MIN, conforme mostrado no topo da minha resposta.
Embora volátil, você pode tentar
OFFSET()aqui:Fórmula em
C2: