Denne vejledning viser dig, hvordan du bruger Excel SUMIF- og SUMIFS -funktionerne i VBA
VBA har ikke en ækvivalent til de SUMIF- eller SUMIFS -funktioner, du kan bruge - en bruger skal bruge de indbyggede Excel -funktioner i VBA ved hjælp af WorkSheetFunction objekt.
SUMIF -regnearkFunktion
WorksheetFunction -objektet kan bruges til at kalde de fleste Excel -funktioner, der er tilgængelige i dialogboksen Indsæt funktion i Excel. SUMIF -funktionen er en af dem.
| 123 | Sub TestSumIf ()Range ("D10") = Application.WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9")))Afslut Sub |
Fremgangsmåden ovenfor vil kun tilføje cellerne i område (D2: D9), hvis den tilsvarende celle i kolonne C = 150.
Tildeling af et SUMIF -resultat til en variabel
Du vil måske bruge resultatet af din formel andre steder i koden frem for at skrive det direkte tilbage til og Excel -område. Hvis dette er tilfældet, kan du tildele resultatet til en variabel, der skal bruges senere i din kode.
| 1234567 | Sub AssignSumIfVariable ()Dim resultat som dobbelt'Tildel variablenresult = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Vis resultatetMsgBox "Det samlede resultat, der matcher 150 salgskoden, er" & resultatAfslut Sub |
Brug af SUMIFS
SUMIFS -funktionen ligner SUMIF -regnearksfunktionen, men det giver dig mulighed for at kontrollere mere end ét kriterium. I eksemplet nedenfor søger vi at lægge salgsprisen sammen, hvis salgskoden er 150 OG kostprisen er større end 2. Bemærk, at i denne formel er celleområdet, der skal tilføjes, foran kriterierne, hvorimod i SUMIF -funktionen er den bagud.
| 123 | Sub MultipleSumIfs ()Range ("D10") = WorksheetFunction.SumIfs (Range ("D2: D9"), Range ("C2: C9"), 150, Range ("E2: E9"), "> 2")Afslut Sub |
Brug af SUMIF med et områdeobjekt
Du kan tildele en gruppe celler til Range -objektet og derefter bruge dette Range -objekt med ArbejdsarkFunktion objekt.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria As RangeDim rngSum som Range'tildele celleområdetIndstil rngCriteria = Range ("C2: C9")Indstil rngSum = Range ("D2: D9")'brug området i formlenOmråde ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'frigiv rækkeviddeobjekterneIndstil rngCriteria = IntetIndstil rngSum = IntetAfslut Sub |
Brug af SUMIFS på objekter med flere områder
På samme måde kan du bruge SUMIFS på flere områdeobjekter.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeDim rngCriteria2 som områdeDim rngSum som Range'tildele celleområdetIndstil rngCriteria1 = Range ("C2: C9")Indstil rngCriteria2 = Range ("E2: E10")Indstil rngSum = Range ("D2: D10")'brug intervallerne i formlenOmråde ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'frigiv rækkeviddeobjektetSet rngCriteria1 = IntetSet rngCriteria2 = IntetIndstil rngSum = IntetAfslut Sub |
Bemærk, at fordi du bruger et større end -tegn, skal kriterierne større end 2 være inden for parentes.
SUMIF formel
Når du bruger ArbejdsarkFunktion.SUMIF for at tilføje en sum til et område i dit regneark returneres en statisk sum, ikke en fleksibel formel. Det betyder, at når dine tal i Excel ændres, den værdi, der er returneret af ArbejdsarkFunktion vil ikke ændre sig.
I eksemplet ovenfor har proceduren tilføjet Range (D2: D9), hvor SaleCode er lig med 150 i kolonne C, og resultatet blev sat i D10. Som du kan se i formellinjen, er dette resultat en figur og ikke en formel.
Hvis nogen af værdierne ændres i enten område (D2: D9) eller område (C2: D9), vil resultatet i D10 IKKE lave om.
I stedet for at bruge RegnearkFunktion.SumIf, kan du bruge VBA til at anvende en SUMIF -funktion på en celle ved hjælp af Formel eller FormelR1C1 metoder.
Formel metode
Formelmetoden giver dig mulighed for at pege specifikt på en række celler, f.eks .: D2: D10 som vist nedenfor.
| 123 | Sub TestSumIf ()Område ("D10"). FormulaR1C1 = "= SUMIF (C2: C9,150, D2: D9)"Afslut Sub |
FormulaR1C1 Metode
FormulaR1C1 -metoden er mere fleksibel, idet den ikke begrænser dig til et bestemt antal celler. Nedenstående eksempel vil give os det samme svar som ovenstående.
| 123 | Sub TestSumIf ()Område ("D10"). FormelR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "Afslut Sub |
For at gøre formlen mere fleksibel kunne vi dog ændre koden til at se sådan ud:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"Afslut Sub |
Uanset hvor du er i dit regneark, vil formlen derefter tilføje de celler, der opfylder kriterierne direkte over det, og placere svaret i din ActiveCell. Området inde i SUMIF -funktionen skal henvises til ved brug af rækken (R) og kolonnen (C).
Begge disse metoder giver dig mulighed for at bruge dynamiske Excel -formler inden for VBA.
Der vil nu være en formel i D10 i stedet for en værdi.
