Cách sử dụng nâng cao hàm Sumproduct trong Excel

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Cách sử dụng nâng cao hàm Sumproduct trong Excel

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Thông thường trong Microsoft Excel, luôn có nhiều hơn một cách để hoàn thành công việc. Nhưng khi nói đến việc so sánh hai hay nhiều mảng, đặc biệt là có nhiều điều kiện, nếu không nói là duy nhất, hàm Sumproduct trong Excel là hàm hiệu quả nhất. Hàm Sumproduct trong Excel hay công thức mảng đều được.

1. Hàm Sumproduct trong Excel có nhiều điều kiện

Giả sử bạn có danh sách các món hàng ở cột A, doanh số bán dự kiến ở cột B, và doanh số bán thực ở cột C. Mục tiêu của bạn là tìm xem có bao nhiêu có bao nhiêu món bán ít hơn dự kiến. Đối với trường hợp này, hãy sử dụng một trong những biến phân của công thức SUMPRODUCT:

=SUMPRODUCT(–(C2:C10<B2:B10))

hay

=SUMPRODUCT((C2:C10<B2:B10)*1)

Trong đó C2:C10 là doanh số thực và B2:B10 là doanh số dự kiến.

Hàm Sumproduct trong Excel có nhiều điều kiện

Hàm Sumproduct trong Excel có nhiều điều kiện

Nhưng nếu bạn có nhiều hơn một điều kiện thì sao? Giả sử bạn muốn đếm số lần Táo bán ít hơn dự kiến. Giải pháp đó là thêm một điều kiện nữa vào công thức SUMPRODUCT:

=SUMPRODUCT(–(C2:C10<B2:B10), –(A2:A10=”táo”))

Hay, bạn có thể sử dụng cú pháp sau:

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))

Và bây giờ, hãy dành ít phút để hiểu rõ công thức trên đang xử lý điều gì. Tôi tin rằng nó đáng vài phút suy ngẫm đấy bởi vì nhiều công thức SUMPRODUCT khác có quy luật tương tự.

2. Cách sử dụng hàm Sumproduct trong Excel có một điều kiện hoạt động

Đối với người mới bắt đầu, hãy chia nhỏ công thức đơ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 chúng ta 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 mảng sau:

Cái chúng ta có ở đây đó là giá trị hàm BOOLEAN TRUE và FALSE, trong đó, TRUE có nghĩa là đã đáp ứng điều kiện xác định (ví dụ, một giá trị ở cột B ít hơn một giá trị ở cột C trong cùng một hàng), và 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 TRUE và FALSE thành 1 và 0: {0;1;0;0;1;0;1;0;0}.

Cách khác để đổi giá trị lôgic 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, nên nó chỉ nhân 1 trong mảng cho kết quả và chúng ta nhận được kết quả đếm mong muốn. Rất dễ, đúng không nào?

3. Cách sử ụng hàm Sumproduct trong Excel có nhiều điều kiện hoạt động

Khi hàm Sumproduct trong Excel chứa hai hay nhiều mảng, nó nhân các thành phần của mảng, rồi cộng các tích số.

Có lẽ bạn vẫn còn nhớ, chúng ta đã 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 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 đó là phương pháp ép buộc TRUE và FALSE thành 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à, chúng ta nhận được hai mảng 1 và 0:

Phép nhân thực hiện bởi hàm Sumproduct trong Excel kết hợp chúng thành một mảng riêng. Và vì nhân 0 thì bằng 0, nên 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:

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