Ergonomie d'une feuille de calcul : validation, formatage conditionnel, plages dynamiques et maintenabilité

02Les plages dynamiques

2.1 Le problème

Une formule =SOMME(D2:D13) couvre douze lignes. Si l'on ajoute trois ventes sous la ligne 13, le total ne les prend pas en compte, sans message d'erreur. C'est l'une des erreurs de maintenance les plus courantes.

2.2 Qu'est-ce qu'une plage dynamique ?

Une plage dynamique est une plage qui s'ajuste automatiquement au nombre de lignes de données. Le programme ne définit pas précisément le terme ; la lecture retenue dans ce cours est celle qui sert à la maintenabilité, avec deux techniques.

2.3 Première technique : le tableau structuré

On met la plage de données sous forme de tableau. Le tableau :

• possède un nom (par exemple Tab_Ventes) et des noms de colonnes ;
• s'étend quand on saisit une ligne juste sous la dernière, et les formules de colonne se recopient d'elles-mêmes ;
• permet des références lisibles : =SOMME(Tab_Ventes[Montant]) désigne toute la colonne Montant du tableau, quel que soit le nombre de lignes.

2.4 Seconde technique : un nom défini par une formule

On définit un nom dont la valeur est une plage calculée avec DECALER et NBVAL.

DECALER(référence;lignes;colonnes;hauteur;largeur) renvoie une plage de hauteur lignes sur largeur colonnes, située à lignes lignes et colonnes colonnes de la cellule de départ.

Nom Ventes_dyn, défini par :

=DECALER(Ventes!$D$2;0;0;NBVAL(Ventes!$D:$D)-1;1)

La formule =SOMME(Ventes_dyn) s'adapte alors à toute ligne ajoutée.

Conditions de bon fonctionnement : aucune cellule non vide parasite dans la colonne (une note, un total) ; aucune ligne vide dans les données (sinon NBVAL sous-estime la hauteur) ; l'en-tête est bien en ligne 1. DECALER est une fonction volatile : elle est recalculée à chaque modification de la feuille, ce qui peut ralentir un grand classeur.

2.5 Choisir

03La maintenabilité d'une feuille de calcul

Une feuille est maintenable si une autre personne peut la comprendre et la modifier sans la casser.

Exemples corrigés

Cas 1 : paramétrer la validation d'une feuille de saisie

On prépare une feuille de saisie des commandes. Les règles à mettre en place, et leur jeu d'essai :

Essais de saisie et résultats attendus :

Pour garantir en plus qu'un code client n'est saisi qu'une seule fois, on remplace la règle de longueur par un critère personnalisé qui réunit les deux conditions, car une cellule ne porte qu'une seule règle de validation : =ET(NBCAR(A2)=4;NB.SI($A$2:$A$200;A2)=1) (codes clients saisis en colonne A à partir de A2).

La liste de famille est plus facile à maintenir si elle est définie dans la plage nommée Liste_familles (feuille de paramètres) : on écrit Liste_familles en source ; un nouvel élément apparaît dans la liste déroulante à condition d'être inséré à l'intérieur de la plage nommée, ou que cette plage soit elle-même dynamique (partie 2 de ce cours).

À retenir

Conclusion à destination du responsable du service des commandes : avec ces cinq règles, les erreurs de saisie les plus fréquentes (quantité nulle ou décimale, famille mal orthographiée, code trop court, taux de remise supérieur à la politique commerciale) sont bloquées à la source. Les règles ne protègent toutefois pas contre un copier-coller venant d'un autre classeur : une formule de contrôle (cours 31) doit compléter le dispositif.

Cas 2 : formatage conditionnel par formule sur un suivi budgétaire

Un suivi budgétaire compare le budget et le réalisé de cinq postes de charges. Seuil_alerte (10 %) est un paramètre.

Formules : D5 =C5-B5 ; E5 =D5/B5 ; total : D10 =SOMME(D5:D9), E10 =D10/B10.

Règles de formatage conditionnel sur la plage A5:E9 :

Le $ devant E fige la colonne : la condition porte toujours sur l'écart en pourcentage de la ligne en cours, et c'est toute la ligne qui est colorée.

Résultats attendus :

Détail de la règle 1 pour la ligne 7 : $E7>Seuil_alerte devient 0,175>0,10, vrai : la ligne est colorée. Pour la ligne 5 : 0,075>0,10, faux.

À retenir

Conclusion à destination de la directrice financière : deux postes dépassent le seuil d'alerte de 10 % : les déplacements (+17,5 %, soit 1 400 €) et la maintenance (+15 %, soit 900 €). L'ensemble des charges reste proche du budget (+2,2 %, soit 1 700 €), car la publicité, sous-consommée de 10 %, compense en partie. Le seuil étant un paramètre, la direction peut le resserrer à 5 % : les fournitures (7,5 %) seraient alors signalées.

Cas 3 : une plage dynamique qui évite d'oublier les nouvelles lignes

