Conserver la somme lors de l'insertion de lignes au dessus et en dessous de la plage.

Bonjour à tous,

Cette astuce provient de Dominique, qui est aussi l'auteur de Quick Bill, un outil tout simple d'édition de factures.

xl-en-bref 1 1
En bref :

L'idée est d'utiliser la formule suivante : =SOMME(DECALER(B$1;1;0;LIGNES($B$1:$B$5)-2)) pour permettre l'ajout de lignes en dessous et au dessus de la plage.

xl-en-detail 1
Dans le détail :

1/ Si on décompose la formule, on part de la plage qui contient le titre du haut (B1) et la cellule du résultat (B5) et on calcule le nombre de lignes : LIGNES($B$1:$B$5).
2/ On enlève deux lignes LIGNES($B$1:$B$5)-2 pour la ligne de titre et la ligne contenant la formule SOMME
3/ On va ensuite utiliser la formule DECALER pour retourner la plage sur laquelle faire la somme : DECALER(B$1;1;0;LIGNES($B$1:$B$5)-2).
Cette formule se lit : définit une plage partant de B$1, décale cette cellule de 1 ligne vers le bas, de 0 colonne vers la droite, et définit la hauteur de la plage comme étant LIGNES($B$1:$B$5)-2. Cela renvoit donc la plage B2:B4.
4/ Il ne reste plus qu'à faire la SOMME.

FormuleDecaler 1

De fait, lors de l'ajout de nouvelles lignes, $B$1:$B$5 va automatiquement s'étendre et la SOMME va s'appliquer à l'ensemble de la plage.

Note : l'ajout de lignes juste au dessus de la formule SOMME va automatiquement redimensionner la plage de la somme, mais cela n'est pas le cas pour la plage du dessus.

Amusez-vous bien !

Gaetan

Pour marque-pages : Permaliens.

4 réponses à Conserver la somme lors de l'insertion de lignes au dessus et en dessous de la plage.

  1. Jeff dit :

    Bonjour
    La plage renvoyée ne serait-elle pas B2:B4 ? De plus concernant la fonction DECALER, je dirais plutôt 0 colonne vers la droite, plutôt que 0 ligne.
    Merci pour toutes ces infos et astuces envoyées régulièrement. C'est toujours un vrai plaisir d'apprendre de nouvelles fonctionnalités, ou tips.......
    Bonne journée

  2. Gaetan Mourmant dit :

    Bonjour Jeff,
    Merci pour les commentaires, je viens de corriger 🙂
    A bientôt
    Gaetan

  3. Verstaevel dit :

    Bonjour, merci pour l'astuce mais que vient faire le numéro de portable de M. Handelsman, PDG de Vinci, dans cette histoire ? (cf. bulle insérée dans le tableau)
    Par ailleurs, quand on insère une ligne dans un tableau, avec un calcul de somme à la dernière ligne, la cellule contenant la somme se décale vers le bas et prend a priori toujours en compte la nouvelle ligne inséré pour calculer le nouveau total. Alors dans quel cas cette astuce trouve t-elle sa justification, svp ? Merci

  4. Gaetan Mourmant dit :

    Bonjour et merci pour vos commentaires.

    Dominique était à l'origine de l'astuce. Comme son téléphone était présent au départ, je l'ai laissé, avec sa permission bien entendu.

    Pour la dernière ligne, c'est effectivement le cas, comme indiqué dans la note de bas de page. Par contre, ca n'est pas le cas pour les lignes du dessus.

    Cordialement
    Gaetan

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *


La période de vérification reCAPTCHA a expiré. Veuillez recharger la page.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.