Se você tiver uma planilha grande, muitos VLOOKUPs podem começar a desacelerar as coisas. Você tem uma planilha lenta devido a VLOOKUP? Estou falando de uma planilha que requer 40 segundos ou 4 minutos para ser calculada. No artigo de hoje, uma fórmula incrível com dois VLOOKUPs usando a pesquisa de intervalo resolverá o problema.
VLOOKUP é uma função relativamente cara. Quando você está procurando uma correspondência exata, o Excel precisa examinar a tabela de pesquisa uma linha por vez.
A pasta de trabalho que estou usando hoje está fazendo 7.000 VLOOKUPs em uma tabela de 116.000 itens. Em uma máquina realmente rápida de 64 bits com 8 núcleos, o tempo de recálculo é de 3,01 segundos.
Uma maneira de melhorar VLOOKUP é mover os itens mais vendidos para o topo da tabela de pesquisa. Obtenha um relatório dos 100 itens mais vendidos e mova esses itens para o topo da lista. A classificação por popularidade melhora o tempo de recálculo para 0,369 segundos. Isso é oito vezes mais rápido que o primeiro resultado.
Mas existe uma maneira de acelerar ainda mais as coisas. Conforme você está construindo sua VLOOKUP, quando chega ao quarto argumento para escolher False, há outra opção que raramente é usada. O Excel diz que "Verdadeiro" faz uma "correspondência aproximada". Isso não é absolutamente correto. Se a equipe do Excel fosse honesta, eles explicariam que True “fornece uma resposta correta muitas vezes, mas outras vezes, sem nenhum aviso, vamos colocar a resposta errada aí. Espero que você não se importe em reafirmar seus números à Comissão de Valores Mobiliários. ”
Claro, há um momento adequado para usar True. Veja este artigo. Mas seria muito ruim usar True quando você está tentando fazer uma correspondência exata.
Se você tentar usar True para uma correspondência exata, obterá a resposta certa na maioria das vezes. Mas quando o item que você está procurando não está na tabela, o Excel fornecerá o valor de uma linha diferente. Esta é a parte que torna “True” um fracasso para todos na contabilidade. Fechar nunca está correto na contabilidade.
Nota
Aprendi o seguinte truque com Charles Williams. Ele é o maior especialista do mundo em velocidade de planilha. Se você tem uma pasta de trabalho lenta, contrate Charles Williams para meio dia de consultoria. Ele pode encontrar os gargalos e tornar sua planilha mais rápida. Encontre Charles em http://www.decisionmodels.com.
Enquanto eu e todos os contadores rejeitamos o argumento “Verdadeiro” de VLOOKUP por causa da imprevisibilidade, Charles Williams defende a Verdade. Ele ressalta que o Verdadeiro é muito mais rápido do que o Falso. Centenas de vezes mais rápido. Ele admite que às vezes você obtém a resposta errada. Mas ele tem uma maneira de lidar com as respostas erradas.
Charles realmente quer que você faça duas VLOOKUPs. Primeiro, faça um VLOOKUP e retorne a coluna 1 da tabela. Veja se o resultado é o que você estava procurando em primeiro lugar. Se esse resultado corresponder, você sabe que é seguro fazer o VLOOKUP real para retornar alguma outra coluna da tabela:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Diante disso, isso parece loucura. Para usar o método de Charles, você precisa fazer o dobro de VLOOKUPs. Mas, ao cronometrar o tempo de cálculo para este método, ele é 35 vezes mais rápido do que o VLOOKUP normal.
Observe que, embora a maioria das tabelas de pesquisa não precise ser classificada, quando você está usando True como o quarto argumento, a tabela deve ser classificada. Para uma discussão de 7 minutos sobre como a versão True de VLOOKUP salta na tabela de pesquisa, consulte http://mrx.cl/TrueVLOOKUP.
Agradeço a Charles Williams por me ensinar esse recurso e a Scott St. Amant por indicá-lo como um dos 40 melhores.
Assistir vídeo
- VLOOKUP quando usado com False é uma função lenta
- Classificar os dados de AZ não acelera a função
- Classificar por popularidade pode acelerar a função
- Mudar para PROCV com Verdadeiro é mais rápido, mas reportará a resposta errada se o item não for encontrado
- Para atenuar o problema, faça uma VLOOKUP (A2, Tabela, 1, Verdadeiro) para ver se o resultado é A2 primeiro
- 14000 VLOOKUP (verdadeiro) e 7000 IF executam mais rápido do que 7000 VLOOKUP (falso)
Transcrição gerada automaticamente
- Aprenda Excel com o Podcast
- episódio 2031 mais rápido vlookup I'm
- podcasting todas as dicas neste livro
- clique no I no canto superior direito
- para chegar à lista de observação
- ei, bem-vindo de volta ao sr. elenco hutnik
- Sou Bill Jelen, fiz isso
- vídeo antes de ser um dos meus favoritos
- truques se você tiver a aparência, se você
- tem vlookup star levando 30 40 50
- segundos quatro minutos você sabe de alguma coisa
- você vai adorar este vídeo se o seu
- vlookup stick um segundo, basta clicar em Avançar
- e ir para o próximo vídeo, eu tenho um
- vlookup aqui está olhando para uma mesa
- de 115.000 itens fazendo 7.000 vlookup, então
- vamos usar alguns Charles Williams
- do código rápido do Excel para ver quanto tempo
- leva para fazer este vlookup tudo bem quatro
- ponto zero nove segundos que é o
- vlookup típico com vírgula falsa no
- terminar e tudo isso surgiu porque longo
- há muito tempo fui fisgado por um cara em
- Twitter que disse que seria melhor se
- você classificaria sua tabela de pesquisa por
- enviando eu disse não, isso não é verdade
- não importa se vamos um
- enviando ou descendo ou completamente
- aleatório o vlookup só tem que ir procurar
- de item para item para item e assim quando nós
- ordenar a mesa ver se realmente leva
- mais quatro vírgula oito quatro segundos, então
- você sabe que não é verdade que classificar o
- a mesa vai fazer ir mais rápido, mas
- realmente a única coisa que poderia fazer ir
- mais rápido se de alguma forma você pudesse classificar por
- popularidade se você pudesse obter o melhor
- venda de itens no topo da lista
- mesmo você sabe o seu top cinquenta, você sabe
- quais são seus 50 itens mais vendidos
- traga-os para o topo da lista e
- observe que por segundos cai para 0,36
- segundos, uma melhoria dez vezes maior no tempo
- usando classificar por popularidade agora ei alguns
- anos atrás eu tive a sorte de ser
- convidado a Amsterdam para apresentar em um
- Excel Summit lá e não é como
- a maioria dos meus seminários onde sou apenas eu
- bem ali havia duas faixas, então sala a
- e a sala B e eu estávamos na sala be
- falando sobre vlookups e no quarto
- um palpite de quem estava sentado naquela sala
- estava Charles Williams bem e Charles
- aqui está
- seu nome sendo mencionado através do
- parede então ele vem para assistir ele
- assiste minha pequena demonstração lá onde eu vou
- de quatro segundos a 0,36 segundo ele
- vem até mim depois diz que aposto
- você está muito feliz com isso
- melhoria
- Eu digo que é uma tenda cheia
- melhoria agora Charles Charles tem o
- serviço de Excel rápido nosso modelo de decisão
- os modelos de decisão limitados em que estamos
- meio dia ele vai analisar sua apostila
- e ele afirma que chega a cem
- vezes mais rápido certo, ele encontrará o
- gargalos Annette e Charles Charles
- comes from he says look at comma false
- that you and your accountant friends are
- doing it is the slowest thing in Excel
- if you would do a comma true it's a
- thousand times faster and then Charles
- says this next Clause is if it doesn't
- really matter he says now sometimes it's
- wrong oh wait Charles you don't
- understand an accountant sometimes is
- wrong is a non-starter we do not accept
- sometimes it's wrong and and the time
- that it's wrong the comma true when
- you're doing a comma true is we go look
- for a P 3 2 2 1 1 and it's not found
- they're gonna give you the item just
- less alright and they're not gonna tell
- you we couldn't find it they're just
- gonna they're just gonna give you Adam
- just less that that's unacceptable and
- Charles says well here's what we could
- do imagine if you did a vlookup of P 3 2
- 2 1 1 into just column G ask for the
- first column comma true and see if what
- you get back is what you were looking
- for if what you get back is what you're
- looking for then you know it's safe to
- go to the second vlookup if it's not
- what you were looking for then you have
- an if statement there that says not
- found alright so we do a vlookup of a2
- into the table comma 1 see if it's equal
- to 82 if it is then it's safe to go on
- and do the second vlookup otherwise they
- not found I said Charles do you realize
- I'm doing 7000 vlookups and now you're
- gonna be doing 7 14000 vlookups and 7000
- if statements I I said you really think
- this is gonna be faster sure I said well
- I'll bet you a pint alright so here we
- go remember the vlookup that all of us
- are doing with the comma falls 4.0 9
- seconds the sort by popularity which is
- kind of hard to do
- Oh point three seconds here we go here's
- Charles Williams are you ready look at
- that point zero four four one hundredths
- of a second from four seconds down to
- four one hundreds of a second imagine if
- you had a spreadsheet that was taking
- for 40 seconds to calculate and how much
- faster would be using this to vlookup
- method it's an amazing trick and yeah I
- guess I stole the trick and put it in
- the book although I put a great cartoon
- version of Charles Williams in there
- saying he's the fastest guy in all of
- Excel you can buy this tip and all of
- the other tips in this book click that I
- at the top right hand corner
- all right so recap vlookup when used
- with false it's a slow function sorting
- the data a disease does not speed up the
- function unless you sell a lot of things
- to begin with a and B sorting by
- popularity does it's about a tenfold
- aumentar, você pode mudar para vlookup
- com verdade, mas vai relatar o errado
- responda se os itens não forem encontrados, então estamos
- na verdade, vou fazer duas pesquisas de vlookups em um
- dois na coluna, um na mesa e
- ver se o que recebemos é um dois se
- é seguro fazer o vlookup em
- a coluna comum para ter um
- se a declaração diz que não foi encontrado certo
- oh ei obrigado a Charles Williams por
- me ensinando esse truque incrível e
- obrigado pela visita vai ver
- você da próxima vez para outro elenco de rede de
- MrExcel
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2031.xlsm
