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à 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à:
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
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
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
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:
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.
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!
Tài liệu kèm theo bài viết
Khóa học phù hợp với bất kỳ ai đang muốn tìm hiểu lại Excel từ con số 0. Giáo án được Gitiho cùng giảng viên thiết kế phù hợp với công việc thực tế tại doanh nghiệp, bài tập thực hành xuyên suốt khóa kèm đáp án và hướng dẫn giải chi tiết. Tham khảo ngay bên dưới!