Excel được biết đến là trợ thủ đắc lực cho các công việc, học tập liên quan đến tính toán. Với sự nhanh nhẹn của mình “thần đồng” Excel có thể cho ra kết quả chính xác chỉ trong vài thao tác. Chỉ cần sử dụng thông thạo các hàm tính bạn sẽ đạt hiệu quả công việc cao hơn. Bên cạnh hàm COUNT, IF, các hàm LOOKUP, SUMPRODUCT cũng là một trong những hàm cơ bản hỗ trợ rất nhiều cho người dùng.
Nếu như bạn đang thắc mắc:
- Hàm Sumproduct là gì?
- Cách sử dụng hàm Sumproduct trong Excel kèm ví dụ cụ thể?
- Hàm Sumproduct kết hợp với hàm đếm Countif có được không?
Trong bài viết này, sửa máy tính uy tín Đà Nẵng sẽ hướng dẫn chi tiết cách dùng hàm SUMPRODUCT cực đơn giản, đừng bỏ qua nhé!
Nội dụng mục lục
I. Hàm SUMPRODUCT là gì?
Thông thường khi muốn tính tổng hoặc tính tổng có điều kiện, người dùng hay sử dụng hàm SUM và SUMIF nhưng vẫn chưa biết SUMPRODUCT cũng có thể vừa tính tổng có điều kiện, vừa đếm, vừa kết hợp với nhiều hàm khác để mở rộng chức năng cực hữu ích.
Hàm SUMPRODUCT là sự kết hợp của hàm tính tổng cơ bản SUM và hàm tính tích PRODUCT, cho ra hàm tính tổng từ các tích trong phạm vi mảng dữ liệu. Hiểu đơn giản hơn tức hàm sẽ nhân các mảng dữ liệu với nhau và cộng các kết quả đó lại.
Hàm SUMPRODUCT thường được dùng thông dụng nhất ở các bảng tính đơn hàng hoặc hoá đơn siêu thị, tạp hoá…kết hợp cùng lúc 2 phép tích giúp tiến độ được rút ngắn, hiệu quả công việc tăng cao.
II. Cách dùng hàm SUMPRODUCT kèm ví dụ minh hoa
1. Công thức
=SUMPRODUCT(array1, [array2], [array3], …)
Hàm Sumproduct trong Excel
Trong đó:
- Array1: đây là mảng bắt buộc chứa thành phần muốn nhân
- Array 2, array 3: các mảng dữ liệu tiếp theo chứa thành phần muốn nhân
Hàm sẽ cộng các tích của mảng dữ liệu được chọn và cho ra kết quả. Số lượng ô dữ liệu tối thiểu là 1 và tối đa là 255.
Lưu ý:
- Các ô, mảng dữ liệu trong hàm phải cùng hàng và cột, nếu không kết quả sẽ trả về bị lỗi #VALUE!
- Hàm Sumproduct tính tổng các tính các dữ liệu có giá trị số, các mảng dữ liệu không phải là số, hàm sẽ tự động bỏ qua và tính về giá trị 0.
2. Các ví dụ minh hoạ
Dùng hàm SUMPRODUCT cơ bản
Muốn tính tổng tiền cần phải thanh toán cho hoá đơn dưới đây, thông thường chúng ta sẽ phải sử dụng hàm PRODUCT để nhân đơn giá và số lượng sản phẩm để tính tổng tiền mỗi sản phẩm. Sau đó mới tiếp tục dùng hàm SUM để cộng các tổng đó lại lần nữa.
Hàm Product tính tổng tiền từng sản phẩm
Tính tổng của giá các sản phẩm
Tuy cách làm này không sai nhưng sẽ mất thời gian, thay vì vậy ta hãy dùng hàm SUMPRODUCT bằng cách nhập: =SUMPRODUCT(C3:C8,D3:D8)
Hàm Sumproduct kết hợp 2 hàm thành 1 thao tác
Trong đó:
- C3:C8: là mảng dữ liệu 1, chứa các thành phần cần nhân
- D3:D8: là mảng dữ liệu 2, chứa các thành phần nhân với mảng 1
Chỉ cần nhấn Enter, hàm sẽ cộng các tích đã nhân lại và cho ra kết quả sau
Cách dùng hàm Sumproduct trong Excel
Dùng hàm SUMPRODUCT có điều kiện
Muốn tính tổng doanh thu của mì Modern ở các chi nhánh, ta nhập công thức: =SUMPRODUCT((B3:B9=”Mì Modern”)*D3:D9)
Dùng hàm Sumproduct tính tổng có điều kiện
Trong đó:
- B3:B9: Cột giá trị cần áp điều kiện, ở đây ta cần tính doanh thu của riêng mì Modern, vì vậy cần khoanh vùng cột Sản phẩm
- =”Mì Morden” : điều kiện
- D3:D9: Cột giá trị để tính tích. Ở đây sẽ là cột doanh thu để nhân với sản phẩm đã được lọc trước đó.
Hàm Sumproduct sẽ cho ra kết quả sau:
Hàm cho ra kết quả chỉ sau phím Enter
Dùng hàm PRODUCT tính tổng nhiều điều kiện
Muốn tính tổng doanh thu của chi nhánh Đống Đa đối với các mặt hàng đã bán trên 100, tức có 2 điều kiện, chúng ta cần nhập công thức sau:
=SUMPRODUCT((C3:C15=”Đống Đa”)*(D3:D15>100)*E3:E15)
Hàm Sumproduct tính tổng nhiều điều kiện
Trong đó:
- C3:C15: là mảng giá trị chứa thành phần cần áp điều kiện, vì điều kiện thứ nhất là chỉ tính riêng chi nhánh Đống Đa nên cần chọn vùng dữ liệu Chi nhánh
- =”Đống Đa” : Điều kiện cần thoả mãn
- D3:D15: mảng giá trị chứa thành phần cần áp điều kiện 2, tại đây chỉ tính doanh thu từ các sản phẩm đã bán trên 100 nên ta chọn vùng dữ liẹu của Số lượng bán
- >100: điều kiện cần thoả mãn
- E3:E15: kết quả cần đạt là doanh thu tổng nên cần nhân với doanh thu từng phần.
Hàm sẽ nhân các giá trị lại với nhau và cộng các tích lại, cho ra kết quả sau:
Cộng các tích của những giá trị thoả mãn điều kiện
Dùng hàm PRODUCT kết hợp với COUNTIF
Không chỉ để tính tổng dựa trên tích, hàm SUMPRODUCT còn làm được nhiều chức năng hơn thế. Hàm có thể so sánh các mảng giá trị và đếm để cho ra tổng các giá trị thoả điều kiện.
Chẳng hạn bạn muốn tính tổng các sản phẩm đạt chỉ tiêu bán ra trong bảng sau, hãy sử dụng công thức:
=SUMPRODUCT(–(E4:E9>F4:F9))
So sánh các mảng giá trị để đếm số lượng
Trong đó:
“—“ : đây là hai toán tử đơn phân, giúp biến TRUE thành 1 và FALSE thành 0. True được hiểu là đã đáp ứng điều kiện xác định, (ví dụ cột E ít hơn 1 giá trị ở cột F ở hàng). Còn False sẽ là điều kiện chưa được đáp ứng.
Phân tách trong bảng trên sẽ thấy: E4<F4=0; E5>F5 = 1; E6>F6 = 1 …Chứ như vậy ta sẽ được:
=SUMPRODUCT(–(0,1,1,0,1,1)). Ta thấy kết quả đúng (1) là 4. Vậy kết quả sẽ là:
Hàm sumproduct kết hợp countif
Hoặc bạn cũng có thể thực hiện cách nhân với 1 để so sánh và đổi giá trị logic sang giá trị số: =SUMPRODUCT((E4:E9>F4:F9)*1)
Cách thực hiện khác
Kết quả cũng thành 4 như trên.
III. Tổng kết
Hàm SUMPRODUCT giúp cho các thao tác tính tổng phức tạp nên dễ dàng, nhanh chóng hơn. Đặc biệt hàm còn kết hợp được với nhiều hàm khác để tối ưu tính năng trong Excel. Trên đây là cách sử dụng hàm SUMPRODUCT trong Excel cực đơn giản kèm theo ví dụ cụ thể. Sua may tinh uy tin Da Nang hi vọng những thông tin này sẽ hữu ích cho công việc, học tập của bạn!
