Note: The other languages of the website are Google-translated. Back to English

Comment concaténer facilement du texte en fonction de critères dans Excel?

Supposons que j'ai une colonne de numéros d'identification qui contient des doublons et une colonne de noms, et maintenant, je veux concaténer les noms en fonction des numéros d'identification uniques comme illustré à gauche, pour combiner rapidement le texte en fonction de critères, comment pourrions-nous faire dans Excel?

doc combine le texte en fonction du critère 1

Concaténer le texte en fonction de critères avec la fonction définie par l'utilisateur

Concaténer le texte en fonction de critères avec Kutools for Excel


Pour combiner du texte avec les numéros d'identification uniques, vous pouvez d'abord extraire les valeurs uniques, puis créer une fonction définie par l'utilisateur pour combiner les noms en fonction de l'ID unique.

1. Prenez les données suivantes comme exemple, vous devez d'abord extraire les numéros d'identification uniques, veuillez appliquer cette formule matricielle: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Entrez cette formule dans une cellule vide, D2 par exemple, puis appuyez sur Ctrl + Maj + Entrée clés ensemble, voir capture d'écran:

doc combine le texte en fonction du critère 2

Conseil : Dans la formule ci-dessus, A2: A15 est la plage de données de liste à partir de laquelle vous souhaitez extraire des valeurs uniques, D1 est la première cellule de la colonne dans laquelle vous souhaitez afficher le résultat de l'extraction.

2. Et puis faites glisser la poignée de remplissage vers le bas pour extraire toutes les valeurs uniques jusqu'à ce que les blancs s'affichent, voir capture d'écran:

doc combine le texte en fonction du critère 3

3. Dans cette étape, vous devez créer un Fonction définie par l'utilisateur pour combiner les noms en fonction des numéros d'identification uniques, maintenez la touche ALT + F11 clés, et il ouvre le Microsoft Visual Basic pour applications fenêtre.

4. Cliquez insérer > Moduleet collez le code suivant dans le Module Fenêtre.

Code VBA: concaténer du texte en fonction de critères

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. Ensuite, enregistrez et fermez ce code, revenez à votre feuille de calcul et entrez cette formule dans la cellule E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , voir capture d'écran:

doc combine le texte en fonction du critère 4

6. Faites ensuite glisser la poignée de remplissage vers les cellules auxquelles vous souhaitez appliquer cette formule, et tous les noms correspondants ont été combinés en fonction des numéros d'identification, voir capture d'écran:

doc combine le texte en fonction du critère 5

Conseils:

1. Dans la formule ci-dessus, A2: A15 correspond aux données originales sur lesquelles vous souhaitez combiner, D2 est la valeur unique que vous avez extraite, et B2: B15 est la colonne de nom que vous souhaitez combiner.

2. Comme vous pouvez le voir, j'ai combiné les valeurs qui sont séparées par une virgule, vous pouvez utiliser n'importe quel autre caractère en changeant la virgule «,» de la formule selon vos besoins.


Si vous avez Kutools pour Excel, Avec son Lignes de combinaison avancées utilitaire, vous pouvez rapidement et commodément concaténer la base de texte sur des critères.

Kutools pour Excel : avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation en 30 jours.

Après l'installation de Kutools pour Excel, procédez comme suit:

1. Sélectionnez la plage de données que vous souhaitez combiner en fonction d'une colonne.

2. Cliquez Kutools > Fusionner et fractionner > Lignes de combinaison avancées, voir capture d'écran:

3. Dans le Combiner des lignes en fonction de la colonne boîte de dialogue, cliquez sur la colonne ID, puis sur Clé primaire pour faire de cette colonne la colonne clé sur laquelle vos données combinées sont basées, voir capture d'écran:

doc combine le texte en fonction du critère 7

4. Et puis cliquez Nom colonne dont vous souhaitez combiner les valeurs, puis cliquez sur Combiner option, et choisissez un séparateur pour les données combinées, voir capture d'écran:

doc combine le texte en fonction du critère 8

5. Une fois ces paramètres définis, cliquez sur OK pour quitter la boîte de dialogue, et les données de la colonne B ont été combinées en fonction de la colonne clé A. Voir capture d'écran:

doc combine le texte en fonction du critère 9

Avec cette fonctionnalité, le problème suivant sera résolu dès que possible:

Comment combiner plusieurs lignes en une seule et additionner les doublons dans Excel?

Téléchargez et essayez gratuitement Kutools for Excel Now!


