Agrégation sous conditions

Y compris Power BI, Power Query et toute autre question en lien avec Excel
n
naio
Jeune membre
Jeune membre
Messages : 10
Inscrit le : 27 novembre 2018
Version d'Excel : 2013 FR PC

Message par naio » 9 octobre 2019, 18:09

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.
Classeur1.xlsx
Agrégation sous conditions
(9.22 Kio) Téléchargé 4 fois
T
Theze
Passionné d'Excel
Passionné d'Excel
Messages : 4'020
Appréciations reçues : 305
Inscrit le : 26 janvier 2011
Version d'Excel : 2007/2019

Message par Theze » 9 octobre 2019, 18:19

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)
Il vaut mieux un qui sait que cent qui cherchent :wink:

Ce forum étant un lieu de partage, je n'accepte pas les messages privés !
n
naio
Jeune membre
Jeune membre
Messages : 10
Inscrit le : 27 novembre 2018
Version d'Excel : 2013 FR PC

Message par naio » 9 octobre 2019, 18:30

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.
T
Theze
Passionné d'Excel
Passionné d'Excel
Messages : 4'020
Appréciations reçues : 305
Inscrit le : 26 janvier 2011
Version d'Excel : 2007/2019

Message par Theze » 9 octobre 2019, 20:38

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)
Il vaut mieux un qui sait que cent qui cherchent :wink:

Ce forum étant un lieu de partage, je n'accepte pas les messages privés !
n
naio
Jeune membre
Jeune membre
Messages : 10
Inscrit le : 27 novembre 2018
Version d'Excel : 2013 FR PC

Message par naio » 10 octobre 2019, 09:22

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 :/
T
Theze
Passionné d'Excel
Passionné d'Excel
Messages : 4'020
Appréciations reçues : 305
Inscrit le : 26 janvier 2011
Version d'Excel : 2007/2019

Message par Theze » 10 octobre 2019, 17:30

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))
Il vaut mieux un qui sait que cent qui cherchent :wink:

Ce forum étant un lieu de partage, je n'accepte pas les messages privés !
T
Theze
Passionné d'Excel
Passionné d'Excel
Messages : 4'020
Appréciations reçues : 305
Inscrit le : 26 janvier 2011
Version d'Excel : 2007/2019

Message par Theze » 10 octobre 2019, 18:55

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é
Il vaut mieux un qui sait que cent qui cherchent :wink:

Ce forum étant un lieu de partage, je n'accepte pas les messages privés !
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message