Archives de catégorie : formation excel

Créer une base de données Excel pour faire un publipostage avec Word

Préparer une base de données de nom sur Excel et s’en servir ensuite sur Word

Pour envoyer un courrier à plusieurs destinataires où seuls les coordonnées et quelques informations diffèrent, nous allons voir comment créer une base de données sous Excel et utiliser cette même base pour faire un publipostage avec Word.

Pour réaliser un publipostage, nous devons dans un premier temps créer une base Excel qui comportera toutes les informations nécessaires pour réaliser notre fusion.

Nous créerons par la suite notre courrier type sur Word en utilisant la fonction publipostage afin que Word puisse aller chercher toutes nos données dans notre base Excel.

Imaginons que nous devons envoyer un courrier à tous les parents d’une classe de 5ème pour les informer des résultats scolaires de chacun des élèves.

Nous allons donc commencer par ouvrir Excel et créer la base qui nous servira de source de données en indiquant toutes les informations dont nous aurons besoin sur notre courrier.

Attention, la première ligne de votre base doit uniquement indiquer les en-têtes de vos colonnes.

Si vous souhaitez ajouter un titre à votre tableau, utiliser la fonction En-tête dans l’onglet Insertion mais vous ne devez pas ajouter de ligne entre votre ligne comportant les titres de vos colonnes et la première ligne de votre tableau.

Image1

Notre tableau comportera ainsi toutes les coordonnées des parents, le prénom de l’élève, les notes obtenues ainsi que sa moyenne et les commentaires éventuels des professeurs.

Il ne nous reste plus qu’à saisir nos informations :

liste word pour publipostage

Nous avons terminés sur Excel et il nous suffit maintenant d’enregistrer notre fichier à l’emplacement souhaité et à le fermer.

Dans la seconde étape, nous allons travailler sur Word pour créer notre courrier type et insérer nos champs de fusion correspondant à notre fichier Excel et pouvoir ainsi effectuer notre publipostage.

Une fois saisie l’expéditeur, le lieu et la date, nous devons dans l’emplacement « Destinaire » que je vous ai indiqué par un carré rouge, devoir insérer les champs de fusion correspondants aux intitulés de nos colonnes de notre fichier Excel et pour cela, nous allons travailler avec les fonctions de l’onglet Publipostage de Word.

base de donnée excel pour publipostage

 

La première fonction Démarrer la fusion et le publipostage nous permet d’indiquer sur quel type de document nous travaillons.

Par défaut, Word nous indique Document Word normal. Pour notre exemple, s’agissant d’une simple lettre, nous pouvons soit laisser ce type de document, soit sélectionner Lettres mais ce qui est intéressant, c’est que nous voyons que nous pouvons également faire des publipostages sur des étiquettes ou des enveloppes par exemple en fonction de nos besoins.

Image4

Une fois le type de document sélectionné, nous allons lier notre document Word à notre base Excel et pour cela nous allons utiliser la fonction Sélection des destinataires et sélectionner Utiliser la liste existante étant donné que nous avons créé notre base Excel contenant les informations dont nous avons besoin.

Image5

Une nouvelle fenêtre Sélectionner la source de données s’ouvre alors et nous allons rechercher notre fichier Excel à l’emplacement où nous l’avons enregistré.

Ensuite, soit vous double-cliquez sur votre fichier soit vous le sélectionnez et cliquer sur le bouton Ouvrir qui se trouve en bas à droite de la fenêtre.

Image6

 

Une fois votre fichier ouvert, vous voyez apparaître une nouvelle fenêtre Sélectionner le tableau  vous permettant de sélectionner la feuille de votre classeur Excel qui contient les données que vous voulez utiliser pour votre publipostage. Un petit conseil, nommer vos feuilles pour vous y retrouver plus facilement sinon par défaut Excel les nommera Feuil1, Feuil2, Feuil3, etc.

Je sais que mes données se trouvent sur la feuille que j’ai nommé en amont Notes du 1er trimestre, je la sélectionne et utilise le bouton Ok pour valider.

Vérifiez toujours que la ligne qui indique « La première ligne de données contient les en-têtes de colonnes » soit bien cochée ; Elle l’est normalement par défaut mais un petit coup d’œil ne sera pas superflu car c’est une fonction important car c’est ainsi que vont être nommés nos champs de fusion.

Image7

Une fois mon fichier et ma feuille sélectionnés, nous pouvons voir que de nouvelles fonctions sont maintenant utilisables dans le bloc Champs d’écriture et d’insertion de mon onglet Publipostage, ce qui n’était pas le cas avant, ces fonctions étant grisées et inaccessibles.

Image8

La fonction qui va nous intéressée ici est Insérer un champ de fusion et en cliquant dessus, je vois que je retrouve toutes les en-têtes de mes colonnes de ma base de données Excel.

