Ten samouczek pokaże Ci, jak korzystać z funkcji Excel SUMIF i SUMIFS w VBA
VBA nie ma odpowiednika funkcji SUMA.JEŻELI lub SUMA.Funkcjach, których można użyć - użytkownik musi korzystać z wbudowanych funkcji Excela w VBA za pomocą Funkcja arkusza roboczego obiekt.
SUMIF Arkusz roboczyFunkcja
Obiekt WorksheetFunction może służyć do wywoływania większości funkcji programu Excel dostępnych w oknie dialogowym Wstaw funkcję w programie Excel. Jednym z nich jest funkcja SUMA.JEŻELI.
| 123 | Suma podrzędna, jeśli()Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))Napis końcowy |
Powyższa procedura spowoduje dodanie komórek z zakresu (D2:D9) tylko wtedy, gdy odpowiadająca komórka w kolumnie C = 150.
Przypisywanie wyniku SUMA.JEŻELI do zmiennej
Możesz chcieć użyć wyniku formuły w innym miejscu w kodzie, zamiast zapisywać go bezpośrednio z powrotem do zakresu programu Excel. W takim przypadku możesz przypisać wynik do zmiennej, która będzie używana później w kodzie.
| 1234567 | Sub AssignSumIf Variable()Przyciemnij wynik jako Double'Przypisz zmiennąwynik = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))'Pokaż wynikMsgBox "Całkowity wynik pasujący do kodu sprzedaży 150 to " & wynikNapis końcowy |
Korzystanie z SUMIFS
Funkcja SUMI.Funkcja jest podobna do funkcji arkusza roboczego SUMA.JEŻELI, ale umożliwia sprawdzenie więcej niż jednego kryterium. W poniższym przykładzie staramy się zsumować cenę sprzedaży, jeśli kod sprzedaży to 150 ORAZ cena kosztowa jest większa niż 2. Zauważ, że w tej formule zakres komórek do zsumowania znajduje się przed kryteriami, podczas gdy w funkcji SUMIF jest w tyle.
| 123 | Sub MultipleSumIfs()Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")Napis końcowy |
Używanie SUMA.JEŻELI z obiektem zakresu
Możesz przypisać grupę komórek do obiektu Range, a następnie użyć tego obiektu Range z Funkcja arkusza roboczego obiekt.
| 123456789101112 | Podrzędna suma testowaIFRange()Dim rngCriteria As RangeDim rngSum jako zakres'przypisz zakres komórekUstaw rngCriteria = Zakres("C2:C9")Ustaw rngSum = Zakres("D2:D9")'użyj zakresu we wzorzeZakres("D10") = WorksheetFunction.SumIf(rngCriteria, 150, rngSum)'zwolnij obiekty zasięguUstaw rngCriteria = NicUstaw sumę rng = NicNapis końcowy |
Używanie SUMIFS na obiektach o wielu zakresach
Podobnie możesz użyć SUMIFS na wielu obiektach zakresu.
| 123456789101112131415 | SubTestSumMultipleRanges()Dim rngCriteria1 jako zakresDim rngCriteria2 jako zakresDim rngSum jako zakres'przypisz zakres komórekUstaw rngCriteria1= Zakres("C2:C9")Ustaw rngCriteria2 = Zakres("E2:E10")Ustaw rngSum = Zakres("D2:D10")'użyj zakresów we wzorzeZakres("D10") = WorksheetFunction.SumIfs(rngSum, rngCriteria1, 150, rngCriteria2, ">2")'zwolnij obiekt zakresuUstaw rngCriteria1 = NicUstaw rngCriteria2 = NicUstaw sumę rng = NicNapis końcowy |
Zauważ, że ponieważ używasz znaku większego niż, kryteria większe niż 2 muszą być w nawiasach.
SUMA.JEŻELI Formuła
Kiedy używasz Arkusz Funkcja.SUMIF aby dodać sumę do zakresu w arkuszu, zwracana jest suma statyczna, a nie elastyczna formuła. Oznacza to, że gdy zmienią się Twoje dane w Excelu, wartość zwrócona przez Funkcja arkusza roboczego nie zmieni się.
W powyższym przykładzie procedura dodała Range(D2:D9), gdzie SaleCode jest równy 150 w kolumnie C, a wynik został umieszczony w D10. Jak widać na pasku formuły, ten wynik jest liczbą, a nie formułą.
Jeśli którakolwiek z wartości zmieni się w Range(D2:D9) lub Range(C2:D9), wynik w D10 będzie NIE reszta.
Zamiast używać Arkusz Funkcja.SumIf, możesz użyć VBA, aby zastosować funkcję SUMA.JEŻELI do komórki za pomocą Formuła lub FormułaR1C1 metody.
Metoda formuły
Metoda formuł pozwala wskazać konkretny zakres komórek, np.: D2:D10, jak pokazano poniżej.
| 123 | Suma podrzędna, jeśli()Zakres("D10").FormułaR1C1 = "=SUMA.JEŻELI(C2:C9,150;D2:D9)"Napis końcowy |
FormułaR1C1 Metoda
Metoda FormulaR1C1 jest bardziej elastyczna, ponieważ nie ogranicza użytkownika do określonego zakresu komórek. Poniższy przykład da nam taką samą odpowiedź jak powyższy.
| 123 | Suma podrzędna, jeśli()Zakres("D10").WzórR1C1 = "=SUMA.JEŻELI(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C )"Napis końcowy |
Aby jednak formuła była bardziej elastyczna, możemy zmienić kod tak, aby wyglądał tak:
| 123 | Podtest SumaJeżeli()ActiveCell.FormulaR1C1 = "=SUMA.JEŻELI(R[-8]C[-1]:R[-1]C[-1];150;R[-8]C:R[-1]C)"Napis końcowy |
Gdziekolwiek jesteś w arkuszu, formuła zsumuje komórki spełniające kryteria bezpośrednio nad nim i umieści odpowiedź w ActiveCell. Zakres wewnątrz funkcji SUMA.JEŻELI należy odwoływać się za pomocą składni Wiersz (R) i Kolumna (C).
Obie te metody umożliwiają korzystanie z formuł Dynamic Excel w VBA.
W D10 będzie teraz formuła zamiast wartości.
