Validation de données : décaler une liste déroulante pour que la première ligne apparaisse toujours
Niveau : intermédiaire
Lecture et test : 10 minutes
Comment décaler une liste pour que la première ligne apparaisse dans la validation de données ?
Dans Excel, la validation des données est un outil puissant qui permet de contrôler les entrées dans une cellule.Mais lorsque vous avez une liste relativement longue à afficher, Excel affiche toujours la fin de la liste, et pas le début.
Vous souhaitez en général que la première ligne de votre liste apparaisse en premier dans la liste déroulante de validation de données.
Voici comment procéder :
Étape 1 : préparer votre liste
- Ouvrez votre fichier Excel et identifiez la liste que vous souhaitez utiliser pour la validation des données.
- Assurez-vous que votre liste est bien organisée dans une colonne ou une ligne.
Par exemple, disons que votre liste est dans les cellules A2 à A35. Son « Titre » étant en cellule A1. Nous avons nommé la plage $A$1:$A$35 « Competencies » (cf Article sur les plages nommées, ou nos formations Excel débutant)
Dans notre exemple, ci-contre, la liste représente des « compétences » de sous-traitants ou fournisseurs pour un projet international, à but environnemental ou humanitaire.
Nous avons besoin de nombreuses compétences externes, pour lesquelles 24 catégories ont déjà été déterminées, mais d’autres pas encore.
C’est pourquoi vous voyez la fin de la liste « en jaune pâle » : c’est une mise en forme conditionnelle pour les valeurs vides (cf. un autre article ou nos formations Excel avancé).
Ensuite, vous avez 3 possibilités de traiter votre liste déroulante :
- classique (mais Excel vous montre la dernière ligne)
- via la validation de données
- via le gestionnaire de noms et la validation de données
Nous allons aborder ces 3 possibilités.
Possibilité 1 : créer une liste déroulante avec la validation de données
- Sélectionnez la cellule où vous souhaitez appliquer la validation des données
- Allez dans l’onglet Données et cliquez sur Validation des données (vous pouvez aussi afficher le bouton de validation de données dans vorte Barre d’Outils Accès Rapide – cf. notre article sur le sujet, ou notre formation Excel Débutant)
- Dans la boîte de dialogue, sous l’onglet Options, choisissez Liste dans le menu déroulant Autoriser
- Cliquez dans le champ Source, et sélectionnez la liste $A$2:$A$35. Vous pouvez aussi coller le nom de votre Liste, « Competencies »
Cliquez sur OK.
Cliquez sur la cellule où vous avez appliqué la validation des données. Vous devriez voir une liste déroulante avec votre liste, la dernière ligne apparaissant en premier. C’est ce que nous allons corriger maintenant.
Possibilité 2 : Intégrer une formule dans la validation de données
- Procédez aux étapes 1 à 3 précédentes pour accéder à la source de votre « Validation de Données »
- Dans le champ Fait référence à, entrez la formule suivante :
=DECALER(Feuil1!$A$2;0;0;NBVAL(Competencies);1) - Validez en cliquant sur OK
Cette formule crée une plage qui commence à A2 (sur la feuille activie, ici Feuil1), et s’étend jusqu’à la dernière cellule non vide de la colonne A.
La fonction « DECALER » prends les arguments :
- Réf : notre plage nommée « Contingencies »
- Lignes / Colonnes : les nombres de lignes ou de colonnes vers le haut / bas / droite / gauche dont la cellule supérieure gauche de la référence doit être décalée (pour notre cas présent, c’est 0, puisque nous ne changeons pas notre liste)
- Hauteur / Largeur : c’est la hauteur (et la largeur) attendues en nombres de lignes / colonnes pour le résultat du décalage.
Le fait de préciser à Excel de nous fournir une liste dont la hauteur est le nombre de valeurs dans la plage nommée Competencies va l’obliger à démarrer cette liste à la première ligne
Cette fonction « DECALER » a un autre avantage : la plage de cellules s’ajuste automatiquement lorsque de nouvelles données sont ajoutées.
La fonction NBVAL compte le nombre de cellules non vides dans une plage, ce qui permet de définir la taille dynamique de la liste.
Possibilité 3 : Intégrer une plage nommée dynamique dans la validation de données
Nous allons, dans cette solution, passer par la création d’une plage nommée « dynamiquement », puis l’intégrer dans la Validation de Données.
- Préparez votre liste comme à l’étape 1
- Créer une plage nommée dynamique
- Allez dans l’onglet Formules et cliquez sur Gestionnaire de noms.
- Cliquez sur Nouveau pour créer un nouveau nom.
- Donnez un nom à votre plage, par exemple « ListeDynamique ».
- Dans le champ Fait référence à, entrez la formule suivante (expliquée ci-dessus):
=DECALER(Feuil1!$A$2;0;0;NBVAL(Competencies);1)
- Procédez à nouveau aux étapes 1 à 3 précédentes pour accéder à la source de votre « Validation de Données »
- Dans le champ Source, entrez « =ListeDynamique«
- Validez en cliquant sur OK
Cette étape fera ainsi le lien entre votre Plage Nommée dynamiquement, et la Validation de Données
Vous pouvez ainsi, dans ce cas, dissocier les 2 complexités que nous avons trouvées, à savoir :
- nommer une plage de manière dynamique
- utiliser une formule dans une validation de données
Si vous rencontrez des difficultés ou si certaines manipulations ne sont pas claires, n’hésitez pas à prendre contact avec Integraal-MD.
Notre entreprise propose des formations adaptées pour vous aider à maîtriser Excel et ses fonctionnalités avancées. Ensemble, nous pouvons explorer ces techniques plus en détail et vous permettre de gagner en efficacité dans votre utilisation quotidienne d’Excel.
