Hàm SUMPRODUCT trong Excel là hàm trả về tổng của các tích của các phần tử số trong các mảng. Hay nói một cách đơn giản hơn, hàm SUMPRODUCT là sự kết hợp của hai hàm SUM-sử dụng để tính tổng và hàm PRODUCT- sử dụng để tính tích. Hàm SUMPRODUCT sẽ phân tích và cộng chúng lại. Nếu chỉ nghe qua, thì bạn chỉ đơn giản hiểu nó dùng để tính tổng. Nhưng tính năng của nó còn có nhiều hơn thế, hàm SUMPRODUCT có thể thay thế cho các hàm khác trong excel như hàm SUM, COUNTIF COUNTIFS, SUMIF, SUMIFS… Bài viết này mình sẽ chia sẻ đến các bạn cách sử dụng cơ bản và nâng cao của hàm SUMPRODUCT cùng với các ví dụ cụ thể, các bạn cùng theo dõi nhé.
Xem Nhanh Mục Lục
KHÁI QUÁT VỀ HÀM SUMPRODUCT TRONG EXCEL
Hàm SUMPRODUCT cũng là một hàm được sư dụng khá nhiều trong công việc. Chính vì thế mà nó cũng được áp dụng cho hầu hết các phiên bản excel, bao gồm cả trên hệ điều hành của iPhone hay Android. Nên bạn hoàn toàn có thể yên tâm sử dụng nó, mà không lo không được hỗ trợ.
KHÁI NIỆM, CÔNG THỨC HÀM SUMPRODUCT TRONG EXCEL
Hiểu đơn giản hàm sumproduct là một hàm excel cho phép nhân các số trong các mảng( hay vùng) sau đó cộng các kết quả của các tích này để cho ra một kết quả cuối cùng.
Cú pháp: =SUMPROUCT(array1, [array2], [array3],…)
Trong đó:
- array1 : Là tham số bắt buộc, đây là đối số mảng đầu tiên mà bạn muốn nhân các thành phần của nó rồi cộng tổng lại với nhau.
- [array2], [array3],… Là tham số tùy chọn. Từ 2 trở đi (số mảng tối đa có thể lên tới 255) là các mảng mà bạn muốn nhân các thành phần của nó rồi cộng tổng lại.
LƯU Ý:
- Tất cả các mảng trong hàm SUMPRODUCT phải có cùng số hàng, số cột. Nếu không cùng, thì hàm SUMPRODUCT sẽ trả về giá trị lỗi #VALUE!
- Nếu có bất kỳ ô dữ liệu nào trong mảng không ở dạng số,thì hàm SUMPRODUCT sẽ mặc định coi nó là số 0.
- Số mảng tối đa với excel 2003 là 30 mảng, còn lại số mảng tối đa là 255 trong Excel 2007, 2010, 2013, 2016.
- Hàm SUMPRODUCT không hỗ trợ ký tự đại diện( WildCard)
- Nếu mảng là phép thử lô gic, thì hàm sẽ trả về giá trị TRUE hoặc FALSE.
CÁCH DÙNG HÀM SUMPRODUCT CƠ BẢN
Để giúp các bạn có cái nhìn khái quát về cách sử dụng, các bạn hãy tham khảo ví dụ sau đây:
Ví dụ 1: Cho bảng số lượng một số loại nước uống, với đơn giá tương ứng. Hãy tính tổng tiền số nước uống đó.
Nếu như không biết đến hàm SUMPRODUCT, các bạn sẽ ngay đến việc đặt công thức nhân cột đơn giá với cột số lượng với nhau. Sau đó dùng hàm SUM để tính tổng. Cách làm này không hề sai, nhưng nếu dữ liệu là nhiều mảng không phải hai mảng như ví dụ này, thì nó lại tiêu tốn đi rất nhiều thời gian của bạn. Từ nay, các bạn có thể áp dụng SUMPRODUCT để thay thế cho cách làm thông thường.
Cách làm khá đơn giản, tại ô A7 đặt công thức = SUMPRODUCT(C2:C5,D2:D5). Trong đó, C2:C5 là mảng đơn giá, D2:D5 là mảng số lượng.
Giải thích công thức:
Công thức chọn số đầu tiên trong mảng đơn giá rồi nhân nó cho số đầu tiên trong mảng số lượng, rồi lấy số thứ hai trong mảng đơn giá nhân cho số thứ hai trong mảng số lượng, công thức cứ tiếp tục đến hết. Biễu diễn công thức có thể hiểu như sau.
Total = C2*D2+C3*D3+ C4*D4+C5*D5
Sau khi đã nhân các thành phần của mảng, sẽ tính tổng của chúng.
Enter và chúng ta có kết quả như bên dưới nhé.
CÁCH SỬ DỤNG HÀM SUMPRODUCT NÂNG CAO TRONG EXCEL
HÀM SUMPRODUCT KẾT HỢP VỚI CÁC HÀM KHÁC
Ví dụ 2: Cho bảng số liệu. Hãy tính tổng tiền hàng được bán trong tháng 3.
Với ví dụ ở trên, để tính được theo yêu cầu của đề bài, chúng ta cần tạo thêm một cột tháng bán dựa vào cột ngày bán để tách.
Theo cách tính thông thường:
- Đầu tiên dùng hàm IF để xét nếu tháng của từng ngày bán có phải tháng 3 hay không. Nếu đúng thì sẽ lấy số lượng nhân với đơn giá tương ứng, còn không phải là tháng 3 thì trả về kết quả là 0.
- Sau khi có kết quả thành tiền từng mặt hàng đã tính trên, sẽ dùng hàm SUM để cộng tổng lại.
Cách làm này sẽ khiến bạn phải sử dụng nhiều công thức, và mất thời gian. Đừng lo, hàm SUMPRODUCT sẽ giúp bạn giải quyết hết những vấn đề đó chỉ trong một công thức duy nhất lồng ghép với hàm MONTH.
Ta có công thức: =SUMPRODUCT((MONTH((B2:B5)=3)*(C2*C5)*(D2:D5))
Trong đó:
- Dùng hàm MONTH(B2:B5), mảng B2:B5 sẽ được tách lấy giá trị tháng, sau đó so sánh với số 3.
- Mảng đơn giá C2: C5
- Mảng D2: D5
- Nhân 3 mảng này với nhau, ta có đơn giá cần tìm.
HÀM SUMPRODUCT TÍNH CHO DỮ LIỆU HAI CHIỀU
Ví dụ 3: Cho bảng số liệu như bên dưới. Tìm đơn giá của các sản phẩm dựa vào mã hàng và mã công ty.
Yêu cầu của đề bài xác định đơn giá dựa vào hai điều kiện là mã hàng và mã công ty.
Trong đó bảng chứa đơn giá mà ta đang cần tìm là bảng hai chiều.
- Chiều ngang là trường mã hàng
- Chiều dọc là trường công ty
- Giao của hai điều kiện này là đơn giá mà ta cần tìm.
Sử dụng hàm SUMPRODUCT ta có công thức tại ô G3: = SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)
Trong đó:
- $B$14:$B$17 là mảng mã công ty B14:B17, nhấn F4 để cố định dữ liệu, xét xem có chứa mã công ty ở ô B3 hay không?
- $C$13:$F$13 tương tự là mảng mã hàng, nhấn F4 để cố định dữ liệu, xét có chứa mã hàng ở ô C3 hay không?
- Nếu trùng khớp ở cả hai mảng điều kiện trên, thì đơn giá sẽ được lấy tương ứng trong vùng đơn giá C14:C17.
Sau đó nhấn enter và kéo công thức cho các ô còn lại là các bạn đã hoàn thành yêu cầu rồi đó.
Hàm SUMPRODUCT là hàm có rất nhiều tính năng hay, một trong những chức năng nâng cao hơn nữa là tính tổng có nhiều điều kiện. Chắc hẳn khi làm việc với Excel thông thường chúng ta không chỉ có một điều kiện duy nhất, vậy với nhiều hơn một thì hàm SUMPRODUCT sẽ sử dụng như thế nào. Các bạn hãy tham khảo bài viết bên dưới nhé.
Xem thêm: Cách sử dụng hàm sumproduct tính tổng nhiều điều kiện
Tổng kết
Vậy là mình đã kết thúc phần hướng dẫn cách sử dụng hàm SUMPRODUCT trong Excel rồi. Cũng khá dễ phải không nào. Hàm nãy rất hữu ích cho các bạn làm việc tại các phòng mua hàng, phòng kinh doanh, phòng kế toán. Ngoài hàm SUMPRODUCT để tính tổng https://updatemoi.com sẽ giới thiệu tới các bạn nhiều hàm khác cũng có chức năng tương tự, cách sử dụng của chúng trong các trường hợp cụ thể. Chúc các bạn thành công!
