Discussion :

[FreddyNB]

Bonjour à tous,
j'aimerais savoir si quelqun à une idée afin qu'une fonction somme ne prenne en considération que des cellules pleines

je m'explique

j'ai une colonne qui ressemble à ca :

A1 = 15
A2 = 13
A3 = 118
A4 = 223
A5 =
A6 = 112
A7 = 433
A8 =
A9 = 34
A10 = 44
A11 =555
A12 = 777

j'aimerais donc qu'une formule placée par exemple en B1, B5, B8 (ou ailleurs) soit en mesure de calculer non pas la somme de toute la colonne a mais la somme de A1:A4 - une autre la Somme de A6:A7 et une autre A9:A12.
Si je rajoute une donnée en A2, et que la première cellule vide devient A6, que la somme soit dont faite sur A1:A5.

si je n'ai pas été clair :
Le but final est d'avoir en C1 une formule du type =si(A1="";somme A1:A4;"rien")
sauf que mon tableau bouge tout le temps et que demain il est possible que la cellule vide ne se trouve qu'en A6. d'ou l'utilité d'une fonction qui ne prend que les groupes de cellules pleines


... je ne sais pas si je suis très clair...mais j'ai essayé :o) (et je me dis que c est peut etre encore le titre qui présente le mieux ma question !
Merci d'avance
Fred

[DarkVader]

Par exemple

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Function subSum(plage As Range)
    Dim tmp(), x As Long, a As Long, b As Long, ret() As Double, iSum As Double
 
    tmp() = plage
    a = LBound(tmp): b = UBound(tmp)
    ReDim ret(a To b + 1, 1 To 1)
 
    For x = a To b
        If tmp(x, 1) <> "" Then
            iSum = iSum + tmp(x, 1)
        Else
            ret(x, 1) = iSum
            iSum = 0
        End If
    Next
    If iSum <> 0 Then ret(x, 1) = iSum
 
    subSum = ret()
End Function

avec une formule matricielle du style :
{=SI(subsum(A1:A12)<>0;subsum(A1:A12);"")}

[FreddyNB]

Ok merci rien de possible sans macro ?

[FreddyNB]

en fait ...j ai collé la macro dans this workbook, j'ai copié le code suivant dans une cellule =SI(subsum(A1:A12)<>0;subsum(A1:A12);"") et apparait
#NOM?

une idée ?

Merci
Fred

[DarkVader]

Ok merci rien de possible sans macro ?

J'imagine que si avec une formule définissant les plages dynamiquement mais avec de grandes chances d'avoir un truc plus lourd voir plus lent

en fait ...j ai collé la macro dans this workbook, j'ai copié le code suivant dans une cellule =SI(subsum(A1:A12)<>0;subsum(A1:A12);"") et apparait
#NOM?

une idée ?

Merci
Fred

La fonction : dans un mode de code standart.

[helios77]

Soit j'ai pas compris ce que tu veux faire soit c'est super simple

Dans tes cellules
A5, A8, A13, tu mets respectivement
=SOMME(A1:A4)
=SOMME(A6:A7)
=SOMME(A9:A12)

Normalement, la formule qui travaile sur A5 pour le moment (car c'est la case de ta somme) se mettra à jour en cas d'ajout de lignes pour travaillersur la A6 par exemple

[FreddyNB]

Lool ...je n'ai pas du etre clair et je craignais cette réponse
en fait, le tableau de base est un tablea à réactualiser (exctract SAP)...

en fait la colonne que j ai appelé A pour simplifier est la colonne G, elle contient cette formule :
=SI(G61="ZM";D61;SI(G61="ZP";E61;""))

en gros si dans la colonne A il y a ZM, il renvoie la colonne D, si ZP, la colonne E ...sinon il renvoie "" ...

en réalité ma colonne A contient donc :

Rubrique1
ZP20
Rubrique 2
ZM69
ZM65
ZM65
ZM65
ZM65
ZM65
ZM63
ZM62
ZM61
ZM61
ZM61
ZM61
ZM61
Rubrique 3
ZP09
ZP09
Rubrique 4
ZM67
Rubrique 5
ZM53
ZM53
ZM53
ZM53
ZM54
ZM52

mais le terme rubrique change de position en A chaque fois que je réactualise les données, il est donc nécessaire d'arreter le compte a chaque fois que autre chose que ZM ou ZP est rencontré ... (bien entendu, le titre de chaque rubrique n'est pas "rubrique" ...sinon ce serait trop facile ) héhé

[FreddyNB]

La fonction : dans un mode de code standart.

heu j'ai bien peur de ne pas avoir saisi là

[DarkVader]

Pardon - au temps pour moi : dans un module de code standart (Alt IM)

[David2304]

Bonjour,

Tu trouveras en pièce jointe une méthode sans macro,

Pour info, voici la formule matricielle utilisée (à valider par CTRL+MAJ+ENTREE):

Code :

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

{=SOMME(INDIRECT("A"&LIGNE(A2)&":A"&MIN(SI(A3:$A$29="";LIGNE(A3:$A$29);""))))*SI(A2="";1;0)}

[FreddyNB]

Whaou

Je suis vraiment impressioné ! je vais decortiquer ca et l'analyser. ... et l adapter ... génial, pas simple mais impressionnant

Un grand Merci à TOUS et bonne soirée!
Fred

[DarkVader]

David,
pour le fun, amuses toi à faire le test suivant :
tu appliques ta fonction sur une plage un peu plus conséquente (ne serait-ce que A1:A1000)
puis tu ajoutes une feuille identique utilisant la fonction vba citée plus haut
ensuite tu exécutes la fonction compareFunctions après avoir copié le code suivant dans un module de code standart

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Declare Function GetTickCount Lib "kernel32" () As Long
Dim t0 As Long, t1 As Long, t2 As Long
 
 Sub compareFunctions()
 'comparer les méthodes
    Dim x As Long, r As Double
    Const k = 10
 
    ' Méthode Fonctions de feuille
    t0 = GetTickCount()
    For x = 1 To k
        Application.Worksheets(1).Calculate
    Next
 
    t1 = GetTickCount()
    ' Méthode fonction VBA
    For x = 1 To k
        Application.Worksheets(2).Calculate
    Next
    t2 = GetTickCount()
 
    MsgBox "Méthode avec Fonctions de feuilles : " & (t1 - t0) & vbCrLf & "Méthode avec Fonctions VBA : " & (t2 - t1)
    If t2 <> t1 And t1 <> t0 Then
        r = (t1 - t0) / (t2 - t1)
        MsgBox "La méthode par fonction de feuille est " & IIf(r > 1, Format(r, "###") & " fois moins ", Format(1 / r, "###") & " fois plus ") & "rapide qu'avec la méthode utilisant la fonction vba"
    End If
 End Sub

Ce qui n'enlève en rien la « beauté » de la formule.

[David2304]

DarkVader,

Il existe effectivement plusieurs méthodes beaucoup plus efficaces que ma formule mais certaines personnes ne peuvent utiliser vba au boulot pour causes de sécurité ou sont tout simplement allergiques aux macros

[DarkVader]

Il est tout de même important de préciser pour ceux que cela concerne
que le code VBA (dans ce cas) est 100 à 200 fois plus rapide -
je laisse imaginer pour une plage de 10 000 cellules au lieu de 1 000
ou plus simplement pour un classeur quelque peu conséquent.