Този урок ще ви покаже как да използвате функцията Excel COUNT във VBA
Функцията VBA COUNT се използва за преброяване на броя на клетките във вашия работен лист, които имат стойности в тях. Достъпът се осъществява чрез метода WorksheetFunction във VBA.
COUNT Работен лист Функция
Обектът WorksheetFunction може да се използва за извикване на повечето от функциите на Excel, които са налични в диалоговия прозорец Вмъкване на функция в Excel. Функцията COUNT е една от тях.
| 123 | Sub TestCountFunctinoДиапазон ("D33") = Application.WorksheetFunction.Count (Диапазон ("D1: D32"))End Sub |
Можете да имате до 30 аргумента във функцията COUNT. Всеки от аргументите трябва да се отнася до диапазон от клетки.
Този пример по -долу ще брои колко клетки са запълнени със стойности в клетки D1 до D9
| 123 | Sub TestCount ()Диапазон ("D10") = Application.WorksheetFunction.Count (Диапазон ("D1: D9"))End Sub |
Примерът по -долу ще брои колко стойности са в диапазон в колона D и в диапазон в колона F. Ако не въведете обекта Application, той ще се приеме.
| 123 | Sub TestCountMultiple ()Диапазон ("G8") = Работен лист Функция.Контрол (Обхват ("G2: G7"), Обхват ("H2: H7"))End Sub |
Присвояване на резултат на променлива
Може да искате да използвате резултата от формулата си другаде в кода, вместо да го пишете директно обратно в и Excel диапазон. Ако случаят е такъв, можете да присвоите резултата на променлива, която да използвате по -късно във вашия код.
| 1234567 | Sub AssignCount ()Затъмняване на резултата Като цяло число'Задайте променливатаresult = WorksheetFunction.Count (Диапазон ("H2: H11"))'Покажете резултатаMsgBox "Броят клетки, запълнени със стойности, е" & резултатEnd Sub |
COUNT с Range Object
Можете да присвоите група клетки на обекта Range и след това да използвате този Range обект с Работен лист Функция обект.
| 123456789 | Sub TestCountRange ()Dim rng As Range'задайте диапазона от клеткиЗадайте rng = Обхват ("G2: G7")'използвайте диапазона във формулатаДиапазон ("G8") = Функция на работен лист.Count (rng)'освободете обекта rangeЗадайте rng = НищоEnd Sub |
COUNT обекти с множество обхвати
По същия начин можете да преброите колко клетки са запълнени със стойности в множество Range Objects.
| 123456789101112 | Sub TestCountMultipleRanges ()Dim rngA As RangeDim rngB като Range'задайте диапазона от клеткиЗадайте rngA = Обхват ("D2: D10")Задайте rngB = Обхват ("E2: E10")'използвайте диапазона във формулатаДиапазон ("E11") = Функция на работен лист.Кон (rngA, rngB)'освободете обекта rangeЗадайте rngA = НищоЗадайте rngB = НищоEnd Sub |
Използване на COUNTA
Преброяването ще брои само СТОЙНОСТИТЕ в клетките, няма да брои клетката, ако клетката има текст в нея. За да преброим клетките, които са попълнени с всякакъв вид данни, ще трябва да използваме функцията COUNTA.
| 123 | Sub TestCountA ()Range ("B8) = Application.WorksheetFunction.CountA (Range (" B1: B6 "))End Sub |
В примера по -долу функцията COUNT ще върне нула, тъй като няма стойности в колона B, докато тя ще върне 4 за колона C. Функцията COUNTA обаче ще преброи клетките с текст в тях и ще върне стойност от 5 в колона B, като все още връща стойност 4 в колона C.
Използване на COUNTBLANKS
Функцията COUNTBLANKS ще брои само празните клетки в диапазона от клетки - т.е. клетки, които изобщо нямат данни.
| 123 | Sub TestCountBlank ()Диапазон ("B8) = Application.WorksheetFunction.CountBlanks (Range (" B1: B6 "))End Sub |
В примера по -долу колона B няма празни клетки, докато колона C има една празна клетка.
Използване на функцията COUNTIF
Друга функция на работен лист, която може да се използва, е функцията COUNTIF.
| 123456 | Sub TestCountIf ()Range ("H14") = WorksheetFunction.CountIf (Range ("H2: H10"), "> 0")Range ("H15") = WorksheetFunction.CountIf (Range ("H2: H10"), "> 100")Range ("H16") = WorksheetFunction.CountIf (Range ("H2: H10"), "> 1000")Range ("H17") = WorksheetFunction.CountIf (Range ("H2: H10"), "> 10000")End Sub |
Горната процедура ще брои клетките със стойности в тях само ако критериите са съвпаднали - по -голямо от 0, по -голямо от 100, по -голямо от 1000 и по -голямо от 10000. Трябва да поставите критериите в кавички, за да работи формулата правилно.
Недостатъци на функцията на работния лист
Когато използвате Работен лист Функция за да преброите стойностите в диапазон във вашия работен лист, се връща статична стойност, а не гъвкава формула. Това означава, че когато вашите цифри в Excel се променят, стойността, която е била върната от Работен лист Функция няма да се промени.
В горния пример процедурата TestCount преброи клетките в колона H, където има стойност. Както можете да видите в лентата с формули, този резултат е цифра, а не формула.
Следователно, ако някоя от стойностите се промени в диапазона (H2: H12), резултатите в H14 ще бъдат НЕ промяна.
Вместо да използвате Работен лист Функция, можете да използвате VBA, за да приложите функция за броене към клетка, използвайки Формула или Формула R1C1 методи.
Използвайки метода на формулата
Методът на формулата ви позволява да посочите конкретно към диапазон от клетки, например: H2: H12, както е показано по -долу.
| 123 | Sub TestCountFormulaОбхват ("H14"). Формула = "= Брой (H2: H12)"End Sub |
Използване на метода FormulaR1C1
Методът FromulaR1C1 е по -гъвкав, тъй като не ви ограничава до определен диапазон от клетки. Примерът по -долу ще ни даде същия отговор като този по -горе.
| 123 | Sub TestCountFormula ()Обхват ("H14"). Формула = "= Брой (R [-9] C: R [-1] C)"End Sub |
За да направим формулата по -гъвкава, бихме могли да променим кода така, че да изглежда така:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Брой (R [-11] C: R [-1] C)"End Sub |
Където и да сте в работния си лист, формулата ще преброи стойностите в 12 -те клетки точно над него и ще постави отговора във вашия ActiveCell. Обхватът във функцията COUNT трябва да бъде посочен, като се използва синтаксисът на ред (R) и колона (C).
И двата метода ви позволяват да използвате формули за динамичен Excel в рамките на VBA.
Сега ще има формула в H14 вместо стойност.
