Excel: trouver et remplacer plusieurs valeurs à la fois

Dans ce tutoriel, nous examinerons plusieurs façons de trouver et de remplacer plusieurs mots, chaînes ou personnages individuels, afin que vous puissiez choisir celui qui convient le mieux à vos besoins.

Comment les gens recherchent-ils habituellement dans Excel? Surtout, en utilisant la fonction Recherche et remplacer, ce qui fonctionne bien pour les valeurs uniques. Mais que se passe-t-il si vous avez des dizaines ou même des centaines d'articles à remplacer? Sûrement, personne ne voudrait faire tous ces remplacements manuellement un par un, puis recommencer lorsque les données changent. Heureusement, il existe quelques méthodes plus efficaces pour effectuer la masse en masse dans Excel, et nous allons enquêter sur chacun d'eux en détail.

• Formule de substitut imbriqué pour remplacer plusieurs valeurs
• Formule xlookup pour rechercher plusieurs mots
• Multi-Replace utilisant une fonction lambda récursive
• Trouver et remplacer en masse par UDF
• Macro de remise en vrac pour trouver et remplacer plusieurs valeurs
• Trouver et remplacer plusieurs outils de sous-chaîne

Trouver et remplacer plusieurs valeurs par un substitut imbriqué

Le moyen le plus simple de trouver et de remplacer plusieurs entrées dans Excel est d'utiliser la fonction de substitution.

La logique de la formule est très simple: vous écrivez quelques fonctions individuelles pour remplacer une vieille valeur par une nouvelle. Et puis, vous nichez ces fonctions les unes dans les autres, de sorte que chaque substitut ultérieur utilise la sortie du substitut précédent pour rechercher la valeur suivante.

Substitut (substitut (substitut ( texte , old_text1 , new_text1 ), old_text2 , new_text2 ), old_text3 , new_text3 )

Dans la liste des emplacements dans A2: A10, supposons que vous souhaitiez remplacer les noms de pays abrégés (tels que FR , Royaume-Uni et USA ) par des noms complets.

Pour le faire, entrez les anciennes valeurs en D2: D4 et les nouvelles valeurs dans E2: E4 comme indiqué dans la capture d'écran ci-dessous. Et puis, mettez la formule ci-dessous en B2 et appuyez sur Entrée:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)

… Et vous aurez tous les remplacements effectués en même temps:

Veuillez noter que l'approche ci-dessus fonctionne uniquement dans Excel 365 qui prend en charge les tableaux dynamiques.

Dans les versions pré-dynamiques d'Excel 2019, Excel 2016 et plus tôt, la formule doit être écrite pour la cellule la plus haute (B2), puis copiée dans les cellules ci-dessous:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

Veuillez faire attention que, dans ce cas, nous verrouillons les valeurs de remplacement par des références de cellules absolues, afin qu'elles ne changent pas lors de la copie de la formule.

Note. La fonction de substitut est sensible à la casse , ce qui signifie que vous devez taper les anciennes valeurs ( old_text ) dans le même cas de lettre qu'ils apparaissent dans les données d'origine.

Aussi simple que possible, cette méthode présente un inconvénient important - lorsque vous avez des dizaines d'éléments à remplacer, les fonctions imbriquées deviennent assez difficiles à gérer.

Avantages : facile à mettre en œuvre; Prise en charge dans toutes les versions Excel

Inconvénients : Il est préférable d'être utilisé pour un nombre limité de valeurs de recherche / remplacement

Rechercher et remplacer plusieurs entrées par xlookup

Dans la situation où vous cherchez à remplacer l' intégralité du contenu cellulaire , pas sa partie, la fonction xlookup est utile.

Disons que vous avez une liste de pays de la colonne A et visez à remplacer toutes les abréviations par les noms complets correspondants. Comme dans l'exemple précédent, vous commencez par saisir les éléments "Rechercher" et "remplacer" dans des colonnes distinctes (D et E respectivement), puis entrez cette formule dans B2:

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

Traduit de la langue Excel dans le langage humain, voici ce que fait la formule:

Recherchez la valeur A2 (lookup_value) dans d2: d4 (lookup_array) et renvoyez une correspondance de e2: e4 (return_array). Si cela n'est pas trouvé, tirez la valeur d'origine de A2.

Double-cliquez sur la poignée de remplissage pour obtenir la formule copiée dans les cellules ci-dessous, et le résultat ne vous fera pas attendre:

Étant donné que la fonction XLookup n'est disponible que dans Excel 365, la formule ci-dessus ne fonctionnera pas dans les versions antérieures. Cependant, vous pouvez facilement imiter ce comportement avec une combinaison d'iFerror ou d'IFNA et de Vlookup:

=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)

