Kaley, de Nashville, está trabalhando em uma planilha de tíquetes. Para cada evento, ela escolhe um plano de ingressos. Esse plano de ingressos pode indicar de 4 a 16 tipos de ingressos para o evento. Kaley quer uma fórmula que vá para a tabela de pesquisa e retorne * todas * as correspondências, inserindo novas linhas conforme apropriado.
Embora eu não tenha um VLOOKUP que possa resolver isso, as novas ferramentas do Power Query integradas ao Excel 2016 podem resolvê-lo.
Nota
Se você tem a versão Windows do Excel 2010 ou Excel 2013, pode baixar o Power Query gratuitamente da Microsoft. Infelizmente, o Power Query ainda não está disponível para Excel para Android, Excel para ios ou Excel para Mac.
Para ilustrar o objetivo: Mike McCann and the Mechanics está aparecendo no Allen Theatre com o plano de ingressos C. Como há quatro linhas correspondentes na tabela de pesquisa, Kaley quer quatro linhas que digam Mike McCann and the Mechanics, cada uma com uma correspondência diferente de a tabela de pesquisa.
Selecione uma célula na tabela original. Pressione Ctrl + T para marcar esses dados como uma tabela. Na guia Ferramentas de Tabela, renomeie a tabela de Tabela 1 para Programas. Repita para a tabela de pesquisa, chamando-a de Tickets.
Selecione uma célula na tabela Shows. Na guia Dados, escolha Da Tabela / Intervalo.
Depois que o editor do Power Query for aberto, abra o menu suspenso Fechar e carregar e escolha Fechar e carregar para….
Na caixa de diálogo Importar dados, escolha Somente criar uma conexão.
Vá para a mesa de Tickets. Repita as etapas para Criar apenas uma conexão com tickets. Você deve ver as duas conexões no painel Consultas:
Selecione qualquer célula em branco. Escolha dados, obter dados, combinar consultas, mesclar.
Existem seis etapas na caixa de diálogo Mesclar. O terceiro e o quarto não me parecem intuitivos.
- Escolha Programas no menu suspenso superior
- Escolha Tickets no segundo menu suspenso.
- Clique no título de Plano de tíquete no topo para selecionar essa coluna como a chave estrangeira na tabela Shows.
- Clique no título de Plano de tíquete na parte inferior para selecionar essa coluna como o campo-chave na tabela de pesquisa.
- Abra o tipo de junção e escolha Inner (apenas linhas correspondentes).
- Clique OK
Os resultados são inicialmente decepcionantes. Você vê todos os campos da tabela 1 e uma coluna que diz Tabela, Tabela, Tabela.
Clique no ícone Expandir na parte superior da coluna Tickets.
Desmarque Plano de tíquete, pois você já tem esse campo. O campo restante se chamará Tickets.Ticket Type, a menos que você desmarque a opção Use Original Name as Prefix.
Sucesso! Cada linha de cada show explode em várias linhas.
Não estou particularmente satisfeito com a classificação dos dados. A classificação por data faz com que os tipos de tíquetes sejam classificados de maneira estranha.
Assistir vídeo
No caso de hoje, o vídeo foi filmado após a redação do artigo. Sugiro adicionar uma coluna de sequência aos Tipos de tíquete para controlar a ordem de classificação.
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2204: Retorne todos os VLOOKUPs.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje da Nashville Music City. Eu estava lá em Nashville, alguém é responsável por programar o carregamento de ingressos em um sistema de venda de ingressos e aqui está o que temos: Temos uma lista de eventos - eventos futuros - temos a data, o local e um plano de ingressos. Então, tipo, mesmo que algo seja realizado no Palace, pode haver planos de ingressos diferentes - tipo, talvez o andar esteja configurado, você sabe, com assentos ou talvez seja apenas uma sala só, certo?
Portanto, dependendo do tipo de plano de tíquete, você precisa ir até a tabela Lookup e encontrar todos os eventos correspondentes e, essencialmente, faremos o que chamo de explosão VLOOKUP. Então, se algo está em Hannah C, eles vão descer para Hannah C e se houver-- 1, 2, 3, 4, 5, 6-- 7 itens em Hannah C, vamos ter para retornar sete linhas - o que significa que você terá que inserir mais seis linhas e copiar esses dados. Tudo bem.
Agora, não vamos fazer isso com um VLOOKUP, mas você entendeu o conceito - estamos fazendo um VLOOKUP e retornando todas as respostas como novas linhas. Tudo bem, então, vou pegar essas duas tabelas e transformá-las em uma tabela real com Ctrl + T. Os primeiros chamados Tabela 1-- nome horrível, vamos chamar de Eventos ou Shows, vamos chamá-lo de Shows, assim - e o segundo, agora, ei, aqui está o que aprendi porque pratiquei isso - temos que ter um campo de sequência aqui. Portanto, = ROW (A1), clique duas vezes, copie e copie e cole os valores especiais. Tudo bem. Agora faremos isso em uma tabela - Ctrl + T, e chamaremos isso de Tickets.
Tudo bem. Então temos shows, temos ingressos. Vou para a guia Dados e estou aqui no show, quero dizer que quero obter meus dados de uma Tabela ou Intervalo - isso é Power Query, a propósito. Se você está de volta ao Excel 2010 ou 2013, pode baixá-lo gratuitamente da Microsoft, baixe a ferramenta Power Query. Se você estiver em um Mac ou iOS ou Android, desculpe, não há Power Query para você. Tudo bem, então em uma mesa ou intervalo … encontre alguém que tenha um … encontre um amigo que tenha um … PC com Windows e peça para ele configurar isso. Tudo bem. Aqui está uma tabela, não vamos fazer nada com isso, apenas Close & Load, Close & Load to, e então dizer "Only Create Connection", perfeito. Vamos passar aqui para a nossa segunda tabela: Obter dados, de uma tabela ou intervalo, não estamos fazendo nada com este, Fechar e carregar,Fechar e carregar para, "Apenas criar conexão", OK. Portanto, o que temos agora é uma conexão com a primeira tabela e uma conexão com a segunda tabela. Não vamos mesclar esses dois, o que essencialmente é como fazer VLOOKUP, ou uma junção de banco de dados, eu acho, é realmente o que é. Combine consultas, vamos mesclar. Tudo bem.
Agora, sete coisas que você precisa fazer nesta caixa de diálogo - e é um pouco confuso - vamos escolher Shows como a primeira tabela; escolha Tickets como a segunda mesa; escolha o campo que eles têm em comum, e isso pode ser vários campos - você pode clicar com o botão direito do mouse - mas, neste caso, há apenas um plano de bilhete; e então Plano de Ingresso; e então vamos alterar o tipo de junção para junção interna com "apenas as linhas correspondentes". Tudo bem. Agora, você clica em OK e acha que todo o seu problema será resolvido, mas você está esmagado porque aqui estão todos os dados de A - eles não inseriram nenhuma linha nova - e aqui, apenas um campo chato e estúpido chamado Tickets que só tem Mesa, Mesa, Mesa, hah.
Mas, felizmente, no topo disso está um ícone Expandir, e vamos expandir isso-- Eu não preciso fazer um plano, eu já tenho isso-- Tipo de Ticket e Sequência. Não quero que se chame Tickets.TicketType, que é o que o Power Query deseja fazer - então, desmarco esta caixa. Tudo bem. No momento, temos 17 linhas de dados; quando clico em OK, BAM! Aí está a explosão. Então, Michael Seeley e o Starlighter aparecem com todos os tipos de ingressos diferentes, assim. Tudo bem, e veja esses tipos de tíquetes aparecerem em sequência, isso é ótimo. Mas Michael Seeley não é o próximo show, o próximo show é no dia 5 de junho. Então, quando tento classificar isso por data - isso me deixa louco, não posso explicar isso. Classifique por data e Mike Man and the Mechanics chega a 65, mas os tíquetes estão todos bagunçados. Eles'está na sequência errada, e é por isso que eu tive que fazer essa sequência - parece que sim. Posso classificar por sequência. Então agora, 6, 5, lindo, e aí dentro, os Tickets estão corretos. E, na verdade, neste ponto, não precisamos mais dessa coluna. Então, posso clicar com o botão direito e remover e, em seguida, Fechar e carregar - desta vez, vou fechar e carregar, não Fechar e carregar para - e teremos nosso resultado. Tudo bem.
Então, passamos de uma lista de eventos para uma grande lista, mas aqui está a parte incrível: eu estraguei tudo, Mike Man and Mechanics não é o Palace B, é o Palace C. Então, volto ao original no canto superior direito - canto de mão para mais informações sobre o livro.
Tudo bem. Tópicos neste episódio: Kaley em Nashville precisa fazer um VLOOKUP para retornar todas as correspondências, geralmente inserindo novas linhas. E é um banco de dados de tickets, certo? Vou chamar isso de Explosão VLOOKUP porque cada show explodirá em até 16 linhas. Vamos usar o Power Query para resolver isso e aprendi que a data vai aparecer na sequência errada, a menos que adicionemos um campo Sequência ao tipo de ticket. Transforme ambos os conjuntos em uma Tabela com Ctrl + T; nomeie-os como Shows e Ingressos; e, a partir de cada tabela, obter dados, da tabela, fechar e carregar, para criar apenas uma conexão; repita para a outra mesa; em seguida, Data, Get Data, Combine Queries, Merge; e essa caixa de diálogo é muito confusa para mim - escolha Eventos, escolha Tickets, clique em Tipo de ticket em ambos, mude a junta para uma junção interna,clique em OK e você obterá aquele resultado terrivelmente decepcionante em que é apenas uma coluna que diz Tabela, Tabela, Tabela, Tabela; clique no ícone Expandir na parte superior; escolha o campo Sequência de tíquete; não prefixe com o nome da tabela; e você pode classificar por data, classificar por sequência; Fechar e carregar na planilha. O bonito é que, se os dados subjacentes mudarem, basta atualizar e você terá seus resultados.
Agora, ei, para baixar a pasta de trabalho usada no vídeo de hoje, visite o URL lá na descrição do YouTube. Também há uma lista dos próximos seminários - adoraria ver você em um dos meus seminários Power Excel ao vivo.
Quero agradecer a Kaley por aparecer em Nashville e me dar essa ótima pergunta. Eu quero você por passar por aqui. Vejo você na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo excel: return-all-vlookups.xlsx
O Power Query continua a me surpreender. Este é o segundo de uma série de três dias em que a resposta é Power Query:
- Terça-feira: Converta uma coluna de Data / Hora para apenas data
- Hoje: Retornar todos os VLOOKUPs
- Quinta-feira: Crie uma pesquisa para cada um dos 1100 itens
Tenho uma lista de reprodução do YouTube inteira com coisas que acabei resolvendo com o Power Query.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Em caso de dúvida, use a função ROUND!"
Mike Girvin
