Excel VLOOKUP peut-il renvoyer tous les résultats et les joindre avec une virgule entre les deux?
Regarder la vidéo
- L'objectif est de concaténer toutes les réponses textuelles d'un RECHERCHEV
- Méthode de Bill: utilisez une fonction VBA appelée GetAll
- Liste unique utilisant Supprimer les doublons
- Méthode de Mike:
- Liste unique utilisant le filtre avancé
- Fonction TEXTJOIN ajoutée dans Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- En raison de la fonction IF, la formule nécessite Ctrl + Maj + Entrée chaque fois que vous modifiez la formule
- Alt AQOR Enter réexécutera le filtre avancé!
Transcription vidéo
Épisode 183: Rejoignez tous les matchs RECHERCHEV
Bill Jelen: Salut, bon retour. Il est temps pour un autre podcast Dueling Excel. Je suis Bill Jelen, je serai accompagné de Mike Girvin pour Excel Is Fun. Ceci est notre épisode 183: Rejoignez tous les matchs RECHERCHEV.
(La musique)
Très bien, la question d'aujourd'hui de Matt. VLOOKUP peut-il renvoyer tous les résultats et les joindre avec une virgule entre chacun d'eux. Par exemple, 109876 qui sont ces deux ici, peut-il renvoyer un espace virgule Low Oil Vérifié le 12/12. Et bien sûr, s'il y en avait plus, cela reviendrait plus. Très bien, donc ma solution ici va utiliser du VBA. Très bien, alors assurez-vous qu'il est enregistré en tant que xlsm ou vous ne pouvez pas exécuter VBA ou xlsb, mais pas xlsx - xlsx est le seul fichier qui ne peut pas exécuter VBA. Nous allons appuyer sur Alt + F11, assurez-vous que vous êtes sur Dual183 ou quel que soit le nom de votre classeur. Insérez le module dans le module vide et nous allons coller ce code, d'accord.
Jetons un coup d'œil à cette fonction GetAll, et voici le numéro d'identification que nous recherchons, puis la plage que nous voulons regarder. Et nous commençons, nous allons retourner une variable appelée GetAll, donc nous commençons avec cela égal à blank blank. Pour chaque cellule de ma plage, si la valeur de la cellule est ce que nous recherchons, nous allons prendre GetAll = GetAll & "", puis le Cell.Offset (0 lignes, 1 colonne), en d'autres termes la valeur c'est juste à côté de ce numéro d'identification car de retour dans VBA, voici le numéro d'identification. Si nous trouvons le numéro d'identification correspondant, nous voulons parcourir une colonne. Maintenant, que se passe-t-il si vous voulez dépasser 2 colonnes ou 3 colonnes, eh bien, vous changez ces 0 lignes et 1 colonne en 2. D'accord, vérifiez également si - nous ne mettons pas d'espace virgule si c'est le premier.Donc, si la variable GetAll est actuellement "", nous ne mettrons pas d'espace virgule, d'accord?
Alors maintenant que nous avons cette fonction ici, regardez à quel point il est facile de résoudre le problème de Matt. Nous allons venir ici et prenons ses identifiants, Ctrl + C et collez Ctrl + V comme ça. Données, supprimer les doublons, cliquez sur OK. Il y a donc une liste unique d'identifiants et ensuite nous voulons dire = getall et nous recherchons cette valeur dans la virgule E2. En parcourant cette plage ici, je vais appuyer sur F4. F4 fonctionne comme une fonction normale. Et en déplaçant à nouveau la question de Matt, double-cliquez pour l'abattre. Ça va marcher.
Et essayons juste, essayons quelque chose de fou ici. Faisons une phrase 1 et mettons-en quelques-unes comme les phrases 1 à 10. Nous signerons toutes celles-ci à 109999. Coller, puis coller ici. Copiez cette formule vers le bas, modifiez la formule pour qu'elle aille tout en bas, bien sûr. Ouaip. Et il renverra toutes ces phrases. D'accord, c'est donc ma solution, VBA, une petite fonction là-bas. Mike, voyons ce que tu as.
Mike Girvin: Merci ,. GetAll, c'est une fonction VBA géniale. Très bien, je vais passer à la feuille ici. Je l'ai déjà converti en un tableau Excel de sorte que lorsque nous ajoutons des enregistrements ci-dessous, j'espère que les choses seront mises à jour.
Maintenant, la première chose que je vais faire en deux parties. Je pourrais faire une formule ici pour extraire une liste unique, mais je veux regarder une autre option: le filtre avancé a une option de liste unique d'extrait et il peut être mis à jour. Je vais mettre en évidence uniquement les données de la colonne ID, sur Advanced Filter ou je vais utiliser le clavier Alt, A, Q. Maintenant, Filter list in-place, no way. Je souhaite le copier dans un autre emplacement. Il ne contient que la colonne A et parce que c'est un tableau Excel qui se développera plus tard. Je n'ai aucun critère, je veux le copier dans D1 et vérifier uniquement les enregistrements uniques. Cliquez sur OK.
Maintenant, je vais venir ici, Tous les commentaires entrent et je vais utiliser une fonction qui ne fonctionne que dans Excel 2016 Office 365: = fonction TEXTJOIN. Cette fonction à elle seule vaut la peine d'obtenir la dernière version d'Excel. C'est une tâche tellement courante que les gens veulent faire, unir beaucoup de choses ensemble. Maintenant, notre délimiteur dans «,», et la grande chose à propos de cette fonction est que nous pouvons lui dire d'ignorer les cellules vides. Maintenant, je peux mettre TRUE, 1 ou le laisser, l'omettre. Alors, je vais le laisser, l'omettre. Et c'est là que nous avons besoin de notre texte. Nous allons utiliser la fonction IF pour filtrer et obtenir uniquement les éléments que nous voulons. Je vais dire que vous regardez à travers toute cette colonne ici: Nom de la table, puis dans () le nom du champ, sont l'un d'entre vous = à cette référence de cellule relative, c'est le test logique. Si je devais cliquer dessus et appuyer sur la touche F9 pour évaluer,vous pouvez voir maintenant que nous n'avons que 2 VRAIS, Ctrl + Z maintenant je tape une virgule et avec le tableau de Vrais et Falses, maintenant je peux lui donner les éléments à choisir. Alors maintenant, nous ne sélectionnerons que les éléments qui ont un VRAI ici dans cette plage. Virgule et je veux m'assurer de mettre "" - qui apparaîtra comme une cellule vide en ce qui concerne le deuxième argument dans TEXTJOIN.
Maintenant, je vais fermer les parenthèses et maintenant la fonction IF créera cette chaîne de vrais et faux, les éléments réels de cette plage seront ramassés s'il le voit True et tous les autres éléments auront cette cellule vide. Et devine quoi? TEXTJOIN ignorera totalement toutes ces cellules vides et renverra uniquement les éléments qui correspondent à cet ID, puis le joindra à ce délimiteur. Maintenant, c'est certainement une formule Array qui nécessite la frappe spéciale Ctrol + Shift + Enter. L'argument de test logique contient notre opération Array et cet argument ne peut pas calculer correctement cette opération Array à moins que nous n'utilisions le clavier Ctrl + Maj + Entrée. Maintenant, je vais fermer les parenthèses. En fait, nous pourrions prouver 1 ici dans le texte 1 si je F9 tout cela, nous pourrions voir que nous obtenons les 2 éléments, le reste de ces cellules vides sera ignoré. Ctrl + Z. Maintenant, laisse'Entrez ceci dans la cellule avec Ctrl + Maj + Entrée. Regardez immédiatement la barre de formule. Ces accolades sont Excel vous indiquant qu'il a compris et calculé cela comme une formule de tableau. Maintenant, je peux double-cliquer et l'envoyer. Cela a l'air bien.
Je vais aller à la dernière cellule et appuyer sur F2 pour vérifier que toutes les plages sont correctement affichées. Maintenant, ce que je ne veux pas faire, c'est que je ne veux pas appuyer sur Entrée car cette formule après l'avoir mise en mode d'édition ne calculera correctement que si nous utilisons Ctrl + Maj + Entrée; ou, parce que nous avons déjà entré la formule, nous pouvons simplement utiliser la touche Échap pour revenir à ce qui se trouve dans la cellule avant de la mettre en mode d'édition.
Maintenant, testons ceci. Je vais cliquer dans la dernière cellule ici et appuyer sur Tab, puis taper un nouvel ID, Tab, Tab. Un autre nouvel album, Tab, et je vois déjà que je n'avais pas assez de travail ici. Je suis, nous allons mettre - Parfait et ensuite Entrer. Maintenant, cela ne va pas se mettre à jour automatiquement comme si nous avions un tas de formules que nous comptons des éléments uniques, puis extrayons des éléments uniques, mais pas de problème. Regarde ça. Nous pouvons mettre à jour cette liste d'enregistrements uniques car nous avons utilisé le filtre avancé et peu importe la cellule à partir de laquelle vous commencez, car lorsque le filtre avancé est appelé, il mémorise la plage d'extrait et les plages qu'il examinait à l'origine. Vous pouvez cliquer sur Filtre avancé ou utiliser le clavier Alt + A + Q. Nous devons sélectionner Copier vers un autre emplacement, mais regardez cela.Il est totalement mémorisé et étendu à A13 en raison de la fonctionnalité de tableau Excel. Il s'est souvenu de la gamme d'extraits. Je ne dois vérifier que les enregistrements uniques, mais cliquez sur OK.
Now, I have to come over and copy this formula down. And there you go, using Advanced Filter and the amazing TEXTJOIN function with, in Array operation to get just the items that match. Alright, throw back to.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Oh hé, je tiens à remercier tout le monde d'être passé. Nous vous reverrons la prochaine fois pour un autre podcast Dueling Excel de et Excel Is Fun.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Duel183.xlsm
