Archives de catégorie : astuce excel

Comment combiner des colonnes sur excel, pour faire des listes d’emails avec CONCATENER ou « & »

La formule concatener : formule pour associer des données dans une seule cellule.

La formule CONCATENER sur Excel est utilisée pour combiner des chaînes de texte de différentes cellules en une seule chaîne de texte. La formule CONCATENER prend plusieurs arguments, qui sont les chaînes de texte à combiner. Par exemple, si vous avez des noms et des prénoms stockés dans des colonnes séparées, vous pouvez utiliser la formule CONCATENER pour les combiner en une seule chaîne de texte, en séparant les noms et les prénoms par une virgule ou un espace.

L’utilisation de CONCATENER pour créer des listes d’email sur un fichier excel.

Pour combiner des colonnes sur Excel afin de créer une liste d’emails, vous pouvez donc utiliser la fonction CONCATENER ou l’opérateur « & ». Voici les étapes à suivre :

  • En premier lieu, ouvrez la feuille de calcul Excel contenant les colonnes que vous souhaitez combiner. Il faut que ces colonnes soient complètes : pour l’application d’une formule d’emails par exemple. Il faut donc le nom, le prénom et le site internet utilisé. Si vous avez une formulation différente, utilisant par exemple la première lettre d’un prénom. Mais il faudra en amont optimiser votre colonne.
  • Créez une nouvelle colonne à côté des colonnes que vous voulez combiner. En plus de celles qui existent déjà.
  • Dans la cellule de la nouvelle colonne, utilisez la fonction CONCATENER ou l’opérateur « & » pour combiner les valeurs de chaque colonne. Par exemple, si vous avez une colonne pour les noms et une colonne pour les adresses e-mail. ous pouvez utiliser la formule suivante dans la nouvelle colonne pour combiner les deux :

=CONCATENER (A1, »@ »,B1)

ou

=A1& »@ »&B1

  • Répétez cette formule pour chaque ligne de données en réalisant un copier coller simple, pour étendre le travail que vous avez fait dans la première cellule.
  • Copiez la nouvelle colonne de données combinées et collez-la dans un nouvel emplacement pour créer votre liste d’emails.
  • Vérifiez que toutes les données ont été correctement combinées et que votre liste d’emails est complète.

Le travail marche également sur Google sheet de la même façon. Vous pourrez si vous le souhaitez travailler sur excel dans un premier temps. Ensuite, vous pouvez sans problème enregistrer le fichier excel sur google sheet. Même si depuis deux ans, google sheet intègre la fonction nativement.

Insérer un graphique Excel dans Powerpoint avec les liaisons

Pour intégrer un graphique excel dans Powerpoint, plusieurs possibilités.

  • La première c’est la fameuse capture d’écran : En fonction de si vous êtes sur mac ou sur pc, les outils ne sont pas les mêmes.

Capture d’écran sur Mac : Association des touches « Command » + « Maj » + « 3 » ou « 4 ».  Avec la touche 3, vous capturez l’ensemble de votre écran. Avec la touche 4, c’est avec le souris que vous déterminerez la zone qui vous intéresse.

Capture d’écran sur Pc : Association des touches « Windows » + touche « écran » / et touche « fn » + « barre espace » si vous n’avez pas le bon clavier (touche impr absente de votre clavier).

C’est une solution suffisante dans la plupart des cas. Mais pensée bien à vérifier la qualité de l’image.

En revanche, si les données associées à ce graphique sont amenées à changer, il faut alors que l’intégration dépendent de la feuille de calcul dans laquelle vous avez produit votre graphique excel.

  • La deuxième, c’est donc d’intégrer un graphique copié sur excel et collé sur Powerpoint

Voici ci-dessus, la donnée brute, et en dessous le tableau croisé dynamique

Peu importe la qualité de la donnée, notre exemple est là pour voir la manipulation à faire.

La copie de ce tableau est donc faite à partir d’un tableau excel.

Pour que les données changent en fonction de ce que vous avez posé, il faut que le tableau excel soit ouvert en même temps que votre fichier powerpoint.

Pour plus de facilité et pour éviter les ruptures de liens, l’idéal est de ranger les deux fichiers dans un même dossier.
De plus, si les deux fichiers portent le même nom, ce sera beaucoup plus facile pour les retrouver.

Voilà donc comment procéder.
Cependant, pendant votre présentation, veillez à ce que les deux fichiers soient ouverts. En fonction des versions de votre Pack Office, vous pourrez avoir des ruptures de liens.

La rupture de lien peut poser problème. Pour vérifier que ce n’est pas le cas, une fois les fichiers renommés, vous pouvez :

  • Cliquez droit sur le tableau copier (sur Powerpoint) puis dans le menu contextuel,
  • Aller sur « objet feuille de calcul » > « Ouvrir »

Si votre fichier est mal lié (qu’il a changé d’emplacement ou qu’il a changé de nom), vous aurez une erreur. Si tout va bien, il ouvrira le fichier source avec le tableau ou le graphique copié dans excel.

Voilà pour l’astuce !

Attention, cette procédure fonctionne sur mac. Mais c’est plus simple sur pc : vous irez dans les documents associés et vous retrouvez l’adresse de lien de votre fichier source excel, dont voici l’article.

Cacher une colonne ou une ligne sur Excel

Certaines fois, lorsqu’on reçoit un fichier excel, on peut se rendre compte que certaines lignes ou certaines colonnes sont manquantes. En réalité, la plupart du temps elles sont cachées, elles sont masquées. C’est ce que nous allons voir, comment masquer et démasquer des colonnes sur Excel. Si l’autre possibilité peut être de figer une colonne ou une ligne sur excel, on utilise très souvent le masquage pour avoir une vue plus synthétique.

Comment masquer une colonne sur Excel?

C’est une opération très pratique lorsque les tableaux présentent trop de données. Sans pour autant créer un nouveau tableau ou supprimer définitivement des données, c’est une façon de les mettre de côté. Cette mise de côté est très utiliser. Mais le problème réside dans les fichiers partagés. (typique pour les utilisateurs de la suite Microsoft 365)
En effet, celui qui masque une colonne ou une ligne oublie souvent de la reposer. Ce qui créé des mauvaises surprises pour celui qui ne sait pas masquer ou démasquer une colonne. Voilà donc pourquoi il faut comprendre comment ça marche !

Pour masquer une colonne sur excel, il faut d’abord sélectionner la colonne en question. Dans l’exemple ci-dessous, nous avons sélectionner la colonne D.

Ensuite, il faut faire un clic droit sur le haut de la colonne, au niveau de la sélection. Le menu contextuel apparaît, ce qui vous permet de voir en bas : « Masquer ».

Il ne reste plus qu’à cliquer pour valider votre choix et la colonne disparaîtra. Du moins, elle va être masquée.

En effet, en regardant de plus prêt on voit que la colonne « D » n’est plus visible, la colonne de texte dans lequel nous avions posé le mot « test » a disparu. Mais le fait de voir que les noms de colonne passent directement de C à E, montre que la colonne est juste mis de côté.

Cacher une ligne sur excel : procédure identique que pour une colonne.

En effet, rien de compliqué. Pour cacher une ligne sur excel, c’est la même procédure. C’est quelque chose qu’on utilise moins puisque la plupart des tableaux excel sont créés avec les catégories en colonnes.

Par conséquent, comme pour la colonne, il faut sélectionner la ligne en premier. Ensuite, faire un clic droit à la base de la ligne. Sélectionner « Masquer ». Et cliquer dessus pour faire disparaître provisoirement la ligne.

Faire réapparaître une colonne ou une ligne Excel après qu’elle ait été masquée.

A nouveau, rien de compliqué ! Et c’est quelque chose que vous pouvez appliquer à tous les tableurs. Vous pourrez même utiliser cette technique sur l’intégration de tableau dans un CMS ou un éditeur de texte, ce qui est bien pratique.

Pour retrouver ce que vous avez caché au préalable, il faut faire la même procédure. La différence sera qu’il faut placer votre souris entre les deux colonnes. Votre souris va alors se transformer en « double flèche ».

Ensuite il faut cliquer sur le clic droit de votre souris. Le menu contextuel apparaît à nouveau. Vous choisirez « afficher » et alors, la colonne va à nouveau apparaître.

C’est la même procédure pour la ligne. Pour la faire réapparaître, il suffira juste de placer la souris en bon endroit et de cliquer sur afficher dans le menu contextuel.

Regrouper des dates dans un tableau croisé dynamique

Si la base de données que vous utilisez comprend des dates, il peut s’avérer utile de les regrouper.

Excel nous donne cette possibilité en les groupant par différentes unités de temps comme, par un certain nombre de jours déterminé, par mois, par trimestre ou encore par année.

Prenons pour exemple le tableau croisé dynamique suivant :

Image1 - Date TCD

Nous voyons que mes données d’étiquettes de colonnes sont des dates journalières mais pour une meilleure analyse de ces données, je souhaite regrouper mes dates mensuellement, par exemple.

Pour cela, je vais me positionner dans la cellule de ma première date et faire un clic droit avec ma souris.

Image2 - Date TCD

 

Je vois alors la fonction « Grouper » et en cliquant dessus, une nouvelle fenêtre apparaît :

Image3 - Date TCD

Par défaut, Excel m’a sélectionné « Mois », ce qui me convient parfaitement pour mon exemple. Je n’ai donc plus qu’à cliquer sur Ok.

Image4 - Date TCD

Mais nous avons pu voir aussi dans cette fenêtre que nous pouvions aussi regrouper par Secondes, Minutes, Heures, Jours (avec la possibilité d’indiquer un Nombre de jours si on veut travailler par quinzaine par exemple), Trimestres et Années.

Pour sélectionner une autre unité, il me suffit de cliquer sur « Mois » pour désélectionner cette unité et de cliquer sur l’unité choisie.

Nous pouvons également travailler avec plusieurs unités comme par exemple en Trimestre ET en Mois. Pour cela, je sélectionne simplement ces 2 unités en cliquant dessus.

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

 

 

 

Nommer une feuille ou un classeur sur Excel

Nommer une feuille et faire appel à une feuille différente sur excel.

Nommer une feuille sur Excel n’a rien à voir avec renommer la feuille sur laquelle on travail.

nommer-feuille-excelPour donner un nom sur la feuille du classeur sur laquelle on travail, il suffit de faire un clic droit sur l’image et de cliquer sur renommer.

Mais renommer la feuille ne vous permettra pas d’aller la chercher, pour cela il faut au delà de son nom, expliquer à excel ce que c’est.

Un feuille se nomme de cette façon : « nomfeuille!« . C’est le point d’exclamation à la fin du nom de la feuille qui indique qu’il s’agit bien d’une feuille.

Pour un classeur, c’est pareil, il faudra le nommer à la façon excel : En l’occurrence, avec des crochets de cette façon [classeur1].

Ces éléments inquiètent souvent à tort ceux qui souhaitent travailler avec Excel. C’est en tout cas le seul moyen d’aller chercher des autres tableaux, des autres feuilles ou des autres classeurs.

ecriture-feuilleEn revanche, à défaut de l’écrire, en allant chercher votre cellule directement dans la feuille citée, vous aurez l’écriture du chemin qui se fera tout seul, comme par exemple :

Actualiser son tableau croisé dynamique sous Excel 2007

Si la base source de votre Tableau Croisé Dynamique se trouve dans le même classeur Excel, il vous est sans doute arrivé d’ajouter des données à votre base source et de ne pas réussir à actualiser votre tableau croisé dynamique.

Nous allons voir pourquoi et comment y remédier.

