Vers une gestion dynamique
Lorsque l'on utilise une mise en forme conditionnelle ou des listes de validité ou des macro-commandes, et pour intervenir le moins possible sur les formules et, par conséquence, rendre la feuille de calcul la plus portable possible, il faut 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 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 non vide. C'est ce qu'illustre l'image ci-dessous où les entêtes des lignes et des colonnes ont un fond jaune.
Dans l' exemple illustré, sont utilisées :
- 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.
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é (ou de validation)
J'ai utilisé la possibilité des tableurs de nommer les plages de cellules :
- onglet "formule" item "gestionnaire de noms" sous MS Excel ;
- menu "feuille" item "plages ou expressions nommées" sous LO Calc ;
- menu "données" item "plages nommées" sous G Sheeet.
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($B;$B); 1) ie la plage B9:B17 de l'image.
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.
2. La mise en forme condtionnelle (MFC)
La MFC se réalise par
- onglet "accueil" item "mise en forme conditionnelle" sous MS Excel ;
- menu "format" item "conditionnel" sous LO Calc ;
- menu "format" item "mise en forme conditionnelle" sous G Sheeet.
La MFC de l'exemple étudié (illustrée avec MS Office), utilise 2 règles qui colorent en jaune respectivement l'arrière plan des cellules non vides de la ligne 8 (=$8:$8) et de la colonne B ( =$B:$B).
3. L'affichage 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.
Pour qu'elles soient indépendantes de la quantité de données(nombre de lignes et de colonnes) présente dans la feuille de calcul, il faut obtenir la référence de cette plage de données.
Cette référence est obtenue par la fonction DECALER().
La principale difficulté que l'on rencontre se trouve dans l'évaluation des paramètres NbLigne et NbColonne de cette fonction.
Solution 1 :
Utilisation de RechercheH()
Les 3 paramètres à utiliser sont :
- E2 : qui correspond au chiffre d'affaire cherché ;
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) ;
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 :
- E3 : qui correspond au vendeur cherché ;
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) ;
- 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 :
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 ;
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) ;
- 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))
4. Preuve par l'image
4 étapes en images pour comprendre la solution 1 du paragraphe précédant.