Kutools pour Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!

Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Commentaires (38)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
C'est une excellente solution (code VBA) et elle a répondu à mes besoins en quelques minutes. Je référerai votre site à d'autres et je visiterai pour tout ce dont j'ai besoin pour aller de l'avant.
Ce commentaire a été minimisé par le modérateur sur le site
Merci pour ce code. C'était EXACTEMENT ce dont j'avais besoin. Vous m'avez épargné beaucoup d'efforts, merci beaucoup.
Ce commentaire a été minimisé par le modérateur sur le site
Merci d'avoir posté c'est exactement ce que je recherche. Il me semble que je n'enregistre pas correctement le code vba. Je reçois un message d'erreur concernant un nom ambigu trouvé. Des suggestions ou étape par étape sur l'étape VBA de ce projet? Merci
Ce commentaire a été minimisé par le modérateur sur le site
Cet outil pourra-t-il gérer les combinaisons sensibles à la casse telles que jABC 123 abc 345 ABc 678 ABC 912
Ce commentaire a été minimisé par le modérateur sur le site
Ce code VBA m'a sauvé la mise. Merci!
Ce commentaire a été minimisé par le modérateur sur le site
Je cherche un moyen d'utiliser une variante de ce code pour créer une liste de variantes basée sur la variante principale. En utilisant vos données d'exemple, je devrais combiner les colonnes A et B en identifiants uniques, puis concaténer ces identifiants à chaque ligne en fonction de la valeur de la colonne A, en excluant la valeur de la combinaison pour cette ligne, et le reste en tri alpha ordre: liste principale variante id id nom CN20150012 Lucy CN20150012-Lucy CN20150012-Andy CN20150012-Monica CN20150012-Phiby US20150011 Tommas US20150011-Tommas US20150011-Rose CN20150012 Monica CN20150012-Monica CN20150012-Andy CN20150012-Lucy CN20150012-Phiby CN20150012 Phiby CN20150012-Phiby CN20150012 -Andy CN20150012-Lucy CN20150012-Monica US20150011 Rose US20150011-Rose US20150011-Tommas UK20150014 Peter UK20150014-Peter UK20150014-Anith UK20150014-Kristi UK20150014-Libin JP20150010 Ramon JP20150010-Ramon JP20150010-Brenda JP20150010-James UK20150014 Libin UK20150014-Libin UK20150014-Anith UK20150014 -Kristi UK20150014-Peter UK20150014 Anith UK20150014-Anith UK20150014-Kristi UK20150014-Libin UK20150014-Peter JP20150010 James JP20150010 20150010-James JP20150010-Brenda JP20150010-James JP20150012-Matus CN20150012 Andy CN20150012-Andy CN20150012-Lucy CN20150012-Monica CN20150014-Phiby UK20150014 Matus UK20150010-Matus JP20150010-Brenda JP20150014-James UK20150014 Kristi UK20150014-Kristi UK20150014-Anith UK20150014-Libin UK20150010- Peter JP20150010 Brenda JP20150010-Brenda JP20150010-James JP1000-Ramon J'ai une feuille avec plus de 4 lignes, chaque article est livré avec jusqu'à XNUMX variantes. Essayer de le faire manuellement est impossible mais je ne trouve pas de solution qui corresponde à mes besoins.
Ce commentaire a été minimisé par le modérateur sur le site
Prendre plus de temps pour mettre à jour la même formule concatenateif(). j'ai 5000 lignes. et ses plus de 2 heures maintenant sa mise à jour toujours :( Une résolution pour le faire fonctionner rapidement ?
Ce commentaire a été minimisé par le modérateur sur le site
Expliqué en détail et facile à comprendre, vraiment aidé quand j'étais coincé exactement dans la même situation.
Ce commentaire a été minimisé par le modérateur sur le site
Extrêmement utile et bien expliqué
Ce commentaire a été minimisé par le modérateur sur le site
Super merci! J'ai utilisé la solution VBA et cela a très bien fonctionné.
Ce commentaire a été minimisé par le modérateur sur le site
Génial!!! Merci beaucoup!
Ce commentaire a été minimisé par le modérateur sur le site
Cela ne fonctionne pas pour la gamme Big Data. J'ai trouvé que sa plage de travail n'est que jusqu'à A2: A362. Nous vous serions reconnaissants de partager la solution pour la gamme de données volumineuses comme A2: A200000 .... Merci
Ce commentaire a été minimisé par le modérateur sur le site
Fonctionne très bien juste lentement. Je le fais avec 27 XNUMX lignes de texte dans Excel, lancez-le et laissez-le fonctionner
Ce commentaire a été minimisé par le modérateur sur le site
Cela ne fonctionne pas pour la gamme Big Data. J'ai trouvé que sa plage de données de travail va jusqu'à A2: A362. Nous vous serions reconnaissants de partager la solution pour couvrir la plage de données plus large comme A2: A200000 ..... Merci
Ce commentaire a été minimisé par le modérateur sur le site
Salut! concactenateif est exactement ce que je cherchais. Mais malheureusement, je ne peux pas le faire fonctionner. Obtenez toujours une erreur de compilation : erreur de syntaxe. Des idées? Dans le passé, avec certains modules VBA importés, j'ai remarqué qu'il fallait remplacer le "," par ";" comme sur mon PC, peut-être en raison de mes paramètres régionaux, c'est la seule façon dont cela fonctionne. Utilisez avidement les sumifs intégrés, etc. Mais je ne comprends pas où je me trompe sur celui-ci. Une autre possibilité qui me vient à l'esprit est le fait que dans office 365, "concat" remplace "concactenate". Pouvez-vous aider s'il vous plaît? Merci d'avance Yash
Ce commentaire a été minimisé par le modérateur sur le site
Le code utilise des espaces insécables pour l'indentation, ceux-ci déclenchent Excel2016. Difficile de repérer une erreur invisible..
Ce commentaire a été minimisé par le modérateur sur le site
J'ai eu un problème après avoir collé ce code dans Excel 2016 - il contient des espaces non réguliers (peut-être des espaces insécables ?) qui génèrent des erreurs de syntaxe qui ne sont pas évidentes, peu importe à quel point vous regardez car elles sont invisibles ! Ce sont les espaces d'indentation qui posent problème. Collez le code dans Word et activez les caractères masqués pour les voir.
Ce commentaire a été minimisé par le modérateur sur le site
Ouah!! Génie! A fonctionné comme un charme! Il y a des espaces qui apparaissent comme un personnage différent. Merci beaucoup Dav! Je me demande comment vous est venue l'idée ! Aussi, demandez-vous comment cela fonctionne pour d'autres personnes. Quoi qu'il en soit, merci encore !
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de le faire sur Mac ????
C'est exactement ce dont j'ai besoin - faites-le moi savoir (ou si un logiciel mac le ferait que vous connaissez). THX
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen d'appliquer cette fonction CONCATENATEIF dans une feuille séparée? Cela fonctionne lorsque je le mets dans la même feuille que les données d'entrée, mais j'ai besoin des deux tables dans des feuilles différentes et cela ne fonctionne pas.
Ce commentaire a été minimisé par le modérateur sur le site
Oui, ce que vous voulez faire, c'est ajouter la fonction à un module. Allez dans l'éditeur VBA, faites un clic droit sur "VBAProject" dans l'explorateur de projet, passez la souris sur l'élément de menu "Insérer", et dans ce sous-menu, choisissez "Module". Toutes les fonctions que vous y mettez seront utilisables sur n'importe quelle feuille de votre classeur.
Ce commentaire a été minimisé par le modérateur sur le site
Salut les gars, j'ai une erreur #NAME ? lorsque j'applique des formules CONCATENATEIF dans un fichier Excel après avoir défini le code VBA pour cela, quelqu'un pourrait-il m'aider à le résoudre, merci beaucoup
Ce commentaire a été minimisé par le modérateur sur le site
Tellement facile, merci :)
Ce commentaire a été minimisé par le modérateur sur le site
Est-il possible de remplacer le séparateur de virgule par un saut de ligne, c'est-à-dire char(10) ? Merci beaucoup.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour David,

Pour combiner les cellules avec un saut de ligne, la fonction définie par l'utilisateur suivante peut vous aider.

Fonction ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
Si CriteriaRange.Count <> ConcatenateRange.Count Alors
ConcaténerSi = CVErr(xlErrRef)
Fonction de sortie
Si fin
Pour I = 1 Vers CriteriaRange.Count
Si CriteriaRange.Cells(I).Value = Condition Alors
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
Si fin
Ensuite je
Si xRésultat <> "" Alors
xRésultat = VBA.Mid(xRésultat, VBA.Len(Séparateur) + 1)
Si fin
ConcaténerSi_LineBreak = xRésultat
Fonction de sortie
Fonction de fin

Après avoir collé ce code, appliquez cette formule : =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

Après avoir obtenu les résultats avec cette formule, vous devez cliquer sur Wrap Text pour obtenir les résultats corrects dont vous avez besoin.
Ce commentaire a été minimisé par le modérateur sur le site
merci beaucoup! C'était si simple et a beaucoup aidé !!
Il n'y a pas encore de commentaires postés ici
TÉLÉCHARGER PLUS
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0  Personnages
Emplacements suggérés