Sumproduct dans VBA avec plusieurs variables

Version imprimable

04/08/2014, 17h16
ADV974

1 pièce(s) jointe(s)

Sumproduct dans VBA avec plusieurs variables

Bonjour,

Je suis novice en programmation VBA et je cherche à calculer le nombre de dossiers présentant la référence X (Variable numérique) avec un montant compris entre un seuil_mini et un seuil maxi. Je souhaiterai passer par la fonction evaluate / sumproduct pour cela.

J'ai bien sûr tenté de me débrouiller par mes propres moyens mais je pense me mélanger les pinceaux avec les ""...:calim2:

Pièce jointe 152769

Dans le fichier joint, je souhaiterais que ma macro affiche en cellule C4 le nombre 18 (comme le calcule la sommeprod dans la cellule B4). En réalité, je voudrais avoir l'équivalent de la formule sommeprod de la cellule B4 en VBA.

Si quelqu'un pouvait m'apporter son aide, ce serait super car j'avoue que je désespère un peu...

Merci beaucoup
04/08/2014, 17h25
Philippe Tulliez

Bonjour,
Plutôt que de laisser le soin aux intervenants d'ouvrir ton fichier et de commencer à lire et à analyser ce que tu as écrit comme code, il serait plus rapide de pouvoir le lire sur cette discussion et au moins tout le monde profiteraient des questions et réponses.
S'il le faut, une courte explication de la manière dont sont organisées tes données et un commentaire pour les variables utilisées.
Tous les intervenants de ce forum n'ouvrent pas systématiquement les classeurs "UpLoadés".

Bonjour,

Désolée, je ne savais pas trop comment procéder mais je comprends ta remarque. Voici donc le code que j'ai commencé à rédiger:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub Test()
'Définition des variables : Seuils mini et maxi, N° de la référence à considérer
Dim Seuil_Mini As Integer, Seuil_Maxi As Integer, Référence As Integer
 
Windows("Base_Chiffre.xls").Activate
Seuil_Mini = Sheets("REPORTING").Range("B1")
Seuil_Maxi = Sheets("REPORTING").Range("B2")
Référence = Sheets("REPORTING").Range("E1")
 
With Workbooks("Base_Chiffre.xls").Sheets("base")
    Set Plage1 = .Range("$A$1:$A$10000")   'Plage contenant les montants 
Set Plage2 = .Range("$B$1:$B$10000")   'Plage contenant les références
End With
 
'Calcul du nombre de dossiers présentant la référence sélectionnée et le montant compris entre le
'seuil mini et le seuil maxi sélectionnés
 
nbdossiers = Evaluate("sumproduct((" & Plage1.Address& "="&Référence&") * ("&Plage2.address&" >= "&Seuil_Mini&") * ("&Plage2.Address&" <= "&Seuil_Maxi&))")
Workbooks("Base_Chiffre.xls").Sheets("REPORTING").Select
 
Range("C4").Value = nbdossiers
 
End Sub

Bonjour,
Alors, j'ai Copier/Coller ton code dans un module et j'ai tout de suite eu une erreur de syntaxe avec cette ligne

Code:

nbdossiers = Evaluate("sumproduct((" & Plage1.Address& "="&Référence&") * ("&Plage2.address&" >= "&Seuil_Mini&") * ("&Plage2.Address&" <= "&Seuil_Maxi&))")

peut-être un problème de parenthèses. Je n'ai pas vraiment cherché.

