Excel giúp bạn dễ dàng tính giá trị trung bình của một số ô: Chỉ cần sử dụng hàm AVERAGE. Nhưng điều gì sẽ xảy ra nếu một số giá trị có "trọng lượng" hơn những giá trị khác? Ví dụ, ở nhiều lớp, các bài kiểm tra có giá trị hơn các bài tập. Đối với những tình huống này, bạn sẽ cần tính giá trị trung bình có trọng số.
Mặc dù Excel không có hàm trung bình có trọng số, nhưng nó có một hàm thực hiện hầu hết công việc cho bạn: SUMPRODUCT. Ngay cả khi bạn chưa từng sử dụng SUMPRODUCT trước đây, bạn sẽ có thể sử dụng nó như một người chuyên nghiệp vào cuối bài viết này. Phương pháp chúng mình đang sử dụng hoạt động với tất cả các phiên bản Excel và nó cũng hoạt động với các ứng dụng bảng tính khác như Google Trang tính.
Thiết lập bảng tính
Để tính giá trị trung bình có trọng số, bạn sẽ cần ít nhất hai cột. Cột đầu tiên (cột B trong ví dụ của chúng tôi) chứa điểm cho mỗi bài tập hoặc bài kiểm tra. Cột thứ hai (cột C) chứa các trọng số. Trọng số cao hơn sẽ khiến bài tập hoặc bài kiểm tra có ảnh hưởng lớn hơn đến điểm tổng kết.
Bạn có thể coi trọng số là tỷ lệ phần trăm của điểm kết thúc cùng. Nhưng trong trường hợp này, trọng số thực sự cộng lại hơn 100%. Điều đó không sao cả vì công thức của chúng tôi sẽ vẫn hoạt động cho dù trọng số cộng lại là bao nhiêu.
Nhập công thức
Bây giờ chúng tôi đã thiết lập bảng tính của mình, chúng tôi sẽ thêm công thức vào ô B10 (bất kỳ ô trống nào sẽ hoạt động). Như với bất kỳ công thức nào, hãy bắt đầu bằng cách nhập dấu bằng (=).
Phần đầu tiên của công thức của chúng ta sẽ là hàm SUMPRODUCT. Bởi vì các đối số sẽ nằm trong dấu ngoặc đơn, hãy tiếp tục và nhập một dấu ngoặc đơn mở:
=SUMPRODUCT(
Tiếp theo, chúng ta sẽ thêm các đối số vào hàm. SUMPRODUCT có thể có bất kỳ số lượng đối số nào, nhưng nó thường sẽ có hai. Trong ví dụ của chúng tôi, đối số đầu tiên sẽ là phạm vi ô B2: B9 các ô chứa điểm của chúng tôi:
=SUMPRODUCT(B2:B9
Đối số thứ hai sẽ là phạm vi ô C2: C9 các ô chứa trọng số. Bạn sẽ cần sử dụng dấu phẩy để phân tách hai đối số này. Khi bạn hoàn tất, hãy nhập một dấu ngoặc đơn đóng:
=SUMPRODUCT(B2:B9, C2:C9)
Bây giờ chúng ta sẽ thêm phần thứ hai của công thức. Phần này sẽ chia SUMPRODUCT cho SUM của các trọng số. Sau đó, chúng ta sẽ nói về lý do tại sao điều này lại quan trọng.
Bắt đầu bằng cách nhập a / (dấu gạch chéo lên) cho phép chia, sau đó nhập hàm SUM:
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Chúng ta chỉ cần một đối số cho hàm SUM: phạm vi ô C2: C9. Hãy nhớ đóng dấu ngoặc đơn sau đối số:
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Đó là nó! Khi bạn nhấn Enter trên bàn phím, Excel sẽ tính giá trị trung bình có trọng số. Trong ví dụ của chúng tôi, điểm cuối cùng là 83,6.
Nó hoạt động như thế nào
Hãy xem từng phần của công thức này để xem nó hoạt động như thế nào, bắt đầu với hàm SUMPRODUCT. SUMPRODUCT là nhân (tìm tích của) điểm của mỗi bài tập với trọng lượng của nó, sau đó cộng tất cả các tích lại với nhau. Nói cách khác, nó tìm thấy tổng của các sản phẩm, đó là nơi nó có tên. Vì vậy, đối với Bài tập 1, nó nhân 85 với 5 và đối với Bài kiểm tra, nó nhân 83 với 25.
Nếu bạn đang tự hỏi tại sao các giá trị cần được nhân lên ngay từ đầu, hãy nghĩ theo cách này: Bài tập có trọng số cao hơn được tính nhiều lần hơn. Ví dụ: Bài tập 2 được tính 5 lần, nhưng Bài kiểm tra cuối cùng được tính 45 lần. Đây là lý do tại sao Kỳ thi cuối kỳ có ảnh hưởng lớn hơn đến điểm cuối cấp. Để so sánh, điểm trung bình "thông thường" sẽ tính mỗi bài tập một lần, vì vậy mỗi bài tập có cùng trọng số
Nếu bạn có thể nhìn vào "bên dưới" của hàm SUMPRODUCT, bạn sẽ thấy rằng đây là những gì nó thực sự đang tính toán:
= (B2 * C2) + (B3 * C3) + (B4 * C4) + (B5 * C5) + (B6 * C6) + (B7 * C7) + (B8 * C8) + (B9 * C9)
May mắn thay, chúng ta không phải viết một công thức dài như thế này vì SUMPRODUCT đang tự động làm tất cả.
Tự nó, SUMPRODUCT sẽ cho chúng ta một con số khổng lồ: 10.450. Đây là nơi mà phần thứ hai của công thức của chúng tôi xuất hiện: / SUM (C2: C9). Phần này đưa giá trị trở lại mức bình thường, tạo ra câu trả lời là 83,6.
Phần thứ hai của công thức thực sự hữu ích vì nó cho phép công thức tự động sửa các phép tính của nó. Hãy nhớ rằng trọng số không cần phải cộng đến 100%? Điều này là do phần này của công thức sẽ giải quyết điều đó cho chúng ta. Ví dụ: nếu chúng ta tăng một hoặc nhiều trọng số, phần thứ hai của công thức chỉ cần chia cho một số lớn hơn, đưa nó trở lại câu trả lời đúng. Chúng tôi thậm chí có thể làm cho trọng số nhỏ hơn nhiều, cung cấp cho chúng các giá trị như 0,5, 2,5, 3,0 và 4,5 và công thức sẽ vẫn hoạt động hoàn hảo.