Note. Contrairement au substitut, les fonctions XLookup et VLookup ne sont pas sensibles à la casse , ce qui signifie qu'ils recherchent les valeurs de recherche en ignorant le cas de lettre. Par exemple, notre formule remplacerait FR et FR par la France .

Avantages : utilisation inhabituelle des fonctions habituelles; Fonctionne dans toutes les versions Excel

Inconvénients : fonctionne au niveau de la cellule, ne peut pas remplacer une partie du contenu cellulaire

Remplacement multiple en utilisant une fonction lambda récursive

Pour les abonnés Microsoft 365, Excel fournit une fonction spéciale qui permet de créer des fonctions personnalisées à l'aide d'un langage de formule traditionnel. Oui, je parle de Lambda. La beauté de cette méthode est qu'elle peut convertir une formule très longue et complexe en une formule très compacte et simple. De plus, il vous permet de créer vos propres fonctions qui n'existent pas dans Excel, ce qui était avant possible avec VBA.

Pour les informations détaillées sur la création et l'utilisation de fonctions Lambda personnalisées, veuillez consulter ce tutoriel: comment écrire des fonctions lambda dans Excel. Ici, nous discuterons de quelques exemples pratiques.

Avantages : Le résultat est une fonction élégante et incroyablement simple à utiliser, quel que soit le nombre de paires de remplacement

Inconvénients : Disponible uniquement dans Excel 365; spécifique au classeur et ne peut pas être réutilisé sur différents classeurs

Exemple 1. Recherche et remplacer plusieurs mots / chaînes à la fois

Pour remplacer plusieurs mots ou texte en une seule fois, nous avons créé une fonction Lambda personnalisée, nommée Multireplace , qui peut prendre l'une de ces formulaires:

=LAMBDA(text, old, new, IF(old"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))

Ou

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

Les deux sont des fonctions récursives qui s'appellent eux-mêmes. La différence n'est que dans la façon dont le point de sortie est établi.

Dans la première formule, la fonction IF vérifie si l' ancienne liste n'est pas vide (old ""). Si c'est vrai, la fonction multi-esclace est appelée. Si faux, la fonction renvoie le texte de sa forme actuelle et sort.

La deuxième formule utilise la logique inverse: si l'ancien est vierge (old = ""), puis renvoyer le texte et quitter; Sinon, appelez multiples .

La partie la plus délicate est accomplie! Ce qui vous reste à faire, c'est de nommer la fonction multiple dans le gestionnaire de noms comme indiqué dans la capture d'écran ci-dessous. Pour les directives détaillées, veuillez consulter comment nommer une fonction Lambda.

Une fois que la fonction obtient un nom, vous pouvez l'utiliser comme toute autre fonction intégrée.

Quelle que soit les deux variations de formule que vous choisissez, dans le point de vue de l'utilisateur final, la syntaxe est aussi simple que celle-ci:

Multi-emplacements (texte, ancien, nouveau)

Où:

• Texte - les données source
• Old - les valeurs à trouver
• Nouveau - les valeurs à remplacer par

Prenant l'exemple précédent un peu plus loin, remplaçons non seulement les abréviations du pays, mais aussi les abréviations de l'État. Pour cela, tapez les abréviations ( anciennes valeurs) dans la colonne D à partir de D2 et les noms complets ( nouvelles valeurs) dans la colonne E commençant par E2.

En B2, entrez la fonction multi-exploitation:

=MultiReplace(A2:A10, D2, E2)

Appuyez sur Entrée et profitez des résultats :)

Comment fonctionne cette formule

L'indice pour comprendre la formule est de comprendre la récursivité. Cela peut sembler compliqué, mais le principe est assez simple. À chaque itération, une fonction récursive résout une petite instance d'un problème plus grand. Dans notre cas, la fonction multiple boucle à travers les anciennes et nouvelles valeurs et, à chaque boucle, effectue un remplacement:

Multi-exploitation (SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))

Comme pour les fonctions de substitut imbriquées, le résultat du substitut précédent devient le paramètre de texte du substitut suivant. En d'autres termes, sur chaque appel ultérieur de plusieurs espaces , la fonction de substitution ne traite pas la chaîne de texte d'origine, mais la sortie de l'appel précédent.

Pour gérer tous les éléments de l' ancienne liste, nous commençons par la cellule la plus supérieure et utilisons la fonction de décalage pour déplacer 1 ligne vers le bas à chaque interaction:

OFFSET(old, 1, 0)

La même chose est faite pour la nouvelle liste:

OFFSET(new, 1, 0)

La chose cruciale est de fournir un point de sortie pour empêcher les appels récursifs de procéder à jamais. Cela se fait avec l'aide de la fonction IF - si l' ancienne cellule est vide, la fonction renvoie le texte de sa forme actuelle et sort:

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(…)))

ou

=LAMBDA(text, old, new, IF(old"", MultiReplace(…), text))

