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