Listes déroulantes aux choix amenuisés

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Listes déroulantes amenuisées

Créer des listes déroulantes avec Excel dont les suggestions s'amenuisent pour ne plus proposer les choix déjà effectuées, est l'enjeu de cette formation.



Dans l'exemple illustré par la capture, nous affectons des tâches à des salariés par le biais d'une liste déroulante. Mais lorsqu'un salarié est déjà affecté, il ne doit plus être proposé par la liste déroulante du dessous pour les actions suivantes. Il s'agit donc d'amenuiser ces propositions au fil des affectations.

Source et présentation
Pour réaliser ces travaux, nous proposons de réceptionner un fichier Excel avec quelques données à manipuler.

• Télécharger le classeur listes-choix-restrictifs.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Des actions à réaliser sont listées en colonne B. Un salarié différent doit être affecté à chacune d'entre elles en colonne C, par le biais d'une liste déroulante. Fort logiquement, cette liste de choix n'existe pas à ce stade.



Sur la droite de la feuille, vous notez la présence d'un tableau à partir de la colonne F. Tous les salariés y sont listés. Les quatre colonnes qui suivent sont vierges pour l'instant. Elles consistent à décomposer le processus pour un raisonnement entonnoir. Dans la dernière colonne titrée Restants triés, ne doivent demeurer que les salariés n'étant pas encore affectés à un rôle. Ces résultats évolutifs serviront alors de source de données aux listes déroulantes, pour offrir des choix amenuisés au fil des des affectations.

Isoler les salariés restants
Sans chercher à les trier, nous devons commencer par créer la liste des salariés restants à affecter, après chaque choix réalisé par le biais de l'une des listes déroulantes. Pour cela, il suffit de faire un test d'existence de chaque nom dans la colonne C des affectations.

• Sélectionner la cellule G4 pour le premier salarié restant à isoler,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Inscrire la fonction de dénombrement conditionnel suivie d'une parenthèse, soit : Nb.Si(,
• Désigner les cellules des affectations, soit la plage C4:C10,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$4:$C$10,

Sur cette plage des affectations, nous souhaitons déceler la présence de chacun des salariés à passer en revue en répliquant la formule sur les lignes du dessous. Malgré ce déplacement, cette analyse doit toujours être effectuée sur cette plage aux bornes inamovibles donc.

• Taper un point-virgule (;) pour passer dans l'argument du critère à compter,
• Désigner alors le premier des salariés en cliquant sur sa cellule F4,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, taper l'inégalité suivante : >0,

Si ce critère est vérifié, cela signifie que le salarié en cours d'étude a déjà été affecté. Nous ne devons donc pas le restituer.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire deux guillemets pour conserver la cellule vide,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Désigner de nouveau le premier des salariés en cliquant sur sa cellule F4,

Lorsque la condition n'est pas satisfaite en effet, cela signifie que le salarié n'a pas encore été affecté puisque pas dénombré. Donc, nous le restituons.

• Fermer la parenthèse de la fonction Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons active la cellule du résultat pour l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée de la cellule pour répliquer la logique sur la hauteur du tableau,

Tous les noms sont répliqués à ce stade et c'est tout à fait logique. Nous n'avons encore procédé à aucune affectation. Mais si vous inscrivez quelques salariés en colonne C, en regard de l'une ou l'autre action, vous remarquez qu'ils disparaissent tour à tour de la liste restituée par notre formule. La première étape est donc franchie, mais pour l'instant cette liste reste désorganisée.



La syntaxe de la formule que nous avons bâtie est la suivante : =SI(NB.SI($C$4:$C$10;F4)>0; ''; F4).

Positions des salariés restants
Pour regrouper les salariés restant à affecter, nous devons commencer par identifier leurs positions dans la liste restituée. Ce sont ces positions qui nous permettront à terme de les extraire de façon groupée.

• Sélectionner la première position à trouver en cliquant sur sa cellule H4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Puis, construire le critère suivant : G4<>'',

En effet, nous souhaitons révéler les positions uniquement pour les salariés restant en lice.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Taper la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Désigner une cellule de la première ligne, par exemple A1,

De la sorte, dès qu'un salarié restant est identifié, sa position relative sera transmise par la fonction Ligne au gré de la réplication sur les lignes du dessous : 1 avec A1, 2 avec A2 etc...

• Fermer la parenthèse de la fonction Ligne,
• Puis, taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets ('') pour garder la cellule vide lorsque le salarié est déjà affecté,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur la colonne,

Comme vous pouvez le voir, toutes les positions des salariés restants sont parfaitement identifiées. Et bien entendu, si vous affectez un nouveau salarié, un nouveau trou se forme dans l'énumération des indices.

La formule de repérage que nous avons construite est très simple. Sa syntaxe est la suivante :

=SI(G4<>''; LIGNE(A1); '')



Réunir les positions
Pour extraire tous les salariés restants de façon groupée, nous devons commencer par réunir ces positions. Pour cela, nous devons exploiter la fonction Excel Petite.Valeur. Nous devons la faire agir sur la colonne H. En guise de rang, nous devons lui passer des numéros incrémentés en débutant par le chiffre 1. La première petite valeur sera ainsi extraite puis les suivantes dans l'enchaînement, respectant un rang toujours croissant.

