여러 조건이 있는 경우 Excel에서 중앙값을 계산하는 방법은 무엇입니까?

Excel에서 데이터 세트의 중앙값을 계산하는 작업은 데이터 분석 및 보고서 작성에서 자주 필요로 하는 작업입니다. 단순한 범위에 대한 중앙값을 찾는 것은 표준 Excel 함수를 사용하여 빠르게 처리할 수 있지만, 특정 조건을 만족하는 데이터에서만 중앙값을 구해야 하는 상황이 종종 발생합니다. 예를 들어, 큰 데이터 세트에서 특정 날짜에 특정 제품의 중간 판매 금액을 알아내는 경우와 같습니다. 이러한 복잡한 조건부 연산을 전통적인 함수만으로 처리하는 것은 어려울 수 있습니다. 이 튜토리얼에서는 Excel에서 여러 조건을 가진 중앙값을 계산하기 위한 다양한 실용적인 솔루션을 소개하며, 수식 기반 접근법과 고급 요구사항을 위해 VBA를 사용한 자동화 방법도 살펴보겠습니다.

• 여러 조건을 충족하는 경우 중앙값 계산
• VBA 코드 - 여러 조건으로 중앙값 계산

여러 조건을 충족하는 경우 중앙값 계산

아래와 같은 데이터 범위가 있다고 가정하고, 여러분의 임무는 두 가지 기준을 충족하는 중앙값을 결정하는 것입니다. 예를 들어, 열 A에 값 'a'가 있고 열 C에 날짜 '2-Jan'이 있을 때 열 B의 중앙값을 결정하는 것입니다. 이 시나리오는 특히 영업 보고서, 학급 시험 결과 및 기타 비즈니스 또는 학술적 데이터 분석에서 여러 카테고리별로 필터링해야 할 때 흔히 발생합니다.

명확성을 위해 다음과 같이 워크시트를 준비합시다: Excel 시트에서 아래 이미지와 유사한 레이아웃으로 조건을 입력하십시오. 여기서 열 E는 열 A에 대한 기준을 나열하고, 열 F 및 이후 열의 1행은 열 C에서 날짜 기준을 나타냅니다.

여러 조건을 충족하는 중앙값을 계산하기 위해 MEDIAN 및 IF 함수를 활용하여 조건에 따라 필터링된 값을 생성하는 배열 수식을 사용할 수 있습니다. 실행 방법은 다음과 같습니다.

1. 중앙값 결과를 표시할 셀 F2를 클릭하고 다음 수식을 입력합니다.

=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))

이 수식은 각 행에서 열 A의 값이 E2의 조건과 일치하고 열 C의 값이 F1의 제목과 일치하는지 확인합니다. 두 조건이 모두 충족되면 열 B의 값을 수집하여 중앙값을 계산합니다.

2. 수식을 입력한 후 배열 수식이므로 Ctrl + Shift + Enter(단순 Enter 아님)를 누릅니다. Excel은 자동으로 수식을 중괄호 { }로 묶어 배열 수식임을 나타냅니다.

3. F2의 오른쪽 하단 모서리에서 채우기 핸들을 드래그하여 다른 관련 셀로 수식을 복사하여 다른 조건에서 중앙값을 계산합니다. 아래와 같이 수행하세요.

매개변수 설명 및 사용 팁: 수식에서 $A$2:$A$12는 첫 번째 조건(예: 제품 이름)을 포함하는 범위이고, $C$2:$C$12는 두 번째 조건(예: 날짜)을 위한 범위이며, $B$2:$B$12는 중앙값을 원하는 숫자 값을 포함하는 범위입니다. 자신의 워크시트에 맞게 이러한 범위를 조정하세요. 항상 절대 참조($ 기호)를 사용하여 수식을 복사할 때 범위가 이동하지 않도록 합니다.

주의 사항: 두 조건을 모두 충족하는 값이 없으면 수식은 #NUM! 오류를 반환합니다. 혼란을 피하려면 수식을 IFERROR로 감싸서 공백이나 사용자 정의 메시지를 반환하도록 할 수 있습니다.

=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")

중앙값 열에 빈 셀이나 숫자가 아닌 값이 포함되어 있지 않은지 확인하세요. 이렇게 되면 결과에 영향을 미칠 수 있습니다.

