Este tutorial mostrará como usar a função Excel Average no VBA.
A função AVERAGE do Excel é usada para calcular uma média de um intervalo de células em sua planilha que contém valores. No VBA, ele é acessado através do método WorksheetFunction.
MÉDIA de planilhaFunção
O objeto WorksheetFunction pode ser usado para chamar a maioria das funções do Excel que estão disponíveis na caixa de diálogo Inserir Função no Excel. A função AVERAGE é uma delas.
| 123 | Sub TestFunctionIntervalo ("D33") = Application.WorksheetFunction.Aadise ("D1: D32")End Sub |
Você pode ter até 30 argumentos na função AVERAGE. Cada um dos argumentos deve se referir a um intervalo de células.
Este exemplo abaixo irá produzir a média da soma das células B11 a N11
| 123 | Sub TestA Average ()Range ("O11") = Application.WorksheetFunction.Aadise (Range ("B11: N11"))End Sub |
O exemplo abaixo irá produzir uma média da soma das células em B11 a N11 e a soma das células em B12: N12. Se você não digitar o objeto Aplicativo, ele será assumido.
| 123 | Sub TestA Average ()Intervalo ("O11") = WorksheetFunction.Awhile (Intervalo ("B11: N11"), Intervalo ("B12: N12"))End Sub |
Atribuindo um resultado MÉDIO a uma variável
Você pode querer usar o resultado de sua fórmula em outro lugar no código, em vez de gravá-lo diretamente em um intervalo do Excel. Se for esse o caso, você pode atribuir o resultado a uma variável para usar posteriormente em seu código.
| 1234567 | Sub AssignA Average ()Dim result As Integer'Atribuir a variávelresultado = WorksheetFunction.Aadise (Range ("A10: N10"))'Mostra o resultadoMsgBox "A média das células neste intervalo é" e resultadoEnd Sub |
AVERAGE com um objeto Range
Você pode atribuir um grupo de células ao objeto Range e, em seguida, usar esse objeto Range com o WorksheetFunction objeto.
| 123456789 | Sub TestA AverageRange ()Dim rng como alcance'atribuir o intervalo de célulasDefinir rng = intervalo ("G2: G7")'use o intervalo na fórmulaIntervalo ("G8") = WorksheetFunction.Aadise (rng)'libere o objeto de alcanceDefinir rng = NadaEnd Sub |
AVERAGE Multiple Range Objects
Da mesma forma, você pode calcular a média das células de vários objetos de intervalo.
| 123456789101112 | Sub TestA AverageMultipleRanges ()Dim rngA como intervaloDim rngB como intervalo'atribuir o intervalo de célulasDefinir rngA = intervalo ("D2: D10")Definir rngB = intervalo ("E2: E10")'use o intervalo na fórmulaIntervalo ("E11") = WorksheetFunction.Aadise (rngA, rngB)'libere o objeto de alcanceDefinir rngA = NadaDefinir rngB = NadaEnd Sub |
Usando AVERAGEA
A função AVERAGEA difere da função AVERAGE por criar uma média de todas as células em um intervalo, mesmo se uma das células contiver texto - ela substitui o texto por um zero e inclui isso no cálculo da média. A função AVERAGE ignora essa célula e não a considera no cálculo.
| 123 | Sub TestA AverageA ()Intervalo ("B8) = Application.WorksheetFunction.AadiseA (Intervalo (" A10: A11 "))End Sub |
No exemplo abaixo, a função AVERAGE retorna um valor diferente para a função AVERAGEA quando o cálculo é usado nas células A10 a A11
A resposta para a fórmula MÉDIA é inferior à fórmula MÉDIA, pois substitui o texto em A11 por um zero e, portanto, calcula a média de 13 valores em vez dos 12 valores sobre os quais MÉDIA está calculando.
Usando AVERAGEIF
A função AVERAGEIF permite calcular a média da soma de um intervalo de células que atendem a determinados critérios.
| 123 | Sub AverageIf ()Intervalo ("F31") = WorksheetFunction.AadiseIf (Intervalo ("F5: F30"), "Economia", Intervalo ("G5: G30"))End Sub |
O procedimento acima apenas fará a média das células no intervalo G5: G30, onde a célula correspondente na coluna F contém a palavra ‘Economia’. Os critérios que você usa devem estar entre aspas.
Desvantagens de WorksheetFunction
Quando você usa o WorksheetFunction para calcular a média dos valores em um intervalo em sua planilha, um valor estático é retornado, não uma fórmula flexível. Isso significa que quando seus números no Excel mudam, o valor que foi retornado pelo WorksheetFunction não mudará.
No exemplo acima, o procedimento TestA Average criou a média de B11: M11 e colocou a resposta em N11. Como você pode ver na barra de fórmulas, esse resultado é uma figura e não uma fórmula.
Se algum dos valores mudar, portanto, no intervalo (B11: M11), os resultados em N11 irão NÃO mudança.
Em vez de usar o WorksheetFunction.Aadise, você pode usar o VBA para aplicar a função AVERAGE a uma célula usando o Fórmula ou FormulaR1C1 métodos.
Usando o Método da Fórmula
O método da fórmula permite que você aponte especificamente para um intervalo de células, por exemplo: B11: M11 conforme mostrado abaixo.
| 123 | Sub TestA AverageFormula ()Intervalo ("N11"). Fórmula = "= Média (B11: M11)"End Sub |
Usando o método FormulaR1C1
O método FomulaR1C1 é mais flexível, pois não o restringe a um determinado intervalo de células. O exemplo abaixo nos dará a mesma resposta que o anterior.
| 123 | Sub TestA AverageFormula ()Intervalo ("N11"). Fórmula = "= Média (RC [-12]: RC [-1])"End Sub |
No entanto, para tornar a fórmula mais flexível, poderíamos alterar o código para ficar assim:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Contagem (R [-11] C: R [-1] C)"End Sub |
Onde quer que você esteja em sua planilha, a fórmula fará a média dos valores nas 12 células diretamente à esquerda dela e colocará a resposta em seu ActiveCell. O intervalo dentro da função AVERAGE deve ser referido usando a sintaxe de linha (R) e coluna (C).
Ambos os métodos permitem que você use fórmulas do Excel Dinâmico no VBA.
Agora haverá uma fórmula em N11 em vez de um valor.
