Melhorar o Desempenho do PROCV

Todos que usam Excel já passaram por esse cenário uma vez: há uma pasta de trabalho que possui uma tabela muito grande e há diversos PROCV e demora muito tempo para recalcula-la. Neste artigo vou mostrar como tornar seu PROCV 1000 vezes mais rápido.

Não estou exagerando: em alguns casos, pode ser mais que 1000 vezes. Se você acha que fiquei doido, vou fazer uma afirmação mais ousada ainda: farei isso substituindo cada PROCV da planilha por uma fórmula com dois PROCV!

Estudo de Caso

Observem o cenário abaixo:

A tabela Banco de Dados (que chamei de tblBancoDeDados), possui aproximadamente 25000 linhas. A Tabela de Testes possui o mesmo tamanho, registros aleatórios de tblBancoDeDados na coluna ID, e possui uma fórmula de PROCV para retornar os valores correspondentes na coluna Um PROCV Linear dada por:

=PROCV([@ID];tblDados;2;0)

No meu computador, foram necessários 11,74 segundos para calcular todos esses PROCV. Uma eternidade, se você precisa alterar com frequência os dados da tabela tblBancoDeDados.

Utilizando uma técnica curiosa, reduzi esse tempo de 11,74 segundos para 0,078 segundos, ou melhor: míseros 78 milissegundos!

A fórmula utilizada para alcançar esse resultado é:

=SE(PROCV([@ID];tblDados;1)=[@ID];PROCV([@ID];tblDados;2);NÃO.DISP())

A primeira observação que chama atenção é que os PROCV utilizados não são do tipo de correspondência exata, mas sim o de busca aproximada (omitir o último argumento do PROCV é o mesmo que considera-lo como VERDADEIRO ou 1).

Vejam que interessante o resultado do teste:

Afinal o que é um PROCV Linear e um PROCV Binário?

Algoritmos de Busca

Busca Linear

O PROCV mais utilizado pelas pessoas é aquele cujo último argumento é 0 ou FALSO. Chamo esse PROCV de linear por causa da forma incremental de como ele busca os dados.

Para efeito de estudo, considere a tabela abaixo de apenas 20 registros. Suponha que a fórmula esteja buscando pelo registro 1310 para retornar Ana. A função procura esse registro na primeira célula da coluna ID. Se não encontrar 1310, procura na segunda, e assim por diante, fazendo um total de 13 iterações até retornar o valor correspondente a 1310, que é Ana:

Busca Binária

O princípio de funcionamento de uma busca binário é dividir um vetor pela metade sucessivas vezes até encontrar o valor procurado. No exemplo a seguir, o tamanho do vetor é 20. Então, a busca linear verifica se o registro na posição 10 é maior ou menor que o valor (1310) procurado. Se for menor, procura 1310 na metade do vetor formado pelos registros 1 a 10, caso contrário, de 11 a 20, que é nosso caso.

No próximo passo, o algoritmo procura verifica se o 15o registro (metade entre 11 e 20) é maior ou menor que o valor procurado. Como é menor, o novo vetor de busca se restringe aos registros de 11 a 15. Por fim, o algoritmo verifica se o registro da metade desse intervalo (13o registro) é igual ao valor procurado, que é verdade, e então retorna sua correspondência, que é José.

Há um ponto de atenção importantíssimo no que se refere ao algoritmo binário: o vetor de busca deve estar classificado em ordem crescente! Essa é sua única limitação. Se essa condição não for atendida, você irá obter resultados errados nas suas fórmulas. Em contrapartida, a busca linear funciona inclusive em vetores que não estejam ordenados. Como essa condição é muito importante e traz resultados errados se não for atendida, vou destaca-la para ficar bem claro:

O PROCV na forma binária só funciona em tabelas cuja coluna de procura esteja classificada em ordem crescente!

Comparação entre PROCV Binário e PROCV Linear