이 수식 기반 접근법은 비교적 간단한 조건(일반적으로 최대 2~3개의 기준)이 있는 경우 적합합니다. 설정이 빠르고 프로그래밍 기술이 필요 없습니다. 하지만 동적 조건 또는 더 큰 데이터 세트를 사용한 복잡한 필터링의 경우 배열 수식을 유지 관리하거나 수정하는 것이 번거로울 수 있습니다.

VBA 코드 - 여러 조건으로 중앙값 계산

많은 조건, 대규모 데이터 세트 또는 조건 자체가 자주 변경되는 경우와 같이 조건부 중앙값 계산을 자동화해야 하는 시나리오에서는 VBA 솔루션이 실용적인 대안을 제공할 수 있습니다. VBA를 사용하면 어떤 수의 조건에도 기초하여 중앙값을 계산하는 재사용 가능한 매크로를 만들 수 있습니다. VBA 기반 솔루션은 반복적인 분석을 간소화하거나 보고서 및 대시보드용 맞춤형 Excel 프로세스를 개발하려는 경우 특히 유용합니다.

조건부 중앙값 계산을 위해 VBA를 사용하는 단계는 다음과 같습니다.

1. 개발 도구 > Visual Basic을 클릭합니다. 새 Microsoft Visual Basic for Applications 창이 열립니다. 삽입 > 모듈을 클릭한 다음 아래 코드를 모듈에 붙여넣습니다.

Sub ConditionalMedian()
    Dim DataRange As Range
    Dim CriteriaRange1 As Range
    Dim CriteriaRange2 As Range
    Dim OutputRange As Range
    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim TempArr() As Double
    Dim i As Long
    Dim j As Long
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
    Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
    Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
    Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
    Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
    Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
    
    count = 0
    For i = 1 To DataRange.Rows.count
        If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
           CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
            ReDim Preserve TempArr(count)
            TempArr(count) = DataRange.Cells(i, 1).Value
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        OutputRange.Value = "No match"
    Else
        Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
        If count Mod 2 = 1 Then
            OutputRange.Value = TempArr(count \ 2)
        Else
            OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
        End If
    End If
End Sub

Sub QuickSort(arr() As Double, first As Long, last As Long)
    Dim i As Long
    Dim j As Long
    Dim pivot As Double
    Dim temp As Double
    
    i = first
    j = last
    pivot = arr((first + last) \ 2)
    
    Do While i <= j
        Do While arr(i) < pivot
            i = i + 1
        Loop
        
        Do While arr(j) > pivot
            j = j - 1
        Loop
        
        If i <= j Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            i = i + 1
            j = j - 1
        End If
    Loop
    
    If first < j Then
        QuickSort arr, first, j
    End If
    
    If i < last Then
        QuickSort arr, i, last
    End If
End Sub

2. 클릭 버튼(또는 F5를 누름)을 눌러 코드를 실행합니다. 필요한 범위를 선택하고 조건을 입력하라는 메시지가 표시됩니다. 프롬프트를 완료하면 모든 조건을 충족하는 중앙값 결과가 지정한 대상 셀에 출력됩니다.

이 매크로를 실행할 때마다 값 범위, 조건 범위, 조건 값 및 결과를 출력할 위치를 유연하게 선택할 수 있습니다. 또한 필요에 따라 더 많은 조건을 포함하도록 코드를 쉽게 수정할 수 있습니다.

팁 및 문제 해결: VBA 솔루션을 사용할 때 선택된 모든 범위의 길이가 동일하고 조건이 올바른 데이터 유형과 형식(예: 텍스트 vs. 날짜)과 일치하는지 확인하세요. 조건을 충족하는 값이 없는 경우 출력에는 "일치하지 않음"이 표시됩니다. 가장 안정적인 작동을 위해 매크로를 실행하기 전에 통합 문서를 저장하고 매크로 사용 요청 시 항상 매크로를 활성화하세요. 이 VBA 솔루션은 매크로 보안 설정에 익숙한 사용자에게 적합하며 자동화된 Excel 워크플로에 사용됩니다.

요약하자면, VBA 접근법은 수식만으로 처리하기 어렵거나 번거로운 복잡한 중앙값 계산을 자동화합니다. 변수 조건, 자주 재계산이 이루어지는 경우, 그리고 대규모 데이터 세트를 다룰 때 특히 유용합니다.

관련 기사:

• 범위 내 중앙값 계산