Agrégation sous conditions

Bonjour,

J'ai un point de blocage sur la mise en place d'une formule excel dont le but est d'agréger des montants en fonction d'une condition.

La base de données est composée de la façon suivante :

  • colonne A : des numéros de compte qui peuvent être itérés (voir exemple : 1/1/1/2/2/3/3) ;
  • colonne B : d'un numéro de tiers, qui peut être itérés lui aussi (Tiers 1/ Tiers 1/Tiers 2/...) ;
  • colonne C : des montants pour chacune des lignes, dépendant des deux critères précédents.

Mon objectif est de faire l'agrégation par tiers des montants, en ne tenant compte que des comptes ayant un total positif.

Exemple :

Pour le tiers 1, on constate que :

  • la somme pour le compte 1 est positive (=20) -> on la prendra donc en compte dans l'agrégation.
  • la somme pour le compte 3 est négative (=-40) -> on ne la prendra donc pas en compte dans l'agrégation.
Le résultat de l'agrégation pour le tiers 1, doit donc être 20 (compte 1) + 30 (compte 2 = 50.

Vous trouverez ci-joint un fichier excel avec la "base".

Je vous remercie d'avance.

4classeur1.xlsx (9.22 Ko)

Bonjour,

Je ne suis pas sûr de bien comprendre donc, j'attend ton retour !

Formule à mettre en cellule G5 et tirer vers le bas :=SOMMEPROD(($B$5:$B$11=F5)*($C$5:$C$11>0);$C$5:$C$11)

Merci pour ton retour.

Il te manque un argument dans ta formule car tu indique "si somme des montants Tiers 1 > 0 alors somme des montants Tiers 1".

Mon objectif est d'inclure dans l'agrégation uniquement la somme des comptes ayant un total > 0 pour le tiers 1.

ex: la somme du compte 3 pour le tiers 1 est <0 (-40), donc il ne faut pas en tenir compte dans l'agrégation.

Donc, il te faut le compte n° 1, le tiers 1 et un montant > 0 :

=SOMMEPROD(($A$5:$A$11=1)*($B$5:$B$11=F5)*($C$5:$C$11>0);$C$5:$C$11)

Le problème c'est que le compte 2 pour le tiers 1 est positif aussi du coup il faut le prendre en compte aussi.

En fait, mon idée finale est de faire un calcul intermédiaire (sans utiliser d'autres onglets/champs) afin de définir quelles sont les valeurs à prendre en compte dans l'agrégation finale.

Cependant, il faut partir du principe que je pourrais avoir "x" comptes différents dont je ne connaîtrais pas les intitulés à l'avance. C'est pour cela que j'ai évité ce type de formule qui oblige à nommer le n° de compte.*

En fait, je ne suis pas certain que cela soit faisable via une simple formule excel :/

Comme il faut prendre en compte les différents comptes (ici 3) et que pour être additionnés ensemble, il faut que chaque compte soit, de façon indépendante, positif, je suis parti sur trois sommeprod() avec conditions Si(), il y a sûrement plus simple mais là, je sèche :

=SI(SOMMEPROD(($A$5:$A$11=1)*($B$5:$B$11=F5);$C$5:$C$11)<0;0;SOMMEPROD(($A$5:$A$11=1)*($B$5:$B$11=F5);$C$5:$C$11))+SI(SOMMEPROD(($A$5:$A$11=2)*($B$5:$B$11=F5);$C$5:$C$11)<0;1;SOMMEPROD(($A$5:$A$11=2)*($B$5:$B$11=F5);$C$5:$C$11))+SI(SOMMEPROD(($A$5:$A$11=3)*($B$5:$B$11=F5);$C$5:$C$11)<0;0;SOMMEPROD(($A$5:$A$11=3)*($B$5:$B$11=F5);$C$5:$C$11))

J'ai pondu la fonction perso ci-dessous qui est à mettre dans un module standard :

Function TotalCompte(Plage As Range, Nom As String) As Double

    Dim Dico1 As Object
    Dim Dico2 As Object
    Dim Cel As Range
    Dim Cle As Variant
    Dim I As Integer

    'crée les deux dictionnaires
    Set Dico1 = CreateObject("Scripting.Dictionary")
    Set Dico2 = CreateObject("Scripting.Dictionary")

    'additionne les valeurs pour chaque compte
    For Each Cel In Plage.Columns(1).Cells

        I = I + 1: Dico1(Cel.Value & "_" & Cel.Offset(, 1).Value) = _
                   Dico1(Cel.Value & "_" & Cel.Offset(, 1).Value) + _
                   Plage.Columns(3).Cells(I).Value

    Next Cel

    I = 0

    'transfère dans le second dicotionnaire les comptes positifs pour chacun des noms
    For Each Cle In Dico1.Keys

        If Dico1(Cle) > 0 Then
            Dico2(Split(Cle, "_")(1)) = Dico2(Split(Cle, "_")(1)) + Dico1(Cle)
        End If

    Next Cle

    'retourne le montant du nom passé en argument
    For Each Cle In Dico2.Keys

        If Cle = Nom Then TotalCompte = Dico2(Cle): Exit For

    Next Cle

    Set Dico1 = Nothing
    Set Dico2 = Nothing

End Function

Qui est appelée dans Excel de la façon suivante : =TotalCompte( $A$5:$C$11;F5)

La plage passée en argument doit être construite comme tu l'as fait dans ton fichier (Compte, Tiers et Montant)

Avec cette fonction, tu t'affranchis des numéros de compte qu'il te faudrait rajouté dans la formule que je t'ai donné

Rechercher des sujets similaires à "agregation conditions"