Vers une gestion dynamique

Si vous êtes en froid avec les formules, les tableaux structurés de MsExcel peuvent être la réponse à votre besoin. Voici quelques étapes à suivre pour obtenir une liste de validation dynamique sans connaître les formules.

Étape 1 : création d'un tableau structuré (cf image ci-dessous)  :

1.  Dans le menu "Insertion", cliquer sur l'icône "Tableau" pour ouvrir la fenêtre "Créer un tableau" (cadre vert de l'image).

2.  Sélectionner les cellules contenant les données qui figureront dans la liste de validation. Ma liste porte l'en-tête "Occurence" donc je coche la case "mon tableau comporte des en-têtes". La référence de la plage de cellule apparait dans le cadre vert.

3. Cliquer sur le bouton "OK"  puis nommer votre tableau structuré (T_Occurence) dans mon exemple.

Étape 2 : Création de la liste (cf image ci-dessous)  :

1.  Dans le menu "Formules", cliquer sur l'icône "Gestionnaire de noms" pour ouvrir la fenêtre "Nouveau nom" (cadre vert de l'image).

2. Inscrire le nom choisi pour votre liste (ListeOccurences dans mon cas). 

3. Sélectionner les cellules contenant les données qui figureront dans la liste de validation pour faire apparaitre la référence de la plage de cellule dans le cadre vert. Cette référence utilise la syntaxe des tableaux structurés. Cliquer sur le bouton "OK".

4. Dans le menu "Données", cliquer sur l'icône "Outils de données" pour ouvrir la fenêtre "Validation de données" (cadre bleu de l'image)

5. Compléter les critères de validation par  :

   •  dans le champ "Autoriser" : "liste"  ;

   •   dans le champ "Source" : la formule"=NomDeLaListe"  (dans mon cas "=ListeOccurrence").

6. Cliquer sur le bouton "OK"  pour visualiser votre liste de validation.

Utilisation de RechercheH()

Les 3  paramètres à utiliser sont :

1. E2 : qui correspond au chiffre d'affaire cherché ;

2. DECALER($A$1; 7; 2; NBVAL($B:$B)+1; NBVAL($8:$8)) qui calcule la référence d'une plage de cellules (celle qui correspond au tableau des données  et qui contient l'information cherchée ie : C8:E17 sur l'image). Notons que la ligne des entêtes doit figurer comme 1^ère ligne de cette plage (car c'est là que se trouve la valeur contenue dans E2)  et que la colonne des entêtes en est exclue (ne contient pas de chiffre d'affaire) ;

3. EQUIV(E3;$B:$B;0) - 7 qui rend la position du vendeur (valeur de la cellule E3) dans la liste des vendeurs (on doit retirer 7 pour tenir compte du décalage).

Ce qui nous donne (NB vous pouvez en voir la preuve en image au $3 de ce billet) :

=RECHERCHEH(E2; DECALER($A$1; 7; 2; NBVAL($B:$B)+1; NBVAL($8:$8)); EQUIV(E3;$B:$B;0)-7)

Ou en utilisant le nom des plages :

=RECHERCHEH(E2; DECALER($A$1; 7; 2; NBVAL(Vendeur)+1; NBVAL(ChiffreAffaire)); EQUIV(E3;Vendeur;0)+1)

Utilisation de RechercheV()

Les 3  paramètres à utiliser sont :

1. E3 : qui correspond au vendeur cherché ;

2. DECALER($A$1; 8; 1; NBVAL($B:$B); NBVAL($8:$8)+1) qui calcule la référence d'une plage de cellules ( celle qui correspond au tableau des données  et qui contient l'information cherchée ie : B9:E17 sur l'image). Notons que la colonne des entêtes doit figurer comme 1^ère colonne de cette plage (c'est là que se trouve la valeur de la cellule E3)  et que la ligne des entêtes en est exclue  (ne contient pas de chiffre d'affaire) ;

3. EQUIV(E2;$8:$8;0) - 1 qui rend la position du chiffre d'affaire (valeur de la cellule E2) dans la liste des chiffres d'affaire (on doit retirer 1 pour tenir compte du décalage).

Ce qui nous donne :

=RECHERCHEV(E3; DECALER($A$1; 8; 1; NBVAL($B:$B); NBVAL($8:$8)+1); EQUIV(E2;$8:$8;0)-1)

ou en utilisant le nom des plages :

=RECHERCHEV(E3; DECALER($A$1; 8; 1; NBVAL(Vendeur); NBVAL(ChiffreAffaire)+1); EQUIV(E2; ChiffreAffaire;0)+1)

Utilisation de INDEX()

Les 3 paramètres à utiliser sont :

1. DECALER($A$1; 8; 2; NBVAL($B:$B); NBVAL($8:$8))  qui calcule la référence d'une plage de cellules ( celle qui correspond au tableau des données et qui contient l'information cherchée ie: C9:E17 sur l'image). Notons que la ligne et la colonne des entêtes figurent dans cette plage ;

2. EQUIV(E3;$B:$B;0)-8 qui rend la position du vendeur (valeur de la cellule E3) dans la liste des vendeurs (position dans la colonne jaune) ;

3. EQUIV(E2;$8:$8;0)-2 qui rend la position de l'année cherchée (valeur de la cellule E2) dans la liste des années (position dans la ligne jaune).

Ce qui nous donne :

=INDEX(DECALER($A$1; 8; 2; NBVAL($B:$B); NBVAL($8:$8)); EQUIV(E3;$B:$B;0)-8; EQUIV(E2;$8:$8;0)-2)

ou en utilisant le nom des plages

=INDEX(DECALER($A$1; 8; 2; NBVAL(Vendeur); NBVAL(ChiffreAffaire)); EQUIV(E3;Vendeur;0); EQUIV(E2;ChiffreAffaire;0))