Comment masquer les éléments déjà utilisés dans une liste déroulante ?
Dans Excel, créer une liste déroulante standard est une pratique courante pour faciliter la saisie de données — mais comment faire si vous souhaitez que cette liste se réduise automatiquement à chaque sélection, en supprimant les éléments déjà choisis afin que chaque option ne puisse être utilisée qu’une seule fois ? Imaginez, par exemple, une liste déroulante contenant 100 noms uniques : dès qu’un nom est sélectionné, il disparaît des choix disponibles, ne laissant plus que 99 options. À chaque nouvelle sélection, la liste continue de se raccourcir jusqu’à épuisement complet des possibilités. Ce niveau d’interactivité s’avère particulièrement précieux dans des scénarios tels que l’attribution unique de tâches, la gestion de places assises ou encore les tirages au sort, où chaque choix doit rester exclusif. Toutefois, Excel ne propose pas nativement cette fonctionnalité, ce qui nécessite le recours à des solutions de contournement spécifiques. Les sections suivantes vous guident pas à pas pour la mettre en œuvre.
Masquer les éléments déjà utilisés dans Liste déroulante à l’aide de colonnes auxiliaires
Masquer les éléments déjà utilisés dans Liste déroulante à l’aide de colonnes auxiliaires
Supposons que vous disposiez d’une liste de noms dans la colonne A, comme illustré dans la capture d’écran ci-dessous. Pour configurer une liste déroulante qui masque automatiquement les éléments déjà utilisés, suivez pas à pas les étapes décrites. Cette méthode s’appuie sur des colonnes auxiliaires supplémentaires pour suivre les éléments déjà sélectionnés et générer dynamiquement la source de la liste déroulante. Bien qu’elle puisse sembler élaborée à première vue, elle est en réalité simple à mettre en œuvre et ne nécessite aucune compétence en programmation.
Les scénarios applicables incluent la planification, l’attribution de ressources ou tout autre cas où un élément ne doit être sélectionné qu’une seule fois jusqu’à épuisement de la liste. L’avantage réside dans la clarté offerte par des formules visibles et une logique facile à suivre ; toutefois, cela implique la gestion de colonnes supplémentaires sur votre feuille de calcul.
1. À côté de votre liste de noms, dans la cellule B1, saisissez la formule suivante pour vérifier si un nom a déjà été sélectionné dans la plage cible de la liste déroulante :
=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())Cette formule compare chaque nom aux sélections effectuées dans la liste déroulante (plage F1:F11). Si le nom a déjà été choisi, elle renvoie une cellule vide ; sinon, elle renvoie le numéro de ligne sous forme de valeur auxiliaire. Veillez à adapter la plage F1:F11 à l’emplacement prévu pour vos listes déroulantes, ainsi que la référence A1 à celui de votre Liste des noms.
Remarque : assurez-vous que la plage « F1:F11 » couvre bien toutes les cellules de votre liste déroulante. La référence « A1 » doit pointer vers la ligne en cours dans votre Liste des noms.
2. Tirez la poignée de recopie vers le bas pour appliquer cette formule à toutes les lignes de votre liste des noms. Vous obtiendrez ainsi une série de résultats auxiliaires identifiant les noms qui n’ont pas encore été utilisés.
3. Dans la colonne C, saisissez une autre formule auxiliaire en C1 afin de générer dynamiquement une liste propre contenant uniquement les noms non encore utilisés :
=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1))))Cette formule regroupe automatiquement tous les noms non utilisés en extrayant les éléments correspondants de la colonne A, selon les valeurs auxiliaires présentes en colonne B. Dès qu’un nom est sélectionné et supprimé de la colonne B, la liste en colonne C se met à jour en temps réel. Si votre liste contient plus de 11 noms, pensez à adapter toutes les plages concernées.
4. Copiez cette formule vers le bas pour qu’elle s’applique à toute la longueur de votre liste des noms d’origine. La plage remplie doit être aussi longue que votre liste située en colonne A.
5. Pour que cette liste mise à jour dynamiquement soit utilisable dans votre liste déroulante, définissez une plage nommée. Sélectionnez la liste nouvellement créée en colonne C (par exemple, C1:C11), puis cliquez sur Formules > Définir un nom.
6. Dans la boîte de dialogue Nouveau nom, saisissez un nom (par exemple,)namecheck) et utilisez la formule de référence dynamique suivante pour que la plage nommée s’adapte automatiquement à mesure que des noms sont sélectionnés :
=OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1)Cela garantit que seules les valeurs non vides de la colonne C sont utilisées comme options de la liste déroulante. Vérifiez attentivement le nom de votre feuille et les références de cellules — en utilisant des formats d’adresse exacts — pour qu’ils correspondent à votre propre feuille de calcul.
Remarque : si vous modifiez la liste des noms, ajoutez ou supprimez des lignes, ou utilisez une feuille différente, pensez à mettre à jour la formule en conséquence pour éviter toute erreur.
7. Pour créer la liste déroulante, sélectionnez maintenant les cellules dans lesquelles vous souhaitez que les utilisateurs fassent leurs choix (par exemple, F1:F11). Rendez-vous dans Données > Validation des données > Validation des données.
8. Dans la boîte de dialogue Validation des données, sous l’onglet Paramètres, choisissez Liste et saisissez =namecheck dans le champ Source, en faisant référence à la plage nommée dynamique que vous avez définie.
Cliquez sur OK pour terminer. Dès qu’un nom est sélectionné dans une liste déroulante, il est automatiquement exclu des options proposées dans les autres listes, garantissant ainsi l’unicité de tous vos choix. Si vous tentez de le sélectionner à nouveau dans une autre cellule, vous constaterez qu’il n’est plus disponible.
Conseil : Ne supprimez ni n’écrasez aucune des colonnes auxiliaires (colonnes B et C), car elles sont essentielles au bon fonctionnement de la liste déroulante. Pour conserver une feuille de calcul propre sans altérer la fonctionnalité, envisagez plutôt de masquer ces colonnes. Si la liste ne se met pas à jour correctement, vérifiez les formules pour repérer d’éventuelles incohérences dans les plages, ou assurez-vous que toutes les validations de données sont bien liées à la plage nommée prévue.
Une limite de cette approche est que, si plusieurs utilisateurs effectuent des sélections en même temps (par exemple, sur une feuille partagée), des conflits peuvent tout de même survenir. Pour des solutions plus avancées, évolutives ou pour automatiser cette tâche avec un encombrement visuel réduit sur la feuille, envisagez d’utiliser VBA — la section suivante présente précisément cette alternative.
Articles associés :
Comment insérer une liste déroulante dans Excel ?
Comment créer une liste déroulante intégrant des images dans Excel ?
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels