Ta vadnica vam bo pokazala, kako uporabljati funkciji Excel SUMIF in SUMIFS v VBA
VBA nima enakovrednih funkcij SUMIF ali SUMIFS, ki jih lahko uporabite - uporabnik mora uporabiti vgrajene funkcije Excel v VBA z uporabo Funkcija delovnega lista predmet.
Funkcija delovnega lista SUMIF
Objekt WorksheetFunction lahko uporabite za klic večine Excelovih funkcij, ki so na voljo v pogovornem oknu Vstavi funkcijo v Excelu. Ena izmed njih je funkcija SUMIF.
| 123 | Sub TestSumIf ()Obseg ("D10") = Application.WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))End Sub |
Zgornji postopek bo sešteval celice v območju (D2: D9) le, če je ustrezna celica v stolpcu C = 150.
Dodelitev rezultata SUMIF spremenljivki
Rezultat svoje formule boste morda želeli uporabiti drugje v kodi, namesto da bi ga zapisali neposredno nazaj in v obseg Excel. V tem primeru lahko rezultat dodelite spremenljivki, ki jo boste uporabili pozneje v kodi.
| 1234567 | Sub AssignSumIfVariable ()Zatemni rezultat kot dvojno'Dodelite spremenljivkoresult = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Pokaži rezultatMsgBox "Skupni rezultat, ki ustreza 150 prodajni kodi, je" & rezultatEnd Sub |
Uporaba SUMIFS
Funkcija SUMIFS je podobna funkciji SUMIF WorksheetFunction, vendar vam omogoča, da preverite več kot eno merilo. V spodnjem primeru želimo sešteti prodajno ceno, če je prodajna oznaka 150 IN je nabavna cena večja od 2. Upoštevajte, da je v tej formuli obseg celic, ki jih je treba sešteti, pred merili. v funkciji SUMIF je zadaj.
| 123 | Sub MultipleSumIfs ()Obseg ("D10") = Funkcija delovnega lista.SumIfs (Razpon ("D2: D9"), Obseg ("C2: C9"), 150, Razpon ("E2: E9"), "> 2")End Sub |
Uporaba SUMIF z objektom obsega
Objektu Range lahko dodelite skupino celic, nato pa ta predmet Range uporabite z Funkcija delovnega lista predmet.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria As RangeZatemni rngSum kot obseg'dodelite obseg celicNastavi rngCriteria = Razpon ("C2: C9")Nastavi rngSum = obseg ("D2: D9")"uporabite obseg v formuliObseg ("D10") = Funkcija delovnega lista.SumIf (rngCriteria, 150, rngSum)'sprostite objekte obsegaNastavi rngCriteria = NičNastavi rngSum = NičEnd Sub |
Uporaba SUMIFS na objektih z več obsegi
Podobno lahko uporabite SUMIFS za več objektov obsega.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeZatemni rngCriteria2 kot obsegZatemni rngSum kot obseg'dodelite obseg celicNastavi rngCriteria1 = Razpon ("C2: C9")Nastavi rngCriteria2 = Obseg ("E2: E10")Nastavi rngSum = Obseg ("D2: D10")"uporabite razpone v formuliObseg ("D10") = Funkcija delovnega lista.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'spustite predmet obsegaNastavi rngCriteria1 = NičNastavi rngCriteria2 = NičNastavi rngSum = NičEnd Sub |
Upoštevajte, da morajo biti merila večja od 2, ker uporabljate znak večji od, v oklepaju.
Formula SUMIF
Ko uporabljate Delovni listFunction.SUMIF če želite vnos na svoj delovni list dodati vsoto, se vrne statična vsota, ne prilagodljiva formula. To pomeni, da ko se vaše številke v Excelu spremenijo, vrednost, ki jo je vrnil Funkcija delovnega lista se ne bo spremenilo.
V zgornjem primeru je postopek sešteval obseg (D2: D9), kjer je koda SaleCode enaka 150 v stolpcu C, rezultat pa je bil zapisan v D10. Kot lahko vidite v vrstici s formulami, je ta rezultat številka in ne formula.
Če se katera koli vrednost spremeni v območju (D2: D9) ali v območju (C2: D9), bo rezultat v D10 NE spremeniti.
Namesto da uporabite Delovni listFunction.SumIf, lahko z VBA uporabite funkcijo SUMIF za celico s pomočjo Formula ali Formula R1C1 metode.
Metoda formule
Metoda formule vam omogoča, da posebej pokažete na vrsto celic, na primer: D2: D10, kot je prikazano spodaj.
| 123 | Sub TestSumIf ()Razpon ("D10"). Formula R1C1 = "= SUMIF (C2: C9,150, D2: D9)"End Sub |
FormulaR1C1 Metoda
Metoda FormulaR1C1 je bolj prilagodljiva, saj vas ne omejuje na določeno območje celic. Spodnji primer nam bo dal enak odgovor kot zgornji.
| 123 | Sub TestSumIf ()Razpon ("D10"). Formula R1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "End Sub |
Da pa bi bila formula bolj prilagodljiva, bi lahko kodo spremenili takole:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"End Sub |
Kjer koli ste na svojem delovnem listu, bo formula nato sestavila celice, ki izpolnjujejo merila neposredno nad njim, in odgovor postavila v vašo ActiveCell. Na obseg znotraj funkcije SUMIF se je treba sklicevati s sintakso vrstice (R) in stolpca (C).
Obe metodi vam omogočata uporabo formul Dynamic Excel v VBA.
Namesto vrednosti bo zdaj v D10 formula.
