Đếm số giá trị bị thiếu trong excel
Công thức chung
= SUMPRODUCT ( – ( COUNTIF (danh sách1 , danh sách2 ) = 0 ))
Mời bạn xem thêm:
>> đơn đề nghị cấp lại thẻ bhyt
>> bài thu hoạch nghị quyết trung ương 6 của đảng viên
>> biên bản đối chiếu công nợ
Giải trình
Để đếm các giá trị trong một danh sách bị thiếu trong danh sách khác, bạn có thể sử dụng công thức dựa trên các hàm COUNTIF và SUMPRODUCT.
Trong ví dụ được hiển thị, công thức trong H6 là:
= SUMPRODUCT ( – ( COUNTIF (danh sách1 , danh sách2 ) = 0 ))
Trả về 1 vì giá trị “Osborne” không xuất hiện trong B6: B11.
Cách thức hoạt động của công thức này
Hàm COUNTIF kiểm tra các giá trị trong phạm vi chống lại tiêu chí. Thông thường, chỉ có một tiêu chí được cung cấp, nhưng trong trường hợp này, chúng tôi cung cấp nhiều hơn một tiêu chí.
Đối với phạm vi, chúng tôi cung cấp cho COUNTIF danh sách dải ô được đặt tên1 (B6: B11) và đối với tiêu chí, chúng tôi cung cấp danh sách dải ô được đặt tên2 (F6: F8).
Vì chúng tôi cung cấp cho COUNTIF nhiều tiêu chí, chúng tôi nhận được nhiều kết quả trong một mảng kết quả trông giống như sau: {2; 1; 0}
Chúng tôi chỉ muốn đếm các giá trị bị thiếu, theo định nghĩa có số không, vì vậy chúng tôi chuyển đổi các giá trị này thành TRUE và FALSE bằng câu lệnh “= 0”, cho kết quả: {FALSE; FALSE; TRUE}
Sau đó, chúng ta buộc các giá trị TRUE FALSE thành 1 và 0 với toán tử âm kép (-), tạo ra: {0; 0; 1}
Cuối cùng, chúng tôi sử dụng SUMPRODUCT để thêm các mục trong mảng và trả về tổng số giá trị bị thiếu.
Thay thế bằng MATCH
Nếu bạn thích nhiều công thức theo nghĩa đen hơn, bạn có thể sử dụng công thức bên dưới, dựa trên MATCH, nghĩa đen đếm các giá trị “thiếu” bằng cách sử dụng hàm ISNA:
= SUMPRODUCT ( – ISNA ( MATCH (danh sách2 , danh sách1 , 0 )))