엑셀 VBA 기존 AVERAGE 평균 함수 수정해서 나만의 함수 만드는 방법

엑셀에서 제공하는 평균 함수인 AVERAGE 는 지정한 영역에 대해서 계산을 하게 됩니다. 그런데 지정한 영역에서 최소값과 최대값 범위에 해당하는 값으로만 평균을 계산하려면 어떻게 해야 할까요? 엑셀에서는 그런 함수가 존재하지 않습니다. 이렇게 사용자가 특정 기능을 수행하는 함수를 만들고 싶다면 사용자 정의 함수를 만들어야 합니다.

 

 

▼ 평균 함수는 AVERAGE 입니다. 그런데 영역에서 최대값과 최소값 범위에 해당하는 값들로만 평균을 구할 수 있는 방법은 무엇일까요? 기존에 제공하는 함수가 없기 때문에 직접 만들 수 밖에 없습니다.

 

▼ 사용자 정의 함수의 소스는 다음과 같습니다. 첫 번째 인수로 선택한 영역을 넘기고 두 번째, 세 번째 인수에는 최소값과 최대값을 넘기는 구조로 만듭니다. 그럼 내부에서는 넘어온 영역에 값을 하나씩 꺼내기 위해서 For Each 문을 사용하였습니다.

Function AVERAGEMAXMIN(rng As Range, lower As Integer, upper As Integer)
 
    Dim tot As Integer
    Dim co As Integer
    Dim cell As Range
 
    For Each cell In rng
       
    Next cell
      
End Function

 

▼ 다음은 For Each 반복문에 들어갈 소스 입니다. 최대값과 최소값에 들어가는 숫자만 평균을 구하기 위해 모두 더하는 것입니다.

If cell.Value >= lower And cell.Value <= upper Then
    tot = tot + cell.Value
    co = co + 1
End If

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 반복문 For Next 문 사용하기 ▶ 엑셀 VBA 함수 만들어서 매크로와 연결하기 ▶ 엑셀 VBA 셀 참조하는 여러가지 방법 ▶ 엑셀 VBA 통합 문서 문서가 열리는 순간 기능 구현하기 ▶ 엑셀 VBA 매크로 디버깅, 디버그 코드 분석하기

 

 

▼ For 문을 돌면서 구한 합계는 숫자의 개수로 나누어서 평균을 구합니다. 구한 평균은함수의 반환값으로 세팅 합니다.

MaxMinAverage = tot / co

 

▼ 아래는 지금까지 설명한 내용을 구현한 전체 소스입니다. 이 함수는 지정한 영역에서 사용자가 원하는 구간의 데이터를 가지고 평균을 구할 수 있습니다. 이렇게 엑셀에서 지원하지 않은 기능은 자신이 직접 만드는 고급 사용자가 돼 보시기 바랍니다.

Function AVERAGEMAXMIN(rng As Range, lower As Integer, upper As Integer)
 
    Dim tot As Integer
    Dim co As Integer
    Dim cell As Range
 
    For Each cell In rng
        If cell.Value >= lower And cell.Value <= upper Then
            tot = tot + cell.Value
            co = co + 1
        End If
    Next cell
   
    AVERAGEMAXMIN = tot / co
   
End Function

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 반복문 For Next 문 사용하기 ▶ 엑셀 VBA 함수 만들어서 매크로와 연결하기 ▶ 엑셀 VBA 셀 참조하는 여러가지 방법 ▶ 엑셀 VBA 통합 문서 문서가 열리는 순간 기능 구현하기 ▶ 엑셀 VBA 매크로 디버깅, 디버그 코드 분석하기