Tóm lược
Để đếm hàng với logic HOẶC, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong G6 là:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
trong đó nhóm (B5: B15), color1 (C5: C15) và color2 (D5: D15) là các phạm vi được đặt tên.
Giải trình
Một trong những vấn đề phức tạp hơn trong Excel là đếm các hàng trong tập hợp dữ liệu với "logic HOẶC". Có hai trường hợp cơ bản: (1) bạn muốn đếm các hàng có giá trị trong cột là "x" HOẶC "y" (2) bạn muốn đếm các hàng có giá trị, "x", tồn tại trong một cột HOẶC cột khác .
Trong ví dụ này, mục tiêu là đếm các hàng trong đó group = "a" VÀ Color1 HOẶC Color2 là "đỏ". Điều này có nghĩa là chúng tôi đang làm việc với kịch bản 2 ở trên.
Với COUNTIFS
Lúc đầu, bạn có thể tiếp cận hàm COUNTIFS, hàm này xử lý nhiều tiêu chí nguyên bản. Tuy nhiên, hàm COUNTIFS kết hợp các điều kiện với logic AND, vì vậy tất cả các tiêu chí phải ĐÚNG để được đưa vào số lượng:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Điều này khiến COUNTIFS không hoạt động được, trừ khi chúng tôi sử dụng nhiều phiên bản COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Dịch: đếm các hàng trong đó nhóm là "a" và color1 là "đỏ" + đếm các hàng trong đó nhóm là "a" và color2 là "đỏ" - đếm các hàng có nhóm là "a" và color1 là "đỏ" và color2 là " red ”(để tránh đếm hai lần).
Công thức này hoạt động, nhưng bạn có thể thấy đây là một công thức hơi phức tạp và dư thừa.
Với logic Boolean
Một giải pháp tốt hơn là sử dụng logic Boolean và xử lý kết quả bằng hàm SUMPRODUCT. (Nếu bạn cần sơ lược về đại số Boolean, video này sẽ cung cấp phần giới thiệu.) Trong ví dụ minh họa, công thức trong G6 là:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
trong đó nhóm (B5: B15), color1 (C5: C15) và color2 (D5: D15) là các phạm vi được đặt tên.
Phần đầu tiên của vấn đề là kiểm tra nhóm = "a" mà chúng tôi thực hiện như sau:
(group="a")
Vì phạm vi B5: B15 chứa 11 ô nên biểu thức này trả về một mảng gồm 11 giá trị TRUE và FALSE như sau:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Mỗi TRUE đại diện cho một hàng trong đó nhóm là "A".
Tiếp theo, chúng ta cần kiểm tra giá trị "màu đỏ" trong cột1 hoặc cột2. Chúng tôi thực hiện điều này với hai biểu thức được nối bằng phép cộng (+), vì phép cộng tương ứng với logic OR trong đại số Boolean:
(color1="red")+(color2="red")
Excel tự động đánh giá các giá trị TRUE và FALSE dưới dạng 1s và 0s trong bất kỳ phép toán nào, vì vậy kết quả từ biểu thức trên là một mảng như sau:
(2;0;0;1;1;0;1;0;0;0;1)
Số đầu tiên trong mảng là 2, vì cả Color1 và Color2 đều "đỏ" ở hàng đầu tiên. Vì những lý do được giải thích bên dưới, chúng tôi cần đề phòng trường hợp này bằng cách kiểm tra các giá trị lớn hơn 0:
((2;0;0;1;1;0;1;0;0;0;1))>0
Bây giờ chúng ta lại có một mảng các giá trị TRUE và FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Bảng dưới đây tóm tắt cách Excel đánh giá logic màu được giải thích ở trên:
Tại thời điểm này, chúng tôi có kết quả từ thử nghiệm Group = "a" trong một mảng:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Và kết quả từ thử nghiệm "màu đỏ" trong Color1 hoặc Color2 trong một mảng khác:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Bước tiếp theo là đưa hai mảng này lại với nhau bằng "AND logic". Để làm điều này, chúng tôi sử dụng phép nhân (*), vì phép nhân tương ứng với logic AND trong đại số Boolean.
Sau khi nhân hai mảng với nhau, chúng ta có một mảng duy nhất gồm 1s và 0, được phân phối trực tiếp đến hàm SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Hàm SUMPRODUCT trả về tổng các số, 2, là kết quả cuối cùng. Đây là số hàng trong đó group = "a" VÀ Color1 HOẶC Color2 là "đỏ".
Để tránh tính hai lần
Chúng tôi không muốn đếm gấp đôi các hàng mà cả Color1 và Color2 đều là "đỏ". Đây là lý do tại sao chúng tôi kiểm tra kết quả từ (color1 = "red") + (color2 = "red") cho các giá trị lớn hơn 0 trong đoạn mã dưới đây:
((color1="red")+(color2="red"))>0
Nếu không có kiểm tra này, 2 từ hàng đầu tiên trong dữ liệu sẽ hiển thị trong mảng cuối cùng và khiến công thức trả về không chính xác 3 là số cuối cùng.
Tùy chọn FILTER
Một điều thú vị về logic Boolean là nó hoạt động hoàn hảo với các hàm mới nhất của Excel, như XLOOKUP và FILTER. Ví dụ: hàm FILTER có thể sử dụng chính xác cùng một logic được giải thích ở trên để trích xuất các hàng phù hợp:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Kết quả từ FILTER là hai hàng đáp ứng tiêu chí, như được thấy bên dưới:
Nếu bạn muốn tìm hiểu thêm về các chức năng mới này, chúng tôi có phần tổng quan và video đào tạo.
