Étiquette : Excel

Le calendrier Excel 2021 est là !

Le calendrier Excel 2021 est là !

Après mon premier calendrier Excel 2020, voici la relève : le calendrier Excel 2021. Pour l’histoire, je vous laisse à disposition l’ancienne version, mais vous recommande vivement la nouvelle ! De nombreuses nouvelles fonctionnalités ont été ajoutées ainsi que de nouvelles régions et nouveaux pays.

Ainsi vous pourrez retrouver tous les jours fériés de chaque canton Suisse et de chaque land d’Allemagne. Sans compter les particularités des départements d’outre-mer et territoires d’outre-mer de la France : Réunion, Martinique, Guadeloupe, Guyane française, Polynésie française, Nouvelle-Calédonie, Mayotte et Saint-Pierre-et-Miquelon. Enfin les spécificités de l’Alsace et la Moselle sont désormais également intégrées.

Dernière marque de fabrique du blog : aucune macro n’a été utilisée. Si vous vous demandez pourquoi je m’embête tant à éviter les lignes de code, relisez cet article sur macro or not macro.

Un calendrier Excel 2020, 2021 ou 2020-2021, c’est vous qui décidez !

C’est l’option qui a nécessité le plus de travail, mais désormais vous pouvez déterminer le mois de départ de votre calendrier .

Choix du mois de départ
Choix du mois de départ

Détermination des jours fériés de l’année suivante

Certaines dates ne posent pas de difficultés à être déterminées, comme le jour de Noël, c’est toujours le 25 du mois de décembre. Mais pour Pâques, c’est une autre histoire. Heureusement, des formules relativement simples existent pour déterminer la bonne date de Pâques.

La particularité de démarrer un calendrier au mois de juin est de devoir calculer les jours fériés de l’année suivante et d’afficher la bonne date.

Heureusement, Excel est équipé de très nombreuses formules qui vont mettre toutes ces dates en musique assez facilement.

Jours fériés année n et n+1
Jours fériés année n et n+1

Mise en valeur du changement d’année sur les mois du calendrier

Lors de la construction du fichier, je me suis rendu compte que l’affichage monochrome était parfait pour une année complète (de janvier à décembre). Mais pour un calendrier à cheval sur deux années (2020-2021), une petite mise en forme conditionnelle s’imposait.

Les mois de l’année suivante sont désormais teintés d’une couleur plus sombre et permettent de détecter la nouvelle année.

Mise en valeur changement année
Mise en valeur changement année

L’affichage d’une ou de deux années dans l’en-tête du calendrier

Lorsque votre calendrier va de janvier à décembre, aucun problème pour placer un titre « Année : 2021 ». Mais lorsque vous être à cheval entre deux années, il faut bien introduire également un titre dynamique qui affiche « Année 2020 / 2021 » par exemple.

Mise en valeur titre calendrier deux années
Mise en valeur titre calendrier deux années

Tous les mois n’ont pas le même nombre de jours…!

C’est une évidence, bien sûr. Sauf pour Excel qui ne sait pas dynamiquement adapter le quadrillage du calendrier. J’ai donc fait toutes les configurations nécessaires pour que le bas du calendrier soit juste parfait !

J’apprécie particulièrement la précision et la justesse. Ne souhaitant pas voir des cases vides en bas du calendrier, j’ai fait le nécessaire pour que les mises en forme conditionnelles fassent effet. Ainsi, votre calendrier Excel est épuré.

Calendrier Excel 2021 - Affichage fin de mois
Affichage fin de mois

Cela peut paraître anodin mais allez simplement voir la liste des mises en forme conditionnelles que j’ai configurées rien que pour ce « détail » !

J’ai compté jusqu’à 19 règles de mise en forme conditionnelle pour le calendrier dans son ensemble ! Et j’ai pourtant optimisé la configuration des plages d’application… sinon, j’aurais presque multiplié ce nombre par 12 !

Mettre en évidence « Aujourd’hui » dans votre calendrier

Je vous propose également une option pour activer la mise en évidence du jour actuel dans le calendrier. Si vous comptez l’imprimer, c’est peut-être inutile. Raison pour laquelle il vous suffit de choisir l’option qui vous convient le mieux !

Calendrier Excel 2021 - Choix afficher aujourd hui
Choix afficher aujourd’hui hui
Calendrier Excel 2021 - Affichage aujourd hui dans le calendrier
Affichage de Aujourd’hui

Les jours fériés et les dates particulières

En plus de présenter les jours fériés officiels, vous pourrez décider d’afficher les dates particulières en complément. Qu’est-ce qu’une date particulière ? Ce sont les jours de changement d’heure ou la date de la fête des mères par exemple.

Si vous le souhaitez, vous pouvez changer la configuration de cette date d’une « date particulière p » en un « jour férié f », ou vice-versa. Ainsi, elle apparaîtra dans le calendrier ou pas.

Calendrier Excel 2021 - Option d'affichage dates particulières
Option d’affichage dates particulières

Cette option activera ou désactivera les dates marquées comme « particulières » dans le second onglet du fichier. Comme ce n’est qu’un paramètre, vous pourrez l’adapter à vos besoins.

Calendrier Excel 2021 - Configuration date particulière
Configuration date particulière

Un calendrier Excel 2021 avec ou sans les numéros de semaine

C’est encore à vous de choisir si vous souhaitez faire apparaître les numéros de semaine. Une simple option « Oui » / « Non » les fera apparaître ou disparaître sur tout le calendrier. Voyez par vous-même :

Calendrier Excel 2021 - Option d'affichage numéro de sermaine
Option d’affichage des numéros de semaine
Calendrier Excel 2021 - Affichage numéro de semaine
Affichage numéro de semaine

Un calendrier Excel 2020-2021 imprimable

Tous les paramétrages sont prêts. Votre calendrier ne s’imprimera que sur une seule page.

Si vous voulez maîtriser la zone d’impression d’Excel, lisez mon article sur le sujet.

Calendrier Excel 2021 - Impression sur une page
Impression sur une page

Avez-vous besoin d’autre chose ?

Je vous propose ici un calendrier avec les jours fériés des pays suivants :

  • France
  • Belgique
  • Luxembourg
  • Suisse (par canton, dont Genève, Vaud, Valais, Fribourg, etc.)
  • Allemagne (par Land)

Jours fériés de la France 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 28 mars 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Assomptiondimanche 15 août 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de l’Alsace Moselle 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 28 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Assomptiondimanche 15 août 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021
Saint Étiennedimanche 26 décembre 2021

Jours fériés de la Réunion 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Assomptiondimanche 15 août 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Abolition esclavagelundi 20 décembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Martinique 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Mardi gras mardi 16 février 2021
Cendres mercredi 17 février 2021
Mi-Carême jeudi 4 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Abolition esclavagesamedi 22 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Jour des Défuntsjeudi 15 juillet 2021
Assomptiondimanche 15 août 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Guadeloupe 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Mardi gras mardi 16 février 2021
Cendres mercredi 17 février 2021
Mi-Carême jeudi 4 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Abolition esclavagejeudi 27 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Jour des Défuntsjeudi 15 juillet 2021
Assomptiondimanche 15 août 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Guyane française 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Mardi gras mardi 16 février 2021
Cendres mercredi 17 février 2021
Mi-Carême jeudi 4 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Abolition esclavagejeudi 10 juin 2021
Fête nationalemercredi 14 juillet 2021
Jour des Défuntsjeudi 15 juillet 2021
Assomptiondimanche 15 août 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Polynésie française 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Arrivée Évangilevendredi 5 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête Autonomiemardi 29 juin 2021
Fête nationalemercredi 14 juillet 2021
Assomptiondimanche 15 août 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Nouvelle-Calédonie 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Assomptiondimanche 15 août 2021
Fête citoyennetévendredi 24 septembre 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de Mayotte 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Abolition esclavagemardi 27 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Assomptiondimanche 15 août 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de Saint-Pierre-et-Miquelon 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 14 mars 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Victoire 1945samedi 8 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête des mères dimanche 30 mai 2021
Fête nationalemercredi 14 juillet 2021
Assomptiondimanche 15 août 2021
Toussaintlundi 1 novembre 2021
heure d’hiver -1h dimanche 7 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Libération SPMvendredi 24 décembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Belgique 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 28 mars 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête nationalemercredi 21 juillet 2021
Assomptiondimanche 15 août 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Toussaintlundi 1 novembre 2021
Armistice 1918jeudi 11 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés du Luxembourg 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 28 mars 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Fête du Travailsamedi 1 mai 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête nationalemercredi 23 juin 2021
Assomptiondimanche 15 août 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Toussaintlundi 1 novembre 2021
Noëlsamedi 25 décembre 2021
Saint Étiennedimanche 26 décembre 2021

Jours fériés du Québec 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 14 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Patrioteslundi 24 mai 2021
Fête nationalejeudi 24 juin 2021
Fête du Canadajeudi 1 juillet 2021
Fête du Travaillundi 6 septembre 2021
Action de grâceslundi 11 octobre 2021
heure d’hiver -1h dimanche 7 novembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Suisse Canton de Fribourg 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 28 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Fête-Dieujeudi 3 juin 2021
Fête nationaledimanche 1 août 2021
Assomptiondimanche 15 août 2021
Jeûne fédéraldimanche 19 septembre 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Toussaintlundi 1 novembre 2021
Immaculée Conceptionmercredi 8 décembre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Suisse Canton de Genève 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 28 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Lundi de Pentecôtelundi 24 mai 2021
Fête nationaledimanche 1 août 2021
Jeûne genevoisjeudi 9 septembre 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Noëlsamedi 25 décembre 2021
Restauration de la Républiquevendredi 31 décembre 2021

Jours fériés de la Suisse Canton de Vaud 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Heure d’été +1h dimanche 28 mars 2021
Vendredi Saintvendredi 2 avril 2021
Pâquesdimanche 4 avril 2021
Lundi de Pâqueslundi 5 avril 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Fête nationaledimanche 1 août 2021
Jeûne fédéraldimanche 19 septembre 2021
lundi du Jeûne fédérallundi 20 septembre 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Noëlsamedi 25 décembre 2021

Jours fériés de la Suisse Canton du Valais 2021

Fériés et événementsDate
Jour de l’anvendredi 1 janvier 2021
Saint Josephvendredi 19 mars 2021
Heure d’été +1h dimanche 28 mars 2021
Pâquesdimanche 4 avril 2021
Ascensionjeudi 13 mai 2021
Pentecôtedimanche 23 mai 2021
Fête-Dieujeudi 3 juin 2021
Fête nationaledimanche 1 août 2021
Assomptiondimanche 15 août 2021
Jeûne fédéraldimanche 19 septembre 2021
Heure d’hiver -1h dimanche 31 octobre 2021
Toussaintlundi 1 novembre 2021
Immaculée Conceptionmercredi 8 décembre 2021
Noëlsamedi 25 décembre 2021

Télécharger le calendrier Excel 2021 mais aussi calendrier perpétuel

Une fois vos coordonnées saisies, vous aurez accès au bouton de téléchargement.

Entrez votre adresse e-mail pour débloquer l'accès au calendrier Excel 2021

  • Calendrier Excel perpétuel - Il vous suffit de changer l'année et tout se met à jour.
  • Jours fériés automatiques pour 55 régions différentes ! France, Belgique, Luxembourg, Suisse, Allemagne, Québec
  • Affichage des numéros de semaine configurable
  • Mois de démarrage du calendrier configurable
  • Présentation soignée

Posez vos questions, donnez-moi votre avis dans les commentaires de cette page. Si vous souhaitez que j’ajoute de nouvelles régions ou de nouveaux pays, faites-moi signe également !

Le tableau croisé dynamique pour les nuls : c’est fini !

Le tableau croisé dynamique pour les nuls : c’est fini !

Ce nouvel article « le tableau croisé dynamique pour les nuls » vient en complément de l’article « Réussir son premier tableau croisé dynamique« . Après avoir lu l’article, vous saurez faire la différence entre les tableaux d’Excel simples et les Tableaux Croisés Dynamiques (TCD) !

Tableau croisé dynamique pour les nuls : Poser les bases

Excel est un tableur. Au départ, il a été conçu pour manipuler des données sous forme de tableaux : des lignes et des colonnes de valeurs.

Qui dit manipuler, dit faire des opérations avec ces données. On peut les compter, les additionner, les soustraire, etc.

Pour cet objectif, Excel propose toute une panoplie de fonctions (J’en ai dénombré 480 sur la page officielle de Microsoft). Avec toutes celles-ci, on pense qu’il doit bien exister une fonction pour chacun de nos besoins.

C’est justement sur ce dernier mot qu’il faut s’arrêter : nos besoins !

Définir ses besoins avant d’expliquer les tableaux croisés dynamiques (pour les nuls et pour les autres !)

Comme une image vaut mieux qu’un long discours, prenons un exemple de tableau. À partir de là, nous pourrons déterminer les différents besoins.

Voici un fichier exemple que vous pouvez récupérer sur le site de Microsoft.

Fichier exempleTCD – Fichier exemple

Vous avez ici des chiffres de ventes par branche, par pays, par produit et par date. Avec toutes ces données, on peut imaginer un bon nombre d’analyses à réaliser…

Commençons par un besoin « simple »

Votre responsable vient vous voir et vous demande : « Peux-tu me faire le top 5 des pays où nous vendons le plus ? ».

À cet instant, votre cerveau commence à entrer en ébullition…

Alors d’abord, il faudrait que je fasse un tri par pays… Ensuite, il faudra que je fasse le total pour chacun d’entre eux. Après je pourrai le noter dans une liste et faire un tri sur le total.

Entre nous, cela fait déjà pas mal de manipulations à faire pour juste « un top 5 », vous ne trouvez pas ?

Excel ne recèlerait pas une fonction pour faire cela en quelques clics ? Vous me voyez venir ? Mais d’abord, continuons avec un besoin plus élaboré.

Et si la demande était plutôt… celle-ci

« J’aurais besoin de l’évolution des ventes par produit, par pays et par mois »

Là, vous vous dîtes que cela va certainement vous prendre des heures de construire manuellement ce type de tableau. Consolider tous les chiffres manuellement prendra du temps.

Même si des fonctions Excel existent pour vous aider à cela, à chaque fois, il faut un peu de temps pour construire votre tableau.

>> Je vous propose d’ailleurs d’essayer par vous-même de répondre à cette demande sans les tableaux croisés dynamiques. Ainsi même les « nuls » (je préfère quand même l’expression « à qui on n’a jamais expliqué comment ça fonctionne ») auront une solution dans cette situation.

Le tableau croisé dynamique aurait toutes les réponses ? (pour les nuls et pour les autres !)

Contrairement au tableau contenant les chiffres de ventes, un tableau croisé dynamique (TCD) ne permet pas de saisir de valeur. Un TCD a BESOIN d’un simple tableau de valeurs pour fonctionner. Il va puiser toutes ses données de ce dernier.

Un TCD sert à faire toutes les consolidations automatiquement. Le mot « dynamique » vient d’ailleurs de là. Derrière le mot « consolidation », il faut comprendre la liste des opérations ci-dessous :

Tableau croisé dynamique pour les nuls - Opérations de consolidationTableau croisé dynamique pour les nuls – Opérations de consolidation

Un autre nom, plus pompeux certes, mais peut-être plus clair, serait « tableau consolidant automatiquement les valeurs d’un autre tableau selon les axes sélectionnés par l’utilisateur ». Finalement, on va s’en tenir à TCD, OK ?

Passer au tableau croisé dynamique pour tous

Récapitulons ce que vous devez avoir appris jusqu’ici :

  1. Un TCD a besoin d’un tableau normal avec des données pour fonctionner.
  2. Un TCD n’est pas modifiable. Son contenu n’est que le résultat d’une consolidation des données du tableau source.
  3. Il faut établir la question à laquelle on souhaite répondre avant de construire un TCD.

Sur ce dernier point, il faut être clair. On ne peut avec un seul tableau répondre à deux questions différentes.

Un tableau croisé dynamique répond à une question. Deux questions = Deux TCD.

Charles Exceller

Déterminer le top 5 des pays où il y a eu le plus de ventse et présenter l’évolution des ventes par produit, par pays et par mois n’est pas la même chose.

Voici donc un nouvel apprentissage : vous devrez construire autant de TCD que vous avez de questions ! Mais, rassurez-vous, Excel sait faire cela parfaitement ! C’est la mémoire de votre ordinateur qui fera défaut avant Excel, je vous l’assure !

Passer de l’idée à l’action : Dessinez votre tableau croisé dynamique !

Avant de commencer à jouer avec l’objet dans Excel, prenez une feuille à côté de vous et représentez la réponse à votre question.

PaysVentes
pays 11’234 €
pays 22’345 €
etc.x’xxx €
Représentation cible de notre futur tableau croisé dynamique

À partir de ce moment, vous êtes prêt à construire votre premier tableau croisé dynamique.

On pourra noter qu’en ligne se trouvent les pays, et les valeurs qui nous intéressent sont les sommes des ventes.

Deuxième question, deuxième représentation :

ProduitPaysMois 1Mois 2Total
Article 1
Pays 112 €23 €35 €
Pays 245 €56 €101 €
Article 2
Représentation cible du futur 2e tableau croisé dynamique

Première observation, ce tableau n’a rien à voir avec le premier. Vous comprenez donc aisément qu’un deuxième TCD est nécessaire pour le représenter.

Vous noterez les éléments suivants :

  1. Le nombre de lignes est déterminé par le nombre de produits puis par le nombre de pays.
  2. Les colonnes contiennent les mois.
  3. Les valeurs représentées sont les sommes des ventes (correspondantes au mois, au produit et au pays donnés).

En représentant ce tableau, vous pourriez vous rendre compte qu’il manque l’indication de l’année. Si vos données contiennent des valeurs sur plusieurs années, les ventes pourraient concerner tous les mois de janvier (toutes années confondues). Ce point doit rester à votre esprit. Attendons de voir comment Excel se comporte.

Créez votre tableau croisé dynamique

Maintenant que vous avez les idées claires sur votre tableau (ou vos tableaux) cible, vous pouvez prendre les rênes des TCDs.

Cliquez sur « Insertion » dans le ruban, puis cliquez sur « Tableau croisé dynamique ».

Tableau croisé dynamique pour les nuls - Insérer votre TCDTableau croisé dynamique pour les nuls – Insérer votre TCD

L’écran suivant apparaît alors :

Tableau croisé dynamique pour les nuls - Sélection des donnéesTCD – Sélection des données

Plusieurs observations sur cet écran :

  1. Excel essaie de détecter automatiquement le tableau qui sera la source de votre TCD. Il peut être juste comme faux. Toujours vérifier.
  2. Sur votre feuille de données, la source de données se trouve encadrée par des pointillés verts (cf. zoom de la capture d’écran). C’est un autre moyen de vérifier que toutes vos données sont correctement sélectionnées.
  3. Par défaut, Excel insérera votre TCD sur une nouvelle feuille de calcul. Ne soyez pas surpris.

Tableau croisé dynamique pour les nuls - Composition de votre feuilleTCD – Composition de votre feuille

Votre nouvelle feuille se composera de trois zones :

  1. À cet emplacement, se trouvera votre tableau résultat. Cette zone ne servira qu’à quelques actions limitées (filtrer et trier).
  2. Ici, vous verrez l’ensemble des colonnes de votre tableau source. Ce sont celles que vous pourrez utiliser pour construire votre TCD. Si une colonne manque à l’appel, vérifier à nouveau votre source, contient-elle bien toutes les colonnes ?
  3. La clé de voûte de votre TCD se trouve ici. Vous construirez ici le contenu des lignes, des colonnes ou des valeurs de votre TCD.

Répondre à notre première question : le top 5 des pays par volume de ventes

Si vous remontez de quelques paragraphes, lorsque je vous ai demandé de dessiner votre tableau cible, j’ai décrit ce tableau d’une manière particulière. J’ai mis l’accent sur les données qui se trouvaient en lignes, en colonnes ou en valeurs.

C’était une manière d’introduire ce qu’il vous reste à faire. Glissez-déposez simplement les colonnes de votre tableau source (en 2) vers les emplacements correspondants (en 3).

Vous obtiendrez pour la première question (le top 5 des pays) le tableau suivant :

Tableau croisé dynamique pour les nuls - Première réponseTCD – Première réponse

Nous avons une petite chance ici, car il n’y a que 5 pays dans la source de données, sinon, quelques clics supplémentaires auraient été nécessaires… 😉

Par ailleurs, j’ai dû mettre en forme les valeurs sous forme monétaires (onglet Accueil, format nombre comptabilité). Souvent, Excel saura reconnaître le bon format des données.

Répondre à notre deuxième question : Les chiffres de ventes des produits, par pays et par mois.

Comme annoncé, vous aurez à insérer un nouveau TCD. Après l’avoir fait, vous aurez à réaliser les opérations suivantes :

  1. Glissez-déposez d’abord les produits, puis les pays en lignes.
  2. Passez ensuite aux ventes vers les valeurs (Sales)
  3. En dernier, placez la date en colonnes.

Vous verrez alors le résultat suivant :

Deuxième réponseTCD – Deuxième réponse

Notez qu’en 4, Excel a reconnu automatiquement que la colonne « Date » contenait effectivement des données de dates (Excel ne se fie qu’au contenu, pas au nom…). Et réalise automatiquement des regroupements par année et par trimestre !

En plus, votre tableau contient des sous-totaux intermédiaires et les totaux par lignes et par colonnes ! Que demander de plus ! Tous ces résultats consolidés en quelques clics…

Merci les tableaux croisés dynamiques ! Pour ceux qui le souhaitent, vous pouvez télécharger le fichier résultat ou encore continuer votre lecture avec « Réussir son premier tableau croisé dynamique« .

Je vous présente également dans cette vidéo un autre exemple d’utilisation des tableaux croisés dynamiques avec les données sur le Covid-19 mis à disposition par le gouvernement via https://data.gouv.fr.

https://www.youtube.com/watch?v=H0iao5mAtcs

Créer une liste déroulante dynamique sur Excel

Créer une liste déroulante dynamique sur Excel

Liste déroulante dynamique sur Excel, liste de choix ou menu déroulant sur une cellule Excel, cette fonctionnalité porte bien des noms différents. À travers cet article, je vous propose de faire le tour des très nombreuses options pour créer une liste déroulante sur Excel. D’une simple liste déroulante, je vais aller jusqu’au cas le plus complexe : deux listes déroulantes liées et remplies dynamiquement.

Avant de commencer, et même si je sais que vous êtes pressé d’avoir votre réponse, vous devez vous poser quelques questions. Excel possède trop d’options pour foncer tête baissée sur la première à disposition. Vous risquez de vous rendre compte trop tard que votre besoin était différent.

Préambule

Je dois vous prévenir avant de poursuivre la lecture de cet article. Vous ne trouverez pas une seule ligne de programmation dans cet article. Je revendique un usage d’Excel (et des autres produits de la suite Office d’ailleurs) sans VBA et donc sans macro.

Cet article ayant pour objectif de traiter du cas le plus simple au cas le plus complexe, ne soyez pas surpris de sa longueur. Le cas plus complexe étant les listes déroulantes dynamiques Excel et en cascade.

Je vous invite à répondre aux questions ci-dessous et ainsi choisir l’option qui correspondra le plus à votre besoin.

Une nouvelle question s’est récemment ajoutée, quelle version d’Excel possédez-vous ? Si vous possédez un abonnement à Microsoft 365, alors la création d’un menu déroulant sur Excel (en particulier pour les menus déroulant dynamique) est devenu encore plus facile. Les nouvelles fonctions UNIQUE(), FILTRE(), TRIER() ainsi que les tables dynamiques (rien à voir avec les tableaux croisés dynamiques) font de ma description du cas 4 une archive de l’histoire..

Mais comme vous n’avez pas nécessairement un abonnement, je vous laisse le mode d’emploi à disposition !

Les questions à se poser avant de créer une liste déroulante dynamique ou statique sur Excel

Liste déroulante dynamique ou statique ?

Cette question est essentielle pour vous orienter vers la solution la plus appropriée. Formulée autrement, cette question revient à vous demander si les choix présentés par votre liste déroulante seront TOUJOURS les mêmes ou bien devront-ils varier selon le contexte ?

Liste déroulante Excel – Choix dynamique

Si les choix seront toujours les mêmes (Fruit ou Légume) par exemple), alors votre liste sera statique. Rendez-vous au paragraphe : « Cas n°1 : Créer un menu déroulant statique sur Excel« .

Vous êtes presque dans cette situation, mais vous savez que votre liste de choix pourrait s’agrandir à l’avenir (Nouvelle catégorie de produit, nouveau produit). Alors rendez-vous au paragraphe : « Cas n°2 d’une liste statique extensible« .

Si au contraire, en fonction d’un critère, la liste présentée doit être différente, on parlera d’une liste déroulante dynamique sur Excel. Plusieurs cas de figure sont possibles avec une liste déroulante dynamique : je vous invite à répondre aux questions suivantes.

D’où viennent les données de votre liste déroulante dynamique Excel ?

Est-ce que vous connaissez toutes les valeurs possibles de votre liste à l’avance ? Ou bien, est-ce que la liste de vos choix va se constituer dynamiquement ?

En reprenant l’illustration ci-dessus, on voit que les listes des catégories et des produits sont connues à l’avance. Si vous êtes dans ce cas, vous pouvez vous rendre au paragraphe : « Cas n°3 : liste déroulante dynamique basée sur une liste fixe« .

Vous l’aurez deviné, si vous ne vous retrouvez pas dans le cas 3, c’est que tous les contenus de vos listes sont dynamiques. La solution existe sans programmation, mais il va falloir s’accrocher ! Vous êtes à la limite de développer une application. Excel peut vous aider à présenter votre idée, mais n’est peut-être plus le produit le plus adapté… Vous devriez considérer Access ou un autre produit. Toutefois, voici la solution avec Excel : Cas n°4 : Créer une liste déroulante dynamique en cascade sur Excel.

Est-ce que je possède plusieurs listes dépendantes les unes des autres ?

Par exemple, je possède deux listes déroulantes. Dans la première, je choisis « Fruit » ou « Légume » et dans la deuxième, je vais choisir parmi une liste de fruits ou une liste de légumes.

On parlera dans cette situation de listes déroulantes en cascade. Ce cas est la situation la plus complexe à traiter dans Excel.

Retrouvez cet article en vidéo

Voici deux vidéos pour voir comment créer des menus déroulants sur Excel. La première pour les possesseurs d’un abonnement à Excel 365 et la deuxième pour les autres…

Dans cette vidéo plus récente et je vous présente la facilité avec laquelle tous les scénarios de création de menus déroulants dynamiques se font désormais :

Ce sujet des menus déroulants est véritablement un sujet simple d’apparence est pourtant pouvant devenir véritablement complexe.

Suite à des questions posées dans les commentaires de la précédente vidéo, un sujet majeur est apparu manquant !! Je présente en effet une technique pour mettre en place ces menus déroulants en cascade SANS contraintes de tri sur votre tableau et SANS avoir à mettre à jour un tableau croisé dynamique. Sauf que cela ne fonctionne que pour une cellule unique…

J’avais donc relevé le défi de trouver une solution TOTALEMENT DYNAMIQUE, pour plusieurs cellules (Avec Excel 365 uniquement… pour les autres, je vous invite à étudier le cas n°4 du présent article).

Le défi est maintenant relevé et en voici le fruit :

Pour tous ceux disposant d’une version plus ancienne d’Excel, alors c’est cette autre vidéo qu’il faut regarder :

Cas n°1 : Créer un menu déroulant sur Excel

Vous avez de la chance, c’est le cas le plus simple à traiter ! Excel résout ce problème en quelques clics !

Étape n°1 : Activez la validation des données

Excel utilise la fonctionnalité de « Validation des données » afin de proposer une liste déroulante sur une cellule.

Rendez-vous dans l’onglet « Données » puis cliquez sur « Validation des données »

Liste déroulante Excel – Validation des données

Étape n°2 : Configurer votre liste déroulante

Un pop-up apparaît. Vous devez sélectionner le choix « Liste » comme indiqué ci-dessous :

Liste déroulante Excel - Validation des données - Liste
Liste déroulante Excel – Validation des données – Liste

Étape n°3 : Configurer votre liste de choix

Pour une liste statique à usage unique, vous pouvez directement configurer votre liste de choix en allant dans le champ « Source » comme ci-dessous :

Liste déroulante Excel - Validation des données - Valeurs
Liste déroulante Excel – Validation des données – Valeurs

Vous devez séparer vos choix par un point-virgule. Aucun autre caractère n’est nécessaire, ni guillemets ni apostrophes.

Défauts de cette méthode

Une méthode simple et rapide, c’est pratique. Vous devez en connaître les limites pour l’utiliser uniquement dans les cas appropriés.

Défaut n°1 : Maintenir cette liste de choix dans la durée

Qu’on le veuille ou non, à un moment ou à un autre, on a besoin de mettre à jour cette liste de choix. Et souvent, la liste de choix va être présente à plusieurs endroits. Malgré la petite case à cocher discrète « Appliquer ces modifications aux cellules de paramètres identiques », vous aurez toujours un doute si cela a été correctement appliqué partout.

Liste déroulante Excel - Validation des données - Appliquer les modifications aux cellules de paramètres identiques
Appliquer les modifications aux cellules de paramètres identiques

Sans compter sur le fait qu’une fois sur deux vous risquez d’oublier de cocher cette case et ainsi rompre le lien avec les autres cellules disposant de cette même liste déroulante.

Défaut n°2 : Gérer les longues listes déroulantes

Dans l’exemple, je n’utilise que deux valeurs séparées par un point-virgule. Si votre liste contient plus de 5 valeurs, vous commencerez déjà à avoir des difficultés en termes de lisibilité.

Défaut n°3 : Retrouver toutes les valeurs de toutes vos listes déroulantes

Si vous souhaitez réaliser une vérification des choix proposés dans toutes vos listes déroulantes de votre fichier Excel, vous devrez rechercher les cellules proposant ces listes et les contrôler dans ce menu. Parmi les autres options de création de liste déroulante vous verrez que cela peut se faire d’une manière bien plus simple !

Cas n°2 : Créer liste déroulante statique mais extensible

Vous aurez quelques manipulations supplémentaires à réaliser par rapport au cas n°1, mais vous aurez le bénéfice de retirer l’ensemble de ses défauts. C’est à mon avis la solution à préconiser pour toutes les listes déroulantes qui n’ont pas à être dynamiques.

Étape n°1 : Créer une feuille de paramètres / données de références

Votre fichier Excel (votre classeur) peut posséder autant de feuilles que la mémoire de votre ordinateur peut supporter ! (c’est Microsoft qui le dit !)

Nombre maximal de feuilles par classeurLimité par la quantité de mémoire disponible (valeur par défaut 1 feuille)
Support de Microsoft

Selon mon expérience, j’ai toujours eu besoin de données « de référence ». Les choix proposés par les listes déroulantes sont de ceux-ci.

Personnellement, j’appelle toujours cette feuille « RefData », ainsi je n’ai aucun doute sur son contenu.

  • Cliquez sur le + qui se trouve en bas de votre classeur
  • Double-cliquez sur le nom « Feuil2 » pour éditer le nom
  • Saisissez « RefData »
Création de la feuille RefData
Création de la feuille RefData

Étape n°2 : Créer un tableau avec votre liste de valeurs

Le tableau est l’objet Excel parmi les plus puissants. J’aurai l’occasion de développer ce sujet dans d’autres articles. À ce stade, suivez simplement les instructions.

Dans la feuille RefData, placez-vous en A1 et donnez un intitulé à votre liste de choix, puis saisissez en A2, A3, etc, la liste de vox choix.

Liste déroulante Excel - Saisie liste choix
Saisie liste choix

Ensuite, sélectionnez vos cellules et cliquez sur « Mettre sous forme de tableau », choisissez une mise en forme de votre choix.

Liste déroulante dynamique Excel - Création tableau
Liste déroulante Excel – Création tableau

Puis, veillez à bien cocher la case « Mon tableau comporte des en-têtes ».

Mon tableau comporte des en-têtes
Mon tableau comporte des en-têtes

Étape n°3 : Renommer votre tableau et créer votre « Nom »

Autant prendre tout de suite de bonnes habitudes ! Lorsque vous créez un tableau, Excel le nomme par défaut « Tableau1 », « Tableau2 », etc. Je vous invite à donner des noms de Tableau qui vous parlent.

Dans le cas de mon exemple, je vais le nommer « TCategories ».

Liste déroulante Excel - Changer le nom du tableau
Changer le nom du tableau

Ensuite, vous devrez créer un « Nom ». Excel ne sait pas réaliser la validation des données à partir d’un tableau… mais à partir d’un « Nom ». Cela changera peut-être un jour…

Petite astuce, sélectionnez uniquement votre liste de valeurs sans le titre. Ce sera utile pour la suite.

Maintenant, rendez-vous sur l’onglet « Formules » puis cliquez sur « Gestionnaire de noms » puis sur « Nouveau ».

Liste déroulante dynamique - Création Nom
Création Nom

Sur l’écran suivant, vous pourrez renommer votre « Nom » comme ci-dessous :

Liste déroulante Excel - Changer le nom du nom
Changer le nom du nom

