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 셀이 유효한 행을 참조하는지 확인하세요. 그렇지 않으면 예상치 못한 결과 또는 "NA"가 표시될 수 있습니다.

(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에 대한 기본 이해가 필요하며 구조가 변경되면 절차를 유지해야 함.