Ce tutoriel va vous montrer comment utiliser les fonctions Excel SUMIF et SUMIFS en VBA
VBA n'a pas d'équivalent des fonctions SUMIF ou SUMIFS que vous pouvez utiliser - un utilisateur doit utiliser les fonctions Excel intégrées dans VBA en utilisant le Fonction Feuille de Travail objet.
Fonction Feuille de calcul SUMIF
L'objet WorksheetFunction peut être utilisé pour appeler la plupart des fonctions Excel disponibles dans la boîte de dialogue Insérer une fonction dans Excel. La fonction SUMIF en fait partie.
| 123 | Sous-TestSumIf()Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))Fin du sous-marin |
La procédure ci-dessus n'ajoutera les cellules de la plage (D2:D9) que si la cellule correspondante de la colonne C = 150.
Affectation d'un résultat SUMIF à une variable
Vous souhaiterez peut-être utiliser le résultat de votre formule ailleurs dans le code plutôt que de l'écrire directement dans la plage Excel. Si tel est le cas, vous pouvez affecter le résultat à une variable à utiliser plus tard dans votre code.
| 1234567 | Sub AssignSumIfVariable()Dim résultat comme Double'Attribuer la variablerésultat = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))'Afficher le résultatMsgBox "Le total du résultat correspondant au code de vente 150 est " & résultatFin du sous-marin |
Utiliser SUMIFS
La fonction SUMIFS est similaire à SUMIF WorksheetFunction 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 le prix de revient est supérieur à 2. Notez que dans cette formule, la plage de cellules à additionner est devant les critères, alors que dans la fonction SUMIF, il est en retard.
| 123 | Sous MultipleSumIfs()Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")Fin du sous-marin |
Utiliser SUMIF avec un objet Range
Vous pouvez affecter un groupe de cellules à l'objet Range, puis utiliser cet objet Range avec le Feuille de travailFonction objet.
| 123456789101112 | Sous-TestSumIFRange()Dim rngCriteria As RangeDim rngSum comme plage'attribuer la plage de cellulesDéfinir rngCriteria = Range("C2:C9")Définir rngSum = Range("D2:D9")'utiliser la plage dans la formuleRange("D10") = WorksheetFunction.SumIf(rngCriteria, 150, rngSum)'libérer les objets de la plageDéfinir rngCriteria = RienDéfinir rngSum = RienFin du sous-marin |
Utilisation de SUMIFS sur plusieurs objets de plage
De même, vous pouvez utiliser SUMIFS sur plusieurs objets Range.
| 123456789101112131415 | Sub TestSumMultipleRanges()Dim rngCriteria1 As RangeDim rngCriteria2 comme plageDim rngSum comme plage'attribuer la plage de cellulesDéfinir rngCriteria1= Range("C2:C9")Définir rngCriteria2 = Range("E2:E10")Définir rngSum = Range("D2:D10")'utiliser les plages dans la formuleRange("D10") = WorksheetFunction.SumIfs(rngSum, rngCriteria1, 150, rngCriteria2, ">2")'libérer l'objet rangeDéfinir rngCriteria1 = RienDéfinir rngCriteria2 = RienDéfinir rngSum = RienFin du sous-marin |
Notez que parce que vous utilisez un signe supérieur à, les critères supérieurs à 2 doivent être entre parenthèses.
Formule SUMIF
Lorsque vous utilisez le WorksheetFunction.SUMIF pour ajouter une somme à une plage dans votre feuille de calcul, une somme statique est renvoyée, pas une formule flexible. Cela signifie que lorsque vos chiffres dans Excel changent, la valeur renvoyée par le Feuille de travailFonction ne changera pas.
Dans l'exemple ci-dessus, la procédure a ajouté Range(D2:D9) où le SaleCode est égal à 150 dans la colonne C, et le résultat a été mis dans 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 Range(D2:D9) ou Range(C2:D9), le résultat dans D10 sera NE PAS monnaie.
Au lieu d'utiliser le WorksheetFunction.SumIf, vous pouvez utiliser VBA pour appliquer une fonction SUMIF à une cellule en utilisant le Formule ou FormuleR1C1 méthodes.
Méthode de formule
La méthode de formule vous permet de pointer spécifiquement vers une plage de cellules, par exemple : D2:D10 comme indiqué ci-dessous.
| 123 | Sous-TestSumIf()Plage("D10").FormuleR1C1 = "=SUMIF(C2:C9,150,D2:D9)"Fin du sous-marin |
Méthode FormulaR1C1
La méthode FormulaR1C1 est plus flexible dans la mesure où elle ne vous limite pas à une plage définie de cellules. L'exemple ci-dessous nous donnera la même réponse que celui ci-dessus.
| 123 | Sous-TestSumIf()Plage("D10").FormuleR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C )"Fin du sous-marin |
Cependant, pour rendre la formule plus flexible, nous pourrions modifier le code pour qu'il ressemble à ceci :
| 123 | Sous-TestSumIf()ActiveCell.FormuleR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"Fin du sous-marin |
Où que vous soyez dans votre feuille de calcul, la formule additionnera ensuite les cellules qui répondent aux critères directement au-dessus et placera la réponse dans votre ActiveCell. La plage à l'intérieur de la fonction SUMIF doit être référencée à l'aide de la syntaxe de ligne (R) et de colonne (C).
Ces deux méthodes vous permettent d'utiliser des formules Excel dynamiques dans VBA.
Il y aura désormais une formule dans D10 au lieu d'une valeur.
