Este é outro exemplo de velocidade de fórmula. Digamos que você tenha que fazer 12 colunas de VLOOKUP. Você pode torná-lo mais rápido usando as funções MATCH e 12 INDEX.
Na figura a seguir, você terá que executar 12 funções VLOOKUP para cada número de conta. VLOOKUP é poderoso, mas leva muito tempo para fazer cálculos.
Além disso, a fórmula deve ser editada em cada célula conforme você copia. O terceiro argumento deve mudar de 2 para 3 para fevereiro, então 4 para março e assim por diante.
Uma solução alternativa é adicionar uma linha com os números das colunas. Então, o terceiro argumento de VLOOKUP pode apontar para esta linha. Pelo menos você pode copiar a mesma fórmula de B4 e colar em C4: M4 antes de copiar todo o conjunto.
Mas aqui está uma abordagem muito mais rápida. Adicione uma nova coluna B com Onde? como o título. A coluna B contém uma função MATCH. Esta função é muito semelhante a VLOOKUP: você está procurando o valor em A4 na coluna P4: P227. O 0 no final é como o False no final de VLOOKUP. Ele especifica que você deseja uma correspondência exata. Aqui está a grande diferença: MATCH retorna onde o valor é encontrado. A resposta de 208 diz que A308 é a 208ª célula no intervalo P4: P227. De uma perspectiva de tempo de recálculo, MATCH e VLOOKUP são quase iguais.
Eu posso ouvir o que você está pensando. “De que adianta saber onde algo está localizado? Eu nunca tive um gerente ligando e perguntando: 'Em que linha é esse crédito?' ”
Embora os humanos raramente perguntem em que linha algo está, a função INDEX pode usar essa posição. A fórmula a seguir informa ao Excel para retornar o 208º item de Q4: Q227.
Conforme você copia essa fórmula, a matriz de valores se move pela tabela de pesquisa. Para cada linha, você está executando uma função MATCH e 12 INDEX. A função INDEX é incrivelmente rápida em comparação com VLOOKUP. Todo o conjunto de fórmulas calculará 85% mais rápido do que 12 colunas de VLOOKUP.
Assistir vídeo
- Digamos que você tenha que fazer 12 colunas de PROCV
- Use cuidadosamente um único cifrão antes da coluna do valor de pesquisa
- Use cuidadosamente quatro cifrões para a tabela de pesquisa
- Você ainda está codificando o argumento da terceira coluna.
- Uma solução comum é adicionar uma linha de células auxiliares com o número da coluna.
- Outra solução menos eficiente é usar COLUMN (B2) dentro da fórmula VLOOKUP.
- Mas, fazer 12 VLOOKUP para cada linha é muito ineficiente
- Em vez disso, adicione uma coluna auxiliar com o título WHERE e faça uma única correspondência.
- A PARTIDA dura tanto quanto a VLOOKUP de janeiro.
- Você pode então usar 12 funções INDEX. Eles são incrivelmente rápidos em comparação com VLOOKUP.
- O INDEX apontará para uma única coluna de respostas com $ antes das linhas.
- O INDEX apontará para a coluna auxiliar com um $ antes da coluna.
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2028 - Substituindo Muitos VLOOKUPs por um MATCH!
Clique no “i” no canto superior direito para acessar a lista de reprodução. Estarei enviando o podcast deste livro inteiro!
Ei, bem-vindo de volta ao netcast, sou Bill Jelen! Bem, é um problema clássico, temos que fazer VLOOKUP uma vez por mês, certo? E você pode ser extremamente cuidadoso aqui ao pressionar F4 3 vezes para travar a coluna e, em seguida, pressionar F4 uma vez para travar toda a linha. Mas quando você chega a este ponto, o, 2, FALSE que 2 está embutido em código, e conforme você copia isso, você terá que editar o 2 para um 3, certo? Agora, uma forma ineficiente de fazer isso, uma forma que não gosto é usar a coluna de B1. A coluna B1 é obviamente 2, mas conforme você a copia, veja que ela mudará para a coluna C1, que é 3, mas pense sobre isso, isso está constantemente descobrindo o número da coluna repetidamente. Então, o que vejo as pessoas fazem e porque, você sabe, preferem mais do que as colunas, é que vamos arrastar com a tecla Ctrl pressionada,coloque os números de 2 a 13 lá em uma célula auxiliar e, então, quando chegarmos a este ponto, subimos e especificamos o número da coluna. Pressione F4 2 vezes para travar na linha,, FALSE e assim por diante. Mas mesmo com esse método, VLOOKUP é incrivelmente ineficiente, porque ele tem que pesquisar todos esses itens aqui até encontrar A308 e essa é a figura B4. Quando ele passa para o C4, ele esquece que apenas foi e olhou e começa tudo de novo, certo. Portanto, você tem uma das funções mais lentas em todo o Excel, o VLOOKUP, FALSE sendo executado continuamente para o mesmo item.porque ele tem que pesquisar todos esses itens aqui até encontrar A308 e essa é a figura B4. Quando ele passa para C4, ele esquece que apenas foi e olhou e começa tudo de novo, certo. Portanto, você tem uma das funções mais lentas em todo o Excel, o VLOOKUP, FALSE sendo executado continuamente para o mesmo item.porque ele tem que pesquisar todos esses itens aqui até encontrar A308 e essa é a figura B4. Quando ele passa para C4, ele esquece que apenas foi e olhou e começa tudo de novo, certo. Portanto, você tem uma das funções mais lentas em todo o Excel, o VLOOKUP, FALSE sendo executado continuamente para o mesmo item.
Portanto, aqui está o caminho muito, muito mais rápido: vamos inserir uma coluna auxiliar, e essa coluna auxiliar eu chamo de Onde? Onde diabos está o A308? Usaremos a = MATCH, procure A308 na primeira linha da tabela, pressione F4 aqui,, 0 para uma correspondência exata, certo, isso nos diz que “Ei, olhe isso, está na linha 6, como incrível é isso? " Mas enquanto copiamos, veja, está em lugares diferentes o tempo todo. Certo, agora esta partida dura tanto quanto leva a VLOOKUP de janeiro, eles estão mortos, mas aqui está o incrível. A partir daí, nunca precisaremos fazer uma PROCV para o resto da linha, poderíamos apenas fazer = INDEX, INDEX diz “Aqui está uma série de respostas”. Vou para as células de janeiro e, com muito cuidado, pressiono F4 2 vezes para bloquear em 4: 227,mas o Q pode mudar conforme eu me movo. Vírgula, e ele quer saber em qual linha, bem, essa será a resposta em B4, pressionarei F4 3 vezes para obter o $ antes do B, tudo bem, copie isso.
Essa fórmula, essas fórmulas INDEX, essas 12 vão acontecer em menos do que o tempo que levaria para fazer a VLOOKUP de fevereiro, certo. Se colocarmos o timer Charles Williams nisso, tudo isso calculará cerca de 14% do tempo de 12 VLOOKUPs. Seu gerente não quer ver o Onde? Tudo bem, é só esconder essa coluna, tudo continua funcionando, tudo bem, essa é uma forma linda de acelerar os 12 meses ou as 52 semanas de VLOOKUPs. Tudo bem, esta dica, e muitas outras dicas, estão neste livro. Clique no “i” no canto superior direito ali, você pode comprar o livro, $ 10 e-book, $ 25 para o livro impresso, tudo bem.
Então, hoje tivemos um problema onde 12 colunas de VLOOKUP, você pode colocar $ cuidadosamente, mas esse terceiro argumento ainda precisa ser codificado. Você poderia usar a coluna (B2), não sou fã disso, porque existem centenas de linhas * 12 colunas onde calculamos isso repetidamente. Basta usar uma célula auxiliar em uma linha, colocar os números de 2 a 12 e apontar para isso, ainda é ineficiente, porque PROCV depois de descobrir janeiro, tem que começar no início de fevereiro. Portanto, recomendo adicionar uma coluna com o título “Onde?” e fazendo uma única partida lá. Essa MATCH leva tanto tempo quanto a VLOOKUP de janeiro, mas as 12 funções INDEX levarão menos tempo do que a VLOOKUP de fevereiro, e você reduziu muito tempo. Novamente, cuidado com o $ na função INDEX em ambos os lugares, um pouco antes das linhas,e a outra antes das colunas, uma referência mista em ambas.
Ei, eu quero agradecer por você passar por aqui, nos vemos na próxima vez para outro netcast de!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2028.xlsx
