Préparer une base de contacts pour un publipostage sur Excel
Un exercice corrigé pour nettoyer une base de contacts avec NOMPROPRE et bâtir les champs de fusion d'un publipostage, avec le fichier Excel à télécharger.
Une base de contacts récupérée à droite à gauche, c'est rarement propre : des noms en majuscules, des prénoms en minuscules, bref un joyeux mélange. Si tu lances ton publipostage tel quel, tes courriers s'ouvriront sur un « Bonjour jean DUPONT » qui fait tout sauf sérieux. Dans cet exercice, on va remettre cette base au carré ensemble, ligne par ligne, avant de la confier à Word !
L'objectif est d'apprendre à normaliser un texte saisi n'importe comment, puis à assembler proprement les morceaux dont la fusion a besoin. Une fois ce réflexe acquis, tu prépares n'importe quelle liste de destinataires en quelques minutes, et tu réutilises la même logique dès que tu dois fabriquer des identifiants, des adresses ou des libellés à partir de colonnes existantes.
Ce que tu vas construire
- Harmoniser une colonne de noms à la casse incohérente avec NOMPROPRE.
- Construire une formule d'appel personnalisée à partir de la civilité et du nom.
- Assembler plusieurs colonnes en un seul champ avec l'opérateur & ou CONCATENER.
- Fabriquer un identifiant ou une adresse en minuscules avec MINUSCULE.
- Préparer une feuille source sans formules ni lignes parasites, à choisir comme source de fusion dans Word.
À connaître avant de commencer
- Savoir saisir une formule et la recopier le long d'une colonne.
- Connaître l'opérateur & (esperluette) au moins de nom : on l'utilise pas à pas pour coller du texte.
- Savoir qu'une fusion Word a besoin d'une colonne par champ à insérer.
Voici les données de départ de cet exercice. Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Nom | Prénom | Civilité | Société |
| 2 | DUPONT | jean | Monsieur | Atelier Lumière |
| 3 | leroy | MARIE | Madame | Studio Hadrien |
| 4 | MARTIN-DUBOIS | claire | Madame | Cabinet Verlaine |
| 5 | bernard | PAUL | Monsieur | Imprimerie Soleil |
| 6 | PETIT | sophie | Madame | Agence Méridien |
| 7 | moreau | lucas | Monsieur | Garage Central |
| 8 | GARCIA | thomas | Monsieur | Boulangerie des Halles |
| 9 | rOUSSEL | camille | Madame | Pharmacie du Parc |
| 10 | Fournier | NICOLAS | Monsieur | Menuiserie Fournier |
| 11 | BLANC-GARNIER | JEAN-MARC | Monsieur | Transports Horizon |
| 12 | chevalier | laura | Madame | Fleurs et Saisons |
| 13 | ROUX | julie | Madame | Librairie des Quais |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
NOMPROPRE n'est pas parfait sur tous les noms
NOMPROPRE met une majuscule après tout caractère autre qu'une lettre, comme une espace, un tiret ou une apostrophe. Parfait pour « jean-pierre » qui devient « Jean-Pierre », mais une particule comme « de la fontaine » devient « De La Fontaine », et « l'hermite » devient « L'Hermite ». Pour ces cas, corrige la cellule à la main après coup : la formule fait 95 % du travail, tu finis le reste.
Range les champs calculés à part
Garde tes colonnes brutes (Nom, Prénom saisis) et place les colonnes calculées (Nom corrigé, Formule d'appel, Identifiant) juste à côté. Quand tu connectes la base à Word, tu choisis simplement les colonnes propres comme champs de fusion et tu laisses les brutes de côté.
Fige la base avant la fusion
Word récupère bien les résultats des formules, figer n'est donc pas obligatoire. La feuille Contacts collée en valeurs a pourtant deux atouts. Elle ne dépend plus des colonnes brutes, alors que sur la feuille de travail, supprimer la colonne Nom ferait tomber le nom corrigé, la formule d'appel et l'identifiant en #REF!. Et elle ne contient que tes destinataires, sans les lignes vides ni les notes que Word lirait sur la feuille de travail. Cette feuille sert ensuite à envoyer un message par ligne, mais aucune pièce jointe, alors que la page pour faire un publipostage Outlook depuis une liste Excel donne une macro qui joint à chaque destinataire son propre fichier.
S'entraîner sur 3 exercices similaires à préparer une base de contacts pour un publipostage
Suivi des notes de frais
Mets en place un suivi des notes de frais sur Excel, avec le total par catégorie, le contrôle du plafond des repas et le total remboursable d'un relevé de dépenses.
Voir l'exercice
Planning d'équipe dynamique
Construire un planning d'équipe visuel et dynamique qui s'adapte automatiquement aux congés, aux jours fériés et aux rotations.
Voir l'exercice
Suivi des congés et absences
Mettre en place un suivi complet des congés et absences de ton équipe : soldes en temps réel, calcul d'ancienneté et alertes automatiques quand un collaborateur approche de la limite.
Voir l'exercice
Tu as peut-être encore une question
Mets une ligne par destinataire et une colonne par champ à insérer (nom, prénom, civilité, société). Nettoie la casse des noms avec NOMPROPRE, puis construis tes champs de fusion (formule d'appel, identifiant) avec l'opérateur & ou CONCATENER. Colle enfin la base en valeurs sur une feuille à part, nommée par exemple Contacts, enregistre le classeur et choisis cette feuille comme source de données dans Word.
NOMPROPRE est faite pour ça : =NOMPROPRE(A2) remet la première lettre de chaque mot en majuscule et le reste en minuscules. « DUPONT » devient « Dupont » et « leroy » devient « Leroy », sans avoir à retaper la colonne à la main.
Utilise l'opérateur & avec des guillemets pour les espaces. Avec la civilité « Monsieur » en C2 et le nom « DUPONT » en A2, ="Bonjour "&C2&" "&NOMPROPRE(A2) donne « Bonjour Monsieur Dupont ». Tu peux écrire la même chose avec CONCATENER, mais le & est plus court à taper.
Ce n'est pas indispensable, Word récupère bien les résultats des formules. Copier la base puis la coller en valeurs sur une feuille à part, nommée par exemple Contacts, donne pourtant la source la plus sûre. Elle ne dépend plus des colonnes brutes, que tu peux supprimer sans faire tomber les champs de fusion en #REF!, et elle ne contient que les destinataires, alors que sur une feuille de travail Word lit aussi les lignes vides et les notes placées sous le tableau.
Un publipostage avec un tableau Excel se lance depuis Word, dans l'onglet Publipostage, en cinq étapes, Démarrer la fusion pour choisir lettre ou étiquettes, Sélection des destinataires puis « Utiliser une liste existante » pour ouvrir ton classeur et choisir la feuille Contacts, Insérer un champ de fusion pour poser le nom, la civilité ou la formule d'appel dans le texte, Aperçu des résultats pour vérifier sur quelques destinataires, et Terminer et fusionner pour imprimer ou envoyer. Word lit la première ligne de la feuille comme noms de champs, d'où l'importance d'en-têtes propres et d'une feuille sans lignes parasites, ce que cet exercice prépare.
Les deux chiffres après la virgule se perdent dans un publipostage parce que Word lit la valeur brute d'Excel, 12,5 au lieu de 12,50, et ignore le format de la cellule. Le plus simple côté Excel est d'ajouter une colonne texte avec =TEXTE(D2;"# ##0,00")&" €", que tu insères comme champ à la place du montant. Côté Word, tu peux aussi afficher le code du champ avec Alt+F9 et ajouter le commutateur \# "# ##0,00 €" dans l'accolade, avant de masquer à nouveau les codes. Les deux méthodes donnent le même résultat, et la colonne Excel se contrôle avant la fusion.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de registre du courrier
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule