Insérer 1 au-dessus / au dessous en gardant une formule dans la colonne

Question

J'ai créé un tableau de tenu de compte bancaire (A2:L100) avec validation des données et un solde à chaque fin de ligne ("Débits", "Crédits", "Solde") - (=SI(ET(J3="";I3="");"";L2+J3-I3) qui fonctionne très bien mais si je crée une ligne au milieu du tableau (que se soit dessus ou dessous), à la colonne solde (L) je me retrouve avec une cellule vide, alors que les autres cellules qui sont avec la validation des données restent, et donc à chaque fois je dois faire avec la cellule du dessus un glisser/déplacer et ce, jusqu'en bas afin que le solde de chaque ligne soit repris en compte. Y'a t-il une solution à ce petit souci ? Merci pour vos réponses. CDLT...

Steelson · Accepted Answer

Parfait ! Protège le bien alors, et solde ce fil de discussion si pas d'autres questions en cliquant sur V...

Steelson · Answer

La solution est simple : Accueil &gt; Mettre sous forme de tableau La nouvelle ligne hérite automatiquement de la mise en forme et des formules Dans ce cas, il vaut mieux écrire aussi en "formule structurée" - As-tu un petit extrait de ton fichier ...

Steelson · Answer

Ok, désolé, je n'avais pas prêté attention à ton profil il faut écrire ta formule avec ARRAYFORMULA https://support.google.com/docs/thread/11907000?hl=en Ton problème par contre est d'avoir une formule différente en L2, Il faut une formule qui teste la ligne ligne()&gt;2 et si elle est supérieure à 2 elle prenne la valeur au-dessus. Autre risque : si tu insères une ligne la formule va se décaler et aller chercher le solde précédent à la ligne plus haut ! il faut aussi inclure dans ce cas une fonction offset ! DECALER(référence_cellule, lignes_décalées, colonnes_décalées, &#91;hauteur&#93;, &#91;largeur&#93;) Avec cela la formule fonctionnera en n'ayant dans ses termes que des cellules de la même ligne. Ce qui te donne une formule en L2 identique avec celles en dessous et sans soucis d'insérer une ligne au-dessus =IF(AND(J2="";I2="");"";if(ROW()&gt;2;offset(L2;-1;0);0)+J2-I2) Remarque surtout que je n'ai plus aucune référence à une autre ligne. Il faut ajouter là-dessus ARRAYFORMULA : regarde ici https://www.sheets-pratique.com/fr/fonctions/arrayformula Sous excel, je calcule ma trésorerie via un TCD et une formule de cumul...

Steelson · Answer

Pas de soucis, mais il faudra que tu te protèges pour éviter qu'un tiers tombe dessus.

Pas de soucis pour L2 ok, mais le but était de parvenir à un solution matricielle qui se répète ensuite. Sauf que j'ai essayé et autant OFFSET admet que l'on appelle sa propre cellule, autant ARRAYFORMULA percute sur la formule. Je vais continuer à investiguer.

ARRAYFORMULA fonctionne trsè bien et simplement, mais ici tu as la difficulté de faire un cumul.

Bilan néanmoins :

tu pourrais faire un cumul avec un TCD (c'est possible avec excel mais je n'ai jamais essayé avec sheets
sinon tu as ci-dessus une formule qui ne bougera pas si tu tries ou si tu insères des lignes, il suffira de rcopier; tu me diras c'était pareil auparavant, sauf que ici il n'y aura pas de saut de ligne si tu insères
mais pour que ce soit répliqué automatiquement, il faut passer par ARRAYFORMULA, j'ai vu que quelqu'un avait soumis cette demande ARRAYFORMULA to sum previous row cell ici https://stackoverrun.com/fr/q/8403516, je vais investiguer

Steelson · Answer

Une solution, un peu complexe car encore une fois on fait à la fois une formule matricielle et un cumul !

en M1

={"cumul Débits";arrayformula(sumif(ROW(A2:A);"<="&ROW(A2:A);I2:I))}

en N1

={"cumul Crédits";arrayformula(sumif(ROW(A2:A);"<="&ROW(A2:A);J2:J))}

en O1

={"Solde";arrayformula(N2:N-M2:M)}

certes il faudrait ne pas afficher le résultat si rien n'est entré en colonne crédit ou débit, à voir ...

Steelson · Answer

Une réflexion ... dans google sheet, la reproduction d'une formule nécessite de passer par arrayformula en l'occurrence, j'ai réussi à cumuler les débits et les crédits et en faire la différence quand tu seras à plus de 1000 lignes, tu feras comme moi, il faudra supprimer ces fonctions matricielles de cumul qui seront chronophages (si cela n'avait pas été en cumul ce serait tout à fait supportable) , 2 solutions viables un cumul via un TCD qui a l'avantage de ne pas calculer en permanence ou la formule proposée avec offset, qui se rapproche de ta formule sans le risque de chamboulement dans les cellules référencées dans la fonction, à recopier (facilement) &gt;&gt; c'est ma préconisation ...