Guru do Excel e Power BI

Entre a data 1 e data 2 existiram 4 repetições do valor 121,504.
Preciso de saber a data da 1ª ocorrência (E9) e a data da última ocorrência (E10)
As fórmulas atuais estão incorretas porque pesquisam toda a lista de datas e eu pretendo que isso seja feito apenas entre a data 1 e data 2.

Agradeço a gentileza dos colaboradores. De preferência, sem fórmulas matriz ou fórmulas voláteis.

Susana

Eu fiz de 2 maneiras diferentes, com matricial e sem, veja se te ajuda.

Abs

Não está a funcionar Felipe.
Na versão com matricial (que não posso usar devido ao tamanho da minha folha), a fórmula em E9 funciona corretamente mas em E10 já não está correta.
Na 2ª alternativa, F9 e F10 estão as duras erradas.
Ainda em relação à função LIN que você usa, tenha em consideração que as fórmulas terão de ser adaptadas para a coluna inteira (A:A, B:B, etc)

Susana, deixar a fórmula calculando a coluna inteira é o que deixa ela pesada, pois ao invés de calcular para 1000 linhas por exemplo, ela irá calcular para 1.048.756.

To tentando adaptar a fórmula, mas deixar a coluna inteira é inviável.

Susana,

Veja se agora atende a sua necessidade

Felipe, a fórmula em F9 continua a não funcionar. Por exemplo, se vc alterar o valor de b8 para outro qualquer, F9 continua a dizer que a 1ª ocorrência foi às 11:35 quando na realidade foi às 11:45.

Independentemente disso, existe mesmo o problema das fórmulas se adaptarem às colunas inteiras, fator necessário.
A minha folha atual possui mais de 100 colunas e 20 000 linhas. E calcula tudo em poucos segundos, e todas as fórmulas evocam as colunas inteiras. Portanto, o problema do desempenho terá mesmo a ver com certo tipo de fórmulas em concreto.
Porque evocar toda a coluna? Porque a minha folha recebe históricos de cotações em real time, e de cada momento pretendo mais ou menos hiostórico: as linhas estão sempre a mudar.

Se descobrir alguma alternativa, fico agradecida.

Susana,

Veja se agora ficou melhor, converti o intervalo em tabela, assim você poderá inserir novas informações e as fórmulas irão automaticamente calcular.

Abs

Felipe, a ideia foi muito boa e agradeço o seu tempo, mas infelizmente os dados não podem ser convertidos para tabela, pois provêm de dados externos importados (ficheiro .csv) e por esse motivo não aceita importar dados externos para tabela.
Eu acredito que haverá uma solução simples, tipo maior / menor / corresp etc para resolver o problema. Vamos ver.

Susana, as fórmulas não são simples devido aos critérios, mas se o problema for só deixar em tabela, isso é o de menos, você pode ir na GUIA DESIGN e clicar em converter para intervalo, com relação ao intervalo da fórmula, não marque a coluna, coloque por exemplo o máximo de linhas que poderão ter na sua planilha, exemplo 2000, 3000, etc, mas evite marcar a coluna, pois toda vez que for calcular, ele calcula para o limite total de linhas do Excel.

Eu coloquei agora até a linha 1700, caso precise alterar é só alterar nas fórmulas.

Os valores estão corretos?

Att

Os valores estão corretos em F9 e F10 sim, que é a opção que estou focada. Só mesmo o problema da fórmula não evocar a coluna inteira, por causa do LIN.

Você pode colocar, porém vai deixar a fórmula lenta, eu sugiro você colocar a qtde máxima de linhas que terá sua planilha, se não souber, usa 5000 ou mais, mas nunca a coluna inteira.

Não dá Filipe, existem situações que a folha tem mais de 100 000 linhas, e não posso estar preocupada que quando isso aconteça, ter de alterar as fórmulas. Também não posso limitar as fórmulas a grande quantidade de linhas, quando muitos vezes a folha possui menor quantidade. É um problema.

Susana, eu pintei em azul as fórmulas usando a coluna inteira, verifique se o desempenho dos cálculos compensa deixar dessa maneira, outra solução seria usar o POWER QUERY, para importar os dados de CSV e trabalhar via medidas do Power Pivot

Não ficou bem, os resultados já não dão certo e demorou muito tempo para calcular.

Não deveria calcular errado só ficar lento, eu disse se vc quiser usar a coluna inteira vai ficar pesado.
Ele calcula para o limite maximo de linhas do excel toda vez.

Com um pouco de imaginação, deve ser possível derivar uma fórmula simples de rápido desempenho.
Estou a pensar em algo assim: Se colocar em qualquer célula esta fórmula:
=SE.ERRO(ÍNDICE(A:A;CORRESP(E7;ÍNDICE(B:B;CORRESP(E3;A:A;0)):ÍNDICE(B:B;CORRESP(E5;A:A;0));0)+CORRESP(E3;A:A;0)-1);"")
devolve a 1ª data que o excel encontra e que respeita 3 critérios: situa-se entre a data 1 e data 2, e corresponde ao valor 121.504.
Ora, como entre a data 1 e data 2 existiram 4 repetições do mesmo valor, deve haver uma maneira de substituir o E7 da fórmula por funções como maior / menor e usar como argumento final (posição do maior ou menor) a própria célula que já está a informar existirem 4 repetições (E8). É esta pequena nuance que não consigo deslindar.

Na minha opinião você tá forçando o Excel a calcular para todas as linhas sendo que não precisa, mas já que quer assim, vamos ver se alguém tem alguma sugestão.

PROBLEMA RESOLVIDO.

Após gastar meia tarde, encontrei uma solução ultra rápida. Testada numa folha de 17 000 linhas nas colunas A e B, os cálculos foram repetidos 300 vezes em simultâneo e os resultados obtidos em 1 fração de segundo!