Discussion :

[arthour973]

Bonjour à toutes et à tous,

J'ai un souci que je ne suis pas parvenu à résoudre depuis longtemps.

Par exemple, j'ai cette formule toute simple : =SOMME(A1:A20)
Le problème est que de la ligne 1 à la ligne 20, pour les besoins du travail il m'arrive souvent de supprimer des lignes.

Si, par exemple, je supprime les lignes 11 à 20, la formule somme se modifie et devient : =SOMME(A2:A10)
Si, ensuite, j'ai besoin d'ajouter des lignes et que je mets une somme en A11, elle n'est pas prise en compte.

Je cherche une formule (pas du VBA) qui donnerait en formule :
SOMME(A1:jusqu'à la dernière cellule non vide de la colonne)

Cela peut paraître incroyable, mais je ne trouve dans mes recherche que du VBA et je n'arrive pas à trouver en formule.
Un grand merci pour votre aide,
Amicalement,
lionel,

[Graveling1979]

Salut,

Si tu travaille sur un tableau et que toute tes colonnes ont des noms uniques, le plus simple est de nommer ton tableau (ruban ==> mise sous forme de tableau).
Ensuite, tu peux faire la somme de la colonne que tu souhaites sans être impacté par l'ajout/suppression de lignes dans la formule.

Il y a un tuto sur le site qui explique bien cela. https://fauconnier.developpez.com/tu...ux-structures/

fruit	quantité
pomme	1
poire	2

A supposer que la première cellule soit en A1, tu fais une mise en forme de tableau, que tu nommes "T_inventaire", puis tu peux alors faire la somme de la manière suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(T_inventaire[quantité])

En faisant ainsi, la formule fonctionnera toujours.

[papouclo]

Bonjour,
Mettre dans une cellule d'une colonne autre que la colonne A la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(DECALER($A$1;0;0;MAX(LIGNE($A:$A)*($A:$A<>""));1))

Mais la meilleure solution est sans doute celle de Graveling1979.
Cordialement
Claude

[sorbetframboise]

A tu des choses en dessous de ta colonne A ?

sinon =somme(A:A)

as tu plusieurs cellule vide exemple :

450
562
666
745
498

152
156
360

415
425
465 ?

sinon si tu n'a qu'une cellule vide tu peux faire comme en pièce jointe mais ce n'est pas jolie du tout (le mieux reste une macro)

[arthour973]

Bonjour Graveling1979,

Merci pour m'avoir répondu.
J'ai déjà voulu nommer mais c'est le même problème, c'est la plage qui se modifie.

D'autre part, mes fichiers de travail sont déjà lourds et j'ai déjà pour d'autres usages bcp de plages nommées.

J'aimerais aller au plus simple et je sais que cette formule existe : SOMME(A1:jusqu'à la dernière cellule non vide de la colonne)
Je l'ai vu il y a maintenant longtemps et bêtement, je ne l'avais pas gardé.

Je continue mes recherches,
Amicalement,
lionel,

[arthour973]

Bonjour papouclo,

Merci pour ta réponse,

Mais cela m'oblige à décaler et les autres colonnes de mes fichiers sont déjà prises.

Je continue mes recherches,
Amicalement,
lionel,

[arthour973]

Bonjour sorbetframboise ,

Merci pour ta réponse

Effectivement pas terrible.

Déjà trop de VBA dans mes fichiers de boulot déjà lourds.
J'aimerais rester sur la formule toute simple,

Je continue mes recherches,
Amicalement,
lionel,

[Pierre Fauconnier]

Salut.

