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.