Sự kết hợp của hàm Sumproduct và các hàm trong Excel

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

Sự kết hợp của hàm Sumproduct và các hàm 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

Ở các phiên bản hiện đại của Excel, hàm Sumproduct hoàn toàn có thể là một lựa chọn thay thế. Dưới đây, bạn sẽ thấy vài ví dụ về sự kết hợp của hàm Sumproduct và các hàm trong Excel.

1. Hàm Sumproduct với hàm Logic And

Giả sử bạn có chuỗi dữ liệu dưới đây, trong đó cột A liệt kê vùng miền, cột B – món hàng và cột C – doanh số bán ra:

Hàm Sumproduct với hàm Logic And

Hàm Sumproduct với hàm Logic And

Điều bạn muốn thực hiện đó là đếm, tính tổng và tính trung bình cộng doanh số táo bán ra ở khu vực phía Bắc.

Trong các phiên bản Excel gần đây: Excel 2016, 2013, 2010 và 2007, bạn có thể dễ dàng hoàn thành nhiệm vụ bằng cách sử dụng công thức SUMIFS, COUNTIFS và AVERAGEIFS. Nếu bạn đang tìm một hướng đi khó khăn hơn, hay nếu bạn vẫn sử dụng Excel 2003 hay phiên bản cũ hơn, thì bạn vẫn đạt được kết quả mong muốn với hàm Sumproduct.

Để đếm doanh số táo bán ra ở khu vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”))

hay

=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”))

Để tính tổng doanh số táo bán ra ở khu vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”), C2:C12)

hay

=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”)*C2:C12)

Để tính trung bình cộng doanh số táo bán ra ở khu vực phía Bắc:

Để tính trung bình cộng, chúng ta chỉ chia tổng cho phép đếm như thế này:

=SUMPRODUCT(–(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”), C2:C12) / SUMPRODUCT( –(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”))

Để hàm Sumproduct linh hoạt hơn, bạn có thể định rõ khu vực và món hàng mong muốn ở các ô riêng, rồi tham chiếu các ô đó vào công thức giống như ảnh chụp màn hình dưới đây:

2. Hàm Sumproduct với hàm Logic Or

Để công hay đếm có điều kiện các ô sử dụng hàm lôgic OR, hãy sử dụng dấu cộng (+) giữa các mảng.

Trong hàm Sumproduct, cũng như trong công thức mảng, dấu cộng đóng vai trò làm toán tử OR hướng dẫn Excel trả về giá trị TRUE nếu bất kỳ điều kiện nào trong biểu thức xác định được đáp ứng.

Ví dụ, để đếm tổng doanh số Táo và Chanh không phân biệt vùng miền, hãy sử dụng công thức này:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”))

Nói đơn giản, công thức trên có nghĩa là: Hãy đếm số ô nếu dải ô B2:B12=”táo” OR B2:B12=”chanh”.

Để tính tổng doanh số Táo và Chanh, hãy thêm một câu lệnh chứa dải ô Doanh số:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”), C2:C12)

Ảnh chụp màn hình dưới đây diễn giải công thức tương tự:

Hàm Sumproduct với hàm Logic Or

Hàm Sumproduct với hàm Logic Or

3. Hàm Sumproduct cùng với hàm Logic and và hàm Logic Or

Trong nhiều trường hợp, có thể bạn cần phải đếm hay tính tổng có điều kiện sử dụng cả hàm lôgic AND và OR. Thậm chí ở phiên bản Excel mới nhất , chuỗi hàm liên quan đến hàm IF cũng không thể làm được điều đó.

Một trong những giải pháp khả thi đó là kết hợp hai hay nhiều hàm SUMIFS+SUMIFS hay COUNTIFS+COUNTIFS.

Cách khác đó là sử dụng hàm Sumproduct trong đó:

Dấu hoa thị (*) được dùng như toán tử AND.

Dấu cộng (+) được dùng như toán tử OR.

Để khiến mọi thứ đơn giản hơn, hãy xem xét các ví dụ dưới đây:

Để đếm số lần Táo và Chanh được bán ở khu vực phía Bắc, hãy lập công thức với hàm lôgic sau:

=Count If ((Vùng miền=”miền Bắc”) AND ((Món hàng=”Táo”) OR (Món hàng=”Chanh”)))

Khi vừa sử dụng theo cú pháp hàm Sumproduct, công thức sẽ có dạng như sau:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”)))

Để tính tổng doanh số Táo và Chanh bán ra ở khu vục phía Bắc, hãy dùng công thức trên rồi thêm dải ô Doanh số cùng với hàm lôgic AND:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”))*C2:C12)

Để công thức trông gọn hơn, bạn có thể gõ các biến vào các ô riêng biệt – Khu vực ở ô F1 và Món hàng ở ô F2 và H2 – rồi tham chiếu các ô này vào công thức:

Hàm Sumproduct cùng với hàm Logic and và hàm Logic Or

Hàm Sumproduct cùng với hàm Logic and và hàm Logic Or

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Đ