Tento tutoriál vám ukáže, ako používať funkcie Excel SUMIF a SUMIFS vo VBA
VBA nemá ekvivalent funkcií SUMIF alebo SUMIFS, ktoré môžete používať - používateľ musí vo VBA používať vstavané funkcie programu Excel pomocou Funkcia pracovného listu predmet.
Funkcia pracovného listu SUMIF
Objekt WorksheetFunction je možné použiť na vyvolanie väčšiny funkcií programu Excel, ktoré sú k dispozícii v dialógovom okne Vložiť funkciu v programe Excel. Funkcia SUMIF je jednou z nich.
| 123 | Sub TestSumIf ()Rozsah ("D10") = Application.WorksheetFunction.SumIf (rozsah ("C2: C9"), 150, rozsah ("D2: D9"))Koniec pod |
Vyššie uvedený postup sčíta bunky v rozsahu (D2: D9) iba vtedy, ak zodpovedajúca bunka v stĺpci C = 150.
Priradenie výsledku SUMIF premennej
Výsledok svojho vzorca môžete chcieť použiť inde v kóde, než ho písať priamo späť do programu Excel Range. Ak je to tak, môžete výsledok priradiť k premennej, ktorá sa má použiť neskôr vo vašom kóde.
| 1234567 | Sub AssignSumIfVariable ()Stmaviť výsledok ako dvojitý„Priraďte premennúvýsledok = WorksheetFunction.SumIf (rozsah ("C2: C9"), 150, rozsah ("D2: D9"))„Ukáž výsledokMsgBox „Celkový výsledok zodpovedajúci 150 predajným kódom je“ & resultKoniec pod |
Použitie SUMIFS
Funkcia SUMIFS je podobná funkcii SUMIF WorksheetFunction, ale umožňuje vám vyhľadať viac ako jedno kritérium. V nižšie uvedenom príklade hľadáme sčítanie predajnej ceny, ak je predajný kód 150 A nákladová cena je väčšia ako 2. Všimnite si, že v tomto vzorci je rozsah buniek na sčítanie pred kritériami, zatiaľ čo vo funkcii SUMIF je pozadu.
| 123 | Sub MultipleSumIfs ()Rozsah ("D10") = WorksheetFunction.SumIfs (rozsah ("D2: D9"), rozsah ("C2: C9"), 150, rozsah ("E2: E9"), "> 2")Koniec pod |
Použitie SUMIF s objektom rozsahu
K objektu Range môžete priradiť skupinu buniek a potom použiť tento objekt Range s príponou Pracovný listFunkcia predmet.
| 123456789101112 | Vedľajší testSumIFRange ()Dim rngKritériá ako rozsahDim rngSum as Range'priradiť rozsah buniekNastaviť rngCriteria = rozsah ("C2: C9")Nastaviť rngSum = rozsah ("D2: D9")„použite rozsah vo vzorciRozsah ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'uvoľnite objekty dosahuNastaviť rngCriteria = ničNastaviť rngSum = ničKoniec pod |
Použitie SUMIFS na objektoch s viacerými rozsahmi
Podobne môžete použiť SUMIFS na viacerých objektoch rozsahu.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeDim rngCriteria2 ako rozsahDim rngSum as Range'priradiť rozsah buniekNastaviť rngCriteria1 = rozsah ("C2: C9")Nastaviť rngCriteria2 = rozsah ("E2: E10")Nastaviť rngSum = rozsah ("D2: D10")„použite rozsahy vo vzorciRozsah ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'uvoľnite predmet dosahuNastaviť rngCriteria1 = NičNastaviť rngCriteria2 = NičNastaviť rngSum = ničKoniec pod |
Všimnite si toho, pretože pretože používate znak viac ako, kritériá vyššie ako 2 musia byť v zátvorke.
SUMIF Formula
Keď použijete WorksheetFunction.SUMIF ak chcete pridať súčet k rozsahu v pracovnom hárku, vráti sa statický súčet, nie flexibilný vzorec. To znamená, že keď sa vaše údaje v Exceli zmenia, hodnota, ktorú vrátil súbor Pracovný listFunkcia sa nezmení.
Vo vyššie uvedenom príklade tento postup spočítal rozsah (D2: D9), kde sa SaleCode rovná 150 v stĺpci C a výsledok bol vložený do D10. Ako vidíte na paneli vzorcov, tento výsledok je údaj a nie vzorec.
Ak sa niektorá z hodnôt zmení v rozsahu (D2: D9) alebo v rozsahu (C2: D9), výsledok v D10 bude NIE zmeniť.
Namiesto použitia WorksheetFunction.SumIf, môžete použiť VBA na použitie funkcie SUMIF na bunku pomocou Vzorec alebo Formula R1C1 metódy.
Metóda vzorca
Metóda vzorca vám umožňuje konkrétne poukázať na rozsah buniek, napr .: D2: D10, ako je uvedené nižšie.
| 123 | Sub TestSumIf ()Rozsah ("D10"). FormulaR1C1 = "= SUMIF (C2: C9,150, D2: D9)"Koniec pod |
Metóda FormulaR1C1
Metóda FormulaR1C1 je flexibilnejšia v tom, že vás neobmedzuje na určitý rozsah buniek. Nasledujúci príklad nám poskytne rovnakú odpoveď ako vyššie.
| 123 | Sub TestSumIf ()Rozsah ("D10"). Vzorec R1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "Koniec pod |
Aby bol však vzorec flexibilnejší, mohli by sme kód zmeniť tak, aby vyzeral takto:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"Koniec pod |
Nech ste kdekoľvek v pracovnom hárku, vzorec potom sčíta bunky, ktoré spĺňajú kritériá, priamo nad ním a odpoveď vloží do vášho ActiveCell. Na rozsah vo funkcii SUMIF je potrebné odkazovať pomocou syntaxe riadka (R) a stĺpca (C).
Obe tieto metódy vám umožňujú používať vzorce Dynamic Excel vo VBA.
Teraz bude namiesto hodnoty vzorec v D10.
