Evite PROCV usando o Modelo de Dados. Portanto, você tem duas tabelas que precisam ser unidas a VLOOKUP antes de fazer uma tabela dinâmica. Se você tem o Excel 2013 ou mais recente em um PC com Windows, agora pode fazer isso de forma simples e fácil.
Digamos que você tenha um conjunto de dados com informações sobre produtos, clientes e vendas.
O departamento de TI esqueceu de colocar setor lá. Aqui está uma tabela de pesquisa que mapeia o cliente para o setor. É hora de uma VLOOKUP, certo?
Não há necessidade de fazer VLOOKUPs para juntar esses conjuntos de dados se você tiver o Excel 2013 ou Excel 2016. Ambas as versões do Excel incorporaram o mecanismo Power Pivot no Excel principal. (Você também pode fazer isso usando o suplemento Power Pivot para Excel 2010, mas existem algumas etapas extras.)
Tanto no conjunto de dados original quanto na tabela de consulta, use Home, Format as Table. Na guia Ferramentas de Tabela, renomeie a tabela de Tabela1 para algo significativo. Eu usei dados e setores.
Selecione uma célula na tabela de dados. Escolha Inserir, Tabela Dinâmica. A partir do Excel 2013, há uma caixa extra Adicionar esses dados ao modelo de dados que você deve selecionar antes de clicar em OK.
A lista Campos da tabela dinâmica é exibida com os campos da tabela Dados. Escolha receita. Como você está usando o Modelo de Dados, uma nova linha aparece no topo da lista, oferecendo Ativo ou Todos. Clique em tudo.
Surpreendentemente, a lista Campos da tabela dinâmica oferece todas as outras tabelas da pasta de trabalho. Isso é inovador. Você ainda não fez uma VLOOKUP. Expanda a tabela Setores e escolha Setor. Duas coisas acontecem para avisá-lo de que há um problema.
Primeiro, a tabela dinâmica aparece com o mesmo número em todas as células.
Talvez o aviso mais sutil seja uma caixa amarela exibida no topo da lista Campos da Tabela Dinâmica indicando que você precisa criar um relacionamento. Escolha Criar. (Se você estiver no Excel 2010 ou 2016, aproveite a detecção automática.)
Na caixa de diálogo Criar relacionamento, você tem quatro menus suspensos. Escolha Dados em Tabela, Cliente em Coluna (Estrangeiro) e Setores em Tabela relacionada. O Power Pivot preencherá automaticamente a coluna correspondente na coluna relacionada (primária). Clique OK.
A tabela dinâmica resultante é um mashup dos dados originais e da tabela de consulta. Não são necessários VLOOKUPs.
Assistir vídeo
- A partir do Excel 2013, a caixa de diálogo Pivot Table oferece o Modelo de Dados
- Esta é a palavra de código para Power Pivot Engine
- Para usar o modelo de dados, faça uma tabela Ctrl + T de cada tabela na pasta de trabalho
- Crie uma tabela dinâmica a partir da primeira tabela
- Na Lista de Campos da Tabela Dinâmica, mude de Ativo para Todos
- Escolha um campo da tabela de pesquisa
- Crie o relacionamento ou Detecte automaticamente
- Auto-Detect não estava lá em 2013
- Agradecimentos a Colin Michael e Alejandro Quiceno por sugerirem o Power Pivot em geral.
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2014 - Elimine VLOOKUP!
Para fazer o podcast de todo o livro, clique no “i” no canto superior direito da lista de reprodução!
Ei, bem-vindo de volta ao netcast, sou Bill Jelen, na verdade isso se chama Eliminar PROCV com o Modelo de Dados! Agora, peço desculpas, este é o Excel 2013 e mais recente, se você está de volta ao Excel 2010, você tem que baixar o suplemento Power Pivot, que obviamente é gratuito em 2010. Então o que temos aqui é o nosso conjunto de dados principal, há um campo Cliente aqui, e então eu tenho uma pequena tabela que mapeia o cliente para o setor, preciso criar a receita total por setor, certo? Este é um VLOOKUP, basta fazer um VLOOKUP, mas ei, graças ao Excel 2013, não precisamos fazer um VLOOKUP! Transformei ambos em uma tabela e, em Ferramentas de Tabela, Design, renomeio as tabelas, chamo isso de Setores e chamo esse de Dados, para transformá-lo em uma tabela, basta escolher uma célula, pressionar Ctrl + T. Então, se tivermos alguns títulos e alguns números, quando você pressiona Ctrl + T,eles perguntam “Onde estão os dados da sua tabela?”, Minha tabela tem cabeçalhos, e eles chamam de Tabela3, você chama de outra coisa. Certo, foi assim que criei essas duas tabelas, vou me livrar dessa tabela, certo.
Portanto, para que esse truque funcione, todos os dados precisam estar em tabelas. Vamos para a guia Inserir, escolha Tabela Dinâmica e, bem aqui na parte inferior, Adicionar esses dados ao Modelo de Dados. Isso parece muito inócuo, certo? Não há nada como um ponto luminoso que diz “Ei, vai deixar você fazer coisas incríveis!” E o que eles estão dizendo aqui, o que eles estão tentando não dizer é que- Oh, a propósito, cada cópia do Excel 2013 tem o mecanismo Power Pivot por trás dele. Você sabe, se você está no Office 365, está pagando $ 10 por mês, e eles querem que você pague $ 12 ou $ 15 por mês para obter o Power Pivot, os dois ou cinco dólares extras. Bem, ei, shh, não diga, na verdade você já tem a maior parte do Power Pivot no Excel 2013. Tudo bem, então clico em OK, leva um pouco mais de tempo para carregar o modelo de dados, certo, mas tudo bem, e logo Aqui,nos campos da tabela dinâmica, tenho uma lista de todos os campos. Então, eu quero mostrar Receita, com certeza, mas o que é diferente é aqui em cima com Ativo e Todos. Quando escolho Tudo, obtenho todas as tabelas da pasta de trabalho. Tudo bem, então eu vou para os Setores, e disse que quero colocar o setor na área de Linhas. Agora, inicialmente, o relatório vai estar errado, veja os 6,7 milhões até o fim, e este aviso amarelo aqui vai dizer que você tem que criar um relacionamento.e este aviso amarelo aqui vai dizer que você tem que criar um relacionamento.e este aviso amarelo aqui vai dizer que você tem que criar um relacionamento.
Tudo bem agora, em 2010 com o Power Pivot, iria apenas oferecer o AutoDetect, em 2013 eles tiraram o AutoDetect e em 2016 eles trouxeram o AutoDetect de volta, certo? Eu deveria mostrar a você a aparência de CRIAR, mas quando clico neste botão CRIAR, ah sim, é isso, certo, ótimo. Então, em nossa primeira tabela de dados, eu tenho um campo chamado Cliente, na tabela relacionada Setores, eu tenho um campo chamado Cliente e você clica em OK, certo. Mas deixe-me mostrar como o AutoDetect é legal, se por acaso você está em 2016, aí, eles descobriram, quão incrível é isso, certo? Você não precisa se preocupar com PROCV, e a vírgula cai no final, se PROCV fizer sua cabeça doer, você vai adorar o Modelo de Dados. Peguei essas duas tabelas, juntou-as, sabe, como o Access faria, eu acho, e criei uma tabela dinâmica, absolutamente incrível.Portanto, verifique o modelo de dados da próxima vez que você tiver que fazer um VLOOKUP entre duas tabelas. Bem, esta e todas as outras 40 dicas estão no livro, clique no “i” no canto superior direito. Você pode comprar o livro, ter uma referência cruzada completa para toda esta série de vídeos, todo o mês de agosto, todo o mês de setembro, diabos, podemos até mesmo transferir para outubro para terminar tudo.
Tudo bem, recapitule hoje: começando no Excel 2013, a caixa de diálogo Tabela Dinâmica oferece algo chamado Modelo de Dados, é a palavra de código para o mecanismo Power Pivot. Antes de criar suas tabelas dinâmicas, pressione Ctrl + T para fazer uma tabela de cada pasta de trabalho. Dediquei um tempo extra para nomear cada uma. Crie uma tabela dinâmica a partir da primeira tabela e, em seguida, na lista de campos, vá até o topo e mude de Ativo para Todos. Escolha um campo da tabela de pesquisa, e então ele irá avisá-lo que você deve criar um relacionamento, ou AutoDetect, em 2013, você deve clicar em CRIAR. Mas é o que, 4 cliques para criá-lo, 5 se você contar o botão OK, então muito, muito fácil de fazer.
Tudo bem, Colin, Michael e Alejandro Quiceno sugeriram o Power Pivot em geral para os livros, obrigado a eles, obrigado pela visita, nos vemos na próxima vez em outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2014.xlsx
