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

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é :

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 :

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 !

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)

• 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 :

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):

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 !

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

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

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.

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.

Lire la suite »

Comment remplir un tableau à partir d'une base de données en répondant à plusieurs critères sans VBA ?

Dans l’article présent de la formation Excel, je vais vous présenter un exercice dont l’objectif est de vous montrer comment créer une formule de recherche personnalisée qui permet d’afficher plusieurs résultats en fonction de plusieurs critères sans utiliser aucun code VBA.

J’ai reçu dernièrement un message d’un de mes contacts qui cherchait une solution pour remplir un tableau à partir d’un autre tableau source en fonction de plusieurs critères de recherche.

L’idée donc consiste à effectuer une recherche pour trouver plusieurs résultats en répondant à plusieurs critères. Une tâche qu’on ne peut pas la faire avec une formule de recherche classique.

Comme vous le voyez dans cet exemple, je veux qu’Excel cherche dans ma base de données les valeurs qui sont liées à mes trois critères de recherche (Nom du vendeur, mois et année) et remplira ensuite mon tableau de Rapport avec les résultats trouvés.

Je souhaite de plus qu’Excel m’affiche dans les entêtes des colonnes Iphone et Samsung la quantité totale des ventes pour chaque smartphone.

Comment arriver à réaliser ce travail ?

Moi, j’y suis arrivé en utilisant une formule de recherche qui se compose des deux fonctions INDEX et EQUIV conjointement en jouant sur leurs paramètres pour qu’elles me permettent d’afficher plusieurs résultats au lieu d’un seul dans le cas classique.

Et pour vous expliquer comment aboutir à ce résultat souhaité et obtenir la formule finale, suivez avec moi, très attentivement, ces étapes :

• Téléchargez tout d’abord le fichier exemple: Rapport de vente.xlsx

Extraire les mois et les années

Dans la feuille Rapport, nous avons deux critères à rechercher à part le nom du vendeur : Mois et Année. Et si nous voulons extraire de la base de données les valeurs correspondant à ces deux critères, nous n’y trouverons aucune colonne les contenant. Ajoutez donc deux colonnes à cette base de données et remplissez-les en extrayant les mois et les années à partir de la colonne Date.

• Dans la cellule E2 saisissez la formule =TEXTE((A2);"mmmm") puis copiez-la vers le bas.
• Dans la cellule F2 tapez =ANNEE(A2) puis copiez-la vers le bas également.

Contourner les limites de EQUIV

Vous savez que INDEX renvoie la valeur contenue dans la cellule qu’on a précisée ses cordonnées : numéro de ligne et de colonne.

Dans notre cas, et si par exemple nous cherchons les données de ventes (Date, Quantité d’Iphone et de Samsung vendue) associées au vendeur Philippe pendant le mois Février 2018, nous devons passer en premier par EQUIV qui va nous renvoyer les numéros de lignes où se positionnent ces données pour les introduire dans INDEX.

Mais avant d'utiliser la fonction EQUIV, nous allons faire quelques manipulations au niveau de ses arguments : valeur cherchée et tableau de recherche pour qu’elle nous renvoie les valeurs souhaitées :

Fournir un nouveau critère de recherche

En partant des trois critères de recherche, nous allons nous servir de la fonction CONCAT pour les combiner à fin d’avoir un seul critère de recherche que nous allons introduire après comme valeur de recherche dans la fonction EQUIV.

• Dans la cellule L2 de la feuille Rapport, entrez la formule suivante :

=CONCAT($E$4;$B$5;$B$3)

• Ce qui donne : PhilippeFévrier2018

Note : CONCAT est une nouvelle fonction qui vient de remplacer la fonction CONCATENER, cette dernière ne sera plus disponible dans les prochaines versions d’Excel selon Microsoft.

Préparer le tableau de recherche

Nous allons créer tout d’abord une nouvelle colonne qui va afficher les trois valeurs concaténées : Vendeur, Mois et Année,  et dont nous nous servirons après pour créer une seconde colonne qui constituera le tableau de recherche (second argument de EQUIV):

• Activez la feuille « Base de données » et choisissez la cellule M1 puis saisissez le texte "Valeurs Concaténées" comme entête de cette nouvelle colonne.
• Dans la cellule M2, tapez la formule =CONCAT(B2;E2;F2)
• Puis copiez-la vers le bas pour remplir toutes les cellules de cette nouvelle colonne.

Créer une clé unique pour chaque résultat trouvé

Vous savez que si nous effectuons une recherche avec INDEX+EQUIV classique, Excel va nous renvoyer seulement le premier résultat, alors et pour palier à ce problème et pour que notre formule de recherche nous renvoie tous les résultats trouvés, nous allons définir pour chacun de ces résultats une clé unique.

Compter le nombre d’occurrences d’une valeur cherchée

La première chose à faire avant la création des clés uniques est de créer une formule qui va compter combien de fois se répète une valeur cherchée dans la dernière colonne obtenue:

• Dans la feuille "Base de données", sélectionnez la cellule L2 et tapez la formule suivante : =NB.SI($M$2:M2;M2) puis étirez vers le bas.

Regardez donc les résultats affichés, par exemple la valeur PhilippeFévrier2018 se répète 5 fois.

Créer la clé unique

Modifiez la formule précédente en lui joignant le nom de chaque élément de la colonne « Valeurs Concaténées » pour créer cette clé.

• La nouvelle formule que vous allez obtenir est la suivante :

=M2&"_"&NB.SI($M$2:M2;M2)

• Excel affiche alors la valeur suivie de son nombre d’occurrence.
• Copiez la formule vers le bas.