La feuille Ventes contient 12 montants en colonne D (de D2 à D13, ceux du cours 26), soit un total de 40 650. En D1 : l'en-tête « Montant ». Le nom Ventes_dyn est défini par =DECALER(Ventes!$D$2;0;0;NBVAL(Ventes!$D:$D)-1;1).

État initial : NBVAL(Ventes!$D:$D) compte 13 cellules non vides (l'en-tête et douze montants) ; la hauteur est 13 - 1 = 12, la plage retournée est D2:D13. =SOMME(Ventes_dyn) donne 40 650, comme =SOMME(D2:D13).

Trois ventes sont ajoutées sous la ligne 13 : 2 100, 4 800 et 900 (lignes 14 à 16).

Vérification : 40 650 + 2 100 + 4 800 + 900 = 48 450. Le nombre de ventes est de 15 (=NB(Ventes_dyn)).

Piège : une cellule parasite. Si quelqu'un écrit une note en D20, NBVAL(Ventes!$D:$D) compte une cellule non vide de plus : la hauteur calculée passe de 15 à 16 et la plage s'étend jusqu'à D17, une cellule vide en trop. SOMME et NB restent justes (une cellule vide n'est pas un nombre), mais LIGNES(Ventes_dyn) renvoie 16 au lieu de 15 : une moyenne calculée par SOMME(Ventes_dyn)/LIGNES(Ventes_dyn) serait faussée. On évite le problème en tenant la colonne propre ou en passant au tableau structuré.

À retenir

Conclusion à destination du responsable du contrôle de gestion : avec une plage fixe, le chiffre d'affaires de 48 450 € serait publié à 40 650 €, soit une erreur de 7 800 € (16 %) sans aucun message. La plage dynamique supprime ce risque et ne coûte qu'une définition de nom. Pour un fichier qui sera repris par d'autres personnes, le tableau structuré, plus lisible et sans fonction volatile, est préférable.

Vocabulaire essentiel

Points clés à retenir

1. Le formatage change l'aspect, pas la valeur.
2. Masquer une ligne ou une feuille ne protège pas les données.
3. La validation contrôle la saisie (liste, bornes, longueur, formule) mais pas le copier-coller.
4. Une règle de validation comprend un critère, un message de saisie et une alerte d'erreur (arrêt, avertissement, information).
5. Le formatage conditionnel par formule s'écrit pour la première cellule de la plage, avec $ pour figer la colonne de la condition.
6. Une plage fixe oublie les lignes ajoutées : on utilise un tableau structuré ou un nom défini par DECALER et NBVAL.
7. DECALER est volatile ; NBVAL est faussé par des cellules parasites.
8. La maintenabilité repose sur les paramètres, les noms, les plages dynamiques, la validation et une notice.

Pièges fréquents

1. Croire que la validation protège tout : un copier-coller la contourne.
2. Oublier le $ dans une règle de formatage par formule : la condition se décale d'une colonne.
3. Écrire la formule de la règle pour une autre cellule que la première de la plage : toute la mise en forme est décalée.
4. Masquer une feuille de paramètres en pensant la sécuriser.
5. Une plage fixe dans un total : les lignes ajoutées sont ignorées.
6. Laisser une note sous les données d'une plage définie avec NBVAL : la hauteur est faussée.
7. Utiliser le formatage pour arrondir : le format affiche, il ne modifie pas la valeur.
8. Valider une liste en recopiant les valeurs à la main dans chaque règle au lieu d'une plage nommée.

Q&R pour le tuteur IA

Q : Comment empêcher la saisie d'une quantité décimale ou négative ? R : Par une règle de validation « nombre entier » avec une borne basse de 1 (et une borne haute si besoin), accompagnée d'un message de saisie et d'une alerte de style Arrêt.

Q : Comment colorer en rouge toute la ligne dont l'écart dépasse 10 % ? R : Par un formatage conditionnel par formule sur la plage des lignes, avec une formule écrite pour la première ligne et la colonne figée, par exemple =$E5>Seuil_alerte, le seuil étant dans une cellule de paramètre.

Q : Pourquoi un total =SOMME(D2:D13) est-il dangereux ? R : Parce que la plage est fixe : une ligne ajoutée en dessous de D13 n'est pas additionnée, sans message d'erreur. On remplace la plage par un tableau structuré ou par un nom dynamique.

Q : Comment définir une plage qui s'étend avec les données ? R : Soit en mettant les données sous forme de tableau (référence Tab_Ventes[Montant]), soit par un nom défini par =DECALER(Ventes!$D$2;0;0;NBVAL(Ventes!$D:$D)-1;1).

Q : Masquer une feuille de paramètres protège-t-il les données ? R : Non. Les cellules masquées restent lues par les formules et accessibles à qui affiche la feuille. Pour protéger, il faut verrouiller les cellules et protéger la feuille ou le classeur (cours 31).

Q : Quel est l'inconvénient de DECALER ? R : C'est une fonction volatile : elle est recalculée à chaque modification de la feuille, ce qui peut ralentir un grand classeur. De plus, son résultat dépend de NBVAL, sensible aux cellules parasites.