Ten samouczek pokaże Ci, jak korzystać z funkcji LICZBA Excela w VBA
Funkcja VBA COUNT służy do zliczania liczby komórek w arkuszu, które zawierają wartości. Jest dostępny za pomocą metody WorksheetFunction w VBA.
COUNT Funkcja arkusza roboczego
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. Jedną z nich jest funkcja ILE.LICZB.
| 123 | PodtestCountFunctinoZakres("D33") = Application.WorksheetFunction.Count(Range("D1:D32"))Napis końcowy |
Możesz mieć do 30 argumentów w funkcji ILE.LICZB. Każdy z argumentów musi odnosić się do zakresu komórek.
Poniższy przykład zlicza, ile komórek wypełnionych wartościami znajduje się w komórkach od D1 do D9
| 123 | Liczba podtestów()Zakres("D10") = Application.WorksheetFunction.Count(Range("D1:D9"))Napis końcowy |
Poniższy przykład zliczy, ile wartości znajduje się w zakresie w kolumnie D iw zakresie w kolumnie F. Jeśli nie wpiszesz obiektu Application, zostanie to przyjęte.
| 123 | PodtestCountMultiple()Range("G8") = WorksheetFunction.Count(Range("G2:G7"), Range("H2:H7"))Napis końcowy |
Przypisywanie wyniku zliczania 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 | Licznik przydziałów podrzędnych()Dim wynik As Integer'Przypisz zmiennąwynik = WorksheetFunction.Count(Range("H2:H11"))'Pokaż wynikMsgBox "Liczba komórek wypełnionych wartościami to " i wynikNapis końcowy |
COUNT 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.
| 123456789 | PodzakresLicznika Testów()Dim rng As Range'przypisz zakres komórekUstaw rng = Zakres("G2:G7")'użyj zakresu we wzorzeZakres("G8") = WorksheetFunction.Count(rng)'zwolnij obiekt zakresuUstaw rng = NicNapis końcowy |
COUNT obiekty o wielu zakresach
Podobnie możesz policzyć, ile komórek jest wypełnionych wartościami w wielu obiektach zakresu.
| 123456789101112 | PodtestCountMultipleRanges()Dim rngA jako zakresDim rngB jako zakres'przypisz zakres komórekUstaw rngA = Zakres("D2:D10")Ustaw rngB = Zakres("E2:E10")'użyj zakresu we wzorzeZakres("E11") = WorksheetFunction.Count(rngA, rngB)'zwolnij obiekt zakresuUstaw rngA = NicUstaw rngB = NicNapis końcowy |
Korzystanie z COUNTA
Licznik zliczy tylko WARTOŚCI w komórkach, nie zliczy komórki, jeśli komórka zawiera tekst. Aby policzyć komórki wypełnione dowolnymi danymi, musielibyśmy użyć funkcji ILE.NIEPUSTYCH.
| 123 | Liczba podtestówA()Range("B8) = Application.WorksheetFunction.CountA(Range("B1:B6"))Napis końcowy |
W poniższym przykładzie funkcja ILE.LICZB zwróci zero, ponieważ nie ma żadnych wartości w kolumnie B, podczas gdy zwróci 4 dla kolumny C. Funkcja ILE.NIEPUSTYCH zliczy jednak komórki z tekstem i zwróci wartość 5 w kolumnie B, podczas gdy nadal zwraca wartość 4 w kolumnie C.
Korzystanie z LICZ.PUSTYCH
Funkcja LICZ.PUSTE zliczy tylko puste komórki w zakresie komórek - tj. komórki, które nie zawierają żadnych danych.
| 123 | PodtestCountBlank()Range("B8) = Application.WorksheetFunction.CountBlanks(Range("B1:B6"))Napis końcowy |
W poniższym przykładzie kolumna B nie zawiera pustych komórek, podczas gdy kolumna C ma jedną pustą komórkę.
Korzystanie z funkcji LICZ.JEŻELI
Inną funkcją arkusza, której można użyć, jest funkcja LICZ.JEŻELI.
| 123456 | PodtestLicznikJeżeli()Zakres("H14") = WorksheetFunction.CountIf(Range("H2:H10"), ">0")Zakres("H15") = WorksheetFunction.CountIf(Range("H2:H10"), ">100")Zakres("H16") = WorksheetFunction.CountIf(Range("H2:H10"), ">1000")Zakres("H17") = WorksheetFunction.CountIf(Range("H2:H10"), ">10000")Napis końcowy |
Powyższa procedura zlicza komórki z wartościami tylko wtedy, gdy kryteria są spełnione - większe niż 0, większe niż 100, większe niż 1000 i większe niż 10000. Aby formuła działała poprawnie, musisz umieścić kryteria w cudzysłowie.
Wady funkcji arkusza roboczego
Kiedy używasz Funkcja arkusza roboczego aby policzyć wartości w zakresie w arkuszu, zwracana jest wartość 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 TestCount policzyła komórki w kolumnie H, w której występuje wartość. Jak widać na pasku formuły, ten wynik jest liczbą, a nie formułą.
Jeśli którakolwiek z wartości zmieni się w związku z tym w zakresie (H2:H12), wyniki w H14 będą NIE reszta.
Zamiast używać WorksheetFunction.Count, możesz użyć VBA, aby zastosować funkcję zliczania do komórki za pomocą Formuła lub FormułaR1C1 metody.
Korzystanie z metody formuły
Metoda formuł pozwala wskazać konkretny zakres komórek, np.: H2:H12, jak pokazano poniżej.
| 123 | Formuła liczby testów podrzędnychZakres("H14").Formuła = "=Liczba(H2:H12)"Napis końcowy |
Korzystanie z metody FormulaR1C1
Metoda FromulaR1C1 jest bardziej elastyczna, ponieważ nie ogranicza Cię do określonego zakresu komórek. Poniższy przykład da nam taką samą odpowiedź jak powyższy.
| 123 | Podrzędna formuła liczby testów()Zakres("H14").Formuła = "=Liczba(R[-9]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 | Podrzędna formuła liczby testów()ActiveCell.FormulaR1C1 = "=Liczba(R[-11]C:R[-1]C)"Napis końcowy |
Gdziekolwiek jesteś w arkuszu, formuła policzy wartości w 12 komórkach bezpośrednio nad nim i umieści odpowiedź w ActiveCell. Zakres wewnątrz funkcji ILE.LICZB 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 H14 będzie teraz formuła zamiast wartości.
