01 trên 01
Hàm SUMPRODUCT của Excel
Tổng quan trung bình theo trọng số và không có trọng số
Thông thường, khi tính trung bình hoặc số học trung bình, mỗi số có giá trị hoặc trọng số bằng nhau.
Mức trung bình được tính bằng cách thêm một dãy số với nhau và sau đó chia tổng số này cho số giá trị trong phạm vi .
Một ví dụ sẽ là (2 + 3 + 4 + 5 + 6) / 5 cho một trung bình không có trọng số là 4.
Trong Excel, các phép tính như vậy được thực hiện dễ dàng bằng chức năng AVERAGE .
Mặt khác, trung bình có trọng số xem xét một hoặc nhiều số trong phạm vi có giá trị hơn hoặc có trọng số lớn hơn các số khác.
Ví dụ, một số điểm nhất định trong trường, chẳng hạn như kỳ thi giữa kỳ và cuối kỳ, thường có giá trị nhiều hơn các bài kiểm tra hoặc bài tập thông thường.
Nếu tính trung bình được sử dụng để tính điểm cuối cùng của học sinh, kỳ thi giữa kì và cuối kỳ sẽ được cho là có trọng số lớn hơn.
Trong Excel, các mức trung bình có trọng số có thể được tính bằng hàm SUMPRODUCT .
Hàm SUMPRODUCT hoạt động như thế nào
Những gì SUMPRODUCT làm là nhân các phần tử của hai hoặc nhiều mảng và sau đó thêm hoặc tổng hợp các sản phẩm.
Ví dụ, trong một tình huống mà hai mảng có bốn phần tử đượ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 sản phẩm của bốn phép toán nhân được tổng hợp và trả về bởi hàm này như là kết quả.
Cú pháp hàm và đối số của hàm SUMPRODUCT trong Excel
Cú pháp của hàm liên quan đến cách bố trí của hàm và bao gồm tên, khung và đối số của hàm.
Cú pháp cho hàm SUMPRODUCT là:
= SUMPRODUCT (mảng1, mảng2, mảng3, ... mảng255)
Các đối số cho hàm SUMPRODUCT là:
array1: (bắt buộc) đối số mảng đầu tiên.
mảng2, array3, ... array255: (tùy chọn) mảng bổ sung, 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 và sau đó thêm 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 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 các số được nhập mà không bị phân tách bởi các toán tử, Excel coi chúng là dữ liệu văn bản. Tình huống này được trình bày trong ví dụ dưới đây.
Ghi chú :
Tất cả các đối số mảng phải có cùng kích thước. Hay nói cách khác, 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ử mảng nào không phải là số - chẳng hạn như dữ liệu văn bản - SUMPRODUCT coi chúng là số không.
Ví dụ: Tính toán mức trung bình có trọng số trong Excel
Ví dụ được hiển thị trong hình ảnh trên tính toán mức trung bình 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 dấu khác nhau theo hệ số trọng lượng riêng của chúng;
- thêm các sản phẩm của các hoạt động nhân này lại với nhau;
- chia tổng số trên bằng tổng hệ số trọng số 7 (1 + 1 + 2 + 3) cho bốn đánh giá.
Nhập công thức tính trọng số
Giống như hầu hết các hàm khác trong Excel, SUMPRODUCT thường được nhập vào một trang tính bằng cách sử dụng hộp thoại của hàm. Tuy nhiên, vì công thức trọng số sử dụng SUMPRODUCT theo cách không chuẩn - kết quả của hàm được chia cho hệ số trọng số - công thức trọng số phải được nhập vào ô bảng tính .
Các bước sau đã được sử dụng để nhập công thức trọng số vào ô C7:
- Bấm vào ô C7 để biến ô hiện hoạt - vị trí nơi dấu cuối cùng của học sinh sẽ được hiển thị
- Nhập công thức sau vào ô:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Nhấn phím Enter trên bàn phím
- Câu trả lời 78.6 sẽ xuấ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
Trung bình không cân đối cho cùng bốn điểm sẽ là 76,5
Kể từ khi học sinh có kết quả tốt hơn cho kỳ thi giữa kỳ và cuối kỳ của mình, trọng số trung bình đã giúp cải thiện điểm số tổng thể của mình.
Biến thể công thức
Để nhấn mạnh rằng kết quả của hàm SUMPRODUCT được chia cho tổng trọng số cho mỗi nhóm đánh giá, số chia - phần thực hiện việc chia - được nhập là (1 + 1 + 2 + 3).
Công thức trọng số tổng thể có thể được đơn giản hóa bằng cách nhập số 7 (tổng của 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ố 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 vì số phần tử trong mảng trọng số tăng làm cho việc thêm của 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 - vì nó sử dụng tham chiếu ô chứ không phải số trong tổng số ước - sẽ sử dụng hàm SUM để tính tổng số chia với công thức là:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Nó thường là tốt nhất để nhập tham chiếu ô chứ không phải là số thực tế vào công thức vì nó đơ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 yếu tố trọng số cho Bài tập được thay đổi thành 0,5 trong ví dụ và cho Bài kiểm tra thành 1,5, thì hai dạng đầu tiên của công thức sẽ phải đượ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ả.