Étiquette : Excel

Additionner des heures avec Excel – Tout comprendre, tout maîtriser !

Additionner des heures avec Excel – Tout comprendre, tout maîtriser !

Comme mentionné dans mon article sur comment calculer un âge avec Excel, la gestion du temps peut sembler peu compréhensible. Or, une fois qu’on SAIT, additionner et soustraire des heures ou des minutes avec Excel devient un jeu d’enfants !

Excel ne sait pas additionner les heures !

Si vous êtes ici, c’est que vous en avez vous-même fait l’expérience, et dans le doute, faites le test suivant :

  1. Saisissez 12:00 dans une cellule
  2. Saisissez 12:00 dans une autre cellule
  3. Réalisez l’addition des deux cellules dans une troisième
  4. Observez le résultat : 00:00 !
Additionner heures Excel - Resultat étrange
Additionner heures Excel – Résultat étrange

Premier pas pour résoudre cette étrangeté, je vous invite à changer le format de présentation des données de votre cellule.

  1. Dans le ruban Accueil, bloc Nombre, sélectionnez le menu déroulant “Autres formats numériques…”
  2. Sur l’onglet “Nombre”, sélectionnez “Personnalisée”
  3. Dans la zone de saisie “Type” saisissez [h]:mm
Additionner heures Excel - Changer format cellule
Additionner heures Excel – Changer format cellule – Étape 1
Changer format cellule
Changer format cellule – Étape 2
Changer format cellule
Changer format cellule – Étape 3

Avec cette configuration, on retrouve le résultat attendu soit : 24:00.

Sur le principe, on pourrait s’arrêter là, mais je ne trouve pas cela très convainquant… Et vous ? Vous ne vous dîtes pas : Mais pourquoi ?

Comment Excel gère les heures ? Comprendre pour additionner, soustraire des heures, des minutes à souhait !

Je vous l’ai déjà dit et j’insiste, Excel ne comprend pas les heures ! Tout est nombre ou texte pour Excel. Le reste n’est que mise en forme pour vos yeux.

D’ailleurs, vous allez découvrir que le 0 janvier 1900 existe chez Microsoft !

Je vous propose de commencer par un petit exercice pratique.

Comprendre et apprendre en pratiquant – Ajouter des heures sur Excel

Préparer votre classeur

  1. Ouvrez un nouveau classeur vide
  2. Saisissez dans la cellule de votre choix (D2 dans mon cas) une heure 13:22 par exemple
  3. Dans la cellule de droite, saisissez =votre_référence (=D2 dans mon cas)
  4. Dans la cellule encore à droit, répétez la même chose avec la même référence (=D2 dans mon cas)
  5. Sélectionnez ces trois cellules et étendez-les sur trois cellules vers le bas (cf. Le curseur Excel : Sous toutes ses formes et fonctions)

Vous devriez vous retrouvez avec cet affichage :

Additionner heures Excel - Cas pratique
Additionner heures Excel – Cas pratique

Ensuite, dans la cellule sous chaque colonne, calculons la somme des lignes du dessus.

=SOMME(D2:D5) pour la première colonne

Il ne vous reste qu’à étendre cette formule sur la droite pour que celle-ci s’adapte aux colonnes d’à côté. Regardez cette partie de l’article pour apprendre à étendre une fonction avec le curseur.

Étudier les données

Je vous propose maintenant de changer le format des données de la 2e colonne (Colonne E pour moi) ainsi que de celui de la 3e colonne (Colonne F pour moi).

Additionner heures Excel - Changer format cellule
Additionner heures Excel – Changer format cellule

Vous vous souvenez, je vous ai proposé de changer le format de la cellule au début de cet article. Vous allez faire la même opération, mais cette fois, voici le format que vous allez saisir pour la 2e colonne :

jj.mm.aaaa hh:mm:ss

Et pour la dernière colonne, mettez simplement “Standard” (c’est le premier de la liste).

Vous devriez avoir l’affichage suivant :

Additionner heures Excel - Déchiffrer les formats
Additionner heures Excel – Déchiffrer les formats

Avez-vous remarqué la dernière ligne, celle des totaux ?

11:2802.01.1900 11:28:002,47777778
Ligne de la somme des valeurs

La même donnée sous trois formes différentes !

En effet, Excel ne fait que présenter les données sous une forme de date ou d’heure. Lorsque vous avez saisi votre première valeur 13:22, vous n’avez pas renseigné de date particulière, celle-ci a été fixée automatiquement au “0 janvier 1900”.

Vous avez bien lu : Le jour zéro existe pour Excel et c’est le 0 janvier 1900 !

Comprendre pour maîtriser la manipulation des heures, des minutes ou des dates avec Excel

Le véritable zéro a été inventé par les indiens vers le IIIe siècle après J.-C.

https://www.lumni.fr/article/mathematiques-histoire-du-zero

Ici, Microsoft a fait le choix technique d’implémenter les dates de cette manière. Je ne me permettrai pas de commentaires tant ce choix a des impacts que je ne peux évaluer.

Une date n’est finalement qu’une apparence visuelle. Au passage, je vous informe que dans le monde Excel, les dates, en plus d’avoir un début, ont une fin !

Eh oui, la fin du monde datée se situe au 31 décembre 9999 à 23:59:59.

Maintenant, que faut-il savoir pour VOUS et MAINTENANT pour vos opérations avec les heures ?

Même si vous ne saisissez qu’une donnée sous la forme d’une heure, Excel va considérer cette information comme étant une date. Et, en plus, Excel traitera cette information comme étant une valeur numérique.

Les nombres entiers correspondent aux jours.

Une heure vaut numériquement 1 divisé par 24.

Une minute vaut 1 divisé par 24 divisé par 60. Pour la seconde, il faudra diviser encore par 60 soit 1 divisé par (24*60*60).

Lorsque vous faites n’importe quelle opération (additionner des heures, soustraire des minutes, etc.), Excel réalise l’opération sur les valeurs numériques puis convertit le résultat en la date correspondante.

Comme Excel va respecter au maximum le format des cellules qui composent votre opération, vous aurez des résultats comme l’exemple du début :

Additionner heures Excel - Comprendre l'affichage
Additionner heures Excel – Comprendre l’affichage

La somme des différentes heures donne le résultat de 11:28 ! Vous l’avez peut-être déjà repéré… Lorsque vous adaptez le format de la cellule du total au format jj.mm.aaaa hh:mm:ss vous obtenez : 02.01.1900 11:32.

C’est à dire, 2×24 + 11:32 = 59:32.

Pour obtenir cet affichage, il faut savoir gérer les options de mises en forme des cellules. Le meilleur format pour le cas présent est [h]:mm.

Quelques dernières précautions pour vos additions ou soustraction d’heures avec Excel

Vous y êtes presque, vous avez déjà fait un grand pas dans votre connaissance d’Excel. Maintenant, c’est la dernière ligne droite, c’est à partir d’ICI que vous allez plus loin. Restez bien jusqu’au bout !

Gérer les erreurs d’affichage

Excel va tant qu’il peut essayer d’afficher les données dans le format de votre choix. Or, si les données ne sont pas affichables, Excel remplira la cellule avec des #.

Cela ne veut pas dire que le calcul ne sera pas fait, ni même que cette cellule ne peut pas être utilisée comme référence pour un autre calcul…

Voyons cela à l’aide d’une illustration !

Repartons de notre heure de 13:22 en D2.

Si en C2, vous saisissez =D2-1, savez-vous quelle opérations vous venez de faire ?

Vous ne venez pas de soustraire une heure à 13:22, mais un jour ! Soit 24h !

Ainsi, arrive ce qui devait arriver :

Soustraire heures Excel - Erreur
Soustraire heures Excel – Erreur

13:22 est interprété par Excel comme étant le 0 janvier 1900, et maintenant vous essayer de soustraire un jour à cette date ! Excel ne sait pas afficher cette information sous forme de date… Il faudrait normalement soustraire (1/24) pour que cela représente une heure.

Cependant, Excel a en mémoire la valeur numérique de 13:22 (allez voir en F2 de votre tableau) et peut heureusement encore soustraire 1 à cette valeur.

En B2, vous pouvez maintenant saisir =C2+2. Arrivez-vous à deviner le résultat affiché ?

Cellule en erreur affichage - Valeur prise en compte
Cellule en erreur affichage – Valeur prise en compte

Malgré l’erreur d’affichage, Excel a bien la valeur en mémoire. En ajoutant 2, vous revenez dans une plage de valeur qu’Excel sait afficher sous forme de date. Et comme vous n’avez pas affiché le jour, chaque unité que vous ajouterez vous fera avancer le jour, mais pas l’heure !

Astuce pour réaliser sereinement toutes vos opérations mathématiques avec les heures

Êtes-vous condamné à ajouter ou soustraire des multiples de 1/24 dans tous vos calculs ? Eh bien non ! Vous pouvez tout simplement utiliser l’écriture AVEC LES GUILLEMETS “1:00” dans vos calculs.

=”1:00″ * 5

Faites l’essai par vous-même !

Excel comprend alors que vous manipulez des heures et adaptera la valeur numérique correspondante !

Opérations heures Excel - Astuce
Opérations heures Excel – Astuce

Il ne vous restera qu’à adapter le format d’affichage de la cellule en heure ou date selon votre préférence.

Évidemment, cela fonctionne également pour les additions ou les soustractions directement dans les formules.

Et les minutes dans tout cela ?

Maintenant que vous connaissez l’astuce pour ne jamais vous tromper dans les opérations avec les heures, sachez que vous pouvez tout à fait l’étendre pour les minutes ou les secondes !

Dans vos calculs, vous pouvez maintenant saisir =”00:30:15″ * 8 et obtenir exactement le résultat escompté ! Le seul point d’attention à conserver en tête est le format d’affichage de votre cellule, soit “Heure”, soit “Personnalisé” avec le type [h]:mm:ss.

Sinon, il faut saisir 1/(24*60) dans toutes vos formules, et pour les secondes 1/(24*60*60). C’est plus lourd et plus compliqué que “00:01:00” ou “00:00:01”.

Pour aller plus loin, avec les dates cette fois-ci !

Excel est pleins de secrets, l’un d’entre eux est cette fonction cachée, oui CACHÉE, qui se nomme DATEDIF(). Cette fonction permet de calculer la différence entre deux dates en nombre de jours, mois ou années.

Le plus simple est d’aller voir par vous-même sur comment calculer un âge avec Excel, vous en apprendrez beaucoup sur la gestion des dates sur Excel.

Vous reste-t-il des questions pour additionner ou soustraire des heures avec Excel ? Avez-vous apprécié cet article ?

Dans tous les cas, utilisez la partie commentaire de cet article pour tous vos échanges avec moi. Votre question et ma réponse pourront servir les autres visiteurs !

Et si vous avez apprécié cet article, alors c’est moi que cela servira de savoir qu’il vous a plu !

Je compte sur vous !

Calcul d’un âge avec Excel

Calcul d’un âge avec Excel

Le calcul de l’âge avec Excel est très utile. On prend par exemple un tableau avec toutes les dates de naissance et on calcule l’âge et la date du prochain anniversaire. Si, en plus, on indique les noms des personnes, on peut déterminer automatiquement la prochaine personne de la liste qui fêtera son anniversaire.

En explorant la notion de calcul de l’âge avec Excel, j’ai eu l’occasion d’approfondir mes connaissances sur le traitement des dates avec Excel. Et je vais m’empresser de vous les partager !

Le premier jour d’Excel

Excel ne sait gérer les dates qu’à partir du 1er janvier 1900 et jusqu’au 31 décembre 9999. D’ailleurs, Excel considère les dates comme des nombres. Vous pouvez faire la simple expérience en saisissant le chiffre 1 et en choisissant le format date pour la cellule. Excel indiquera alors le 1er janvier 1900.

Ensuite, chaque valeur indiquera le nombre de jour écoulés après ce 1er jour. La valeur 2 représentera le 2 janvier 1900 et ainsi de suite. Comme nous sommes plus de 100 ans après cette date, le 1er janvier 2000 correspond à environ 10*365 + le nombre d’années bissextiles entre 1900 et 2000 + 1. En l’occurrence, le 1er janvier 2000 correspond à la valeur 36’526. Or, si vous vérifiez la règle qui détermine les années bissextiles (multiple de 4 + pour les centenaires, le nombre de centaines doit également être un multiple de 4), vous ne devriez pas trouver le même résultat qu’Excel, mais 1 de moins !

Un bug officiel d’Excel est qu’il considère l’année 1900 comme une année bissextile !

https://docs.microsoft.com/fr-fr/office/troubleshoot/excel/determine-a-leap-year

Pour en revenir au traitement des dates par Excel et comme déjà expliqué, si vous saisissez 1 dans une cellule et que vous changez le formatage de la cellule en date, Excel vous affichera le 1/1/1900 !

Calcul âge Excel - Format date Excel
Calcul âge Excel – Format date Excel

Par conséquent, lorsque vous faites des opérations entre deux dates, Excel réalise une opération numérique entre les deux valeurs.

Calculer la différence entre deux dates

Intuitivement, on se dit qu’en soustrayant deux dates, on trouvera le nombre de jours les séparant. Or, la première fois que vous faites cela, vous vous retrouvez avec ceci :

Calcul différence entre deux dates - Format date
Calcul de la différence entre deux dates – Format date

La soustraction entre deux dates vous renvoie une date ! Comportement étrange, n’est-ce pas ?

Finalement, ce n’est qu’une question de format d’affichage de la cellule. Il vous suffit de placer le format d’affichage de la cellule au format “Standard” pour que tout rentre dans l’ordre.

Calcul différence entre deux dates - Format standard
Calcul la différence entre deux dates – Format standard

Ce calcul pourrait être la base pour calculer l’âge de quelqu’un à l’aide d’Excel. Par exemple, vous pourriez tenter de déterminer le quotient d’une division par 365.

Toutefois, je vous propose d’explorer une fonction cachée d’Excel = DATEDIFF().

DATEDIFF() – La fonction cachée d’Excel pour calculer l’âge

Vous pouvez faire le test, si vous commencez à saisir cette fonction dans la zone de formule, Excel ne vous la proposera pas.

Une fois saisie, vous n’aurez pas plus d’assistance. À vous de vous débrouiller pour trouver les paramètres, leur signification et les options.

Pourtant, Microsoft propose bien une page détaillant cette fonction. Je vous partage la même mise en garde, en théorie, cette fonction n’est pas supportée et peut retourner des résultats incorrects.

Toutefois, pour un usage basique et avec une acceptation sur les risques d’erreurs (le cas particulier est détaillé sur l’article de Microsoft), vous pouvez tout à fait utiliser cette fonction.

La syntaxe de la fonction DATEDIF()

DATEDIF(date_début,date_fin,unité)

Pour résumé cette fonction, DATEDIF() calcule un écart entre deux dates constituant votre intervalle de temps. Cet écart peut représenter un nombre de jours, de mois ou d’années selon ce que vous saisirez dans le dernier paramètre. Quelques options supplémentaires permettent de calculer des différences “spéciales”.

Par exemple, il sera possible de calculer un écart en nombre de jours entre deux dates en ignorant les valeurs des années, un nombre de mois entre deux dates en ignorant les jours et les années, ou encore un nombre de jours entre deux dates en ignorant la valeur des années.

Détail des attributs

date_début représente le début de l’intervalle. Cet attribut doit être une valeur soit au format date via DATEVAL(jj/mm/aaaa) soit une valeur de date telle qu’expliquée précédemment (=nombre de jours écoulés depuis le 1er janvier 1900).

date_fin représente la fin de l’intervalle et les mêmes contraintes de format s’appliquent.

unité détermine le type d’écart que vous souhaitez calculer. Les options sont les suivantes (et n’ont pas été traduites comme le laisse prétendre la page d’aide de Microsoft. Il faut donc bien utiliser les valeurs ci-dessous).

