Deze tutorial laat je zien hoe je de Excel SUMIF- en SUMIFS-functies in VBA gebruikt
VBA heeft geen equivalent van de SUMIF- of SUMIFS-functies die u kunt gebruiken - een gebruiker moet de ingebouwde Excel-functies in VBA gebruiken met behulp van de Werkbladfunctie object.
SUMIF-werkbladfunctie
Het object WorksheetFunction kan worden gebruikt om de meeste Excel-functies aan te roepen die beschikbaar zijn in het dialoogvenster Functie invoegen in Excel. De SUMIF-functie is er een van.
| 123 | Sub TestSumIf()Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))Einde sub |
De bovenstaande procedure telt alleen de cellen in Bereik (D2:D9) op als de corresponderende cel in kolom C = 150.
Een SUMIF-resultaat toewijzen aan een variabele
Misschien wilt u het resultaat van uw formule ergens anders in de code gebruiken in plaats van het rechtstreeks terug te schrijven naar en Excel-bereik. Als dit het geval is, kunt u het resultaat toewijzen aan een variabele om later in uw code te gebruiken.
| 1234567 | Sub AssignSumIfVariable()Dim resultaat als Double'Wijs de variabele toe'resultaat = WerkbladFunctie.SumIf(Bereik("C2:C9"), 150, Bereik("D2:D9"))'Toon het resultaat'MsgBox "Het totaal van het resultaat dat overeenkomt met de 150 verkoopcode is " & resultaatEinde sub |
SOMMEN gebruiken
De SOMMEN-functie is vergelijkbaar met de SOM.ALS-werkbladfunctie, maar u kunt er meer dan één criterium mee controleren. In het onderstaande voorbeeld willen we de verkoopprijs optellen als de verkoopcode 150 is EN de kostprijs groter is dan 2. Merk op dat in deze formule het celbereik dat moet worden opgeteld vóór de criteria staat, terwijl in de SUMIF-functie staat het achter.
| 123 | Sub MultipleSumIfs()Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")Einde sub |
SUMIF gebruiken met een bereikobject
U kunt een groep cellen aan het Range-object toewijzen en dat Range-object vervolgens gebruiken met de WerkbladFunctie object.
| 123456789101112 | Sub TestSumIFRange()Dim rngCriteria als bereikDim rngSum als bereik'het bereik van cellen toewijzen'Stel rngCriteria = Bereik ("C2:C9") inStel rngSum = Bereik ("D2:D9") in'gebruik het bereik in de formule'Range("D10") = WerkbladFunctie.SumIf(rngCriteria, 150, rngSum)'laat de bereikobjecten los'Stel rngCriteria = Niets inStel rngSum = Niets inEinde sub |
SUMIFS gebruiken op objecten met meerdere bereiken
Op dezelfde manier kunt u SUMIFS gebruiken op meerdere Range Objects.
| 123456789101112131415 | SubtestSumMultipleRanges()Dim rngCriteria1 Als bereikDim rngCriteria2 als bereikDim rngSum als bereik'het bereik van cellen toewijzen'Stel rngCriteria1= Bereik ("C2:C9") inStel rngCriteria2 = Bereik ("E2:E10") inStel rngSum = Bereik ("D2:D10") in'gebruik de bereiken in de formule'Range("D10") = WorksheetFunction.SumIfs(rngSum, rngCriteria1, 150, rngCriteria2, ">2")'laat het bereikobject los'Stel rngCriteria1 = Niets inStel rngCriteria2 = Niets inStel rngSum = Niets inEinde sub |
Merk op dat omdat u een groter dan-teken gebruikt, de criteria groter dan 2 tussen haakjes moeten staan.
SUMIF-formule
Wanneer u de WerkbladFunctie.SUMIF om een som toe te voegen aan een bereik in uw werkblad, wordt een statische som geretourneerd, geen flexibele formule. Dit betekent dat wanneer uw cijfers in Excel veranderen, de waarde die is geretourneerd door de WerkbladFunctie zal niet veranderen.
In het bovenstaande voorbeeld heeft de procedure Range (D2:D9) opgeteld, waarbij de SaleCode gelijk is aan 150 in kolom C, en het resultaat is in D10 geplaatst. Zoals u in de formulebalk kunt zien, is dit resultaat een cijfer en geen formule.
Als een van de waarden verandert in Bereik (D2:D9) of Bereik (C2:D9), zal het resultaat in D10 NIET verandering.
In plaats van de WerkbladFunctie.SumIf, kunt u VBA gebruiken om een SUMIF-functie op een cel toe te passen met behulp van de Formule of FormuleR1C1 methoden.
Formule Methode:
Met de formulemethode kunt u specifiek naar een celbereik verwijzen, bijvoorbeeld: D2:D10, zoals hieronder weergegeven.
| 123 | Sub TestSumIf()Bereik ("D10").FormuleR1C1 = "=SUMIF(C2:C9,150,D2:D9)"Einde sub |
FormuleR1C1-methode
De FormulaR1C1-methode is flexibeler omdat deze u niet beperkt tot een bepaald celbereik. Het onderstaande voorbeeld geeft ons hetzelfde antwoord als het bovenstaande.
| 123 | Sub TestSumIf()Bereik ("D10").FormuleR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C )"Einde sub |
Om de formule echter flexibeler te maken, kunnen we de code aanpassen om er als volgt uit te zien:
| 123 | Sub TestSumIf()ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"Einde sub |
Waar u ook bent in uw werkblad, de formule zal dan de cellen optellen die voldoen aan de criteria er direct boven en het antwoord in uw ActiveCell plaatsen. Naar het bereik binnen de SUMIF-functie moet worden verwezen met behulp van de syntaxis Rij (R) en Kolom (C).
Met beide methoden kunt u dynamische Excel-formules gebruiken binnen VBA.
Er komt nu een formule in D10 in plaats van een waarde.
