Tento tutoriál vám ukáže, jak používat funkce Excel SUMIF a SUMIFS ve VBA
VBA nemá ekvivalent funkcí SUMIF nebo SUMIFS, které můžete použít - uživatel musí ve VBA používat vestavěné funkce Excelu pomocí Funkce pracovního listu objekt.
Funkce listu SUMIF
Objekt WorksheetFunction lze použít k volání většiny funkcí aplikace Excel, které jsou k dispozici v dialogovém okně Vložit funkci v aplikaci Excel. Funkce SUMIF je jednou z nich.
| 123 | Dílčí testSumIf ()Rozsah ("D10") = Application.WorksheetFunction.SumIf (rozsah ("C2: C9"), 150, rozsah ("D2: D9"))End Sub |
Výše uvedený postup sečte buňky v rozsahu (D2: D9) pouze v případě, že odpovídající buňka ve sloupci C = 150.
Přiřazení výsledku SUMIF proměnné
Možná budete chtít použít výsledek vzorce jinde v kódu, než abyste ho psali přímo zpět do a rozsahu Excelu. Pokud tomu tak je, můžete výsledek přiřadit proměnné, kterou použijete později v kódu.
| 1234567 | Sub AssignSumIfVariable ()Dim výsledek jako Double"Přiřaďte proměnnou."result = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))„Ukaž výsledekMsgBox "Celkový výsledek odpovídající 150 prodejním kódům je" & resultEnd Sub |
Pomocí SUMIFS
Funkce SUMIFS je podobná funkci SUMIF WorksheetFunction, ale umožňuje vám vyhledat více než jedno kritérium. V níže uvedeném příkladu hledáme sečíst prodejní cenu, pokud je prodejní kód 150 A nákladová cena je větší než 2. Všimněte si, že v tomto vzorci je rozsah buněk, které se mají sečíst, před kritérii, zatímco ve funkci SUMIF je pozadu.
| 123 | Sub MultipleSumIfs ()Rozsah ("D10") = WorksheetFunction.SumIfs (rozsah ("D2: D9"), rozsah ("C2: C9"), 150, rozsah ("E2: E9"), "> 2")End Sub |
Použití SUMIF s objektem rozsahu
K objektu Range můžete přiřadit skupinu buněk a poté použít tento Range objekt s Pracovní list Funkce objekt.
| 123456789101112 | Dílčí testSumIFRange ()Dim rngCriteria As RangeDim rngSum as Range'přiřaďte rozsah buněkNastavit rngCriteria = rozsah ("C2: C9")Nastavit rngSum = rozsah ("D2: D9")'použijte rozsah ve vzorci.'Rozsah ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'uvolněte objekty dosahuNastavit rngCriteria = NicNastavit rngSum = NicEnd Sub |
Použití SUMIFS na objektech s více rozsahy
Podobně můžete použít SUMIFS na více objektech rozsahu.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 jako rozsahDim rngCriteria2 jako rozsahDim rngSum as Range'přiřaďte rozsah buněkNastavit rngCriteria1 = Rozsah ("C2: C9")Nastavit rngCriteria2 = Rozsah ("E2: E10")Nastavit rngSum = rozsah ("D2: D10")'použijte rozsahy ve vzorci.'Rozsah ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'uvolněte předmět dosahuNastavit rngCriteria1 = NicNastavit rngCriteria2 = NicNastavit rngSum = NicEnd Sub |
Všimněte si, že protože používáte znaménko větší než, musí být kritéria větší než 2 v závorkách.
Vzorec SUMIF
Když použijete WorksheetFunction.SUMIF Chcete -li přidat součet do rozsahu v listu, vrátí se statický součet, nikoli flexibilní vzorec. To znamená, že když se vaše čísla v Excelu změní, hodnota, která byla vrácena Pracovní list Funkce nezmění.
Ve výše uvedeném příkladu procedura sečetla Rozsah (D2: D9), kde SaleCode se rovná 150 ve sloupci C a výsledek byl vložen do D10. Jak vidíte na řádku vzorců, tento výsledek je obrázek a ne vzorec.
Pokud se některá z hodnot změní v rozsahu (D2: D9) nebo rozsahu (C2: D9), výsledek v D10 bude NE změna.
Místo použití WorksheetFunction.SumIf, můžete použít VBA k použití funkce SUMIF na buňku pomocí Vzorec nebo Vzorec R1C1 metody.
Metoda vzorce
Metoda vzorce vám umožňuje konkrétně ukázat na rozsah buněk, např .: D2: D10, jak je uvedeno níže.
| 123 | Dílčí testSumIf ()Rozsah ("D10"). FormulaR1C1 = "= SUMIF (C2: C9,150, D2: D9)"End Sub |
Metoda FormulaR1C1
Metoda FormulaR1C1 je flexibilnější v tom, že vás neomezuje na nastavený rozsah buněk. Následující příklad nám dá stejnou odpověď jako ten výše.
| 123 | Dílčí testSumIf ()Rozsah ("D10"). Vzorec R1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "End Sub |
Aby byl vzorec flexibilnější, mohli bychom kód upravit tak, aby vypadal takto:
| 123 | Dílčí testSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"End Sub |
Ať jste kdekoli v listu, vzorec poté sečte buňky, které splňují kritéria přímo nad ním, a umístí odpověď do vašeho ActiveCell. Na rozsah uvnitř funkce SUMIF je třeba odkazovat pomocí syntaxe Row (R) a Column (C).
Obě tyto metody vám umožňují používat ve VBA vzorce Dynamic Excel.
Nyní bude místo hodnoty vzorec v D10.
