Excel에서 여러 vlookup 결과를 평균하는 방법은 무엇입니까?

동일한 조회 항목과 관련된 여러 값을 찾아 평균해야 할 때, 직접 공식을 사용하는 것이 가장 빠르고 유연한 방법 중 하나입니다. AVERAGEIF 또는 배열 공식은 추가 열을 생성하지 않고도 이를 쉽게 처리합니다.

빈 셀(예: F2)에 다음 공식을 입력하세요:

=AVERAGEIF(A1:A24,E2,C1:C24)

다음 키를 누르세요: Enter 공식 입력 후 Enter 키를 누릅니다. 그러면 A열에서 E2 셀에 있는 조회 값과 일치하는 모든 C열의 값들의 평균이 즉시 계산됩니다. 아래 예시를 참조하세요:

매개변수 설명 및 팁:

• A1:A24: 조회 값이 있는 범위.
• E2: 찾고자 하는 특정 값.
• C1:C24: 일치하는 값들을 평균할 범위.

대안적 접근 (배열 공식에 익숙한 사용자를 위한):

빈 셀에 다음 공식을 입력하고 Ctrl + Shift + Enter를 눌러 확인하세요:

=AVERAGE(IF(A1:A24=E2,C1:C24))

배열 공식은 각 비교를 개별적으로 처리하므로, 동적 배열을 지원하지 않는 Excel 버전에서 유용합니다. 오류를 방지하려면 범위가 정확히 같은 크기인지 신중히 확인하세요.

실용적인 시나리오 및 참고 사항:
- 필터링되지 않은 데이터 세트와 간단한 조회 요구사항에 적합합니다.
- 어느 범위에든 비어 있는 셀이 있으면, 평균 계산 시 무시됩니다.
- 동적 테이블이나 데이터 추가 시 보다 강력한 공식을 위해 테이블 참조를 사용하는 것이 좋습니다.
- 잘못된 셀 범위 불일치에 주의하세요. 이것은 잘못된 평균 또는 오류의 일반적인 원인입니다.

중복된 데이터를 기반으로 요약하고 집계해야 하는 경우, Kutools for Excel은 Advanced Combine Rows 유틸리티를 통해 실용적인 솔루션을 제공합니다. 이 도구는 한 단계로 일치하는 레코드의 평균, 합계 또는 개수를 빠르게 결합하거나 계산할 수 있어, 대규모 데이터 세트나 정기 보고서에 적합합니다.

1. 조회 열과 평균할 값이 포함된 데이터 테이블의 범위를 강조 표시하세요. 그런 다음 다음으로 이동하세요: Kutools > 텍스트 > 고급 행 병합. 스크린샷을 참조하세요:

2. 나타나는 대화 상자에서:

• 조회 값이 있는 열을 선택하고 기본 키를 클릭하세요.
• 대상 값이 있는 열을 선택한 후, 계산 > 평균을 클릭하세요.
• 다른 열에 대해 필요한 대로 조합 또는 계산 규칙을 설정하세요 — 예를 들어 쉼표로 텍스트를 결합하거나 합계, 최대값, 최소값을 적용합니다.

3. 설정을 적용하려면 Ok를 클릭하세요.

이제 중복된 조회 값을 가진 행들이 병합되었으며, 지정된 열의 값은 각각의 고유 조회 값에 대해 자동으로 평균화됩니다. 이는 요약 보고서 작성이나 데이터 압축에 특히 유용합니다.

실용적인 팁: 고급 행 병합을 사용하면 수작업 계산 및 실수 가능성을 최소화할 수 있습니다. 이 도구는 반복적으로 조회 값을 처리하고 신속한 요약을 원하는 사용자에게 적합합니다. 항상 결합하기 전에 올바른 열이 할당되었는지 다시 확인하세요. 특히 데이터 구조가 변경된 경우 더욱 그렇습니다.

데모: Kutools for Excel을 사용하여 여러 vlookup 결과 평균하기

피벗 테이블은 데이터를 요약하고 분석하는 데 동적이고 시각적인 접근 방식을 제공합니다. 피벗 테이블을 사용하면 조회 값별로 항목을 자동으로 그룹화하고, 각 그룹에 대해 대상 열의 평균을 표시할 수 있어 데이터 변경 시 업데이트되는 대화형 요약을 제공합니다.

가장 효과적인 시나리오: 이 접근 방식은 단일 조회 값에 초점을 맞추기보다는 모든 조회 값에 대한 전체 요약이 필요한 경우에 적합합니다. 또한 피벗 테이블은 빠른 데이터 탐색, 보고서 생성 및 결과를 정렬 가능하고 확장 가능한 형식으로 제시하고자 할 때에도 탁월합니다.

지침:

• 헤더를 포함한 전체 데이터 세트를 선택하세요.
• 삽입 > 피벗테이블 > 표 또는 범위에서. 필요에 따라 새 워크시트 또는 기존 워크시트에 피벗 테이블을 배치하세요.
• 피벗 테이블 필드 패널에서 조회 값이 있는 열을 행 영역으로 드래그하세요.
• 평균을 내고 싶은 열을 값 영역으로 드래그하세요. 값 필드를 클릭하고, 값 필드 설정을 선택한 후, 계산 유형을 평균으로 설정하세요.

결과적으로 각 고유 조회 값과 연결된 데이터에 대해 평균이 계산된 요약 테이블이 생성됩니다. 필요에 따라 그룹화를 변경하거나 필터를 적용하거나 세부 정보를 드릴다운할 수 있습니다.

장점: 공식이 필요 없으며, 동적 업데이트를 지원하며, 보고 및 데이터 탐색에 적합합니다.

단점: 데이터 변경 후 새로 고칠 때 추가 단계가 필요하며, 다른 공식에 직접 값을 추출하는 데 적합하지 않으며, 초기 설정에는 피벗 테이블에 대한 기본적인 이해가 필요합니다.

문제 해결 팁: 값이 평균 대신 개수 또는 합계로 표시되는 경우 필드 계산 설정을 확인하세요. 최상의 결과를 위해 열에 적절한 제목이 있는지 확인하고 피벗 테이블을 만들기 전에 중복된 열 이름을 명확히 하세요.

고급 사용자 및 정기적으로 업데이트되는 데이터를 관리하는 사용자의 경우, VBA 매크로를 사용하면 조회 값과 일치하는 모든 항목에 걸쳐 평균 과정을 자동화할 수 있습니다. 이 방법은 데이터를 반복적으로 처리하여 모든 일치 항목을 찾고 평균을 계산하므로, 대규모 데이터 세트 또는 반복 가능한 작업 흐름이 필요할 때 적합합니다.

적용 가능한 시나리오 및 참고 사항: VBA는 평균 계산을 자주 수행해야 하거나 보고서를 자동화하거나 비정형 데이터 레이아웃에 맞춰 유연성을 제공하는 경우 이상적입니다. VBA 매크로는 워크북에서 매크로를 활성화하는 데 익숙하고, 사용자 정의 출력이 필요한 경우 가장 잘 작동합니다.

1. 개발자 탭으로 이동하여 Visual Basic을 선택하거나 Alt + F11 키를 눌러 VBA 편집기를 열고 삽입 > 모듈을 클릭하세요. 아래 코드를 새 모듈에 복사하여 붙여넣으세요:

Sub AverageVlookupMatches()
    Dim lookupCol As Range
    Dim avgCol As Range
    Dim lookupValue As Variant
    Dim total As Double
    Dim count As Long
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set lookupCol = Application.InputBox("Select the lookup column", xTitleId, Selection.Address, Type:=8)
    Set avgCol = Application.InputBox("Select the column to average", xTitleId, , Type:=8)
    lookupValue = Application.InputBox("Enter lookup value", xTitleId, , Type:=2)
    
    Application.ScreenUpdating = False
    total = 0
    count = 0
    
    For i = 1 To lookupCol.Rows.Count
        If lookupCol.Cells(i, 1).Value = lookupValue Then
            If IsNumeric(avgCol.Cells(i, 1).Value) Then
                total = total + avgCol.Cells(i, 1).Value
                count = count + 1
            End If
        End If
    Next i
    
    If count > 0 Then
        MsgBox "Average of all matches: " & total / count, vbInformation, "Result"
    Else
        MsgBox "No matches found.", vbExclamation, "Result"
    End If
    
    Application.ScreenUpdating = True
End Sub

2. 코드를 붙여넣은 후 VBA 편집기를 닫습니다. 매크로를 실행하려면 Excel로 돌아가서 F5 키를 누르거나 실행을 클릭하세요. 프롬프트가 표시되면 조회 열, 평균할 값 열 및 조회 값을 선택하세요. 매크로는 메시지 상자에 계산된 평균을 표시합니다.

실용적인 팁 및 주의 사항: 조회 및 값 열의 행 수가 같고 선택한 영역 내에 빈 행이 없는지 확인하세요. 대상 열에 숫자가 아닌 값이 있는 항목은 무시됩니다. 최상의 자동화를 위해 필요에 따라 명명된 범위 또는 매크로 논리를 워크시트 레이아웃에 맞게 조정하세요.

문제 해결: '일치하는 항목이 없습니다'라는 메시지가 표시되면 조회 열에서 앞뒤 공백이나 데이터 유형 불일치를 확인하세요. 매크로가 실행되도록 설정되어 있는지 확인하세요.

Excel에서 평균/복합 연간 성장률 계산하기

Excel에서 이동/롤링 평균 계산하기

피벗 테이블을 사용하여 일/월/분기/시간별 평균 계산하기