Excel 에서 가중 평균을 계산하는 방법은？

다음 스크린샷과 같은 쇼핑 목록이 있다고 가정해 보겠습니다. Excel 의 AVERAGE 함수는 무게나 수량을 고려하지 않고 평균 가격을 제공하지만， 이러한 경우 더 정확한 방법은 가중 평균을 계산하는 것입니다。 이는 무게나 빈도가 높은 항목이 최종 결과에 더 큰 영향을 미치도록 하여 단위당 실제 비용을 더 잘 반영합니다。

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

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

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

그리고Enter키를 눌러 결과를 얻으세요。

참고： 이 수식에서C2:C18은 가중치 열을，D2:D18은 가격 열을 참조합니다。 데이터 레이아웃에 따라 해당 범위를 필요에 맞게 조정하세요。SUMPRODUCT함수는 각 가중치를 해당 가격과 곱한 후 결과를 합산하고，SUM은 가중치의 총합을 계산하여 올바른 가중 평균을 산출합니다。 범위의 길이가 동일하고 데이터에 불일치하거나 빈 셀가 없는지 확인하세요。 그렇지 않으면 계산 오류가 발생할 수 있습니다。

계산된 가중 평균이 선호하는 것보다 소수점 자리가 너무 많거나 적게 표시되는 경우， 셀을 선택한 후소수점 자릿수 늘림버튼또는소수점 자릿수 줄임버튼을홈탭에서 클릭하여 표시되는 소수 자릿수을 필요에 따라 조정하세요。

#VALUE!와 같은 오류가 발생하면 참조된 각 셀에 숫자 값이 포함되어 있고 범위가 일치하는지 다시 확인하세요。 또한 정확한 결과를 얻기 위해 계산 범위에 머리글 행을 포함하지 마세요。 대규모 데이터셋을 사용할 때는 명확성과 유지보수 용이성을 위해 이름이 지정된 범위를 사용하는 것을 고려하세요。

이전 수식은 모든 항목에 대한 가중 평균 가격을 계산합니다。 실제 분석에서는 사과(Apples)와 같은 특정 카테고리에 대한 가중 평균만 계산해야 할 수도 있습니다。 이러한 경우 조건을 기준으로 수식을 개선하여 원하는 기준에 맞는 가중 평균을 계산할 수 있습니다。

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

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

그런 다음Enter키를 눌러 특정 기준을 충족하는 가중 평균을 계산하세요。 이 수식은 항목이 조건(이 경우 “Apple”)과 일치할 때만 각 가중치와 가격 쌍을 곱한 후 합산하고， 해당 항목의 가중치 합계로 나눕니다。

참고： 여기서B2:B18은 과일 열，C2:C18은 가중치，D2:D18은 가격입니다。 필요에 따라 “Apple”을 다른 항목으로 바꾸세요。 이 방법은 하나의 조건으로 필터링하는 데 효과적이며， 여러 기준(예： 과일 종류 및 공급업체)으로 필터링해야 하는 경우 보조 열 또는 더 고급 수식이 필요할 수 있습니다。

수식을 적용한 후 명확성을 위해 소수점 자릿수를 조정하고 싶을 수 있습니다。 결과 셀을 선택하고소수점 자릿수 늘림또는소수점 자릿수 줄임버튼을홈탭에서 사용하여 표시되는 소수 자릿수을 변경하세요。

수식이 예상치 못한 결과를 반환하면 대상 범위 내에 기준과 일치하는 항목이 있는지 확인하고， 숫자형으로 의도된 열에 빈 셀이나 텍스트 항목이 없는지 주의하세요。

경우에 따라 크기가 변하는 범위， 결측값이 포함된 범위， 또는 동시에 여러 기준을 적용하는 유연한 필터링이 필요한 가중 평균을 자주 계산해야 할 수도 있습니다。 수식이나 범위를 수동으로 업데이트하는 대신 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 에서 누적 합계/평균 계산하기