VLOOKUP é incrível e minha função favorita
Essas tabelas não apenas tornam a atualização dos dados mais fácil, mas também tornam a leitura de fórmulas muito mais fácil! A única coisa que você precisa fazer é pressionar Ctrl + T antes de escrever a fórmula.
Vamos voltar para a fórmula VLOOKUP acima. Desta vez, converta sua tabela de itens e sua tabela de compras em uma tabela Excel com Ctrl + T desde o início! Para tornar as coisas mais fáceis, dê a cada tabela um nome amigável usando a guia Ferramentas de Tabela:
Agora digite PROCV novamente sem fazer nada diferente do que você normalmente faz, sua fórmula em C2 agora é em =VLOOKUP((@Item),Items,2,0)vez de =VLOOKUP(B2,$E$5:$F$10,2,0)!
Mesmo se a tabela de itens estiver em uma planilha diferente, a fórmula é a mesma, em vez de menos legível =VLOOKUP(B2,Items!$A$2:$B$7,2,0).
O (@Item) na fórmula refere-se à célula na coluna Item desta tabela (na mesma linha da fórmula) e, portanto, é o mesmo em toda a coluna. E os itens referem-se a toda a tabela de itens (sem os cabeçalhos). O melhor de tudo é que você não precisa digitar nada disso. Uma vez que esta é uma tabela, o Excel colocará esses nomes em sua fórmula conforme você seleciona as células / intervalos!
Vamos dar um passo adiante. Adicione outra coluna à tabela Vendas para calcular a receita com a fórmula =(@Price)*(@Qty). Se agora você deseja calcular a receita total, a fórmula é =SUM(Sales(Revenue)); o que é realmente fácil de entender, não importa onde os dados estão ou quantas linhas cobrem!
Assistir vídeo
- VLOOKUP é incrível e minha função favorita
- Os que odeiam VLOOKUP reclamam que é frágil devido ao terceiro argumento
- Se a forma da sua tabela de pesquisa mudar, as respostas podem mudar
- Uma solução alternativa é substituir o terceiro argumento por MATCH
- Mas imagine fazer um MATCH para 1000 linhas de VLOOKUP
- Faça sua tabela de pesquisa em uma tabela antes de fazer VLOOKUP
- A referência de tabela estruturada tratará se a forma da tabela mudar
- Além disso, não requer fazer uma PARTIDA repetidamente
- Peter Albert enviou esta dica
Transcrição de vídeo
Aprenda Excel para Podcast, Episódio 2003 - Referências Legíveis
Não se esqueça de se inscrever na lista de reprodução XL. Estarei enviando um podcast para este livro inteiro.
Tudo bem, dica de hoje do Peter Albert. Peter Albert. Agora vamos falar sobre VLOOKUP. Eu sou um grande fã de VLOOKUP. Para mim, VLOOKUP é a linha divisória. Se você pode fazer VLOOKUPs, todo o resto no Excel será fácil para você. Então VLOOKUP nos permite pesquisar o preço dessa tabela e falaremos sobre VLOOKUPs mais tarde.
Então, copie isso e tudo funcionará bem, mas eu tenho que te dizer. Eu os vi. Eu conversei com eles. Eu os conheci. Existem haters VLOOKUP por aí. Pessoas que odeiam se você olhar para cima e quais outras reclamações é que é tão frágil, aquele terceiro argumento, onde dissemos que queríamos a terceira coluna, que se alguém decidir mais tarde que precisamos de um novo campo aqui, talvez tipo, tamanho . Tudo bem, em primeiro lugar, parece haver algum tipo de bug que o Excel não está recalculando tudo isso. Deixe-me desfazer, desfazer e refazer. Aqui vamos nós. Isso é estranho, eu tenho que relatar isso para a equipe do Excel, mas você vê que onde estávamos obtendo preço, agora está ficando colorido, porque foi codificado para dizer que eles querem a terceira coluna. Tudo bem e o que as pessoas fazem para contornar isso é essa coisa maluca com = MATCH.Procure a palavra Preço na primeira linha da tabela, F4,0, e isso nos dirá que o preço neste ponto é a quarta coluna. Então eles realmente farão = VLOOKUP. Estamos procurando A104, nesta tabela. F4 e então em vez de codificar o número quatro, eles vão fazer uma PARTIDA e a PARTIDA será bloqueada para o preço. Então F4, duas vezes para colocar $ antes de 1 e ele estará olhando através da primeira linha da tabela. Opa, F4 duas vezes, vírgula, falhou a vírgula. Tudo bem, pressione F4 aqui, vírgula 0 para uma correspondência exata para a correspondência e, em seguida, a vírgula cai para uma correspondência exata para VLOOKUP. Sim, ei, isso funciona muito bem e aqui eu só tenho seis deles, então não é grande coisa.nesta tabela. F4 e então em vez de codificar o número quatro, eles vão fazer uma PARTIDA e a PARTIDA será bloqueada para o preço. Então F4, duas vezes para colocar $ antes de 1 e ele estará olhando através da primeira linha da tabela. Opa, F4 duas vezes, vírgula, falhou a vírgula. Tudo bem, pressione F4 aqui, vírgula 0 para uma correspondência exata para a correspondência e, em seguida, a vírgula cai para uma correspondência exata para VLOOKUP. Sim, ei, isso funciona muito bem e aqui eu só tenho seis deles, então não é grande coisa.nesta tabela. F4 e então em vez de codificar o número quatro, eles vão fazer uma PARTIDA e a PARTIDA será bloqueada para o preço. Então F4, duas vezes para colocar $ antes de 1 e ele estará olhando através da primeira linha da tabela. Opa, F4 duas vezes, vírgula, falhou a vírgula. Tudo bem, pressione F4 aqui, vírgula 0 para uma correspondência exata para a correspondência e, em seguida, a vírgula cai para uma correspondência exata para VLOOKUP. Sim, ei, isso funciona muito bem e aqui eu só tenho seis deles, então não é grande coisa.Tudo bem, pressione F4 aqui, vírgula 0 para uma correspondência exata para a correspondência e, em seguida, a vírgula cai para uma correspondência exata para VLOOKUP. Sim, ei, isso funciona muito bem e aqui eu só tenho seis deles, então não é grande coisa.Tudo bem, pressione F4 aqui, vírgula 0 para uma correspondência exata para a correspondência e, em seguida, a vírgula cai para uma correspondência exata para VLOOKUP. Sim, ei, isso funciona muito bem e aqui eu só tenho seis deles, então não é grande coisa.
Veja se eu inserir um novo, ele se ajustará automaticamente e continuará obtendo o preço, mas imagine se você tivesse mil VLOOKUPs e cada VLOOKUP fosse refazer aquela correspondência para descobrir os preços na quinta ou quarta coluna. É horrível. As tabelas simplesmente resolvem esse problema. Então aqui está minha tabela VLOOKUP, muito antes de eu fazer qualquer coisa, vou clicar aqui e CTRL T para torná-la uma tabela real. Eles vão chamá-la de tabela 1, mas vou chamá-la de ProductTable, toda uma palavra, sem espaços: ProductTable. Portanto, agora tem um nome. Tudo bem, agora temos uma tabela chamada ProductTable. Então viemos aqui e dizemos que vamos fazer = INDEX desses preços. Qual preço queremos? Queremos o resultado da combinação de A104 nesses itens. Correspondência exata, feche parênteses para INDEX.Isso está fazendo apenas uma única partida. Não está fazendo uma correspondência e uma VLOOKUP. Mais ou menos, será muito, muito mais rápido. Copie isso. Tudo bem e depois se inserirmos o tamanho, então insira a coluna, o tamanho tudo continua funcionando porque está procurando a coluna chamada Preço e digamos que se mudarmos para Preço de Tabela, essa fórmula será reescrita. Certo, muito, muito mais seguro, caminho mais seguro de ir.
Tudo bem, tantos truques legais nas mesas. Confira este livro de Kevin Jones e Zach Barresse sobre tabelas do Excel. Todos os tipos de truques lá e tudo o que podcasting em agosto e setembro está neste livro lotado. Além de muita diversão. Piadas do Excel. Coquetéis Excel. Tuítes do Excel. Aventuras do Excel. Embalado em cores. Confira, compre este livro. Eu realmente apreciaria isto.
Tudo bem, episódio de hoje. VLOOKUP é incrível e é minha função favorita, mas há pessoas que odeiam VLOOKUP por aí que reclamam que ele é frágil devido ao terceiro argumento, se a forma de sua tabela VLOOKUP mudar, as respostas mudarão. Uma solução alternativa é substituir esse terceiro argumento por um MATCH, mas caramba, imagine fazer um MATCH para mil linhas de VLOOKUP. Portanto, transforme sua VLOOKUP em uma tabela antes de fazer a VLOOKUP. As referências da tabela de estrutura serão tratadas se a forma da tabela mudar. Além disso, você não está fazendo uma VLOOKUP e uma correspondência. Apenas uma única combinação junto com um INDEX e INDEX é extremamente rápido.
Obrigado a Peter Robert por esta dica e obrigado pela visita. Nos vemos na próxima vez, para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2003.xlsx
