VBA – Fonctions SOMME.SI et SOMME.SI.ENS

Ce didacticiel vous montre comment utiliser les fonctions SOMME.SI et SOMME.SI.ENS d’Excel en VBA.

VBA ne dispose pas d’un équivalent des fonctions SOMME.SI ou SOMME.SI.ENS que vous pouvez utiliser dans les feuilles de calcul – l’utilisateur doit utiliser les fonctions Excel intégrées dans VBA en utilisant l’objet WorksheetFunction.

Fonction SOMME.SI avec WorksheetFunction

L’objet WorksheetFunction peut être utilisé pour appeler la plupart des fonctions Excel disponibles dans la boîte de dialogue Insérer une Fonction d’Excel. La fonction SOMME.SI (« SUMIF » en anglais) est l’une d’entre elles. Notez que le nom anglais des fonctions doit être utilisé dans VBA excepté lorsqu’on tente de spécifier textuellement une formule tel que démontré dans la section « Méthode Formula » plus bas.

Sub TestSOMMESI()
Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
End Sub

La procédure ci-dessus n’additionnera les cellules de la plage (D2:D9) que si la cellule correspondante de la colonne C = 150.

Affectation d’un Résultat SOMME.SI à une Variable

Il se peut que vous souhaitiez utiliser le résultat de votre formule ailleurs dans le code plutôt que de le réécrire directement dans une plage Excel. Si c’est le cas, vous pouvez affecter le résultat à une variable que vous utiliserez plus tard dans votre code.

Sub AssignerVariableSOMMESI()
    Dim result As Double
    
    'Assignation de la variable
    résultat = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
    
    'Affichage du résultat
    MsgBox "Le total des ventes avec un code correspondat à 150 est " & résultat
End Sub

Utilisation de la Fonction SOMME.SI.ENS

La fonction SOMME.SI.ENS est similaire à la fonction SOMME.SI, mais elle vous permet de vérifier plusieurs critères. Dans l’exemple ci-dessous, nous cherchons à additionner le prix de vente si le code de vente est 150 ET si le coût de revient est supérieur à 2. Remarquez que dans cette formule, la plage de cellules à additionner se trouve devant le critère, alors que dans la fonction SOMME.SI, elle se trouve derrière.

Sous MultipleSumIfs()
   Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")
End Sub

Utilisation de SOMME.SI avec un Objet Range

Vous pouvez affecter un groupe de cellules à l’objet Range, puis utiliser cet objet Range avec l’objet WorksheetFunction.

Sub TestSOMMESI_Range()
    Dim rngCritère As Range
    Dim rngSomme As Range
    
    'Affectation de la plage de cellules
    Set rngCritère = Range("C2:C9")
    Set rngSomme = Range("D2:D9")
   
    'Utilisation de la plage dans la formule
    Range("D10") = WorksheetFunction.SumIf(rngCritère, 150, rngSomme)
   
    'Libération des objets range
    Set rngCritère = Nothing
    Set rngSomme = Nothing
End Sub

Utilisation de SOMME.SI.ENS sur Plusieurs Objets Range

De même, vous pouvez utiliser la fonction SOMME.SI.ENS avec plusieurs objets range.

Sub TestSOMMESIENS_Range()
    Dim rngCritère1 As Range
    Dim rngCritère2 As Range
    Dim rngSomme As Range
   
    'Affectation la plage de cellules
    Set rngCritère1 = Range("C2:C9")
    Set rngCritère2 = Range("E2:E9")
    Set rngSomme = Range("D2:D9")
   
    'Utilisation des plages dans la formule
    Range("D10") = WorksheetFunction.SumIfs(rngSomme, rngCritère1, 150, rngCritère2, ">2")

    'Libération des objets range
     Set rngCritère1 = Nothing
     Set rngCritère2 = Nothing
     Set rngSomme = Nothing
End Sub

Remarquez que, comme vous utilisez le signe supérieur à, le critère supérieur à 2 doit être entre guillemets.

Formule SOMME.SI

Lorsque vous utilisez la fonction WorksheetFunction.SUMIF pour ajouter une somme à une plage de votre feuille de calcul, vous obtenez une somme statique et non une formule flexible. Cela signifie que lorsque vos chiffres dans Excel changent, la valeur renvoyée par la WorksheetFunction ne change pas.

Dans l’exemple ci-dessus, la procédure a additionné la plage (D2:D9) lorsque le code de vente est égal à 150 dans la colonne C, et le résultat a été placé en D10. Comme vous pouvez le voir dans la barre de formule, ce résultat est un chiffre et non une formule.

Si l’une des valeurs change dans la plage (D2:D9) ou la plage (C2:D9), le résultat en D10 NE changera PAS.

Au lieu d’utiliser la fonction WorksheetFunction.SumIf, vous pouvez utiliser VBA pour appliquer une fonction SOMME.SI à une cellule à l’aide des méthodes Formule ou FormuleR1C1.

Méthode Formula

La méthode formula de l’objet Range vous permet d’indiquer spécifiquement une plage de cellules, par exemple D2:D10 et d’en définir la formule tel qu’illustré ci-dessous.

Sub TestSOMMESI_Formule()
  Range("D10").Formula = "=SUMIF(C2:C9,150,D2:D9)"
End Sub

Remarquez que même si on spécifie textuellement la formule à insérer dans la cellule ou la plage de cellule spécifiée, la fonction SUMIF en anglais doit être utilisée. VBA utilise les noms de formules en anglais, la formule résultante dans votre feuille de calcul sera traduite dans la langue dans laquelle Excel est configuré sur votre système.

Méthode FormulaR1C1

La méthode FormulaR1C1 est plus flexible, car elle ne vous limite pas à une plage de cellules donnée. L’exemple ci-dessous nous donnera la même réponse que celle ci-dessus.

Sub TestSOMMESI_FormuleR1C1()
    Range("D10").FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

Cependant, pour rendre la formule plus flexible, nous pourrions modifier le code de la manière suivante :

Sub TestSOMMESI_FormuleR1C1() 
   ActiveCell.FormulaR1C1 = "=SOMME.SI(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

Où que vous soyez dans votre feuille de calcul, la formule additionnera les cellules qui répondent aux critères directement au-dessus de la cellule active et placera la réponse dans celle-ci. La plage dans la fonction SOMME.SI doit être désignée à l’aide de la syntaxe Row (R) et Column (C).

Ces deux méthodes vous permettent d’utiliser des formules Excel dynamiques dans VBA.

Il y aura maintenant une formule dans D10 au lieu d’une valeur.