Công thức Excel: Đếm các ô không nằm giữa hai số
Công thức Excel: Đếm các ô không nằm giữa hai số

Công thức chung
=COUNTIF(range,"<"&low)+COUNTIF(range,">"&high)
Tóm lược

Để đếm các giá trị ô không nằm giữa hai số, bạn có thể sử dụng Hàm COUNTIF. Trong ví dụ được hiển thị, công thức trong ô K5, được sao chép xuống, là:

=COUNTIF(C5:G5,"<"&I5)+COUNTIF(C5:G5,">"&J5)

Tại mỗi hàng mới, công thức này trả về số lượng giá trị không phải giữa các giá trị thấp và cao trong cột I và J.

Giải trình

Mục tiêu của ví dụ này là đếm các giá trị số được ghi trong 5 ngày không nằm giữa hai số, giá trị thấp và giá trị cao. Nói cách khác, để đếm các giá trị “nằm ngoài phạm vi”. Lưu ý rằng mỗi hàng, có nhãn AG, có giới hạn cao và thấp riêng, trong cột I và J.

Lúc đầu, bạn có thể nghĩ rằng sử dụng Hàm COUNTIFS với hai tiêu chí. Tuy nhiên, vì COUNTIFS kết hợp tiêu chí với logic AND, nó không thể được sử dụng với hai tiêu chí trong trường hợp này. Logic nhỏ hơn thấp hơn VÀ lớn hơn cao sẽ luôn không thành công và kết quả sẽ luôn bằng không. Thay vào đó, chúng ta cần logic HOẶC.

Một giải pháp đơn giản là sử dụng Hàm COUNTIF hai lần như thế này:

=COUNTIF(C5:G5,"<"&I5)+COUNTIF(C5:G5,">"&J5)

COUNTIF đầu tiên đếm các giá trị phía dưới giá trị trong I5 và COUNTIF thứ hai đếm các giá trị ở trên giá trị trong J5. Khi được cộng lại với nhau, hai kết quả này sẽ xử lý đúng logic được yêu cầu: nhỏ hơn I5 HOẶC lớn hơn J5. Chú ý các toán tử lớn hơn (“>”) và nhỏ hơn (“là nối đến các tham chiếu ô với một toán tử dấu và (&), một câu nói bâng quơ của Các hàm RACON.

Với SUMPRODUCT

Một giải pháp thanh lịch hơn là sử dụng Hàm SUMPRODUCT với hai biểu thức logic:

=SUMPRODUCT((C5:G5<I5)+(C5:G5>J5))

Lưu ý rằng chúng ta không cần sử dụng nối với các tham chiếu ô như với hàm COUNTIF ở trên; biểu thức tiêu chuẩn hoạt động tốt.

Đây là một ví dụ về việc sử dụng đại số Boolean với phép cộng (+), tạo logic OR. Khi các biểu thức này được đánh giá, chúng ta có hai mảng trong số các giá trị TRUE và FALSE như sau:

=SUMPRODUCT({FALSE,FALSE,FALSE,FALSE,TRUE}+{FALSE,FALSE,TRUE,FALSE,FALSE})

Phép toán tự động ép các giá trị TRUE và FALSE thành 1s và 0s. Kết quả có thể được hình dung như thế này:

=SUMPRODUCT({0,0,0,0,1}+{0,0,1,0,0})

Điều này dẫn đến một mảng duy nhất chứa hai số 1:

=SUMPRODUCT({0,0,1,0,1})

Chỉ với một mảng để xử lý, SUMPRODUCT tính tổng các mục trong mảng và trả về kết quả cuối cùng là 2.

Định dạng có điều kiện

Để dễ dàng xem giá trị nào không nằm giữa hai giá trị, bạn có thể sử dụng quy tắc định dạng có điều kiện với công thức.

Định dạng có điều kiện để đánh dấu các giá trị ngoài phạm vi

Công thức được sử dụng để đánh dấu các giá trị nằm ngoài phạm vi ở trên là:

=OR(C5<$I5,C5>$J5)

Thêm chi tiết đây.

https://exceljet.net/formula/count-cells-not-between-two-numbers