Somme avec différentes conditions

[khazar49]

Bonjour à tous,

J'ai un tableau de travail avec plusieurs lignes de référence qui comprennent chacune tout un ensemble d'informations.

J'aimerai créé au bout de cette ligne une formule somme qui serait identique sur toute les lignes, seulement le résultat que je souhaite est différent en fonction des renseignement de la ligne.

Je m'explique:

-Si dans la colonne A j'ai la valeur "T" (Ligne de Titre), Alors je souhaite avoir la somme des valeurs dans les lignes qui se trouvent en dessous et qui contiennent la valeur "ST" (sous titre) dans la colonne A. Mais seulement jusqu'à la prochaine ligne qui contient la valeur "T" dans la colonne A (non inclus). Et ainsi de suite

-Si dans la colonne A j'ai la valeur "ST", alors je souhaite avoir la somme des valeurs dans les lignes qui se trouvent en dessous et qui contiennent une autre valeur que "T" ou "ST" dans la colonne A. Mais seulement jusqu'à la prochaine ligne qui contient la valeur "ST" dans la colonne A (non inclus. Et ainsi de suite

-Si dans la colonne A j'ai une autre valeur que "T" ou "ST" alors je souhaite seulement avoir la somme des valeurs de la ligne qui se trouve dans la colonne G et H

Le plus important dans tout ça est d'avoir exactement la même formule qui prend en compte toute les conditions.

En espérant que quelqu'un pourra m'aider à résoudre mon problème.

Ps: Je vous joint un exemple de mon tableau excel

 

[Modeste]

Re : Somme avec différentes conditions

Bonsoir khazar49,

Pour autant que j'aie bien compris, essaie (en K2, pour pouvoir comparer):

=SOMME(SI(A2="T";DECALER(G2;1;0;SI(NB.SI(A3:A$100;"T")>0;EQUIV("T";A3:A$100;0);100);2);SI(A2="ST";DECALER(G2;1;0;SI(NB.SI(A3:A$100;"ST")>0;EQUIV("ST";A3:A$100;0);100);2);G2:H2)))

 

[job75]

Re : Somme avec différentes conditions

Bonsoir khazar49, Modeste,

Avec cette fonction VBA on se prend moins la tête :

Function STotal#(c As Range, colonnes As Range)
Dim i&
i = 2
If c = "T" Then
  While c(i) <> "T" And c(i) <> ""
    If c(i) <> "ST" Then STotal = STotal + _
      Application.Sum(Intersect(c(i).EntireRow, colonnes))
    i = i + 1
  Wend
ElseIf c = "ST" Then
  While c(i) <> "ST" And c(i) <> "T" And c(i) <> ""
    STotal = STotal + Application.Sum(Intersect(c(i).EntireRow, colonnes))
    i = i + 1
  Wend
Else
  STotal = Application.Sum(Intersect(c.EntireRow, colonnes))
End If
End Function

Formule unique en I2 à copier vers le bas :

=STotal(A2;G:H)

Fichier joint.

Bonne fin de soirée.

 

[job75]

Re : Somme avec différentes conditions

Re,

Message annulé, j'essayais de gagner du temps pour "T" mais ce n'est pas bon.

Bonne nuit.

 

[job75]

Re : Somme avec différentes conditions

Bonjour le fil, le forum,

Ceci est nettement mieux :

Function STotal#(c As Range, colonnes As Range)
Dim i&
i = 2
If c = "T" Then
  While c(i) <> "T" And c(i) <> ""
    i = i + 1
  Wend
  If i > 2 Then STotal = Application.Sum(Intersect(c(2).Resize(i - 2).EntireRow, colonnes))
ElseIf c = "ST" Then
  While c(i) <> "ST" And c(i) <> "T" And c(i) <> ""
    i = i + 1
  Wend
  If i > 2 Then STotal = Application.Sum(Intersect(c(2).Resize(i - 2).EntireRow, colonnes))
Else
  STotal = Application.Sum(Intersect(c.EntireRow, colonnes))
End If
End Function

Sur Win 8 Excel 2013 le fichier (1) se calcule en 2,8 millisecondes, ce fichier (2) en 1,7 milliseconde.

Bonne journée.

 

[Modeste]

Re : Somme avec différentes conditions

Bonjour le forum, khazar49,
Salut job

Avec cette fonction VBA on se prend moins la tête

... ça m'en a tout l'air

 

[job75]

Re : Somme avec différentes conditions

Re,

Bon cher Modeste j'aime quand même bien me casser la tête

Voici une solution sans VBA dans le fichier joint.

Voyez les 7 noms définis P ligT ligST dligT dligST derligT derligST et la formule en I2 :

=SOMME(DECALER(G2:H2;;;SI(A2="T";derligT-LIGNE();SI(A2="ST";derligST-LIGNE();1))))

Comme pour les autres fichiers j'ai mesuré la durée d'exécution des calculs.

Et surprise : cette durée est de 0,33 milliseconde, 5 fois plus rapide que le VBA

A+

 

[job75]

Re : Somme avec différentes conditions

Re Modeste,

J'ai testé ta solution du post #2.

Durée d'exécution 0,16 milliseconde, c'est donc la meilleure solution.

A+

 

[Modeste]

Re : Somme avec différentes conditions

Mon pauvre job

0,16 milliseconde ... c'est une blague! Et si les températures redescendent la semaine prochaine, il faudra tenir compte d'un facteur de dilatation qui empêchait la fonction de se mouvoir librement (ou le contraire!?)
Tu auras sans doute constaté que je limite à 100 le nombre cellules à prendre en considération. Sur une plage plus étendue, le résultat sera sans aucun doute différent!

Et depuis que je te lis chronométrer les macros et formules, je me demande: il doit tout de même y avoir des facteurs extérieurs qui influencent la rapidité d'exécution, non? Si d'autres tâches tournent en arrière plan, le temps alloué par le processeur à chacune de celles-ci intervient forcément. La même macro s'exécutera dans des conditions différentes et le temps risque fort de varier ... ou alors me trompe-je (à moins que tu ne programmes l'exécution un millier de fois pour chaque fonction/macro et que tu établisses une moyenne, les jours où tu t'ennuies? )

 

[job75]

Re : Somme avec différentes conditions

Re Modeste,

Je compare très souvent les durées d'exécution des diverses solutions car sur de grands tableaux il vaut mieux prendre la solution la plus rapide.

De plus c'est très instructif.

Pour tester ici :

1) j'ai rendu volatiles les fonctions VBA, pour les formules DECALER les rend volatiles