• Sélectionner la première position triée à trouver, soit la cellule I4,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Inscrire la fonction Petite.Valeur suivie d'une parenthèse, soit : Petite.Valeur(,
• Désigner les positions précédemment trouvées, soit la plage de cellules H4:H12,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $H$4:$H$12,

L'extraction des positions à regrouper doit immuablement se faire sur cette plage dont les bornes ne doivent pas bouger avec la réplication de la formule.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,
• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Désigner une cellule de la première ligne, par exemple A1,

Le premier résultat retourné par la fonction Ligne sera donc l'indice 1 puis le 2 etc... Nous allons donc extraire la première petite valeur, puis la deuxième et les suivantes dans l'ordre. Dans ce processus, les cellules vides ou de texte sont naturellement exclues.

• Fermer la parenthèse de la fonction Ligne,
• Puis, fermer la parenthèse de la fonction Petite.Valeur,
• Valider enfin la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur la colonne,

Nous retrouvons bien les mêmes positions mais cette fois elle sont réunies.



En revanche, des erreurs surgissent en bout de course. Elles s'expliquent tout à fait logiquement. Après la dernière valeur numérique, la fonction Petite.Valeur ne trouve plus de données cohérentes à extraire en concordance avec le rang incrémenté. Elle retourne donc une erreur. Pour corriger ce défaut, nous devons englober la précédente syntaxe dans la fonction SiErreur de gestion des anomalies: =SIERREUR(PETITE.VALEUR($H$4:$H$12; LIGNE(A1)); '').

Extraire en fonction des positions
Désormais, pour obtenir la liste amenuisée des salariés groupés restant à affecter, il suffit d'exploiter la fonction d'extraction Index. En deuxième argument, celui de l'indice de ligne, il suffit de lui indiquer la position précédemment trouvée. Par le jeu de la réplication, elles seront toutes passées en revue. Il en résultera la liste des salariés restant à affecter.

• Sélectionner le premier salarié à extraire, soit la cellule J4,
• Taper le symbole égal (=) pour démarrer le calcul,
• Inscrire la fonction de gestion des anomalies, suivie d'une parenthèse, soit : SiErreur(,

En cas d'affectations déjà réalisées, tous les noms ne pourront pas être trouvés. Lorsqu'une fonction d'extraction ne trouve pas ce qui lui est demandé, elle retourne une erreur. De la sorte, nous cherchons par anticipation à les neutraliser.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des salariés, soit la plage de cellules F4:F12,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $F$4:$F$12,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Désigner la première position trouvée en cliquant sur sa cellule I4,
• Fermer la parenthèse de la fonction Index,

Comme vous le remarquez, nous ne prenons pas le soin de renseigner le troisième argument de cette fonction Index. Il concerne l'indice de colonne. Mais en guise de matrice pour la recherche, nous lui avons fournie une plage d'une seule colonne. La fonction Index pointera donc naturellement dessus.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour garder la cellule vierge en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Enfin, double cliquer sur la poignée du résultat pour répliquer la logique sur la colonne,

Comme vous le constatez, tous les salariés qui n'ont pas encore été missionnés sont parfaitement extraits. Et grâce au calcul précédent sur les positions avec la fonction Petite.Valeur, ces salariés restants sont parfaitement groupés.



Si vous affectez un nouveau salarié sur une tâche restante, il disparaît automatiquement de la liste qui se recompose et se réorganise.

La formule d'extraction que nous avons construite est la suivante :

=SIERREUR(INDEX($F$4:$F$12; I4); '')

Liste restreinte et ajustée
Maintenant que l'essentiel du travail est fait, il ne nous reste plus qu'à construire les listes déroulantes des affectations. Mais attention, elles doivent ajuster leur contenu et leur hauteur aux salariés restants. Pour cela, nous devons exploiter la fonction Nb.Si dans l'argument de la hauteur pour la fonction Excel Decaler.

• Sélectionner toutes les cellules des affectations, soit la plage C4:C10,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Avec la liste déroulante de la zone Autoriser, choisir Liste,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, construire la syntaxe suivante :

=Decaler($J$4;;; Nb.Si($J$4:$J$12; '>*<'))



En premier argument de la fonction Decaler, nous fournissons le point de départ de la source de données servant à alimenter les listes déroulantes. Il s'agit du premier salarié extrait et restant à affecter. Nous ignorons les deux arguments suivants. Il s'agit respectivement du décalage en ligne et du décalage en colonne. Par rapport à ce point de départ, nous ne souhaitons observer ni l'un ni l'autre. De fait, nous nous retrouvons dans le quatrième argument de la fonction Decaler. Il concerne la hauteur variable. Nous l'ajustons grâce à la fonction Excel de dénombrement conditionnel Nb.Si. Grâce au critère passé en second argument ('>*<'), nous comptons toutes les cellules réellement non vides sur la plage d'extraction J4:J12. En effet, certaines le sont en apparence mais portent un calcul. Elles ne sont pas considérées grâce à cette syntaxe. De ce nombre, nous en déduisons la hauteur ajustée de chaque liste déroulante amenuisée.

• Cliquer sur le bouton Ok de la boîte de dialogue pour créer ces listes déroulantes,

Désormais, à chaque fois que vous actionnez l'une des listes déroulantes, cette dernière restreint ses propositions à celles qui n'ont pas encore été faites. Nous ne risquons donc pas d'affecter deux fois un même salarié. La solution est ainsi sécurisée.