Hôm nay chúng ta cùng quay trở lại với Học Exel Online để cùng bàn luận về hàm Sumproduct. 

Cú pháp hàm SUMPRODUCT

=SUMPRODUCT(--(Biểu thức),(Vùng tính tổng))

hoặc:

=SUMPRODUCT((Biểu thức)*(Vùng tính tổng))

• Khi chỉ có biểu thức: =SUMPRODUCT(--(Biểu thức)), lúc này SUMPRODUCT tính theo dạng đếm tương tự COUNTIFS.
• Khi có cả biểu thức lẫn vùng tính tổng: =SUMPRODUCT(--(Biểu thức),(Vùng tính tổng)), lúc này SUMPRODUCT sẽ tính theo dạng tính tổng tương tự SUMIFS.
• Hàm SUMPRODUCT sẽ tính toán chậm hơn so với SUMIFS/COUNTIFS, tuy nhiên sẽ có nhiều trường hợp ta chỉ có thể dùng hàm SUMPRODUCT để giải quyết bài toán. Vì vậy các bạn nên xét trường hợp phù hợp mà áp dụng.
• Các vùng tham chiếu, chỉ nên giới hạn, không nên dùng cả cột (A:A, phải ghi rõ từ đâu tới đâu, ví dụ A2:A1000).
• Căn bản hàm SUMPRODUCT là 1 hàm mảng, vì vậy KHÔNG cần phải nhấn CTRL+SHIFT+ENTER.
• Lưu ý: dấu --, là 2 dấu trừ, dùng để chuyển đổi “chữ số” thành số. Ví dụ: =--"1" => 1.

Xem thêm: Hướng dẫn cách dùng hàm Sumif trong Excel qua các ví dụ cụ thể

Bạn có thể tham khảo video giải thích hàm SUMPRODUCT qua video bài giảng sau:

Ta có ví dụ sau:

Ta những sản phẩm khác nhau có trong kho A, B, C. Bây giờ làm sao tính tổng số lượng có trong Kho A? Và có bao nhiêu sản phẩm trong kho A? Kết quả ta có:

• Dấu phẩy phân cách: =SUMPRODUCT($E$2:$E$5,--($A$2:$A$5=G2))
  • Vùng tính tổng: $E$2:$E$5.
  • Biểu thức so sánh điều kiện: $A$2:$A$5=G2.
  • Khi chỉ có 1 biểu thức, bạn nên dùng dấu --() để chuyển đổi dữ liệu từ TRUE/FALSE sang dạng số. Có lúc bạn sẽ thấy có lúc *1, hoặc +0, thực ra đó cũng là cách chuyển đổi dữ liệu TRUE/FALSE sang số. Nhưng cách tốt nhất nên dùng là --(), sẽ giúp Excel đỡ tính toán hơn.
  • Bạn có thể chuyển đổi cái nào trước, cái nào sau không quan trọng, nhưng mình khuyến khích các bạn nên đồng nhất theo kiểu SUMIFS(Vùng tính tổng, Vùng điều kiện, Điều kiện).
• Dấu nhân (*) kết hợp: =SUMPRODUCT(($E$2:$E$5)*($A$2:$A$5=G2))
  • Thay vì dùng dấu phẩy, ta có thể dùng các vùng nhân với nhau.
  • Lúc này khi có phép tính nhân giữa các vùng điều kiện và vùng tính tổng, nên bạn cũng không cần chuyển đổi dữ liệu TRUE/FALSE, vì khi có phép toán tử thì Excel sẽ chuyển dữ liệu sang số rồi mới tính.

Vậy dấu nhân và dấu phẩy trong SUMPRODUCT khác nhau ở đâu? Cách xử lý lỗi trả về khi áp dụng.

Khi dữ liệu của bạn bị lẫn “chữ” ký tự ở ô E6, lúc này SUMPRODUCT phép nhân sẽ trả về #VALUE. Bởi vì “Chữ” nhân với một số sẽ trả về #VALUE. Lúc này ta dùng dấu phẩy, sẽ bỏ qua chữ tương tự hàm SUM, sẽ tính tổng vùng bỏ qua chữ và nếu có giá trị lỗi trong vùng tính tổng, sẽ trả về lỗi.

Vậy tùy tình huống, các bạn cần áp dụng cho phù hợp, cũng có hướng dẫn nên dùng hàm SUMIFS hoặc COUNTIFS để có thể bỏ qua lỗi #NA, #VALUE trong dữ liệu. Tuy nhiên, mình cũng khuyến khích nên dùng SUMPRODUCT để khi nhận được thông báo lỗi! Vì sao? Vì nếu bỏ qua lỗi, chúng ta sẽ bị sai lệch số liệu mà không hay biết có lỗi phát sinh trong dữ liệu.

Ngoài ra, tương tự như hàm COUNTIFS, SUMIFS nếu các độ dài của các vùng không khớp nhau. Ví dụ: SUMPRODUCT($E$1:$E$6,--($A$2:$A$6)), sẽ trả về #VALUE. Vì vùng E1:E6 là sai, phải khớp nhau E2:E6 so với A2:A6. Vậy tại sao không sửa lại A1:A6 cho khớp, bạn nên bỏ qua vùng tiêu đề vì đó là chữ, nếu dùng phép nhân * sẽ lỗi.

