Một vấn đề xuất hiện nhiều trong Excel là đếm hoặc tính tổng dựa trên nhiều điều kiện OR. Ví dụ: có lẽ bạn cần phân tích dữ liệu và đếm đơn đặt hàng ở Seattle hoặc Denver, đối với các mặt hàng có màu Đỏ, Xanh lam hoặc Xanh lục? Điều này có thể phức tạp một cách đáng ngạc nhiên, vì vậy tự nhiên nó sẽ tạo ra một thử thách tốt!
Các thách thức
Dữ liệu bên dưới đại diện cho các đơn đặt hàng, một đơn đặt hàng trên mỗi hàng. Có ba thách thức riêng biệt.
Công thức nào trong F9, G9 và H9 sẽ đếm chính xác các đơn đặt hàng với các điều kiện sau:
- F9 - Áo phông hoặc áo Hoodie
- G9 - (Tshirt hoặc Hoodie) và (Red, Blue hoặc Green)
- H9 - (Áo phông hoặc Hoodie) và (Đỏ, Xanh dương hoặc Xanh lá cây) và (Denver hoặc Seattle)
Bóng màu xanh lá cây được áp dụng với định dạng có điều kiện và chỉ ra các giá trị phù hợp cho từng bộ tiêu chí OR trong mỗi cột.
Để thuận tiện cho bạn, có sẵn các phạm vi được đặt tên sau:
item = B3: B16
color = C3: C16
city = D3: D16
Bảng tính được đính kèm. Để lại câu trả lời của bạn dưới dạng bình luận!
Trả lời (bấm để mở rộng)Giải pháp của tôi sử dụng SUMPRODUCT với ISNUMBER và MATCH như sau:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Điều này sẽ tính các đơn đặt hàng ở đâu…
- Mặt hàng là (Tshirt hoặc Hoodie) và
- Màu là (Đỏ, Xanh lam hoặc Xanh lục) và
- Thành phố là (Denver hoặc Seattle)
Một số người cũng đề xuất cách tiếp cận tương tự. Tôi thích cấu trúc này vì nó mở rộng quy mô dễ dàng để xử lý nhiều tiêu chí hơn và cũng hoạt động với các tham chiếu ô (thay vì các giá trị được mã hóa cứng). Với tham chiếu ô, công thức trong H9 là:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Chìa khóa của công thức này là cấu trúc ISNUMBER + MATCH. MATCH được thiết lập "ngược" - các giá trị tra cứu đến từ dữ liệu và tiêu chí được sử dụng cho mảng. Kết quả là một mảng cột duy nhất mỗi khi MATCH được sử dụng. Mảng này chứa lỗi # N / A (không khớp) hoặc số (khớp), vì vậy ISNUMBER được sử dụng để chuyển đổi thành các giá trị boolean TRUE và FALSE. Thao tác nhân các mảng với nhau ép các giá trị TRUE FALSE thành 1s và 0s, và mảng cuối cùng bên trong SUMPRODUCT chứa các 1s trong đó các hàng đáp ứng tiêu chí. SUMPRODUCT sau đó tính tổng mảng và trả về kết quả.