La gestion des String dans une chaîne de caractères lorsque l'on doit insérer des variables ne facilite pas effectivement la lecture.
J'opte pour ma part pour des balises.
Les données
Pour mon test, j'ai une feuille nommée bd contenant une liste de données à évaluer et une feuille nommée également Reporting
Dans la feuille bd, la plage contenant les montants se trouve en E2:E101, les références (des type de logements) en L2:L101.
Les paramètres se trouve sur la feuille Reporting en cellule B2, la valeur Minimum (pour l'exemple 2000), en C2, la valeur Maximum (pour l'exemple 3000) et en D2, la référence (pour l'exemple "Villa"
La formule
Manuellement, la formule placée dans une cellule quelconque de la feuille Reporting est

Code:

=SOMMEPROD( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)

La formule évaluée en VBA (sans les balises)

Code:

MsgBox Evaluate("SUMPRODUCT( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)")

La formule en VBA avec les balises
En vba, comme indiqué plus haut, lorsqu'il y a des paramètres, j'insère des balises (Exemple <NomParametre>) que je remplace ensuite par les paramètres à l'aide de la fonction Replace
donc la chaîne de caractères contenant la formule placée dans la variable myFormula est pour notre exemple

Code:

"SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)"

La procédure
Code:

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 Sub TestEvaluate() Dim rngMontant As Range, rngRef As Range Dim Seuil_Mini As Double, Seuil_Maxi As Double, Reference As String Dim myFormula As String myFormula = "SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)" With ThisWorkbook.Worksheets("REPORTING") Seuil_Mini = .Range("B2") Seuil_Maxi = .Range("C2") Reference = Chr(34) & .Range("D2") & Chr(34) End With With ThisWorkbook.Sheets("db") Set rngMontant = .Range("$E$2:$E$101") 'Plage contenant les montants Set rngRef = .Range("$L$2:$L$101") 'Plage contenant les références End With ' Remplacement des balises myFormula = Replace(myFormula, "<plageMontant>", rngMontant.Address(external:=True)) myFormula = Replace(myFormula, "<plageRef>", rngRef.Address(external:=True)) myFormula = Replace(myFormula, "<Ref>", Reference) myFormula = Replace(myFormula, "<Mini>", Seuil_Mini) myFormula = Replace(myFormula, "<Maxi>", Seuil_Maxi) Debug.Print myFormula ' =SOMMEPROD( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101) 'MsgBox Evaluate("SUMPRODUCT( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)") MsgBox Evaluate(myFormula) End Sub
La formule telle qu'elle va être évaluée (après les remplacements)
Après le Replace voici ce que donne la formule qui va être évaluée (variable myFormula)

Code:

SUMPRODUCT( ('[Liste de données1]db'!$L$2:$L$101="Villa") * ('[Liste de données1]db'!$E$2:$E$101>=2000) * ('[Liste de données1]db'!$E$2:$E$101<=3000) * '[Liste de données1]db'!$E$2:$E$101)

Il reste à adapter avec ton cas précis mais ayant pris pratiquement le même exemple, je pense que cela sera relativement simple.
Dans l'exemple, les paramètres mis dans les variables Seuil_Mini et Seuil_Maxi ainsi que Reference sont les valeurs des cellules comme dans ton code mais pour ma part, j'aurais opté pour les références aux cellules. (objet Range)

[EDIT]
J'ai rectifié la formule en ajoutant un bloc With...End With (lignes 7 à 11)

06/08/2014, 08h32
ADV974

Bonjour,

Avant toute chose, un grand merci pour ton aide et pour le temps que tu as pris pour rédiger tes explications très claires !!! Je l'ai adapté à mon cas et cela marche parfaitement bien.

Je ne connaissais pas du tout cette façon de faire et, crois moi, je m'en souviendrai!

Encore merci
06/08/2014, 08h53
Philippe Tulliez

Bonjour,
Merci pour ton retour et ravi d'avoir pu t'aider.
Il y a un bémol dans cette procédure qu'il faudra peut être rectifiée pour n'envoyer à la formule que les adresses Feuille!Cellules. En effet, si la chaîne de caractères évaluée dépasse 255 caractères la formule renvoie une erreur.
Je l'ai constaté après avoir sauvé le classeur de test avec un nom très long.
Je m'explique.
Dans la construction de la référence à certaines plages (Montant et référence - Lignes 17 & 18), on utilise la propriété Address avec comme valeur True à l'argument nommé external

Code:

rngMontant.Address(external:=True)

Cet argument renvoie donc la référence complète [Classeur]Feuille!Reférence. Ce qui peux donner plus de 255 caractères donc en utilisant uniquement feuille!Reference on pourrait réduire le nombre de caractères.
Je n'ai pas une grande expérience de la fonction Evaluate et j'ai supposé que le problème venait d'une limitation des caractères n'ayant pas eu l'occasion d'approfondir la question.
Un autre intervenant plus expérimenté sur cette question pourra peut-être nous éclairer mes quelques recherches sur ce sujet n'ayant pas abouti.

Bonjour, je reviens sur cette discussion car j'essaye d'appliquer cette méthode néanmoins j'ai un message d'erreur, si vous pourriez m'aider j'en serais très reconnaissante car je galère un peu.

Alors pour simplifier,

j'ai une table avec une ligne ou il y a des mois
et une ligne qui donne les valeurs pour chaque mois.

J'aimerais faire une sommeprod avec condition sur le mois pour faire apparaitre la valeur
j'ai appliqué votre code sur un test simple de sommeprod et il y a une erreur 13 'incompatibilité de format

voici le code:

Sub Macro1()

Dim données As Range, mois As Range

Dim myformula As String

Dim moisref As String

myformula = "SUMPRODUCT( (<plage mois>=<Ref>)*<plage données>)"

With ThisWorkbook.Worksheets("Feuil1")
moisref = .Cells(2, 6)
Set données = .Range("A2:C2")
Set mois = .Range("A1:C1")
End With

myformula = Replace(myformula, "<plage données>", données.Address(external:=True))
myformula = Replace(myformula, "<plage mois>", mois.Address(external:=True))
myformula = Replace(myformula, "<Ref>", moisref)

Debug.Print myformula
MsgBox Evaluate(myformula)

End Sub

merci d'avance pour votre aide
Citation:
Envoyé par Philippe Tulliez
Bonjour,
Alors, j'ai Copier/Coller ton code dans un module et j'ai tout de suite eu une erreur de syntaxe avec cette ligne

Code:

nbdossiers = Evaluate("sumproduct((" & Plage1.Address& "="&Référence&") * ("&Plage2.address&" >= "&Seuil_Mini&") * ("&Plage2.Address&" <= "&Seuil_Maxi&))")

