Essas dicas e alterações são para que melhore a produtividade e você ganhe tempo em cada tarefa fazendo que no fim você ganhe horas.
Sumário
- Consulta Binária
- Cálculo Manual
Consulta binária
Solução: Retorna o resultado de uma consulta de uma tabela(procv/corresp) muito rapidamente.
Nas formulas do excel PROCV e CORRESP existe um argumento que altera a forma que a consulta é realizada.
Normalmente utilizamos a consulta exata:
PROCV(A2;B:D;2;FALSO) / CORRESP(A2;B:B;1)
que traz o resultado normalmente porém caso você queria fazer muitas consultas ao mesmo tempo (no meu caso 100.000) vai demorar bastante para concluir.
Nos testes as consultas dessa planilha ai (a dos 100.000 consultas) demorava cerca de 5 minutos. E como tinha que fazer essas consultas pelo menos umas 20 vezes no período de 2 horas ficava inviável.
A solução foi fazer duas alterações:
1- Alterar o argumento das fórmulas para correspondência aproximada, ficando assim:
PROCV(A2;B:D;2;VERDADEIRO) / CORRESP(A2;B:B;0)
2- Ordenar a base com a coluna de consulta/índice em ordem alfabética ou do maior para o menor
Pronto!!
Agora tem um porém devido a característica da consulta é necessário um mudança da formula:
SE(PROCV(A2;B:D;1;VERDADEIRO)=A2;PROCV(A2;B:D;3;VERDADEIRO); NÃO.DISP())
SE(ÍNDICE(B:B;CORRESP(A2;B:D;1))=A2;ÍNDICE(D:D;CORRESP(A2;B:D;1)); NÃO.DISP())
Isso se faz necessário pois como essa consulta utiliza o algoritmo binária ou seja divide o intervalo em partes
o valor que pesquisamos é comparado com o elemento no meio da lista e se não corresponder vai dividindo a tabela que ficam no intervalo fora da pesquisa até chegar ao valor mais próximo do valor que procuramos.
Conceito bastante utilizado em bancos de dados chamado de índice(http://www.linhadecodigo.com.br/artigo/3620/indices-mysql-otimizacao-de-consultas.aspx)
O Resultado dessa consulta nesse case de 100.000 consultas, hoje é concluído em cerca 10 segundos considerando um processador Core i5-4570 - 3.20 Ghz - 8Gb Ram - HDD disco magnético (não ssd)
Por padrão o excel ao criar uma fórmula ele realiza o cálculo da fórmula automaticamente, por exemplo
Se criou a fórmula abaixo
=SOMA(A2;A3)
E depois disso alterar os valores de A2 e A3 o excel realiza esse calculo automaticamente.
Porém se tiver planilhas grandes e com muitas fórmulas pode começar a observar o excel travando ou lento, porém ele dá um sinal do por que está assim:
Isso mostra que por algum motivo o excel está realizando os cálculos que as vezes você não pediu.
Uma solução para este problema é alterar a configuração padrão para calcular manualmente. Que agora para calcular a planilha atual utiliza-se F9 e toda a pasta de trabalho Ctrl+F9.
Com essa alteração você ganha bastante tempo e o Excel só vai calcular quando solicitado.
Atenção: Se abrir alguma planilha depois dessa alteração todas elas irão absorver essa característica e com isso ao salvar e outras pessoas abrirem podem cometer erros ao achar que está automático. Ou seja use essa dica com cuidado
Top comments (0)