Pierre Fauconnier

Voici une solution au problème survenant avec les formules de cumul dans un tableau Excel



Salut

- Pierre, t'as une minute?
- Les "une minute" se transforment souvent en heures, avec toi.
- Oh, comme c'est drôle... Je peux aller demander ailleurs, si tu veux.
- On se calme, on se calme... C'est quoi ton problème?
- Je fais un cumul dans une colonne d'un tableau structuré et ça ne marche pas.
- C'est un problème courant... (quand ça ne marche pas, c'est que ça court... )


Cumul dans un tableau classique


Lorsque l'on veut calculer un cumul, on fige la première cellule de la référence et pas la dernière. De cette façon, lorsque la formule s'étend sur les nouvells lignes, la cellule de départ est fixe et la cellule d'arrivée mobile... Ca marche fonctionne très bien sur un tableau classique:




Cumul dans un tableau structuré

Cumul hors tableau

Pour bien comprendre ce qui va se passer, considérons d'abord le cumul hors tableau en remarquant, sur l'illustration ci-dessous, la façon dont Excel considère les deux formules. Ces formules seraient celles qui permettraient le cumul "dans le tableau" comme illustré plus haut dans le tableau classique. A ce stade, on peut donc considérer que la formule SOMME(A$2:A3) prend toute la colonne du tableau... Et ça, c'est important à comprendre.



Si on ajoute des lignes au tableau, on va s'apercevoir que la première formule n'est pas modifiée, alors que la seconde s'est étendue aux nouvelles lignes, ce qui est normal puisqu'elle prend "toute la colonne":



Cette modification a lieu car Excel a considéré que la formule SOMME(A$2:A3) pouvait être considérée comme SOMME(Tableau1[Valeur]), c'est-à-dire comme étant la somme de toute la colonne du tableau. Dès lors, lorsque le tableau s'étend, Excel continue à prendre "toute la colonne du tableau". On comprend donc bien ici que les deux formules SOMME(A$2:A2) et SOMME(A$2:A3) sont différentes et qu'Excel va donc les gérer différemment.

Cumul dans le tableau

Si on saisit la formule dans le tableau, en première ligne, Excel va se comporter exactement comme pour les formules hors tableau, en considérant que la formule en ligne 2 correspond à SOMME(A$2:A2) alors que sur la ligne 3, Excel considère la formule comme étant SOMME([Valeur]) (formule erronée, destinée à faire comprendre comment Excel interprète la formule).



Dès lors, lorsque l'on va ajouter des lignes, Excel va laisser la première formule en l'état, mais la seconde va s'étendre aux nouvelles lignes. Excel se comporte donc tout à fait "normalement" dès lors que l'on a compris comment les formules sont interprétées.




Solution

La solution va consister à utiliser une référence mixte Classique/Structurée:

• Classique pour la cellule de départ de manière à la fixer;
• Structurée sur base de la même ligne pour rendre la référence relative.

Ici, il n'y a bien qu'une seule interprétation de la formule, et donc, il n'y a qu'une seule formule. Excel va de A2 à la ligne courante. Du coup, lorsque l'on ajoute des lignes, Excel propage la formule et va de la ligne 2 à la ligne courante.




Conclusions

A nouveau, une bonne compréhension de ce que l'on demande à Excel et un peu de réflexion nous permet de mieux appréhender notre outil préféré et donc, de mieux travailler avec.

A bientôt!