Trong quá trình tính toán trên Excel bạn đã sử dụng đến rất nhiều các loại hàm. Hàm SUMPRODUCT chắc hẳn đã có bạn sử đụng, nhưng cũng có bạn chưa sử dụng và chưa biết rõ về hàm này. Vậy hàm SUMPRODUCT dùng để làm gì? Bài viết này sẽ giúp bạn hiểu rõ hơn.
Hàm SUMPRODUCT
Hàm SUMPRODUCT là loại hàm trong Excel giúp bạn nhân các số trong mảng dữ liệu được chọn, sau đó trả về tổng của các tích số đó. Cú pháp của hàm SUMPRODUCT:
SUMPRODUCT(Array1, [Array2], [Array3], …)
Trong đó:
Array1, Array2,… là các dải ô liên tục hay là các mảng có chứa thành phần bạn muốn nhân, rồi cộng lại.
Hàm này có số mảng tối thiểu là 1 và số mảng tối đa là 255.
Lưu ý khi sử dụng hàm SUMPRODUCT
- Tất cả mảng trong công thức SUMPRODUCT phải có cùng số hàng và số cột, nếu nhưng số hàng hay số cột không giống nhau thì sẽ không trả về kết quả mà sẽ báo lỗi #VALUE!.
- Nếu bất kỳ dữ liệu nào trong mảng không phải là số thì những dữ liệu đó được xử lý thành số 0.
- Nếu mảng là phép thử logic, thì nó sẽ trả về giá trị TRUE và FALSE. Thông thường, nếu bạn cần phải đổi các giá trị TRUE và FALSE này thành 1 và 0 bằng cách sử dụng toán tử đơn phân (-).
Cách sử dụng hàm SUMPRODUCT
DẠNG 1: Cách sử dụng cơ bản
Chúng ta dùng hàm SUMPRODUCT để tính tổng giá tiền khi có số lượng và đơn giá.
Ví dụ: Hãy tính tổng tiền của bảng bên dưới:
Với bài như bảng trên chúng ta sẽ sử dụng hàm SUMPRODUCT để tính toán. Với cú pháp:
SUMPRODUCT(“Cột Số lượng”,”Cột Đơn giá”)
DẠNG 2: Dùng hàm SUMPRODUCT có nhiều điều kiện
Ví dụ: Bảng dữ liệu như bên dưới. Hỏi có bao nhiêu sản phẩm có số lượng đã bán ít hơn số lượng dự kiến.
Với dạng bài tìm số lượng thì có rất nhiều loại hàm để thực hiện. Và hàm SUMPRODUCT cũng có thể đếm. Theo cú pháp:
SUMPRODUCT(-(“Cột Số lượng đã bán”<”Cột Số lượng bán dự kiến”))
hay
SUMPRODUCT((“Cột Số lượng đã bán”<”Cột Số lượng bán dự kiến”)*1)
Ngoài ra nếu bạn chỉ muốn biết có bao nhiêu sản phẩm Chuối bán ít hơn dự kiến, thì bạn có thể áp dụng công thức:
SUMPRODUCT(-(“Cột Số lượng đã bán”<”Cột Số lượng bán dự kiến”),-(“Cột Sản phẩm”=”Chuối”))
Hay
SUMPRODUCT((“Cột Số lượng đã bán”<”Cột Số lượng bán dự kiến”)*(“Cột Sản phẩm”=”Chuối”))
DẠNG 3: Dùng SUMPRODUCT kết hợp với hàm SUM để tính giá trị trung bình
Chúng ta có thể tính giá trị trung bình với cú pháp:
SUMPRODUCT(“Cột Số lượng”,”Cột Đơn giá”)/SUM(“Cột Số lượng”)
DẠNG 4: Dùng hàm SUMPRODUCT và hàm MOD để đếm ô chứa số lẻ
Để đếm những số lẻ, chúng ta sẽ thực hiện công thức như sau:
SUMPRODUCT(-(MOD(“Cột chứa dãy số cần đếm”,2)=1))
Công thức này sẽ hoạt động như sau, (MOD(“Cột chứa dãy số cần đếm”,2)=1) sẽ trả về giá trị mảng {TRUE; FALSE}, sẽ là TRUE nếu ô không chia hết cho 2 và là FALSE nếu ô chia hết cho 2. Tương ứng với TRUE là 1 và FALSE là 0. Và cuối cùng là hàm SUMPRODUCT sẽ có chức năng tính tổng lại các giá trị, cho ra kết quả.
Ví dụ: Đếm những ô chứa số lẻ bảng bên dưới:
Chúng ta sẽ có công thức
Một số ứng dụng của hàm SUMPRODUCT
ỨNG DỤNG 1: Tính tổng số giờ đi làm, giờ đi làm muộn, giờ về sớm,…
Chúng ta sẽ quy ước là giờ đi làm là 8:00 ở ô BH1, nếu đi sau 8:00 sẽ là đi trễ.
Do trong Excel chỉ tính một số thập phân 0 -> 1 nên để quy ra số phút, chúng ta cần nhân cho 24 giờ x 60 phút.
Ở ứng dụng này hàm SUMPRODUCT sẽ lấy giờ checkin, trừ cho giờ quy định.
ỨNG DỤNG 2: Tính công, chấm công cho nhân viên
Để có thể thực hiện tính ngày công một cách nhanh gọn, không mất quá nhiều thời gian, cũng như việc liệt kê, kiểm soát lại thì bạn nên lập bảng ký hiệu, và quy đổi ký hiệu đó sang ngày công tương ứng. Từ đó bạn có thể tính ngày công một cách dễ dàng, cũng như dùng hàm thích hợp để tính nhanh hơn.
Hàm SUMPRODUCT kết hợp với các hàm
ISNUMBER/ISERROR + SEARCH
Hàm SEARCH khi kết hợp với hàm SUMPRODUCT sẽ giúp bạn tìm kiếm điều kiện có trong vùng tìm kiếm. Nếu tìm thấy sẽ trả về số của vị trí tìm thấy, còn không tìm thấy hàm sẽ trả về #VALUE. Lúc này để tránh lỗi, ta dùng hàm ISNUMBER để bỏ qua lỗi, chỉ tính cái tìm thấy.
Lưu ý: Hàm SEARCH giúp chúng ta tìm kiếm không phân biệt chữ Hoa/thường, nếu bạn cần tìm chữ giống nhau thì lúc này có hàm FIND.
Hàm SUMIFS
Khi bạn làm việc có các sheet khác nhau đồng nhất về cấu trúc, ví dụ như bảng chấm công từng tháng trong năm. Bạn làm báo cáo tổng kết cuối năm, lúc này sự kết hợp của các hàm: SUMPRODUCT+SUMIFS+INDIRECT sẽ giúp bạn làm nhanh hơn.
Ví dụ:
Bài viết này đã nói rõ hơn về hàm SUMPRODUCT và những cách sử dụng của hàm. Hi vọng sau khi đọc qua bài viết này bạn có thể áp dụng loại hàm này vào công việc của mình để có thể thao tác nhanh hơn.
Tham khảo các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel
- Có những cách nào để gộp ô trong excel nhưng không mất dữ liệu?
- Làm thế nào để xuống dòng trong một ô tính?
- Cách dùng hàm subtotal và ứng dụng của hàm
Team Content Uniace
