Hướng dẫn này đưa ra hai cách để tính trung bình trọng số trong Excel – bằng cách sử dụng hàm SUM hoặc SUMPRODUCT.

Trung bình trọng số là gì?

Trung bình trọng số là một loại trung bình số học, trong đó một số phần tử của tập dữ liệu có tầm quan trọng hơn các yếu tố khác. Nói cách khác, mỗi giá trị được tính trung bình được gán một tỉ trọng nhất định.

Điểm của sinh viên thường được tính bằng cách sử dụng trung bình trọng số, như hình sau. Trung bình cộng thông thường được tính toán dễ dàng bằng hàm Excel AVERAGE. Tuy nhiên, ta cần công thức tính trung bình cho các hoạt động chiếm tỉ trọng khác nhau liệt kê trong cột C.

Trong toán học và thống kê, bạn tính toán trung bình trọng số bằng cách nhân mỗi giá trị với tỉ trọng của nó, sau đó cộng các kết quả rồi lấy tổng đó chia cho tổng tỉ trọng.

Trong ví dụ này, để tính trung bình trọng số (của tổng thể), bạn nhân mỗi loại với tỷ lệ phần trăm tương ứng (được chuyển thành số thập phân), cộng 5 kết quả lại, rồi chia cho tổng số phần trăm:

((91*0.1)+(65*0.15)+(80*0.2)+(73*0.25)+(68*0.3)) / (0.1+0.15+0.2+0.25+0.3)=73.5

Như bạn thấy, mức trung bình cộng (75,4) và trung bình trọng số (73,5) là các giá trị khác nhau.

Xem thêm: Hướng dẫn 3 cách tính trung bình trên excel 2017, 2010, 2013, 2016

Tính toán trung bình trọng số trong Excel

Trong Microsoft Excel, trung bình trọng số được tính bằng phương pháp tương tự nhưng ít phức tạp hơn bởi các hàm Excel sẽ thực hiện hầu hết công việc cho bạn.

Ví dụ 1. Tính trung bình trọng số bằng cách sử dụng hàm SUM

Nếu bạn có kiến ​​thức cơ bản về hàm SUM trong Excel, có thể dễ dàng hiểu công thức dưới đây:

= SUM (B2 * C2, B3 * C3, B4 * C4, B5 * C5, B6 * C6,) / SUM (C2: C6)

Về bản chất, nó thực hiện tính toán tương tự như mô tả ở trên, ngoại trừ việc bạn cung cấp các tham chiếu ô thay vì số.

Như bạn thấy trong hình, công thức trả về kết quả chính xác như kết quả tính ta đã làm trước đó. Chú ý sự khác biệt giữa trung bình cộng từ hàm AVERAGE (C8) và trung bình trọng số (C9).

Mặc dù hàm SUM rất đơn giản và dễ hiểu nhưng nó không phải là một lựa chọn khả thi nếu tính trung bình nhiều số. Trong trường hợp này, bạn nên sử dụng hàm SUMPRODUCT thể hiện trong ví dụ tiếp theo.

Ví dụ 2. Tìm trung bình trọng số với hàm SUMPRODUCT

Hàm SUMPRODUCT của Excel phù hợp hoàn hảo cho việc này vì nó được thiết kế để tổng sốsản phẩm, đó chính xác là những gì ta cần. Vì vậy, thay vì nhân mỗi giá trị theo trọng số riêng lẻ, bạn cung cấp hai mảng trong công thức SUMPRODUCT (trong bài này, mảng là một dãy các ô liên tiếp) và sau đó chia cho tổng trọng số:

=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

Giả sử các giá trị trung bình nằm trong các ô B2: B6 và trọng số trong các ô C2: C6, hàm SUMPRODUCT có dạng sau:

= SUMPRODUCT (B2: B6, C2: C6) / SUM (C2: C6)

Để xem các giá trị trong mảng, hãy chọn mảng chứa các giá trị đó trong thanh công thức và nhấn phím F9. Kết quả sẽ tương tự như sau:

Vậy những gì hàm SUMPRODUCT làm là nhân giá trị thứ nhất trong mảng 1 với giá trị thứ nhất trong mảng2 (91 * 0,1 trong ví dụ này), sau đó nhân giá trị thứ 2 trong mảng 1 bởi giá trị thứ 2 trong mảng 2 (65 * 0,15 trong ví dụ này) và cứ như vậy. Khi tất cả các phép nhân được thực hiện, hàm sẽ cộng lại và trả về kết quả đó.

Để đảm bảo rằng hàm SUMPRODUCT mang lại kết quả chính xác, hãy so sánh nó với hàm SUM từ ví dụ trước và bạn sẽ thấy rằng các con số giống nhau.

Khi sử dụng hàm SUM hoặc SUMPRODUCT để tìm trọng lượng trung bình trong Excel, tỉ trọng không nhất thiết là 100% hoặc thể thể hiện dưới dạng phần trăm. Ví dụ: bạn có thể tạo ra mức độ ưu tiên / tầm quan trọng và chỉ định một số nhất định cho mỗi mục, như hình sau:

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học