Si je reprends le titre de ta discussion: Formule somme (de A1 jusqu'à la dernière cellule non vide de la colonne) , il te suffit d'utiliser =somme(a:a), et une matricielle dans ce cas ne fera qu'alourdir les calculs. Cela dit, je n'aime pas cette méthode, et Graveling1979 t'a donné une piste si tu utilises une table de données (au passage, merci pour le lien vers mon tuto ). L'as-tu essayée?

[...]
J'ai déjà voulu nommer [...]

Nommer une plage ou utiliser une table de données sont des choses différentes qui n'ont rien à voir entre elles...

Peut-être ton problème et ta situation sont-ils mal exposés?

[arthour973]

Bonjour Pierre,

Merci pour m'avoir répondu

Mal m'expliquer, c'est mon habitude LOL (désolé)

Je ne vois pas trop comment dire autrement.

Pour faire au plus simple dans mes fichiers et que les formules restent les mêmes quoiqu'il arrive (suppressions ou ajouts de lignes).

Je souhaiterai une formule genre : somme (de A1 jusqu'à la dernière cellule non vide de la colonne)

Autrement dit en vba :.End(xlDown).Row

Je voudrais éviter le vba, les matricielles et autres décaler ou nommer.
Bien sûr; le =somme(a:a) fonctionne quoiqu'il arrive mais je ne peux le mettre en A1 et je ne peux le mettre que là.

Je continue à chercher,
Amicalement,
lionel,

[Pierre Fauconnier]

[...]
Bien sûr; le =somme(a:a) fonctionne quoiqu'il arrive mais je ne peux le mettre en A1 et je ne peux le mettre que là.[...]

Pas compris...


Voici une matricielle qui permet de ne pas démarrer en A1. Attention que si tu déplaces des colonnes, ça craint...



Si ça ne convient pas, explique ton problème autrement avec éventuellement une copie d'écran explicite.

[arthour973]

Merci Pierre, de t'intéresser à mon "petit" souci

Je tente un gif animé :

lionel

[Pierre Fauconnier]

Le problème des gif, c'est qu'on ne sait pas les arrêter, qu'ils tournent en boucle et qu'on ne sait pas où ils commencent et où ils finissent. C'est assez pénible pour les yeux (J'espérais qu'on en avait fini avec les gif dans les messages... )

Je serais curieux de connaître la réalité professionnelle de ta demande, car je ne vois pas bien dans quel cas on rencontre ce besoin. Peut-être pourrais-tu expliquer les tenants et aboutissants en français (sans jargon et/ou tentative Excel) . Cela aide souvent à clarifier la situation de départ et l'objectif visé.

J'aurais tendance à dire: =SOMME(A2:A1048576)
Si tu dois partir de C5, tu utilises =SOMME(C5:C1048576)

[sorbetframboise]

si tu n'as rien en dessous tu peux rentrer en A1 =somme(A2:A5000) et tu ne devrais plus avoir de soucis pendant un long moment mais a vrai dire sans fichier difficile d'imaginer le problème

[Pierre Fauconnier]

@SorbetFramboise,

Le problème de ne pas "plonger" jusqu'au bas de la feuille, c'est qu'au fur et à mesure de tes suppressions de lignes, A5000 va être modifié et "remonter"... Ce n'est pas une solution fiable

La solution fiable existe, à partir du moment où le besoin REEL et la situation REELLE sont expliquées. Hors cela, ça reste un cas d'école qui n'a aucune réalité de terrain.

[arthour973]

Re-bonjour Pierre,

Je ne sais pas expliquer plus et je ne veux pas te faire perdre ton temps

Pour l'instant, je me débrouille en modifiant les formules quand elles changent mais c'est CH... et risqué.

Je vais donc faire en vba : dans le code de chaque feuille (change)

Private Sub Worksheet_SelectionChange(ByVal R As Range)
If Not Intersect(R, Range("a2:a20")) Is Nothing Then
Range("a1").Formula = "=SUM(a2: a65535)"
End If
End Sub

Je finirai bien par retomber sur la bonne formule ... un jour
lionel,

[sorbetframboise]

Oui je sais mais bon pourquoi ne pas tout simplement mettre le total dans une autre colonne la on pourrait alors mettre somme(A:A) et puis la vie serait + simple

[Pierre Fauconnier]

[...]je ne veux pas te faire perdre ton temps[...]

Ce n'est pas une question de perte de temps...

As-tu testé ce que je t'ai proposé? Si oui, tu verras qu'avec =somme(a2:a1048576), si tu supprimes des lignes, ta formule ne bouge pas. N'était-ce pas cela que tu souhaitais?

Oui je sais mais bon pourquoi ne pas tout simplement mettre le total dans une autre colonne la on pourrait alors mettre somme(A:A) et puis la vie serait + simple

Ah mais si c'était si simple... Durant les formations que je donnes, j'insiste sur LA CONCEPTION du classeur... Si "on" prenait le temps de concevoir selon les règles, la vie serait plus simple...

A ce message, je n'ai toujours pas la réponse sur la réalité de la situation illustrée (jamais rencontrée en 22 ans d'Excel quotidien, 1300 journées de formation données et des centaines de fichiers créés pour mes clients)... Ce n'est pas une question de perte de temps (pour moi en tout cas, vu que je réponds si je veux ou bien je passe à autre chose), mais l'envie de faire passer le message que, sans capacité à énoncer clairement la situation de départ et l'objectif à atteindre, aucune solution technique viable ne pourra voir le jour, et le vba proposé par Arthur ne sera jamais qu'un pansement sur une jambe de bois.

Ce que je n'ai pas compris (dans la formule et dans le code VBA), c'est "pourquoi A20"?

[arthour973]

Re

"As-tu testé ce que je t'ai proposé? Si oui, tu verras qu'avec =somme(a2:a1048576), si tu supprimes des lignes, ta formule ne bouge pas. N'était-ce pas cela que tu souhaitais?"

Oui, c'est bien ce que je souhaite mais : =somme(a2:a1048576) me donne dans la cellule "#NOM?

Ne nous embêtons pas, je ferai du vba LOL
Merci Pierre pour ta voloonté de m'aider
lionel,
"

[Pierre Fauconnier]

[...]mais : =somme(a2:a1048576) me donne dans la cellule "#NOM?[...]

Vu tes illustrations, tu as Excel en français, c'est donc impossible que, si tu as bien saisis ce que tu donnes dans ton message, ça te renvoie l'erreur #NOM...

Mets-toi sur la cellule qui contient la formule, et envoie une copie d'écran de ton excel, avec la cellule et surtout, bien visible, la barre de formule avec la formule que tu as saisie.

[arthour973]

LOL, je commence à me sentir vieux et nul

J'ai juste fait un copier/coller et si je ne sais même plus faire ça ... je deviens trop grave LOL

lionel,