Excel SUMPRODUCT chức năng
Trong Excel, hàm SUMPRODUCT có thể được sử dụng để nhân hai hoặc nhiều cột hoặc mảng với nhau, sau đó lấy tổng của các sản phẩm. Trên thực tế, hàm SUMPRODUCT là một hàm hữu ích có thể giúp đếm hoặc tính tổng các giá trị ô với nhiều tiêu chí như hàm COUNTIFS hoặc SUMIFS. Bài viết này sẽ giới thiệu cú pháp hàm và một số ví dụ cho hàm SUMPRODUCT này.
- Ví dụ 1: Sử dụng cơ bản hàm SUMPRODUCT
- Ví dụ 2: Tính tổng các ô có nhiều tiêu chí với hàm SUMPRODUCT
- Ví dụ 3: Đếm các ô có nhiều tiêu chí bằng hàm SUMPRODUCT
- Ví dụ 4: Tính giá trị trung bình có trọng số với hàm SUMPRODUCT
Cú pháp:
Cú pháp cho hàm SUMPRODUCT trong Excel là:
đối số:
- array1: Cần thiết. Mảng hoặc dải ô đầu tiên bạn muốn nhân và sau đó thêm.
- array2: Không bắt buộc. Mảng hoặc dải ô thứ hai bạn muốn nhân và sau đó thêm.
Ghi chú:
- 1. Nếu có các giá trị không phải số trong phạm vi của bạn, hàm SUMPRODUCT sẽ coi chúng là số không.
- 2. Nếu các mảng hoặc phạm vi có số lượng hàng và cột khác nhau, hàm SUMPRODUCT sẽ trả về lỗi #VALUE! lỗi.
- 3. Nếu có các kiểm tra logic trong mảng, chúng sẽ tạo ra các giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn cần chuyển đổi chúng thành 1 và 0 bằng cách sử dụng toán tử đơn phân kép (-).
- 4. Hàm SUMPRODUCT có thể xử lý tối đa 255 đối số trong Excel 2007 và các phiên bản mới hơn, và chỉ 30 đối số trong các phiên bản Excel trước.
- 5. SUMPRODUCT không hỗ trợ các ký tự đại diện.
Trở về:
Trả về kết quả của mảng nhân và mảng tổng.
Ví dụ:
Ví dụ 1: Sử dụng cơ bản hàm SUMPRODUCT
Cách sử dụng cơ bản của SUMPRODUCT là nhân hai cột và sau đó cộng chúng lại, ví dụ: bạn có dữ liệu ảnh chụp màn hình sau, bây giờ bạn muốn nhân đơn đặt hàng và đơn giá, sau đó tổng hợp tất cả các kết quả nhân với nhau để có được Tổng giá.
Để giải quyết công việc này, vui lòng áp dụng công thức dưới đây:
Giải thích: Công thức này SUMPRODUCT(B2:B7,C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.
Và sau đó, nhấn đăng ký hạng mục thi khóa, các ô trong cột B được nhân với các ô tương ứng của nó trong cùng một hàng của cột C và kết quả được cộng lại. Xem ảnh chụp màn hình:
Ví dụ 2: Tính tổng các ô có nhiều tiêu chí với hàm SUMPRODUCT
Ngoại trừ hàm SUMIFS để tính tổng các giá trị ô dựa trên nhiều điều kiện, hàm SUMPRODUCT cũng có thể giải quyết công việc này một cách nhanh chóng và dễ dàng.
Trường hợp 1: Tính tổng các giá trị ô nếu chứa văn bản cụ thể trong một cột khác
Giả sử, bạn có một bảng dữ liệu như hình minh họa bên dưới, bây giờ, bạn muốn tính tổng giá của sản phẩm là KTE và tên là David, bạn có thể làm thế nào bằng cách sử dụng công thức SUMPRODUCT trong Excel?
1. Vui lòng nhập hoặc sao chép bất kỳ công thức nào dưới đây vào ô trống:
- Lời khuyên: Trong công thức trên:
- A2: A12 = "KTE": là tiêu chí đầu tiên và tiêu chí bạn muốn tính tổng dựa trên.
- B2: B12 = "David": là dải tiêu chí thứ hai và tiêu chí bạn muốn tính tổng dựa trên.
- D2: D12: là phạm vi tổng mà bạn muốn tính tổng các giá trị ô.
2. Sau đó, nhấn đăng ký hạng mục thi chìa khóa để nhận được kết quả như bạn cần:
Trường hợp 2: Tính tổng các ô có nhiều tiêu chí với logic OR
Để tính tổng các ô dựa trên nhiều tiêu chí với logic HOẶC, bạn nên sử dụng biểu tượng dấu cộng (+) để kết hợp nhiều điều kiện với nhau trong hàm SUMPRODUCT.
Ví dụ: tôi muốn tính tổng giá của sản phẩm KTE và KTO trong cột A như ảnh chụp màn hình dưới đây:
Vui lòng áp dụng công thức sau vào một ô trống:
Và sau đó, nhấn đăng ký hạng mục thi chính, tổng giá của sản phẩm KTE và KTO đã được tính toán, xem ảnh chụp màn hình:
Trường hợp 3: Tính tổng các ô có nhiều tiêu chí với logic OR và AND
Trong một số tình huống, bạn có thể cần tính tổng các ô có cả logic OR và AND cùng một lúc. Hàm SUMPRODUCE cũng có thể giải quyết công việc này một cách dễ dàng.
Dấu hoa thị (*) được sử dụng làm toán tử AND.
Biểu tượng dấu cộng (+) được sử dụng làm toán tử OR.
Hãy xem một ví dụ, để tính tổng giá sản phẩm là KTE và KTO khi doanh số bán hàng lớn hơn 200.
Bạn nên sao chép hoặc nhập công thức dưới đây:
Sau đó nhấn đăng ký hạng mục thi chìa khóa để nhận được kết quả như bạn cần:
Ví dụ 3: Đếm các ô có nhiều tiêu chí bằng hàm SUMPRODUCT
Nói chung, hàm SUMPRODUCT này cũng có thể giúp chúng tôi đếm các ô có nhiều tiêu chí trong Excel. Tương tự như các giá trị sum, bạn chỉ cần xóa đối số sum_range khỏi công thức.
Trường hợp 1: Đếm các ô có nhiều tiêu chí với logic AND
Ở đây, tôi muốn đếm các ô mà sản phẩm là KTE và doanh số bán hàng lớn hơn 200. Vì vậy, vui lòng sử dụng bất kỳ công thức nào dưới đây:
- Lời khuyên: Trong công thức trên:
- A2: A12 = "KTE": là tiêu chí đầu tiên và tiêu chí mà bạn muốn đếm dựa trên.
- B2: B12> 200: là tiêu chí thứ hai và tiêu chí bạn muốn đếm dựa trên.
Và sau đó, nhấn đăng ký hạng mục thi phím để nhận số kết quả:
Trường hợp 2: Đếm các ô có nhiều tiêu chí với logic OR
Để đếm số lượng sản phẩm KTE và KTO trong cột A, vui lòng áp dụng công thức sau:
Và sau đó, nhấn đăng ký hạng mục thi phím để lấy số của sản phẩm KTE và KTO. Xem ảnh chụp màn hình:
Trường hợp 3: Đếm các ô có nhiều tiêu chí với logic OR và AND
Để đếm số lượng sản phẩm KTE và KTO bán được lớn hơn 200, bạn nên áp dụng công thức sau:
Sau đó nhấn đăng ký hạng mục thi phím để nhận số kết quả bạn cần:
Ví dụ 4: Tính giá trị trung bình có trọng số với hàm SUMPRODUCT
Thông thường, chúng ta có thể tính giá trị trung bình của phạm vi ô một cách nhanh chóng và dễ dàng. Tuy nhiên, nếu bạn muốn tính giá trị trung bình của một danh sách các giá trị với các mức độ liên quan khác nhau, thì có nghĩa là phải tính giá trị trung bình có trọng số. Không có hàm trực tiếp để lấy nó trong Excel. Tuy nhiên, hàm SUMPRODUCT có thể giúp bạn giải quyết nó.
Ví dụ, tôi có một bảng thống kê điểm của một học sinh, mỗi nhiệm vụ có trọng số khác nhau như hình minh họa bên dưới.
Để tính giá trị trung bình có trọng số, vui lòng sử dụng công thức sau:
Giải thích: Công thức này: SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)
Và sau đó, nhấn đăng ký hạng mục thi chìa khóa, trung bình có trọng số đã được tính toán cùng một lúc, xem ảnh chụp màn hình:
Các bài viết khác có hàm SUMPRODUCT:
- Countif một giá trị cụ thể trên nhiều trang tính
- Giả sử, tôi có nhiều trang tính chứa dữ liệu sau và bây giờ, tôi muốn lấy số lần xuất hiện của một giá trị cụ thể “Excel” từ các trang tính này. Làm cách nào để đếm các giá trị cụ thể trên nhiều trang tính?
- Tìm và nhận giá trị lớn nhất dựa trên nhiều tiêu chí trong Excel
- Trong Excel, chúng ta có thể áp dụng hàm max để có được số lớn nhất nhanh nhất có thể. Tuy nhiên, đôi khi, bạn có thể cần phải tìm giá trị lớn nhất dựa trên một số tiêu chí, làm thế nào bạn có thể giải quyết công việc này trong Excel?
- Tính toán trung bình có trọng số trong Excel
- Ví dụ: bạn có một danh sách mua sắm với giá cả, trọng lượng và số lượng. Bạn có thể dễ dàng tính giá trung bình với hàm AVERAGE trong Excel. Nhưng nếu giá bình quân gia quyền thì sao? Trong bài viết này, tôi sẽ giới thiệu phương pháp tính bình quân gia quyền, cũng như phương pháp tính bình quân gia quyền nếu đáp ứng các tiêu chí cụ thể trong Excel.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