Nous nous positionnons donc là où nous souhaitons insérer nos champs de fusion et nous les indiquons un par un.

Image9

Dans notre encart où nous devons indiquer le destinataire, nous insérons donc les champs « Titre », « Nom », « Adresse », « Code_Postal » et « Ville ».

Image10

Nous pouvons maintenant continuer notre courrier et insérer au fur et à mesure nos champs de fusion là où c’est nécessaire, toujours en utilisant la fonction Insérer un champ de fusion.

Notre courrier est terminé !

Je vous conseille d’enregistrer votre lettre type avant de démarrer le publipostage. Ainsi, dans notre exemple, nous pourrons utiliser la même lettre pour envoyer les notes du second  trimestre et il ne nous restera juste à sélectionner une autre feuille de notre classeur ou un autre fichier grâce à la fonction Sélection des destinataires et à modifier notre source de données.

Image11

Pour la dernière étape, il nous reste à fusionner nos lettres et pour cela, nous allons utiliser la fonction Terminer & fusionner de l’onglet Publipostage.

Si nous cliquons sur la flèche permettant de dérouler toutes les options, nous voyons que nous avons plusieurs possibilités : Modifier des documents individuels, Imprimer les documents, Envoyer des messages électroniques.

Avant d’imprimer, nous voulons être sûr de ce que donne nos courriers, nous allons donc sélectionner Modifier des documents individuels

Image12

Une fois que vous avez cliquez sur Modifier des documents individuels, vous pouvez voir une nouvelle fenêtre apparaitre intitulée Fusion avec un nouveau document et par défaut, Fusionner les enregistrements, le choix  « Tous » est coché.

Pour notre exemple, nous allons laisser ainsi mais vous pourriez tout à fait ne fusionner que certaines lignes avec le choix « De :          A :        »

Image13

Nous validons avec la touche Ok et un nouveau fichier s’ouvre alors avec toutes nos lettres personnalisées ; Tous les champs de fusion que nous avions inséré dans notre lettre type ont été remplacés par les données de notre fichier Excel.

Image14

Copier – Coller plusieurs éléments avec le Presse papier de Excel 2010

Pour tout utilisateur régulier des logiciels du pack office, nous connaissons tous les fonctions copier – coller.

Mais nous avons aussi dans Microsoft une fonction nommée Presse-papiers qui est d’ailleurs sous-utilisée et pourtant est des plus pratiques pour copier-coller plusieurs éléments dans Excel ou tout autre logiciel de la suite office.

Dans l’onglet Accueil, groupe Presse-Papier, il vous suffit de cliquer sur la petite icône en bas à droite pour afficher le volet Presse-Papier.

Presse papier - 1

En cliquant sur cette icône, une nouvelle fenêtre s’affiche à la gauche de votre classeur.

 

Presse papier - 2

A partir d’ici, vous pouvez copier le nombre d’éléments que vous souhaitez. Ils s’afficheront au fur et à mesure dans la bibliothèque du Presse-Papier.

Je prends un exemple tout simple mais valable même pour un grand nombre de données que nous souhaitons copier et coller.

Comme vous pouvez le voir plus haut, mon premier onglet « Factures 2013 » comporte le montant total des factures par mois, et mon second d’onglet, une représentation graphique de ces données.

Presse papier - 3

Je souhaite copier la donnée du montant total de mes factures pour l’année se trouvant dans le premier onglet et mon graphique de mon second onglet dans un nouvel onglet pour regrouper les deux.

Je pourrai bien sûr aller copier ma première donnée et aller la copier dans ma Feuil3 puis aller copier mon graphique et aller le copier dans ma Feuil3 mais si nous avons beaucoup de données à des emplacements différents, cela peut vite devenir long et fastidieux.

D’où tout l’intérêt du Presse-papier.

Je me positionne dans ma première feuille, sélectionne ce que je souhaite et copie ces données (Ctrl + C en raccourci pour gagner du temps). Je vois de suite que ce que j’ai copié, c’est de suite ajouter dans le presse-papier.

Presse papier - 4

Je fais de même avec mon graphique qui se trouve dans le second onglet qui s’ajoute également dans la bibliothèque du Presse-Papier.

Presse papier - 5

Je vais maintenant dans ma Feuil3 et il ne me reste plus qu’à cliquer sur les éléments que je souhaite coller ou cliquer sur le bouton Coller tout si je veux toutes les données copiées.

Presse papier - 6

Le presse-Papier peut être très pratique pour copier des données d’Excel et aller les coller dans Word ou tout autre logiciel du pack Office.

Par exemple, j’ouvre un nouveau document Word et je retrouve mon volet Presse-papier avec mes données Excel et il ne reste plus qu’à cliquer sur Coller tout.

