Cách tính trung bình gia quyền trong Excel: 3 cách dễ dàng trong năm 2026

Để tính trung bình trọng số trong Excel, hãy nhân mỗi giá trị với trọng số của nó, cộng các kết quả lại và chia cho tổng các trọng số. Công thức nhanh nhất là =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4), trong đó cột B chứa các giá trị và cột C chứa các trọng số. Nếu các trọng số của bạn đã cộng lại bằng 100%, chỉ riêng SUMPRODUCT đã cho ra kết quả.
Hướng dẫn này giải thích trung bình trọng số là gì và khi nào nên sử dụng nó. Sau đó, hướng dẫn sẽ đề cập đến ba cách để tính trung bình trọng số trong Excel: SUMPRODUCT kết hợp với SUM, một cột phụ, và SUMPRODUCT với các điều kiện. Mỗi phương pháp đều đi kèm một ví dụ thực tế. Chi tiết về các hàm được lấy từ các trang hỗ trợ của Microsoft, truy cập vào ngày 17 tháng 11 năm 2026.
Trung bình trọng số là gì
Một giá trị trung bình thông thường sẽ coi mọi giá trị đều như nhau. Microsoft mô tả hàm AVERAGE là trả về "trung bình (trung bình cộng) của các đối số." Mỗi con số được tính một lần.
Trung bình trọng số giúp một số giá trị có sức ảnh hưởng lớn hơn những giá trị khác. Mỗi giá trị có một trọng số, và các giá trị có trọng số lớn hơn sẽ kéo kết quả về phía chúng.
Công thức rất đơn giản:
Trung bình trọng số = (giá trị 1 × trọng số 1 + giá trị 2 × trọng số 2 + …) ÷ (trọng số 1 + trọng số 2 + …)
Dưới đây là một ví dụ nhanh. Điểm một khóa học được tính từ bài tập về nhà, bài kiểm tra giữa kỳ và bài thi cuối kỳ. Điểm thi cuối kỳ chiếm một nửa số điểm, vì vậy điểm trung bình thông thường sẽ cho ra kết quả sai.
| Thành phần | Điểm số | Trọng số |
|---|---|---|
| Bài tập về nhà | 85 | 20% |
| Giữa kỳ | 78 | 30% |
| Thi cuối kỳ | 92 | 50% |
Điểm trung bình thông thường của ba điểm số là 85. Kết quả trung bình trọng số là 86.4, vì điểm thi cuối kỳ cao chiếm trọng số lớn nhất. Sự khác biệt đó là lý do tại sao trung bình trọng số lại quan trọng.
Khi nào nên sử dụng trung bình trọng số
Hãy sử dụng trung bình trọng số bất cứ khi nào một số mục cần được tính nhiều hơn những mục khác. Các trường hợp phổ biến bao gồm:
- Điểm số. Các bài kiểm tra, dự án và bài tập về nhà có trọng số khác nhau.
- Giá cả. Giá trung bình được trả cho các đơn hàng có quy mô khác nhau.
- Danh mục đầu tư. Tỷ suất sinh lời trung bình trên các tài sản nắm giữ có giá trị khác nhau.
- Khảo sát. Các câu trả lời được tính trọng số theo quy mô nhóm hoặc trọng số mẫu.
- Hàng tồn kho. Chi phí trung bình trọng số của hàng hóa được mua ở các mức giá khác nhau.
- KPIs. Điểm số được xây dựng từ nhiều chỉ số có tầm quan trọng khác nhau.
Trọng số có thể đến từ nhiều nguồn khác nhau. Đôi khi chúng được thiết lập theo một quy tắc, chẳng hạn như đề cương môn học quy định bài thi cuối kỳ chiếm 50%. Đôi khi chúng đến từ chính dữ liệu, chẳng hạn như số lượng đơn vị đã bán hoặc giá trị của từng tài sản nắm giữ. Trong cả hai trường hợp, phép toán đều giống nhau.
Nếu mọi mục đều quan trọng như nhau, một điểm trung bình thông thường là đủ. Nếu một mục rõ ràng xứng đáng có tầm ảnh hưởng lớn hơn đến kết quả, bạn cần có trọng số.
Thiết lập dữ liệu của bạn
Hãy đặt dữ liệu của bạn vào một bảng đơn giản trước khi viết bất kỳ công thức nào. Sử dụng một hàng cho mỗi mục và các cột sau:
| Cột | Nội dung bên trong |
|---|---|
| A | Tên mục, chẳng hạn như Bài tập về nhà |
| B | Giá trị, chẳng hạn như điểm số |
| C | Trọng số, dưới dạng số hoặc phần trăm |
Trọng số có thể là tỷ lệ phần trăm, chẳng hạn như 20%, hoặc các con số thông thường, chẳng hạn như số lượng đơn vị đã bán. Cả hai đều hoạt động với các công thức bên dưới, miễn là bạn chia cho tổng các trọng số.
Hai bước kiểm tra sau sẽ giúp ngăn chặn hầu hết các lỗi. Đầu tiên, hãy đảm bảo mọi giá trị đều có trọng số nằm trên cùng một hàng. Thứ hai, giữ cho các vùng chọn giá trị và trọng số có cùng kích thước, nếu không công thức sẽ trả về lỗi.
Việc chuyển đổi vùng chọn thành một bảng Excel cũng rất hữu ích. Chọn vùng đó, sau đó chọn Home > Format as Table. Các hàng mới sau đó sẽ tự động mở rộng bảng, và các công thức sử dụng tham chiếu bảng sẽ tự động cập nhật chúng.
Phương pháp 1: SUMPRODUCT và SUM
Đây là cách tiêu chuẩn để tính trung bình trọng số trong Excel. Nó sử dụng hai hàm trong một công thức.
Microsoft cho biết SUMPRODUCT "trả về tổng của các tích của các dải ô hoặc mảng tương ứng." Nói một cách dễ hiểu, nó nhân mỗi giá trị với trọng số của nó rồi cộng các kết quả lại.
Cú pháp là =SUMPRODUCT(array1, [array2], [array3], ...). Đối với ví dụ về điểm số của chúng ta, các giá trị nằm trong ô B2:B4 và các trọng số nằm trong ô C2:C4. Hãy nhập:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
Dưới đây là những gì Excel thực hiện:
- SUMPRODUCT nhân 85 với 20%, 78 với 30%, và 92 với 50%.
- Nó cộng ba tích lại: 17 + 23.4 + 46 = 86.4.
- SUM cộng các trọng số: 20% + 30% + 50% = 100%.
- Công thức chia 86.4 cho 100%, cho ra kết quả là 86.4.
Khi các trọng số cộng lại bằng 100%, phép chia không làm thay đổi kết quả. Tuy nhiên, bạn vẫn nên giữ nó. Nếu sau này ai đó thay đổi trọng số và tổng số trở thành 90%, công thức vẫn cho ra kết quả chính xác.
Microsoft liệt kê hai quy tắc quan trọng đối với SUMPRODUCT ở đây. Các dải ô "phải có cùng kích thước", nếu không hàm sẽ trả về lỗi #VALUE!. Và SUMPRODUCT "coi các mục nhập mảng không phải là số như thể chúng là số không." Một trọng số trống hoặc dạng văn bản sẽ âm thầm được tính là số không, vì vậy hãy kiểm tra dữ liệu của bạn.
Microsoft cũng cảnh báo việc tham chiếu toàn bộ cột. Họ cho biết SUMPRODUCT "không nên được sử dụng với các tham chiếu toàn bộ cột" để đạt hiệu suất tốt nhất. Hãy sử dụng B2:B500, thay vì B:B.
Phương pháp 2: Một cột phụ
Một số người thích xem từng bước thực hiện hơn. Một cột phụ sẽ hiển thị từng tích số, giúp việc kiểm tra và giải thích trung bình trọng số trở nên dễ dàng hơn.
- Trong cột D, hãy nhập một tiêu đề như Giá trị × Trọng số.
- Trong ô D2, nhập
=B2*C2và sao chép công thức xuống dưới. - Bên dưới hàng cuối cùng, tính tổng cột D bằng công thức
=SUM(D2:D4). - Tính tổng các trọng số bằng công thức
=SUM(C2:C4). - Chia tổng thứ nhất cho tổng thứ hai.
Đối với ví dụ về điểm số, cột D hiển thị các giá trị 17, 23.4, và 46. Chúng cộng lại bằng 86.4. Các trọng số cộng lại bằng 100%, vì vậy trung bình trọng số là 86.4.
Đây là cùng một phép toán mà SUMPRODUCT thực hiện trong một bước. Trang riêng của Microsoft chỉ ra rằng một công thức dài hơn như =C2*D2+C3*D3+C4*D4+C5*D5 sẽ cho ra "cùng một kết quả" như SUMPRODUCT.
Hãy sử dụng cột phụ cho mục đích giảng dạy, kiểm toán hoặc bất kỳ trang tính nào mà người khác cần theo dõi logic. Hãy sử dụng Phương pháp 1 khi bạn muốn có một trang tính gọn gàng.
Phương pháp 3: SUMPRODUCT với các điều kiện
Đôi khi bạn chỉ cần tính toán cho một phần dữ liệu. Ví dụ, bạn có thể muốn tính giá trung bình được trả ở một khu vực, được tính trọng số theo số lượng đơn vị.
SUMPRODUCT có thể kiểm tra các điều kiện ngay bên trong công thức. Ví dụ của Microsoft đếm số lượng quả anh đào được bán ở một khu vực bằng công thức =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). Các phép so sánh trả về TRUE hoặc FALSE, hoạt động như 1 hoặc 0 khi được nhân lên.
Hãy áp dụng ý tưởng tương tự ở đây. Giả sử cột A chứa khu vực, cột B chứa giá cả, và cột C chứa số lượng đơn vị. Để có được giá trung bình trọng số cho khu vực phía Đông (East):
=SUMPRODUCT((A2:A100="East")*B2:B100*C2:C100)/SUMIF(A2:A100,"East",C2:C100)
Phần phía trên cộng tích của giá nhân với số lượng đơn vị, nhưng chỉ dành cho các hàng thuộc khu vực phía Đông (East). Phần phía dưới sử dụng SUMIF để chỉ cộng số lượng đơn vị cho các hàng thuộc khu vực phía Đông. Microsoft giải thích rằng SUMIF tính tổng "các giá trị trong một dải ô đáp ứng tiêu chí mà bạn chỉ định."
Hãy đặt tên khu vực vào một ô thay vì nhập trực tiếp chữ "East". Sau đó, bạn có thể thay đổi ô đó để xem từng khu vực mà không cần chỉnh sửa công thức.
Đối với hai điều kiện, chẳng hạn như khu vực và tháng, hãy thêm phép so sánh thứ hai vào phần phía trên. Sử dụng SUMIFS thay vì SUMIF ở phần phía dưới.
Ví dụ thực tế: giá trung bình trọng số
Điểm số là ví dụ kinh điển. Giá cả cũng phổ biến không kém trong công việc. Dưới đây là một ví dụ về mua hàng.
Một cửa hàng đã mua cùng một sản phẩm ba lần với các mức giá khác nhau. Giá được tính trên mỗi đơn vị sản phẩm.
| Đơn hàng | Số lượng đơn vị | Đơn giá | Số lượng × đơn giá |
|---|---|---|---|
| Đơn hàng 1 | 10 | 4.00 | 40.00 |
| Đơn hàng 2 | 25 | 3.20 | 80.00 |
| Đơn hàng 3 | 15 | 5.00 | 75.00 |
| Tổng cộng | 50 | 195.00 |
Điểm trung bình thông thường của ba mức giá là 4.07. Con số đó gây hiểu lầm, bởi vì hầu hết các đơn vị sản phẩm đều đến từ đơn hàng rẻ nhất.
Trung bình trọng số sử dụng số lượng đơn vị làm trọng số:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)
Phần phía trên là 195. Phần phía dưới là 50. Giá trung bình trọng số là 3.90 trên mỗi đơn vị. Đây là chi phí trung bình thực tế của hàng tồn kho, và đây là con số cần sử dụng để tính toán biên lợi nhuận hoặc lập kế hoạch tái đặt hàng.
Lưu ý rằng các trọng số ở đây là số lượng đơn vị, không phải tỷ lệ phần trăm. Điều đó hoàn toàn ổn. Việc chia cho tổng các trọng số có thể xử lý bất kỳ loại trọng số nào.
Trung bình trọng số trong PivotTable
PivotTable giúp tóm tắt các bảng lớn một cách nhanh chóng, nhưng các tùy chọn tích hợp sẵn của chúng không bao gồm số trung bình trọng số. Tùy chọn Average trong PivotTable là điểm trung bình thông thường. Có một giải pháp thay thế đơn giản.
- Trong bảng nguồn của bạn, hãy thêm một cột phụ nhân giá trị với trọng số, chẳng hạn như
=B2*C2. Đặt tên cho nó là Product. - Làm mới (Refresh) PivotTable để nó nhận diện cột mới.
- Chọn PivotTable, sau đó chọn PivotTable Analyze > Fields, Items & Sets > Calculated Field.
- Đặt tên cho trường, chẳng hạn như Weighted price, và nhập công thức
=Product/Units. - Thêm trường mới vào vùng Values.
Cách này hoạt động vì một trường được tính toán (calculated field) sẽ chia các tổng số, chứ không phải các hàng đơn lẻ. Đối với mỗi nhóm, nó chia tổng các tích cho tổng các trọng số. Đó chính xác là phép toán chính xác.
Định dạng trường mới với định dạng số bạn cần, chẳng hạn như hai chữ số thập phân. PivotTable giờ đây sẽ hiển thị con số chính xác cho mọi khu vực, sản phẩm hoặc tháng mà bạn kéo vào các hàng.
Trung bình trọng số với tỷ lệ phần trăm
Nhiều độc giả thắc mắc cách xử lý các trọng số được đưa ra dưới dạng tỷ lệ phần trăm. Có hai trường hợp xảy ra.
Các trọng số cộng lại bằng 100%. Bạn có thể sử dụng =SUMPRODUCT(B2:B4,C2:C4) mà không cần chia. Kết quả đã chính xác sẵn rồi. Tuy nhiên, việc chia cho SUM(C2:C4) không tốn gì cả và giúp bảo vệ bạn khỏi các lỗi làm tròn hoặc lỗi nhập liệu.
Các trọng số không cộng lại bằng 100%. Luôn luôn chia cho tổng các trọng số. Nếu không, kết quả sẽ quá cao hoặc quá thấp.
Định dạng các ô trọng số dưới dạng phần trăm. Chọn chúng, sau đó chọn Percentage từ danh sách định dạng số trên tab Home. Nhập 20 vào một ô được định dạng dưới dạng phần trăm, và Excel sẽ lưu trữ nó dưới dạng 0.2.
Một sai lầm phổ biến là trộn lẫn các định dạng phần trăm, chẳng hạn như 20% ở một hàng và 30 ở hàng tiếp theo. Excel khi đó sẽ coi trọng số thứ hai là 3,000%. Hãy giữ cho mọi trọng số có cùng một định dạng.
Tính trung bình trọng số trong Excel bằng AI
Các công thức trên hoạt động tốt cho một bảng. Khi dữ liệu của bạn trải dài trên nhiều hàng hoặc nhiều tệp, việc thiết lập các vùng chọn sẽ mất thời gian. Một công cụ AI có thể đọc tệp và thực hiện phép toán cho bạn.
Dưới đây là phương pháp gồm ba bước, sử dụng Powerdrill Bloom.
Bước 1: Tải lên tệp Excel của bạn
Tải lên bảng tính hoặc CSV chứa các giá trị và trọng số của bạn. Trang Tính toán Excel yêu cầu bạn đảm bảo dữ liệu của mình "được định dạng chính xác để cho phép tính toán diễn ra suôn sẻ."
Bạn có thể tải lên nhiều tệp cùng một lúc. Trang này cho biết bạn có thể tải lên "tối đa 10 tệp cùng một lúc vào một tập dữ liệu."
Bước 2: Yêu cầu tính trung bình trọng số bằng ngôn ngữ tự nhiên
Nhập phép tính bạn cần. Ví dụ: "Tính giá trung bình trên mỗi đơn vị theo khu vực, được tính trọng số theo số lượng đơn vị đã bán."
Trang này cho biết công cụ "sẽ nhận diện và áp dụng công thức chính xác." Hãy yêu cầu nó hiển thị công thức đã sử dụng, để bạn có thể xây dựng lại công thức đó trong trang tính của mình.
Bước 3: Kiểm tra kết quả và tái sử dụng công thức
So sánh kết quả với một bước kiểm tra thủ công nhanh chóng trên một nhóm. Sau đó, sao chép công thức vào bảng tính của bạn, hoặc xuất bảng tóm tắt.
Trang này cho biết công cụ sẽ "nhanh chóng tạo ra kết quả chính xác dựa trên thông tin nhập bằng văn bản của bạn." Hãy giữ công thức trong trang tính của bạn để kết quả tự động cập nhật khi dữ liệu thay đổi.
Các sai lầm phổ biến cần tránh
Đây là những vấn đề thường gặp nhất khi bạn tính trung bình trọng số trong Excel.
- Sử dụng AVERAGE. AVERAGE cung cấp cho mỗi giá trị một trọng số bằng nhau. Hãy sử dụng SUMPRODUCT và SUM để thay thế.
- Không chia cho các trọng số. Trừ khi các trọng số cộng lại bằng chính xác 100%, hãy chia cho tổng của chúng.
- Các dải ô không khớp nhau. SUMPRODUCT cần các dải ô có cùng kích thước, nếu không nó sẽ trả về lỗi #VALUE!.
- Trọng số trống. Một trọng số trống được tính là số không, điều này sẽ bỏ qua hàng đó mà không có cảnh báo.
- Trộn lẫn các định dạng phần trăm. Hãy giữ cho mọi trọng số ở dạng phần trăm hoặc mọi trọng số ở dạng số.
- Tham chiếu toàn bộ cột. Sử dụng các dải ô có giới hạn, chẳng hạn như B2:B500, để tăng tốc độ.
- Nhập trực tiếp điều kiện vào công thức. Hãy đặt điều kiện vào một ô để công thức dễ dàng được tái sử dụng.
Đối với các tính toán phổ biến khác, hãy xem các hướng dẫn của chúng tôi về cách tạo PivotTable trong Excel và cách thực hiện phân tích ABC trong Excel.
Khi các giá trị và trọng số của bạn nằm trong một tệp xuất dữ liệu lớn, hãy dùng thử Powerdrill Bloom và yêu cầu tính trung bình trọng số chỉ trong một câu.
Các câu hỏi thường gặp
Công thức tính trung bình trọng số trong Excel là gì?
Sử dụng =SUMPRODUCT(values,weights)/SUM(weights). Ví dụ, =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) nhân mỗi giá trị với trọng số của nó, cộng các kết quả lại và chia cho tổng trọng số.
Tôi có thể tính trung bình trọng số mà không cần SUMPRODUCT không?
Có. Hãy thêm một cột phụ nhân mỗi giá trị với trọng số của nó. Sau đó chia tổng của cột đó cho tổng các trọng số.
Làm thế nào để tính trung bình trọng số với tỷ lệ phần trăm trong Excel?
Nếu các tỷ lệ phần trăm cộng lại bằng 100%, hàm SUMPRODUCT của các giá trị và trọng số sẽ cho ra câu trả lời. Nếu không, hãy chia cho tổng các trọng số.
Làm thế nào để tính trung bình trọng số với các điều kiện?
Nhân một điều kiện bên trong SUMPRODUCT, chẳng hạn như (A2:A100="East"), với các giá trị và trọng số. Sau đó chia cho hàm SUMIF của các trọng số cho cùng điều kiện đó.
Sự khác biệt giữa trung bình thông thường và trung bình trọng số là gì?
Một điểm trung bình thông thường tính mọi giá trị như nhau. Một trung bình trọng số cung cấp cho mỗi giá trị một trọng số, vì vậy các giá trị quan trọng hơn sẽ có ảnh hưởng lớn hơn đến kết quả.
Nguồn: Hỗ trợ Microsoft, hàm SUMPRODUCT · Hỗ trợ Microsoft, hàm AVERAGE · Hỗ trợ Microsoft, hàm SUMIF.