Pour exemple, nous allons imaginer qu’occasionnellement, nous vendons des fleurs sur un marché et que nous faisons un suivi de nos ventes, comme le présente le tableau ci-après :

Image1

Les données telles quelles sont difficilement exploitable et nous avons donc généré un Tableau Croisé Dynamique :

Image2

Pour rappel :

Pour générer le Tableau Croisé Dynamique, vous devez vous positionner dans une cellule à l’intérieur de votre tableau source puis dans l’onglet Accueil, vous trouverez la fonction Tableau Croisé Dynamique.

Image3

Maintenant, essayons d’ajouter une ligne à notre base source :

Le 30 mai 2014, j’ai décidé d’ajouter des Mimosas à mes ventes et j’en ai vendu 112.

Image4

Je retourne sur l’onglet de mon Tableau Croisé Dynamique et aucun changement ; Mon tableau est toujours identique et mes Mimosas n’apparaissent nulle part.

Image5

Dès l’instant que je clique à l’intérieur de mon Tableau Croisé Dynamique, nous pouvons voir que l’outil Tableau Croisé Dynamique est apparu et que nous avons accès à deux nouveaux onglets : Option et Création

Nous allons donc essayer en passant par l’onglet Option où nous avons la fonction Actualiser

Image6

Je clique sur Actualiser et … rien ne se passe ! Mes Mimosas n’apparaissent toujours pas.

Image7

Pourquoi ?

Rappelez vous, quand vous générez votre Tableau Croisé Dynamique, Excel vous défini votre base source qui comprend vos données initiales, à savoir dans notre exemple, de la cellule A1 à la cellule C47.

Image8

Par conséquent, en ajoutant une ligne en dessous de notre base source définie initialement, Excel ne peux pas la prendre en compte.

Deux solutions dans ce cas là :

1- Soit vous ajouter votre ligne supplémentaire à l’intérieur de votre tableau. Peu importe où mais la ligne ajoutée doit impérativement être inséré entre la première et la dernière ligne de notre base source initiale.

Par exemple, j’insère ma ligne « Mimosas » en date du 30/05/2014 entre les Roses et les Orchidées, comme ci-après :

Image9

Maintenant, si je retourne dans la feuille de mon Tableau Croisé Dynamique et que je clique  sur la fonction Actualiser qui se trouve dans l’onglet Option, je vois bien mon Tableau à jour avec les Mimosas qui ont été ajoutés.

Image10

2- Si par contre, votre suivi est comme dans notre exemple, c’est-à-dire afficher par date et que vous ne souhaitez pas à avoir à chaque fois à re-trier vos données parce que vous avez dû insérer vos lignes à l’intérieur de votre tableau initial, nous allons donc devoir cette fois-ci redéfinir notre base source.

Imaginons que le 03/06/2014, nous faisons un nouveau marché et que nous ajoutons donc ces nouvelles données à la suite des précédentes.

Image11

Si nous essayons par la fonction Actualiser, nos nouvelles données étant en dessous de la base source initialement définie, celles-ci ne seront pas prises en compte.

Nous allons donc devoir changer notre base source et la redéfinir.

Pour cela, nous allons simplement dans la feuille où se trouve notre Tableau Croisé Dynamique, utiliser la fonction Changer la source de données qui se trouve également dans l’onglet Option de l’outil Tableau Croisé Dynamique.

(Attention, à bien toujours vous positionner à l’intérieur de votre tableau pour qu’apparaissent ces outils).

Image12

Une fois que vous avez cliquez sur la fonction Changer la source de données, vous êtes automatiquement rebasculer dans la feuille contenant votre base source et la fenêtre Modifier la source de données du tableau croisé dynamique s’affiche alors.

Image13

Il ne vous reste donc plus qu’à redéfinir votre zone en sélectionnant les données ajoutées et à cliquer sur Ok.

Image14

Votre tableau croisé dynamique est alors mis à jour et toutes les nouvelles données prises en compte.