Vers une gestion dynamique

Lorsque l'on utilise une mise en forme conditionnelle ou des listes de validité ou des macro-commandes, il est préférable que les formules utilisées soient  :

  • le plus indépendantes possible des données de la feuille de calcul  et
  • permettent l'ajout ou la suppression  de lignes et de colonnes

pour intervenir le moins possible sur ces formules ou macro-commandes et rendre la feuille de calcul la plus portable possible.

Pour une telle feuille de calcul, il faudra que toutes les colonnes non vides et toutes les lignes non vides de la plage utilisée pour les calculs aient une entête comme l'illustre l'image ci-dessous où les entêtes sont colorées en jaune.
Dans cet exemple, sont utilisés :

  • les listes de validité (cellules E2 et E3)
  • les mises en forme conditionnelle (entête en ligne 8 et en colonne B)

La cellule E5 affiche le chiffre d'affaire réalisé par le vendeur sélectionné en E3 pour l'année choisie en E2

Cliquer sur l'image pour l'agrandir

Ce billet détaille les fonctions utilisées pour réaliser cette feuille dans une optique de portabilité des données ( par exemple pour l' ajout d'une colonne CA2022  ou l'ajout d'un nouveau vendeur ou le retrait d'un vendeur existant).

1. Listes de validité

J'ai utilisé la possibilité des tableurs de donner des noms aux plages de cellules.

J'ai nommé "ChiffreAffaire" la plage de cellules dont la référence est le résultat que donne la formule DECALER($A$1;7;2;1;NBVAL($8;$8)) ie la plage C8:E8 de l'image ci-dessus.
J'ai nommé "Vendeur" la plage de cellules dont la référence est le résultat que donne la formule DECALER($B$1;8;0;NBVAL($8;$8);1) ie la plage B9:B17 de l'image.

Cliquer sur l'image pour l'agrandir

 

Lorsque les noms sont définis, ils sont utilisés dans le champ "source" de l'item "validité" pour la plage de cellule dont le contenu doit apparaitre (être autorisé)  dans la liste.

Cliquer sur l'image pour l'agrandir

 

2. Le calcul du C.A

Plusieurs combinaisons de formules permettent d'obtenir le chiffre d'affaire du vendeur sélectionné en cellule B3 pour l'année sélectionnée en cellule B2. La principale difficulté que l'on rencontre se trouve dans l'évaluation des décalages (paramètres NbLigne et NbColonne de DECALER()) et des positions (paramètres index des fonctions RECHERCHEV,  RECHERCHEH et INDEX())

Solution 1 :

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)

Solution 2 :

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)

 

Solution 3 :

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))

3. Preuve par l'image

4 étapes en images pour comprendre la solution 1 du paragraphe précédant

Cliquer sur l'image pour l'agrandir