Voilà donc, c’est cette colonne qui sera référencée comme tableau de recherche dans EQUIV.

Finaliser la formule EQUIV

Jusqu’à maintenant, les deux arguments valeur cherchée et tableau de recherche sont prêts.

• Activez donc la feuille Rapport où nous allons utiliser la fonction EQUIV, et numérotez premièrement les lignes du tableau Rapport (31 lignes maximum) à partir de la cellule A9.
• Dans la cellule B9, tapez donc : =EQUIV($L$2&"_"&A9;'Base de données'!$L$2:$L$72;0)
• $L$2&"_"&A9 : joint le contenu de L2 (PhilippeFévrier2018) au numéro affiché dans A9 reliés tous les deux par Underscore, ce qui donne PhilippeFévrier2018_1.
• 'Base de données'!$L$2:$L$72 : est la référence du tableau de recherche obtenu précédemment dans lequel effectuera Excel sa recherche.
• 0 : c’est le dernier argument de EQUIV qui impose à Excel de renvoyer la valeur exacte.
• En exécutant la formule, Excel renvoie le numéro 3.

• Copiez la formule vers le bas pour afficher les autres numéros : 5 résultats trouvés et 5 numéros renvoyés.

Supper ! tout ce travail que nous avons mené jusqu’ici c’est pour arriver à obtenir ces numéros de lignes dont INDEX a besoin pour fonctionner d’une façon dynamique.

Afficher la date de vente :

Intégrons à présent la formule EQUIV dans INDEX pour effectuer la première recherche. Copiez la formule suivante dans B9 :

=INDEX('Base de données'!$A$2:$D$72;EQUIV($L$2&"_"&$A9;'Base de données'!$L$2:$L$72;0);1)

• 'Base de données'!$A$2:$D$72 : est la référence de notre base de données (sans inclure les entêtes de colonnes et sans inclure les colonnes Mois et Année) où sera effectuée la recherche.
• L'argument numéro de ligne sera renvoyé par EQUIV.
• 1 est le numéro d’ordre de la colonne Date dans la base de données.

Excel affiche donc la date convenable.

• Copiez la formule vers le bas pour obtenir les résultats restants.

Note : définissez le format Date pour les cellules de la colonne Date, si la date ne s’affiche pas correctement.

Afficher les quantités vendues pour chaque smartphone

• Copiez la formule index sur la cellule C9 puis sur D10.
• Sous Iphone, modifiez le numéro de colonne en 3, et sous Samsung, entrez le numéro 4.
• N’oubliez pas d’appliquer le format standard ou le format Nombre entier aux deux cellules.
• Sélectionnez les deux cellules et double-cliquez sur la poignée de recopie.
• Tous les résultats s’affichent bien.

• Effectuez d’autres sélections des critères de recherches et vérifiez si tout fonctionne comme souhaité.

Il nous reste ce message d’erreur #NA !

Pour vous débarrasser de ce message, vous pouvez utiliser la fonction SIERREUR pour le remplacer par une chaîne vide par exemple, ou bien, et ce qui est mieux, est d’utiliser la fonction SI.

Le tour que va jouer SI dans ce cas est d’interdire à Excel de continuer sa recherche quand le nombre de résultat trouvés est atteint.

• Premièrement, nous allons compter le nombre de résultats trouvés :
• Sélectionnez M2 dans la feuille Rapport et tapez la formule suivante :

=NB.SI('Base de données'!$M$2:$M$72;L2)

• Remarquez qu'ici j’ai indiqué à NB.SI d’aller compter le nombre de la valeur affichée dans L2 (PhilippeFévrier2018) dans la colonne « Valeurs concaténées » : 'Base de données'!$M$2:$M$72.

• Deuxièmement, nous allons créer une formule pour compter le nombre de lignes sélectionnées:
• Testez par exemple cette formule =LIGNES($B$9:B9) en la tapant dans H1 et étirez vers le bas, que remarquez-vous ?

Revenons à notre formule de recherche

• Sous Date, modifiez la formule de recherche en l’intégrant dans SI :

=SI(LIGNES($B$9:B9)<=$M$2;INDEX('Base de données'!$A$2:$D$72;EQUIV($L$2&"_"&$A9;'Base de données'!$L$2:$L$72;0);1);"")

• Si le nombre de lignes renvoyé par LIGNES($B$9:B9) est inférieur ou égal au nombre de résultat affiché dans M2, effectuer la formule de recherche, si non afficher une chaîne vide.
• Copiez la formule vers le bas.
• Faites la même chose avec les colonnes Iphone et Samsung.

Génial ! n’est-ce pas ?!

Afficher le nombre d’Iphone vendu dans l’entête

• Sélectionnez l’entête C8 puis tapez ceci :

="Iphone"&" ( "&SOMME($C$9:$C$39)&" )"

Afficher le nombre de Samsung vendu dans l’entête

• Sélectionnez la cellule D8 puis tapez ceci :

="Samsung"&" ( "&SOMME($D$9:$D$39)&" )"

Retouche dernière

Cette dernière tâche consiste à faire masquer (et ne pas supprimer !!) tous ce que vous avez ajouté à vos deux feuilles de calcul pour revenir à l’affichage initial de votre classeur :

• Sélectionnez vos cellules et vos plages de cellules concernées, et définissez une couleur de police blanche ou bien affichez la boite de dialogue Format de cellules et cliquez sur Personnaliser puis tapez ;;; dans la zone Type et validez.

Bravo vous avez pu faire un excellent travail !

Dernier mot, n’hésitez pas à partager cette solution à votre tour !

Lire la suite »