CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL

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.

Xem thêm: Cách sử dụng hàm index, hàm match và cách kết hợp của hai hàm

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:

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 1
DẠNG 1: Cách sử dụng cơ bản

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á”)

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 2
DẠNG 1: Cách sử dụng cơ bản

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.

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 3
DẠNG 2: Dùng hàm SUMPRODUCT có nhiều điều 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”))

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 4
DẠNG 2: Dùng hàm SUMPRODUCT có nhiều điều kiện

hay

SUMPRODUCT((“Cột Số lượng đã bán”<”Cột Số lượng bán dự kiến”)*1)

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 5
DẠNG 2: Dùng hàm SUMPRODUCT có nhiều điều kiện

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”))

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 6
DẠNG 2: Dùng hàm SUMPRODUCT có nhiều điều kiện

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”))

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 7
DẠNG 2: Dùng hàm SUMPRODUCT có nhiều điều kiện

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”)

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 8
DẠNG 3: Dùng SUMPRODUCT kết hợp với hàm SUM để tính giá trị trung bình

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

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 9
DẠNG 4: Dùng hàm SUMPRODUCT và hàm MOD để đếm ô chứa số lẻ

Đọc thêm: Hướng dẫn cách thay đổi nguồn và cập nhật bảng tổng hợp PivotTable

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.

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 10
ỨNG DỤNG 1: Tính tổng số giờ đi làm, giờ đi làm muộn, giờ về sớm,…

Ứ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.

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 11
ỨNG DỤNG 2: Tính công, chấm công cho nhân viên

Có thể bạn quan tâm: Hướng dẫn Power Query cơ bản cho người mới bắt đầu. (Phần II)

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.

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 12
ISNUMBER/ISERROR + SEARCH

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ụ:

CÁC CÁCH DÙNG VÀ ỨNG DỤNG HÀM SUMPRODUCT TRONG EXCEL 13
Hàm SUMIFS

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

Trả lời

Website UNIACE có sử dụng Cookie và thu thập thông tin cá nhân của học viên tuân thủ chính sách bảo vệ thông tin cá nhân của người tiêu dùng theo quy định của pháp luận. Tiếp tục sử dụng Website đồng nghĩa với việc bạn đồng ý về các điều khoản này. Xem thêm chi tiết tại https://uniace.vn/chinh-sach-bao-mat-thong-tin/