VBA 평균 – AVERAGE, AVERAGEA, AVERAGEIF

이 튜토리얼에서는 VBA에서 Excel 평균 함수를 사용하는 방법을 보여줍니다.

Excel AVERAGE 함수는 워크시트에서 값이 있는 범위 셀의 평균을 계산하는 데 사용됩니다. VBA에서는 WorksheetFunction 메서드를 사용하여 액세스합니다.

AVERAGE WorksheetFunction

WorksheetFunction 객체를 사용하면 Excel의 함수 삽입 대화 상자에서 사용할 수 있는 대부분의 Excel 함수를 호출할 수 있습니다. AVERAGE 함수도 그중 하나입니다.

Sub TestFunction
  Range("D33") = Application.WorksheetFunction.Average("D1:D32")
End Sub

AVERAGE 함수에는 최대 30개의 인수를 사용할 수 있습니다. 각 인수는 셀 범위를 참조해야 합니다.

아래 예제는 셀 B11에서 N11까지의 범위에 대한 평균을 계산합니다.

Sub TestAverage()
   Range("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))
End Sub

아래 예에서는 B11에서 N11에 있는 셀과 B12:N12에 있는 셀의 평균을 계산합니다. Application 객체는 생략할 수 있습니다.

Sub TestAverage()
  Range("O11") = WorksheetFunction.Average(Range("B11:N11"),Range("B12:N12")) 
End Sub

변수에 평균 함수를 사용한 결과값 저장하기

수식의 결과를 Excel 범위에 직접 다시 작성하는 대신 코드의 다른 곳에서 사용하고 싶을 수 있습니다. 이 경우 결과를 변수에 할당하여 나중에 코드에서 사용할 수 있습니다.

Sub AssignAverage()
   Dim result As Integer
'결과값을 변수에 저장합니다
   result = WorksheetFunction.Average(Range("A10:N10"))
'결과값을 메시지박스에 표시합니다
   MsgBox "셀 범위의 평균값 : " & result
End Sub

AVERAGE 함수에서 Range 객체 사용하기

Range 객체에 셀 그룹을 할당하고 해당 Range 객체를 WorksheetFunction 객체와 함께 사용할 수 있습니다.

Sub TestAverageRange()
   Dim rng As Range
'셀 범위를 변수에 할당합니다
   Set rng = Range("G2:G7")
'Average 함수에 Range 객체를 사용합니다
   Range("G8") = WorksheetFunction.Average(rng)
'Range 객체를 초기화합니다
  Set rng = Nothing
End Sub

AVERAGE 함수에서 Range 객체 여러개 사용하기

마찬가지로 여러 Range 객체의 평균을 계산할 수 있습니다

Sub TestAverageMultipleRanges() 
   Dim rngA As Range 
   Dim rngB as Range
'셀 범위를 변수에 할당합니다
   Set rngA = Range("D2:D10") 
   Set rngB = Range("E2:E10")   
'Average 함수수에 Range 객체를 사용합니다
Range("E11") = WorksheetFunction.Average(rngA, rngB)
 'Range 객체를 초기화합니다
  Set rngA = Nothing 
  Set rngB = Nothing
End Sub

AVERAGEA 함수 사용하기

AVERAGEA 함수는 셀 중 하나에 텍스트가 있더라도 범위의 모든 셀에서 평균을 생성한다는 점에서 AVERAGE 함수와 다르며, 텍스트를 0으로 바꾸고 평균을 계산할 때 포함시킵니다. AVERAGE 함수는 해당 셀을 무시하고 계산에 포함하지 않습니다.

Sub TestAverageA()
   Range("B8) = Application.WorksheetFunction.AverageA(Range("A10:A11")) 
End Sub

아래 예제 데이터의 경우 셀 A11에서 M11 범위에 대해서 AVERAGE 함수와 AVERAGEA 함수가 다른 값을 반환합니다.

AVERAGEA 수식의 경우 A11의 텍스트를 0으로 대체하므로 AVERAGE 수식이 계산하는 12개의 값 대신 13개의 값에 대한 평균을 합니다. 따라서 AVERAGEA의 결과 값이 AVERAGE 수식보다 작습니다.

AVERAGEIF 함수 사용하기

AVERAGEIF 함수를 사용하면 셀 범위 내에서 특정 기준을 충족하는 셀 그룹에 대해서 평균을 구할 수 있습니다.

Sub AverageIf()
   Range("F31") = WorksheetFunction.AverageIf(Range("F5:F30"), "Savings", Range("G5:G30"))
End Sub

위의 프로시저는 F 열의 셀 중에서 ‘Savings’ 와 일치하는 셀을 찾아서 해당 셀의 행 번호에 일치하는 G5:G30 범위의 셀들에 대한 평균 값을 계산합니다. 기준이 되는 값은 따옴표로 묶어야 합니다.

 

WorksheetFunction의 단점

WorksheetFunction을 사용하여 범위의 값의 평균을 구하는 경우 유연한 수식이 아닌 정적인 값이 반환됩니다. 즉, 범위의 수치가 변경되어도 WorksheetFunction이 반환한 값은 변경되지 않습니다.

위의 예에서 TestAverage 프로시저는 B11:M11의 평균을 생성하고 결과 값을 N11셀에 넣었습니다. 수식 입력줄에서 볼 수 있듯이 이 결과는 수식이 아닌 값 입니다.

따라서 B11:M11 범위에서 값이 변경되더라도 N11의 결과는 변경되지 않습니다.

WorksheetFunction.Average를 사용하는 대신 VBA에서 Formula 또는 FormulaR1C1 메서드를 사용하여 셀에 평균 함수 수식을 적용할 수 있습니다.

Formula 메서드 사용하기

Formula 메서드를 사용하면 아래와 같이 셀 범위(예: B11:M11)를 구체적으로 지정할 수 있습니다.

Sub TestAverageFormula()
  Range("N11").Formula = "=Average(B11:M11)"
End Sub

 

FormulaR1C1 메서드 사용하기

FomulaR1C1 메서드는 셀의 범위를 정해진 범위로 제한하지 않는다는 점에서 더 유연합니다. 아래 예제는 위와 동일한 답을 제공합니다.

Sub TestAverageFormula()
   Range("N11").Formula = "=Average(RC[-12]:RC[-1])"
End Sub

수식을 더 유연하게 만들려면 코드를 다음과 같이 수정할 수 있습니다:

Sub TestAverageFormula() 
   ActiveCell.FormulaR1C1 = "=Average(R[-11]C:R[-1]C)" 
End Sub

그러면 워크시트의 어느 위치에 있든 수식이 바로 왼쪽에 상단에 있는 12개 셀의 값의 평균을 구하여 ActiveCell에 결과 값을 배치합니다. AVERAGE 함수 내부의 범위는 행(R) 및 열(C) 구문을 사용하여 참조해야 합니다.

이 두 가지 방법을 사용하면 VBA를 사용하여 동적 Excel 수식을 사용할 수 있습니다.

이제 N11셀에 값 대신 수식이 있습니다.