Bài học cách tính điểm trung bình có trọng số trong Excel qua hàm Sum và Sumproduct

Bài viết này hướng dẫn hai phương pháp để thực hiện phép tính trung bình có trọng số trong Excel – sử dụng hàm SUM hoặc SUMPRODUCT.

Khái niệm trung bình trọng số

Trung bình trọng số là một dạng trung bình số, ở đây các phần tử có vai trò quan trọng không đồng đều trong bộ dữ liệu. Cụ thể, mỗi giá trị xác định có một trọng số khác nhau.

Ví dụ, việc tính điểm sinh viên thường dùng đến trung bình trọng số, như hình dưới đây. Có thể tính trung bình cộng một cách đơn giản với hàm AVERAGE của Excel. Tuy nhiên, khi ta muốn tính trung bình dựa vào những tỷ trọng không giống nhau được liệt kê trong cột C, ta cần một công thức đặc biệt hơn.

Trong toán học và thống kê, trung bình trọng số được tìm ra bằng phép nhân mỗi giá trị với trọng số của nó, rồi cộng tất cả lại và chia cho tổng các trọng số đó.

Trong trường hợp cụ thể này, chúng ta tìm trung bình trọng số (toàn bộ) bằng cách nhân các loại điểm với tỷ lệ phần trăm tương ứng (được quy đổi thành dạng số thập phân), cộng tất cả 5 kết quả này lại và sau đó chia cho tổng tỷ lệ 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ư quý vị thấy, giá trị trung bình cộng thuần túy (75,4) và trung bình có trọng số (73,5) không giống nhau.

Xem thêm: Bài viết hướng dẫn 3 cách tính trung bình sử dụng Excel 2017, 2010, 2013, 2016

Phương pháp tính trung bình có trọng số trong Excel

Trong chương trình Microsoft Excel, việc tính trung bình có trọng số thực hiện tương tự nhưng đơn giản hơn do các hàm Excel tự động xử lý phần lớn công việc.

Ví dụ 1. Áp dụng hàm SUM để tính trung bình có trọng số

Nếu đã quen thuộc với hàm SUM trong Excel, công thức sau sẽ không quá phức tạp:

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

Bản chất của nó là thực hiện cách tính như đã giải thích ở trên, chỉ khác là bạn nhập các ô tham chiếu thay vì nhập số trực tiếp.

Nhìn vào hình, bạn có thể thấy công thức cho ra kết quả đúng như kết quả đã tính trước đó. Lưu ý sự biệt lập giữa trung bình cộng nhận từ hàm AVERAGE (C8) và trung bình có trọng số (C9).

Dù hàm SUM tiện dụng và dễ áp dụng, nhưng nó sẽ trở nên không hiệu quả nếu bạn cần tính trung bình cho một lượng lớn số liệu. Trong tình huống đó, bạn nên chọn hàm SUMPRODUCT, được mô tả trong ví dụ sau đây.

Ví dụ 2. Sử dụng hàm SUMPRODUCT để tính trung bình có trọng số

Hàm SUMPRODUCT trong Excel thật sự phù hợp cho công việc này, vì nó được tạo ra để tính tổng của các tích, chính xác là những gì chúng ta cần làm ở đây. Vậy nên, bạn cần chỉ cung cấp hai mảng trong công thức SUMPRODUCT (trong bài viết này, mảng chỉ một dãy các ô liền kề) rồi chia cho tổng trọng số:

=SUMPRODUCT(dãy_giá_trị, dãy_trọng_số) / SUM(dãy_trọng_số)

Giả định các điểm trung bình nằm ở dãy B2: B6 và trọng số nằm ở dãy C2: C6, hàm SUMPRODUCT có thể viết như thế này:

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

Nếu muốn kiểm tra giá trị trong mảng, hãy chọn dãy ô chứa giá trị đó trong thanh công thức và ấn phím F9. Kết quả sẽ xuất hiện như đã nêu sau đây:

“`html

Chức năng của hàm SUMPRODUCT bao gồm việc nhân vị trí đầu tiên của mảng thứ nhất với vị trí đầu tiên của mảng thứ hai (trong trường hợp này là 91 nhân với 0,1), tiếp theo là việc nhân giá trị thứ hai của mảng đầu với giá trị thứ hai của mảng hai (65 nhân với 0,15 theo ví dụ) và cứ tiếp tục như vậy. Sau khi hoàn tất quá trình nhân, hàm sẽ tổng hợp tất cả và trả về kết quả cuối cùng.

Kết quả của hàm SUMPRODUCT cần phải được đối chiếu với hàm SUM trong ví dụ trước để đảm bảo tính chính xác, và bạn sẽ nhận ra rằng hai kết quả này có sự tương đồng.

Khi dùng hàm SUM hoặc SUMPRODUCT để tính trọng số trung bình trong Excel, giá trị tỉ lệ không nhất thiết phải là 100% hay được biểu diễn dưới dạng phần trăm. Chẳng hạn, có thể sử dụng mức độ ưu tiên hoặc độ quan trọng và gán một giá trị cụ thể cho từng mục, như ví dụ dưới đây:

Nguồn: Ablebits, được dịch và chỉnh sửa bởi Siêu Marketing.

Vận dụng Excel thành thạo trong công việc không chỉ đòi hỏi việc hiểu rõ các hàm mà còn phải biết cách sử dụng hiệu quả các công cụ của Excel. Các hàm cao cấp như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… đều hỗ trợ rất tốt trong công việc cùng với các công cụ thường được sử dụng như Data validation, Conditional formatting, Pivot table…

Bạn hoàn toàn có thể nắm bắt toàn bộ những kiến thức trên thông qua khóa học.

“`

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop