Този урок ще ви покаже как да използвате средната функция на Excel във VBA.
Функцията Excel AVERAGE се използва за изчисляване на средна стойност от клетки в диапазона във вашия работен лист, които имат стойности в тях. Във VBA достъпът се осъществява чрез метода WorksheetFunction.
СРЕДНА Функция на работен лист
Обектът WorksheetFunction може да се използва за извикване на повечето от функциите на Excel, които са налични в диалоговия прозорец Вмъкване на функция в Excel. Функцията AVERAGE е една от тях.
| 123 | Субтестова функцияДиапазон ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
Можете да имате до 30 аргумента във функцията AVERAGE. Всеки от аргументите трябва да се отнася до диапазон от клетки.
Този пример по -долу ще даде средната стойност на сумата от клетките B11 до N11
| 123 | Sub TestAverage ()Диапазон ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))End Sub |
Примерът по -долу ще даде средно от сумата на клетките в В11 до N11 и сумата от клетките в В12: N12. Ако не въведете обекта Application, той ще се приеме.
| 123 | Sub TestAverage ()Диапазон ("O11") = Функция на работен лист. Средна стойност (Обхват ("B11: N11"), Обхват ("B12: N12"))End Sub |
Присвояване на СРЕДЕН резултат на променлива
Може да искате да използвате резултата от формулата си другаде в кода, вместо да го записвате директно в диапазон на Excel. Ако случаят е такъв, можете да присвоите резултата на променлива, която да използвате по -късно във вашия код.
| 1234567 | Sub AssignAverage ()Затъмняване на резултата Като цяло число'Задайте променливатаresult = WorksheetFunction.Average (Range ("A10: N10"))'Покажете резултатаMsgBox "Средната стойност за клетките в този диапазон е" & резултатEnd Sub |
AVERAGE с Range Object
Можете да присвоите група клетки на обекта Range и след това да използвате този Range обект с Работен лист Функция обект.
| 123456789 | Sub TestAverageRange ()Dim rng As Range'задайте диапазона от клеткиЗадайте rng = Обхват ("G2: G7")'използвайте диапазона във формулатаДиапазон ("G8") = Функция на работен лист. Средна стойност (rng)'освободете обекта rangeЗадайте rng = НищоEnd Sub |
СРЕДНИ Обекти с множество обхвати
По същия начин можете да изчислите средната стойност на клетките от множество Range Objects.
| 123456789101112 | Sub TestAverageMultipleRanges ()Dim rngA As RangeDim rngB като Range'задайте диапазона от клеткиЗадайте rngA = Обхват ("D2: D10")Задайте rngB = Обхват ("E2: E10")'използвайте диапазона във формулатаДиапазон ("E11") = Функция на работен лист. Средна стойност (rngA, rngB)'освободете обекта rangeЗадайте rngA = НищоЗадайте rngB = НищоEnd Sub |
Използване на AVERAGEA
Функцията AVERAGEA се различава от функцията AVERAGE по това, че създава средна стойност от всички клетки в диапазон, дори ако една от клетките има текст в нея - тя замества текста с нула и включва това при изчисляване на средната стойност. Функцията AVERAGE ще игнорира тази клетка и няма да я включи в изчислението.
| 123 | Sub TestAverageA ()Диапазон ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))End Sub |
В примера по -долу функцията AVERAGE връща различна стойност на функцията AVERAGEA, когато изчислението се използва за клетки A10 до A11
Отговорът за формулата AVERAGEA е по -нисък от формулата AVERAGE, тъй като заменя текста в A11 с нула и следователно усреднява над 13 стойности, а не 12 стойности, които AVERAGE изчислява.
Използване на AVERAGEIF
Функцията AVERAGEIF ви позволява да осредните сумата от диапазон от клетки, които отговарят на определени критерии.
| 123 | Под средно Ако ()Диапазон ("F31") = Функция на работен лист.AverageIf (Диапазон ("F5: F30"), "Спестявания", Обхват ("G5: G30"))End Sub |
Горната процедура ще осредни само клетките в диапазон G5: G30, където съответната клетка в колона F съдържа думата „Спестявания“ в нея. Критериите, които използвате, трябва да бъдат в кавички.
Недостатъци на функцията на работния лист
Когато използвате Работен лист Функция за да усредните стойностите в диапазон във вашия работен лист, се връща статична стойност, а не гъвкава формула. Това означава, че когато вашите цифри в Excel се променят, стойността, която е била върната от Работен лист Функция няма да се промени.
В горния пример процедурата TestAverage процедурата е създала средната стойност на B11: M11 и е поставила отговора в N11. Както можете да видите в лентата с формули, този резултат е цифра, а не формула.
Следователно, ако някоя от стойностите се промени в диапазона (B11: M11), резултатите в N11 ще НЕ промяна.
Вместо да използвате Работен лист Функция. Средно, можете да използвате VBA, за да приложите СРЕДНАТА функция към клетка, като използвате Формула или Формула R1C1 методи.
Използвайки метода на формулата
Методът на формулата ви позволява да посочите конкретно диапазон от клетки, например: B11: M11, както е показано по -долу.
| 123 | Sub TestAverageFormula ()Обхват ("N11"). Формула = "= Средно (B11: M11)"End Sub |
Използване на метода FormulaR1C1
Методът FomulaR1C1 е по -гъвкав, тъй като не ви ограничава до определен диапазон от клетки. Примерът по -долу ще ни даде същия отговор като този по -горе.
| 123 | Sub TestAverageFormula ()Обхват ("N11"). Формула = "= Средно (RC [-12]: RC [-1])"End Sub |
За да направим формулата по -гъвкава, бихме могли да променим кода така, че да изглежда така:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Брой (R [-11] C: R [-1] C)"End Sub |
Където и да сте в работния си лист, формулата ще осредни стойностите в 12 -те клетки директно вляво от него и ще постави отговора във вашия ActiveCell. Обхватът във функцията AVERAGE трябва да бъде посочен чрез синтаксиса на ред (R) и колона (C).
И двата метода ви позволяват да използвате формули за динамичен Excel в рамките на VBA.
Сега ще има формула в N11 вместо стойност.
