Comment masquer les éléments précédemment utilisés dans une liste déroulante ?
Dans Excel, créer une liste déroulante standard est une technique courante de saisie de données, mais que faire si vous souhaitez que la liste déroulante devienne plus courte à chaque sélection – en supprimant les éléments déjà choisis afin que chaque option ne puisse être sélectionnée qu'une seule fois ? Par exemple, imaginez que vous avez une liste déroulante avec 100 noms uniques : après avoir sélectionné un nom, celui-ci est retiré des choix de la liste déroulante, ne laissant que 99 options. À mesure que vous continuez vos sélections, la liste se réduit automatiquement jusqu'à ce qu'il n'y ait plus d'options disponibles. Ce niveau d'interactivité peut être très pratique dans des scénarios tels que l'assignation de tâches sans doublons, les arrangements de sièges ou les tirages au sort où chaque choix doit être unique. Cependant, Excel n'offre pas cette fonctionnalité directement, donc vous devez recourir à des solutions spécifiques. Dans les sections suivantes, vous trouverez des instructions étape par étape sur la façon d'y parvenir.
Masquer les éléments précédemment utilisés dans une liste déroulante avec des colonnes auxiliaires
Masquer les éléments précédemment utilisés dans une liste déroulante avec des colonnes auxiliaires
Supposons que vous ayez 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 les éléments précédemment utilisés, suivez les étapes décrites ci-dessous. Cette approche utilise des colonnes auxiliaires supplémentaires pour suivre les éléments déjà sélectionnés et pour construire la liste source dynamique de la liste déroulante. Bien que cette méthode puisse sembler élaborée, elle est simple et ne nécessite aucune compétence en programmation.
Les scénarios applicables incluent la planification, l'attribution de ressources, ou tout cas où un élément ne doit être choisi qu'une seule fois jusqu'à épuisement de la liste. L'avantage est la clarté grâce à des formules visibles et une logique traçable ; cependant, il est nécessaire de maintenir des colonnes supplémentaires sur votre feuille de calcul.
1. À côté de votre liste de noms, dans la cellule B1, entrez la formule suivante pour vérifier si un nom a déjà été sélectionné dans la plage de la liste déroulante cible :
=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())Cette formule compare chaque nom avec les 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 comme valeur auxiliaire. Assurez-vous d'ajuster la plage F1:F11 pour qu'elle corresponde à l'emplacement où vous prévoyez de placer vos listes déroulantes, et la référence A1 à l'emplacement de votre liste de noms.
Remarque : Vérifiez deux fois que la plage 'F1:F11' englobe toutes les cellules de la liste déroulante. Le 'A1' doit pointer vers la ligne actuelle dans votre liste de noms.
2. Faites glisser la poignée de remplissage vers le bas pour appliquer cette formule à toutes les lignes de votre liste de noms. Cela créera une série de résultats auxiliaires identifiant les noms non utilisés.
3. Dans la colonne C, configurez une autre formule auxiliaire dans la cellule C1 pour créer dynamiquement une liste propre contenant uniquement les noms non 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 rassemble tous les noms non utilisés en extrayant les éléments appropriés de la colonne A en fonction des valeurs auxiliaires dans la colonne B. À mesure que des noms sont choisis et supprimés de B, cette liste dans la colonne C se met automatiquement à jour. Si votre liste dépasse 11 noms, assurez-vous d'ajuster toutes les plages en conséquence.
4. Copiez cette formule vers le bas pour correspondre à la longueur de votre liste de noms d'origine. La plage que vous remplissez doit être aussi longue que votre liste dans la colonne A.
5. Pour rendre cette liste mise à jour dynamiquement utilisable pour votre liste déroulante, définissez une plage nommée. Sélectionnez la nouvelle liste créée dans la colonne C (par exemple, C1:C11), puis cliquez sur Formules > Définir un nom.
6. Dans la boîte de dialogue Nouveau nom, entrez un nom (par exemple, namecheck), et utilisez cette formule de référence dynamique pour garder la plage nommée correctement dimensionnée à 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 pour les options de la liste déroulante. Vérifiez soigneusement 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 de calcul différente, assurez-vous de mettre à jour la formule en conséquence pour éviter les erreurs.
7. Maintenant, pour créer la liste déroulante réelle, sélectionnez les cellules où vous souhaitez que les utilisateurs fassent leurs sélections (par exemple, F1:F11). Allez 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 tapez =namecheck dans le champ Source, faisant référence à la plage nommée dynamique que vous avez définie.
Cliquez sur OK pour terminer. Chaque fois qu'un nom est sélectionné dans la liste déroulante, il est omis de la liste pour les autres listes déroulantes, garantissant que tous les choix sont uniques. Si vous essayez de sélectionner le même nom dans une autre cellule, vous constaterez qu'il n'est plus disponible comme option.
Astuce : Ne supprimez ni n'écrasez aucune des colonnes auxiliaires (colonnes B et C), car elles sont essentielles pour que la liste déroulante se mette à jour correctement. Envisagez de masquer ces colonnes si vous souhaitez garder votre feuille de calcul bien rangée sans perturber la fonctionnalité. Si vous rencontrez des problèmes avec les mises à jour de la liste, vérifiez les formules pour détecter des incohérences de plage, ou assurez-vous que tous les liens de validation des données sont corrects et font référence à la plage nommée prévue.
Une limitation de cette approche est que si plusieurs utilisateurs font des sélections simultanément (par exemple, sur une feuille de calcul partagée), des conflits peuvent encore survenir. Pour des solutions plus avancées, évolutives ou pour automatiser cette tâche avec moins d'encombrement visible sur la feuille de calcul, envisagez d'utiliser VBA - la section suivante illustre cette alternative.
Articles connexes :
Comment insérer une liste déroulante dans Excel ?
Comment créer une liste déroulante avec des images dans Excel ?
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels