Este fórum está sendo desativado

[Susana778]

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.

[FelipeMGarcia]

Susana

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

Abs

[Susana778]

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)

[FelipeMGarcia]

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.

[FelipeMGarcia]

Susana,

Veja se agora atende a sua necessidade

[Susana778]

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.

[FelipeMGarcia]

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

[Susana778]

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.

[FelipeMGarcia]

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.

[FelipeMGarcia]

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

Os valores estão corretos?

Att

[Susana778]

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.

[FelipeMGarcia]

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.

[Susana778]

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.

[FelipeMGarcia]

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

[Susana778]

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

[FelipeMGarcia]

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.

[Susana778]

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.

[FelipeMGarcia]

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.

[Susana778]

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!