VBA – Fonctions SOMME.SI et SOMME.SI.ENS
In this Article
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.