Hàm Sumproduct có rất nhiều tính năng. Do hàm Sumproduct khả năng đặc biệt có thể xử lý mảng một cách tinh tế và thông minh, nên hàm Sumproduct rất hữu ích, khi nói đến việc so sánh dữ liệu ở hai hay nhiều dải ô và việc tính toán có nhiều điều kiện. Bài viết dưới đây sẽ hướng dẫn bạn cách dùng hàm Sumproduct trong Excel để tính tổng có nhiều điều kiện.
Hàm Sumproduct trong Excel – Công thức và chức năng:
Về mặt kỹ thuật, hàm SUMPRODUCT trong Excel là nhân các con số trong mảng xác định, rồi trả về kết quả tổng của các tích số đó.
Công thức của hàm SUMPRODUCT rất đơn giản:
=SUMPRODUCT(array1, [array2], [array3], …)
Trong đó, array 1, array 2, … là các dải ô liên tục hay là các mảng có chứa thành phần bạn muốn nhân, rồi cộng lại.
Số mảng tối thiểu là 1. Trong trường hợp này, hàm SUMPRODUCT chỉ đơn giản là tính tổng các thành phần mảng rồi trả kết quả về tổng số.
Số mảng tối đa là 255 trong phiên bản Excel 2016, Excel 2013, Excel 2010, và Excel 2007, và 30 trong các phiên bản trước của Excel.
Mặc dù hàm SUMPRODUCT xử lý mảng, nhưng nó không yêu cầu phím tắt mảng (Ctrl + Shift + Enter).
Lưu ý:
Tất cả mảng trong công thức SUMPRODUCT phải có cùng một số hàng và một số cột , nếu không thì bạn sẽ nhận được lỗi #VALUE!.
Nếu có bất cứ câu lệnh mảng nào chứa giá trị phi số, chúng sẽ được xử lý y như số 0.
Nếu mảng nào đó là phép thử logic, thì nó sẽ trả về các giá trị TRUE và FALSE. Thông thường, bạn cần phải đổi các giá trị TRUE và FALSE này thành giá trị 1 và 0 bằng cách sử dụng toán tử đơn phân (–). Bạn hãy xem qua ví dụ hàm SUMPRODUCT có nhiều điều kiện để hiểu rõ hơn.
Hàm SUMPRODUCT sẽ không được hỗ trợ ký tự đại diện.
Hướng dẫn cách dùng cơ bản của hàm Sumproduct trong Excel:
Để bạn có cái nhìn tổng quát về cách hàm SUMPRODUCT hoạt động, hãy xem qua ví dụ sau đây:
Giả sử bạn có số lượng ở dải ô A2:A4, và bạn muốn tính tổng nó. Nếu bạn đang phải giải một phép toán ở trường, thì bạn sẽ phải nhân số lượng với giá tiền của mỗi sản phẩm, rồi cộng chúng lại với nhau là ra kết quả. Trong Microsoft Excel, bạn hoàn toàn có thể nhận được kết quả chỉ với một công thức SUMPRODUCT:
=SUMPRODUCT(A2:A4,B2:B4)
Hình ảnh dưới đây cho thấy công thức trên thực tế:
Công thức chọn số đầu tiên ở trong mảng đầu tiên rồi nhân nó cho số đầu tiên ở trong mảng thứ hai, rồi làm tương tự như thế với các số tiếp theo ở trong mảng tiếp theo
Khi bạn đã nhân tất các thành phần mảng, công thức sẽ tính tổng các tích số rồi trả về tổng số là kết quả cần tìm.
Nói cách khác, công thức hàm SUMPRODUCT biểu diễn công thức toán học sau đây:
=A2*B2 + A3*B3 + A4*B4
Hãy nghĩ đến việc bạn hoàn toàn có thể tiết kiệm bao nhiêu thời gian nếu bảng của bạn không chỉ chứa 3 hàng dữ liệu, mà sẽ là 3 trăm hay 3 ngàn hàng dữ liệu!
Hướng dẫn cách sử dụng hàm Sumproduct trong Excel – Ví dụ công thức:
Khi bạn nhân hai hay nhiều dải ô với nhau rồi cộng các tích số đó để ra kết quả là cách dùng đơn giản và rõ ràng nhất của hàm SUBTOTAL trong Excel, nhưng đây không phải là cách dùng duy nhất trong excel. Vẻ đẹp thực sự của hàm SUMPRODUCT trong Excel đó là nó hoàn toàn có thể làm nhiều hơn các tính năng đã được nêu rõ.
Hàm Sumproduct có nhiều điều kiện
Thông thường trong Microsoft Excel, luôn có nhiều hơn một cách để giúp bạn hoàn thành công việc. Nhưng khi nói đến việc thực hiện so sánh hai hay nhiều mảng, đặc biệt là với công việc có nhiều điều kiện, nếu không nói là duy nhất, thì hàm SUMPRODUCT là hàm hiệu quả nhất trong excel.
Giả sử bạn đang có danh sách các món hàng ở cột A, doanh số bán theo dự kiến ở cột B, và doanh số thực bán được ở cột C. Mục tiêu của bạn phải thực hiện là tìm xem có bao nhiêu món bán ít hơn dự kiến bán. Đối với trường hợp này, bạn hãy sử dụng một trong những biến phân của công thức hàm SUMPRODUCT:
=SUMPRODUCT(–(C2:C10<B2:B10))
hay
=SUMPRODUCT((C2:C10<B2:B10)*1)
Trong đó dải ô C2:C10 là doanh số thực và dải ô B2:B10 là doanh số dự kiến.
Giả sử bạn muốn đếm số lần Táo bán được ít hơn so với dự kiến. Giải pháp thực hiện đó là thêm một điều kiện nữa vào công thức của hàm SUMPRODUCT:
=SUMPRODUCT(–(C2:C10<B2:B10), –(A2:A10=”táo”))
Hay, bạn hoàn toàn có thể sử dụng cú pháp sau:
=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))
Hướng dẫn cách công thức Sumproduct có một điều kiện hoạt động:
Đối với người mới bắt đầu, bạn hãy chia nhỏ công thức thành các phần đơn giản hơn – công thức so sánh các con số trong hai cột hàng theo hàng, và nói cho bạn biết số lần cột C ít hơn cột B:
=SUMPRODUCT(–(C2:C10<B2:B10))
Nếu bạn chọn phần (C2:C10<B2:B10) trong thanh công thức, và nhấn F9 để xem giá trị ẩn, thì bạn sẽ thấy công thức sau:
Cái bạn có ở đây đó là giá trị hàm BOOLEAN TRUE và FALSE, trong đó, giá trị TRUE có nghĩa là đã đáp ứng điều kiện xác định, và giá trị FALSE cho thấy điều kiện vẫn chưa được đáp ứng.
Hai dấu trừ (–), về mặt kỹ thuật được gọi là hai toán tử đơn phân, nó ép buộc giá trị TRUE và FALSE thành 1 và 0: {0;1;0;0;1;0;1;0;0}.
Cách khác để đổi giá trị logic thành giá trị số đó là nhân mảng với 1:
=SUMPRODUCT((C2:C10<B2:B10)*1)
Cách nào cũng được, vì chỉ có một mảng trong công thức SUMPRODUCT, bạn nên nó chỉ nhân 1 trong mảng cho kết quả và bạn nhận được kết quả đếm mong muốn.
Hướng dẫn cách công thức Sumproduct có nhiều điều kiện hoạt động:
Khi công thức SUMPRODUCT trong Excel chứa hai hay nhiều mảng, nó sẽ nhân các thành phần của mảng, rồi cộng các tích số với nhau.
Có lẽ bạn vẫn còn nhớ, bạn đã sử dụng công thức dưới đây để đếm số lần doanh số thực (cột C) ít hơn doanh số dự kiến (cột B) đối với cột Táo (cột A):
=SUMPRODUCT(–(C2:C10<B2:B10), –(A2:A10=”táo”))
hay
=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))
Điểm khác biệt duy nhất về mặt kỹ thuật giữa hai công thức đó chính là phương pháp ép buộc giá trị TRUE và FALSE thành giá trị 1 và 0 – bằng cách sử dụng hai toán tử đơn phân hay phép nhân. Kết quả là, bạn nhận được hai mảng 1 và 0:
Phép nhân được thực hiện bởi hàm SUMPRODUCT kết hợp chúng thành một mảng riêng. Và vì nhân với giá trị 0 thì bằng 0, nên giá trị 1 chỉ xuất hiện khi cả hai điều kiện được đáp ứng, và do đó chỉ những hàng đó mới được đếm trên bảng dữ liệu:
