Comment avoir une liste déroulante dynamique de tous les vendredis de l'année spécifiée dans une autre liste déroulante ?

Un des visiteurs de mon blog Formation Excel a posté ce commentaire :« Je n'arrive pas à trouver la formule qui me permettra d'avoir une liste déroulante de tous les vendredis de l'année. A savoir que l'année concernée est choisie par une autre liste déroulante. »

Liste déroulante dynamique de tous le vendredis de l'année

Son message est clair, il a deux listes déroulantes, et il veut que lorsqu'il sélectionne une année dans la première liste, la seconde liste sera remplie de tous les vendredis correspondant à cette année.

Cet exercice ressemble à celui que j’ai traité dans un article précédent nommé : Comment créer des listes déroulantes dépendantes ou en cascade avec Excel ? (vous pouvez y jeter un coup d'œil si vous le désiriez) à une différence que dans l’exercice présent, les données que va contenir la liste déroulante dépendante, doivent être extraites d’un tableau ou plage de cellules à l’aide d’une formule de recherche.

Voici donc une capture du résultat estimé :

Avoir une liste déroulante de tous les vendredis

Comment procéder pour résoudre cet exercice ?

Pour vous donner une réponse directe :

Nous allons dans un premier temps nous servir de la fonction INDEX pour extraire « tous les vendredis » à partir d’une colonne contenant tous les jours de l’année.
Puis nous allons utiliser la fonction DECALER pour remplir la liste déroulante des résultats trouvés.

Soyez patient et suivez avec moi la procédure en dessous pour pouvoir répondre à la requête de notre ami !

Créez la liste déroulante des années

Dans C1 tapez : Sélectionnez une année.
Puis sélectionnez la cellule D1 et créez une liste déroulante contenant les années de 2019 à 2026 par exemple.

Créez une colonne contenant tous les jours de l’année

Dans la cellule A1 tapez la formule suivante pour afficher le premier jour de l’année en fonction de l’année affichée dans D1 :

=DATE($D$1;1;1)

Dans la cellule A2, insérez cette formule =A1+1 pour incrémenter la première date d’un jour puis cliquez sur la poignée de recopie et étirez vers le bas jusqu’à la cellule A365.
Dans la cellule A366, entrez la formule suivante :

=SI(A365=DATE($D$1;12;30);A365+1;"")

A366 va afficher le dernier jour de l’année si elle est bissextile, si non la cellule n’affiche rien.

Sélectionnez la plage A1:A366 et nommez-la par exemple : ListeJours

Extraire tous les vendredis à partir de la plage ListeJours en utilisant INDEX

En d’autres termes, nous effectuerons une recherche à l’aide d’INDEX pour renvoyer plusieurs résultats en fonction d’un seul critère qui est l’année sélectionnée.

Allons pas à pas pour arriver à atteindre ce but :

Identifier les cellules contenant tous les vendredis

Sélectionnez G1 et saisissez la formule suivante : =JOURSEM(ListeJours;2)=5

En exécutant cette formule, Excel va vérifier si le chiffre renvoyé par la fonction JOURSEM est égal à 5 (désignant vendredi) ou non, et ceci en tenant en compte que le premier jour de la semaine est lundi, ce que nous l’avons indiqué à Excel en choisissant le chiffre 2 dans le deuxième argument de notre fonction JOURSEM.

Comme vous le remarquez , Excel renvoie FAUX pour 01/01/2019.
Copiez ensuite la formule vers le bas jusqu’à la cellule G366 et voyez ce que ça donne.

Note : ne vous dérangez pas si G366 affiche une erreur en cas d’une année régulière.

Depuis ce résultat, vous pouvez identifier les cellules qui contiennent tous les vendredis. Voici par exemple les premières montrées dans cette image :

Les premières cellules affichant vendredi

Renvoyer les numéros de lignes des cellules affichant les dates trouvées :

Cette étape est très importante parce que nous avons besoin de connaitre les numéros de lignes des cellules qui contiennent tous les vendredis pour les référencer dans INDEX d’une façon à ce qu’elle puisse nous renvoyer tous les résultats trouvés.

Servez-vous de la fonction AGREGAT !

La fonction AGREGAT dans notre cas, va créer un ensemble de ces numéros de lignes en les faisant isoler des autres numéros des lignes qui ne répondent pas à notre condition.

Par exemple, les dates des vendredis trouvées pour 2019 se positionnent dans les lignes : 4,11,18,25,32…95,102,109 et ainsi de suite jusqu’à la ligne 361. C’est alors cette liste de chiffres que nous voulons les introduire dans INDEX pour renvoyer tous les vendredis de 2019.

Afficher les numéros de lignes des vendredis :

Modifiez la formule qui existe dans G1 en tapant ceci :

=(JOURSEM(ListeJours;2)=5)/(JOURSEM(ListeJours;2)=5)

Puis copiez-la vers le bas :

A ce stade, Excel effectuera les divisions FAUX/FAUX (0/0 ce qui renvoie l’erreurDIV#0) et VRAI/VRAI (1/1). Les résultats renvoyés seront exploités dans l’étape suivante :

Modifiez encore cette dernière formule dans G1, en la multipliant par LIGNE(A1) :

=((JOURSEM(ListeJours;2)=5)/(JOURSEM(ListeJours;2)=5))*LIGNE(A1)

Attention aux parenthèses !
Copiez la formule vers le bas
Voilà, nos numéros de lignes émergent !

Obtenir les numéros de lignes par formule

Utiliser AGREGAT

Pour assembler ces numéros de lignes comme je l’ai mentionné avant, nous allons utiliser la fonction PETITE.VALEUR qui se trouve parmi les fonctions que regroupe la fonction AGREGAT. La fonction PETIITE.VALEUR renvoie le ou les plus petits nombres dans une plage de cellules selon un paramètre spécifié.

L’utilité d’utiliser AGREGAT est que PETITE.VALEUR sera effectuée sur les valeurs numériques obtenue en ignorant ces erreurs DIV#0.

Tapez la formule suivante dans H1 :

=AGREGAT(15;6;$G$1:$G$366;1)

Choisir la fonction PETITE.VALEUR et l'option sans erreur dans AGREGAT

15 : spécifie la fonction PETITE.VALEUR.
6 : est le numéro affecté à l’option permettant d’ignorer les valeurs d’erreur.
$G$1:$G$366 : est la plage contenant les numéros de lignes concernés.
1 : est le paramètre qui désigne le rang du numéro à renvoyer.

Excel va renvoyer donc la première petite valeur qui est 4.

Et avec une petite amélioration, nous pourrons obtenir une liste de tous les numéros de lignes.

Si nous comptons le nombre des numéros de lignes qu’affiche la plage $G$1:$G$366 ça peut varier entre 52 et 53 selon l’année choisie. Etant donnée, nous allons entrer la formule suivante à la place de 1 :

LIGNES($A$1:A1)

Cette formule permet d’incrémenter le numéro de ligne lorsqu’elle est copiée vers le bas, regardez l’exemple suivant :

Incrémenter les numéros de lignes par formule

Alors quand elle est insérée dans AGREGAT, elle nous donnera le résultat suivant :

Après avoir modifié la formule dans H1, étirez vers le bas jusqu’à ce que vous obteniez une valeur d’erreur (#NOMBRE):

Extraire les numéros de lignes d'une plage de cellules

Voilà donc notre liste de tous les numéros de lignes dont nous avions besoin pour les utiliser dans INDEX est obtenue.

Remarque : il y a des années comme 2021 qui compte 53 vendredis, alors lorsque vous la sélectionnez, vous verrez que la dernière cellule H53 affiche le dernier numéro de ligne au lieu de la valeur d’erreur.

Utiliser la fonction INDEX

Modifiez la formule dans H1 en y ajoutant INDEX :

=INDEX(ListeJours;AGREGAT(15;6;$G$1:$G$366;LIGNES($A$1:A1)))

INDEX va chercher dans la plage ListeJours la date correspondant au premier numéro renvoyé par AGREGAT qui est 4.

Alors, Excel affiche dans H1 : 04/01/2019. C'est la date du premier vendredi qui correspond à la 4^ème ligne.

Note : Appliquez le format Date à la cellule H1, si cette dernière n’affiche pas la date correctement.

Copiez la formule vers le bas jusqu’à la cellule H53
Tout fonctionne bien alors !

Liste des vendredis extraits par formule

Sélectionnez une autre année par exemple 2021 et voyez le résultat affiché.

La formule de recherche améliorée :

Nous ferons mieux si nous intégrons la formule utilisée dans G1:G366 directement dans notre formule de recherche au lieu de demander à Excel d’aller chercher à chaque fois les numéros de lignes dans la plage G1:G366. Voici ce que vous allez faire :

Copiez la formule contenue dans la cellule G1 sans le signe = et collez-la à la place de $G$1:$G$366 référencée dans AGREGAT.
Modifiez ensuite LIGNE (A1) par LIGNE(ListeJours).
Votre formule ressemblera à ceci :

=INDEX(ListeJours;AGREGAT(15;6;((JOURSEM(ListeJours;2)=5)/(JOURSEM(ListeJours;2)=5))*LIGNE(ListeJours);LIGNES($A$1:A1)))

Copiez-la vers le bas.

Formule de recherche avec INDEX et AGREGAT

Si vous êtes satisfait de ce résultat, supprimez le contenu de la colonne G.

Créez et remplissez la liste déroulante dépendante :

Sélectionnez par exemple : C3 et tapez : Liste de tous les vendredis

Et pour créer et remplir finalement la liste déroulante des dates de tous les vendredis trouvés, nous allons utiliser la fonction DECALER :

Nommez tout d’abord la plage H1:H53 : ListeVendredis
Sélectionnez la cellule D3 et cliquez sur Validation des données puis choisissez Liste sous Autoriser
Entrez la formule suivante dans la zone Source:

=DECALER($H$1;0;0;NB(ListeVendredis))

Remplir une liste déroulante dépendante par la formule DECALER

Excel renvoie donc les valeurs contenues dans la plage H1:H53 en fonction du nombre de résultats trouvés 52 ou 53.

Faites un test pour vérifier si tout fonctionne comme il le faut.

Avoir une liste déroulante dynamique de tous les vendredis

Félicitations ! vous avez réussi à créer une liste déroulante dépendante affichant tous les vendredis en fonction de l’année sélectionnée dans l’autre liste déroulante.