Cách dùng hàm SUMPRODUCT để tổng hợp dữ liệu cho báo cáo

Hàm SUMPRODUCT có thể giúp bạn tổng hợp dữ liệu rất hiệu quả phục vụ cho việc làm báo cáo trên Excel, đặc biệt là báo cáo quản trị. Hãy cùng chúng mình tìm hiểu nhé.

Có nhiều hàm trong Excel có thể dùng để tổng hợp dữ liệu như SUMPRODUCT, COUNTIF, COUNTIFS, SUMIF, SUMIFS,… Trong bài viết này, chúng ta sẽ tìm hiểu cách dùng hàm SUMPRODUCT cho việc tổng hợp dữ liệu báo cáo qua ví dụ cụ thể.

Hàm SUMPRODUCT là gì?

Định nghĩa và cấu trúc hàm SUMPRODUCT

Hàm SUMPRODUCT là hàm xử lý công thức dạng mảng, không yêu cầu kết thúc hàm với tổ hợp phím Ctrl + Shift + Enter. Có rất nhiều người nhầm tưởng rằng SUMPRODUCT có nghĩa là tính tổng các sản phẩm. Điều này là không chính xác, PRODUCT trong Excel có nghĩa là tích, do đó SUMPRODUCT có nghĩa là tính tổng các tích theo các mảng (hay còn gọi array). 

Cấu trúc của hàm SUMPRODUCT có dạng như sau:

=SUMPRODUCT(array1, [array2], [array3], …)

Trong đó bao gồm các thành phần là:

• array1: là mảng thứ 1. Tối thiểu hàm này phải có 1 mảng
• array2, 3, ...: là mảng thứ 2. 

Chúng ta có thể thêm vào công thức nhiều hơn một mảng. Đối với các phiên bản từ Excel 2007 trở lên thì các bạn có thể thêm tối đa 255 mảng, các phiên bản cũ hơn thì chỉ cho phép tối đa 30 mảng.

Xem thêm: CÁCH SỬ DỤNG HÀM TIME VÀ HÀM SUMPRODUCT NÂNG CAO TRONG EXCEL

Ví dụ về hàm SUMPRODUCT

Giả sử chúng ta có một bảng tính gồm đơn giá và số lượng của từng loại mặt hàng như sau:

Thông thường, nếu tính tổng thành tiền thì rất nhiều người sẽ sử dụng cách lấy đơn giá của từng sản phẩm thực hiện phép nhân với số lượng và cuối cùng là cộng tổng tiền. Cách làm này không sai nhưng lại tương đối mất thời gian.

Thay vào đó, chúng ta có thể sử dụng hàm SUMPRODUCT với công thức cụ thể như sau:

=SUMPRODUCT(F16:F19,G16:G19)

Trong đó vùng F16:F19 là mảng 1 - vùng chứa dữ liệu về số lượng; vùng G16:G19 là mảng 2 - chứa dữ liệu về đơn giá. Công thức này sẽ lấy lần lượt các giá trị của mảng 1, nhân với giá trị tương ứng ở mảng 2 rồi sau đó cộng tổng tất cả các tích đã nhân vào với nhau. Thay vì phải tính toán làm 2 lần như cách truyền thống thì chúng ta chỉ cần dùng một hàm là đã tính ra được kết quả.

Xem thêm: Cách tính đơn giá trong Excel bằng hàm VLOOKUP chuẩn

Cách dùng hàm SUMPRODUCT nhiều điều kiện

Excel không có hàm SUMPRODUCTIF để chúng ta có thể tính tổng của tích có điều kiện. Như vậy, nếu muốn dùng hàm SUMPRODUCT thì chúng ta sẽ tạo ra điều kiện. 

Ví dụ: Cho bảng tính sau đây với các yêu cầu như sau:

Yêu cầu 1: Tính tổng thành tiền của riêng sản phẩm A.

Như vậy chúng ta phải viết hàm sao cho cứ gặp giá trị A thì Excel sẽ nhân đơn giá với số lượng tương ứng. Nếu gặp các giá trị khác thì sẽ nhân đơn giá với 0. Công thức cụ thể mà chúng mình sử dụng cho trường hợp này là:

=SUMPRODUCT(F31:F34*G31:G34*(E31:E34="A"))

hoặc

=SUMPRODUCT((E31:E34="A")*(F31:F34)*(G31:G34))

Kết quả mà chúng ta tính ra được là 164.000.

Yêu cầu 2: Tính tổng thành tiền của mặt hàng có số lượng ≥5

Khi đó công thức mà chúng ta sử dụng cần phải được lồng điều kiện sao cho cứ gặp giá trị có số lượng <5 thì sẽ nhân đơn giá với 0; ngược lại thì nhân đơn giá với số lượng tương ứng. Công thức cụ thể mà chúng mình sử dụng trong trường hợp này là:

=SUMPRODUCT(--(F31:F34>=5),F31:F34,G31:G34)

hoặc

=SUMPRODUCT((F31:F34>=5)*(F31:F34)*(G31:G34))

Xem thêm: Ứng dụng hàm SUMPRODUCT trong kế toán

Lưu ý khi sử dụng hàm SUMPRODUCT trong báo cáo

Khi sử dụng hàm SUMPRODUCT để tổng hợp dữ liệu thì các bạn hãy lưu ý một số điều sau đây:

1. Điều kiện được viết trực tiếp vào mảng dưới dạng logic, so sánh.
2. Các điều kiện logic, so sánh (A=B, A>B, A<B…) thì trả về kết quả TRUE/FALSE nếu đối tượng so sánh là dạng Text. Nếu muốn chuyển đổi định dạng sang số để tính toán được thì nhân với 1 hoặc viết thêm 2 dấu - trước mảng đó. Lúc này kết quả TRUE = 1 và FALSE = 0.
3. Các điều kiện logic, so sánh (A=B, A>B, A<B…) thì trả về kết quả 1 hoặc 0 nếu đối tượng so sánh là dạng Number.
4. Cả 2 cách viết dạng mảng 1, mảng 2… hoặc chỉ có 1 mảng nhưng gồm nhiều mảng nhỏ nhân với nhau đều đúng.
5. Có thể sử dụng các hàm dưới dạng công thức mảng khi đặt trong hàm SUMPRODUCT mà không cần kết thúc công thức dưới dạng { }.

Hàm SUMPRODUCT mang lại nhiều lợi ích cho chúng ta khi tính toán, đồng thời có thể thay thế các hàm SUMIF và COUNTIF trong hầu hết các trường hợp. Tuy nhiên nó có nhược điểm là tính toán chậm hơn và dễ xảy ra lỗi sai nếu người dùng không thành thạo Excel.

Kết luận

Qua bài viết này, các bạn đã biết thêm một cách để tổng hợp dữ liệu khi lập báo cáo. Các bạn muốn thực hành có thể tải file đính kèm bài viết về để làm thử nhé.

Nếu các bạn muốn được học kiến thức như trong bài viết này nhưng bài bản, chuyên sâu hơn với sự hướng dẫn tỉ mỉ thì hãy đăng ký ngay khóa học:

Hướng dẫn cách lập và phân tích hệ thống báo cáo quản trị trên Excel

Khóa học phù hợp với các bạn làm công việc liên quan đến tổng hợp, xử lý và phân tích dữ liệu để lập báo cáo quản trị doanh nghiệp. Hoàn thành chương trình học bao gồm cả lý thuyết và thực hành, các bạn sẽ nắm được những điểm mấu chốt để xây dựng báo cáo quản trị cho doanh nghiệp. Điều này sẽ giúp cho công việc của bạn đạt hiệu quả cao hơn và có nhiều cơ hội thăng tiến hoặc phát triển mới trong công việc được mở ra. Chúc các bạn thành công!