Lors du traitement de grands lots de données, peu de fonctions Excel sont aussi polyvalentes que la fonction SUBSTITUTE. Malheureusement, elle est souvent négligée au profit de solutions beaucoup plus compliquées à des problèmes souvent simples.
La fonction SUBSTITUTE est un moyen rapide et propre d’assainir les données, d’ajouter des détails aux rapports et de créer des formulaires de communication simples. La maîtrise de cette fonction et la compréhension de son utilisation en font un outil irremplaçable et facile à utiliser pour l’analyse de grands ensembles de données.
1. Créer des communications formelles avec la fonction SUBSTITUTE d’Excel
La fonction SUBSTITUTE d’Excel permet de remplacer rapidement et facilement des marqueurs prédéfinis sur un morceau de texte préformaté. Cela permet aux utilisateurs de créer facilement des formulaires de communication, en remplaçant les informations qui changent à chaque itération.
Vous pouvez placer un modèle dans une cellule d’une feuille de calcul, puis saisir une liste de destinataires dans une autre feuille de calcul. Ensuite, enchaînez les fonctions SUBSTITUTE sur une autre cellule pour ajouter les détails de chaque destinataire.
Notez que vous pouvez imbriquer plusieurs SUBSTITUTE les uns dans les autres :
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Template!$A$1, "{ cardlastfour }", F2), "{ billingtotal }", E2), "{ cardtype }", D2), "{ enddate }", TEXT(C2, "mmm dd, yyyy")), "{ lname }", B2), "{ fname }", A2) Si vous avez trop de champs à modifier, vous pouvez diviser la lettre entière en paragraphes et les modifier un par un. Dans la dernière cellule de la ligne, concaténé tous ces paragraphes en une seule lettre.
Les courriers électroniques générés automatiquement sont un moyen fantastique d’atteindre rapidement et facilement une liste de clients, de créer une correspondance marketing personnalisée ou de générer des lettres de motivation personnalisées.
Vous pouvez diviser les messages plus longs en paragraphes ou en phrases simples afin d’éviter les formules SUBSTITUTE trop complexes. Vous pouvez même utiliser des conditionnels pour une personnalisation supplémentaire, par exemple en changeant les listes de compétences d’une lettre de motivation en fonction du type d’emploi auquel vous postulez.
2. Utiliser la fonction SUBSTITUTE d’Excel pour générer des rapports
La fonction SUBSTITUTE est non seulement pratique pour insérer des informations de base dans des champs préformatés, mais elle permet également d’injecter des calculs complexes dans des rapports préétablis. L’injection de calculs vous permet d’utiliser Excel pour créer un tableau de bord qui fournit des informations visuelles et des observations personnalisées basées sur des données.
La combinaison de SUBSTITUTE avec des blocs de texte pré-générés et des conditionnels comme la fonction IF vous permet de créer des rapports plus détaillés.
Dans l’exemple ci-dessus, vous pouvez voir que deux paragraphes distincts sont créés à l’aide d’informations provenant d’un rapport. Une fonction conditionnelle peut ensuite déterminer lequel des deux paragraphes doit être affiché aux lecteurs du rapport.
Le premier paragraphe est créé à l’aide de la formule suivante :
=IF(A2>B2, SUBSTITUTE(Templates!$A$3, "{ growthpercent }", (ROUND(B2 / A2, 2) * 100) - 100 & "%"), SUBSTITUTE(Templates!$A$4, "{ decreasepercent }", 100 - (ROUND(A2 / B2, 2)) * 100 & "%")) L’association de plusieurs fonctions SUBSTITUTE enchaînées avec des conditionnelles peut vous aider à concevoir des tableaux de bord et des rapports plus pertinents. Les calculs peuvent même être effectués directement dans les fonctions SUBSTITUTE afin de garantir que vous ne présentez que les informations les plus pertinentes à vos lecteurs.
3. Assainissement des données Excel pour le stockage à l’aide de la fonction SUBSTITUTE d’Excel
Lorsqu’il s’agit de stocker des données à long terme, peu de considérations sont aussi cruciales que l’assainissement. Il est essentiel d’empêcher les données indésirables de polluer ou d’endommager un ensemble de données afin de prévenir les erreurs futures que vous ne pouvez pas comprendre.
Cela peut aller de l’élimination des caractères illégaux d’un ensemble de données à l’assurance que les mots-clés réservés sont traités comme du texte plutôt que comme des commandes. Peu importe ce que vous devez faire pour protéger vos bases de données contre les menaces telles que l’injection SQL et pour que vos données soient utilisables, la fonction SUBSTITUTE d’Excel peut vous aider.
Par exemple, nous pouvons préparer une liste de réponses d’utilisateurs à une enquête pour l’insérer dans une base de données SQL. Les données non validées peuvent contenir une variété de caractères illégaux qui peuvent causer des problèmes ultérieurs s’ils ne sont pas supprimés :
=SUBSTITUTE(A2:A4, "'", '\'") 
La fonction ci-dessus représente une étape simpliste mais nécessaire dans la préparation des données pour le stockage. Cette fonction examine chaque réponse de la colonne B à la recherche d’un guillemet simple. S’il est présent, le caractère est précédé d’une barre oblique inverse. L’ajout d’une barre oblique inverse permet de s’assurer que les caractères réservés n’interfèrent pas avec la requête utilisée pour insérer les données dans la base de données.
Si le caractère ou la chaîne que vous recherchez doit être complètement supprimé, la fonction SUBSTITUTE est toujours utile :
=SUBSTITUTE(B2:B20, "$", "") L’utilisation d’une chaîne vide comme troisième argument supprimera entièrement les caractères spécifiés de la cellule. Grâce à la fonction SUBSTITUTE, vous pouvez supprimer un texte spécifique afin d’éliminer complètement les caractères potentiellement dangereux de vos données.
4. Assurer un formatage correct des données avec la fonction SUBSTITUTE d’Excel
Un problème potentiel lors de la collecte de données est la possibilité de recevoir des informations mal formées. Lorsque les utilisateurs saisissent des informations dans un formulaire de réponse libre, cela peut conduire à des informations incorrectes. Les données inexactes peuvent être aussi simples qu’une faute d’orthographe courante ou aussi complexes qu’une équation au mauvais format.
La fonction SUBSTITUTE d’Excel est le moyen idéal d’éviter que des erreurs simples n’entraînent des rapports de données incorrects. Lors de l’analyse des résultats produits par une source spécifique, la suppression des inexactitudes connues peut contribuer à produire des résultats plus précis :
=SUBSTITUTE(A2, "CompanyName Inc.", "Company Name Inc.") 
L’exemple ci-dessus montre comment la fonction SUBSTITUTE peut supprimer une simple faute de frappe dans une liste d’avis sur des produits. Le remplacement d’une faute d’orthographe courante permet de s’assurer que la mention d’une marque ou d’une entreprise n’est pas oubliée lors d’un examen ultérieur des données.
L’utilisation de la fonction SUBSTITUTE pour remplacer les erreurs potentielles connues par les informations correctes permet de détecter les problèmes dans votre ensemble de données avant qu’ils ne deviennent des problèmes, ce qui vous aide à éviter les erreurs si vous souhaitez utiliser votre feuille de calcul pour l’établissement de profils de données à l’avenir.
La fonction SUBSTITUTE d’Excel : Simple et fonctionnelle
Bien que la fonction SUBSTITUTE soit relativement simple, il s’agit d’une formule extrêmement utile. Elle permet de remplacer du texte dans des blocs préformatés, de générer des rapports, d’assainir des données et de supprimer d’un ensemble de données des informations susceptibles de poser problème.
L’utilisation de calculs dans SUBSTITUTE et leur association à des formules conditionnelles permettent d’étendre ses capacités. Cela permet d’étendre les tableaux de bord et les rapports de la présentation de données visuelles à la fourniture d’analyses complexes générées automatiquement.
En combinant cette fonction avec d’autres formules et techniques, il est possible de créer un tableau de bord rivalisant avec les outils de base de données spécialisés. Il est donc impératif de connaître et de comprendre la fonction SUBSTITUTE d’Excel si vous êtes analyste de données, ingénieur ou si vous travaillez avec des ensembles de données volumineux.
