Remplacements multiples avec Excel
La fonction SUBSTITUE ne permet que de remplacer une seule valeur par une autre valeur.
Voici comment vous pouvez effectuer de multiples remplacements avec une seule formule et, un peu plus bas, comment créer une fonction personnalisée pour pouvoir ensuite le faire encore plus facilement :
=SUBSTITUE_TABLEAU(texte;tableau_remplacements)
Exemple d'utilisation
L'objectif ici est de retourner le texte de la cellule A2 après avoir remplacé toutes les valeurs (définies dans le tableau D2:E8) :
La formule suivante permet d'obtenir ce résultat :
=REDUCE(A2;SEQUENCE(NBVAL(D2:D8));LAMBDA(t;n;SUBSTITUE(t;INDEX(D2:D8;n);INDEX(E2:E8;n))))
La formule a bien effectué tous les remplacements.
Pour mieux comprendre cette formule, en voici tout d'abord une version un peu plus lisible :
=REDUCE(A2;SEQUENCE(NBVAL(D2:D8));LAMBDA(texte;numero;SUBSTITUE(texte;INDEX(D2:D8;numero);INDEX(E2:E8;numero))))Décomposons maintenant cette formule.
La formule débute par la fonction REDUCE avec comme valeur initiale le texte de la cellule A2 :
=REDUCE(A2;La fonction REDUCE va ensuite parcourir le tableau généré par la fonction SEQUENCE (qui est un simple tableau numéroté de 1 à 7, car le tableau D2:D8 contient 7 valeurs) et effectuer une action sur le texte en fonction de celui-ci :
SEQUENCE(NBVAL(D2:D8));La fonction REDUCE va donc exécuter 7 fois la fonction LAMBDA dont les arguments sont : le texte à modifier et le numéro de remplacement (de 1 à 7) :
LAMBDA(texte;numero;Et pour finir la fonction SUBSTITUE va effectuer le remplacement de la ligne en cours :
SUBSTITUE(texte;INDEX(D2:D8;numero);INDEX(E2:E8;numero))))Les valeurs utiles aux remplacements sont récupérées par la fonction INDEX dans le tableau en fonction du numéro en cours.
Fonction personnalisée
Pour simplifier son utilisation, vous pouvez créer une fonction personnalisée pour pouvoir ensuite l'utiliser très facilement :
=SUBSTITUE_TABLEAU(A2;D2:E8)
Dans ce cas, créez une nouvelle fonction personnalisée, entrez SUBSTITUE_TABLEAU dans le premier champ, puis la formule suivante dans le dernier champ :
=LAMBDA(texte;tableau_remplacements;REDUCE(texte;SEQUENCE(LIGNES(tableau_remplacements));LAMBDA(t;n;SUBSTITUE(t;INDEX(tableau_remplacements;n;1);INDEX(tableau_remplacements;n;2)))))