Excel에서 가중 평균을 계산하는 방법은 무엇입니까?

아래 스크린샷에 표시된 것처럼 쇼핑 목록이 있다고 가정합시다. Excel의 AVERAGE 함수는 가중치나 수량을 고려하지 않고 단순히 평균 가격을 제공하지만, 이러한 경우에는 가중 평균을 계산하는 것이 더 정확합니다. 이렇게 하면 더 높은 가중치 또는 빈도를 가진 항목들이 최종 결과에 더 큰 영향을 미치므로 실질적인 개별 비용을 더 잘 반영할 수 있습니다.

가중 평균 가격을 계산하려면 다음과 같이 SUMPRODUCT와 SUM 함수를 조합하여 사용하세요:

F2와 같은 빈 셀을 선택하고 다음 수식을 입력하세요:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

그리고 결과를 얻기 위해 Enter 키를 누르세요.

참고: 이 수식에서 C2:C18은 가중치 열을, D2:D18은 가격 열을 나타냅니다. 자신의 데이터 구조에 맞게 범위를 조정하세요. SUMPRODUCT 함수는 각 가중치와 해당 가격을 곱하고 결과를 합산하며, SUM은 가중치의 총합을 계산하여 올바른 가중 평균을 도출합니다. 계산 오류를 방지하려면 길이가 같은 범위를 사용하고 데이터 내에 일치하지 않거나 비어 있는 셀이 없는지 확인하세요.

계산된 가중 평균이 너무 많은 소수점 자리 또는 너무 적은 소수점 자리를 표시한다면, 셀을 선택한 후 소수점 자릿수 증가 버튼 또는 소수점 자릿수 감소 버튼 을 클릭하세요 홈 탭에서 표시되는 소수점 자릿수를 필요에 맞게 조정하세요.

#VALUE!와 같은 오류가 발생하면, 참조된 모든 셀이 숫자 값을 포함하고 있고 범위가 일관성 있는지 다시 확인하세요. 또한 정확한 결과를 보장하기 위해 계산 범위에 헤더 행이 포함되지 않도록 하세요. 더 큰 데이터 세트 작업 시에는 명명된 범위를 사용하면 명확성과 유지보수가 쉬워집니다.

이전 공식은 모든 항목에 대한 가중 평균 가격을 계산합니다. 실제 분석에서는 사과에 대한 가중 평균 가격만 구하는 등 특정 카테고리에 대한 가중 평균을 원할 수 있습니다. 이런 경우 조건에 따라 공식을 확장하여 이를 포함시킬 수 있습니다.

이를 위해 F8과 같은 빈 셀을 선택하고 다음 수식을 입력하세요:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

그런 다음 Enter 키를 눌러 특정 조건을 충족하는 가중 평균을 계산합니다. 이 수식은 항목이 조건(이 경우 “사과”)과 일치하는 경우에만 각 가중치와 가격 쌍을 곱하고, 이를 합산한 후 해당 항목에 대한 가중치 총합으로 나눕니다.

참고: 여기에서 B2:B18은 과일 열, C2:C18은 가중치, D2:D18은 가격입니다. 필요에 따라 “사과”를 다른 항목으로 바꾸세요. 이 방법은 하나의 조건으로 필터링하는 데 효과적이지만, 여러 조건(예: 과일 종류 및 공급자)으로 필터링해야 하는 경우 도우미 열이나 더 복잡한 수식이 필요할 수 있습니다.

공식을 적용한 후 명확성을 위해 소수점을 조정하고 싶다면 결과 셀을 선택하고 소수점 자릿수 증가 또는 소수점 자릿수 감소 버튼을 클릭하세요 홈 탭에서 표시되는 소수점 자릿수를 변경하세요.

공식이 예상치 못한 결과를 반환하면, 목표 범위 내 조건과 일치하는 값이 있는지 확인하고, 숫자로 간주되어야 할 열에 빈 셀이나 텍스트 항목이 없는지 확인하세요.

특정 상황에서는 크기가 변하는 범위, 결측값이 포함된 범위, 또는 한 번에 여러 조건을 동시에 적용해야 하는 유연한 필터링이 필요한 가중 평균을 자주 계산해야 할 수 있습니다. 수식이나 범위를 수동으로 업데이트하는 대신, VBA 매크로를 사용하여 계산을 자동화하면 시간을 절약하고 오류 가능성을 줄일 수 있습니다. 특히 대규모 데이터나 정기적으로 업데이트되는 데이터 세트를 처리할 때 유용합니다.

다음은 가중 평균을 위한 VBA 매크로를 만들고 사용하는 방법입니다:

1. 개발 도구 > Visual Basic (또는 Alt + F11)을 클릭하여 Microsoft Visual Basic for Applications 편집기 창을 엽니다. 그런 다음 삽입 > 모듈을 클릭하고 새 모듈 창에 아래 코드를 붙여넣습니다:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. 누르세요 F5 (또는 실행 버튼)을 클릭하여 실행합니다.
매크로는 단계별로 범위를 선택하라는 메시지를 표시합니다 (조건 범위—필요 없으면 건너뛸 수 있음, 가중치 범위, 값 범위). 특정 조건을 입력하여 계산을 필터링하거나 모든 데이터를 고려하기 위해 비워둘 수도 있습니다. 이 매크로는 동적 데이터 범위를 지원하므로 표가 자주 변경되거나 성장할 때 유용합니다.

마지막으로 가중 평균 결과가 표시된 메시지 박스를 받게 됩니다.

팁:

• 이 방법은 반복적인 가중 평균 분석을 자동화하며, 추가 필터링이나 출력 옵션을 처리하도록 확장할 수 있습니다.
• 선택한 범위의 길이가 같고 데이터 유형이 일관되도록 하세요.
• 기본적인 오류 처리를 포함하세요 (예: 유효한 가중치가 발견되지 않거나 가중치 합계가 0인 경우).
• 필터링/보이는 행에만 적용하려면 특수 셀 열거를 통해 코드를 더욱 강화할 수 있습니다.

권한 또는 매크로 보안 문제를 겪는 경우 코드를 실행하기 전에 Excel 설정에서 매크로가 활성화되어 있는지 확인하세요.

Excel에서 반올림된 범위 평균

Excel에서 평균 변화율

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

Excel에서 이동/롤링 평균

Excel에서 누적 합계/평균