Les différentes options de l’attribut unité

  • “Y” : Nombre d’année complètes entre les deux dates.
  • “M” : Nombre de mois complets entre les deux dates.
  • “D” : Nombre de jours complets entre les deux dates.

Avant de vous donner les autres options possibles, j’insiste sur le mot “complet” qui est mis en gras. En effet, si vous calculez l’écart entre le 1er janvier et le 31 décembre de la même année, vous trouverez un jour de moins qu’on ne pourrait l’imaginer !

Prenez l’écart entre le 1er et le 2 janvier (1er jour et 2e jour), il n’y a qu’un jour d’écart (=2-1). Ainsi, entre le 1er jour et le 365e de la même année (pour les années qui ne sont pas bissextiles), il n’y a que 364 jours d’écart (=365-1). Il faut tenir compte de cet élément pour tous les types d’écart que vous calculerez (nombre jours, nombre de mois ou nombre d’année).

Les autres options à votre disposition sont :

  • “MD” : Différence entre les valeurs du jour des deux dates (les mois et les années sont ignorés).
  • “YM” : Différence entre les valeurs de mois des deux dates (les jours et les années sont ignorés).
  • “Yd” : Différence en nombre de jours entre les deux dates en ignorant l’année. Ce n’est pas totalement vrai… L’année de la date de fin est considérée comme étant égale à l’année de la date de début.
    • Entre le 1er janvier 2020 et le 1er mars 2020 il y aura un jour de plus qu’entre 1er janvier 2021 et le 1er mars 2022.
    • En effet, l’année 2020 étant bissextile, le résultat sera 31 + 29 = 60, alors que dans le second cas, il sera de 31 + 28 = 59.

Calculer un âge et le nombre de jours jusqu’au prochain anniversaire avec Excel

Calculer un âge avec Excel à partir d’une date de naissance est désormais à votre portée ! Plus besoin de se soucier de créer une fonction, ou pire, une macro (j’ai un avis argumenté sur la question des macros) pour déterminer l’âge de votre collègue.

Mieux, je vous propose à la fin de cet article de récupérer un modèle de fichier où vous n’aurez qu’à saisir le nom et la date de naissance pour que tout soit automatiquement calculé !

Première partie du classeur Excel de calcul de l’âge

Les deux premières colonnes sont les seules à éditer. Vous n’avez qu’à y saisir le nom et la date de naissance et tout le reste se fera tout seul.

J’ai pour habitude de placer en italique tous les champs qui sont calculés. Ainsi, je ne me demande pas très longtemps si je dois saisir une donnée ou non.

Calcul âge Excel - Première partie
Calcul âge Excel – Première partie

Une fois les donnée saisies, cette première partie facile se met à jour.

Deuxième partie du classeur Excel de calcul de l’âge

Ici, cela devient plus intéressant. Je vous propose un champ calculé avec une mise en forme conditionnelle qui vous indique d’un coup d’œil les anniversaires qui ont déjà été fêtés et en vert ceux qui sont à venir.

Afin de vous donner une chance de vous rattraper, je vous propose le nombre de jours depuis lesquels l’anniversaire a eu lieu. Si par chance c’était le week-end dernier…

Calcul âge Excel - Deuxième partie
Calcul âge Excel – Deuxième partie

Chacune de ces colonnes utilise bien sûr la fonction DATEDIF().

Troisième partie du classeur Excel de calcul de l’âge

Au cas où vous souhaitez organiser une fête particulière pour une dizaine, je vous propose également le décompte du nombre de jours de la prochaine dizaine.

Calcul âge Excel - Troisième partie
Calcul âge Excel – Troisième partie

Premier bonus : Déterminer le prochain anniversaire

Cela peut paraître simple a priori.

Mais la colonne “nombre de jours restant avant le prochain anniversaire” va s’avérer indispensable. Combinée à la colonne “Déjà passé ?”, il est possible de savoir si c’est la date de cette année ou celle de l’année prochaine qu’il faut prendre en compte dans les calculs.

Partie bonus
Calcul âge Excel – Partie bonus

De plus, pour éviter d’oublier une personne qui serait née le même jour, j’ai configuré une mise en forme conditionnelle dans le tableau pour mettre en avant les personnes ayant la même date d’anniversaire.

Partie bonus 2
Calcul âge Excel – Partie bonus

PS : J’ai changé la date de naissance de Jeanne pour illustrer la situation.

Deuxième bonus : Changer la date de référence du calcul de l’âge

D’abord, expliquons la signification de cette fonctionnalité. Parfois, nous faisons des hypothèses : “Combien de jours lui restera-t-il avant son anniversaire lorsque nous serons à Noël ?”.

C’est pour cette raison que j’ai paramétré la date de référence sur une cellule et non sur la fonction AUJOURD’HUI().

J’ai nommé la cellule Q1 en DATEDUJOUR pour pouvoir l’utiliser dans toutes les fonctions ou mises en forme conditionnelles.

Calculer âge Excel - Dans le futur ou le passé
Calculer âge Excel – Dans le futur ou le passé

Ainsi, en vous plaçant sur cette cellule, vous pouvez soit saisir une date soit simplement réaliser une addition ou soustraction du nombre de jours souhaités.

Vous pouvez maintenant télécharger le fichier Excel de calcul de l’âge.

Comment fusionner plusieurs fichiers Excel en un seul ?

Comment fusionner plusieurs fichiers Excel en un seul ?

