Hàm SUMPRODUCT là một trong những hàm được sử dụng khá phổ biến hiện nay bởi sự hữu ích mà nó mang lại. Tuy nhiên đối với những bạn mới tiếp xúc với Excel hàm SUMPRODUCT có vẻ chỉ là công thức tính tổng thông thường của các tích số. Đây chỉ là một tính năng nhỏ của hàm này, những ví dụ dưới đây Azgad Edu sẽ cho bạn thấy được sự linh hoạt của hàm tính tổng của tích này trong Excel.
Hàm SUMPRODUCT trong Excel
Công thức
= SUMPRODUCT (Mảng1, Mảng2, Mảng3 …)
Trong đó:
- Array1 (mảng 1): Đây là đối số mảng đầu tiên muốn nhân rồi tính tổng. Đối số này bắt buộc phải có.
- Array2, Array3,… (mảng 2, mảng 3,…): Đây là đối số mảng tự chọn. Đối số này không bắt buộc phải có và có phạm vi từ 2 đến 255.
Lưu ý:
- Số mảng có trong công thức tối thiểu là 1. Lúc này Excel sẽ trả về tổng giá trị của các thành phần có trong mảng.
- Các phiên bản Excel từ 2007 trở xuống có số mảng tối đa là 30 mảng. Các phiên bản Excel từ 2007 trở lên có số mạng tối đa là 255 mảng
Ứng dụng
- Hàm SUMPRODUCT là hàm kết hợp giữa hàm tính tổng SUM và hàm tính nhân PRODUCT vì vậy kết quả sẽ trả về tổng các tích của 1 dãy số cho trước trong Excel.
- Hàm SUMPRODUCT thường được ứng dụng trong tính tổng có điều kiện của các tích như: tính doanh thu các mặt hàng,…
Hướng dẫn chi tiết cách sử dụng hàm SUMPRODUCT
Để giúp bạn có cái nhìn tổng quát về cách sử dụng hàm SUMPRODUCT trong Excel, Azgad Edu sử dụng các ví dụ minh hoạ dưới đây để giúp bạn hiểu rõ hơn về hàm tính tổng của tích này.
Sử dụng hàm SUMPRODUCT để tính tổng của tích các giá trị
Trong trường hợp này ta sử dụng SUMPRODUCT để tính tổng thông thường của các tích trong các giá trị cho trước.
Ví dụ 1: Tính tổng thành tiền của các hàng hóa trong bảng danh sách mua hàng sau:
Tính tổng thành tiền của các sản phẩm
Để tính được tổng thành tiền của các hàng hoá trên, ta nhập công thức sau và nhấn Enter:
= SUMPRODUCT (D3: D10, E3: E10)
Tính tổng thành tiền của các sản phẩm
Ý nghĩa: Lúc này hàm SUMPRODUCT sẽ tiến hành tính tích của các đối số trong 2 mảng trên sau đó tính tổng các giá trị vừa tính lại. Và chúng ta nhận được kết quả tổng thành tiền của các hàng hóa trên là 1,939,000.
Ví dụ 2: Sử dụng bản dữ liệu trên, giả sử đơn hàng có số lượng trên 10 sẽ được chiết khấu 20%, số lượng từ 5 đến 10 chiết khấu 10% và dưới 5 thì không có chiết khấu. Tính tổng số tiền được chiết khấu trong trường hợp trên.
Để giải quyết được bài toán này, cách làm hoàn toàn tương tự như ví dụ 1.
Nhập công thức sau =SUMPRODUCT(D3:D10,E3:E10,F3:F10), nhận được kết quả:
Tổng số tiền chiết khấu
Lưu ý:
Những ô chứa dữ liệu trống hoặc chứa dữ liệu dạng text công thức SUMPRODUCT sẽ coi là số 0 khi thực hiện phép tính.
Hàm SUMPRODUCT tính tổng với 1 điều kiện
Đối với trường hợp này, ta sử dụng SUMPRODUCT để tính tổng của tích các giá trị thoả mãn 1 điều kiện cho trước.
Ví dụ 3: Sử dụng ví dụ trên để tính tổng thành tiền những hàng hóa loại 1 trong bảng danh mục mua hàng.
Ta nhập công thức sau: =SUMPRODUCT((C3:C10=”I”)*D3:D10*E3:E10)
Tính tổng thành tiền của hàng hoá loại I
Ý nghĩa: Trong cột loại hàng hoá (C3:C10) lọc những hàng hoá loại I sau đó tiến hành tính tổng của tích các đối số trên.
Hàm SUMPRODUCT tính tổng với nhiều điều kiện
Trong trường hợp này ta sử dụng hàm SUMPRODUCT để tính tổng tích của những giá trị thỏa mãn các điều kiện cho trước.
Ví dụ 4: Cho bảng thống kê danh sách mua hàng dưới đây, tính tổng thành tiền của các vật tư là xăng và có số lượng lớn hơn 50.
Tính tổng thành tiền của các vật tư là xăng và có số lượng lớn hơn 50.
Tại ô F13 ta nhập công thức sau:
= SUMPRODUCT ((C3: C12 = “xéc măng”) * (D3: D12> 50) * F3: F12)
Tính tổng thành tiền của các vật tư là xăng và có số lượng lớn hơn 50.
Ý nghĩa: Trong cột vật tư (C3:C12) tìm những vật tư là xăng, trong những hàng hoá là xăng này tiếp tục tìm những vật tư có số lượng (D3:D12) lớn hơn 50 và tiến hành tính tổng thành tiền (ở cột F3:F12) của các vật tư vừa tìm được.
Hàm SUMPRODUCT kết hợp với hàm OR trong Excel
Cách dùng này bạn có thể áp dụng để tính tổng giá trị sao cho giá trị đó thỏa mãn nhiều điều kiện cho trước.
Ví dụ 5: Sử dụng bảng dữ liệu ở ví dụ 4, tính tổng thành tiền của những vật tư là xăng hoặc ga. Lúc này ta kết hợp công thức SUMPRODUCT với hàm OR để giải quyết vấn đề.
Tại ô F13: =SUMPRODUCT(OR(C3:C12=”XĂNG”,C3:C12=”GA”)*F3:F12)
Tính tổng thành tiền của những vật tư là xăng hoặc ga
Ý nghĩa: Lúc này Excel sẽ lọc ra những vật tư là xăng hoặc ga ở cột (C3:C12) và có số lượng lớn hơn 50 (ở cột D3:D12) sau đó tiến hành tính tổng thành tiền (ở cột F3:F12) của các vật tư vừa tìm được.
Một số lưu ý giúp sử dụng hàm SUMPRODUCT hiệu quả
- Khi nhập dữ liệu mảng cho công thức SUMPRODUCT bạn cần phải chú ý kích cỡ các mảng phải bằng nhau (cùng số hàng/ số cột), nếu không Excel sẽ trả về kết quả lỗi #VALUE!
- Những ô chứa dữ liệu trống, hàm logic, hoặc chữ sẽ bị loại bỏ qua, không thể thực hiện phép toán (tương đương với việc Excel sẽ xem giá trị này bằng 0).
Như vậy qua những ví dụ minh hoạ trên Azgad Edu đã hướng dẫn bạn sử dụng hàm SUMPRODUCT một cách chi tiết nhất. Hãy áp dụng thật hiệu quả hàm tính tổng có điều kiện này vào công việc của mình nhé!
>>> Xem thêm: Khóa học tin học văn phòng từ cơ bản đến nâng cao dành cho người mới Học Excel cơ bản – Làm quen những hàm căn bản
