Hướng dẫn sử dụng hàm SUMPRODUCT nâng cao trong Excel

Hàm SUMPRODUCT là một hàm rất hữu ích trong Excel. Hàm này thay thế được cho hầu hết các hàm khác như hàm SUM, hàm COUNT, hàm SUMIF, COUNTIF, SUMIFS, COUNTIFS… ngoài ra hàm Sumproduct còn giúp bạn tính được trong cả những mảng 2 chiều mà các hàm khác không làm được. Trong bài viết này chúng ta cùng tìm hiểu cách sử dụng hàm SUMPRODUCT nâng cao trong Excel nhé.

Cách sử dụng hàm SUMPRODUCT

Trước khi tìm hiểu các ứng dụng nâng cao, chúng ta cùng xem lại cách sử dụng hàm SUMPRODUCT:

Trong hình trên ta có:

Để tính tổng thành tiền của các mặt hàng, theo cách tính thông thường thì chúng ta phải làm như sau:

• Với mỗi 1 mặt hàng, lấy Số lượng * Đơn giá = Thành tiền của từng mặt hàng (tích của từng đối tượng)
• Tổng thành tiền = Tổng thành tiền của từng mặt hàng (tổng của các tích)

Vì vậy để rút gọn lại cách tính trên, chúng ta có hàm SUMPRODUCT gồm 2 mảng:

• Số lượng là vùng F6:F9
• Đơn giá là vùng G6:G9
• Hai mảng này nhân với nhau, cho chúng ta kết quả như mong muốn

Đây là nguyên tắc hoạt động cơ bản của hàm SUMPRODUCT. Sau đây chúng ta tìm hiểu tiếp một số ứng dụng nâng cao của hàm này.

Lồng ghép hàm khác trong hàm SUMPRODUCT để thay đổi giá trị của mảng cần tính

Trong ví dụ trên, để có thể tính tổng tiền của những mặt hàng bán được trong tháng 3, nhưng dữ liệu chỉ có cột Ngày bán, không có sẵn cột Tháng. Để tính được, chúng ta phải tách được Tháng trong cột Ngày.

Ở cách tính thông thường, chúng ta phải làm như sau:

• Dùng hàm IF để xét xem tháng của từng ngày bán có phải là 3 không. Nếu đúng thì sẽ lấy số lượng nhân đơn giá, còn không thì trả kết quả là số 0
• Sau đó dùng hàm SUM để cộng tổng lại các kết quả thu được từ hàm IF trên từng dòng

Cách làm như vậy sẽ tốn nhiều công thức và thời gian phải không? Nếu dùng hàm SUMPRODUCT chúng ta chỉ cần 1 hàm là ra ngay kết quả, trong đó xét 3 mảng:

• Mảng Ngày bán: B2:B5 sẽ kết hợp hàm MONTH cho mảng này để có thể đưa về giá trị số tháng, từ đó so sánh với số 3
• Mảng Số lượng: C2:C5
• Mảng Đơn giá: D2:D5
• Nhân 3 mảng này lại với nhau, ta thu được kết quả

Tham khảo thêm:

Cách lập báo cáo tổng hợp theo từng tháng với hàm SUMIFS

Cách lập báo cáo theo tháng bằng pivot table khi dữ liệu không có sẵn cột tháng

Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiều

Trong bảng tính trên, ta có yêu cầu xác định đơn giá của 1 mặt hàng gồm 2 điều kiện: Mã hàng và Mã công ty.

Bảng chứa thông tin cần tìm (Bảng đơn giá) là bảng 2 chiều:

• Chiều dọc là thông tin mã Công ty
• Chiều ngang là thông tin mã hàng
• Giao điểm của các dòng, các cột trong bảng này chính là đơn giá cần tìm của sản phẩm.

Khi đó chúng ta sử dụng hàm SUMPRODUCT như sau:

G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)

• Xét mảng Mã công ty (B14:B17) có chứa mã công ty tại ô C3 không
• Xét mảng Mã hàng (C13:F13) có chứa mã hàng tại ô B3 không
• Nếu chứa kết quả đúng ở cả 2 mảng trên, thì sẽ lấy tương ứng theo đơn giá trong vùng C14:F17 theo dòng và cột chứa các mã đó.

Xem chi tiết tại: Hướng dẫn 4 cách xác định đơn giá theo nhiều điều kiện trong excel

Như vậy qua một số ví dụ trên, chúng ta đã có thể biết được cách kết hợp các hàm khác bên trong hàm SUMPRODUCT, cách sử dụng hàm SUMPRODUCT để tính toán trên những bảng tính 2 chiều. Hy vọng những kiến thức này sẽ giúp ích cho các bạn trong công việc.

Ngoài ra các bạn có thể tham khảo thêm một số bài viết sau để biết được những ưu nhược điểm của hàm SUMPRODUCT khi so sánh với hàm SUMIFS, COUNTIFS:

So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo nhiều điều kiện

Tại sao nên dùng hàm SUMIFS thay cho hàm SUMPRODUCT tính tổng theo nhiều điều kiện