Công thức chung
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Tóm lược
Để đếm các giá trị nằm ngoài dung sai trong một tập dữ liệu, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT và hàm ABS. Trong ví dụ được hiển thị, công thức trong F6 là:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
trong đó "dữ liệu" là phạm vi được đặt tên B5: B14, "mục tiêu" là phạm vi được đặt tên F4 và "dung sai" là phạm vi được đặt tên F5.
Giải trình
Công thức này tính có bao nhiêu giá trị không nằm trong phạm vi của dung sai cố định. Sự thay đổi của mỗi giá trị được tính toán như sau:
ABS(data-target)
Vì phạm vi được đặt tên là "dữ liệu" chứa 10 giá trị, trừ đi giá trị đích trong F4 sẽ tạo ra một mảng có 10 kết quả:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Chức năng ABS thay đổi mọi giá trị âm thành dương:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Mảng này được so sánh với dung sai cố định trong F5:
ABS(data-target)>tolerance
Kết quả là một mảng hoặc các giá trị TRUE FALSE và âm kép thay đổi các giá trị này thành các giá trị một và số không. Bên trong SUMPRODUCT, mảng cuối cùng trông giống như sau:
(0;0;1;0;1;0;0;1;0;1)
trong đó các số không đại diện cho các giá trị nằm trong dung sai và 1 đại diện cho các giá trị ngoài dung sai. SUMPRODUCT sau đó tính tổng các mục trong mảng và trả về kết quả cuối cùng, 4.
Tất cả các giá trị trong phạm vi dung sai
Để trả về "Có" nếu tất cả các giá trị trong phạm vi dữ liệu nằm trong một dung sai nhất định và "Không" nếu không, bạn có thể điều chỉnh công thức như sau:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Nếu hàm SUMPRODUCT trả về bất kỳ số nào lớn hơn 0, IF sẽ đánh giá phép thử logic là TRUE. Kết quả bằng không sẽ được đánh giá là FALSE.
Đánh dấu các giá trị vượt quá dung sai
Bạn có thể đánh dấu các giá trị vượt quá dung sai bằng quy tắc định dạng có điều kiện dựa trên công thức như sau:
=ABS(B5-target)>tolerance
Trang này liệt kê nhiều ví dụ hơn về định dạng có điều kiện với công thức.
