Điều cần biết
- Cú pháp: =SUMPRODUCT (Mảng1, Mảng2, Mảng3,… Mảng255).
- Trong phép tính trung bình có trọng số, một số số trong phạm vi có giá trị cao hơn các số khác.
- Công thức trọng số SUMPRODUCT là một công thức không chuẩn phải được nhập vào một ô để hoạt động.
Bài viết này giải thích cách sử dụng hàm SUMPRODUCT để tính giá trị trung bình có trọng số trong các công thức Microsoft Excel.
Nhập Công thức Trọng số
Giống như hầu hết các hàm khác trong Excel, hàm SUMPRODUCT có thể được nhập vào trang tính bằng cách sử dụng Functions Librarytrong tab Formulas. Vì công thức trọng số trong ví dụ này sử dụng hàm SUMPRODUCT không theo cách chuẩn (kết quả của hàm được chia cho hệ số trọng số), nên công thức trọng số phải được nhập vào một ô trang tính.
Ví dụ hiển thị trong bài viết này tính điểm trung bình có trọng số cho điểm cuối cùng của học sinh bằng cách sử dụng hàm SUMPRODUCT.
Hàm thực hiện điều này bằng cách:
- Nhân các điểm khác nhau với hệ số trọng lượng riêng của chúng.
- Cộng các tích của các phép nhân này lại với nhau.
-
Chia tổng ở trên cho tổng của hệ số trọng số 7 (1 + 1 + 2 + 3) cho bốn lần đánh giá.
Để nhập công thức SUMPRODUCT để tính giá trị trung bình có trọng số, hãy mở một trang tính trống, nhập dữ liệu vào các hàng từ 1 đến 6từ hình trên và làm theo các bước sau:
- Chọn ô C7để làm ô hoạt động (đây là vị trí hiển thị điểm cuối cùng của học sinh).
- Nhập công thức =SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)vào ô. Công thức xuất hiện trong Thanh Công thức.
- Nhấn phím Nhậptrên bàn phím.
- Câu trả lời 78,6xuất hiện trong ô C7(câu trả lời của bạn có thể có nhiều chữ số thập phân hơn).
Điểm trung bình không trọng số cho bốn điểm giống nhau sẽ là 76,5. Vì học sinh có kết quả tốt hơn cho các bài kiểm tra giữa kỳ và cuối kỳ nên việc cân nhắc điểm trung bình đã giúp cải thiện điểm tổng thể.
Cách hoạt động của hàm SUMPRODUCT
Thông thường, khi bạn tính giá trị trung bình hoặc trung bình cộng, mỗi số có giá trị hoặc trọng số bằng nhau. Giá trị trung bình được tính bằng cách cộng một dãy số với nhau rồi chia tổng số này cho số giá trị trong dãy. Mặt khác, trung bình có trọng số coi một hoặc nhiều số trong phạm vi có giá trị lớn hơn hoặc có trọng số lớn hơn các số khác.
SUMPRODUCT nhân các phần tử của hai hoặc nhiều mảng rồi cộng các tích để tính giá trị trung bình có trọng số. Ví dụ, một số điểm nhất định ở trường, chẳng hạn như bài kiểm tra giữa kỳ và cuối kỳ, thường có giá trị hơn các bài kiểm tra hoặc bài tập thông thường. Nếu lấy điểm trung bình để tính điểm cuối cùng của học sinh, thì bài kiểm tra giữa kỳ và cuối kỳ sẽ có trọng số lớn hơn.
Trong trường hợp hai mảng có bốn phần tử, mỗi mảng được nhập làm đối số cho hàm SUMPRODUCT:
- Phần tử đầu tiên của mảng1 được nhân với phần tử đầu tiên trong mảng2.
- Phần tử thứ hai của mảng1 được nhân với phần tử thứ hai của mảng2.
- Phần tử thứ ba của mảng1 được nhân với phần tử thứ ba của mảng2.
- Phần tử thứ tư của mảng1 được nhân với phần tử thứ tư của mảng2.
Tiếp theo, các tích của bốn phép nhân được tính tổng và trả về bởi hàm dưới dạng kết quả.
SUMPRODUCT Cú pháp và đối số
Cú pháp của hàm đề cập đến bố cục của hàm và bao gồm tên, dấu ngoặc và đối số của hàm. Cú pháp cho hàm SUMPRODUCT là:
=SUMPRODUCT (Array1,Array2,Array3,… Mảng255)
Các đối số cho hàm SUMPRODUCT là:
- Array1:Đối số mảng đầu tiên (bắt buộc).
- Array2,Array3,… Array255: Mảng bổ sung (tùy chọn), tối đa 255. Với hai hoặc nhiều mảng, hàm nhân các phần tử của mỗi mảng với nhau rồi cộng kết quả.
Các phần tử mảng có thể là tham chiếu ô đến vị trí của dữ liệu trong trang tính hoặc các số được phân tách bằng toán tử số học, chẳng hạn như dấu cộng (+) hoặc dấu trừ (-). Nếu bạn nhập các số không được phân tách bằng toán tử, Excel sẽ coi chúng là dữ liệu văn bản.
Các đối số của mảng phải có cùng số phần tử trong mỗi mảng. Nếu không, hàm SUMPRODUCT trả về giá trị lỗi VALUE! giá trị lỗi. Nếu bất kỳ phần tử nào của mảng không phải là số, chẳng hạn như dữ liệu văn bản, SUMPRODUCT sẽ coi chúng là số không.
Biến thể công thức SUMPRODUCT
Để nhấn mạnh rằng kết quả của hàm SUMPRODUCT được chia cho tổng trọng số của mỗi nhóm đánh giá, số chia (phần thực hiện phép chia) được nhập là:
(1 + 1 + 2 + 3)
Có thể đơn giản hóa công thức tính trọng số tổng thể bằng cách nhập số 7(tổng các trọng số) làm số chia. Công thức sau đó sẽ là:
=SUMPRODUCT (B3: B6, C3: C6) / 7
Lựa chọn này là tốt nếu số lượng phần tử trong mảng trọng số nhỏ và chúng có thể dễ dàng được thêm vào với nhau, nhưng nó trở nên kém hiệu quả hơn khi số lượng phần tử trong mảng trọng số tăng lên, khiến việc cộng chúng trở nên khó khăn hơn.
Một tùy chọn khác, và có lẽ là lựa chọn tốt nhất, cho rằng nó sử dụng tham chiếu ô thay vì số để tính tổng số chia, sẽ là sử dụng hàm SUM để tính tổng số chia. Công thức sau đó là:
=SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Tốt nhất là nhập tham chiếu ô chứ không phải số thực vào công thức. Điều này giúp đơn giản hóa việc cập nhật chúng nếu dữ liệu của công thức thay đổi.
Ví dụ: nếu các hệ số trọng số cho Bài tập được thay đổi thành 0,5 trong ví dụ và Bài kiểm tra được thay đổi thành 1,5, hai dạng đầu tiên của công thức cần được chỉnh sửa theo cách thủ công để sửa số chia.
Trong biến thể thứ ba, chỉ dữ liệu trong ô B3 và B4 cần được cập nhật và công thức sẽ tính toán lại kết quả.
