SOMMEPROD après regroupement

[sand13]

Bonjour à tous,

Je bloque sur une sommeprod et j’apprécierai énormément votre aide:

en colonne A je dispose de codes titres avec des valeurs respectives en B (on peut avoir plusieurs fois le même code titre avec des valeurs différentes)

je souhaiterai une sommeprod qui me garde que les sommes négatives après avoir fait un regroupement par titre.

Par exemple pour 1 titre avec des valeurs +20 et -30 la sommeprod me donne -10 pour ce titre
Par contre pour le titre 2 avec des valeurs +30 -5 et -4 la sommeprod l'exclue car la somme est positive.

Je souhaiterai une sommeprod car je dois au préalable filtrer ces titres en fonction des comptes sur lesquels ils se trouvent.

Merci par avance en espérant que vous pourrez m'aider.

Cordialement.

 

[sand13]

Re : SOMMEPROD après regroupement

enfin une sommeprod ou une autre fonction, je précisai sommeprod car d'après mes petites connaissances c'est ptet la formule la plus appropriée.

par contre je ne peux passer par une étape intermédiaire (par exemple faire des formules en D pour chaque ligne et ensuite faire l'addition de cette colonne) il faudrait que l'ensemble du calcul se fasse dans une seule cellule.

merci beaucoup d'avance

 

[sand13]

Re : SOMMEPROD après regroupement

Personne pour mon soucis ?

 

[david84]

Re : SOMMEPROD après regroupement

Bonjour,
peut-être

=SOMMEPROD((A2:A7="FR011")*B2:B7)

ou

=SOMMEPROD((A2:A7="FR011")*(C2:C7=303133);B2:B7)

s'il est nécessaire de préciser le compte.
A+

 

[Regueiro]

Re : SOMMEPROD après regroupement

Bonjour le Forum.
Au préalable transformer vos données en Tableau
Insertion -> Tableau ( Excel 2010 )
Ensuite :

=SOMME.SI(Tableau1[code titre];"FR013";Tableau1[valeur])

=SOMME.SI.ENS(Tableau1[valeur];Tableau1[code titre];"FR013";Tableau1[compte];"303133")

A+

 

[job75]

Re : SOMMEPROD après regroupement

Bonjour sand13, bonjour David, heureux de te croiser,

Avec cette fonction VBA utilisant l'objet Dictionary :

Function SommeNegative(r As Variant, P As Variant)
Dim d As Object, i&, a
r = r
P = Intersect(P, P.Parent.UsedRange) 'matrice, plus raoide
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(P)
  If P(i, 3) = r Then d(P(i, 1)) = d(P(i, 1)) + P(i, 2)
  Next
a = d.items
For i = 0 To UBound(a)
  If a(i) < 0 Then SommeNegative = SommeNegative + a(i)
Next
End Function

Résultat en H20 bien sûr -10 (et pas -8).

Fichier joint.

Edit : bonjour aussi Regueiro.

A+

 

[sand13]

Re : SOMMEPROD après regroupement

Bonjour et merci pour vos réponses.

Je ne peux pas utiliser VBA ni retoucher les données pour les mettre en tableau.

De plus il me faudrait une formule générique, je veux dire que je ne peux pas faire une formule par titre parce que j'en ai des centaines.

Donc si c'est possible il faudrait que la formule soit du genre:

= sommeprod (( compte = 303133 ) * ( (regroupement des valeurs par code titre) < 0 ) * valeur )

Car pour un même compte donc je peux avoir des codes titres différents (pas de soucis de regroupement à faire) et des codes titres qui se répètent car les titres ont été achetés plusieurs fois mais ont une valeur différente donc il faut qu'au préalable ils soient regroupés pour ensuite être filtrés avec la sommeprod

 

[job75]

Re : SOMMEPROD après regroupement

Bonsoir sand13,

Il me paraît très douteux que vous trouviez une formule qui fasse ce que fait le code VBA.

Car le problème n'est pas linéaire.

A+

 

[sand13]

Re : SOMMEPROD après regroupement

D'accord job75 et bien je vais utiliser votre code VBA mais est-il possible de rajouter un champ dans la formule afin de pouvoir indiquer quelle colonne de valeur il faut sommer ?

De plus, est-il possible d'intégrer cette fonction à Excel ou vais-je devoir copier le code dans chaque classeur où je vais en avoir besoin ?

D'avance merci

Edit: par contre si je modifie une des valeurs dans le tableau la formule l'ajoute en plus au lieu de remplacer

 

[job75]

Re : SOMMEPROD après regroupement

Bonjour Sand13,

Si l'on veut pouvoir modifier l'ordre des 3 colonnes on peut entrer leurs positions en arguments :

Function SommeNegative(r As Variant, P As Variant, ColTitre%, ColValeur%, ColCompte%)
Dim d As Object, i&, a
r = r
P = Intersect(P, P.Parent.UsedRange) 'matrice, plus rapide
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(P)
  If P(i, ColCompte) = r Then d(P(i, ColTitre)) = d(P(i, ColTitre)) + P(i, ColValeur)
Next
a = d.items
For i = 0 To UBound(a)
  If a(i) < 0 Then SommeNegative = SommeNegative + a(i)
Next
End Function

La 1ère colonne de la plage entrée en argument a le numéro 1.

Fichier (2) avec en H20 =SommeNegative(G$20;A:C;1;2;3)

Nota : le code doit être placé dans un module standard de chaque fichier qui utilise cette fonction.

A+

 

[sand13]

Re : SOMMEPROD après regroupement

merci beaucoup job75 c'est niquel !

 

[job75]

Re : SOMMEPROD après regroupement

Re,

Pour ne pas entrer le code de la fonction dans chaque fichier vous pouvez procéder comme suit :

- placez ce code dans un module d'un nouveau document (vierge)

- nommez le document "MesFonctions" et enregistrez-le au format "Macro complémentaire Microsoft Office Excel" (extension .xla ou .xlam)

- chargez cette macro complémentaire dans Excel (cherchez comment faire sur le forum ou le web).

Ainsi ce fichier sera chargé à chaque ouverture d'Excel et les formules avec SommeNegative se calculeront.

A+