Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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.

Excel formation - 0014-remplacement multiple - 01

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 :

Excel formation - 0014-remplacement multiple - 02

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.

Excel formation - 0014-remplacement multiple - 03

Nous voulons rendre la description plus accessible pour un public moins technique.

Voici un tableau des termes que nous allons remplacer :

Excel formation - 0014-remplacement multiple - 04

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.

Excel formation - 0014-remplacement multiple - 05

La formule va effectuer les remplacements demandés dans le texte, transformant la description d'origine en une version plus accessible.

 

 



Articles qui pourraient vous intéresser

Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !
Les erreurs de concaténation qui vous font perdre du temps sur Excel
Découvrez la nouvelle fonction Excel que 90% des experts ne connaissent pas !
Boostez vos investissements : maîtrisez le calcul du taux de rentabilité avec Excel en 10 minutes !
Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.