Excel đã làm cho việc tính giá trị trung bình của nhiều ô trở thành một nhiệm vụ rất dễ dàng - chỉ cần sử dụng hàm TRUNG BÌNH CỘNG (TRUNG BÌNH). Nhưng điều gì sẽ xảy ra nếu một số giá trị có trọng lượng lớn hơn những giá trị khác? Ví dụ, trong nhiều khóa học, các bài kiểm tra có trọng lượng hơn các bài tập. Đối với những trường hợp như vậy, cần phải tính bình quân gia quyền.
Excel không có hàm để tính giá trị trung bình có trọng số, nhưng có một hàm sẽ thực hiện hầu hết công việc cho bạn: SUMPRODUCT (SẢN PHẨM SUM). Và ngay cả khi bạn chưa từng sử dụng tính năng này trước đây, thì đến cuối bài viết này, bạn sẽ sử dụng nó như một người chuyên nghiệp. Phương pháp chúng tôi sử dụng hoạt động trong bất kỳ phiên bản nào của Excel cũng như các bảng tính khác, chẳng hạn như Google Trang tính.
Chúng tôi chuẩn bị bàn
Nếu bạn đị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 lượng nhiều hơn có nghĩa là ảnh hưởng của nhiệm vụ hoặc bài kiểm tra lên lớp cuối cấp nhiều hơn.
Để hiểu trọng lượng là gì, bạn có thể coi nó như một tỷ lệ phần trăm của điểm cuối cùng của bạn. Trong thực tế, đây không phải là trường hợp, vì trong trường hợp này, trọng số phải cộng lại 100%. Công thức mà chúng ta sẽ phân tích trong bài học này sẽ tính toán mọi thứ một cách chính xác và không phụ thuộc vào khối lượng mà các trọng số cộng lại.
Chúng tôi nhập công thức
Bây giờ bảng của chúng tôi đã sẵn sàng, chúng tôi thêm công thức vào ô B10 (bất kỳ ô trống nào sẽ làm được). Như với bất kỳ công thức nào khác trong Excel, chúng ta bắt đầu bằng dấu bằng (=).
Phần đầu tiên của công thức của chúng tôi là hàm SUMPRODUCT (SẢN PHẨM SUM). Các đối số phải được đặt trong dấu ngoặc, vì vậy chúng tôi mở chúng:
=СУММПРОИЗВ(
=SUMPRODUCT(
Tiếp theo, thêm các đối số của hàm. SUMPRODUCT (SUMPRODUCT) có thể có nhiều đối số, nhưng thường có hai đối số được sử dụng. Trong ví dụ của chúng tôi, đối số đầu tiên sẽ là một dải ô. B2:B9A chứa điểm số.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Đối số thứ hai sẽ là một dải ô C2:C9, chứa các trọng số. Các đối số này phải được phân tách bằng dấu chấm phẩy (dấu phẩy). Khi mọi thứ đã sẵn sàng, hãy đóng dấu ngoặc:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Bây giờ, hãy thêm phần thứ hai của công thức của chúng tôi, phần này sẽ chia kết quả được tính bởi hàm SUMPRODUCT (SUMPRODUCT) bằng tổng các trọng số. Chúng ta sẽ thảo luận sau tại sao điều này lại quan trọng.
Để thực hiện phép chia, chúng ta tiếp tục công thức đã nhập với ký hiệu / (gạch chéo thẳng), và sau đó viết hàm TÓM TẮT (TỔNG):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Đối với chức năng TÓM TẮT (SUM) chúng tôi sẽ chỉ định một đối số - một phạm vi ô C2:C9. Đừng quên đóng dấu ngoặc đơn sau khi nhập đối số:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Sẳn sàng! Sau khi nhấn phím đăng ký hạng mục thi, Excel sẽ tính giá trị trung bình có trọng số. Trong ví dụ của chúng tôi, kết quả cuối cùng sẽ là 83,6.
Cách thức thực hiện
Hãy chia nhỏ từng phần của công thức, bắt đầu bằng hàm SUMPRODUCT (SUMPRODUCT) để hiểu cách nó hoạt động. Hàm số SUMPRODUCT (SUMPRODUCT) tính tích điểm của từng mặt hàng và trọng lượng của nó, sau đó tính tổng tất cả các sản phẩm kết quả. Nói cách khác, hàm tìm tổng của các sản phẩm, do đó có tên. Vì vậy đối với Bài tập 1 nhân 85 với 5 và cho Các bài kiểm tra nhân 83 với 25.
Nếu bạn đang tự hỏi tại sao chúng ta cần nhân các giá trị trong phần đầu tiên, hãy tưởng tượng rằng trọng lượng của nhiệm vụ càng lớn, thì số lần chúng ta phải xét điểm cho nó càng nhiều. Ví dụ, Tác vụ 2 đếm 5 lần và Kỳ thi cuối kỳ - 45 lần. Đó là lý do tại sao Kỳ thi cuối kỳ có tác động lớn hơn đến lớp cuối cấp.
Để so sánh, khi tính giá trị trung bình cộng thông thường, mỗi giá trị chỉ được tính đến một lần, tức là tất cả các giá trị đều có trọng số bằng nhau.
Nếu bạn có thể tìm hiểu về một hàm SUMPRODUCT (SUMPRODUCT), chúng tôi thấy rằng trên thực tế cô ấy tin điều này:
=(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 cần phải viết một công thức dài như vậy vì SUMPRODUCT (SUMPRODUCT) thực hiện tất cả điều này một cách tự động.
Một chức năng tự nó SUMPRODUCT (SUMPRODUCT) trả về cho chúng ta một con số khổng lồ - 10450. Tại thời điểm này, phần thứ hai của công thức phát huy tác dụng: / SUM (C2: C9) or / SUM (C2: C9), trả kết quả về phạm vi điểm bình thường, đưa ra câu trả lời 83,6.
Phần thứ hai của công thức rất quan trọng vì cho phép bạn tự động sửa các phép tính. Hãy nhớ rằng trọng lượng không nhất thiết phải cộng 100%? Tất cả điều này là nhờ vào phần thứ hai của công thức. Ví dụ: nếu chúng ta tăng một hoặc nhiều giá trị trọng số, phần thứ hai của công thức sẽ chỉ đơn giản là chia cho giá trị lớn hơn, một lần nữa dẫn đến câu trả lời đúng. Hoặc chúng ta có thể làm cho trọng số nhỏ hơn nhiều, chẳng hạn như bằng cách chỉ định các giá trị như 0,5, 2,5, 3 or 4,5, và công thức sẽ vẫn hoạt động chính xác. Thật tuyệt vời đúng không?