Bình quân gia quyền

= SUMPRODUCT (weights,values)/ SUM (weights)

Để tính giá trị trung bình có trọng số, bạn có thể sử dụng GIỚI THIỆU hoạt động cùng với Hàm SUM . Trong ví dụ được hiển thị, công thức trong G5, được sao chép xuống, là:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

ở đâu trọng lượng là phạm vi được đặt tên I5: K5.

Trung bình có trọng số, còn được gọi là giá trị trung bình có trọng số, là giá trị trung bình trong đó một số giá trị được tính nhiều hơn các giá trị khác. Nói cách khác, một số giá trị có nhiều 'trọng lượng' hơn. Chúng ta có thể tính giá trị trung bình có trọng số bằng cách nhân các giá trị của giá trị trung bình với các trọng số tương ứng, sau đó chia tổng kết quả cho tổng trọng số. Trong Excel, điều này có thể được biểu diễn bằng công thức chung bên dưới, trong đó trọng số và giá trị là phạm vi ô:

cách tạo đồ thị hình tròn trong excel

 
= SUMPRODUCT (weights,values)/ SUM (weights)

Trong trang tính được hiển thị, điểm cho 3 bài kiểm tra xuất hiện trong các cột từ C đến E và trọng số nằm trong phạm vi được đặt tên trọng lượng (I5: K5). Công thức trong ô G5 là:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

Làm việc từ trong ra ngoài, trước tiên, chúng tôi sử dụng hàm SUMPRODUCT để nhân trọng số với điểm số tương ứng và tính tổng kết quả:

 
= SUMPRODUCT (weights,C5:E5) // returns 88.25

Trước tiên SUMPRODUCT nhân các phần tử tương ứng của hai mảng với nhau, sau đó trả về tổng của tích:

 
= SUMPRODUCT ({0.25,0.25,0.5},{90,83,90}) = SUMPRODUCT ({22.5,20.75,45}) =88.25

Kết quả sau đó được chia cho tổng các trọng số:

 
=88.25/ SUM (weights) =88.25/ SUM ({0.25,0.25,0.5}) =88.25/1 =88.25

Khi công thức được sao chép xuống cột G, phạm vi được đặt tên trọng lượng I5: K5 không thay đổi, vì nó hoạt động như một tham chiếu tuyệt đối . Tuy nhiên, điểm số trong C5: E5, được nhập dưới dạng tài liệu tham khảo tương đối , cập nhật trong mỗi hàng mới. Kết quả là trung bình có trọng số cho mỗi tên trong danh sách như hình minh họa. Giá trị trung bình trong cột F chỉ được tính để tham khảo với Hàm AVERAGE :

 
= AVERAGE (C5:E5)

Các trọng số không tổng thành 1

Trong ví dụ này, các trọng số được định cấu hình để cộng tới 1, vì vậy ước số luôn là 1 và kết quả là giá trị được trả về bởi SUMPRODUCT. Tuy nhiên, một tính năng tuyệt vời của công thức là các trọng số không cần cộng đến 1.

Ví dụ: chúng ta có thể sử dụng trọng số 1 cho hai bài kiểm tra đầu tiên và trọng số 2 cho bài kiểm tra cuối cùng (vì bài kiểm tra cuối cùng quan trọng gấp đôi) và trung bình có trọng số sẽ giống nhau:

nhỏ hơn và lớn hơn excel

Trong ô G5, công thức được giải như sau:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights) = SUMPRODUCT ({1,1,2},{90,83,90})/ SUM (1,1,2) = SUMPRODUCT ({90,83,180})/ SUM (1,1,2) =353/4 =88.25

Lưu ý: các giá trị trong dấu ngoặc nhọn {} ở trên là các phạm vi được biểu thị bằng mảng .

Chuyển trọng lượng

Hàm SUMPRODUCT yêu cầu kích thước mảng phải tương thích. Nếu thứ nguyên không tương thích, SUMPRODUCT sẽ trả về lỗi #VALUE. Trong ví dụ bên dưới, các trọng số giống như ví dụ ban đầu, nhưng chúng được liệt kê trong thẳng đứng phạm vi:

Để tính toán trung bình có trọng số với cùng một công thức, chúng ta cần 'lật' các trọng số thành một mảng ngang với Hàm TRANSPOSE như thế này:

 
= SUMPRODUCT ( TRANSPOSE (weights),C5:E5)/ SUM (weights)

Sau khi TRANSPOSE chạy, mảng dọc:

 
= TRANSPOSE ({0.250.250.5}) // vertical array

trở thành:

 
={0.25,0.25,0.5} // horizontal array

Và từ thời điểm này, công thức hoạt động như trước.

Đọc thêm: mảng dọc và ngang .

exceljet có trọng số trung bình.xlsx