Abaixo podemos ver como o algoritmo binário é muito mais rápido que o linear. Veja que à medida que aumentamos a quantidade de linhas do vetor de procura, a quantidade de iterações no algoritmo linear aumenta na mesma proporção. Por outro lado, no caso do algoritmo binário soma-se aproximadamente apenas 4 passos ao multiplicar o vetor de busca por 10:

Como se trata de caso médio, dividi o total de iterações do PROCV linear por 2 e mantive o mesmo valor do binário, uma vez que para fazer uma fórmula PROCV binário, precisamos usar a função PROCV duas vezes em sua composição. No entanto, mesmo avaliando o PROCV duas vezes, a forma binária é incomparavelmente mais rápido que o linear.

A Fórmula do PROCV Binário

=SE(PROCV([@ID];tblDados;1)=[@ID];PROCV([@ID];tblDados;2);NÃO.DISP())

O fato da fórmula do PROCV binário necessitar usar a função PROCV duas vezes não possuem relação direta. A explicação se dá porque as duas formas do PROCV não são diferentes apenas no que se refere na forma de como o algoritmo procura os dados, mas também na forma como consideram válido o resultado de uma busca. Então:

  • PROCV com último argumento FALSO: Faz busca linear e retorna uma correspondência exata. Se não for encontrada uma correspondência exata, retorna o erro #N/D.
  • PROCV com último argumento VERDADEIRO: Faz busca binária e retorna uma correspondência aproximada. Se não for encontrada uma correspondência exata, retorna o valor imediatamente menor que o termo procurado.

O que faz precisarmos de usar dois PROCV no caso binário é a segunda característica do PROCV com último argumento VERDADEIRO. Como ele pode trazer um resultado que seja diferente do que é buscado, é necessário primeiro testar se o valor retornado pela função é igual ao valor buscado:

PROCV([@ID];tblDados;1)=[@ID]

Se essa condição for satisfeita, sabemos que o registro existe na nossa tabela e então podemos retornar a coluna desejada na função:

PROCV([@ID];tblDados;2)

Se o teste condicional falhar, a fórmula irá retornar o erro #N/D, representado por NÃO.DISP().

Repetindo o que foi escrito na seção anterior, mesmo utilizando a função duas vezes, a fórmula como todo pode ser mais de 1000 vezes mais rápida que um único PROCV linear.

Melhorando o Desempenho de Outras Funções Além do PROCV

O Excel possui outras funções além do PROCV que fazem busca binária como a CORRESP, PROC e PROCH. Para o exemplo inicial das ~25000 mil linhas, as fórmulas seriam:

Para CORRESP:

=SE(ÍNDICE(tblDados[ID];CORRESP([@ID];tblDados[ID];1))=[@ID];ÍNDICE(tblDados[Valor];CORRESP([@ID];tblDados[ID];1));NÃO.DISP())

Para PROC:

=SE(PROC([@ID];tblDados[ID])=[@ID];PROC([@ID];tblDados[ID];tblDados[Valor]);NÃO.DISP())

Pessoalmente, a forma que mais uso é o PROC, pois é a mais enxuta. No entanto, decidi escrever o artigo destacando o PROCV por ser uma função mais conhecida do Excel.

Referências

Charles Williams

Para fazer download do arquivo de exemplo deste artigo, clique aqui.

Sobre Felipe Gualberto

