Đếm nhiều dãy với một tiêu chí trong microsoft excel

Trong bài viết này, chúng ta sẽ tìm hiểu Đếm nhiều dãy với một tiêu chí trong microsoft excel.

Kịch bản:

Nói một cách đơn giản, khi làm việc với bảng dữ liệu, đôi khi chúng ta cần đếm các ô có nhiều hơn hai phạm vi đáp ứng tiêu chí. Trong Excel, Bạn có thể thực hiện các tác vụ như thao tác trên nhiều phạm vi bằng cách sử dụng công thức được giải thích bên dưới. Tiêu chí có thể được áp dụng cho văn bản, số hoặc bất kỳ đối sánh một phần nào trong Excel. Tiêu chí bên trong công thức được thực hiện bằng toán tử. Các toán tử như bằng ( = ), nhỏ hơn bằng ( <= ), lớn hơn ( > ) hoặc không bằng ( <> ).

Làm thế nào để giải quyết vấn đề?

Đối với vấn đề này, chúng tôi sẽ được yêu cầu sử dụng chức năng TÓM TẮT . Bây giờ chúng ta sẽ tạo một công thức từ hàm. Ở đây chúng tôi được cung cấp hai phạm vi dữ liệu và chúng tôi cần đếm các hàng đáp ứng 3 tiêu chí. Hàm SUMPRODVEL trả về TỔNG của các giá trị TRUE tương ứng (là 1) và bỏ qua các giá trị tương ứng với giá trị FALSE (là 0) trong mảng được trả về

Công thức chung:

=  SUMPRODUCT( ( rng_1 op_1 crit_1 ) + 0 , ( rng_2 op_2 crit_1 ) + 0 , rng_2 op_2 crit_1 ) + 0 )

rng : phạm vi cần tìm

crit: tiêu chí để áp dụng

op : toán tử tiêu chí, điều kiện được đưa ra dưới dạng toán tử giữa phạm vi & tiêu chí

+0 : chuyển đổi giá trị boolean thành nhị phân (0 & 1).

Ví dụ:

Tất cả những điều này có thể gây nhầm lẫn để hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ hiển thị bên dưới. Ở đây chúng ta cần tìm số hàng được liệt kê trong phạm vi có 3 điều kiện. Ở đây chúng tôi có một danh sách các cuộc họp ngoại giao được tổ chức giữa Ấn Độ và Hoa Kỳ từ năm 2014. Bảng hiển thị Tổng thống / Thủ tướng với nhãn quốc gia và năm. Bảng cũng được chia thành các phần đại diện cho quốc gia sở tại và danh sách quốc gia đến thăm.

Các điều kiện được liệt kê dưới đây:

Tổng thống Hoa Kỳ “Barack Obama thăm Ấn Độ có ít vấn đề hơn 2.

Sử dụng Công thức:

=SUMPRODUCT ( ( C4:C10 = “Barack Obama”) + 0 , ( F4:F10 = “Ấn Độ” ) + 0 , ( G4:G10 < 2 ) + 0 ) )

C4:C10=”Barack Obama” : Tổng thống khớp với “Barack Obama” trong danh sách khách thăm.

F4:F10=”Ấn Độ” : nước chủ nhà khớp với “Ấn Độ”.

G4:G10<2 : vấn đề ít hơn hai.

+0 : chuyển đổi giá trị boolean thành nhị phân (0 & 1).

Ở đây phạm vi được đưa ra dưới dạng tham chiếu ô. Nhấn Enter để lấy số đếm.

Như bạn có thể thấy, chỉ có một lần tổng thống Hoa Kỳ Barack Obama đến thăm Ấn Độ vào năm 2015. Điều này cho thấy công thức trích xuất số lần khớp trong mảng tương ứng. Vì cũng có một lần tổng thống Hoa Kỳ “Barack Obama” đến thăm Ấn Độ, nơi các vấn đề cũng bằng 1 và nhỏ hơn 2.

Bằng với Tiêu chí:

Ví dụ trên rất dễ dàng. Vì vậy, để làm cho nó thú vị, chúng tôi sẽ đếm số lần Hoa Kỳ tổ chức Ấn Độ bắt đầu từ năm 2014 theo dữ liệu.

Các điều kiện được liệt kê dưới đây:

Hoa Kỳ tổ chức Ấn Độ có vấn đề bằng 2.

Sử dụng Công thức:

= SUMPRODUCT ( ( F4:F10 = “US”) + 0 , ( D4:D10 = “Ấn Độ”) + 0 , ( G4:G10 = 2 ) + 0 )

F4:F10=”US” : nước chủ nhà khớp với “US”.

D4:D10=”Ấn Độ” : đang truy cập quốc gia khớp với “Ấn Độ”.

