Цей підручник покаже вам, як користуватися функцією Excel Average у VBA.
СЕРЕДНЯ функція Excel використовується для обчислення середнього значення з клітинок діапазону на вашому аркуші, у яких є значення. У VBA доступ до нього здійснюється за допомогою методу WorksheetFunction.
СЕРЕДНЯ функція робочого аркуша
Об'єкт WorksheetFunction можна використовувати для виклику більшості функцій Excel, доступних у діалоговому вікні Вставка функції в Excel. Функція AVERAGE - одна з них.
| 123 | Субтестова функціяДіапазон ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
Ви можете мати до 30 аргументів у функції AVERAGE. Кожен з аргументів повинен посилатися на діапазон клітинок.
У цьому прикладі нижче буде отримано середнє значення суми комірок В11 -Н11
| 123 | Sub TestAverage ()Діапазон ("O11") = Application.WorksheetFunction.Average (Діапазон ("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 (Діапазон ("A10: N10"))'Покажіть результатMsgBox "Середнє значення для клітинок у цьому діапазоні" та результатEnd Sub |
AVERAGE з об'єктом Range
Ви можете призначити групу клітинок об'єкту Range, а потім використовувати цей об'єкт Range разом із Робочий лист Функція об'єкт.
| 123456789 | Sub TestAverageRange ()Зменшити діапазон як діапазон'призначити діапазон клітинокВстановити rng = Діапазон ("G2: G7")'використовуйте діапазон у формуліДіапазон ("G8") = Функція робочого аркуша. Середнє значення (rng)'випустіть об'єкт діапазонуВстановити rng = нічогоEnd Sub |
СЕРЕДНІ Об'єкти з кількома діапазонами
Так само можна обчислити середнє значення клітинок з кількох об’єктів діапазону.
| 123456789101112 | Підтест СереднєМногоразове ()Dim rngA As RangeDim rngB як діапазон'призначити діапазон клітинокВстановити rngA = Діапазон ("D2: D10")Встановити rngB = Діапазон ("E2: E10")'використовуйте діапазон у формуліДіапазон ("E11") = Функція робочого аркуша. Середнє значення (rngA, rngB)'випустіть об'єкт діапазонуВстановити rngA = НічогоВстановити rngB = нічогоEnd Sub |
Використання AVERAGEA
Функція AVERAGEA відрізняється від функції AVERAGE тим, що вона створює середнє значення з усіх клітинок у діапазоні, навіть якщо в одній з клітинок є текст - вона замінює текст нулем і включає це до обчислення середнього значення. Функція AVERAGE ігнорує цю клітинку і не враховуватиме її у обчисленні.
| 123 | Підтест СередняA ()Діапазон ("B8) = Application.WorksheetFunction.AverageA (Діапазон (" A10: A11 "))End Sub |
У наведеному нижче прикладі функція AVERAGE повертає інше значення функції AVERAGEA, коли обчислення використовується для клітинок A10 - A11
Відповідь на формулу AVERAGEA є нижчою за формулу AVERAGEA, оскільки вона замінює текст у A11 нулем, а отже, усереднює понад 13 значень, а не 12 значень, за якими розраховується AVERAGE.
Використання AVERAGEIF
Функція AVERAGEIF дозволяє вам усереднити суму діапазону клітинок, які відповідають певним критеріям.
| 123 | До середнього значенняIf ()Діапазон ("F31") = Функція робочого аркуша.AverageIf (Діапазон ("F5: F30"), "Економія", Діапазон ("G5: G30"))End Sub |
Вищеописана процедура буде лише усереднювати осередки в діапазоні G5: G30, де у відповідній комірці у стовпці F є слово "Економія". Критерії, які ви використовуєте, повинні бути в лапках.
Недоліки роботи аркуша
Коли ви використовуєте Робочий лист Функція щоб усереднити значення в діапазоні на вашому аркуші, повертається статичне значення, а не гнучка формула. Це означає, що коли ваші цифри в Excel змінюються, значення, яке було повернуто Робочий лист Функція не зміниться.
У наведеному вище прикладі процедура «Процедура TestAverage» створила середнє значення В11: М11 і помістила відповідь у N11. Як ви можете бачити на панелі формул, цей результат є цифрою, а не формулою.
Якщо будь -яке зі значень зміниться в діапазоні (B11: M11), результати в N11 будуть НІ зміна.
Замість того, щоб використовувати Робочий лист Функція. Середнє значення, ви можете використовувати VBA, щоб застосувати функцію AVERAGE до клітинки за допомогою Формула або Формула 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 замість значення буде формула.