Exemple 2. Remplacez plusieurs caractères dans Excel

En principe, la fonction multiple discutée dans l'exemple précédent peut également gérer les caractères individuels, à condition que chaque ancien et nouveau caractère soit entré dans une cellule séparée, exactement comme les noms abrégés et complets dans les captures d'écran ci-dessus.

Si vous préférez saisir les anciens caractères dans une cellule et les nouveaux caractères dans une autre cellule, ou les taper directement dans la formule, vous pouvez créer une autre fonction personnalisée, nommée RemplaceChars , en utilisant l'une de ces formules:

=LAMBDA(text, old_chars, new_chars, IF(old_chars"", ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1)), text))

Ou

=LAMBDA(text, old_chars, new_chars, IF(old_chars="", text, ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))))

N'oubliez pas de nommer votre nouvelle fonction Lambda dans le gestionnaire de noms comme d'habitude:

Fonction RemplaceChars "Title =" Name une fonction RemplaceChars personnalisée ">

Et votre nouvelle fonction personnalisée est prête à l'emploi:

Remplacechars (texte, old_chars, new_chars)

Où:

• Texte - les cordes originales
• Old - les personnages à rechercher
• Nouveau - les personnages à remplacer

Pour lui donner un test sur le terrain, faisons quelque chose qui est souvent effectué sur des données importées - remplacez des devis intelligents et des apostrophes intelligents par des devis droits et des apostrophes droites.

Tout d'abord, nous entrons les citations intelligentes et l'apostrophe intelligente en D2, les citations droites et l'apostrophe droite dans E2, séparant les caractères avec des espaces pour une meilleure lisibilité. (Comme nous utilisons le même délimiteur dans les deux cellules, cela n'aura aucun impact sur le résultat - Excel ne remplacera qu'un espace par un espace.)

Après cela, nous entrons dans cette formule en B2:

=ReplaceChars(A2:A4, D2, E2)

Et obtenez exactement les résultats que nous recherchions:

Il est également possible de taper les caractères directement dans la formule. Dans notre cas, n'oubliez pas de "dupliquer" les citations droites comme ceci:

