Cách sử dụng Hàm sumproduct trong excel với nhiều điều kiện nâng cao cùng 11 ví dụ hay (để đếm, để tính tổng,…)

Hơn 1 tỉ năm rồi Trường mới viết bài, nhưng một khi viết thì phải thật chất lượng và có chiều sâu. Minh chứng là một đề tài hot như thế này đã hoàn tất trong 2 ngày Trường miệt mài viết lách.

Các nội dung chính:

– Hiểu Cơ bản về hàm

– Các ví dụ về so sánh mảng

– Tính tổng có điều kiện

– Đếm các ô thỏa mãn nhiều điều kiện

– Và nhiều nội dung hay khác

1. Hàm sumproduct là gì và cú pháp trong excel

1.1. Định nghĩa:

Về cơ bản, hàm sumproduct là một hàm excel cho phép nhân các số trong các mảng (vùng) cụ thể rồi 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ó thể hiểu đơn giả như sau:

Sumproduct = Sum ( Product )

• Product: Tích
• Sum: Tổng

Sumproduct là Tổng của các Tích

Lưu ý:

Trên thực tế, đây là một hàm Khó ở cả 2 cấp độ:

– Khó hiểu để viết hàm

– Khó vận dụng để tính toán trong excel

Nhưng hàm này cực kỳ hiệu quả trong nhiều trường hợp khi bạn biết cách vận dụng.

1.2. Cú pháp hàm

= Sumproduct (Array1, Array2, Array3, …)

Array là mảng.

Lưu ý quan trọng về Array/ Mảng:

– Bạn có thể dùng tới 255 mảng trong excel 2007 trở đi, và 30 mảng đối với excel 2003

– Độ lớn các mảng phải giống nhau, ngược lại Sumproduct sẽ trả về kết quả lỗi #VALUE

– Bất kỳ dữ liệu nào có trong các mảng không phải dữ liệu số, hàm sẽ trả về kết quả là 0

2. Cách sử dụng hàm sumproduct nâng cao/ nhiều điều kiện.

2.1. Ví dụ về cách sử dụng cơ bản của hàm Tính tổng của tích

Như trong hình trên, nếu không biết cách dùng hàm vlookup bạn hoàn toàn có thể tính được tổng doanh thu một cách dễ dàng:

– Thêm cột Doanh thu

– Tính doanh thu cho từng lần bán

– Tính tổng doanh thu ở cuối cùng của danh sách hàng bán.

Nhưng làm như vậy sẽ hơi mất công.

Giải pháp:

Dùng hàm sumproduct để tính tổng của các tích (tích ở đây là số lượng * giá bán)

Cú pháp hàm tại trường hợp này như sau:

= Sumproduct (A2:A5, B2:B5) = Sum (A2*B2, A3*B3, A4*B4, A5*B5) = 135

Trong đó:

• A2:A5: Số lượng
• B2:B5: Giá bán

2.2. Cách sử dụng Hàm sumproduct nâng cao/ nhiều điều kiện

a/ Sử dụng hàm sumproduct để đếm với 1 điều kiện

Trong bảng kê bán hàng ở hình trên, ta có 2 chỉ tiêu là: Số lượng bán dự kiến và số lượng bán thực tế.

Nhiệm vụ của ta là đếm xem có bao nhiêu giao dịch bán hàng có lượng bán thực tế ít hơn dự kiến.

Nếu không biết dùng hàm sumproduct thì bạn sẽ phải thêm cột phụ rồi dùng countif để đếm, hơi mất công cũng như sẽ khó làm khi bảng kê có cả nghìn giao dịch.

Còn với hàm sumproduct thì quá đơn giản như sau:

= Sumproduct ( — (Vùng ô bán dự kiến  > Vùng ô bán thực tế ) = Sumproduct (–(B2:B10>C2:C10)) = 4

Chỉ trong nháy mắt là ra ngay.

b/ Hàm sumproduct với nhiều điều kiện dùng để đếm

Tiếp nối phần a của phần ứng dụng nâng cao hàm sumproduct. Ta thêm 1 điều kiện nữa khi đếm đó là:

• Đếm số giao dịch của mặt hàng A thỏa mãn điều kiện Số lượng bán thực tế < dự kiến.

Thực sự là khó nhằn hơn rất nhiều,

Ta vận dụng hàm như sau:

= Sumproduct ((A2:A10 = “A”)*(B2:B10<C2:C10)) = 2

Dấu * sẽ giúp kết nối các điều kiện với nhau.

Đây là cách vận dụng rất phổ biến của hàm tính tổng của tích Sumproduct trong thực tế công việc.

3. 11 ví dụ hay về hàm sumproduct

Trên thực tế, khi bạn đi sâu vào công việc và nghiên cứu bạn sẽ còn thấy rất nhiều cách vận dụng khác của hàm này.

Đơn cử, như file file bạn có thể download dưới đây.

Trường xin trích dẫn lại 11 ví dụ hay về hàm sumproduct của một website chuyên về excel của nước ngoài. Các bạn hãy download về tự nghiên cứu và phát triển thêm.

Lưu ý: File này là file toàn là tiếng anh nên các bạn chịu khó học hỏi nhé.

Click để download.

Ngoài ra, Còn có 3 ví dụ ở phần 2 nhé các bạn.

Như vậy, Trường đã hướng dẫn xong các bạn cách sử dụng hàm Sumproduct trong excel.

Rất hi vọng rằng, những chia sẻ của Trường sẽ giúp ích nhiều cho các bạn.

Nếu gặp bất kỳ khó khăn nào bạn hãy email cho Trường tại hòm mail: Webkynang.vn@gmail.com

KHÓA HỌC HÀM EXCEL NÂNG CAO VỚI CAO THỦ.

Hoặc bạn nào có nhu cầu học nâng cao hơn nữa về Hàm sumproduct và các hàm excel khác dưới sự dẫn dắt, chỉ bảo của người có kinh nghiệm.

Bạn chỉ cần email vào hòm mail trên và nói rõ nguyện vọng nhé.

Trường sẽ tự vấn cụ thể cũng như sắp xếp thầy để hướng dẫn tận tình cho các bạn.

Đã khá muộn rồi, nên Trường cần phải đi ngủ đã, Trường rất quyết tâm để hoàn thành bài viết này sau một ngày dài Hỗ trợ khách hàng sử dụng Phần mềm Webkynang.vn.

Hẹn các bạn vào tuần tới, sẽ có thêm nhiều điều bất ngờ đang chờ đợi các bạn.

Cảm ơn bạn đã theo dõi và ủng hộ Trường cũng như blog Excel.webkynang.vn

Thân,

Truongpx – Admin webkynang.vn