Presse papier - 7

 

 

 

La valeur cible avec Excel 2007

La fonction valeur cible a pour utilité de rechercher une valeur par rapport à un objectif défini: Voici comment l’utiliser.

La valeur cible sur Excel est un système de calcul qui permet de partir d’un résultat pour obtenir l’une des parties de notre calcul. Bien pratique pour des crédits ou des soldes par exemple : Voici une exemple de la vie pratique qui vous permettra de bien comprendre la notion de valeur cible.

Imaginons que nous devions changer de canapé. Nous souhaitons l’acquérir à crédit et nous nous sommes fixés au départ un budget de 1800 euros.

Nous souhaitons un remboursement sur un an en 12 mensualités et le magasin où nous souhaitons l’acheter propose un taux de remboursement de 3,5 %.

Nous avons donc toutes les informations nécessaires pour saisir nos données.

Image1

Dans un premier temps, je dois calculer le montant de mon remboursement et pour cela, je vais utiliser une fonction financière, nommée VPM qui me permettra ce calcul.

Profitons-en pour voir cette formule en détail :

Image2

Mon premier argument sera mon taux d’intérêt ramené à ma périodicité ; Dans notre exemple, 3,5%/12

Mon second argument sera le nombre total de remboursement soit dans notre exemple, 12 (ma périodicité)*1 (ma durée)

Mon troisième argument sera le prix de mon canapé.

Attention, pour obtenir un résultat positif, je dois mettre un devant ; C’est une question de somme algébrique. Ce qui donnera dans mon exemple, -1800

Ce qui me donne la formule suivante (sans oublier de séparer chacun de mes arguments par des ; )

Image3

Je valide et j’obtiens le montant de mon remboursement pour chaque période, soit mensuellement comme j’ai choisi un remboursement sur 12 mois :

Image4

Je dois donc rembourser 152,86 euros par mois pour cet achat.

Le problème est que j’avais prévu dans mon budget de ne rembourser que 120 euros par mois au maximum.

La question que je me pose donc est à quel prix dois-je acheter mon canapé pour avoir un remboursement égal ou inférieur à 120 euros par mois dans ces mêmes conditions ?

Pour cela, nous allons donc utiliser la valeur cible et pour trouver cette fonction, je vais aller dans l’onglet Données où je vais trouver l’icône Analyse de Scénarios qui se trouve dans le groupe Outils de données.

Image5

Je clique dessus pour ouvrir les différentes options et je découvre la fonction Valeur cible

Image6

Je clique sur Valeur cible et une nouvelle boite de dialogue fait son apparition

Image7

Nous avons 3 paramètres à renseigner :

  • La cellule à définir, c’est-à-dire l’objectif que je me suis défini.

Comme j’étais positionné dessus, Excel m’indique bien la cellule B7 soit le montant de mon remboursement.

Attention, cette cellule doit obligatoirement comporter une formule au sens large du terme. Elle peut contenir aussi bien une fonction d’Excel qu’une formule simple comme une valeur multipliée par une autre.

 

  • La valeur à atteindre, c’est-à-dire l’objectif que je me suis fixée. Je ne souhaite pas un remboursement supérieur à 120 euros et je vais donc entrer cette valeur.

 

  • La cellule à définir, c’est-à-direla cellule à modifier en fonction de mon objectif ; Dans mon exemple, ce sera la cellule B2 soit le prix de mon canapé.

 

Ce qui nous donne :

Image8

Je valide en cliquant sur Ok et Excel m’indique qu’il a trouvé un résultat.

Image9

Je valide à nouveau et je sais maintenant que je ne peux pas dépasser 1 413,07 euros pour l’achat de mon canapé en ayant les mêmes conditions.

Image10

Dans mon exemple, ce qui m’intéressait c’était de connaître le prix d’achat maximum de mon canapé en ayant un remboursement de 120 euros sur 1 an en 12 mensualités avec un taux d’intérêt de 3,5 % mais j’aurai pu également garder mon prix d’achat initial et avec les mêmes conditions, savoir sur quelle durée, je devrais emprunter.

Dans ce cas, la cellule à renseigner aurait été B3, soit ma durée et je peux ainsi rapidement savoir que pour un achat de 1800 euros avec un remboursement de 120 euros par mois, mon remboursement serait de 1 an et 3 mois.

Image11

Image12

La rechercheH

Dans un cours précédent, vous avez pu voir comment utiliser la fonction RechercheV pour la recherche verticale et nous allons maintenant voir l’intérêt et l’utilisation de la RechercheH pour la recherche horizontale.

Prenons pour exemple, un tableau de notes pour une classe de 14 élèves .

Tableau Source

 

Celui-ci pourrait comporter bien plus de colonnes et de lignes, ce qui rendrait la collecte d’information plus longue et ce que nous voulons c’est pouvoir facilement et rapidement trouver les renseignements pour un élève en particulier.

Pour commencer, j’ai créé en dessous de mon tableau principal, un nouveau petit tableau pour accéder rapidement aux informations souhaitées une fois que j’aurai saisi les formules adéquates.

Image2

 

Ce que l’on souhaite, c’est que dès que je saisi le nom d’un élève, je puisse voir immédiatement dans quelle classe il se trouve et quelles notes a-t-il obtenus pour chaque matière.

Dans la cellule B12 : « Elève », je ne rentre aucune formule car c’est ici que je vais saisir le nom de mon élève.

Par contre, en cellule B13 : « Classe », je vais utiliser la formule RechercheH pour par la suite obtenir l’information de manière automatique.

La RechercheH se décompose comme la recherveV en 4 parties :

La formule RechercheH

 

Comme toute formule, nous commençons par le signe = , suivi du nom de la formule, soit RechercheH.

Pour le premier élément, Excel nous indique de lui préciser la valeur cherchée, ce qui dans notre exemple, sera le nom de l’élève que nous allons saisir dans la cellule B12.

Valeur cherchée

Premier élément : La valeur cherchée

Sans oublier de séparer chaque élément de notre formule par les « », Excel nous demande comme second élément, notre tableau et que nous allons simplement lui désigner en sélectionnant toutes les cellules.

Image5

Second élément : La sélection du tableau

 

Le troisième élément (et on n’oublie pas nos « »), indiqué par Excel par l’intitulé « no_index_lig », représente le numéro de la ligne où Excel doit rechercher l’information dans notre tableau.

Dans notre exemple, dans la cellule où nous entrons la formule, nous voulons savoir la Classe de l’élève. Dans notre tableau, cette information se trouve en ligne 2, ce que nous lui indiquons en saississant simplement le chiffre 2.

Attention, il s’agit bien du numéro de la ligne en fonction de la sélection de votre tableau. Même si vous avez crée votre tableau plus bas dans votre feuille Excel, par exemple si vous l’avez commencé en ligne 5, les informations par rapport à la Classe, seront toujours en ligne 2 par rapport à la sélection de votre tableau.

Image6

 

Quatrième et dernier élément, Excel nous demande si il doit rechercher une correspondance approximative ou exacte. Comme nous souhaitons avoir une information et une valeur juste, nous allons simplement cliquer sur FAUX – Correspondance exacte.

Ci qui signifie que si nous entrons le nom d’un élève qui n’existe pas, Excel ne donnera bien sûr aucune information. Si nous avions sélectionné la correspondance approximative, Excel, nous aurai fourni les informations de l’élève le plus proche.

Image7

Faux - La correspondance exacte

 

On ferme la parenthèse pour signifier que notre formule est terminée et il ne nous reste plus qu’à cliquer sur Entrée.

Une fois validée, si vous voyez #N / A, ne vous inquiétez pas ; Excel vous signifie qu’il n’y a pas de valeur et c’est normal car nous n’avons pas renseigné le nom de l’élève à chercher dans la cellule B12.

#N / A

 

Si maintenant, je lui demande de me chercher la classe de l’élève13 par exemple, Excel me dit bien qu’il est en classe 1B.

Image10

 

La formule sera la même pour toutes les cellules pour lesquelles nous voulons les notes ; La seule chose qui changera est le numéro de la ligne où l’information doit être retrouvée.

Soit la ligne 3 pour la note de Français, la ligne 4 pour la note de Mathématiques, la ligne 5 pour la note d’Histoire-Géographie et ainsi de suite.

Une fois mes formules indiquées pour chaque cellule, je peux saisir le nom de n’importe quel élève et je vois immédiatement toutes les informations que je souhaitais.

 

Image11

Image12

Dupliquer une donnée pour l’intégrer dans toutes les feuilles d’un classeur

Si vous avez besoin d’intégrer une nouvelle colonne ou un nouvel élément dans toutes les feuilles de votre classeur, Excel vous permet de le faire automatiquement.

Pour cela, il faut aller en bas de votre première feuille > faire un clic droit > sélectionner toutes les feuilles > puis il faut faire votre action, qui peut être l’ajout d’un nombre, l’ajout d’une colonne ou la mise en place d’un calcul.feuilles-excel

Ensuite, votre “action” sera présente sur toutes vos feuilles, vous économisant ainsi un temps précieux!

Ainsi dans l’exemple de l’image, il y a des tableaux pour répertorier les différents vins, permettant ensuite de définir des coûts de transport.
Si par exemple vous avez besoin d’une colonne de caractéristique supplémentaire qui s’applique à toutes les sortes de vins, vous aurez alors ce qu’il faut pour