Aller au contenu principal


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.

 

Billet créé le :
25 nov 2021
Dernière MAJ :
30 Sep 2024

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.

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é (ou de validation)

J'ai utilisé la possibilité des tableurs de nommer une cellule ou une plage 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.

Cliquer sur l'image pour l'agrandir

J'ai donc 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.

Cliquer sur l'image pour l'agrandir

 

une variante pour GG décaler ... trop compliqué pour toi ... !

Si vous êtes en froid avec les formules, les tableaux structurés de MsExcel peuvent être la réponse à votre besoin. Pour en savoir plus, lire le billet " les tableaux structurés". Voici, cependant, quelques étapes à suivre pour obtenir une liste de validation dynamique sans utiliser de 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.

Cliquer pour agrandir l'mage


É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.

Cliquez pour agrandir l'image

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.

Cliquer pour agrandir l'image

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. Ce chiffre d'affaire est la valeur d'une cellule. Les fonctions de recherche ou la fonction "index" répondent à ce besoin.
Pour que les formules restent indépendantes de la quantité de données (nombre de lignes et de colonnes) présente dans la feuille de calcul, il faut obtenir par calcul la référence de cette plage de données.
Cette référence est, encore une fois,  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 :

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

4. 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