Remplacement de texte en bloc avec des formules

Supposons que vous disposiez d'une liste dans laquelle, avec plus ou moins de "simplicité", des données initiales sont écrites - par exemple, des adresses ou des noms d'entreprises :

On voit clairement que la même ville ou entreprise est présente ici dans des variantes hétéroclites, ce qui, évidemment, créera beaucoup de problèmes lors de l'utilisation de ces tables à l'avenir. Et si vous réfléchissez un peu, vous pouvez trouver de nombreux exemples de tâches similaires dans d'autres domaines.

Imaginez maintenant que de telles données tordues vous parviennent régulièrement, c'est-à-dire qu'il ne s'agit pas d'une histoire ponctuelle «réparez-la manuellement, oubliez-la», mais d'un problème sur une base régulière et dans un grand nombre de cellules.

Que faire? Ne remplacez pas manuellement le texte tordu 100500 fois par le bon via la case "Rechercher et remplacer" ou en cliquant sur Ctrl+H?

La première chose qui vient à l'esprit dans une telle situation est de faire un remplacement en masse selon un livre de référence pré-compilé d'options incorrectes et correctes correspondantes - comme ceci :

Malheureusement, avec la prévalence évidente d'une telle tâche, Microsoft Excel ne dispose pas de méthodes intégrées simples pour la résoudre. Pour commencer, voyons comment faire cela avec des formules, sans impliquer «l'artillerie lourde» sous la forme de macros dans VBA ou Power Query.

Cas 1. Remplacement complet en vrac

Commençons par un cas relativement simple - une situation où vous devez remplacer l'ancien texte tordu par un nouveau. d’étiquettes électroniques entièrement.

Disons que nous avons deux tables :

Dans le premier – les noms variés originaux des entreprises. Dans le second – un livre de référence de la correspondance. Si on retrouve dans le nom de l'entreprise du premier tableau n'importe quel mot de la colonne Trouver, alors vous devez remplacer complètement ce nom tordu par le bon - de la colonne Représentant(e) suppléant(e) deuxième table de recherche.

Pour plus de commodité:

• Les deux tables sont converties en dynamiques ("intelligentes") à l'aide d'un raccourci clavier Ctrl+T ou équipe Insertion – Tableau (Insérer — Tableau).
  
• Dans l'onglet qui s'affiche Constructeur (Conception) première table nommée Données, et la deuxième table de référence – remplacements.

Pour expliquer la logique de la formule, partons un peu de loin.

En prenant la première entreprise de la cellule A2 comme exemple et en oubliant temporairement le reste des entreprises, essayons de déterminer quelle option de la colonne Trouver s'y retrouve. Pour ce faire, sélectionnez n'importe quelle cellule vide dans la partie libre de la feuille et entrez-y la fonction TROUVER (TROUVER):

Cette fonction détermine si la sous-chaîne donnée est incluse (le premier argument est toutes les valeurs de la colonne Trouver) dans le texte source (la première société de la table de données) et doit afficher soit le nombre ordinal du caractère à partir duquel le texte a été trouvé, soit une erreur si la sous-chaîne n'a pas été trouvée.

L'astuce ici est que puisque nous avons spécifié non pas une, mais plusieurs valeurs comme premier argument, cette fonction renverra également comme résultat non pas une valeur, mais un tableau de 3 éléments. Si vous ne disposez pas de la dernière version d'Office 365 prenant en charge les tableaux dynamiques, après avoir entré cette formule et cliqué sur Entrer vous verrez ce tableau directement sur la feuille :

Si vous avez des versions précédentes d'Excel, après avoir cliqué sur Entrer nous ne verrons que la première valeur du tableau de résultats, c'est-à-dire l'erreur #VALUE ! (#ÉVALUER!).

Vous ne devriez pas avoir peur 🙂 En fait, notre formule fonctionne et vous pouvez toujours voir tout le tableau des résultats si vous sélectionnez la fonction saisie dans la barre de formule et appuyez sur la touche F9(n'oubliez pas d'appuyer sur Escpour revenir à la formule):

Le tableau de résultats qui en résulte signifie que dans le nom de l'entreprise tordu d'origine (GK Morozko OAO) de toutes les valeurs d'une colonne Trouver n'a trouvé que le deuxième (Morozko), et à partir du 4ème caractère consécutif.

Ajoutons maintenant une fonction à notre formule VOIR(CHERCHER):

Cette fonction a trois arguments :

1. Valeur souhaitée - vous pouvez utiliser n'importe quel nombre suffisamment grand (l'essentiel est qu'il dépasse la longueur de tout texte dans les données source)
   
2. Vecteur_vu – la plage ou le tableau où nous recherchons la valeur souhaitée. Voici la fonction introduite précédemment TROUVER, qui renvoie un tableau {#VALEUR !:4:#VALEUR !}
   
3. Vecteur_résultats – la plage à partir de laquelle on veut retourner la valeur si la valeur désirée se trouve dans la cellule correspondante. Voici les noms corrects de la colonne Représentant(e) suppléant(e) notre tableau de référence.
   

La caractéristique principale et non évidente ici est que la fonction VOIR s'il n'y a pas de correspondance exacte, recherche toujours la plus petite valeur (précédente) la plus proche. Par conséquent, en spécifiant n'importe quel nombre lourd (par exemple, 9999) comme valeur souhaitée, nous forcerons VOIR trouvez la cellule avec le plus petit nombre le plus proche (4) dans le tableau {#VALUE!:4:#VALUE!} et renvoyez la valeur correspondante du vecteur de résultat, c'est-à-dire le nom de société correct de la colonne Représentant(e) suppléant(e).

La deuxième nuance est que, techniquement, notre formule est une formule matricielle, car la fonction TROUVER renvoie comme résultats non pas un, mais un tableau de trois valeurs. Mais puisque la fonction VOIR prend en charge les tableaux prêts à l'emploi, nous n'avons pas à entrer cette formule comme une formule matricielle classique - en utilisant un raccourci clavier Ctrl+Shift+Entrer. Un simple suffira Entrer.

C'est tout. J'espère que vous comprenez la logique.

Il reste à transférer la formule finie dans la première cellule B2 de la colonne Fixé – et notre tâche est résolue !

Bien sûr, avec des tables ordinaires (non intelligentes), cette formule fonctionne également très bien (n'oubliez pas la clé F4 et en fixant les liens pertinents) :

Cas 2. Remplacement partiel en vrac

Ce cas est un peu plus délicat. Encore une fois, nous avons deux tables "intelligentes":

La première table avec des adresses écrites de travers qui doivent être corrigées (je l'ai appelée Data2). La deuxième table est un ouvrage de référence, selon lequel vous devez effectuer un remplacement partiel d'une sous-chaîne à l'intérieur de l'adresse (j'ai appelé cette table Remplacements2).

La différence fondamentale ici est que vous n'avez besoin de remplacer qu'un fragment des données d'origine - par exemple, la première adresse a une adresse incorrecte "St. Pétersbourg” sur la droite "St. Pétersbourg”, en laissant le reste de l'adresse (code postal, rue, maison) tel quel.

La formule finie ressemblera à ceci (pour faciliter la perception, je l'ai divisée en combien de lignes en utilisant autre+Entrer):

Le travail principal ici est effectué par la fonction de texte standard d'Excel REMPLACER (REMPLAÇANT), qui a 3 arguments :

1. Texte source - la première adresse tordue de la colonne Adresse
2. Ce que nous recherchons - ici, nous utilisons l'astuce avec la fonction VOIR (CHERCHER)de la manière précédente pour extraire la valeur de la colonne Trouver, qui est inclus en tant que fragment dans une adresse courbe.
3. Par quoi remplacer - de la même manière, nous trouvons la valeur correcte qui lui correspond dans la colonne Représentant(e) suppléant(e).

Entrez cette formule avec Ctrl+Shift+Entrer n'est pas nécessaire ici non plus, bien qu'il s'agisse en fait d'une formule matricielle.

Et on voit clairement (voir les erreurs #N/A dans l'image précédente) qu'une telle formule, malgré toute son élégance, a quelques inconvénients :

• Fonction SUBSTITUTE est sensible à la casse, donc "Spb" dans l'avant-dernière ligne n'a pas été trouvé dans la table de remplacement. Pour résoudre ce problème, vous pouvez soit utiliser la fonction ZAMÉNIT (REMPLACER), ou amener préalablement les deux tables au même registre.
• Si le texte est initialement correct ou dans celui-ci il n'y a pas de fragment à remplacer (dernière ligne), alors notre formule renvoie une erreur. Ce moment peut être neutralisé en interceptant et en remplaçant les erreurs à l'aide de la fonction IFERREUR (SIERREUR):

  

• Si le texte original contient plusieurs fragments du répertoire à la fois, alors notre formule ne remplace que la dernière (à la 8ème ligne, Ligovsky «avenue« changé en "pr-t", Mais "S-Pb" on "St. Pétersbourg” non plus, car "S-Pb” est plus haut dans le répertoire). Ce problème peut être résolu en réexécutant notre propre formule, mais déjà le long de la colonne Fixé:

  
  

Pas parfait et encombrant par endroits, mais bien mieux que le même remplacement manuel, n'est-ce pas ? 🙂

PS

Dans le prochain article, nous verrons comment implémenter une telle substitution en bloc à l'aide de macros et de Power Query.

• Fonctionnement de la fonction SUBSTITUTE pour remplacer du texte
  
• Recherche de correspondances de texte exactes à l'aide de la fonction EXACT
• Recherche et substitution sensibles à la casse (VLOOKUP sensible à la casse)