Validation - listes de choix

Introduction
Création d'une liste simple
Liste à partir du résultat d'une autre liste (fonctions Decaler())
Liste de choix de villes

Introduction

Une validation de données vous permet de placer des limites sur les données qui seront accepté dans une cellule ou une étendue de cellules. Vous déterminez les critères avec l'option de validation de données.

Une vidéo vous montre les options de cette commande. Cette option est plus souvent utilisée pour limiter les valeurs en utilisant une liste prédéterminée. La vidéo ci-dessous va vous montrer le fonctionnement.

Création d'une liste simple

Liste à partir du résultat d'une autre liste (fonctions Decaler())

On peut déveloper cette option en utilisant plusieurs validation basée sur des listes. Dans le prochain exemple, on utilise le contenu de la première liste pour déterminer quel sera le contenu de la seconde liste. La fonction Decaler() sera utilisée pour déterminer le contenu de la seconde liste de validation.

Pour suivre cet exercice, vous pouvez aller chercher le classeur deuxlistesdéroulantes.xlsx qui se trouve sur la page des fichiers de démonstrations.

Entrez le texte et les valeurs dans les cellules appropriées.

Vous pouvez aussi aller chercher le fichier deuxlistesderoulantes.xlsx à partir de la page des fichiers de démonstration. Sélectionnez la feuille de calcul Exemple 1.

Placez le curseur dans la cellule B1.
De l’onglet Données, sélectionnez l’option Validation de données.

Word 2003
Word 2007
Excel
VBA pour Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière

De la case Autoriser, sélectionnez l’option Liste. Pour la case Source, entrez l’étendue A4:A8. Appuyez sur le bouton OK. Ne vous inquiétez pas s'il y a des "$" dans l'adressagedes cellules. Cela n'aura pas d'impact sur le résultat de la commande.

Placez le curseur dans la cellule B1. Appuyez sur le triangle au bout de la cellule B1. Vous verrez une liste de 1 à 5 qui est le contenu de A4 à A8.

Cette cellule va déterminer dans quelle colonne la seconde liste de validation devra chercher les valeurs requises. Ces valeurs sont dans l’étendue de cellules de A4 à E8. Les valeurs de la première colonne sont de 1 à 5. Les valeurs de la seconde colonne vont de 6 à 10 etc. Il faut utiliser la fonction Decaler() pour déterminer la colonne de valeurs à afficher selon le résultat de la cellule B1.

Placez le curseur dans la cellule B2.
De l’onglet Données, sélectionnez l’option Validation de données.

De la case Autoriser, sélectionnez l’option Liste. Pour la case Source, entrez la formule =DECALER(A4;0;B1-1;5;1) . Appuyez sur le bouton OK.

=decaler(point de départ;décaler horizontalement, décaler verticalement; [hauteur]; [largeur])

La fonction Decaler() permet d’afficher le contenu d’une autre cellule. Elle requiert un minimum trois arguments. Le premier est un point de départ. Il s’agit de l’adresse d’une cellule de votre feuille de calcul. Le second argument détermine le déplacement vertical.  Un entier positif déplace de X cellules vers le bas. Un entier négatif déplace de cette valeur vers le haut. Une valeur de zéro (0) indique aucun déplacement. Le troisième argument indique le déplacement horizontal. Un entier positif déplace de X cellules vers la droite. Un entier négatif déplace vers la gauche.

Pour cet exercice, le déplacement horizontal est déterminé par la formule B1-1. Il s’agit du résultat de la cellule B1 qui peut être entre 1 et 5. Il indique dans quelle colonne la seconde liste doit aller chercher les valeurs.  Le point de départ est la cellule A4. Ceci est le début de la première colonne des valeurs. Il ne faut donc pas qu’il y ait un déplacement. C’est pour cela qu’on ajouter -1 à la formule. Le déplacement sera de 1 pour aller à la seconde colonne et ainsi de suite. Une façon d’éviter cette situation est de placer le point de départ juste à l’extérieur de la liste des valeurs. Mais le tableau commence dans la colonne A. Il faudrait déplacer tout le tableau d’une colonne vers la droite pour avoir un point de départ à l’extérieur du tableau.

