Kaley de Nashville travaille sur une feuille de calcul de billetterie. Pour chaque événement, elle choisit un plan de billetterie. Ce plan de billetterie pourrait indiquer de 4 à 16 types de billets pour l'événement. Kaley veut une formule qui ira à la table de recherche et retournera * toutes * les correspondances, en insérant de nouvelles lignes le cas échéant.
Bien que je n'ai pas de RECHERCHEV qui puisse résoudre ce problème, les nouveaux outils Power Query intégrés à Excel 2016 peuvent le résoudre.
Remarque
Si vous disposez de la version Windows d'Excel 2010 ou d'Excel 2013, vous pouvez télécharger Power Query gratuitement auprès de Microsoft. Malheureusement, Power Query n'est pas encore disponible pour Excel pour Android, Excel pour iOS ou Excel pour Mac.
Pour illustrer l'objectif: Mike McCann and the Mechanics apparaît dans le Allen Theatre avec le plan de billetterie C. Puisqu'il y a quatre lignes correspondantes dans la table de recherche, Kaley veut quatre lignes qui indiquent Mike McCann et les Mechanics, chacune avec une correspondance différente de la table de recherche.
Sélectionnez une cellule dans le tableau d'origine. Appuyez sur Ctrl + T pour marquer ces données comme un tableau. Dans l'onglet Outils de tableau, renommez le tableau de Table1 en spectacles. Répétez l'opération pour la table de recherche, en l'appelant Tickets.
Sélectionnez une cellule dans le tableau Spectacles. Dans l'onglet Données, choisissez À partir de la table / plage.
Une fois l'éditeur Power Query ouvert, ouvrez la liste déroulante Fermer et charger et choisissez Fermer et charger vers….
Dans la boîte de dialogue Importer des données, choisissez Créer uniquement une connexion.
Accédez à la table Tickets. Répétez les étapes pour créer uniquement une connexion aux tickets. Vous devriez voir les deux connexions dans le volet Requêtes:
Sélectionnez une cellule vide. Choisissez Données, Obtenir des données, Combiner des requêtes, Fusionner.
La boîte de dialogue Fusionner comporte six étapes. Le 3ème et le 4ème ne me semblent pas intuitifs.
- Choisissez des émissions dans le menu déroulant supérieur
- Choisissez Billets dans la deuxième liste déroulante.
- Cliquez sur l'en-tête Plan de billets en haut pour sélectionner cette colonne comme clé étrangère dans le tableau des spectacles.
- Cliquez sur l'en-tête Plan de billets en bas pour sélectionner cette colonne comme champ clé dans la table de recherche.
- Ouvrez le type de jointure et choisissez Inner (uniquement les lignes correspondantes).
- Cliquez sur OK
Les résultats sont d'abord décevants. Vous voyez tous les champs de la table 1 et une colonne qui dit Table, Table, Table.
Cliquez sur l'icône Développer en haut de la colonne Tickets.
Désélectionnez Plan de billets car vous avez déjà ce champ. Le champ restant sera appelé Tickets.Ticket Type sauf si vous décochez Utiliser le nom d'origine comme préfixe.
Succès! Chaque ligne de chaque émission explose en plusieurs lignes.
Je ne suis pas particulièrement satisfait du tri des données. Le tri par date entraîne un tri étrange des types de billets.
Regarder la vidéo
Dans le cas d'aujourd'hui, la vidéo a été tournée après la rédaction de l'article. Je suggère d'ajouter une colonne de séquence aux types de billets pour contrôler l'ordre de tri.
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2204: renvoyer tous les RECHERCHES VIDÉO.
Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui de Nashville Music City. J'étais là-bas à Nashville, quelqu'un est responsable de la planification du chargement des billets dans un système de billetterie et voici donc ce que nous avons: Nous avons une liste d'événements - les événements à venir - nous avons la date, le lieu et un plan de billets. Donc, comme, même si quelque chose se passe au palais, il peut y avoir différents plans de billets - comme, peut-être que le sol est configuré, vous savez, avec des sièges ou peut-être juste une salle debout, non?
Donc, selon le type de plan de ticket, vous devez venir ici à la table de recherche et trouver tous les événements correspondants, et essentiellement nous allons faire ce que j'appelle une explosion RECHERCHEV. Donc, s'il y a quelque chose au Hannah C, ils vont descendre à Hannah C et s'il y a - 1, 2, 3, 4, 5, 6--7 articles dans Hannah C, nous allons avoir pour renvoyer sept lignes - ce qui signifie que vous allez devoir insérer six lignes supplémentaires et copier ces données vers le bas. Bien.
Maintenant, nous n'allons pas du tout faire cela avec une RECHERCHEV, mais vous comprenez le concept - nous faisons une RECHERCHEV et nous renvoyons toutes les réponses sous forme de nouvelles lignes. Très bien, donc, je vais prendre ces deux tables et en faire une vraie table avec Ctrl + T. Les premiers appelés Table 1 - nom horrible, appelons ça des événements ou des spectacles, appelons ça des spectacles, comme ça - et le second, maintenant, hé, voici ce que j'ai appris parce que j'ai pratiqué cela - nous devons avoir un champ de séquence ici. Donc = ROW (A1), double-cliquez et copiez-le vers le bas, puis copiez et collez les valeurs spéciales. Bien. Maintenant, nous en faisons un tableau - Ctrl + T, et nous l'appellerons Tickets.
Bien. Nous avons donc des spectacles, nous avons des billets. Je vais aller à l'onglet Données, et je suis ici dans la chose des émissions, je veux dire que je veux obtenir mes données à partir d'une table ou d'une plage - c'est Power Query, au fait. Si vous êtes de retour dans Excel 2010 ou 2013, vous pouvez le télécharger gratuitement auprès de Microsoft, téléchargez l'outil Power Query. Si vous êtes sur un Mac ou iOS ou Android, désolé, pas de Power Query pour vous. Très bien, donc à partir d'une table ou d'une plage… trouvez quelqu'un qui a un - trouvez un ami qui a un - PC Windows et demandez-lui de le configurer. Bien. Voici un tableau, nous n'allons rien faire pour cela, il suffit de Fermer et de charger, de Fermer et de charger vers, puis de dire "Créer uniquement une connexion", parfait. Nous allons venir ici à notre deuxième table: Obtenir des données, à partir d'une table ou d'une plage, nous ne faisons rien pour celle-ci, Fermer et charger,Fermer et charger dans, "Créer une connexion uniquement", OK. Donc, ce que nous avons maintenant, c'est que nous avons une connexion à la première table et une connexion à la deuxième table. Nous n'allons pas fusionner ces deux éléments, ce qui revient essentiellement à faire RECHERCHEV, ou un joint de base de données, je suppose, est vraiment ce que c'est. Combinez les requêtes, nous allons fusionner. Bien.
Maintenant, sept choses que vous devez faire dans cette boîte de dialogue - et c'est un peu déroutant - nous allons choisir Spectacles comme premier tableau; choisissez Tickets comme deuxième table; choisissez le champ qu'ils ont en commun, et il peut s'agir de plusieurs champs - vous pouvez contrôler-cliquer - mais dans ce cas, il n'y a qu'un seul plan de ticket; puis Plan de billets; puis nous allons changer le type de jointure en jointure interne avec "uniquement les lignes correspondantes". Bien. Maintenant, vous cliquez sur OK et vous pensez que tout votre problème va être résolu, mais vous êtes juste écrasé parce que voici toutes les données de A - ils n'ont pas inséré de nouvelles lignes du tout - et ici, juste un champ stupide et ennuyeux appelé Tickets qui a juste Table, Table, Table, hah.
Mais, heureusement, en haut de cela se trouve une icône Développer, et nous allons développer cela - je n'ai pas besoin de prendre un plan, j'ai déjà cela - Type de ticket et séquence. Je ne veux pas qu'il s'appelle Tickets.TicketType, ce que Power Query veut faire - je décoche donc cette case. Bien. À l'heure actuelle, nous avons 17 lignes de données; quand je clique sur OK, BAM! Voilà l'explosion. Ainsi, Michael Seeley et les Starlighter se présentent avec tous les différents types de billets, comme celui-ci. D'accord, et voir ces types de billets apparaître dans l'ordre, c'est génial. Mais Michael Seeley n'est pas le prochain spectacle, le prochain spectacle est le 5 juin. Alors quand j'essaye de trier ça par date - ça me rend fou, je ne peux pas l'expliquer. Trier par date, et Mike Man and the Mechanics arrive à 65, mais alors les billets sont tous foutus. Ils're sur la mauvaise séquence, et c'est pourquoi j'ai dû faire cette séquence - se sent comme ça. Je peux trier par séquence. Alors maintenant, 6, 5, beau, et puis à l'intérieur de cela, les billets sont corrects. Et en fait, à ce stade, nous n'avons plus besoin de cette colonne. Je peux donc cliquer avec le bouton droit de la souris et supprimer, puis Fermer et charger - cette fois, je vais en fait Fermer et charger, pas Fermer et charger vers - et nous avons notre résultat. Bien.
Nous sommes donc passés d'une liste d'événements à toute cette grande liste, mais voici la partie géniale: j'ai foiré ça, Mike Man and Mechanics n'est pas le Palace B, son Palace C. Donc je reviens à l'original en haut à droite - coin pour plus d'informations sur le livre.
Bien. Sujets de cet épisode: Kaley à Nashville doit effectuer une RECHERCHEV pour renvoyer toutes les correspondances, en insérant généralement de nouvelles lignes. Et c'est une base de données de billetterie, d'accord? Je vais donc appeler cela une explosion de RECHERCHEV parce que chaque émission explosera en 16 lignes maximum. Nous allons utiliser Power Query pour résoudre ce problème, et j'ai appris que la date va apparaître sur la mauvaise séquence à moins que nous ajoutions un champ de séquence au type de ticket. Transformez les deux ensembles en un tableau avec Ctrl + T; les nommer comme Spectacles et Billets; puis à partir de chaque table, obtenir des données, à partir de la table, fermer et charger, pour créer uniquement une connexion; répétez pour l'autre table; puis données, obtenir des données, combiner des requêtes, fusionner; et puis cette boîte de dialogue, c'est assez déroutant pour moi - choisissez Événements, choisissez Billets, cliquez sur Type de billet dans les deux, changez le joint en jointure interne,cliquez sur OK, et vous obtenez ce résultat horriblement décevant où il n'y a qu'une colonne qui dit Table, Table, Table, Table; cliquez sur l'icône Développer en haut de cela; choisissez le champ Séquence de ticket; ne préfixez pas le nom de la table; et vous pouvez trier par date, trier par séquence; Fermer et charger la feuille de calcul. La belle chose est que si les données sous-jacentes changent, il suffit de rafraîchir et vous obtenez vos résultats.
Maintenant, pour télécharger le classeur utilisé à partir de la vidéo d'aujourd'hui, visitez l'URL ci-dessous dans la description YouTube. Également une liste des prochains séminaires - j'aimerais vous voir à l'un de mes séminaires en direct sur Power Excel.
Je tiens à remercier Kaley d'être venu à Nashville et de m'avoir posé cette excellente question. Je veux que tu sois passé. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger le fichier Excel
Pour télécharger le fichier Excel: return-all-vlookups.xlsx
Power Query continue de m'étonner. Ceci est le deuxième d'une série de trois jours où la réponse est Power Query:
- Mardi: Convertissez une colonne de Date / Heure en une seule date
- Aujourd'hui: renvoyer tous les RECHERCHEV
- Jeudi: créez une enquête pour chacun des 1100 éléments
J'ai toute une liste de lecture YouTube de choses que j'ai fini par résoudre avec 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:
"En cas de doute, utilisez la fonction ROUND!"
Mike Girvin