=ReplaceChars(A2:A4, "“ ” '", """ "" '")

Comment fonctionne cette formule

La fonction RemplaceChars passe à travers les cordes old_chars et new_chars et fait un remplacement à la fois à partir du premier caractère à gauche. Cette partie est effectuée par la fonction de substitut:

SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars))

À chaque itération, la fonction droite dépouille un caractère de la gauche des cordes old_chars et new_chars , afin que la gauche puisse récupérer la paire de caractères suivante suivante:

ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))

Avant chaque appel récursif, la fonction IF évalue la chaîne old_chars . S'il n'est pas vide, la fonction s'appelle. Dès que le dernier personnage a été remplacé, le processus d'itération se termine, la formule le renvoie de sa forme actuelle et sort.

Note. Parce que la fonction de substitut utilisée dans nos formules de base est sensible à la casse , les lambdas ( multi-emplacements et remplacements ) traitent les lettres majuscules et les lettres minuscules comme des caractères différents.

Trouver et remplacer en masse par UDF

Dans le cas où la fonction Lambda n'est pas disponible dans votre Excel, vous pouvez écrire une fonction définie par l'utilisateur pour le multi-Replace de manière traditionnelle à l'aide de VBA.

Pour distinguer l'UDF de la fonction multiple définie par Lambda, nous allons le nommer différemment, disons Massreplace . Le code de la fonction est le suivant:

Fonction Massreplace (Inputrng comme plage, findrng comme plage, remplaçage en tant que plage) en tant que variant () dim arres () en tant que variant ' tableau pour stocker les résultats dim arsearchreplace ( ) , stmp as string ' Array où stocker les paires de fin iinputcurcol, cntinputrows, cntinputcols As long 'index de la ligne actuelle dans la plage source, index de la colonne actuelle dans la plage de source, comptage des lignes, comptage des colonnes cntinputrows = entrée . cntinputrows, 1 à cntinputcols) redim arsearchreplace (1 à cntfindrows, 1 à 2) 'préparant le tableau de paires find / remplacer par ifindcurrow = 1 à cntfindrow RemplacerNG.Cells (ifindCurrow, 1) .Value Next 'Rechercher et remplacer dans la plage source pour iInputCurrow = 1 à Cntinputrows pour iInputCurcol = 1 à CntiNputCols stmp = inputrng.cells ( iinputcurrow À cntFindrows stmp = remplacer (stmp, arsearchreplace (ifindCurrow, 1), arsearchreplace (ifindCurrow, 2)) Next arres (iinputcurrow, iinputcurcol) = stmp suivant massReplace Next = arre final fonction fin

Comme les fonctions définies par Lambda, les UDF sont à l'échelle du classeur . Cela signifie que la fonction Massreplace ne fonctionnera que dans le classeur dans lequel vous avez inséré le code. Si vous ne savez pas comment le faire correctement, veuillez suivre les étapes décrites dans la façon d'insérer le code VBA dans Excel.

Une fois le code ajouté à votre classeur, la fonction apparaîtra dans la formule Intellisense - seulement le nom de la fonction, pas les arguments! Cependant, je crois que ce n'est pas grave de se souvenir de la syntaxe:

MassReplace (Input_Range, find_range, Replace_Range)

Où:

• Input_range - La plage source où vous souhaitez remplacer les valeurs.
• Find_range - les caractères, les chaînes ou les mots à rechercher.
• Replace_Range - les caractères, les chaînes ou les mots à remplacer.

Dans Excel 365, en raison de la prise en charge des tableaux dynamiques, cela fonctionne comme une formule normale, qui doit seulement être entrée dans la cellule supérieure (B2):

=MassReplace(A2:A10, D2:D4, E2:E4)

Dans Excel pré-dynamique, cela fonctionne comme une formule de tableau CSE à l'ancienne: vous sélectionnez toute la plage source (B2: B10), tapez la formule et appuyez simultanément sur les touches Ctrl Shift Entrée pour la compléter.

Avantages : une alternative décente à une fonction Lambda personnalisée dans Excel 2019, Excel 2016 et les versions antérieures

Inconvénients : le classeur doit être enregistré en tant que fichier .xlsm macro-compatible

Remplacer en vrac dans Excel par une macro VBA

Si vous aimez l'automatisation des tâches courantes avec des macros, vous pouvez utiliser le code VBA suivant pour trouver et remplacer plusieurs valeurs dans une plage.

Sous- bulkreplace () dim rng comme plage, sourcerng comme plage, remplacer en tant que plage sur l' erreur reprendre ensuite définir sourcerNG = application.inputbox ( "data source:" , " bulk remplace" , application.selection.address, type : = 8 ) err.clar RempacerNG n'est rien alors application.screenupdating = false pour chaque RNG dans remplace.Columns (1) .Cells sourcerng.replace what: = rng.value, remplacement : = rng.offset (0, 1 ) .value Next application.screenupdati

Pour utiliser immédiatement la macro, vous pouvez télécharger notre exemple de classeur contenant le code. Ou vous pouvez insérer le code dans votre propre classeur.

Comment utiliser la macro

Avant d'exécuter la macro, tapez les anciennes et nouvelles valeurs en deux colonnes adjacentes comme indiqué dans l'image ci-dessous (C2: D4).

Et puis, sélectionnez vos données source, appuyez sur ALT F8 , choisissez la macro Bulkreplace et cliquez sur Exécuter .

Comme la rage source est présélectionnée, vérifiez simplement la référence et cliquez sur OK:

Après cela, sélectionnez la plage de remplacement et cliquez sur OK:

Fait!

Avantages : Configuration une fois, réutilisez à tout moment

Inconvénients : la macro doit être exécutée à chaque changement de données

Trouver plusieurs et remplacer en Excel par l'outil de substring

Dans le tout premier exemple, j'ai mentionné que le substitut imbriqué est le moyen le plus simple de remplacer plusieurs valeurs dans Excel. J'avoue que j'avais tort. Notre suite ultime rend les choses encore plus faciles!

Pour effectuer une masse dans votre feuille de calcul, rendez-vous vers l'onglet Données AbleBits et cliquez sur Outils de sous-chaîne > Remplacez les sous-chaînes .

La boîte de dialogue Remplacer les sous-chaînes semblera vous demander de définir la plage de la plage de source et la plage de sous-chaînes .

Avec les deux plages sélectionnées, cliquez sur le bouton Remplacer et recherchez les résultats dans une nouvelle colonne insérée à droite des données d'origine. Oui, c'est aussi facile!

Conseil. Avant de cliquer sur Remplacer , il y a une chose importante à considérer - la case sensible à la cas . Assurez-vous de le sélectionner si vous souhaitez gérer les lettres majuscules et minuscules en différents caractères. Dans cet exemple, nous cochons cette option car nous voulons seulement remplacer les chaînes capitalisées et laisser les sous-chaînes comme "FR", "UK" ou "AK" dans d'autres mots intacts.

Si vous êtes curieux de savoir quelles autres opérations en vrac peuvent être effectuées sur les chaînes, consultez d'autres outils de sous-chaîne inclus dans notre suite ultime. Ou encore mieux, téléchargez la version d'évaluation ci-dessous et essayez-le!

C'est comment trouver et remplacer plusieurs mots et personnages à la fois dans Excel. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Téléchargements disponibles

Multiple trouver et remplacer dans Excel (fichier .xlsm) Version entièrement fonctionnelle de la suite ultime (fichier .exe)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!