IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Sumproduct dans VBA avec plusieurs variables


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre à l'essai
    Profil pro
    Inscrit en
    Août 2012
    Messages
    4
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2012
    Messages : 4
    Par défaut 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 ""...

    Base_Chiffre.XLS

    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

  2. #2
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 166
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 166
    Billets dans le blog
    53
    Par défaut
    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".
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  3. #3
    Membre à l'essai
    Profil pro
    Inscrit en
    Août 2012
    Messages
    4
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2012
    Messages : 4
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  4. #4
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 166
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 166
    Billets dans le blog
    53
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
     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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    "SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)"
    La procédure
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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)
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  5. #5
    Membre à l'essai
    Profil pro
    Inscrit en
    Août 2012
    Messages
    4
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2012
    Messages : 4
    Par défaut
    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

  6. #6
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 166
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 166
    Billets dans le blog
    53
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  7. #7
    Invité de passage
    Femme Profil pro
    Étudiant
    Inscrit en
    Juin 2018
    Messages
    1
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Juin 2018
    Messages : 1
    Par défaut
    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 Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
     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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    "SUMPRODUCT( (<plageRef>=<Ref>) * (<plageMontant>>=<Mini>) * (<plageMontant><=<Maxi>) * <plageMontant>)"
    La procédure
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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)

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Lancer job talend avec plusieurs variables de contexte dans le script .sh
    Par TheRealWill dans le forum Développement de jobs
    Réponses: 2
    Dernier message: 07/06/2010, 10h07
  2. Procédure événementielle avec plusieurs variables
    Par Shadow5 dans le forum Macros et VBA Excel
    Réponses: 13
    Dernier message: 17/04/2008, 15h10
  3. Requête dans VBA avec between
    Par adrien.gendre dans le forum VBA Access
    Réponses: 4
    Dernier message: 25/07/2007, 15h29
  4. UPDATE avec plusieurs variables
    Par melmouj dans le forum Langage SQL
    Réponses: 6
    Dernier message: 20/03/2007, 14h51
  5. insertion dans table avec plusieurs clés étrangères
    Par philippe281281 dans le forum Administration
    Réponses: 2
    Dernier message: 14/06/2006, 18h35

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo