Denne opplæringen viser deg hvordan du bruker Excel SUMIF og SUMIFS -funksjonene i VBA
VBA har ikke tilsvarende SUMIF- eller SUMIFS -funksjoner som du kan bruke - en bruker må bruke de innebygde Excel -funksjonene i VBA ved å bruke WorkSheetFunction gjenstand.
SUMIF -regnearkFunksjon
WorksheetFunction -objektet kan brukes til å ringe de fleste Excel -funksjonene som er tilgjengelige i dialogboksen Sett inn funksjon i Excel. SUMIF -funksjonen er en av dem.
| 123 | Sub TestSumIf ()Range ("D10") = Application.WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9")))Slutt Sub |
Fremgangsmåten ovenfor vil bare legge til cellene i område (D2: D9) hvis den tilsvarende cellen i kolonne C = 150.
Tilordne et SUMIF -resultat til en variabel
Det kan være lurt å bruke resultatet av formelen din andre steder i koden i stedet for å skrive det direkte tilbake til og Excel Range. Hvis dette er tilfelle, kan du tilordne resultatet til en variabel som skal brukes senere i koden.
| 1234567 | Sub AssignSumIfVariable ()Dim resultat som Double'Tilordne variabelenresult = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Vis resultatetMsgBox "Totalen av resultatet som samsvarer med 150 salgskoden er" & resultatSlutt Sub |
Bruker SUMIFS
SUMIFS -funksjonen ligner SUMIF WorksheetFunction, men den lar deg se etter mer enn ett kriterium. I eksemplet nedenfor ønsker vi å legge opp salgsprisen hvis salgskoden er 150 OG kostprisen er større enn 2. Legg merke til at i denne formelen er celleområdet som skal legges opp foran kriteriene, mens i SUMIF -funksjonen er den bak.
| 123 | Sub MultipleSumIfs ()Range ("D10") = WorksheetFunction.SumIfs (Range ("D2: D9"), Range ("C2: C9"), 150, Range ("E2: E9"), "> 2")Slutt Sub |
Bruke SUMIF med et rekkeviddeobjekt
Du kan tilordne en gruppe celler til Range -objektet, og deretter bruke det Range -objektet med Arbeidsark Funksjon gjenstand.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria As RangeDim rngSum som Range'tilordne celleområdetSett rngCriteria = Range ("C2: C9")Sett rngSum = Range ("D2: D9")'bruk området i formelenOmråde ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'Slipp rekkeviddeobjekteneSett rngCriteria = IngentingSett rngSum = IngentingSlutt Sub |
Bruke SUMIFS på objekter med flere områder
På samme måte kan du bruke SUMIFS på flere rekkeviddeobjekter.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeDim rngCriteria2 som rekkeviddeDim rngSum som Range'tilordne celleområdetSett rngCriteria1 = Range ("C2: C9")Sett rngCriteria2 = Range ("E2: E10")Sett rngSum = Range ("D2: D10")'bruk områdene i formelenOmråde ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'slipp rekkeviddeobjektetSett rngCriteria1 = IngentingSett rngCriteria2 = IngentingSett rngSum = IngentingSlutt Sub |
Legg merke til at fordi du bruker et større enn -tegn, må kriteriene større enn 2 være innenfor parentes.
SUMIF Formula
Når du bruker Regneark Funksjon.SUMIF for å legge til en sum i et område i regnearket, returneres en statisk sum, ikke en fleksibel formel. Dette betyr at når tallene i Excel endres, vil verdien som er returnert av Arbeidsark Funksjon vil ikke endre seg.
I eksemplet ovenfor har prosedyren lagt til område (D2: D9) der SaleCode er lik 150 i kolonne C, og resultatet ble satt i D10. Som du kan se i formellinjen, er dette resultatet en figur og ikke en formel.
Hvis noen av verdiene endres i enten Range (D2: D9) eller Range (C2: D9), vil resultatet i D10 IKKE endring.
I stedet for å bruke RegnearkFunksjon.SumIf, kan du bruke VBA til å bruke en SUMIF -funksjon på en celle ved hjelp av Formel eller FormelR1C1 metoder.
Formelmetode
Formelmetoden lar deg peke spesifikt på et område av celler, for eksempel: D2: D10 som vist nedenfor.
| 123 | Sub TestSumIf ()Område ("D10"). FormulaR1C1 = "= SUMIF (C2: C9,150, D2: D9)"Slutt Sub |
FormulaR1C1 Metode
FormulaR1C1 -metoden er mer fleksibel ved at den ikke begrenser deg til et angitt celleområde. Eksemplet nedenfor gir oss det samme svaret som det ovenfor.
| 123 | Sub TestSumIf ()Område ("D10"). FormelR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "Slutt Sub |
For å gjøre formelen mer fleksibel, kan vi imidlertid endre koden slik at den ser slik ut:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"Slutt Sub |
Uansett hvor du er i regnearket, vil formelen legge til cellene som oppfyller kriteriene rett over det og plassere svaret i ActiveCell. Området inne i SUMIF -funksjonen må refereres til ved bruk av rad (R) og kolonne (C) syntaks.
Begge disse metodene lar deg bruke dynamiske Excel -formler i VBA.
Det vil nå være en formel i D10 i stedet for en verdi.
