Công thức Excel: Tìm và truy xuất các giá trị bị thiếu -

Mục lục

Công thức chung

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))

Tóm lược

Để so sánh hai danh sách và kéo các giá trị bị thiếu từ danh sách này sang danh sách khác, bạn có thể sử dụng công thức mảng dựa trên INDEX và MATCH. Trong ví dụ được hiển thị, giá trị cuối cùng trong danh sách B nằm trong ô D11. Công thức trong D12, được sao chép xuống, là:

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))

trong đó "hoàn thành" là phạm vi được đặt tên là B5: B15.

Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.

Giải trình

Làm việc từ trong ra ngoài, cốt lõi của công thức này là biểu thức MATCH bên trong:

ISNA(MATCH(complete,$D$5:D11,0)

Ở đây, hàm MATCH được sử dụng để so sánh tất cả các giá trị "hoàn chỉnh" với danh sách từng phần. Phạm vi được đặt tên "hoàn thành" được sử dụng cho các giá trị tra cứu và danh sách một phần được sử dụng làm mảng tra cứu. Tuy nhiên, lưu ý rằng danh sách một phần được nhập dưới dạng phạm vi mở rộng kết thúc "một ô phía trên" ô công thức. Điều này cho phép danh sách từng phần mở rộng để bao gồm các giá trị mới khi chúng xuất hiện bên dưới danh sách ban đầu.

Kết quả của MATCH là một mảng số và lỗi # N / A, trong đó các số đại diện cho các giá trị trong danh sách đầy đủ tồn tại trong danh sách một phần; và lỗi thể hiện các giá trị bị thiếu:

(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)

Hàm ISNA được sử dụng để chuyển đổi các kết quả này thành một mảng các giá trị TRUE và FALSE. Trong mảng này, TRUE tương ứng với các giá trị bị thiếu và FALSE tương ứng với các giá trị hiện có:

(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)

Hàm ISNA trả về mảng này cho MATCH bên ngoài là mảng tra cứu. Hàm MATCH luôn trả về kết quả phù hợp đầu tiên được tìm thấy, do đó, so khớp sẽ trả về vị trí (hàng) của giá trị bị thiếu đầu tiên được tìm thấy. Kết quả này được trả về INDEX dưới dạng số hàng, với phạm vi được đặt tên là "hoàn thành" được cung cấp dưới dạng mảng.

Trong ô D12, giá trị bị thiếu đầu tiên được tìm thấy là "kiwi" ở hàng 2, vì vậy chúng ta có: