Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !
Dans ce tutoriel, je vais vous montrer comment remplacer plusieurs mots dans vos données Excel en un seul coup en utilisant une seule formule.
Cela sera particulièrement utile si vous avez une liste de tâches ou de produits à mettre à jour rapidement, sans avoir à entrer manuellement chaque changement.
Nous allons tout explorer pas à pas, avec des exemples concrets et des astuces pour rendre ce processus plus rapide et efficace.
À la fin de ce tutoriel, vous saurez comment manipuler plusieurs remplacements en une seule formule, et vous aurez découvert de nouvelles façons d’optimiser notre travail sur Excel.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons utiliser le tableau présentant les différentes tâches pour un projet.
Comme nous pouvons le constater, ce tableau contient deux colonnes : une pour la description de la tâche et une pour le type de tâche correspondant.
Le but sera donc ici de remplacer certains types de tâches par de nouveaux termes, en utilisant une formule Excel, afin d’harmoniser le tout.
Nous devons donc remplacer les types de tâches avec les termes suivants :
Si nous devions faire ces modifications manuellement, cela pourrait être long et fastidieux, surtout si le tableau contient de nombreuses lignes. Nous allons donc automatiser ce processus avec les fonctions REDUCE et LAMBDA, ce qui nous permettra de remplacer tous ces termes d’un seul coup.
2. Utiliser les fonctions LAMBDA et REDUCE pour remplacer plusieurs mots
Comme nous venons de le voir, nous allons utiliser deux fonctions récentes et très puissantes d’Excel.
2.1. LA FONCTION REDUCE
Tout d’abord, découvrons la fonction REDUCE d'Excel.
Cette fonction est une nouvelle fonction introduite dans Excel 365 et Excel 2021, elle est encore en version Beta, ce qui explique que celle-ci n’a pas été traduite en français.
Elle permet d'effectuer des opérations répétées (ou cumulatives) sur une liste de valeurs.
Pour cela, commence par une valeur initiale, puis applique une fonction à chaque élément d'un tableau jusqu'à ce que toutes les valeurs aient été traitées.
Elle est donc particulièrement utile lorsqu'il s'agit de traiter plusieurs éléments à la fois dans un tableau, comme dans notre cas, où nous souhaitons remplacer plusieurs mots dans un texte.
REDUCE permet de simplifier des opérations complexes sur des données en les appliquant de façon itérative.
Combinée à la fonction LAMBDA sur laquelle nous reviendrons juste après, elle devient encore plus puissante, car nous pouvons personnaliser les opérations à réaliser à chaque itération.
L’avantage de cette fonction est qu’elle nous permet d’éviter d’écrire plusieurs lignes de formules SUBSTITUE, ce qui rend notre fichier Excel plus clair et plus rapide à traiter.
La structure de la fonction REDUCE dans Excel est la suivante :
=REDUCE(Valeur_initiale; Tableau; Fonction)
Où :
- Valeur_initiale : c'est la valeur à partir de laquelle nous souhaitons commencer l'opération (dans notre cas, la tâche ou le texte à modifier).
- Tableau : il s'agit de la série de valeurs sur laquelle nous souhaitons appliquer notre transformation (par exemple, les anciens termes que nous souhaitons remplacer).
- Fonction permet de définir l’opération à effectuer pour chaque itération.
Et c’est justement dans ce troisième et dernier argument « fonction » que nous allons utiliser la seconde fonction de ce tutoriel : la fonction LAMBDA.
2.2. LA FONCTION LAMBDA
La fonction LAMBDA d'Excel permet de créer des mini-fonctions personnalisées directement dans une cellule.
Contrairement aux macros, qui nécessitent un peu de programmation, LAMBDA peut être utilisée directement dans une formule et permet d'exécuter des calculs spécifiques à vos besoins.
Voici comment s’utilise la fonction LAMBDA dans Excel :
=LAMBDA(param1; param2; Calcul)
Où :
- param1, param2 : ce sont les paramètres de la fonction, que nous pouvons nommer comme bon vous semble. Dans notre exemple, nous utilisons a et b pour représenter la tâche actuelle et l'ancien terme respectivement.
- Calcul : c’est l’opération que nous souhaitons réaliser avec les paramètres fournis. Par exemple, ici nous utilisons SUBSTITUE pour remplacer un terme par un autre.
3. Mise en place du remplacement multiple
Comme nous venons de le voir, dans notre exemple, la fonction LAMBDA est utilisée pour gérer le processus de remplacement.
À chaque itération de la fonction REDUCE, LAMBDA prend deux valeurs (la tâche actuelle et un ancien terme) et applique la fonction SUBSTITUE pour effectuer le remplacement.
=REDUCE(B4;$A$13:$A$17;LAMBDA(a;b;SUBSTITUE(a;b;INDEX($B$13:$B$17;EQUIV(b; $A$13:$A$17;0)))))
Dans cette formule :
- Valeur initiale : «B4», la valeur à transformer (ici, « Financier").
- Tableau : «$A$13:$A$17», la liste des anciens termes que nous voulons remplacer.
- LAMBDA : La fonction qui définit ce qu'il faut faire pour chaque élément du tableau (ici, appliquer un remplacement via SUBSTITUE).
La fonction LAMBDA permet de définir une fonction personnalisée directement dans Excel, sans avoir besoin de créer une macro.
Ici, LAMBDA prend deux paramètres, a et b :
- a : représente la valeur actuelle en cours de modification. Il commence par la valeur initiale (dans ce cas, «B4», qui contient « Financier").
- b : représente chaque élément de la liste (ou tableau) passé à REDUCE, soit les termes dans «$A$13:$A$17» (la liste des anciens termes à remplacer).
Ensuite, nous utilisons la fonction SUBSTITUE pour remplacer un texte dans une cellule par un autre.
Ici, SUBSTITUE(a; b; …) signifie que l'on va remplacer dans a (la tâche actuelle) la valeur b (un ancien terme trouvé dans la liste des anciens termes) par une nouvelle valeur déterminée par les fonctions INDEX et EQUIV qui permettent d’effectuer une recherche dans une base.
Ici, la fonction INDEX permet de récupérer une valeur dans un tableau, ici dans la plage «$B$13:$B$17», où se trouvent les nouveaux termes à utiliser pour remplacer les anciens.
Et la fonction EQUIV permet de trouver la position d'une valeur (ici b) dans la plage «$A$13:$A$17», c'est-à-dire la position de l'ancien terme que nous sommes en train de remplacer.
- EQUIV(b; $A$13:$A$17; 0) : recherche la position du terme b (ancien terme) dans la plage des anciens termes «$A$13:$A$17».
- INDEX($B$13:$B$17; …) : renvoie le terme correspondant dans la plage des nouveaux termes.
Pour résumer, le fonctionnement de la formule en cellule C4 :
- B4 contient « Financier ».
- Le premier élément de la plage «$A$13:$A$17» est « Financier ».
- SUBSTITUE remplace « Financier » par « Comptabilité » (terme correspondant dans «$B$13:$B$17»).
- Le résultat dans la cellule C4 est donc « Comptabilité », et la formule continue pour vérifier si d'autres remplacements sont nécessaires.
Cette approche est puissante, car elle permet d'effectuer tous les remplacements dans une seule formule, sans avoir à utiliser plusieurs appels à SUBSTITUE.
4. Description de produit
L’exemple que nous venons de voir n’est pas forcément représentatif de la puissance de la formule que nous venons de voir.
En effet, ici il serait plus rapide de n’utiliser que les fonctions INDEX et EQUIV pour atteindre le même résultat…
Mais maintenant supposons que nous disposions d’un long texte à modifier en fonction d’une table de termes à remplacer.
Ainsi, nous travaillons avec une description de produit et que nous voulons remplacer plusieurs termes techniques par d'autres termes plus accessibles ou marketing-friendly.
Nous voulons rendre la description plus accessible pour un public moins technique.
Voici un tableau des termes que nous allons remplacer :
Comme nous l’avons vu dans le premier cas, nous allons utiliser la fonction REDUCE associée à LAMBDA pour effectuer tous ces remplacements en une seule formule.
Voici comment nous allons procéder :
1. La cellule B4 contient le texte original.
2. Le tableau des anciens termes est dans les cellules A13:A21.
3. Le tableau des nouveaux termes est dans les cellules B13:B21.
La formule que nous allons utiliser dans la cellule C4 est la suivante :
=REDUCE(B4;$A$13:$A$21;LAMBDA(a;b;SUBSTITUE(a;b;INDEX($B$13:$B$21;EQUIV(b;$A$13:$A$21;0)))))
Où :
- B4 : C’est le texte initial (la description du téléphone) que nous souhaitons modifier.
- $A$13:$A$21 : Il s’agit de la plage des anciens termes (les termes techniques que nous voulons remplacer).
- LAMBDA(a; b; …) : Cette partie de la formule définit comment la transformation sera appliquée. a est la valeur courante (le texte modifié à chaque étape), et b est l’ancien terme à remplacer.
- SUBSTITUE(a; b; …) : Cette fonction remplace dans a (le texte) la valeur b (ancien terme) par le nouveau terme correspondant.
- INDEX($B$13:$B$21; EQUIV(b; $A$13:$A$21; 0)) : Cette partie trouve le terme de remplacement dans la colonne B en fonction de l’ancien terme trouvé dans la colonne A.
La formule va effectuer les remplacements demandés dans le texte, transformant la description d'origine en une version plus accessible.