G4:G10=2 : vấn đề bằng hai.

+0 : chuyển đổi giá trị boolean thành nhị phân (0 & 1).

Ở đây phạm vi được đưa ra dưới dạng tham chiếu ô. Nhấn Enter để lấy số đếm.

Như bạn có thể thấy, có 2 lần Hoa Kỳ tổ chức Ấn Độ và các vấn đề bằng hai. Điều này cho thấy rằng công thức trích xuất số lần khớp trong mảng tương ứng. Vì có 5 lần Hoa Kỳ tổ chức Ấn Độ nhưng các vấn đề là 1 hoặc 3, nhưng ở đây chúng tôi cần các vấn đề được khớp với 2.

Với tiêu chí lớn hơn:

Ở đây để làm cho nó thú vị, chúng tôi sẽ đếm số lần tổng thống Hoa Kỳ “Donald Trump” tiếp đón Thủ tướng Ấn Độ bắt đầu từ năm 2014 theo dữ liệu.

Các điều kiện được liệt kê dưới đây:

Tổng thống Mỹ “Donald Trump” tổ chức Ấn Độ có vấn đề lớn hơn 1.

Sử dụng Công thức:

=SUMPRODUCT ( ( C4:C10 = “Donald Trump”) + 0 , ( F4 : F10 = “Ấn Độ”) + 0 , ( G4:G10 >1 ) + 0 )

F4:F10=”US” : tổng thống chủ nhà phù hợp với “Donald Trump”.

D4:D10=”Ấn Độ” : đang truy cập quốc gia khớp với “Ấn Độ”.

G4:G10=2 : vấn đề bằng hai.

+0 : chuyển đổi giá trị boolean thành nhị phân (0 & 1).

Ở đây phạm vi được đưa ra dưới dạng tham chiếu ô. Nhấn Enter để lấy số đếm.

Như bạn có thể thấy, một lần Tổng thống Hoa Kỳ “Donald Trump” tổ chức Ấn Độ và các vấn đề lớn hơn hai. Điều này cho thấy rằng công thức trích xuất số lần khớp trong mảng tương ứng. Như đã có 2 lần tổng thống Mỹ “Donald Trump” tiếp đón Ấn Độ nhưng các vấn đề là 1 hoặc 3, Nhưng ở đây chúng ta cần các vấn đề lớn hơn 1 tức là 3 nằm trong năm 2019.

Với các vấn đề không được xem xét trong Tiêu chí:

Ở đây để dễ hiểu và thuận tiện, chúng tôi sẽ đếm tổng số lần tổng thống Mỹ đến thăm Ấn Độ bắt đầu từ năm 2014 theo dữ liệu.

Các điều kiện được liệt kê dưới đây:

Tổng thống Mỹ đã thăm Ấn Độ tổng cộng từ năm 2014.

Sử dụng Công thức:

= SUMPRODUCT((F4:F10=”Ấn Độ”)+0,(D4:D10=”Mỹ”)+0)

F4:F10=”US” : nước chủ nhà khớp với “US”.

D4:D10=”Ấn Độ” : đang truy cập quốc gia khớp với “Ấn Độ”.

G4:G10=2 : vấn đề bằng hai.

+0 : chuyển đổi giá trị boolean thành nhị phân (0 & 1).

Ở đây phạm vi được đưa ra dưới dạng tham chiếu ô. Nhấn Enter để lấy số đếm.

Như bạn có thể thấy, 2 lần Hoa Kỳ đến thăm Ấn Độ và các vấn đề lớn hơn hai. Điều này cho thấy rằng công thức trích xuất số lần khớp trong mảng tương ứng. Như đã có một lần Tổng thống Hoa Kỳ “Barack Obama” thăm Ấn Độ vào năm 2015 và một lần khi Tổng thống Hoa Kỳ “Donald Trump” thăm Ấn Độ vào năm 2020.

Bạn cũng có thể biểu diễn phạm vi làm tiêu chí. Đếm các ô có 2 phạm vi đáp ứng tiêu chí. Tìm hiểu thêm về Countif với TÓM TẮT trong Excel tại đây .

Dưới đây là một số ghi chú quan sát được hiển thị bên dưới.

Ghi chú:

1. Công thức chỉ hoạt động với các số.
2. Các mảng trong công thức phải có độ dài bằng nhau, vì công thức trả về lỗi khi không.
3. Hàm TÓM TẮT coi các giá trị không phải là số là 0.
4. Hàm TÓM TẮT coi giá trị logic TRUE là 1 và False là 0.
5. Mảng đối số phải có cùng kích thước, nếu không hàm sẽ trả về lỗi.
6. Hàm SUMPRODUCT trả về tổng sau khi lấy các tích riêng lẻ trong mảng tương ứng.