Excel 에서 동적 범위의 평균을 계산하는 방법은 무엇인가요？

수식은 월별 판매 실적이나 누적 합계처럼 범위의 시작점이나 끝점이 자주 변경되는 경우 동적 범위의 평균을 계산하는 데 매우 유연한 방법입니다。 입력 셀을 통해 동적 범위의 경계를 지정하면 수식을 다시 작성하지 않고도 업데이트된 데이터에 즉시 대응할 수 있습니다。

설정하려면 C4 셀과 같은 빈 셀을 선택하고 다음 수식을 입력하세요：

=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2)))

그런 다음Enter키를 눌러 결과 평균을 확인하세요。

이 수식은 A2 부터 C2 셀에 지정된 행까지 모든 셀을 자동으로 포함하도록 범위를 조정하므로 C2 의 값이 변경되면 평균을 산출하는 범위도 함께 변경됩니다。 이는 새 데이터가 추가되거나 특정 하위 집합만 분석하고자 할 때 평균 범위를 유연하게 확장하거나 축소하는 데 유용합니다。

참고:

(1) 이 수식에서=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2))):A2는 평균을 계산할 범위의 첫 번째 셀을 나타내며，C2는 대상 범위의 마지막 셀 행 번호를 저장하는 셀을 가리킵니다。 필요에 따라 데이터 구조에 맞게 이러한 참조를 수정하세요。 C2 셀이 유효한 행을 참조하지 않으면 예기치 않은 결과나 「#N/A」 오류가 발생할 수 있습니다。

(2) 대안으로 다음 수식을 사용할 수도 있습니다：

=AVERAGE(INDIRECT("A2:A"&C2))

이 방법 역시 범위에 대한 텍스트 참조를 생성하고 이를INDIRECT함수가 동적으로 해석하기 때문에 동일한 효과를 냅니다。 그러나 INDIRECT 함수는 닫힌 통합 문서나 대규모 데이터셋과 함께 사용할 경우 계산 속도에 영향을 줄 수 있으므로， 변동성이 큰 데이터에서는 INDEX 함수보다 효율성이 떨어질 수 있음을 주의하세요。

실무 팁： 데이터가 지속적으로 증가하는 경우(예： 매일 새로운 행을 추가하는 경우)， COUNTA 또는 COUNT 함수를 사용해 상한 셀 참조를 자동으로 설정할 수 있습니다。 이렇게 하면 동적 범위가 항상 최신 항목을 포함하도록 보장됩니다。

적용 가능한 시나리오： 일일 데이터 로그， 시계열 항목 또는 범위의 시작/끝이 사용자 입력이나 요약 셀에 의해 결정되는 분석。 장점： 추가 도구 없이 직접 적용 가능。 한계： 행 위치가 크게 변경될 경우 수식을 수동으로 조정해야 합니다。

범위가 위치가 아닌 특정 조건(예： 지역， 카테고리 또는 사용자 정의 레이블)에 따라 정의되는 경우， 동적 이름 범위와 INDIRECT 같은 함수를 결합하여 계산을 조정할 수 있습니다。 이는 특히 사용자가 드롭다운 목록에서 선택하면 즉시 관련 평균을 표시해야 하는 대시보드에 매우 유용합니다。

먼저 데이터셋을 머리글 행 또는 열 기준으로 그룹화하세요。 방법은 다음과 같습니다：

1. 전체 영역(A1:D11 등)을 선택한 후선택에서 생성버튼을 클릭하세요。이는이름 관리자창에 있습니다。 팝업 대화상자에서맨 위 행및가장 왼쪽 열옵션을 모두 선택한 후확인을 클릭하세요。 이 단계를 통해 행과 열의 데이터에 자동으로 이름 범위가 지정되어 수식에서 참조하기가 간편해집니다。

2. 원하는 빈 셀에 다음 수식을 입력하세요：

=AVERAGE(INDIRECT(G2))

