Excel là ứng dụng hỗ trợ làm việc văn phòng rất hiệu quả. Trong excel có hỗ trợ nhiều hàm giúp việc tính toán trở nên đơn giản hơn, một trong số đó phải kể đến hàm Sumproduct. Vậy hàm sumproduct để làm gì? Bài viết dưới đây sẽ cung cấp những thông tin bổ ích về loại hàm này và hướng dẫn bạn cách dùng hàm sumproduct tính tổng nhiều điều kiện.
- Phần mềm quản lý bán hàng đa kênh dùng thử miễn phí
Xem thêm: Tổng hợp các thủ thuật Excel mới nhất
Tìm hiểu về hàm Sumproduct là gì?
Hàm sumproduct là một hàm công thức trong excel thuộc nhóm dữ liệu dạng mảng. Đây là sự kết hợp của hai hàm sum – hàm tính tổng và hàm product – hàm dùng tính tích.
Cú pháp sử dụng hàm: =sumproduct (array1, [array2], [array3],…)
Trong đó:
- array1 là tham số bắt buộc. Đây chính là đối số của mảng thứ nhất, tại đây chứa các thành phần mà bạn muốn nhân, sau đó cộng tổng chúng lại với nhau.
- [array2], [array3] …chính là tham số tuỳ chọn. Từ mảng thứ hai trở đi là các mảng mà bạn muốn nhân các thành phần trong nó rồi cộng chúng lại với nhau.
Nguyên tắc đếm của hàm sumproduct trong excel
Giá trị của hàm sumproduct tới từ các mảng array trong công thức tính của nó. Phạm vi hoạt động của các mảng này khá linh hoạt, bạn có thể gộp nhiều vùng dữ liệu vào hoặc một vài phần tử của một vùng dữ liệu. Việc tính toán theo các mảng sẽ không gặp giới hạn, việc tính toán trở nên dễ dàng hơn.
Để đạt điều kiện thì mỗi phần tử trong vung đó phải thỏa mãn điều kiện thì sẽ được tính là 1. Kết quả của mỗi lần đếm đưa ra chính là tổng các giá trị thỏa mãn những điều kiện trên. Việc này tương tự với việc tính tổng các giá trị thỏa mãn điều kiện mà chúng ta hay dùng. Tuy nhiên điểm khác biệt chính là mỗi giá trị thỏa mãn sẽ không phụ thuộc vào bất kỳ giá trị nào khác ngoài 1.
Những lưu ý khi sử dụng hàm sumproduct
- Các mảng trong hàm sumproduct đều phải có cùng số cột, số hàng. Nếu không cùng số cột số hàng, khi tính kết quả sẽ trả về giá trị lỗi, bạn sẽ không thể thực hiện được phép tính.
- Trường hợp có một ô nào đó trong mảng không có dạng số, hàm sumproduct tự mặc định giá trị của nó là 0 và tiếp tục tính toán.
- Số mảng tối đa có thể tính toán bởi hàm sumproduct là 255 mảng với các đời excel từ 2007 trở đi.
- Hàm này không hỗ trợ ký tự đại diện.
- Nếu mảng là một phép thử logic, giá trị trả về sẽ là FALSE hoặc TRUE.
Hướng dẫn sử dụng hàm sumproduct tính tổng trong excel
Có nhiều cách dùng hàm sumproduct tính tổng trong excel. Sau đây chính là hướng dẫn cách dùng hàm trong một số trường hợp thường gặp.
Dùng hàm sumproduct tính tổng của các tích
Đây là phần sử dụng cơ bản của hàm này, bạn có thể đơn giản phép tính của mình mà kết quả trả về hoàn toàn chính xác. Điều này giúp tiết kiệm thời gian, công sức của bạn trong làm việc.
Ví dụ 1: tính tổng doanh thu của một cửa hàng có bán nhiều loại mặt hàng. Nếu bạn không biết cách sử dụng hàm sumproduct trong excel thì phép tính của bạn sẽ dài hơn. Bạn phải tính tích từng cột riêng sau đó lại gộp tổng của chúng lại. Hàm sumproduct sẽ hỗ trợ giải quyết rất đơn giản:
Nhập cú pháp: =SUMPRODUCT (A2:A5,B2:B5) và enter, bạn sẽ nhận được kết quả trả về chính xác.
Dùng hàm sumproduct tính tổng nhiều điều kiện
Đây chính là điểm mạnh của hàm này, giúp hỗ trợ tính toán những trường hợp khó hơn với nhiều mảng phức tạp hơn. Là một nhân viên văn phòng, đặc biệt là kế toán, việc nắm vững các hàm tính toán trong excel là điều cần thiết. Điều này giúp công việc của bạn trở nên suôn sẻ hơn, bạn không còn lo lắng khi phải tính toán quá nhiều số liệu khác nhau. Hãy xem và ứng dụng nó trong công việc nhé, chắc chắn bạn sẽ có hiệu quả làm việc tốt hơn.
Ví dụ: bạn hãy tính tổng các Sp1 thuộc miền Nam theo dữ liệu trong bảng dưới đây.
Chúng ta cùng phân tích đề bài để hiểu rõ hơn nhé. Theo yêu cầu, ta phải tính tổng Sp1 với hai điều kiện phải đáp ứng đó là sản phẩm đó thuộc miền Nam và có tên là Sp1.
Cách tính bài toán này được trình bài theo các bước sau:
Bước 1: Khi xác định được các điều kiện rõ ràng, ta bắt tay vào tính toán. Với điều kiện tên sản phẩm là sp1, nhìn vào bảng ta thấy được không chỉ một mà có nhiều mã hàng có chứ Sp1. Bây giờ, ta phải tách sp1 ra khỏi các mã hàng đó bằng cách dùng công thức sau: =LEFT(A2:A7,3). Trong công thức này, 3 chính là số ký tự cần lấy, A2: A7 chính là mảng tên.
Bước 2: Với điều kiện miền Nam, bạn phải tách chúng ra khỏi mảng các miền. Sử dụng công thức sau để tách: =RIGHT(B1:E1, 3).
Bước 3: Khi đã xử lý tách điều kiện xong, bây giờ ta dùng hàm sumproduct để tính toán kết quả.
Ta nhập công thức sau: =SUMPRODUCT ((LEFT(A2:A7, 3)= “SP1”)*RIGHT(B1:(B2:E7)).
Sau đó nhấn enter và ta thu được kết quả chính xác cần tính.
Một số ví dụ về hàm Sumproduct nâng cao
Hàm SUMPRODUCT linh hoạt và nhiều công dụng hơn rất nhiều khi tính tổng theo điều kiện. Dưới đây là một số hàm Sumproduct tính tổng nhiều điều kiện nâng cao bạn có thể tham khảo
Tính hàm sumproduct có điều kiện
Ví dụ:
Cách dùng hàm SUMPRODUCT có điều kiện ở trên:
- Biểu thức (C4:C8>140): thực hiện so sánh từng phần tử của mảng với 140.
- Sau khi so sánh xong các phần tử trong mảng, hàm SUMPRODUCT trở thành: SUMPRODUCT(- {FALSE, FALSE, TRUE, TRUE, FALSE})
- Dấu âm “ – ” đặt trước mảng để biến các giá trị TRUE/ FALSE về dạng số nguyên -1 và 0 thì kết quả hàm SUMPRODUCT mới trả về số chính xác, nếu không hàm sẽ trả về 0. Có thể sử dụng 2 dấu “-” để trả TRUE/ FALSE về 1 và 0.
Sử dụng hàm SUMPRODUCT nhiều điều kiện
Ví dụ này sử dụng hàm SUMPRODUCT nhiều điều kiện thay cho hàm COUNTIFS vẫn thường sử dụng
Giải thích cách sử dụng hàm SUMPRODUCT nhiều điều kiện:
- Biểu thức (A4:A8=”táo”): thực hiện kiểm tra từng phần tử của mảng có phải là Táo hay không: kết quả {TRUE, TRUE, FALSE, FALSE, TRUE}
- Biểu thức (C4:C8>99): thực hiện kiểm tra từng phần tử của mảng có lớn hơn 99 hay không: kết quả {TRUE, TRUE, TRUE, TRUE, FALSE}
- Dấu * trong hàm biến TRUE/ FALSE về dạng số 1/ 0
Hàm SUMPRODUCT thực hiện chức năng trả về số sản phẩm thỏa mãn cả 2 điều kiện
Nguyên nhân gặp lỗi value trong hàm Sumproduct
Khi mới học cách sử dụng hàm sumproduct, nhiều người thường mắc phả lỗi #VALUE và dưới đây là một trong những nguyên nhân gây nên tình trạng này
Vùng tham chiếu bị lệch kích thước
Vùng tham chiếu trong đối số của hàm Sumproduct có kích thước không giống nhau, điều này sẽ tạo ra lỗi #VALUE!
Trong vùng tham chiếu có ô chứa lỗi #VALUE!
Trong vùng tham chiếu có 1 hay nhiều ô chứa lỗi #VALUE! cũng là lý do gây ra lỗi #VALUE!
Cách khắc phục: chỉnh sửa lại dữ liệu dùng để tính toán trước khi áp dụng hàm Sumproduct
Kết luận
Trên đây mình đã giới thiệu cho các bạn về hàm sumproduct. Hy vọng với những gì hướng dẫn, mọi người nắm rõ và có thể ứng dụng được vào trong công việc của mình.