Trong ví dụ trên, ta có thể tính tổng tích nhân nhau giữa Số lượng và Đơn giá theo điều kiện sản phẩm trong kho A. Ở đây bạn lưu ý, với dấu phẩy mình có thể bắt đầu vùng bao gồm chữ. Nhưng với phép nhân, thì mình đã bỏ qua dòng tiêu đề, bắt đầu từ dòng thứ 2. Lúc này phép tính xảy ra như sau: (1*2000)+(1000*2) = 4000, cho sản phẩm có trong kho A.

Ngoài ra, phức tạp hơn bạn có thể dùng kết quả tính toán trước để ghép nối tìm ra từ bảng khác tương ứng. Bạn có thể dùng công cụ Evaluate Formula để kiểm tra công thức mình bị lỗi chỗ nào, khi mắc lỗi, cũng như xem xét các điều kiện.

Các ứng dụng thường gặp, và các hàm thường dùng với SUMPRODUCT.

• Các hàm kiểm tra logic: ISNUMBER/ISERROR + SEARCH

  • Hàm SEARCH giúp bạn tìm kiếm điều kiện có trong vùng tìm kiếm hay không? 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.
    
    Ví dụ, ta tính tổng số lượng sản phẩm chứa chữ “á“, trong ví dụ bạn lưu ý khác biệt giữa dấu * với dấu phẩy (,).
  • 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.

• Tính tổng tất cả các sheet với SUMIFS

  • Khi bạn có các sheet khác nhau đồng nhất về cấu trúc, ví dụ 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 hàm SUMPRODUCT+SUMIFS+INDIRECT sẽ giúp bạn làm việc này.
    Hoặc bạn có danh sách sản phẩm từng tháng, bạn cần tính số lượng tương ứng. Lúc này bạn chỉ cần liệt kê danh sách tên các sheet, và vùng dữ liệu tương ứng. Vậy là ta có được tính tổng một cách nhanh chóng.
• Tách chuỗi ra để lấy số liệu tính toán:
  • Bài toán đặt ra bạn có chuỗi ký tự: u, x, 3g, nhuộm. Và làm thế nào để tách ra theo các đơn vị tính tương ứng.
  • Vận dụng kết hợp hàm TEXT để chuyển đổi các số, chữ, và số 0 thành con số tương ứng để tính toán.

• Chuyển chuỗi ký tự biểu thức cộng trừ thành kết quả tính toán:
  • Bước 1: Chúng ta sẽ tìm thay thế ký tự “-” thành “+-“.
  • Bước 2: Chúng ta sẽ tìm thay thế ký tự “+” với hàm REPT(” “,99) nhằm chèn thay thế 99 ký tự ” “.
  • Bước 3: Hàm MID sẽ tách các phần ra giữa 99 ký tự, bạn có thể tìm hiểu thêm qua bài viết SUBSTITUTE và 40 tên cướp.
  • Bước 4: Khác với hàm TRIM, chúng ta sẽ dùng hàm TEXT nhận dạng ký tự.
  • Bước 5: Với hàm SUMPRODUCT kết hợp dấu -- (dùng để chuyển chữ thành số), sau đó cộng dồn lại với nhau.
  • Kết quả:=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","+-"),"+",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A2)))*99-98,99),"0.00;-0.00;0;\0"))

• Đếm duy nhất với SUMPRODUCT:

• Tính số giờ đi muộn, về sớm, giờ làm của nhân viên với hàm SUMPRODUCT.

  • Ta quy ước giờ đi muộn vào ô BM1, là 8:00, nếu đi sau giờ đó tính là muộn.
  • SUMPRODUCT lúc này lấy giờ checkin, trừ cho giờ quy định.
  • Để quy ra số phút, ta cần nhân cho 24 giờ x 60 phút. Vì thời gian trong Excel là một con số thập phân từ 0 -> 1.
  • 

• Ứng dụng SUMPRODUCT trong việc chấm công, tính công cho nhân viên.

  • Bạn có thể lập bảng ký hiệu, và quy đổi ký hiệu đó sang ngày công tương ứng. Rồi từ đó bạn hoàn toàn có thể tính ngày công.
  • Ví dụ như sau, ta thấy có 4x = 4 ngày công, 1np = 1 ngày công, x2 = 2 ngày công. Kết quả 7 là chính xác.
    
  • Ứng dụng chấm công theo giờ, tính tăng ca ngoài giờ và Chủ nhật.
    
  • Ứng dụng SUMPRODUCT kết hợp SUBTOTAL để tính toán bỏ qua các dòng ẩn, cột ẩn:
    Bài toán đặt ra: Sẽ tính tổng số nhân công theo từng ngày, và họ có thể nghỉ lễ nhưng vẫn đi làm nên cần tính công từng ngày.