여기서G2는 사용자가 행 또는 열 머리글 이름을 입력하거나 선택하는 조건 셀입니다. G2 값이 변경되면("Region1"에서 "Region2"로 전환되는 경우 등) 수식은 해당 범위의 평균을 동적으로 계산합니다. #REF! 오류를 방지하려면 G2 의 입력값이 정의된 이름과 정확히 일치해야 하며(대소문자 구분 포함)， 이를 항상 확인하세요。

최적 활용처： 리포팅 대시보드， 조건 기반 분석。 장점： 사용자 상호작용을 통해 매우 유연한 동적 보고서 또는 단일 셀 분석이 가능함。 한계： 적절한 이름 관리와 일관된 입력값이 필요함。

Excel 에서 채울 색상별로 셀 자동 개수/합계/평균 계산

때때로 셀을 채울 색상로 표시한 후 나중에 해당 셀의 개수를 세거나 합계를 구하거나 평균을 계산해야 할 수 있습니다. Kutools for Excel 의색상별 통계유틸리티를 사용하면 이를 쉽게 해결할 수 있습니다。

마지막 N 행의 평균 계산， 여러 동적 조건에 따른 평균 계산， 또는 여러 시트의 데이터를 결합하는 등 고급 동적 동작이 필요한 경우 사용자 정의 VBA 매크로를 작성할 수 있습니다。 이 방법은 기본 제공 수식이 너무 복잡해지거나 자주 변경되는 구조에 자동으로 대응해야 할 때 특히 유용합니다。

예를 들어 사용자가 입력한 N 값에 따라 A 열의 마지막 N 행에 대한 평균을 계산하거나， 사용자가 제한된 범위한 비연속 셀의 값을 평균 내고자 할 수 있습니다。

1.개발 도구 > Visual Basic을 선택하여Microsoft Visual Basic for Applications편집기를 엽니다。 그런 다음삽입 > 모듈을 선택하고 다음 VBA 코드를 붙여넣으세요：

Sub DynamicAverage_LastNRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim N As Long
    Dim result As Double
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    N = Application.InputBox("How many last rows to average?", xTitleId, 5, Type:=1)
    
    If N <= 0 Or N > lastRow - 1 Then
        MsgBox "Invalid input for N!", vbExclamation
        Exit Sub
    End If
    
    Set rng = ws.Range("A" & lastRow - N + 1, "A" & lastRow)
    result = Application.WorksheetFunction.Average(rng)
    
    MsgBox "Average of the last " & N & " rows in column A: " & result, vbInformation
End Sub

2.실행 버튼을 클릭하세요。 팝업 대화상자에서 평균을 계산할 마지막 행 수(예： 5,10 등)를 입력하고 확인을 누르면 결과가 메시지 상자에 표시됩니다。

더 복잡한 조건(예: 특정 기준에 따른 평균 또는 여러 시트에서 데이터를 가져와 평균 계산)으로 평균을 구하려면 VBA 코드를 적절히 수정할 수 있습니다. 예를 들어 기준 값을 입력받는 InputBox 를 추가하거나 여러 워크시트를 순회하며 병합 범위한 후 평균을 계산하도록 할 수 있습니다。

이 접근법은 복잡하거나 반복적인 동적 평균 계산을 자동화하는 데 최대한의 유연성을 제공합니다。 다만 매크로를 활성화하고 신뢰할 수 있는 통합 문서에서만 사용하여 보안 위험을 방지해야 합니다。 새로운 매크로를 실행하기 전에는 작업 내용을 저장하고， 자동화된 변경 사항에 대비해 백업을 고려하세요。

장점: 자동화 가능, 복잡하거나 대규모 데이터 시나리오 처리 가능, 특정 비즈니스 로직에 맞춤화 가능. 단점: VBA 에 대한 기본 이해가 필요하며 구조 변경 시 절차 유지보수가 필요함。