2) j'exécute cette macro :

Sub test()
Dim t, i
Application.ScreenUpdating = False
t = Timer
For i = 1 To 10000
Calculate
Next
MsgBox Timer - t
End Sub

A+

 

[khazar49]

Re : Somme avec différentes conditions

Bonjour et merci à tous pour vos différentes solutions.

La solution de job75 sans macro me plait bien et je vais la tester sur mon fichier qui fait 22000 lignes.

Je préfère cette solution car j'ai besoin d'un résultat en temps réel et je rejoins job75 sur l'importance d'avoir un calcul rapide.

Dès que j'aurai fais la modification sur mon fichier je vous informerai du temps de réponse pour le calcul des 22000 lignes.

Merci pour votre aide!

 

[khazar49]

Re : Somme avec différentes conditions

Job75,

Je suis un peut perdu sur la définition des noms.

Mon tableau contient un Format supplémentaire que je pensais pouvoir gérer par moi même, seulement les formules que tu utilises me dépasse.

Dans la colonne A j'ai un format supplémentaire que j'ai nommé "R"

Cette valeur est traité de la même façon que pour la valeur "ST" mais n'affecte pas le résultat pour la valeur "T".

C'est à dire:

-Si dans la colonne A j'ai la valeur "R", alors je souhaite avoir la somme des valeurs dans les lignes qui se trouvent en dessous et qui contiennent une autre valeur que "T" , "ST" ou "R" dans la colonne A. Mais seulement jusqu'à la prochaine ligne qui contient la valeur "T" , "ST" ou "R" dans la colonne A (non inclus)

Peux-tu m'aider à modifier ta formule en fonction de cette nouvelle valeur?

Merci à toi

 

[job75]

Re : Somme avec différentes conditions

Bonjour khazar49,

Pas clair sans fichier...

Mais la logique de construction des noms définis au post #7 est facile à capter non ?

Donc avec en plus la lettre "R" on peut définir les 3 noms ligR dligR derligR.

La formule en I2 deviendrait :

=SOMME(DECALER(G2:H2;;;SI(A2="R";derligR-LIGNE();SI(A2="T";derligT-LIGNE();SI(A2="ST";derligST-LIGNE();1)))))

A+

 

[khazar49]

Re : Somme avec différentes conditions

J'ai réajuster le tableau avec la valeur R en plus.

J'ai testé avec ta nouvelle formule mais je n'arrive pas au résultat souhaité.

Un petit coup de main?

 

[job75]

Re : Somme avec différentes conditions

Re,

Bon d'accord, il faut définir derligR par :

=MIN(derligT;derligST;SI(ESTNUM(dligR);dligR;LIGNES(P)+1))

Fichier (2).

A+