Il est fréquent de se retrouver avec le besoin de fusionner plusieurs fichiers Excel en un seul. Par exemple, vous avez préparé un fichier modèle contenant un tableau. Puis, ce fichier est transmis à plusieurs personnes afin de collecter leurs données. Lorsque vous récupérez ces fichiers, vous allez devoir les fusionner en un seul sur Excel.

Malheureusement, vous songez sérieusement à fusionner les fichiers Excel manuellement à l’aide de nombreux copier-coller. Heureusement pour vous, vous avez fait une recherche sur Internet et avez trouvé cette page qui vous fera gagner de précieuses heures de manipulation.

Vous avez peut-être un autre objectif que fusionner plusieurs classeurs Excel en un ? Vous trouverez probablement l’objectif recherché dans le paragraphe suivant.

Déterminez votre objectif de fusion de fichiers Excel

En effet, fusionner plusieurs fichiers Excel peut viser trois objectifs très différents. Et en fonction de celui-ci, les moyens de l’atteindre ne seront pas les mêmes.

Les mots ont toute leur importance. Et pour bien parler de la même chose, je vous propose un récapitulatif des termes Excel :

  • Une feuille Excel correspond à ce que l’on nomme également les onglets.
  • Le classeur représente la totalité du fichier Excel.

Les différentes objectifs de fusion peuvent ainsi être :

  1. Cas n°1 : Fusionner les feuilles de plusieurs classeurs Excel dans un seul classeur.
    L’objectif ici est d’avoir toutes les feuilles de plusieurs classeurs réunies dans un seul classeur.
  2. Cas n°2 : Fusionner les feuilles d’un même classeur dans une seule feuille.
    L’objectif est de n’avoir qu’un seul tableau de valeurs pour les analyser, à la place d’un tableau disposant d’une structure identique mais dont les données sont réparties sur plusieurs feuilles.
  3. Cas n°3 : Fusionner une feuille en particulier de plusieurs fichiers dans une seule feuille.
    L’objectif ici est de regrouper toutes les valeurs au sein d’un même tableau. Il y a une certaine analogie avec le cas n°2, mais au lieu de se trouver dans des feuilles différentes, les données sont dans des classeurs différents. Il faudra évidemment que les données soient dans un tableau disposant de la même structure (même nombre de colonnes et mêmes noms de colonnes).

Dans ces deux cas, des questions essentielles sont à se poser pour définir la meilleure approche :

  • Est-ce une action ponctuelle ou à faire régulièrement (et à quelle fréquence) ?
  • Vais-je devoir intégrer des mises à jour de fichiers Excel déjà fusionnés ? Exceptionnellement ou régulièrement ?

Cas n°1 : Consolider les feuilles de plusieurs fichiers en un seul classeur Excel

Voici un exemple avec deux fichiers. Le premier fichier contient les données du 1er trimestre :

Fusionner plusieurs fichiers en un - Zoom 1er Trimestre
Fusionner plusieurs fichiers en un – Zoom 1er Trimestre

Puis un second avec les données du 2nd trimestre :

Fusionner plusieurs fichiers en un - Zoom 2nd Trimestre
Fusionner plusieurs fichiers en un – Zoom 2nd Trimestre

Votre objectif est d’avoir toutes ces feuilles dans le même classeur.

Étape n°0 : Décidez de votre destination

Avant de commencer une quelconque opération, vous devez déterminer où vous souhaitez retrouver le résultat de la fusion de vos fichiers Excel. Est-ce dans l’un des fichiers que vous souhaitez consolider les autres ou bien dans un classeur à part ?

Ensuite, vous devez en premier lieu ouvrir votre fichier cible puis les fichiers à fusionner les uns après les autres. Prenez le premier d’entre eux, puis continuez avec les étapes suivantes.

Étape n°1 : Sélectionnez vos feuilles à fusionner

Pour déplacer les feuilles d’un classeur Excel vers un autre classeur (et ainsi fusionner vos deux fichiers Excel) il faut cliquer sur les différentes feuilles en maintenant la touche Ctrl pressée.

Vous verrez que les feuilles sont bien sélectionnées car les noms des feuilles sont en gras et toutes sur fond blanc comme ceci :

Cas 1 - Sélection des feuilles à fusionner
Cas 1 – Sélection des feuilles à fusionner

Étape n°2 : Sélectionnez “Déplacer ou copier…”

Vous pouvez maintenant relâcher la touche Ctrl puis opérer un clic droit sur n’importe quelle feuille sélectionnée :

Cas 1 - Clic droit puis Déplacer ou copier
Cas 1 – Clic droit puis Déplacer ou copier

Si vous vous rendez compte que vous avez sélectionné une feuille de trop, pressez à nouveau sur la touche Ctrl puis cliquez sur la feuille que vous souhaitez retirer. Le fond redeviendra gris.

Étape n°3 : Sélectionnez le classeur Excel cible de la fusion

La fenêtre suivante s’affiche alors et vous permet de déterminer le classeur cible.

Cas 1 - Sélection classeur Excel cible pour la fusion
Cas 1 – Sélection classeur Excel cible pour la fusion

En cliquant sur le menu déroulant, vous verrez apparaître la liste des classeurs ouverts. Vous aurez également une option supplémentaire “(nouveau classeur)”. Comme son nom l’indique, cette option permet de déplacer (ou copier) toutes les feuilles sélectionnées vers un nouveau classeur Excel.

Cas 1 - Liste des classeurs disponibles pour la fusion
Cas 1 – Liste des classeurs disponibles pour la fusion

Dans mon exemple, j’ai choisi de fusionner mes données dans le classeur Excel “1er Trimestre.xlsx”. Comme la capture d’écran le présente, vous pouvez à cet instant viser un nouveau classeur. Dans ce cas, cochez la case “Créer une copie” de l’écran suivant.

Déterminez l’emplacement de vos feuilles dans le nouveau classeur. Veillez à bien noter les intitulés que je vous ai mis en valeur sur la capture suivante.

Vous aurez à sélectionner où vous souhaitez placer vos feuilles importées dans le classeur Excel cible.

Cas 1 - Sélection de l'emplacement des feuilles pour la fusion
Cas 1 – Sélection de l’emplacement des feuilles pour la fusion

Notez que si vous ne cochez pas la case “Créer une copie”, les feuilles seront bien déplacées d’un classeur à l’autre.

Dès que vous aurez cliqué sur “OK”, vous aurez terminé la fusion de vos deux fichiers Excel.

Cas 1 - Feuilles Excel fusionnées
Cas 1 – Feuilles Excel fusionnées

Et si j’ai de nombreux fichiers Excel à fusionner ?

Je vous l’ai déjà écrit, je ne suis pas pour l’utilisation des macros à tout va dans Excel ou n’importe quel outil de la suite Microsoft 365 (nouveau nom de la suite Office depuis avril 2020). Mais là, je dois reconnaître qu’il sera nécessaire de mettre en place une macro pour automatiser la fusion des feuilles de plusieurs classeurs Excel vers un unique classeur.

Je mettrai à jour cette section dans les prochaines semaines afin d’y intégrer le code VBA qui vous offrira cette fonctionnalité.

Cas n°2 : Consolider les feuilles d’un même classeur dans une seule feuille

Vous avez plusieurs manières de résoudre ce cas de figure. La première sera sans code. Ce sera toujours ma préférence personnelle.

Particularité : vous devrez utiliser un nouveau classeur comme point de départ. Vous devez même quitter le classeur où se trouvent les feuilles à fusionner.

Étape n°0 : Bienvenue dans le monde de PowerQuery

Si vous aimez Excel, vous allez adorer PowerQuery (avant de devenir passionné de PowerBI…). Cet outil est venu enrichir les fonctionnalités d’Excel depuis la version 2010.

Avant cette version d’Excel, PowerQuery n’était pas intégré dans l’installation initiale d’Excel, vous deviez aller télécharger le composant sur le site de Microsoft.

Depuis Excel 2016, PowerQuery fait partie intégrante d’Excel. Cette plateforme offre la possibilité d’automatiser de nombreuses manipulations sur les données. Les informaticiens appellent cela un ETL : Extract Transform Load. C’est un outil qui va automatiser des instructions sans écrire de code.

Je le rappelle à qui veut l’entendre, le code (= les macros ou le VBA pour les intimes) peut aussi être utilisé pour écrire des virus informatiques. Les entreprises vont de plus en plus empêcher l’envoi et la réception des fichiers en contenant.

PowerQuery apporte une très belle réponse à de nombreuses opérations qu’il fallait programmer auparavant. Je vous propose d’en découvrir l’une d’entre elles maintenant (si vous allez jusqu’au bout de l’article, vous verrez un deuxième usage de PowerQuery).

Étape n°1 : Déterminer votre source de données

Nous partons donc d’un classeur tout neuf, sans les données. C’est à partir de celui-ci que vous vous rendrez dans l’onglet “Données” puis “Obtenir des données”. En sélectionnant “À partir d’un fichier” puis “À partir d’un classeur”, vous pointerez vers le fichier Excel où se trouvent les feuilles à fusionner.

Cas 2 - Déterminer le fichier source de la fusion des feuilles
Cas 2 – Déterminer le fichier source de la fusion des feuilles

Étape n°2 : Sélectionner les feuilles Excel à agréger

Lorsque vous avez sélectionné votre fichier Excel qui contient les feuilles à fusionner, vous vous retrouvez avec cet écran :

Cas 2 - Sélectionner les feuilles à fusionner
Cas 2 – Sélectionner les feuilles à fusionner

Veillez à bien cocher la case “Sélectionner plusieurs éléments” afin que les cases à cocher apparaissent en 2. Vous pouvez alors sélectionner les feuilles de votre choix (ici toutes) qui seront ensuite fusionnées.

ATTENTION : En 3, je vous invite à bien sélectionner “Charger dans…” et non “Charger” car nous n’avons pas besoin de ces données maintenant. C’est lorsque nous aurons terminées les manœuvres de fusion que vous pourrez les charger !

Lorsque vous aurez cliqué sur “Charger dans…”, la fenêtre suivante s’affichera et vous devrez sélectionner : “Ne créer que la connexion” :

Cas 2 - Charger dans... - Ne créer que la connexion
Cas 2 – Charger dans… – Ne créer que la connexion

Étape n°3 : Fusionner les feuilles Excel se nomme “Ajouter”…

A ce stade, vous avez créé une connexion vers un classeur Excel. Ensuite, vous avez créé des tables basées sur les différentes feuilles qui le compose. Maintenant, il ne reste que le dernier mouvement : Fusionner vos feuilles. Hélas, la traduction française de cette fonction est “Ajouter” :

Cas 2 - Ajouter des requêtes et non fusionner !
Cas 2 – Ajouter des requêtes et non fusionner !

L’option “Fusionner” permet de connecter deux tables à l’aide d’une référence commune. Ainsi, si vous disposez d’une table avec des commandes avec des références de produits et une autre table avec le détail des produits, vous pourrez avoir les commandes avec le détail des produits en fusionnant ces deux tables (basées sur la référence produit).

Lorsque vous sélectionnez l’option “Ajouter”, vous aurez alors à sélectionner les requêtes à mutualiser.

Cas 2 - Sélection des feuilles à 'Ajouter'
Cas 2 – Sélection des feuilles à ‘Ajouter’

En premier lieu, sélectionnez “Au moins trois tables” puis sélectionnez les tables disponibles. Cliquez sur “Ajouter” puis “Ok”.

Étape n°4 : Finaliser la mutualisation des données

La fenêtre suivante apparaît alors :

Cas 2 - Finaliser la fusion des données des différentes feuilles Excel
Cas 2 – Finaliser la fusion des données des différentes feuilles Excel

Vous pourrez changer le nom par défaut “Ajouter1” en un nom qui correspond mieux à la situation. En l’occurrence, je vous propose “Mutualisation”.

Il ne vous reste alors qu’à presser sur “Fermer et charger”.

Cas 2 - Etat final - Données Excel Mutualisées
Cas 2 – État final – Données Excel Mutualisées

Étape n°5 : Bilan de la situation

A ce stade, vous disposez d’un nouveau classeur. Celui-ci est connecté à votre fichier Excel où se trouvent les feuilles à fusionner. Quels sont les avantages et inconvénients de la situation ?

Avantages

Si votre fichier source est mis à jour, vous pourrez venir sur celui-ci et simplement “Actualiser les données” pour que la fusion opère à nouveau !

Alors, pas n’importe quel type de mise à jour.. Si les données des feuilles existantes sont modifiées, pas de problèmes (ajout, suppression ou modification).

Si de nouvelles feuilles sont ajoutées, vous pourrez adapter les requêtes et compléter la fusion des feuilles. Je ne le développerai pas ici, car ce serait rentrer dans trop de détails sur PowerQuery. Mais c’est possible !

Lorsque vous aurez goûté à la puissance de PowerQuery, vous aurez envie d’aller plus loin dans les transformations et l’analyse des données brutes. À travers ce cas d’utilisation, vous entrez dans ce monde par la petite porte.

Inconvénients

Comme votre fichier contenant les données fusionnées est lié à un autre fichier, celui contenant les données de départ ne doit pas être déplacé. Si cela arrivait, pas de panique, il y a quelques paramètres à modifier et la connexion peut être retrouvée. Idem, je ne développerai pas ce point dans le présent article pour ne pas rentrer dans les détails de PowerQuery.

Vous disposez d’un classeur connecté. Le deuxième corolaire de la situation est que vous ne pouvez pas modifier la structure de ce tableau (ajouter des colonnes, en retirer). Heureusement, vous pouvez toujours réaliser un bon vieux “Copier-Coller” pour récupérer les données fusionnées libérées de toute contrainte.

Cas n°3 : Fusionner une feuille en particulier de plusieurs fichiers Excel dans une seule feuille

La situation est la suivante : vous devez collecter des données provenant de plusieurs personnes. Chacune d’entre elles dispose d’un tableau modèle à compléter et vous envoie les données dès que possible.

Vous allez en plus devoir faire face aux enjeux suivants :

  • Vous n’allez pas recevoir toutes les données en même temps et devrez consolider les données reçues au fur et à mesure
  • Certains pourraient vous envoyer des données incomplètes puis des mises à jour
  • Vous aurez peut-être à consolider des données par région / pays ou un autre critère

Comme annoncé plus haut, PowerQuery va être votre meilleur allié.

Étape n°1 : Préparer les noms des fichiers Excel à fusionner

Le nom des fichiers que vous aurez collectés va être essentiel pour la fusion à venir. Grâce à PowerQuery, vous pourrez collecter cette information et identifier l’origine des données, même lorsqu’elles seront fusionnées.

Cas 3 - Préparez les noms de fichiers à fusionner
Cas 3 – Préparez les noms de fichiers à fusionner

Comme vous pouvez le voir sur la capture ci-dessus, j’ai nommé les fichiers par pays et de manière bien structurée.

Étape n°2 : Créer un nouveau classeur pour y fusionner les données des différentes fichiers Excel

Depuis le menu “Données”, vous sélectionnez “Obtenir des données” puis “À partir d’un fichier” et enfin “À partir d’un dossier” :

Cas 3 - Obtenir des données à partir d'un dossier
Cas 3 – Obtenir des données à partir d’un dossier

Excel (via PowerQuery) est capable d’aller récupérer l’ensemble des fichiers d’un dossier en particulier et les traiter.

Cas 3 - Sélection du dossier source des fichiers Excel à fusionner
Cas 3 – Sélection du dossier source des fichiers Excel à fusionner

Étape n°3 : Importer uniquement les fichiers Excel que vous souhaitez fusionner

Après avoir sélectionné le dossier où se trouvent vos fichiers Excel, l’écran suivant s’affiche :

Cas 3 - Filtrer la liste pour sélectionner les fichiers Excel à fusionner
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner

À ce moment (où à un autre), vous vous rendrez compte que d’autres fichiers sont présents dans votre dossier sélectionné (en 1). Dans ce cas, vous aurez à passer par la case “Transformer les données”.

Vous vous trouverez dans une vue similaire à celle-ci. Un peu comme au sein d’Excel, vous aurez la possibilité de filtrer cette liste. Ce que fera PowerQuery, c’est enregistrer la liste des étapes suivies pour les reproduire automatiquement par la suite.

Cas 3 - Filtrer la liste pour sélectionner les fichiers Excel à fusionner - Suite
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner – Suite

En cliquant sur le bouton de filtre, vous retrouverez l’interface familière suivante :

Cas 3 - Filtrer la liste pour sélectionner les fichiers Excel à fusionner - Suite 2
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner – Suite 2

Dans mon cas, je ne souhaite fusionner que les fichiers commençant par “Financial Sample_”. Le caractère “_” est important ici, car je ne souhaite pas y intégrer l’un de mes fichiers (Financial Sample – Tout.xlsx).

Cas 3 - Filtrer la liste pour sélectionner les fichiers Excel à fusionner - Suite 3
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner – Suite 3

Étape n°4 : Combiner vos fichiers pour fusionner les données des fichiers Excel sélectionnés

PowerQuery vous montre désormais la bonne liste de fichiers à importer. Il ne reste qu’à cliquer sur le bouton destiné à fusionner toutes les données contenues dans les fichiers.

Cas 3 - Combiner les fichiers

Cas 3 – Combiner les fichiers

PowerQuery va alors vous présenter le premier des fichiers qu’il prendra comme exemple.

  1. Vous pouvez éventuellement sélectionner un autre fichier pour le prendre en exemple.
  2. Vous déterminez l’objet à importer. Ici, PowerQuery a détecté la présence d’un tableau. Mais si vous le souhaitez, vous pouvez importer la totalité de la feuille.
  3. Ici, cette option va éviter le blocage de PowerQuery en cas d’erreur. D’un côté, c’est une option utile car elle évite les erreurs. D’un autre côté, si vous avez des erreurs dans certains fichiers, vous ne le saurez pas et risquez de penser que tout a été importé… A vous de peser le pour et le contre de cette option.
Cas 3 - Déterminer le contenu à fusionner
Cas 3 – Déterminer le contenu à fusionner

Étape n°5 : Importer les données fusionnées dans Excel

Lorsque vous aurez cliqué sur “OK”, PowerQuery opérera plusieurs opérations automatiquement.

Sur la droite (1), vous trouverez la liste des instructions qui seront suivies à chaque rafraîchissement des données. À ce stade, vous n’avez rien d’autre à faire qu’à “Enregistrer et charger” (2) pour finaliser l’import dans Excel.

Cas 3 - Prêt à charger les données fusionnées des fichiers Excel
Cas 3 – Prêt à charger les données fusionnées des fichiers Excel

Étape n°6 : Bilan de la situation

Vous venez de mettre en place une chaîne automatique d’import des données depuis un dossier. Dans Excel, vous n’aurez qu’à rafraîchir votre tableau pour que la magie opère automatiquement.

Cas 3 - Actualiser les données importées depuis les fichiers
Cas 3 – Actualiser les données importées depuis les fichiers

Lorsque vous cliquerez sur “Actualiser”, Excel ira chercher les fichiers dans le dossier configuré, fera le filtre des fichiers à sélectionner (si vous avez bien suivi ce tuto !) et fusionnera les données de tous les fichiers Excel.

  • Toutes les données seront actualisées. Si un des fichiers a été mis à jour, ce sera pris en compte.
  • Tous les nouveaux fichiers seront inclus. Si un ou plusieurs fichiers ont été ajoutés (ou supprimés), le tableau final n’aura que l’agrégation des données présentes au moment de l’actualisation.

Excel pourra ainsi fusionner des dizaines de classeurs présents dans le dossier en un seul clic.

Comme dans le cas précédent, si votre dossier est déplacé ou que celui qui ouvre le fichier Excel n’a pas les droits d’accès, vous ne pourrez pas actualiser les données. Vous devrez faire un copier-coller (juste les valeurs) dans un fichier séparé pour pouvoir transmettre ces données agrégées à des personnes n’ayant pas accès au dossier source.

Dernière étape

Avez-vous d’autres cas de figure auxquels je n’aurai pas pensé ? Est-ce que ce que je viens d’écrire soulève de nouvelles questions ?

Avez-vous apprécié ce que vous venez de lire ? J’aimerais bien le savoir en vous lisant à mon tour, via les réseaux sociaux ou les commentaires ci-dessous.

J’attends de vos nouvelles ! A bientôt !

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 exemple
TCD – 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 consolidation
Tableau 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 TCD
Tableau 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ées
TCD – 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 feuille
TCD – 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éponse
TCD – 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éponse
TCD – 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.

Créer une liste déroulante dynamique sur Excel

Créer une liste déroulante dynamique sur Excel

Liste déroulante Excel, liste de choix, combo box, 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 et en cascade.

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

Les questions à se poser avant de créer une liste déroulante 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 une liste déroulante statique“.

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. 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 ?

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 des données statiques“.

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.

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

Cas n°1 : Créer une liste déroulante statique

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 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 Excel - 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. 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 Excel - 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 basée sur des données statiques

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 Excel - 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 Excel - 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 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 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

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 Excel - Exemple cascade dynamique
Exemple liste déroulante en cascade dynamique
Liste déroulante 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 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 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 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 : Les listes déroulantes sur Excel.

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 !