La question d'aujourd'hui de Quentin qui était à mon séminaire Atlanta Power Excel. Quentin doit générer les 7 mêmes questions d'enquête pour chacun des plus de 1000 clients dans Excel.
Comme vous pouvez le voir sur cette figure, les clients sont en A. Les questions à répéter se trouvent dans la colonne D.
Vous pouvez résoudre ce problème avec VBA ou des formules, mais c'est la semaine de Power Query à, donc je vais utiliser une astuce intéressante dans Power Query.
Si vous voulez une ligne vide entre chaque enquête, ajoutez un numéro de séquence et ajoutez le numéro 7 après la dernière question.
Appuyez sur Ctrl + T à partir des deux ensembles de données. Nommez le deuxième ensemble de données avec un nom dont vous vous souviendrez, quelque chose comme Questions ou Enquête.
À partir du deuxième ensemble de données, utilisez Data, From Table.
L'éditeur Power Query s'ouvre. Dans l'onglet Accueil, choisissez la liste déroulante Fermer et charger et choisissez Fermer et charger vers…. Dans la boîte de dialogue suivante, choisissez Créer uniquement une connexion.
Vous êtes maintenant de retour dans Excel. Choisissez une cellule du tableau client dans la colonne A. Données, à partir du tableau. Une fois l'éditeur de requête ouvert, cliquez sur l'onglet Ajouter une colonne dans le ruban, puis choisissez Colonne personnalisée. La formule est =#"Questions"(y compris le # et les guillemets).
Une nouvelle colonne apparaît dans l'éditeur avec la valeur Table répétée dans chaque ligne. Cliquez sur l'icône Développer dans l'en-tête de colonne.
Choisissez les deux champs dans le tableau. Dans l'onglet Accueil, choisissez Fermer et charger.
Une nouvelle feuille de travail apparaîtra avec les 7 questions répétées pour chacun des plus de 1000 clients.
Regarder la vidéo
Transcription vidéo
Apprenez Excel de l'épisode 2205 du podcast: Explosion de l'enquête sur les prêts.
Hé, bienvenue à nouveau sur le netcast, je suis Bill Jelen. Hier encore, dans l'épisode 2204, c'est Kaylee de Nashville qui a dû faire une explosion VLOOKUP - pour chaque élément ici dans la colonne D, nous avions un groupe d'articles correspondant dans la colonne G et nous devions les faire exploser. Donc, si Palace C avait 8 éléments, nous aurions 8 lignes.
Maintenant, aujourd'hui, nous avons Quentin. Maintenant, Quentin était à mon séminaire à Atlanta, mais il est en fait de Floride, et Quentin a près de 1000 clients ici - enfin, plus de 1000 clients - dans la colonne A, et pour chaque client, il doit créer cette enquête - - cette enquête de 1, 2, 3, 4, 5, 6 questions. Et ce que je vais faire ici, c'est que je vais ajouter un numéro de séquence juste avec les numéros 1 à 7, de cette façon, je peux créer une belle ligne vide entre les deux. Je vais transformer ces deux ensembles de données en un tableau; nous essayons donc de faire exploser ces 7 lignes pour chacun de ces 1000 clients. Voilà le but.
Maintenant, je peux le faire avec VPA; Je peux le faire avec des formules; mais c'est une sorte de "Power Query Week" ici, nous sommes sur une course de ceci est notre troisième exemple Power Query d'affilée, donc je vais utiliser Power Query. Je vais transformer celui de gauche en table. Je vais faire très attention de ne pas nommer ce tableau 1. Je vais lui donner un nom. Nous devrons réutiliser ce nom plus tard, alors je vais l'appeler Questions - comme ça. Et puis ce sera le tableau 2, mais je vais le renommer en Clients - pas si important que je renomme celui-ci car c'est le deuxième qui doit avoir le nom. Donc, nous allons choisir ceci; Les données; et nous allons dire From Table / Range. Obtenir et transformer des données - c'est ce qu'on appelle Power Query Il est intégré à Excel 2016. Si vous avez 2010 ou 2013, sous Windows,ni Mac, ni iOS, ni Android, vous pouvez télécharger Power Query gratuitement auprès de Microsoft.
Donc, nous allons obtenir des données de la table / plage; voici notre table - nous n'allons rien y faire, juste Fermer et charger; Fermer et charger vers; créer uniquement une connexion; très bien, et voyez, le nom de cette requête est Questions. Il utilise le même nom qu'ici. Et puis nous revenons à celui-ci, et, Data; À partir de la table / plage; donc, il y a une liste de nos 1000 clients ou plus.
Hé maintenant, voici un cri à Miguel Escobar, mon ami, qui est le co-auteur de M Is For (DATA) MONKEY). Je vais mettre un lien vers cela dans la vidéo - un excellent livre sur Power Query - m'a aidé avec cela. Nous allons mettre dans une toute nouvelle colonne personnalisée, et la formule de colonne personnalisée est la suivante: = # "le nom de la requête". Je n'aurais jamais compris ça sans Miguel, alors merci à Miguel pour ça.
Et quand je clique sur OK, ouais, ça n'a pas l'air de marcher - on a juste une table, une table, une table, mais c'est exactement ce que nous avons eu hier avec Kaylee et la billetterie. Et tout ce que j'ai à faire est de développer cela, et je vais en fait dire que je n'ai probablement pas besoin de la séquence … eh bien, mettons-la juste au cas où. Nous pouvons le retirer après l'avoir vu. À l'heure actuelle, nous avons 1000 lignes, et maintenant nous avons 7000 lignes - magnifique. Je peux voir maintenant qu'il apparaît dans Séquence, donc je n'en ai pas besoin. Je vais faire un clic droit et supprimer seulement cette colonne. Et puis je peux à la maison; Fermer et charger; et BAM! - nous devrions maintenant avoir plus de 7000 lignes avec 6 questions et un espace vide pour chaque client. Quentin était ravi de celui-là lors du séminaire. Astuce cool et cool - évite VBA, évite tout un tas de formules utilisant Index,et des choses comme ça - excellent chemin à parcourir.
Mais, hé, aujourd'hui, permettez-moi de vous envoyer avec M Is For (DATA) MONKEY. Ken Puls et Miguel Escobar ont écrit le plus grand livre sur Power Query. J'adore ce livre; dans 2 heures, vous deviendrez un pro avec ce livre.
Bon, alors, récapitulez aujourd'hui - Quentin doit générer une enquête identique pour 1000 clients différents. Il y a 6, 7 ou 8 questions pour chaque client. Maintenant, nous pourrions le faire avec VBA ou une macro, mais, puisque nous sommes sur une Power Query exécutée ici, faisons une Power Query. J'ai ajouté une question vierge supplémentaire aux questions; J'ai ajouté un numéro de séquence, pour m'assurer que le blanc reste là; faire des clients une table; faites les questions dans un tableau; il est vraiment important que vous nommiez Questions quelque chose dont vous vous souviendrez - j'ai appelé les miennes «Questions». Ajoutez les questions à Power Query, en tant que connexion uniquement; puis, lorsque vous ajoutez les clients à Power Query, créez une nouvelle colonne personnalisée dans laquelle la formule est: # "le nom de la première requête", puis développez cette colonne dans l'éditeur Power Query; Proche &Chargez à nouveau dans la feuille de calcul et vous avez terminé. Une astuce incroyable - j'adore Power Query - la plus grande chose qui soit arrivée à Excel en 20 ans.
Je tiens à remercier Quentin pour sa participation à mon séminaire. Il est allé à mon séminaire plusieurs fois auparavant - un gars formidable. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger le fichier Excel
Pour télécharger le fichier excel: prêt-enquête-explosion.xlsx
Power Query continue de m'étonner. Consultez le livre M is for Data Monkey pour en savoir plus sur Power Query.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Vous pouvez faire n'importe quoi avec AGGREGATE sauf le comprendre."
Liam Bastick
