주어진 범위 평균 구하기 - 사용자 지정 함수

안녕하세요.

 

평균을 구할 범위와 기간을 정하면 범위로 선택한 데이터 중 최근 데이터 순서로 지정한 기간만큼 평균을 구하도록 사용자 지정 함수를 만들어 보겠습니다.

선택한 범위 모든 셀에 값이 들어 있는 것이 아니기 때문에 값이 들어 있는 셀만 대상으로 작업을 해야 합니다.

 

VBE를 실행해 코드를 작성합니다.

Public Function 주어진범위평균(범위 As Range, 개월 As Integer)
    Dim 배열(100)
    Dim 합 As Double
    
    i = 1
    For Each 셀 In 범위
        If 셀 <> "" Then
            배열(i) = 셀
            i = i + 1
        End If
    Next 셀
    
    i = i - 1
    
    If i < 개월 Then
        주어진범위평균 = "값 부족"
    Else
        For j = i To i - 개월 + 1 Step -1
            합 = 합 + 배열(j)
        Next j
    
        주어진범위평균 = 합 / 개월
    End If
End Function

계산할 범위 중 값이 들어 있는 셀 값을 저장하기 위해 배열을 지정했습니다.

배열의 크기를 최대 100으로 지정해서 100개 이상의 값이 있는 셀 범위를 지정한다면 올바른 결과가 나오지 않습니다.

 

지정한 범위 값이 들어 있는 셀 수가 기간보다 적은 경우에도 값을 제대로 구할 수 없기 때문에 "값 부족"이라는 결과를 표시합니다.

 

완성 파일 다운로드

주어진 범위 평균 구하기(사용자지정함수).xlsm

0.02MB

다른 엑셀 파일에서도 사용하려면 함수 모듈을 복사하거나 추가 기능으로 등록합니다.

 

2020/05/10 - [엑셀] - 어디에서나 사용자 정의 함수를 사용할 수 있도록 하기

어디에서나 사용자 정의 함수를 사용할 수 있도록 하기

---

코드를 조금 수정합니다.

 

셀이 비어 있는 경우 뿐만아니라 숫자가 아닌 값이 들어 있는 셀도 제외하고 결과값을 구하도록 수정합니다.

Public Function 주어진범위평균(범위 As Range, 개월 As Integer)
    Dim 배열(100)
    Dim 합 As Double
    
    i = 1
    For Each 셀 In 범위
        If VarType(셀.Value) >= vbInteger And VarType(셀.Value) <= vbCurrency Then
            배열(i) = 셀.Value
            i = i + 1
        End If
    Next 셀
    
    i = i - 1
    
    If i < 개월 Then
        주어진범위평균 = "값 부족"
    Else
        For j = i To i - 개월 + 1 Step -1
            합 = 합 + 배열(j)
        Next j
    
        주어진범위평균 = 합 / 개월
    End If
End Function

완성 파일 다운로드

주어진 범위 평균 구하기2(사용자지정함수).xlsm

0.02MB

VarType 함수는 VBA에서 셀 데이터 형식을 확인합니다.

이 함수를 이용해 숫자만 들어 있는 셀을 확인합니다.

 

docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function

VarType function (Visual Basic for Applications)