Nous arrivons aux fêtes de fin d’année et pour l’occasion, je souhaite vous offrir une petite recette de Noël comprenant quelques nouveautés Excel fort sympathiques. Evidemment, qui dit nouveauté dit dernière version. Donc pour cet article, il vous faudra la dernière version avec les dernières mises à jour !
Nous allons à travers cet exemple construire une petite carte choroplèthe à laquelle nous ajouterons un filtre basé sur l’année. Le rafraîchissement se fera avec un icône. Voici ce que cela pourra donner (bien sûr adapté aux fêtes de Noël !):
Les différentes étapes à réaliser seront les suivantes:
- Transformer les données en Tableau
- Importer les données dans PowerQuery
- Transformer les données (Ajouter Année, supprimer dates, regrouper par, adapter les types)
- Charger les données
- Ajouter un segment
- Insérer une carte
- Créer une macro pour actualiser
- Définir les couleurs
- Insérer des icônes
- Mettre à jour les couleurs
Cela peut sembler une longue liste mais chaque action est vraiment très simple. Et cette recette ne contient aucune difficulté particulière. Vous allez voir avec quelle facilité nous pouvons faire ce genre de tableau de bord !
Mais avant de commencer, il vous faudra télécharger le fichier des données:
Télécharger le fichier de données
Et sauvegardez-le en .XLSM car nous y ajouterons une macro.
1. Transformer les données en Tableau
Votre classeur a bien été sauvegardé.
- Sélectionner n’importe quelle cellule du tableau
- Accueil > Styles > Mettre sous forme de tableau > choisir Style de tableau Moyen 6
- Cocher « Mon tableau comporte des entêtes et faire OK
- Nommer le tableau: Outils de tableau > Création > Propriétés > Nom du tableau: Donnees_Pays
Vous devriez avoir ceci:
2. Importer les données dans PowerQuery
La première des nouveautés Excel 2016 est PowerQuery totalement intégré. Nous l’avons déjà utilisé à plusieurs reprises à travers de nombreux articles: Articles PowerQuery.
- Sélectionner n’importe quelle cellule du tableau
- Données > Récupérer et transformer > A partir d’un tableau
3. Transformer les données
Les données ont bien été importées et vous êtes à présent dans une nouvelle fenêtre ; la fenêtre de PowerQuery.
Nous pouvons commencer la transformation:
- Supprimer l’étape « Type modifié » (panneau sur la droite)
- Sélectionner la colonne Date
- Ajouter une colonne > Date et heure de début > Date > Année > Année
- Clic-droit sur la colonne Date puis Supprimer
- Sélectionner les colonnes Pays et Year (en maintenant la touche CTRL)
- Accueil > Transformer > Regrouper par
- Nouveau nom de colonne: « CA Annuel »
- Opération, choisir Somme
- Colonne: CA puis faire OK
- Sélectionner la colonne Pays
- Accueil > Transformer > Type de données > Texte
- Sélectionner la colonne CA Annuel et la déplacer (en maintenant la souris) à gauche de la colonne Year.
Si vous avez bien suivi chacune des étapes ci-dessus, vous devriez avoir l’écran suivant:
4. Charger les données
Cette étape est toute simple.
- Accueil > Femer > Fermer et charger dans…
- Choisir Table puis Feuille de calcul existante > $F$6:
- Sélectionner la colonne CA Annuel
- Clic-droit > Format de cellule > Onglet Nombre > Catégorie: Nombre > Nombre de décimales: 2 > cocher le séparateur de milliers > OK
- Sélectionner une cellule du tableau
- Outils de Tableau > Création > Styles de tableau > Style de tableau Moyen 6
A la fin de cette étape, vous devriez avoir l’écran suivant:
5. Ajouter un segment
Nous allons à présent ajouter un segment Year afin de pouvoir filtrer sur l’année. Il s’agit là d’une nouveauté 2013 et non 2016.
- Sélectionner une cellule du tableau
- Outils de tableau > Création > Outils > Insérer un segment
- Choisir Year puis OK
- Outils segment > Boutons > Colonnes: 2
- Redimensionner en adaptant la hauteur
- Déplacer le segment au-dessus du tableau
A la fin de l’étape:
6. Insérer une carte
Notre tableau de bord commence à prendre forme. La prochaine étape nécessite un accès à Internet et est une nouveauté de novembre 2016.
- Sélectionner les colonnes Pays et CA Annuel (entête compris)
- Insérer > Graphiques > Cartes > Carte choroplèthe
Une carte choroplèthe est une carte thématique où les régions sont colorées ou remplies d’un motif qui montre une mesure statistique, tels la densité de population ou le revenu par habitant.
- Modifier le titre du graphique: Chiffre d’affaire par Pays
- Modifier les couleurs: Outils de graphique > Création > Styles du graphiques > Modifier les couleurs > Palette monochrome 12
- Positionner ensuite la carte comme ci-dessous:
Remarque:
La carte ne prend que la première valeur trouvée pour le pays et non la somme pour un pays. Par exemple, pour la France, la première valeur est 514849,54 en 2016 puis 385282,51 en 2015. Si on clique sur la France dans le graphique, nous pouvons voir la valeur de 514849,54.
Pour avoir une réelle utilité de la carte, il faudra donc cliquer sur l’année souhaitée dans le segment. Ainsi la bonne valeur sera affichée dans le graphique.
7. Créer une macro pour actualiser
L’objectif est de créer un bouton permettant d’actualiser les données du second tableau si on modifie des valeurs dans le premier. Nous n’allons pas rentrer dans le code VBA mais uniquement utiliser l’enregistreur de macro.
- Placer le curseur dans la cellule A1 (le but étant de ne pas avoir la cellule sur le second tableau).
- Cliquer sur le petit bouton en bas à gauche pour enregistrer la macro:
- Une fenêtre apparaît. Dans Nom de la macro, écrire: Rafraichir_Donnees
- Cliquer sur OK
- Placer le curseur en cellule A6
- Clic-droit > Actualiser
- Cliquer sur le carré noir en bas à gauche (pour arrêter l’enregistreur de macro)
La macro a été créée avec succès.
N’oubliez pas d’enregistrer (CTRL+S) régulièrement votre fichier.
8. Définir les couleurs
A présent, nous allons mettre notre classeur aux couleurs de Noël mais vous pouvez choisir d’autres styles si vous préférez.
- Mise en page > Thèmes > Couleurs > Choisir Jaune
Vous pouvez constater que toutes les couleurs ne sont donc pas jaunes mais rouges !
Toutefois le segment n’a pas les couleurs attendues. Nous les modifierons dans la dernière étape.
9. Insérer des icônes
Voici la seconde nouveauté Excel 2016 que nous allons utiliser. Il s’agit de l’insertion d’icônes modifiables en quelques clics. Jusqu’à présent, pour rendre mes applications plus professionnelles, je téléchargeais mes icônes sur le site https://thenounproject.com/ puis je modifiais les couleurs à l’aide de SNAGIT. Cela était souvent fastidieux mais le résultat était celui que j’attendais pour une application professionnelle.
Mais avec cette nouveauté d’Excel, ce temps-là est révolu puisque nous avons la possibilité d’ajouter des icônes (plus de 500 sont proposées à ce jour) et de les modifier directement dans Excel en quelques clics seulement comme nous allons le voir.
Nous allons commencer par ajouter l’icône permettant de rafraîchir les données. Puis nous customiserons le classeur pour lui apporter une touche de fête de fin d’année !!!
- Insérer > Illustrations > Icônes > Lieux > Choisir l’icônes cartes comme ci-dessous > Insérer
- Placer l’icône au-dessus de la carte
- Sélectionner l’icône
- Outils d’image > Format (vous avez la possibilité de customiser votre icône comme bon vous semble)
- Styles de graphique > Remplissage d’image > Choisir Rouge
- Clic-droit > Affecter une macro > Choisir Rafraichir_Donnees > OK
Pour tester le nouveau bouton, modifier la première ligne des données en remplaçant Italie par Pologne.
Cliquez ensuite sur l’icône nouvellement créé et vous verrez que le second tableau se met à jour ainsi que le graphique (si votre segment n’est pas défini sur l’année 2015).
Nous allons à présent ajouter quelques icônes pour « décorer ». Attention, il s’agit uniquement d’un exemple pour les périodes de Noël. Pour la réalisation d’un outil plus professionnel, il faudra éviter d’en mettre trop. Comme toujours, il faut rester simple !
- Insérer > Illustrations > Icônes > Lieux > Choisir l’icônes cartes comme ci-dessous > Insérer
- Dans Météo, vous pouvez choisir les icônes Neige et Bonhomme de neige.
- Modifier la couleur: Outils d’image > Format > Styles de graphique > Remplissage d’image > Choisir Rouge
- Les déplacer où bon vous semble, copier / coller, redimensionner…
Vous devriez avoir quelque chose comme ceci:
10. Mettre à jour les couleurs
Il ne nous reste plus qu’à modifier les couleurs du segment pour être en adéquation avec notre thème de Noël.
- Sélectionner le segment
- Outils segments > Options > Styles de segments
- Clic-droit sur le rouge > Dupliquer
- Le nommer Noël
- Segment entier > Format > Bordure: Choisir la couleur rouge ; Encadrer l’ensemble
- Sélectionné avec données > Police: Blanc ; Remplissage: Rouge
- Cliquer sur OK
- Outils segments > Options > Styles de segments > Choisir « Noël »
- Pour finaliser: Affichage > Afficher > Décocher Quadrillage
Et voici la feuille finalisée:
Bonus
Avant de vous laisser, je vais vous montrer une toute dernière chose. Mais comme pour les icônes et la carte, il est nécessaire d’avoir une connexion Internet. il s’agit là de la dernière nouveauté d’Excel 2016 de cette recette.
- Positionnez votre cellule sur celle qui contient le mot Pologne (en F7).
- Révision > Aperçu > Recherche intelligente
Vous pouvez constater qu’Excel a fait une recherche sur la Pologne et affiche les informations relatives au pays:
Je vous souhaite de Joyeuses fêtes de fin d’année et espère que vous prendrez autant de plaisir que moi à pratiquer Excel en 2017 !
Et n’hésitez pas à aller voir d’autres recettes Cooking-Excel !!!