=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)는 가중 평균을 구합니다. B열의 각 점수에 C열의 가중치를 곱하고, 그 곱을 더한 뒤, 합계를 가중치의 합으로 나눕니다.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Average | Result | |
| 2 | Homework | 85 | 20% | Weighted | 81.2 | |
| 3 | Quizzes | 78 | 30% | Plain AVERAGE | 80.75 | |
| 4 | Midterm | 72 | 20% | |||
| 5 | Final | 88 | 30% |
가중 성적은 81.2인 반면 일반 AVERAGE는 80.75를 냅니다. AVERAGE는 20%인 과제를 30%인 기말시험과 똑같이 취급하기 때문입니다. 기말시험 점수를 바꾸면 과제 점수를 같은 만큼 바꿀 때보다 가중 성적이 더 크게 움직입니다.
엑셀에는 WEIGHTED.AVERAGE 함수가 없으므로 SUMPRODUCT를 SUM으로 나누는 것이 표준 수식입니다. Google 스프레드시트에는 AVERAGE.WEIGHTED(B2:B5,C2:C5)가 있습니다.
가중 평균 수식의 원리
SUMPRODUCT는 두 범위를 행 단위로 곱하고 결과를 더합니다. 도우미 열로 풀어 쓰면 곱의 열과 그 SUM입니다:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Part | Score | Weight | Score x weight |
| 2 | Homework | 85 | 20% | 17.0 |
| 3 | Quizzes | 78 | 30% | 23.4 |
| 4 | Midterm | 72 | 20% | 14.4 |
| 5 | Final | 88 | 30% | 26.4 |
| 6 | Total | 100% | 81.2 | |
| 7 | Weighted average | 81.2 |
각 항목은 점수 곱하기 가중치만큼 기여합니다. 85 × 20%는 17.0, 78 × 30%는 23.4 하는 식입니다. 이것을 더하면 81.2가 됩니다. 가중치의 합이 100%이므로 여기서는 C6으로 나눠도 달라지는 것이 없지만, 합이 100%가 아닐 때 수식을 올바르게 유지해 주는 것이 이 나누기입니다.
합이 100%가 아닌 가중치
가중치가 백분율일 필요는 없습니다. 평점은 학점으로, 평균 가격은 수량으로 가중치를 둡니다. 가중치의 SUM으로 나누면 합계가 얼마든 처리됩니다.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Course | Grade points | Credits | Average | Result | |
| 2 | Math | 4 | 4 | Weighted GPA | 3.51 | |
| 3 | History | 3 | 3 | Plain average | 3.48 | |
| 4 | Biology | 3.7 | 4 | Total credits | 14 | |
| 5 | Art | 2.7 | 2 | |||
| 6 | Lab | 4 | 1 |
나누기가 없으면 수식은 평점이 아니라 평점 곱하기 학점의 합, 여기서는 49.2를 반환합니다. 나누기가 있으면 F2는 14학점으로 가중한 평점을 보여 줍니다. 4학점 과목은 평균을 자기 성적 쪽으로 끌어당기고, 1학점 실험 과목은 거의 움직이지 못합니다. B6을 2로 바꾸고 F2가 F3에 비해 얼마나 적게 바뀌는지 보세요.
가중치가 합이 정확히 100%인 백분율이라면 =SUMPRODUCT(B2:B5,C2:C5)만으로도 같은 결과가 나옵니다. 그래도 /SUM(...)은 남겨 두세요. 어느 날 가중치 하나가 바뀌어 합계가 105%가 되면, 나누기가 없는 수식은 틀리는데 시트 어디에도 그 사실이 드러나지 않습니다.
조건을 건 가중 평균
일부 행에만 가중치를 적용하려면 SUMPRODUCT 안에서 조건을 곱하고, 일치하는 가중치는 SUMIF로 더합니다. 아래에서는 지역별 평균 가격을 판매 수량으로 가중합니다.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Price | Qty | Region | Average price | |
| 2 | North | Apple | $1.20 | 100 | North | $1.45 | |
| 3 | South | Pear | $1.50 | 40 | South | $1.36 | |
| 4 | North | Pear | $1.50 | 60 | |||
| 5 | South | Apple | $1.20 | 120 | |||
| 6 | North | Plum | $2.00 | 40 | |||
| 7 | South | Plum | $2.00 | 20 |
North는 사과 100개, 배 60개, 자두 40개를 팔았으므로 평균 가격은 $1.45이며, 세 가격의 단순 평균보다 사과 가격에 더 가깝습니다. 조건 (A2:A7=F2)는 North 행에서 1, 나머지에서 0이므로 다른 행은 분자에 아무것도 더하지 않고, SUMIF는 분모에 North의 수량만 더합니다.
연습: 가중 평균 가격
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Batch | Price | Qty | Average | Result | |
| 2 | Jan | $4.20 | 100 | Weighted price | ||
| 3 | Feb | $4.50 | 40 | |||
| 4 | Mar | $3.90 | 250 | |||
| 5 | Apr | $4.80 | 10 | |||
| 6 | May | $4.10 | 120 |
직접 해 보세요: 같은 품목을 다섯 번에 나눠 서로 다른 가격으로 샀습니다. 각 구매 수량으로 가중한 단위당 평균 가격을 구하세요. 수식은 F2에 쓰세요.
가중 평균을 틀리게 만드는 실수
- 곱의 AVERAGE. 점수 × 가중치 열에
=AVERAGE(D2:D5)를 쓰면 가중치가 아니라 행 수로 나누므로 작고 의미 없는 숫자가 나옵니다. 곱의 SUM을 가중치의 SUM으로 나누세요. - 가중치가 아니라 개수로 나누기.
=SUMPRODUCT(B2:B6,C2:C6)/COUNT(B2:B6)는 모든 가중치가 1일 때만 맞습니다. - 어긋난 범위.
=SUMPRODUCT(B2:B6,C3:C7)는 각 값을 다음 행의 가중치와 짝짓습니다. 두 범위는 같은 행에서 시작하고 끝나야 하며, 크기가 다르면#VALUE!가 나옵니다. - 빈 가중치. 빈 가중치는 0으로 세므로 그 행은 조용히 빠집니다. 가중치가 빠졌을 때 계산을 멈추고 싶다면 먼저
=COUNTBLANK(C2:C6)로 확인하세요. - 평균의 평균. 반 평균 70(학생 10명)과 90(학생 30명)의 평균은 80이 아닙니다. 반 인원으로 가중하면 85가 됩니다. AVERAGEIF 페이지에서도 조건과 함께 같은 함정을 다룹니다.
자주 묻는 질문
엑셀에서 가중 평균은 어떻게 구하나요?
값이 B열, 가중치가 C열에 있다면 =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)를 씁니다. SUMPRODUCT가 각 값에 가중치를 곱해 더하고, 가중치의 합으로 나누면 평균이 됩니다.
가중치의 합이 100%여야 하나요?
가중치의 SUM으로 나누기만 하면 그럴 필요가 없습니다. 학점 3, 4, 2, 1이나 가중치 2, 1, 1도 똑같이 동작합니다. 나누기 없이 =SUMPRODUCT(B2:B5,C2:C5)만 쓰는 간단한 방법만 가중치의 합이 정확히 100%여야 합니다.
엑셀에 가중 평균 함수가 있나요?
없습니다. 엑셀에는 가중 평균을 구하는 내장 함수가 없으므로 SUMPRODUCT와 SUM 조합이 표준 수식입니다. Google 스프레드시트에서는 AVERAGE.WEIGHTED(B2:B5,C2:C5)가 같은 일을 합니다.
조건을 걸어 가중 평균을 구하려면 어떻게 하나요?
SUMPRODUCT에 조건을 더하고 가중치는 SUMIF로 더합니다: =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMIF(A2:A7,"North",C2:C7).
