Table des matières
J'ai déjà écrit sur la façon dont vous pouvez rapidement coller du texte de plusieurs cellules en une seule et, inversement, analyser une longue chaîne de texte en composants. Examinons maintenant une tâche proche, mais légèrement plus complexe - comment coller du texte à partir de plusieurs cellules lorsqu'une certaine condition spécifiée est remplie.
Disons que nous avons une base de données de clients, où un nom d'entreprise peut correspondre à plusieurs e-mails différents de ses employés. Notre tâche consiste à collecter toutes les adresses par noms d'entreprise et à les concaténer (séparées par des virgules ou des points-virgules) afin de créer, par exemple, une liste de diffusion pour les clients, c'est-à-dire obtenir une sortie du type :
En d'autres termes, nous avons besoin d'un outil qui collera (liera) le texte en fonction de la condition - un analogue de la fonction SOMMESLI (SUMIF), mais pour le texte.
Méthode 0. Formule
Pas très élégant, mais le moyen le plus simple. Vous pouvez écrire une formule simple qui vérifiera si l'entreprise de la ligne suivante diffère de la précédente. Si elle ne diffère pas, collez l'adresse suivante séparée par une virgule. S'il diffère, alors nous "réinitialisons" l'accumulé, en recommençant:
Les inconvénients de cette approche sont évidents : de toutes les cellules de la colonne supplémentaire obtenue, nous n'avons besoin que des dernières pour chaque entreprise (jaune). Si la liste est longue, alors pour les sélectionner rapidement, vous devrez ajouter une autre colonne à l'aide de la fonction DLSTR (LEN), en vérifiant la longueur des chaînes accumulées :
Maintenant, vous pouvez filtrer ceux et copier le collage d'adresse nécessaire pour une utilisation ultérieure.
Méthode 1. Macrofonction de collage par une condition
Si la liste d'origine n'est pas triée par entreprise, la formule simple ci-dessus ne fonctionne pas, mais vous pouvez facilement vous déplacer avec une petite fonction personnalisée dans VBA. Ouvrez Visual Basic Editor en appuyant sur un raccourci clavier Alt + F11 ou en utilisant le bouton Visual Basic languette promoteur (Développeur). Dans la fenêtre qui s'ouvre, insérez un nouveau module vide via le menu Insertion – Module et copiez-y le texte de notre fonction :
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " les collages ne sont pas égaux entre eux - nous sortons avec une erreur If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Exit Function End If 'parcourez toutes les cellules, vérifiez la condition et collectez le texte dans la variable OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'affiche les résultats sans dernier délimiteur MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End fonction
Si vous revenez maintenant à Microsoft Excel, alors dans la liste des fonctions (bouton fx dans la barre de formule ou l'onglet Formules - Insérer une fonction) il sera possible de trouver notre fonction FusionnerSi dans la catégorie Défini par l'utilisateur (Défini par l'utilisateur). Les arguments de la fonction sont les suivants :
Méthode 2. Concaténer du texte par condition inexacte
Si nous remplaçons le premier caractère de la 13ème ligne de notre macro = à l'opérateur de correspondance approximative Comme, alors il sera possible d'effectuer un recollage par une correspondance inexacte des données initiales avec le critère de sélection. Par exemple, si le nom de l'entreprise peut être écrit dans différentes variantes, nous pouvons les vérifier et les collecter toutes avec une seule fonction :
Les caractères génériques standard sont pris en charge :
- astérisque (*) - indique n'importe quel nombre de caractères (y compris leur absence)
- point d'interrogation (?) - représente n'importe quel caractère unique
- signe dièse (#) - représente n'importe quel chiffre (0-9)
Par défaut, l'opérateur Like est sensible à la casse, c'est-à-dire qu'il comprend, par exemple, « Orion » et « orion » comme des sociétés différentes. Pour ignorer la casse, vous pouvez ajouter la ligne au tout début du module dans l'éditeur Visual Basic Option Comparer le texte, qui changera Like pour être insensible à la casse.
De cette manière, vous pouvez composer des masques très complexes pour vérifier les conditions, par exemple :
- ?1##??777RUS - sélection de toutes les plaques d'immatriculation de la région 777, en commençant par 1
- LLC* – toutes les sociétés dont le nom commence par LLC
- ##7## – tous les produits avec un code numérique à cinq chiffres, où le troisième chiffre est 7
- ?????? – tous les noms de cinq lettres, etc.
Méthode 3. Fonction macro pour coller du texte sous deux conditions
Dans le travail, il peut y avoir un problème lorsque vous devez lier le texte à plusieurs conditions. Par exemple, imaginons que dans notre tableau précédent, une colonne supplémentaire avec la ville ait été ajoutée et que le collage soit effectué non seulement pour une entreprise donnée, mais également pour une ville donnée. Dans ce cas, notre fonction devra être légèrement modernisée en y ajoutant un autre contrôle de plage :
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'caractères délimiteurs (peuvent être remplacés par un espace ou ; etc.) e.) 'si les plages de validation et de collage ne sont pas égales, sortir avec une erreur If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'parcourez toutes les cellules, vérifiez toutes les conditions et collectez le texte dans la variable OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'affiche les résultats sans dernier délimiteur MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Il sera appliqué exactement de la même manière - seuls les arguments doivent maintenant être spécifiés davantage :
Méthode 4. Regroupement et collage dans Power Query
Vous pouvez résoudre le problème sans programmation dans VBA, si vous utilisez le complément gratuit Power Query. Pour Excel 2010-2013, il peut être téléchargé ici, et dans Excel 2016, il est déjà intégré par défaut. La séquence des actions sera la suivante :
Power Query ne sait pas comment travailler avec des tables régulières, donc la première étape consiste à transformer notre table en une table "intelligente". Pour cela, sélectionnez-le et appuyez sur la combinaison Ctrl+T ou sélectionnez dans l'onglet Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau). Sur l'onglet qui apparaît alors Constructeur (Conception) vous pouvez définir le nom de la table (j'ai laissé le standard Tableau 1):
Chargeons maintenant notre table dans le complément Power Query. Pour cela, dans l'onglet Données (si vous avez Excel 2016) ou sur l'onglet Power Query (si vous avez Excel 2010-2013) cliquez sur De la table (Données — du tableau):
Dans la fenêtre de l'éditeur de requête qui s'ouvre, sélectionnez la colonne en cliquant sur l'en-tête Entreprise et appuyez sur le bouton ci-dessus Réservation de groupe (Par groupe). Entrez le nom de la nouvelle colonne et le type d'opération dans le regroupement - Toutes les lignes (Toutes les lignes):
Cliquez sur OK et nous obtenons un mini-tableau de valeurs groupées pour chaque entreprise. Le contenu des tableaux est clairement visible si vous faites un clic gauche sur le fond blanc des cellules (pas sur le texte !) dans la colonne résultante :
Ajoutons maintenant une colonne supplémentaire, où, à l'aide de la fonction, nous collons le contenu des colonnes d'adresse dans chacune des mini-tables, séparées par des virgules. Pour cela, dans l'onglet Ajouter une colonne nous pressons Colonne personnalisée (Ajouter une colonne — Colonne personnalisée) et dans la fenêtre qui apparaît, entrez le nom de la nouvelle colonne et la formule de couplage dans le langage M intégré à Power Query :
Notez que toutes les fonctions M sont sensibles à la casse (contrairement à Excel). Après avoir cliqué sur OK nous obtenons une nouvelle colonne avec des adresses collées :
Il reste à supprimer la colonne déjà inutile TableAdresses (clic droit sur le titre) Supprimer la colonne) et téléchargez les résultats sur la feuille en cliquant sur l'onglet Accueil — Fermer et télécharger (Accueil — Fermer et charger):
Nuance importante: Contrairement aux méthodes (fonctions) précédentes, les tables de Power Query ne sont pas mises à jour automatiquement. Si, à l'avenir, des modifications sont apportées aux données source, vous devrez cliquer avec le bouton droit n'importe où dans le tableau des résultats et sélectionner la commande Mettre à jour et enregistrer (Rafraîchir).
- Comment diviser une longue chaîne de texte en parties
- Plusieurs façons de coller du texte de différentes cellules dans une seule
- Utilisation de l'opérateur Like pour tester le texte par rapport à un masque