peut-être un problème de parenthèses. Je n'ai pas vraiment cherché.

La gestion des String dans une chaîne de caractères lorsque l'on doit insérer des variables ne facilite pas effectivement la lecture.
J'opte pour ma part pour des balises.
Les données
Pour mon test, j'ai une feuille nommée bd contenant une liste de données à évaluer et une feuille nommée également Reporting
Dans la feuille bd, la plage contenant les montants se trouve en E2:E101, les références (des type de logements) en L2:L101.
Les paramètres se trouve sur la feuille Reporting en cellule B2, la valeur Minimum (pour l'exemple 2000), en C2, la valeur Maximum (pour l'exemple 3000) et en D2, la référence (pour l'exemple "Villa"
La formule
Manuellement, la formule placée dans une cellule quelconque de la feuille Reporting est

Code:

=SOMMEPROD( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)

La formule évaluée en VBA (sans les balises)

Code:

MsgBox Evaluate("SUMPRODUCT( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)")

La formule en VBA avec les balises
En vba, comme indiqué plus haut, lorsqu'il y a des paramètres, j'insère des balises (Exemple <NomParametre>) que je remplace ensuite par les paramètres à l'aide de la fonction Replace
donc la chaîne de caractères contenant la formule placée dans la variable myFormula est pour notre exemple

Code:

"SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)"

La procédure
Code:

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 Sub TestEvaluate() Dim rngMontant As Range, rngRef As Range Dim Seuil_Mini As Double, Seuil_Maxi As Double, Reference As String Dim myFormula As String myFormula = "SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)" With ThisWorkbook.Worksheets("REPORTING") Seuil_Mini = .Range("B2") Seuil_Maxi = .Range("C2") Reference = Chr(34) & .Range("D2") & Chr(34) End With With ThisWorkbook.Sheets("db") Set rngMontant = .Range("$E$2:$E$101") 'Plage contenant les montants Set rngRef = .Range("$L$2:$L$101") 'Plage contenant les références End With ' Remplacement des balises myFormula = Replace(myFormula, "<plageMontant>", rngMontant.Address(external:=True)) myFormula = Replace(myFormula, "<plageRef>", rngRef.Address(external:=True)) myFormula = Replace(myFormula, "<Ref>", Reference) myFormula = Replace(myFormula, "<Mini>", Seuil_Mini) myFormula = Replace(myFormula, "<Maxi>", Seuil_Maxi) Debug.Print myFormula ' =SOMMEPROD( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101) 'MsgBox Evaluate("SUMPRODUCT( (db!$L$2:$L$101=$D$2) * (db!$E$2:$E$101>=$B$2) * (db!$E$2:$E$101<=$C$2) * db!$E$2:$E$101)") MsgBox Evaluate(myFormula) End Sub
La formule telle qu'elle va être évaluée (après les remplacements)
Après le Replace voici ce que donne la formule qui va être évaluée (variable myFormula)

Code:

SUMPRODUCT( ('[Liste de données1]db'!$L$2:$L$101="Villa") * ('[Liste de données1]db'!$E$2:$E$101>=2000) * ('[Liste de données1]db'!$E$2:$E$101<=3000) * '[Liste de données1]db'!$E$2:$E$101)

Il reste à adapter avec ton cas précis mais ayant pris pratiquement le même exemple, je pense que cela sera relativement simple.
Dans l'exemple, les paramètres mis dans les variables Seuil_Mini et Seuil_Maxi ainsi que Reference sont les valeurs des cellules comme dans ton code mais pour ma part, j'aurais opté pour les références aux cellules. (objet Range)

[EDIT]
J'ai rectifié la formule en ajoutant un bloc With...End With (lignes 7 à 11)