Les deux prochains arguments sont optionnels mais nécessaire pour cet exemple. Le quatrième argument détermine le nombre de cellules en hauteur qu’il faut choisir. Pour cet exemple, il faut une hauteur de cinq cellules. Le denier argument détermine la largeur, en colonnes, qu’il faut choisir. Les listes de choix sont toujours d’une colonne de largeur. Donc la formule requise est =DECALER(A4;0;B1-1;5;1).

Placez le curseur dans la cellule B1. De la liste de validation, sélectionnez une valeur où la seconde liste ira chercher les valeurs requises. Sélectionnez la cellule B2. Vous pourrez constater que la liste de choix correspond à la colonne choisie dans la première liste de choix.

Index d’une liste de villes par régions

Le prochain exercice démontre un exemple pratique de ce que vous venez de réaliser. Il s’agit de choisir une ville selon une liste d’une région du Québec. La première liste vous permet de choisir parmi cinq régions du Québec. La seconde liste vous permettra de choisir une ville de la région choisie. En plus de la fonction Decaler(), la fonction Recherchev() sera utilisée pour déterminer la colonne à choisir et le nombre de villes par régions.

Il est préférable de télécharger le fichier deuxlistesderoulantes.xlsx à partir de la page des fichiers de démonstrations. Sélectionnez la feuille de calcul Exemple 2.

Avant de commencer, regardons le contenu de cette feuille de calcul. Les cellules B1 et B2 seront utilisées pour déterminer la liste de choix pour les régions et les villes respectivement. Les cellules A6 à A10 seront les valeurs de la liste de régions. Les cellules A13 à C17 seront utilisées avec la fonction Recherchev() pour déterminer la colonne à choisir ainsi que la « hauteur » de chaque liste de villes. Les cellules E7 à I41 contiennent la liste des villes selon les régions. C’est ce qui sera affiché dans la seconde liste selon le choix de région indiqué dans la cellule B1. Les cellules K6 à L123 contient la liste de toutes les villes en ordre alphabétique et une valeur unique dans la seconde colonne. La cellule B3 affichera cet index selon la ville choisie en B2. La fonction Recherchev() est encore une fois utilisé pour cette situation. Il faut maintenant créer les deux listes de validation.

Placez le curseur dans la cellule B1.
De l’onglet Données, sélectionnez l’option Validation de données.

De la case Autoriser, sélectionnez l’option Liste. Pour la case Source, entrez l’étendue A6:A10. Appuyez sur le bouton OK. La liste des choix de régions est déterminée.

Placez le curseur dans la cellule B2.
De l’onglet Données, sélectionnez l’option Validation de données.

De la case Autoriser, sélectionnez l’option Liste. Pour la case Source, entrez la formule =decaler(e7;0;recherchev(b1;a13:c17;2)-1;recherchev(b1;a13:c17;3);1). Appuyez sur le bouton OK. La cellule E7 est le point de départ pour la seconde liste de choix et la liste des villes. Le déplacement vertical est de zéro. Le déplacement horizontal dépend de la région choisie en B1qui est comparé au tableau de comparaison utilisé par la fonction Recherchev. Il utilisera le résultat de la seconde colonne pour déterminer la colonne à choisir. Le -1 est encore utilisé dans ce cas. La hauteur est déterminée par le résultat d’un autre recherchev qui utilisera le contenu de la troisième colonne du tableau de comparaison de A13 à C17. La largeur doit être de 1 pour que la liste de validation accepte cette formule.

Il est temps de vérifier les résultats. Dans la cellule B1, sélectionnez une région du Québec. Dans la cellule B2, sélectionnez une ville de cette région. La cellule B3 devrait indiquer l’index que vous retrouverez dans le tableau K6 à L123 pour la ville choisie.

Abonnez-vous à l'infolettre

Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.

Cliquez ici pour vous abonner à l'infolettre