=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) tính trung bình có trọng số (weighted average): mỗi điểm trong cột B được nhân với trọng số của nó trong cột C, các tích được cộng lại, rồi tổng được chia cho tổng các trọng số.
| 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% |
Điểm có trọng số là 81,2, còn AVERAGE thông thường cho 80,75, vì hàm này coi bài tập về nhà, chiếm 20%, quan trọng ngang bài thi cuối kỳ, chiếm 30%. Đổi điểm thi cuối kỳ thì điểm có trọng số thay đổi nhiều hơn so với khi đổi cùng mức đó ở bài tập về nhà.
Excel không có hàm WEIGHTED.AVERAGE, nên SUMPRODUCT chia cho SUM là công thức chuẩn. Google Sheets có AVERAGE.WEIGHTED(B2:B5,C2:C5).
Công thức trung bình có trọng số hoạt động thế nào
SUMPRODUCT nhân hai vùng theo từng hàng rồi cộng các kết quả. Viết ra bằng một cột phụ, đó là một cột các tích cùng với SUM của chúng:
| 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 |
Mỗi phần đóng góp điểm của nó nhân với trọng số: 85 × 20% là 17,0, 78 × 30% là 23,4, và cứ thế tiếp tục. Tổng cộng là 81,2. Các trọng số cộng lại đúng 100%, nên chia cho C6 không thay đổi gì ở đây, nhưng chính phép chia này giữ cho công thức đúng khi tổng trọng số không bằng 100%.
Trọng số không cộng lại thành 100%
Trọng số không nhất thiết phải là phần trăm. GPA được tính trọng số theo số tín chỉ, giá trung bình theo số lượng. Chia cho SUM của các trọng số sẽ xử lý được mọi tổng.
| 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 |
Không có phép chia, công thức sẽ trả về tổng điểm nhân tín chỉ, ở đây là 49,2, chứ không phải GPA. Có phép chia, F2 hiện GPA tính theo 14 tín chỉ. Các môn bốn tín chỉ kéo điểm trung bình về phía điểm của chúng, còn môn thực hành một tín chỉ gần như không làm nó nhích: đổi B6 thành 2 và xem F2 thay đổi ít thế nào so với F3.
Nếu trọng số là phần trăm cộng lại đúng 100%, chỉ cần =SUMPRODUCT(B2:B5,C2:C5) cũng cho cùng kết quả. Dù vậy, hãy giữ /SUM(...): vào ngày một trọng số bị đổi và tổng thành 105%, công thức không có phép chia sẽ sai mà trên trang tính chẳng có gì báo hiệu.
Trung bình có trọng số kèm điều kiện
Để chỉ tính trọng số cho một số hàng, hãy nhân với một điều kiện bên trong SUMPRODUCT, và cộng các trọng số khớp điều kiện bằng SUMIF. Bên dưới, giá trung bình theo từng khu vực được tính trọng số theo số lượng bán ra.
| 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 bán 100 quả táo, 60 quả lê và 40 quả mận, nên giá trung bình là $1,45, gần giá táo hơn so với trung bình cộng thông thường của ba mức giá. Điều kiện (A2:A7=F2) bằng 1 ở các hàng North và bằng 0 ở các hàng khác, nên những hàng còn lại không cộng thêm gì vào tử số, và SUMIF chỉ cộng số lượng của North ở mẫu số.
Luyện tập: giá trung bình có trọng số
| 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 |
Đến lượt bạn: Bạn mua cùng một mặt hàng trong năm đợt với giá khác nhau. Tính giá trung bình mỗi đơn vị, có trọng số theo số lượng của từng đợt. Viết công thức vào F2.
Những lỗi làm sai trung bình có trọng số
- Lấy AVERAGE của các tích.
=AVERAGE(D2:D5)trên một cột điểm × trọng số sẽ chia cho số hàng chứ không chia cho trọng số, và cho ra một số nhỏ vô nghĩa. Hãy chia SUM của các tích cho SUM của các trọng số. - Chia cho số lượng thay vì trọng số.
=SUMPRODUCT(B2:B6,C2:C6)/COUNT(B2:B6)chỉ đúng khi mọi trọng số đều bằng 1. - Các vùng không thẳng hàng.
=SUMPRODUCT(B2:B6,C3:C7)ghép mỗi giá trị với trọng số của hàng kế tiếp. Hai vùng phải bắt đầu và kết thúc ở cùng các hàng; khác kích thước sẽ trả về#VALUE!. - Trọng số để trống. Trọng số trống được tính là 0, nên hàng đó bị bỏ qua một cách âm thầm. Nếu thiếu trọng số thì cần dừng phép tính, hãy kiểm tra trước bằng
=COUNTBLANK(C2:C6). - Lấy trung bình của các giá trị trung bình. Hai điểm trung bình lớp là 70 (10 học sinh) và 90 (30 học sinh) không cho trung bình 80. Tính trọng số theo sĩ số lớp thì kết quả là 85; trang AVERAGEIF có cùng cái bẫy này khi dùng điều kiện.
Câu hỏi thường gặp
Làm sao tính trung bình có trọng số trong Excel?
Dùng =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5), với giá trị ở cột B và trọng số ở cột C. SUMPRODUCT nhân mỗi giá trị với trọng số của nó rồi cộng các kết quả; chia cho tổng các trọng số sẽ biến tổng đó thành giá trị trung bình.
Tổng các trọng số có phải bằng 100% không?
Không, miễn là bạn chia cho SUM của các trọng số. Tín chỉ 3, 4, 2 và 1, hay trọng số 2, 1 và 1, đều dùng được theo cùng cách. Chỉ có cách viết tắt =SUMPRODUCT(B2:B5,C2:C5) không kèm phép chia mới đòi hỏi tổng trọng số đúng bằng 100%.
Excel có hàm WEIGHTED.AVERAGE không?
Không. Excel không có sẵn hàm tính trung bình có trọng số, nên kết hợp SUMPRODUCT và SUM là công thức chuẩn. Trong Google Sheets, AVERAGE.WEIGHTED(B2:B5,C2:C5) làm việc tương tự.
Làm sao tính trung bình có trọng số kèm điều kiện?
Thêm điều kiện vào SUMPRODUCT và dùng SUMIF cho các trọng số: =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMIF(A2:A7,"North",C2:C7).