Veillez bien à ce que le champ « Fait référence à : » contienne « =TCategories[Catégories] » et pas « =TCategories[[#Tout];[Catégories]] ». Adaptez le contenu si besoin. Cela veut simplement dire que vous avez inclus le nom de la colonne dans votre sélection.

Étape n°4 : Créer votre liste déroulante dynamique Excel. Enfin !

Vous venez de créer votre tableau de référence, un « Nom » qui sera lié à votre tableau. Vous êtes maintenant fin prêt à configurer votre cellule.

Sélectionnez votre cellule cible de votre liste déroulante puis cliquez sur « Validation des données » de l’onglet « Données ». (Étapes identiques aux deux premières du Cas n°1). Puis sélectionnez « Liste » comme source des données.

Validation des données
Validation des données
Liste déroulante dynamique - Validation des données - Liste
Liste déroulante Excel – Validation des données – Liste

Et cette fois, au lieu de saisir votre liste de choix directement, vous saisissez « =LCategories » que vous venez de créer.

Avantages de cette méthode

Avantage n°1 : Votre liste de choix est dynamique

Il vous suffit de vous rendre sur la feuille RefData et ajouter un élément à la suite de vos valeurs pour que tout soit immédiatement à jour !

Avantage n°2 : Vous pouvez réutiliser la liste

En référençant votre « Nom » ou votre tableau dans des formules ou dans d’autres cellules, vous aurez la certitude d’utiliser toujours la bonne liste de valeurs.

Avantage n°3 : Trier automatiquement les valeurs au sein de votre liste déroulante

C’est l’ordre d’affichage dans votre tableau qui déterminera l’ordre d’affichage de votre liste déroulante. Vous pouvez ainsi ordonnancer vos valeurs selon vos besoins dans le tableau et votre liste s’affichera dans cet ordre.

Cas n°3 : Créer une liste déroulante dynamique Excel basée sur une liste fixe

Prenons un exemple pour bien situer ce cas de figure.

Vous avez une première cellule avec un choix à opérer « Fruits » ou « Légumes » et une deuxième cellule où vous souhaiteriez une liste déroulante de fruits ou une liste déroulante de légumes.

Vous connaissez par avance toutes les options possibles, reste à mettre en œuvre.

Étape n°1 : Créer votre tableau de référence

Comme à l’étape n°1 du cas n°2, créez une feuille nommée RefData dans laquelle vous placerez votre tableau de correspondance comme suit :

Liste déroulante dynamique - Tableau des valeurs
Tableau des valeurs

Étape n°2 : Préparer votre feuille cible

Pour l’étape suivante, vous aurez besoin de savoir dans quelle cellule sera votre liste déroulante dynamique. De plus, vous aurez également besoin de la cellule définitive où sera située la sélection de la catégorie (Fruit ou Légume).

Pour l’exemple, je resterai sur la feuille RefData afin de simplifier les captures d’écran. Voici comment j’ai préparé mon classeur :

Liste déroulante dynamique - Préparation cible
Préparation cible

Mon objectif sera de configurer une liste déroulante en F2 en fonction de la sélection en E2.

Étape n°3 : Préparer le calcul d’une plage dynamique

Je dois vous l’annoncer, vous devez vous accrochez. Ici, nous allons utiliser deux des fonctions des plus puissantes d’Excel, mais aussi des plus difficiles à appréhender au début : DECALER() et EQUIV().

J’y ai déjà fait référence dans mon article sur le calendrier Excel perpétuel. Mais je n’avais pas donné de détails. Ici, vous aller devoir apprendre à vous en servir !

Fixons notre objectif : nous souhaitons trouver une plage de valeurs (les produits) dont la catégorie correspond à la valeur en E2.

Il se trouve que la fonction DECALER() renvoie une plage de valeur, EQUIV() renvoie un rang dans une liste. Il ne restera qu’à compter le nombre de valeurs à retourner. Ce sera chose faite avec la fonction NB.SI().

Les paramètres de DECALER(réf, lignes, colonnes, [hauteur], [largeur]) signifient :

  • Réf : Votre point de départ pour calculer votre plage
  • Lignes : De combien de lignes dois-je descendre pour commencer ma plage de renvoi ?
  • Colonnes : De combien de colonnes dois-je me déplacer à droite pour commencer ma plage de renvoi ?
  • Hauteur : Combien de lignes seront dans ma plage de renvoi ?
  • Largeur : Combien de colonnes seront dans ma plage de renvoi ?

L’objectif ici est de répondre à chacune de ces questions. Les crochets indiquent que ces paramètres sont facultatifs et que par défaut, la valeur sera 1.

Liste déroulante dynamique Excel - Calculer la plage dynamiquement
Calculer la plage dynamiquement

Répondons à ces questions :

  • Réf : Votre point de départ sera le coin supérieur gauche de votre tableau (rectangle rouge)
  • Lignes : La fonction EQUIV() va donner le point de départ (blocs verts)
  • Colonnes : Nous souhaitons la liste des produits. Il faut donc se décaler d’une colonne à droite (flèche horizontale noire)
  • Hauteur : La fonction NB.SI() va compter le nombre d’éléments correspondants (double flèche rouge + bloc vert)

Dans mon exemple, cela donnera la fonction suivante :

=DECALER(TProduits[[#En-têtes];[Catégorie]];EQUIV(TCible[@[La catégorie de mon choix]];TProduits[Catégorie];0); 1; NB.SI(TProduits[Catégorie];TCible[@[La catégorie de mon choix]])😉

Étape n°4 : Créer le « Nom » et finaliser la validation des données

Vous n’avez plus qu’à configurer votre nouveau « Nom ». Rendez-vous dans l’onglet « Formules » puis « Gestionnaire de noms » afin de créer votre « Nom ».

Lorsque vous créez votre « Nom », vous devrez saisir la formule complète ci-dessus.

Liste déroulante dynamique Excel - Définir le nom dynamique
Définir le nom dynamique

Ensuite, vous devriez connaître la musique ! Rendez-vous sur votre cellule où cette liste déroulante devrait apparaître, onglet « Données », « Validation des données », sélectionnez « Liste » puis donnez votre liste comme source.

Bref avertissement

Si vous souhaitez ajouter des éléments dans votre catalogue initial, vous devrez trier votre tableau au minimum selon les catégories. En effet, cette méthode peut fonctionner uniquement si tous les fruits et tous les légumes sont consécutifs. Sinon, vous aurez de drôles de surprises…

Avantages de cette méthode

Devrez-vous faire cette manipulation pour chaque ligne de votre feuille cible ? Heureusement non !

Cependant, cela n’est garanti que si vous utilisez bien les tableaux d’Excel. Il suffit d’étendre le tableau, et vous verrez que toutes les cellules sont prêtes à l’emploi et fonctionnent parfaitement !

Etendre son tableau cible
Étendre son tableau cible

Cas n°4 : Créer deux listes déroulantes dynamiques en cascade sur Excel

Vous êtes dans la situation la plus complexe à mettre en place. Vous devez constituer une liste déroulante qui devra se mettre à jour automatiquement selon les saisies qui seront réalisées ailleurs dans votre classeur.

Pour aller au bout du bout, je vais aller encore plus loin en donnant les explications pour avoir deux listes déroulantes dynamiques et en cascades.

Un exemple vaut mieux qu’un long discours, alors en voici un :

Vous disposez d’une feuille dans laquelle vous avez une liste de produits qui peuvent être livrés par différents fournisseurs. Dans un second onglet, vous souhaitez sélectionner un produit et savoir quels fournisseurs pourraient le fournir.

Pour simplifier mes illustrations à travers les captures d’écran, je vais tout placer dans la même feuille.

Liste déroulante dynamique Excel - Exemple cascade dynamique
Exemple liste déroulante en cascade dynamique
Liste déroulante dynamique Excel - Exemple cascade dynamique suite
Exemple liste déroulante en cascade dynamique suite

L’objectif est de pouvoir ajouter de nouveaux producteurs et de nouveaux produits et que les listes déroulantes soient dynamiquement mises à jour.

Avertissement avant de continuer

Excel est plein de surprises. La solution que je vous propose n’est pas unique. C’est celle qui n’utilise que des fonctions relativement courantes.

D’autres solutions existent certainement. L’une d’entre elles est certainement l’utilisation de PowerQuery. J’ai fait le choix dans cet article de ne pas l’utiliser. Cet outil complémentaire à Excel (et intégré depuis la version 2016) nécessiterait une prise en main à part entière.

Si ces conditions vous conviennent, alors vous pouvez poursuivre la lecture de l’article !

Explication de la procédure à suivre

La mise en œuvre de la solution sans code va suivre les étapes suivantes :

  1. Construire la première liste déroulante dynamique
    • Configurer un tableau croisé dynamique (TCD) pour avoir une liste unique et triée des produits
    • Construire un « Nom » (cf. Gestionnaire de noms) qui s’alimente du précédent TCD. Ce sera l’étape la plus complexe car Excel ne gère pas les TCD comme les tableaux simples
    • Utiliser ce « Nom » pour la validation de votre première cellule
  2. Construire la deuxième liste déroulante dynamique
    • Configurer un deuxième TCD pour avoir la liste des producteurs livrant un produit
    • Construire un nouveau « Nom »
    • Utiliser ce « Nom » pour la validation de la deuxième cellule

Êtes-vous prêt ? C’est parti !

Construire la première liste déroulante dynamique

Étape n°1 : Construire le premier TCD

Le meilleur outil pour obtenir une liste triée de valeurs uniques et mis à jour automatiquement, c’est bien le TCD. Comme d’habitude (si vous avez bien lu le début de l’article), je vous invite à créer une feuille dédiée aux données de référence que je nommerai RefData. Cette feuille pourra également contenir les données intermédiaires nécessaires à notre objectif.

J’ai nommé mon premier tableau TProducteurs :

Liste producteurs - produits
Liste producteurs – produits

Je vais ensuite insérer un TCD sur la feuille RefData basée sur ce tableau. Mes premières cellules de RefData étant utilisées, je vais l’insérer en colonne H. Il est très pratique de l’insérer en première ligne (cellule H1).

Liste déroulante dynamique Excel - Liste dynamique cascade - 1er TCD
Liste dynamique en cascade – 1er TCD

Il est essentiel de désactiver l’affichage des totaux. Pour cela, cliquer à l’intérieur du TCD, onglet « Création », menu « Totaux généraux » puis « Désactiver pour les lignes et les colonnes ».

Liste déroulante dynamique Excel - Désactiver les totaux généraux TCD
Désactiver les totaux généraux TCD

Étape n°2 : Définir un « Nom » qui s’alimente du TCD

Ici, nous allons de nouveau utiliser une combinaison de fonctions Excel :

  • DECALER() afin de fournir à notre « Nom » la liste de choix
  • NBVAL() qui permet de compter le nombre de cellules qui ne sont pas vides.

Le reste du travail a déjà été fait par notre TCD.

Complétons ensemble les paramètres de la fonction DECALER() :

  • Réf : Votre point de départ sera la première cellule de votre TCD. Dans notre cas, il s’agira de RefData!H$2$.
  • Lignes : 0. En effet, pas de décalage à prévoir.
  • Colonnes : 0. Idem, pas de décalage à prévoir.
  • Hauteur : La fonction NBVAL(RefData!H$:H$)-1 va compter le nombre d’éléments non vides de toute la colonne. Ne pas oublier de retirer 1. La première ligne du TCD contient un titre qui ne doit pas être compté.

Avez-vous remarqué que cette fois-ci j’emploie les références avec le caractère $ ? Ils sont essentiels cette fois-ci, ne pouvant référencer ces cellules comme celle d’un simple tableau (cf. l’étape 3 du cas n°3).

Votre formule pour définir votre nom devrait donc être : =DECALER(RefData!$H$2;0;0;NBVAL(RefData!$H:$H)-1;).

Créez alors votre « Nom » avec cette formule et configurez votre cellule cible pour être validée avec cette liste de valeur. (Référez-vous aux étapes déjà décrites pour le cas n°3 à l’étape 4).

Construire la deuxième liste déroulante dynamique

Étape n°1 : Construire un second TCD !

Ce nouveau TCD doit ressembler en tous points à l’exemple donné en cas 3. Un tableau avec en première colonne les noms des produits et en deuxième, les produits livrés.

Pour cela, il faut créer un TCD basé sur le même tableau que précédemment (TProducteurs) mais configuré différemment.

Je vous invite à l’insérer sur la feuille RefData en J1.

Liste déroulante dynamique Excel - Liste dynamique cascade - 2nd TCD
Liste dynamique cascade – 2nd TCD

Mais, vous n’avez pas le même affichage ! Voici les changements à opérer afin d’obtenir cette présentation :

  1. Retirez l’affichage des totaux généraux comme précédemment.
  2. Dans le menu « Disposition du rapport », sélectionnez « Afficher sous forme tabulaire »
  3. Dans le menu « Disposition du rapport », sélectionnez « Répéter toutes les étiquettes d’élément »
Liste déroulante Excel - Configuration 2nd TCD
Configuration 2nd TCD

Et cette fois, vous obtenez le même résultat.

Étape n°2 : Trouver la bonne formule pour définir le « Nom » pour la validation de la cellule

Finalement, maintenant que notre second TCD ressemble à l’exemple du cas n°3, la suite lui ressemble aussi grandement !

Trois différences majeures tout de même :

  1. La définition du « Nom » se fait à partir des références des cellules et non des tableaux. Le caractère $ est OBLIGATOIRE pour un bon fonctionnement.
  2. Lorsque vous ajouterez des valeurs à votre tableau de producteurs, vous DEVREZ « Actualiser » vos TCD pour que les listes déroulantes soient à jour. Un raccourci clavier existe pour cela « Ctrl + Alt + F5 ».
  3. Pour déterminer l’ordre dans lequel les valeurs s’affichent dans votre liste déroulante, vous devrez opérer les tris de votre choix dans les TCD.

Cette fois, je vous donne le résultat directement, il suit exactement le même principe que le cas 3. Si vous avez bien nommé la feuille RefData et placé les TCD dans les colonnes indiquées, alors aucun changement à l’horizon.

Petite précision tout de même, mon tableau cible se nomme « TProduit ».

Liste déroulante Excel - Exemple cascade dynamique
Exemple liste déroulante en cascade dynamique

=DECALER(RefData!$J$1; EQUIV(TProduit[@Produits];RefData!$J:$J;0)-1; 1; NB.SI(RefData!$J:$J;TProduit[@Produits]);)

Vous pouvez alors définir votre « Nom » avec cette formule. Ensuite, configurez la validation de votre cellule basée sur cette liste.

Retrouvez cet article en vidéo sur Youtube

Je vous propose d’avoir une explication visuelle avec la vidéo que j’ai réalisée et publiée sur Youtube : Créer une liste déroulante dynamique sur Excel.

Et comme il n’est rien de mieux que d’avoir le fichier sous la main pour voir et reproduire, voici le fichier ci-dessous :

Entrez votre adresse e-mail pour débloquer l'accès au fichier exemple

  • Cas Excel 365 (V2)
  • Cas pour les autres versions d'Excel (>2013)

BRAVO et MERCI !

Vous avez réussi ! Vous avez lu la totalité de l’article, et appliquée appliquer la méthode que je vous ai présentée ?

Soyez fier.ère de vous ! Vous venez de mettre en pratique ce que peu savent faire et en plus SANS CODE !

Vous pouvez fièrement partager cet article et annoncez que vous savez le faire ! Si vous m’indiquiez en commentaire, sur Facebook ou sur LinkedIn votre avis, je l’apprécierais grandement !

Apprendre Excel : Des premiers pas aux premiers résultats

Apprendre Excel : Des premiers pas aux premiers résultats

Comment apprendre Excel ? Vaste programme ! Par où débuter son apprentissage d’Excel ? Apprendre Excel veut dire bien des choses différentes pour chacun d’entre nous : est-ce apprendre toutes les fonctions d’Excel ? Est-ce concevoir un classeur complet ? Est-ce savoir utiliser et faire évoluer des fichiers Excel existants ? Bref, apprendre Excel est à la fois un objectif et tout un parcours (Personnellement, j’en apprends encore après tant d’années d’utilisation !)

Pour bien apprendre Excel, commençons par comprendre les fondamentaux

Excel est un tableur. Mais qu’est-ce qu’un tableur ? Prenons un extrait de la définition sur Wikipédia :

Une feuille de calcul est une table (ou grille) d’informations de toutes natures (données industrielles, scientifiques, commerciales, comptables, financières, statistiques, données de métiers, etc.). D’une manière générale, toute problématique, pouvant être disposée en lignes et colonnes, éventuellement sur plusieurs plans, impliquant ou n’impliquant pas des calculs, rend le tableur pertinent.

Wikipedia

Une partie de la deuxième phrase est essentielle “toute problématique pouvant être disposée en ligne et colonnes”.

Pour apprendre Excel, il faut penser le monde en lignes et en colonnes. Vous devez apprendre à quadriller, classer, structurer dans des tableaux vos informations / données.

Un petit avertissement à ce stade : si vous pouvez penser votre problème en lignes et en colonnes, Excel est un candidat, mais pas nécessairement LA solution. Access est un autre candidat… D’autres critères sont à explorer pour bien sélectionner le bon outil de travail.

Vous pouvez ainsi consulter les 4,5 apprentissages clés d’un cours Excel et en particulier le premier !

Apprendre Excel c’est apprendre à automatiser

Automatiser, oui, c’est le mot approprié. On ne parle pas encore à ce stade de programmation, mais il s’agit de faire travailler Excel à notre place pour des tâches répétitives.

La première des tâches répétitives est la réalisation d’opérations mathématiques simples pour plusieurs lignes ou plusieurs colonnes.

Afin d’automatiser, il faut pouvoir donner des instructions du type « Prends la valeur de la cellule en 2e ligne et 2e colonne, ajoute la valeur de la cellule en 3e ligne et 2e colonne et donne-moi le résultat ».

Vous comprendrez que c’est un peu long à écrire…

Pour simplifier cette instruction, chaque cellule possède une adresse. On parle également de référence d’une cellule.

Cette référence de cellule s’écrit avec des lettres et un nombre.

Dans Excel 365, les cellules vont de A1 à XFD1048576.

Apprendre Excel : les références

A quoi servent les lettres et les nombres d’une référence ?

La lettre détermine le rang de la colonne.

« A » = 1ère lettre de l’alphabet = 1ère colonne

« Z » = 26e lettre de l’alphabet = 26e colonne

Et après le « Z », on recommence l’alphabet, mais avec deux lettres : « AA », « AB », « AC », …, et ainsi de suite.

La colonne ZZ se trouve donc à la 26*26 + 26 = 702e colonne. (Nous avons 26 fois l’alphabet avec deux lettres + 1 fois l’alphabet avec une seule lettre)

Finalement, on ne cherchera pas vraiment à savoir à quel rang se trouvent les colonnes. Ce qui compte, c’est d’avoir compris le principe.

La colonne XFD se situe à la 16’384e colonne (je vous passe le calcul). Il s’agit d’une puissance de 2.

16’384 = 214

Eh oui, les informaticiens aiment bien les puissances de 2 !

Les nombres déterminent le rang de la ligne

Là, c’est bien plus simple à comprendre.

A12 sera la cellule située à la 12e ligne de la colonne A. B28, sera la cellule située à la 28e ligne de la colonne B.

Depuis quelques versions d’Excel, le nombre de lignes disponibles est de 1’048’576. Devinez quoi ? Encore une puissance de 2 !

1’048’576 = 220

Pour Exceller, vous devez savoir ceci : Excel peut utiliser une autre manière d’écrire les références !

Je dois vous avertir, le monde entier a décidé que les références s’utilisaient comme je vous l’ai décrit ci-dessus. Toutefois, une simple case à cocher dans les options d’Excel et vous entrez dans un autre monde. Celles des références ultra lisibles !

Au lieu d’écrire ZZ28, vous pourriez écrire L28C702.

En quoi est-ce plus simple ? Cette référence indique « L » (comme Lignes) suivi du numéro de la ligne puis « C » (comme Colonnes) suivi du numéro de la colonne.

C’est immédiatement plus clair.

Si vous souhaitez activer (ou désactiver) cette option, procédez comme suit :

Changer l'affichage des références - Étape 1 - Menu fichier - Option
Menu fichier – Options

Ensuite, cliquez sur le menu « Formules » et activez (ou désactivez) la case « Style de référence L1C1 ».

Activer le style de référence L1C1 dans le menu Formules
Menu Formules – Style de référence L1C1

Votre feuille de calcul prendre alors la forme suivante :

Affichage d'une feuille Excel avec le style L1C1
Feuille de calcul – Style L1C1

Rassurez-vous tout de suite, toutes les formules de votre feuille de calcul sont automatiquement converties dans le bon format. C’est un simple remplacement d’une lettre par son rang.

En revanche, pour les plus habitués d’entre vous, cela peut sembler difficile de s’y retrouver.

Pour la suite, je n’utiliserai que la manière la plus répandue d’utiliser les références, à savoir avec les lettres et les nombres.

Apprendre à automatiser les opérations mathématiques simples avec Excel

Maintenant que vous avez bien compris la notion de référence d’une cellule, le véritable usage d’Excel va pouvoir commencer.

Nous disposons de cellules dans lesquelles nous allons inscrire des valeurs, ou des opérations.

Lorsque, dans une cellule, vous souhaitez faire un calcul utilisant la valeur de la cellule A1, vous allez indiquer à Excel, non pas la valeur elle-même, mais le fait qu’Excel doive récupérer la valeur contenue dans A1.

Ainsi, dès que vous modifierez la valeur de la cellule A1, toutes les cellules qui font référence à A1 se mettront automatiquement à jour.

Cependant, Excel ne peut pas savoir si vous écrivez A1 en référence à la cellule A1 ou juste parce que vous souhaitez écrire le texte « A1″…

C’est pourquoi il faut l’indiquer ! Ceci se fait en commençant par écrire le simple symbole « égal » : ‘=’.

Pour dire à Excel que vous souhaitez référencer le contenu de la cellule A1, il faut commencer par écrire le symbole égal ‘=’

Exemple 1 : L’addition et la multiplication

Prenons un exemple avec une facture extrêmement simplifiée.

Vous disposez d’un petit tableau avec les colonnes suivantes :

  • L’intitulé d’un article
  • Le prix unitaire de l’article
  • La quantité
  • Le prix total par article acheté
  • Le prix total de la facture
Exemple opération simple pour apprendre Excel

L’objectif à atteindre est de pouvoir modifier les valeurs du prix unitaire ou de la quantité et d’avoir automatiquement les résultats qui se mettent à jour.

Au lieu de raisonner avec les valeurs (1×299, 6×49, puis la somme des valeurs obtenues, il faut raisonner avec les références des cellules.

Référence celluleRésultat attenduFormule correspondante
D2La multiplication de B2 par C2=B2*C2
D3La multiplication de B3 par C3=B3*C3
D5La somme de D2 et de D3=D2+D3

Vous pouvez également observer que pendant que vous écrivez les formules, le texte de votre formule change de couleur. Les couleurs correspondent aux cellules auxquelles elles font références.

Apprendre Excel - Illustration d'une formule Excel avec la coloration des références d'une formule

Si vous avez bien retranscrit mon exemple, vous devriez avoir le résultat ci-dessous. J’aimerais d’ailleurs en profiter pour attirer votre attention sur 3 éléments :

  1. Premièrement, c’est la position actuelle de mon curseur. Il se trouve en D5, sur le total de la facture. C’est la zone de nom.
  2. Deuxièmement, on peut voir le contenu de cette cellule. Et c’est une formule que l’on voit, et non le résultat ! C’est la barre de formule.
  3. Troisièmement, sur la feuille de calcul, on voit le résultat.

A partir de maintenant, si vous modifiez le prix ou la quantité, vous verrez les résultats se mettre à jour automatiquement.

Exemple 2 : Utiliser votre première fonction – SOMME()

Dans mon exemple, c’était facile, il n’y avait que 2 lignes à ajouter pour obtenir le total de la facture. Si vous en aviez 30, ce serait un peu plus pénible de saisir =D2+D3+D4+…+D31.

C’est pour cela qu’Excel possède de nombreuses fonctions prédéfinies.

Je vous propose de découvrir la fonction SOMME().

Avant d’aller plus loin, je dois maintenant vous présenter la notion de PLAGE (sans le soleil et les serviettes… 🙂 ).

Apprendre Excel : Les PLAGES

Une PLAGE correspond à un ensemble de cellules. Ce sera une manière simplifiée d’indiquer à Excel que nous avons besoin de l’ensemble des cellules contenues dans un rectangle en particulier.

Regardez la sélection ci-dessous :

Apprendre Excel : Les plages

J’ai volontairement vidé tout le contenu des cellules pour mettre l’accent sur la zone sélectionnée. Et c’est également volontairement un rectangle composée de plusieurs colonnes et plusieurs lignes.

Nous aurons besoin des cellules situées en haut à gauche (ici B2) et en bas à droite (ici D5). Ce sont elles qui détermineront notre plage.

L’écriture à utiliser sera : B2:D5.

Excel comprendra qu’il faut prendre en compte toutes les cellules contenues dans ce rectangle.

Ce rectangle peut posséder uniquement une colonne : B2:B5 (en rouge). Ou encore, ne posséder qu’une seule ligne B2:D2 (en bleue).

Utiliser la fonction SOMME()

Vous l’avez deviné, nous allons pouvoir fournir une plage à la fonction SOMME() pour terminer notre exemple.

Avez-vous trouvé comment décrire cette plage ?

Les articles vont de la ligne 2 à la ligne 31, et les cellules à additionner se trouvent dans la colonne D. La plage se décrira ainsi : D2:D31.

Excel pourra alors interpréter cette plage et exécuter la fonction SOMME() sur celle-ci. Comme son nom l’indique, la fonction SOMME additionnera toutes les cellules et vous présentera le résultat.

Petite astuce pour avoir tout lu !

Pour vous remercier d’avoir tout lu, je vous présente une fonction que même les assez bons ne connaissent pas !

Vous avez pu voir sur cette capture qu’à moins d’être sur la cellule, rien n’indique que c’est le résultat d’une opération avec d’autres cellules.

Une fonctionnalité d’Excel permet de montrer immédiatement toutes les formules présentes dans toutes les cellules du classeur.

Ainsi, au lieu de voir le résultat du calcul, vous verrez les formules !

  1. Menu Formules
  2. Afficher les formules (à désactiver ensuite)

Et à ce moment, toutes les formules se révèlent ! En en cliquant sur les cellules (comme en 3), vous verrez immédiatement les cellules concernées par la formule.

N’oubliez pas de cliquer à nouveau sur cette option pour que le résultat revienne.

Pour une raison encore inconnue, les largeurs des cellules augmentent soudainement. Pas d’inquiétude, lorsque l’affichage des formules est désactivé, les largeurs reviennent à leur état originel.

Avez-vous appris à mieux utiliser Excel ?

Faites-le moi savoir dans les commentaires, partagez cet article sur vos réseaux, mettez-vous en contact avec moi sur LinkedIn ou Facebook.

Merci !

Calendrier Excel 2020

Calendrier Excel 2020

Je vous ai préparé LE calendrier Excel pour 2020. Mais pas seulement ! C’est un calendrier Excel pour 2020, 2021 et toutes les années que vous souhaitez ! En plus, j’ai décidé de me démener pour y ajouter les jours fériés automatiquement ! Après la publication de la première version, j’ai mis à jour le fichier pour également disposer des numéros de semaines, selon une simple liste de choix !

J’ai encore amélioré ce calendrier. Il s’appelle version 2021 est se trouve ici.

Et comme je pense au plus grand nombre d’entre vous, mon calendrier Excel contient les jours fériés pour la Belgique, le Canada, la France et deux cantons Suisse : Genève et Vaud.

Calendrier Excel 2020 - Champs configurables
Tous les jours fériés pour toujours et pour les pays suivants : Belgique, Canada, France, Suisse (GE+VD)

Au départ, je ne voulais qu’un calendrier Excel pour 2020…

Et puis, mes souvenirs m’ont rappelé que j’ai toujours souhaité trouver les formules pour calculer automatiquement les dates des jours fériés. J’ai alors commencé ma quête des formules pour calculer les dates automatiquement.

J’ai dû me demander comment chacune des dates était déterminée. Évidemment, les dates avec un numéro fixe sont faciles à rendre variables selon l’année (1er janvier, 1er mai, etc.). Finalement, il n’y a que la date de Pâques qui est particulière à calculer.

J’ai ensuite pensé à VOUS. Le trafic de ce site montre une provenance de nombreux autres pays francophones, en particulier du Canada, de la Belgique et de la Suisse. Je me suis alors mis en quête des formules pour chacun de ces pays.

Certaines dates sont plus difficiles à calculer. Par exemple, en Suisse, dans le canton de Genève, le jeûne genevois se fête le premier jeudi suivant le premier dimanche de septembre. Toujours en Suisse, dans le canton de Vaud, le lundi du jeûne fédéral est fixé au lundi suivant le 3e dimanche de septembre. Enfin, au Canada, la fête du travail est toujours le premier lundi du mois de septembre et l’action de grâce (Thanksgiving) est fixé au 2e lundi du mois d’octobre.

Un calendrier Excel 2020 correctement mis en forme

Il ne suffisait pas de calculer les dates des jours fériés, je souhaitais en plus disposer d’une mise en page qui ressemble à un calendrier. Mais pour cela, il faut pouvoir mettre en forme les bons jours fériés selon le pays. Et cela a passablement compliqué la chose !

Il fallait trouver une formule de mise en forme conditionnelle qui est elle-même conditionnée au choix du pays !

Après quelques tâtonnements, j’ai combiné à différents endroits les fonctions INDIRECT(), ADRESSE(), RECHERCHEV() et une formule matricielle !

À cela s’ajoute la gestion de la mise en forme conditionnelle des samedis, des dimanches et bien sûr des jours fériés ! Sans oublier la gestion du 29 février ! Étant légèrement perfectionniste, je souhaitais de plus que le quadrillage soit correctement mis en forme. Ce qui est le cas.

Un calendrier Excel 2020 imprimable…

Il ne s’agissait pas seulement de faire un joli tableau, il fallait bien le rendre imprimable !

Heureusement qu’avec mon article pour maîtriser la zone d’impression d’Excel, j’ai tout ce qu’il faut pour préparer le fichier à l’impression !

Et j’ai bien prévu de n’avoir que la zone du calendrier qui est imprimée.

Avec ou sans les numéros de semaine, votre calendrier Excel personnalisable

Excel demande souvent pas mal de créativité ! Ajouter les numéros de semaine au calendrier n’est pas très compliqué en soi. La fonction NO.SEMAINE.INT() vous donne tout de suite cette information. En revanche, dans la mesure où dans mon cas tout est dynamique, il fallait rendre conditionnel l’affichage du numéro de semaine.

Et puis, j’utilisais initialement la présence ou non d’un texte (le nom du jour férié) dans la cellule des jours pour adapter la mise en forme. L’ajout d’un numéro de semaine a donc légèrement compliqué la donne ! (voici un article pour découvrir une partie du potentiel des mises en forme conditionnelles d’Excel)

Finalement, c’est fait. Il ne vous reste plus qu’à récupérer la version du calendrier Excel actualisée (le lien se trouve en bas de la page) et me dire ce que vous en pensez en commentaire.

Liste des jours fériés pour 2020 (Belgique, Canada, France et Suisse – GE et VD)

J’ai trouvé les dates suivantes pour la Belgique, le Canada, la France et les deux cantons Suisse de Genève et de Vaud.

PaysDateJour férié
Belgiquemercredi, 1 janvier 2020Jour de l’an
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
vendredi, 1 mai 2020Fête du travail
vendredi, 8 mai 2020Armistice 39/45
jeudi, 21 mai 2020Ascension
dimanche, 31 mai 2020Pentecôte
lundi, 1 juin 2020Lundi de Pentecôte
mardi, 21 juillet 2020Fête Nationale
samedi, 15 août 2020Assomption
dimanche, 1 novembre 2020Toussaint
mercredi, 11 novembre 2020Armistice 14/18
dimanche, 15 novembre 2020Fête du Roi
vendredi, 25 décembre 2020Noël
Canadamercredi, 1 janvier 2020Jour de l’an
vendredi, 10 avril 2020Vendredi saint
lundi, 13 avril 2020Lundi de Pâques
lundi, 18 mai 2020Journée nationale des patriotes
mercredi, 1 juillet 2020Fête du Canada
lundi, 7 septembre 2020Fête du travail
lundi, 12 octobre 2020Action de grâce
vendredi, 25 décembre 2020Fête de Noël
Francemercredi, 1 janvier 2020Jour de l’an
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
vendredi, 1 mai 2020Fête du travail
vendredi, 8 mai 2020Armistice 39/45
jeudi, 21 mai 2020Ascension
dimanche, 31 mai 2020Pentecôte
lundi, 1 juin 2020Lundi de Pentecôte
mardi, 14 juillet 2020Fête Nationale
samedi, 15 août 2020Assomption
dimanche, 1 novembre 2020Toussaint
mercredi, 11 novembre 2020Armistice 14/18
vendredi, 25 décembre 2020Noël
Suisse GEmercredi, 1 janvier 2020Nouvel an
vendredi, 10 avril 2020Vendredi Saint
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
jeudi, 21 mai 2020Ascension
lundi, 1 juin 2020Pentecôte
samedi, 1 août 2020Fête Nationale
jeudi, 10 septembre 2020Jeûne genevois
vendredi, 25 décembre 2020Noël
jeudi, 31 décembre 2020Restauration de la république
Suisse VDmercredi, 1 janvier 2020Nouvel An
jeudi, 2 janvier 2020Nouvel An (et oui, 2 jours !)
vendredi, 10 avril 2020Vendredi Saint
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
jeudi, 21 mai 2020Jeudi de l’Ascension
lundi, 1 juin 2020Lundi de Pentecôte
samedi, 1 août 2020Fête Nationale
dimanche, 20 septembre 2020Jeûne fédéral
lundi, 21 septembre 2020Lundi du Jeûne
vendredi, 25 décembre 2020Noël

Le fichier Excel en téléchargement ci-dessous

Une fois le paragraphe débloqué, le lien de téléchargement sera dans le texte ci-dessous. N’oubliez pas de cliquer dessus pour récupérer le fichier.

​Entrez votre email pour ​débloquer l'accès au fichier Excel contenant le ​​calendrier

​​vous recevez en plus mon e-book gratuit

Les 15 fonctionnalités que SEULS les vrais experts de Microsoft Office connaissent !

Si vous n'avez pas reçu l'email d'ici quelques minutes, pensez à vérifier votre dossier "spam" ou "courrier indésirable", les filtres anti-spams sont parfois un peu trop sensibles !

Utilisateurs de GMail : pensez à vérifier votre dossier "Promotion" pour les mêmes raisons. 

Comme promis, voici le lien vers le fichier Excel contenant le calendrier 2020 et toutes les années que vous souhaitez, ainsi que les jours fériés.

Et si vous souhaitez que j’ajoute d’autres pays, que ce fichier vous a plu, dites-moi tout dans les commentaires !

Utiliser un fichier CSV avec Excel

Utiliser un fichier CSV avec Excel

Le fichier CSV et Excel sont des amis mais parfois, ils ont du mal à s’entendre. Je vous propose dans cet article de rétablir la meilleure entente entre les deux. Commençons par la définition : un fichier CSV signifie « Comma Separated Values », des valeurs séparées par des virgules. Or, parfois, le fichier CSV contient des valeurs séparées par … des points-virgules !

Bref histoire du format de fichier CSV

Tout d’abord, c’est un format qui est proposé comme format standard pour Internet par l’IETF (Internet Engineering Task Force) à travers la RFC 4180 puis mise à jour avec la RFC 7111.

Donc, si c’est un standard, tout le monde devrait pouvoir lire et écrire de la même façon, non ? Malheureusement, entre un standard déclaré et son application, il y a souvent des écarts importants… Et vous pouvez le constater à chaque fois que vous ouvrez un fichier CSV avec Excel, c’est toujours une surprise. Enfin, avant d’avoir lu cet article… car après, vous maîtriserez parfaitement la question !

Prérequis pour lire et écrire un fichier CSV

Les fichiers CSV sont des fichiers texte. Premièrement, ce sont des fichiers texte que n’importe quel éditeur de fichiers peut lire et écrire. Ils n’ont pas besoin d’un outil propriétaire (payant) pour être lus. Deuxièmement, l’idée de ces fichiers est de pouvoir échanger des données entre deux organisations (cela peut être deux personnes, deux programmes, un mélange des deux).

L’intérêt d’un standard, c’est que chacun sait à quoi s’attendre et donc comment lire un fichier reçu. Or, plusieurs problèmes viennent troubler l’ordre a été établi. Nous allons les passer en revue.

Le format des nombres

Dans le monde anglo-saxon, le séparateur de décimales est le point, alors qu’en Europe (sauf pour les britanniques), c’est la virgule. Cela va donc commencer à poser un problème pour un fichier CSV qui veut dire « Comma Separated Values »… vos valeurs à virgule risquent de se faire découper en deux !

Il serait donc bienvenu de disposer d’un autre séparateur que la virgule…

De plus, la virgule est utilisée par les anglo-saxons comme un séparateur de milliers. Cela ne facilite pas la lecture des valeurs.

Le format des dates

Pour continuer à simplifier la situation (!), la manière dont les dates sont écrites dans un fichier risque également de subir une interprétation. La même différence entre le monde anglo-saxon et les autres existe pour le format des dates.

Ainsi, une date inscrite 8/12/11 correspond-elle au 8 décembre 2011 ou au 12 août 2011 ? Rien n’empêche d’imaginer le 11 décembre 2008… Il est donc vital de comprendre comment les dates sont inscrites dans un fichier pour pouvoir les interpréter de la bonne manière !

Le format du texte

Pour des raisons techniques, les données de type texte apportent leur lot de complexité également. En dehors de l’encodage dont je vous parle plus loin, il faut traiter le problème du caractère apostrophe ‘ et du guillemet « .

Le premier (apostrophe) pose des problèmes à nos amis les développeurs. En effet, les apostrophes sont utilisées dans les appels aux bases de données et génèrent donc des difficultés. Le moyen de contourner ce problème consiste à placer des guillemets  » autour du texte contenant une apostrophe ‘.

Là aussi, pour des raisons que je ne détaillerai pas, c’est parfois l’inverse qui est utilisé ! Des guillemets à la place d’une apostrophe et vice-versa.

Les séparateurs de valeurs

Normalement, le nom du fichier indique clairement que les valeurs de notre fichier CSV devraient être séparées par des virgules. Faisons un petit récapitulatif de la situation :

  1. Les nombres décimaux utilisent parfois des virgules, parfois des points
  2. Les dates ont des ordres variables d’affichage du jour, du mois et de l’année
  3. Les textes sont parfois encerclés de guillemets, parfois d’apostrophes
  4. Les textes voient parfois leurs apostrophes remplacées par des guillemets

Ces raisons font que différentes méthodes existent pour séparer les valeurs dans un fichier. Il s’agit parfois de point-virgule, parfois le caractère tabulation, parfois ce sera un autre caractère (#, |, …)… l’objectif étant d’éviter qu’il y ait des collusions entre ce caractère et les données.

Et pour terminer, je vous laisse le meilleur pour le prochain paragraphe : l’encodage du fichier !

L’encodage du fichier

Comme la situation était déjà simple, le monde des technologies a ajouté un autre problème… l’encodage.

Je n’aime pas devoir rentrer dans les considérations si techniques. Mais, dans le cas présent, c’est essentiel pour pouvoir comprendre pourquoi, dans certains cas, un import de fichier CSV dans Excel ne fonctionne pas…

Lorsque vous écrivez un fichier texte sur votre ordinateur, le programme d’édition utilise un certain ensemble de caractères (un charset – contraction de « characters » et « set »). Ainsi, lorsque j’utilise le 8e caractère de l’ensemble, cela correspond à une lettre, un chiffre ou un caractère spécial en particulier. Il se trouve que la technologie s’est d’abord développée dans le monde anglo-saxon. Ces derniers n’ont pas prévu dès le départ que le système développé allait autant s’étendre. Ils n’ont donc pas prévu d’intégrer tous les alphabets ou idéogrammes du monde.

L’internationalisation des échanges a vu naître de plus en plus de charsets différents ! Et biensûr, ils ne sont pas compatibles entre eux ! Vous devez donc savoir avec qui vous traitez…

Vos paramètres régionaux

Eh oui, les paramètres système de votre ordinateur vont avoir un impact sur la manière dont un fichier CSV sera lu par Excel. Il est fréquent de voir ce problème lors d’échange de fichiers entre collègues de la même organisation mais ne travaillant pas dans la même langue ou la même région du monde (et ayant donc des configurations d’ordinateur différentes).

Ces paramètres se trouvent dans le panneau de configuration (ancienne version d’affichage – même sur Windows 10 !). Vous trouverez les paramètres qui sont les vôtres pour votre ordinateur.

Vous pouvez également aller explorer les autres onglets (en 4) comme présenté ci-dessous.

Fichier CSV Excel - Paramètres régionaux
Fichier CSV Excel – Paramètres régionaux

Importer un fichier CSV avec Excel

Même si l’explorateur de fichiers associe les fichiers .csv avec Excel, je ne vous recommande pas d’ouvrir directement un fichier csv (en double-cliquant dessus). La probabilité que le fichier ne s’ouvre pas convenablement est très (trop) élevée. Au final, vous risquez plus de frustrations que de gagner réellement du temps.

Mon meilleur ami pour ouvrir les fichiers CSV est Notepad++. C’est un éditeur de texte très puissant et totalement libre d’utilisation. Il permet d’ouvrir les fichiers texte volumineux sans planter ! Et surtout, il sait :

  1. Reconnaître automatiquement l’encodage des fichiers
  2. Convertir un encodage très simplement
  3. Remplacer des caractères par un autre à toute vitesse

Méthode n°1 : Préparer votre fichier avec Notepad++ avant d’ouvrir votre fichier CSV dans Excel

Dans mon tuto sur comment réussir son premier tableau croisé dynamique, j’utilise un exemple de données provenant du site du gouvernement français : https://www.data.gouv.fr/fr/datasets/r/cc16163c-aca0-4977-97da-8ce592f78de1.

Ce fichier CSV n’a pas un encodage qu’Excel comprend immédiatement (vous pouvez tester, vous verrez !).

Alors que lorsque vous l’ouvrez avec Notepad++, cela fonctionnera parfaitement. Vous pourrez alors aller dans le menu « Encodage » (en 1) et visualiser l’encodage actuel (en 2).

Ensuite, vous pourrez convertir ce fichier dans un encodage qui sera immédiatement reconnu par Excel, à savoir ANSI. Cet encodage est celui par défaut pour Windows.

Fichier CSV Excel - Changer encodage Notepad++
Fichier CSV Excel – Changer encodage Notepad++

Vous pourrez ensuite travailler depuis Excel pour terminer la conversion. Mais au moins, à ce stade, les caractères spéciaux du type é ne seront plus présents !

Avant de quitter Notepad++, vous devez:

  1. Vérifier que l’encodage du fichier a bien changé (cf. image ci-dessous)
  2. Enregistrer votre fichier avant de fermer ! (si la disquette en haut à gauche est rouge, elle montre que l’enregistrement n’a pas été fait)
Fichier CSV Excel - ANSI
Fichier CSV Excel – Encodé ANSI

Méthode n°2 : Utiliser l’import de données externes d’un fichier CSV dans Excel

Étape n°1 : Importer le fichier CSV

Il y a un avantage à utiliser cette technique : Excel peut cette fois détecter l’encodage correctement !

Fichier CSV Excel - Importer fichier texte
Fichier CSV Excel – Importer fichier texte

Vous pouvez ensuite sélectionner votre fichier CSV à importer. L’écran suivant apparaît alors.

  1. Type de données d’origine
    • Délimité : Vous disposez de caractères de séparation, par défaut Excel reconnaît les virgules.
    • Largeur fixe : Parfois, les fichiers CSV sont construits avec le même nombre de caractères à chaque ligne. Les lignes qui ont plus ou moins de caractères sont comblées avec des espaces.
  2. Origine du fichier.
    • C’est l’encodage. Ici, comme avec Notepad++, Excel reconnaît que le contenu est en UTF-8 (et non ANSI). Les caractères spéciaux n’apparaissent pas.
    • Toutefois, il est possible qu’Excel se trompe. Vous devez alors choisir par vous-même le bon encodage dans la liste déroulante.
  3. Commencer l’importation à la ligne xx.
    • Il est possible de ne pas importer la ligne d’en-tête par exemple. Ou parfois, les fichiers CSV ont des lignes de description qui ne sont pas utiles à l’importation.
  4. Mes données ont des en-têtes.
    • Cette case permet d’indiquer à Excel si la première ligne représente les noms des colonnes.
  5. Vous avez une prévisualisation de vos données.
    • Cela permet de vérifier si les paramètres sont adaptés.
Assistant importation données CSV - Étape 1
Assistant importation données CSV – Étape 1

Vous pouvez alors cliquer sur le bouton « Suivant > » et continuer la configuration de votre import.

Étape n°2 : Configurer les séparateurs de votre fichier CSV

Assistant importation données CSV - Étape 2
Assistant importation données CSV – Étape 2

À ce stade, par défaut, la seule case cochée est « Tabulations ». Vous aurez certainement besoin de cocher soit « Virgule » soit « Point-virgule ». Les autres options sont explicites. Dès que vous aurez choisi le bon séparateur, l’aperçu se met à jour. Vous pourrez évaluer la pertinence de votre choix.

Vous pouvez alors passer à l’étape 3.

Étape n°3 : Configurer le choix et le format de vos colonnes

Assistant importation données CSV - Étape 3
Assistant importation données CSV – Étape 3

À ce moment, 3 parties très, très, très importantes se présentent à vous (j’ai déjà dit que c’était important ?)

  1. Vous pouvez déterminer le type de chacune des colonnes importées à ce moment précis.
    • Vous pouvez notamment adapter le format des dates. JMA signifie Jour Mois Année. Dans le menu déroulant, vous trouverez les 6 combinaisons possibles. À vous de choisir la bonne !
  2. Vous pouvez déterminer les séparateurs de décimales et de milliers (en cliquant sur « Avancé… »).
  3. Vous pouvez sélectionner chaque colonne les unes après les autres pour déterminer leur type.

N’oubliez surtout pas l’option « Colonne non distribuée ». Cette option permet de ne pas importer les données de la colonne en question. Cela peut s’avérer très utile de n’importer que les données nécessaires.

Enfin, lorsque vous avez terminé, vous pouvez cliquer sur le bouton « Terminer » et utiliser vos données dans Excel.

Considérations (avantages/inconvénients) de cette méthode

Selon les cas, un avantage pour les uns peut être un inconvénient pour lesn autres, je préfère donc lister les considérations à avoir avec cette méthode.

  • Votre fichier CSV reste intact. Vous n’importez que les données.
  • Vous pouvez mettre à jour votre fichier CSV et Excel pourra actualiser l’import des données (en cliquant sur Actualiser). C’est à dire que si le nombre de lignes varie, Excel s’adaptera.
  • Si vous déplacez le fichier CSV ou le fichier Excel, vous aurez un message d’erreur à chaque rafraîchissement des données.
  • Plusieurs personnes peuvent travailler à partir du même fichier CSV.

Méthode n°3 : Récupérer et transformer votre fichier CSV dans Excel

Pour utiliser cette méthode, PowerQuery est nécessaire. Ce composant est présent nativement à partir d’Excel 2016. Pour les Excel 2013 ou Excel 2010 Professionnal Plus (+Software Assurance), Microsoft propose de télécharger le composant depuis son site.

Étape n°1 : Débuter l’import

C’est certainement la méthode la plus complète, et par conséquent, la plus complexe également.

Importer un fichier CSV dans Excel - Récupérer et transformer
Importer un fichier CSV dans Excel – Récupérer et transformer

L’écran suivant apparaît avec les options suivantes :

  1. Origine du fichier
    • Comme nous l’avons déjà vu, c’est l’encodage.
    • Ce qui est surprenant, c’est que l’interface de PowerQuery ne reconnaît pas le bon encodage automatiquement !
    • C’est donc à vous de sélectionner le bon encodage ! La liste est longue, mais je vous recommande de tester parmi les encodages les plus fréquemment utilisés que sont :
      • 1252 – Europe de l’ouest (Windows)
      • 1200 – Unicode
      • 65001 – Unicode UTF-8
      • >>Ne me demandez pas pourquoi, les valeurs ne sont pas triées selon les valeurs numériques, mais selon le nom de l’encodage…
  2. Délimiteur
    • C’est le séparateur qui est utilisé dans votre fichier.

À ce stade, au lieu de tout de suite cliquer sur le bouton « Charger », je vous invite à découvrir un peu plus PowerQuery en cliquant sur « Modifier ».

Fichier CSV Excel - Configuration PowerQuery
Fichier CSV Excel – Configuration PowerQuery

Étape n°2 : Modifier la requête d’importation

Dès que vous avez sélectionné le bon encodage et cliqué sur « Modifier », vous vous retrouvez face à l’affichage suivant.

Fichier CSV Excel - Interface PowerQuery
Fichier CSV Excel – Interface PowerQuery

Je pourrais vous détailler à ce stade toute l’interface, cela serait un peu long. Toutefois, je peux vous informer sur les quelques possibilités que vous pourrez explorer. Quelques exemples :

  • Changer l’ordre des colonnes ou en supprimer
  • Filtrer automatiquement des lignes, autrement dit ne pas les importer
  • Calculer de nouvelles colonnes sur la base des autres colonnes
  • Changer les types des données
  • etc.

Lorsque vous aurez terminé vos paramétrages, vous n’aurez qu’à cliquer sur « Enregistrer et charger » pour que les données se chargent sur votre feuille Excel.

Considérations (avantages/inconvénients) de cette méthode

C’est sans conteste la méthode la plus complète pour importer des données CSV dans Excel. C’est également la méthode la plus complexe. Selon vos besoins, vous pourrez déterminer la solution la plus adaptée.

Comme précédemment, vous aurez les considérations suivantes :

  • Votre fichier CSV reste intact. Vous n’importez que les données.
  • Vous pouvez mettre à jour votre fichier CSV et Excel pourra actualiser l’import des données (en cliquant sur Actualiser). C’est-à-dire que si le nombre de lignes varie, Excel s’adaptera.
  • Si vous déplacez le fichier CSV ou le fichier Excel, vous aurez un message d’erreur à chaque rafraîchissement des données.
  • Plusieurs personnes peuvent travailler à partir du même fichier CSV.

Dernière étape

Avez-vous encore des questions ? Est-ce que cet article vous a plu ? J’attends votre feed-back dans les commentaires !

Merci !

>