SOMME et DECALER

[Jam]

Bonjour à tous,

Cette satanée fonction DECALER me posera donc toujours des soucis !
Bref, mon problème est assez simple:
- Je cherche à mettre en "en-tête" de colonne une formule de sommation dont la plage s'agrandira automatiquement si des lignes sont ajoutées en bas de tableau. On est d'accord c'est pas bien compliqué. mais voilà j'y arrive pô 🙁
J'utilise la fonction suivante dans la cellule E3 (et je veux sommer les cellules du dessous !:
=SOMME(E$4😀ECALER(E$4;0;0;NB(E:E);1))
j'ai essayé plein de variante...sans succès 🙁 J'ai soit un #REF, soit une circulaire, soit un résultat = à 0 alors que j'ai mis des valeurs dans les cellules du dessous (c'est mieux pour tester).

Si quelqu'un à une ch'tite idée je suis preneur.

PS./ J'ai joint un fichier exemple au cas où je serai pas clair.

 

[filiph79]

Re : SOMME et DECALER

bonjour jam,

le nombre de cellules à compter doit plutôt corresponde à la colonne D.

a tester : =SOMME(E$4😀ECALER(E$4;;;NBVAL(D😀);1))

Cordialement

 

[Tibo]

Re : SOMME et DECALER

Bonjour jam,

Le souci vient du fait que tu as des cellules vides.

Compter le nombre de valeurs ne nous permet pas d'obtenir le numéro de la dernière ligne.

Une solution, avec SOMME et DECALER intégrés dans une formule matricielle :

en E3 :

=SOMME(DECALER(E$4;1;0;MAX((E4:E100<>"")*LIGNE(E4:E100))-3))

à valider par CTRL + MAJ + ENTREE

Je te laisse tester

@+

 

[Jam]

Re : SOMME et DECALER

Salut à tous,

Merci pour vos réponses malheureusement ni l'une ni l'autre ne me convienne.
filiph, cela ne change marche pas mieux
tibo, en fait autant utiliser une somme bête et méchante sur une plage très large, comme cela il n'y a pas de risque 😉 Car en fait le pbm des formules matricielle c'est qu'on ne peut en changer la taille 🙁

Merci pour votre aide.
Une autre idée peut-être ?

 

[Tibo]

Re : SOMME et DECALER

re,

Dans l'exemple que tu nous donnes, il s'agit d'additionner le 100 et le 25.

Y-a-t-il un rapport avec les données contenues en colonne D ? C'est-à-dire additionner les valeurs de la colonne E jusqu'à la dernière ligne non vide de la colonne D ?

Si c'est le cas, la solution de Philippe devrait convenir. Sa retranscription sans les smileys :

=SOMME(DECALER(E4;;;NBVAL(D:D)))

@+

 

[Jam]

Re : SOMME et DECALER

Re,

Il n'y a pas de lien véritable. En fait je préfère ne pas m'appuyer sur cette colonne car il est probable que rien n'y soit mis alors que dans le même temps la colonne E soit remplie. Donc ca marche pô. De même si une ligne est sautée alors la ligne d'après n'est pas prise en compte.
Il s'agit donc en fait de connaître la dernière cellule qui contient une valeur.

Merci pour ton aide

 

[Tibo]

Re : SOMME et DECALER

re,

Pour connaître la dernière cellule contenant une donnée, il y a plusieurs solutions (avec formule matricielle ou non). Le souci vient du fait que la cellule destinée à recevoir le total se trouve dans la même colonne, ce qui empèche de faire référence à la colonne dans son entier (pour cause de référence circulaire).

La formule que je t'avais proposé permetttait d'adapter la taille de la plage de E4 à E... (dernière cellule contenant une donnée).

Sinon, le plus simple effectivement serait :

=SOMME($E$4:$E$65536)

Sinon, pas d'autre idée (pour le moment).

@+