locked
PROCV PESANDO EXCEL RRS feed

  • Pergunta

  • Boa tarde a todos!

    Segundo o que venho pesquisando, as vezes o excel fica lento por função usadas incorretamente para algumas tarefas, e uma delas foi citado foi a função "PROCV", pois em alguns casos outras funções podem serem substituídas onde melhora o desempenho da planilha, foi colocado que em alguns casos a função "INDICE" e "CORRESP", poderia substituir dando um melhor desempenho então venho aki no fórum pedir ajuda novamente.

    Não sei como aplicar essas funções "´ÍNDICE" e "CORRESP" no caso de minha fórmula.

    Veja essa fórmula que é relacionada as planilha chamadas de "ITAU", "BANCO DO BRASIL", "BRADESCO", "PJ BRADESCO" e copiei essa fórmula por todas as linhas da minha planilha na coluna K , tenho tambem nas outras colunas outras fórmulas fazendo ficar muito lenta minha planilha

    A fórmula é :

    =SE($E184="ITAU";SE(ÉERROS(PROCV($C184;Itau!$A$1:$G$30001;2;0));"";PROCV($C184;Itau!$A$1:$G$30001;2;0));SE($E184="BANCO DO BRASIL";SE(ÉERROS(PROCV($C184;Banco_do_Brasil!$A$3:$F$9939;2;FALSO));"";PROCV($C184;Banco_do_Brasil!$A$3:$F$9939;2;FALSO));SE($E184="BRADESCO";SE(ÉERROS(PROCV($C184;Bradesco_9861!$A$1:$F$10033;2;0));"";PROCV($C184;Bradesco_9861!$A$1:$F$10033;2;0));SE($E184="CAIXA";"";""))))

    Por favor alguém me ajude!

    segunda-feira, 27 de dezembro de 2010 15:17

Respostas

  • Veja que vc está chamando procv() duas vezes para cada caso de sucesso. Vc chama procv() para testar se encontra o dado procurado, se encontrar, vc dá outro procv().


    No caso, a busca em a1 até A30001, ou seja 30001 linhas, são 15 mil comparações em média, que vezes 2 (pois vc chama procv() duas vezes), são 30000 mil comparações em média por linha. Se essa fórmula for aplicada em 1000 linhas ... significa que vc aplica 1000 x 30.000, ou seja, 300.000 comparações, no caso do Itaú.

     

    No caso do Banco do brasil são 9939, no bradesco 10033 e por aí vai.

     

    Tente otimizar assim 

    =se( $E184 = "ITAU"; corresp( $c184 ; Itau!$A$1:$A$30001 ; 0) ;

            se( $e184 = "BANCO DO BRASIL" ; corresp( $c184 ; Banco_do_Brasil!$A$3:$A$9939;0)  ; 

                  se( $e184 = "BRADESCO" ; corresp( $c184; Bradesco_9861!$A$1:$A$10033;0) ; 

                        se( $e184 = "CAIXA" ; "" ; "" )

                      )

                 )

            )

     

    Escrevi em várias linhas para manter mais claro.

    Observe que o último teste                     se( $e184 = "CAIXA" ; "" ; "" ) é inútil. Isso vc tem que otimizar também. Se não for nem itau, nem bb nem

    bradesco, vc taca "" logo, para que testar se é caixa ? Pense bem.

    Coloque essa fórmula numa coluna auxiliar. Ela poderá conter 3 possibilidades 

    1 - O número da linha correspondente ao dado procurado

    2 - #N/D caso o dado não seja encontrado

    3 - "" caso o banco num seja itau, nem bb nem bradesco.

     

    Agora é só usar a função índice para resgatar o dado, mas .. antes testamos se a busca deu certo.

     

    =se( énúm( f184 ) ; se( $e184= "ITAU" ; índice( Itau!$b$1:$b$30001 ; f184) ;

                                        se( $e184 = "BANCO DO BRASIL" ; índice( Banco_do_brasil!$b$3:$b$9939; f184) ; índice( Bradesco_9861!$B$1:$B$10033 ; f184) 

                                      ) ;

    " Não achou em nenhum banco" )

     

    Dessa forma vc reduzirá a metade o número de procvs()

     

    experimente.

     

     


    João Eurico Consultor Manguetown - Brazil
    • Marcado como Resposta AlexGyn sexta-feira, 31 de dezembro de 2010 09:38
    segunda-feira, 27 de dezembro de 2010 16:48

Todas as Respostas

  • Veja que vc está chamando procv() duas vezes para cada caso de sucesso. Vc chama procv() para testar se encontra o dado procurado, se encontrar, vc dá outro procv().


    No caso, a busca em a1 até A30001, ou seja 30001 linhas, são 15 mil comparações em média, que vezes 2 (pois vc chama procv() duas vezes), são 30000 mil comparações em média por linha. Se essa fórmula for aplicada em 1000 linhas ... significa que vc aplica 1000 x 30.000, ou seja, 300.000 comparações, no caso do Itaú.

     

    No caso do Banco do brasil são 9939, no bradesco 10033 e por aí vai.

     

    Tente otimizar assim 

    =se( $E184 = "ITAU"; corresp( $c184 ; Itau!$A$1:$A$30001 ; 0) ;

            se( $e184 = "BANCO DO BRASIL" ; corresp( $c184 ; Banco_do_Brasil!$A$3:$A$9939;0)  ; 

                  se( $e184 = "BRADESCO" ; corresp( $c184; Bradesco_9861!$A$1:$A$10033;0) ; 

                        se( $e184 = "CAIXA" ; "" ; "" )

                      )

                 )

            )

     

    Escrevi em várias linhas para manter mais claro.

    Observe que o último teste                     se( $e184 = "CAIXA" ; "" ; "" ) é inútil. Isso vc tem que otimizar também. Se não for nem itau, nem bb nem

    bradesco, vc taca "" logo, para que testar se é caixa ? Pense bem.

    Coloque essa fórmula numa coluna auxiliar. Ela poderá conter 3 possibilidades 

    1 - O número da linha correspondente ao dado procurado

    2 - #N/D caso o dado não seja encontrado

    3 - "" caso o banco num seja itau, nem bb nem bradesco.

     

    Agora é só usar a função índice para resgatar o dado, mas .. antes testamos se a busca deu certo.

     

    =se( énúm( f184 ) ; se( $e184= "ITAU" ; índice( Itau!$b$1:$b$30001 ; f184) ;

                                        se( $e184 = "BANCO DO BRASIL" ; índice( Banco_do_brasil!$b$3:$b$9939; f184) ; índice( Bradesco_9861!$B$1:$B$10033 ; f184) 

                                      ) ;

    " Não achou em nenhum banco" )

     

    Dessa forma vc reduzirá a metade o número de procvs()

     

    experimente.

     

     


    João Eurico Consultor Manguetown - Brazil
    • Marcado como Resposta AlexGyn sexta-feira, 31 de dezembro de 2010 09:38
    segunda-feira, 27 de dezembro de 2010 16:48
  • Alex,

    É possível renomear as abas Banco_do_Brasil e Bradesco_9861 para BANCO DO BRASIL e BRADESCO , respectivamente??

    Explico: Se ambas forem renomeadas, vc pode transformar o nome do banco em variável de busca e sua fórmula ficaria assim:

    .xlsx --> =SEERRO(PROCV(C184;INDIRETO("'"&E184&"'!A1:B30000");2;0);"")

    .xls --> =SE(ÉERROS(PROCV(C184;INDIRETO("'"&E184&"'!A1:B30000");2;0));"";PROCV(C184;INDIRETO("'"&E184&"'!A1:B30000");2;0))

    Repare que como vc sempre procura a segunda coluna, diminuí o range para A:B.

    Tente e me diga se funciona. Talvez melhore a performance.

    Abs,
    segunda-feira, 27 de dezembro de 2010 19:26