Microsoft Most Valuable Professional (MVP) de Excel.
Esta entrada foi publicada em Dicas e marcada com a tag , , , , , , , . Adicione o link permanente aos seus favoritos.
  • Victor Martins

    Boa noite Felipe Gualberto, o suplemento expressxl não encontra mais disponível no seu website, tem a possibilidade de disponibilizar o arquivo novamente? Obrigado.

  • Carlo Cordeiro Silva

    Felipe, vc possuia outra versão do site Ambiente Office com dicas de EXCEL, fórmulas matriciais, etc? Onde está este site?

  • Marcos Branco

    Só não compreendi quando você menciona que haverá 24 iterações? Não seria 19 ou 20 iterações => 2^19 = 524.288, não é isso?

    • Correto, errei nos cálculos, tenho que corrigir. Só um detalhe: um PROCV itera 20 vezes, mas a fórmula como um todo, por ter dois PROCV binário, irá varrer o dobro de vezes. Tenho que considerar isso também na tabela.

  • Erithon Luz

    Felipe bom dia.

    Gostaria de saber se existe um Suplemento para Excel para a importação e exportação de informações prestadas nos arquivos do Sistema Público de Escrituração Digital (SPED).

    Seria: Regeração dos arquivos do EFD Contribuições, SPED Fiscal a partir das planilhas de Excel geradas pelo suplemento, com a inclusão dos registros indicados pelo usuário e com a possibilidade de retificação de informações de registros já existentes. O usuário poderá fazer alterações na planilha Excel em qualquer registro e exportar as informações para o arquivo TXT no layout estabelecido pela legislação de cada tipo de arquivo do SPED citado acima.

    Caso consiga desenvolver algo entre em contato comigo: erithon@hotmail.com

  • Rafael Ferreira

    Muito Top!

    Somente uma duvida, você disse que caso não esteja ordenado o resultado no PROCV binário sera errado. Mas o teste que a formula possui não evitaria isso? ” =SE(PROCV(D1;A:B;1)=D1; ” PROCV(D1;A:B;2);NÃO.DISP())

    Abraço

    • Obrigado.

      O teste na fórmula é necessário porque o PROCV binário não traz a correspondência exata, e não aproximada.
      Por exemplo: suponha que você esteja procurando o número 2 no vetor {1;3;4;5}. A expressão PROCV(2;{1;3;4;5};1) retornaria 1. Como 1 é diferente do que você está procurando (que é 2), o resultado final da fórmula tem que nos mostrar #N/D.

  • Jhoni Rosales

    Amigo mto bacana o artigo.
    Uso planilhas do google, para grandes volumes de dados tambem.

    Há comandos como Vlookup e Query.
    Desconfio que o Query seja bem mais veloz que o Vlookup.

    O que você acha ?

  • Ivanildo Junior

    Olá amigo,

    Muito legal a dica e consegui fazer a aplicação.

    Seguinte, gostaria de saber se há uma forma de fazermos isso com as funções de condição =CONT.SES, =SOMASES

    Tenho uma planilha gigante que usa essas funções, há alguma forma?

    • CONT.SES e SOMASES não fazem uma busca binária, apenas linear.
      Seu caso teria que ser estudado à parte para tentar otimizar.

  • Ivanildo Junior

    Fiz uns testes aqui com 20 Mil linhas, e não percebi diferença no time para trazer os resultados para ser sincero, mas valeu a dica, irei aplicar em alguns cenários empresariais com mais dados.

    • Você testou o exemplo para baixar? Tem que haver diferença sim, senão há algum detalhe que não foi alterado.

      • Ivanildo Junior

        Bom Felipe,

        O Exemplo que você deixou eu não apliquei pois queria fazer a aplicação em um cenário que eu faço parte, então peguei um relatório e fiz. Posso fazer uma plan de teste e te mandar simulando meu cenário.

  • Tyler

    Felipe, talvez você possa confirmar (ou desconfirmar) minha intuição quanto à seguinte questão:
    Se eu fizer um procv(G3,B:C,2,VERDADEIRO) em uma base de dados de 3 mil linhas, o excel vai usar o algoritmo binário, mas como eu deixei o intervalo aberto, isto é, procurando nas colunas inteiras, o Excel começaria a primeira iteração procurando na linha 524.288, depois 262.144, etc.
    Minha intuição é que em geral o Excel não fará isso. Se ele souber que não existem dados depois da linha 3000 (fato confirmado pelo tamanho da barra de rolagem à direita), então ele vai começar a busca pela última linha em que ele reconhece que existem dados (ou melhor, pela linha que está no meio do caminho entre a primeira e esta última). Procede?

    • Você está correto. O PROCV é uma das funções que dizemos que “faz interseção com a área de planilha usada”. Então, antes de ela começar a fazer as divisões binárias, ela faz interseção com a primeira e última células preenchidas.