Sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện hiệu quả trong Excel
Khi bạn nghe tên hàm SUMPRODUCT lần đầu tiên, nó có vẻ giống như một công thức vô dụng thực hiện một tổng số thông thường của hoạt động sản phẩm. Nhưng với cách hướng dẫn dưới đây thì thực sự hàm SUMPRODUCT không hề vô dụng chút nào nhé.
1. Sơ lược về hàm SUMPRODUCT
Hàm SUMPRODUCT trong Excel là hàm tính tích các phạm vi hoặc mảng với nhau và trả về tổng số sản phẩm. Điều này nghe có vẻ khá nhàm chán, nhưng hàm SUMPRODUCT có những chức năng cực kỳ linh hoạt có thể được sử dụng để đếm và tính tổng linh hoạt hơn. Trong thực tế, hàm SUMPRODUCT được dùng khá nhiều và cực kỳ hữu ích.
2. Công thức tính của hàm SUMPRODUCT
Về mặt kỹ thuật, hàm SUMPRODUCT trong Excel sẽ nhân các số trong các mảng đã chỉ định và trả về tổng của các sản phẩm đó.
Cú pháp của hàm SUMPRODUCT rất đơn giản và dễ hiểu:
TÓM TẮT (mảng 1, [mảng 2], [mảng 3], tầm)
Cú pháp của hàm SUMPRODUCT rất đơn giản và dễ hiểu:
TÓM TẮT (mảng 1, [mảng 2], [mảng 3], tầm)
Trong đó:
mảng 1, mảng 2, v.v. là các phạm vi ô hoặc mảng liên tục có các phần tử bạn muốn nhân, sau đó thêm vào.
Số lượng mảng tối thiểu là 1. Trong trường hợp này, công thức SUMPRODUCT chỉ đơn giản là cộng tất cả các phần tử mảng và trả về tổng.
Số lượng mảng tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007 và 30 trong các phiên bản Excel trước đó.
Mặc dù SUMPRODUCT hoạt động với các mảng, nhưng nó không yêu cầu sử dụng phím tắt mảng ( Ctrl + Shift + Enter).. Bạn cạnh tranh một công thức SUMPRODUCT theo cách thông thường bằng cách nhấn phím Enter.
mảng 1, mảng 2, v.v. là các phạm vi ô hoặc mảng liên tục có các phần tử bạn muốn nhân, sau đó thêm vào.
Số lượng mảng tối thiểu là 1. Trong trường hợp này, công thức SUMPRODUCT chỉ đơn giản là cộng tất cả các phần tử mảng và trả về tổng.
Số lượng mảng tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007 và 30 trong các phiên bản Excel trước đó.
Mặc dù SUMPRODUCT hoạt động với các mảng, nhưng nó không yêu cầu sử dụng phím tắt mảng ( Ctrl + Shift + Enter).. Bạn cạnh tranh một công thức SUMPRODUCT theo cách thông thường bằng cách nhấn phím Enter.
Lưu ý:
+ Tất cả các mảng trong công thức SUMPRODUCT phải có cùng số lượng hàng và cột, nếu không, bạn sẽ nhận được #VALUE! lỗi.
+ Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số, chúng sẽ được coi là số không.
+ Nếu một mảng là một bài kiểm tra logic, nó sẽ dẫn đến 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 nguyên kép (-).
+ Tổng hợp không hỗ trợ các ký tự đại diện.
+ Tất cả các mảng trong công thức SUMPRODUCT phải có cùng số lượng hàng và cột, nếu không, bạn sẽ nhận được #VALUE! lỗi.
+ Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số, chúng sẽ được coi là số không.
+ Nếu một mảng là một bài kiểm tra logic, nó sẽ dẫn đến 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 nguyên kép (-).
+ Tổng hợp không hỗ trợ các ký tự đại diện.
3. Ví dụ cách sử dụng hàm SUMPRODUCT
Giả sử bạn có số lượng trong các ô A2: A4, giá trong các ô B2: B4 và bạn muốn tìm hiểu tổng số. Nếu bạn đang làm bài kiểm tra toán ở trường, bạn sẽ tính tích số lượng với giá cho mỗi mục, sau đó cộng các tổng phụ. Trong Microsoft Excel, bạn có thể nhận kết quả bằng một công thức SUMPRODUCT duy nhất:
=SUMPRODUCT(A2:A4,B2:B4)
=SUMPRODUCT(A2:A4,B2:B4)
Ví dụ về cách sử dụng hàm sumproduct trong Excel.
3. Cách sử dụng hàm SUMPRODUCT
+ Trường hợp 1: Sử dụng hàm SUMPRODUCT với một tiêu chí duy nhất
Ví dụ: Tập dữ liệu dưới đây chứa 6 tên trong các hàng và 12 tháng trong các cột. Hãy tư
ởng tượng sếp của bạn muốn bạn cộng tất cả các số cho tháng Sáu. Bắt đầu công thức của bạn bằng cách chọn phạm vi dữ liệu (không có tên và tháng) và nhân nó với mảng chứa các tháng bằng 6.
Các bước thực hiện:
Bước 1: Nhập công thức: = SUMPRODUCT(array1,[array2],[array3],…)
Các bước thực hiện:
Bước 1: Nhập công thức: = SUMPRODUCT(array1,[array2],[array3],…)
Trường hợp 1: Sử dụng hàm SUMPRODUCT với một tiêu chí duy nhất
Trong đó:
Mảng 1 của công thức hướng dẫn Excel cộng tất cả các số trong lựa chọn C9: N14. Array2 kiểm tra từng số tháng xem nó có bằng 6. Kết quả của việc này là TRUE (1) hoặc FALSE (0). Bạn có thể thấy kết quả của hàng này trong hàng 16. Chỉ với cột H kết quả là TRUE, vì vậy kết quả là 1. Để nhân Array1 với Array2 có nghĩa là nhân các số thực với 0 hoặc 1. Trong ví dụ này, công thức nhân tất cả các số trong cột C bằng 0 và tất cả các số từ cột H bằng 1. Tất cả các giá trị được nhân với 0 cộng với không. Các số duy nhất còn lại được nhân với 1, trong trường hợp này là tháng 6.
Mảng 1 của công thức hướng dẫn Excel cộng tất cả các số trong lựa chọn C9: N14. Array2 kiểm tra từng số tháng xem nó có bằng 6. Kết quả của việc này là TRUE (1) hoặc FALSE (0). Bạn có thể thấy kết quả của hàng này trong hàng 16. Chỉ với cột H kết quả là TRUE, vì vậy kết quả là 1. Để nhân Array1 với Array2 có nghĩa là nhân các số thực với 0 hoặc 1. Trong ví dụ này, công thức nhân tất cả các số trong cột C bằng 0 và tất cả các số từ cột H bằng 1. Tất cả các giá trị được nhân với 0 cộng với không. Các số duy nhất còn lại được nhân với 1, trong trường hợp này là tháng 6.
+ Trường hợp 2: Sử dụng hàm SUMPRODUCT làm tiêu chí cho các cột
+ Hãy tiếp tục ví dụ trên bằng cách thêm các tiêu chí khác. Sếp của bạn bây giờ muốn biết doanh số bán hàng trong quý 2 là bao nhiêu, vì vậy từ tháng thứ 4 trở lên và bao gồm cả tháng 6. Có nghĩa là chúng tôi có một phạm vi dữ liệu lớn hơn để thêm vào. Làm thế nào để bạn xử lý nó?
Trường hợp 2: Sử dụng hàm SUMPRODUCT làm tiêu chí cho các cột
Mô tả:
Công thức sử dụng là:
=SUMPRODUCT(( C9:N14) * (C8:N*>=4) * (C8:N8<=6))
Có 3 tháng thuộc về Q2. Lưu ý rằng bạn không cần ba tuyên bố để chỉ định mỗi tháng. Thay vào đó, bạn có thể sử dụng một công trình trong đó các tháng lớn hơn hoặc bằng 4 (> = 4) và nhỏ hơn hoặc bằng 6 (<= 6).
Thường thì bạn sẽ muốn làm cho công thức này là năng động nhất có thể. Không phải ai cũng thoải mái khi thay đổi công thức phức tạp. Tuy nhiên, hầu hết mọi người đều hoàn toàn ổn với việc điều chỉnh một số trong một tế bào. Thay vì mã hóa cứng các tháng để thêm, bạn có thể tham khảo các ô chỉ định các giá trị này. Các ô này thường được gọi là tham số.
Công thức sử dụng là:
=SUMPRODUCT(( C9:N14) * (C8:N*>=4) * (C8:N8<=6))
Có 3 tháng thuộc về Q2. Lưu ý rằng bạn không cần ba tuyên bố để chỉ định mỗi tháng. Thay vào đó, bạn có thể sử dụng một công trình trong đó các tháng lớn hơn hoặc bằng 4 (> = 4) và nhỏ hơn hoặc bằng 6 (<= 6).
Thường thì bạn sẽ muốn làm cho công thức này là năng động nhất có thể. Không phải ai cũng thoải mái khi thay đổi công thức phức tạp. Tuy nhiên, hầu hết mọi người đều hoàn toàn ổn với việc điều chỉnh một số trong một tế bào. Thay vì mã hóa cứng các tháng để thêm, bạn có thể tham khảo các ô chỉ định các giá trị này. Các ô này thường được gọi là tham số.
Trường hợp 2: Sử dụng hàm SUMPRODUCT làm tiêu chí cho các cột
Để đạt được điều đó, hãy thêm tháng giới hạn thấp hơn trong ô J4 và giới hạn cao hơn trong ô J5. Bây giờ thay đổi công thức để phản ánh điều này. Thay vì tham khảo số 4, hãy viết ô J4. Thay số 6 bằng ô J5.
Bạn sẽ được công thức cuối cùng sẽ là:
=SUMPRODUCT ((C9:N14) * (C8:N8>= J14) * (C8:N8<=J5))
Bạn sẽ được công thức cuối cùng sẽ là:
=SUMPRODUCT ((C9:N14) * (C8:N8>= J14) * (C8:N8<=J5))
+ Trường hợp 3: Sử dụng hàm SUMPRODUCT làm tiêu chú cho cột và hàng
Ví dụ trước đó cho thấy các tiêu chí đã xem xét các tháng. Bạn có thể thực hiện một thao tác tương tự cho các hàng, như được hiển thị trong bài viết trước. Bây giờ, sếp của bạn yêu cầu bạn làm cho cột doanh thu của Lisa không được hiện ra. Cách làm như sau:
Trường hợp 3: Sử dụng hàm SUMPRODUCT làm tiêu chú cho cột và hàng
Như vậy, để tính một tiêu chí bổ sung trong các hàng, hãy thêm một mảng khác vào công thức. Chỉ định rằng các tên phải bằng “Lisa” . Như chúng ta vừa tìm hiểu, bạn có thể muốn tham khảo một ô tham số thay vì mã hóa nó. Vì vậy, viết Lisa trong ô M4. Bây giờ thêm một mảng chọn các ô B9: B14 bằng với ô tham số của bạn, trong trường hợp này là M4. Cột P cho thấy hàng nào đáp ứng điều kiện này, là hàng 11.
Công thức sử dụng là:
=SUMPRODUCT ((C9:N14) * (C8:N8 >=J4) * (C8:N<=J5) * (B9:B14=M4))
Điều này thêm vào tổng số 2.164. Đó là doanh số trong quý 2 từ Lisa.
Công thức sử dụng là:
=SUMPRODUCT ((C9:N14) * (C8:N8 >=J4) * (C8:N<=J5) * (B9:B14=M4))
Điều này thêm vào tổng số 2.164. Đó là doanh số trong quý 2 từ Lisa.
Như vậy, mình đã hướng dẫn các bạn cách sử dụng của hàm SUMPRODUCT, hy vọng bài viết này sẽ giúp ích được nhiều cho bạn.
Trong quá trình học, các bạn còn vấn đề gì thắc mắc xin truy cập Khóa học Excel trên Unica. Chúc các bạn thành công!
Tags: Excel
Khóa học nổi bật
-
Nguyễn Lê Hoàng499,000đ800,000đ
?DienDan.Edu.Vn cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn. https://diendan.edu.vn/
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
