A pergunta de hoje de Quentin que estava em meu seminário Atlanta Power Excel. Quentin tem que gerar as mesmas 7 perguntas de pesquisa para cada um dos mais de 1000 clientes no Excel.
Como você pode ver nesta figura, os clientes estão em A. As perguntas a serem repetidas estão na coluna D.
Você poderia resolver isso com VBA ou fórmulas, mas esta é a semana do Power Query, então vou usar um truque legal no Power Query.
Se você quiser uma linha em branco entre cada pesquisa, adicione um número de sequência e adicione o número 7 após a última pergunta.
Pressione Ctrl + T em ambos os conjuntos de dados. Nomeie o segundo conjunto de dados com um nome que você possa lembrar, algo como Perguntas ou Pesquisa.
Do segundo conjunto de dados, use Dados, Da Tabela.
O editor do Power Query é aberto. Na guia Home, escolha a lista suspensa Close & Load e escolha Close & Load To…. Na próxima caixa de diálogo, escolha Only Create a Connection.
Você está de volta ao Excel. Escolha qualquer célula na tabela do cliente na coluna A. Dados, da tabela. Depois que o Editor de Consultas abrir, clique na guia Adicionar coluna na faixa de opções e escolha Coluna personalizada. A fórmula é =#"Questions"(incluindo o # e as aspas).
Uma nova coluna aparece no editor com o valor Tabela repetido em cada linha. Clique no ícone Expandir no cabeçalho da coluna.
Escolha os dois campos da Tabela. Na guia Home, escolha Close & Load.
Uma nova planilha aparecerá com as 7 perguntas repetidas para cada um dos mais de 1000 clientes.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com o Podcast Episódio 2205: Explosão da Pesquisa de Empréstimos.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Agora, ontem no episódio 2204, foi Kaylee de Nashville que teve que fazer uma explosão VLOOKUP - para cada item aqui na coluna D, tínhamos um monte de itens correspondentes na coluna G e precisávamos explodi-los. Portanto, se o Palácio C tivesse 8 itens, obteríamos 8 linhas.
Agora, hoje, temos Quentin. Agora, Quentin estava em meu seminário em Atlanta, mas na verdade ele é da Flórida, e Quentin tem quase 1000 clientes aqui-- bem, mais de 1000 clientes-- na coluna A, e para cada cliente, ele precisa criar esta pesquisa- - esta pesquisa de 1, 2, 3, 4, 5, 6 perguntas. E o que vou fazer aqui é adicionar um número de sequência apenas com os números de 1 a 7, de modo que, assim, posso criar uma bela linha em branco no meio. Vou transformar esses dois conjuntos de dados em uma tabela; então, estamos tentando obter essas 7 linhas explodidas para cada um desses 1000 clientes. Esse é o objetivo.
Agora, posso fazer isso com VPA; Posso fazer isso com fórmulas; mas é uma espécie de "Power Query Week" aqui, estamos em uma corrida deste é o nosso terceiro exemplo de Power Query consecutivo, então vou usar o Power Query. Vou transformar este da esquerda em uma mesa. Terei muito cuidado para nomear esta não Tabela 1. Vou dar um nome a ela. Teremos que reutilizar esse nome mais tarde, então vou chamá-lo de Perguntas - assim. E então esta será a Tabela 2, mas vou renomear isso para Clientes - não é tão importante renomear este porque é o segundo que deve ter o nome. Então, vamos escolher isso; Dados; e vamos dizer Da tabela / intervalo. Obter e transformar dados - isso é conhecido como Power Query. É integrado ao Excel 2016. Se você tiver 2010 ou 2013, no Windows,não é um Mac, nem iOS, nem Android, você pode baixar o Power Query gratuitamente da Microsoft.
Então, vamos obter dados da Tabela / Intervalo; aqui está nossa mesa - não vamos fazer nada com ela, apenas Fechar e Carregar; Fechar e carregar para; apenas crie uma conexão; tudo bem, e veja, o nome dessa consulta é perguntas. Ele usa o mesmo nome que aqui. E então voltamos a este, e, Data; Da tabela / intervalo; então, há uma lista de nossos 1000 ou mais clientes.
Ei, um grito para Miguel Escobar, meu amigo, que é o co-autor de M Is For (DATA) MONKEY). Vou colocar um link para isso no vídeo - ótimo livro sobre Power Query - me ajudou com isso. Vamos colocar uma nova coluna personalizada, e a fórmula da coluna personalizada é esta aqui: = # "o nome da consulta". Eu nunca teria percebido isso sem Miguel, então, obrigado a Miguel por isso.
E quando eu clico em OK, sim, parece que não funcionou - nós apenas pegamos mesa, mesa, mesa, mas isso é exatamente o que tivemos ontem com Kaylee e os ingressos. E tudo o que tenho a fazer é expandir isso, e na verdade vou dizer que provavelmente não preciso da Sequência … bem, vamos colocá-la no caso. Podemos retirá-lo depois de vê-lo. No momento, temos 1000 linhas e agora temos 7.000 linhas - lindo. Posso ver agora que está aparecendo em Sequence, então não preciso disso. Vou clicar com o botão direito e remover apenas essa coluna. E então posso ir para casa; Fechar e carregar; e BAM! - agora devemos ter mais de 7.000 linhas com 6 perguntas e um espaço em branco para cada cliente. Quentin ficou emocionado com isso no seminário. Truque legal, legal - evita VBA, evita um monte de fórmulas usando o índice,e coisas assim - ótimo caminho a percorrer.
Mas, ei, hoje, deixe-me mandá-lo embora com M Is For (DATA) MONKEY. Ken Puls e Miguel Escobar escreveram o maior livro sobre Power Query. Eu amo esse livro; em 2 horas você se tornará um profissional com esse livro.
Tudo bem, conclua hoje - Quentin precisa gerar uma pesquisa idêntica para 1000 clientes diferentes. Existem 6, 7 ou 8 perguntas para cada cliente. Agora, poderíamos fazer isso com VBA ou macro, mas, uma vez que estamos executando um Power Query aqui, vamos fazer um Power Query. Eu adicionei uma pergunta em branco extra às Perguntas; Eu adicionei um número de sequência, para garantir que o espaço em branco permaneça lá; transformar os clientes em uma mesa; faça as perguntas em uma mesa; é muito importante que você nomeie Perguntas de algo que possa lembrar - chamei o meu de "Perguntas". Adicione as perguntas ao Power Query, apenas como uma conexão; e então, conforme você adiciona os clientes ao Power Query, crie uma nova coluna personalizada onde a fórmula seja: # "o nome da primeira consulta" e então expanda essa coluna no editor do Power Query; Perto &Carregue de volta na planilha e pronto. Um truque incrível - adoro o Power Query - a maior coisa que aconteceu ao Excel em 20 anos.
Quero agradecer a Quentin por ter mostrado no meu seminário. Ele já esteve no meu seminário algumas vezes antes - grande cara. Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo excel: Loop-Survey-Explosão.xlsx
O Power Query continua a me surpreender. Confira o livro M is for Data Monkey para saber mais sobre o Power Query.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Você pode fazer qualquer coisa com AGGREGATE, exceto entendê-lo."
Liam Bastick
