Tìm các giá trị còn thiếu
Có những trường hợp khi bạn cần so sánh hai danh sách để kiểm tra xem một giá trị của danh sách A có tồn tại trong danh sách B trong Excel hay không. Ví dụ: bạn có một danh sách các sản phẩm và bạn muốn kiểm tra xem các sản phẩm trong danh sách của mình có tồn tại trong danh sách sản phẩm do nhà cung cấp của bạn cung cấp hay không. Để hoàn thành nhiệm vụ này, chúng tôi liệt kê ba cách dưới đây, hãy chọn cách bạn thích.
Tìm các giá trị còn thiếu với MATCH, ISNA và IF
Tìm các giá trị còn thiếu bằng hàm VLOOKUP, ISNA và IF
Tìm các giá trị bị thiếu với COUNTIF và IF
Tìm các giá trị còn thiếu với MATCH, ISNA và IF
Tim ra nếu tất cả các sản phẩm trong danh sách của bạn tồn tại trong danh sách của nhà cung cấp như thể hiện trong ảnh chụp màn hình ở trên, trước tiên bạn có thể sử dụng chức năng MATCH để truy xuất vị trí của một sản phẩm trong danh sách của bạn (giá trị của danh sách A) trong danh sách của nhà cung cấp (danh sách B). MATCH sẽ trả về lỗi # N / A khi không tìm thấy sản phẩm. Sau đó, bạn có thể cung cấp kết quả cho ISNA để chuyển đổi lỗi # N / A thành TRUE, có nghĩa là các sản phẩm đó bị thiếu. Sau đó, hàm IF sẽ trả về kết quả mà bạn mong đợi.
Cú pháp chung
=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")
√ Lưu ý: Bạn có thể thay đổi "Thiếu", "Đã tìm thấy" thành bất kỳ giá trị nào khi bạn cần.
- tra cứu_giá trị: Giá trị MATCH được sử dụng để truy xuất vị trí của nó nếu nó tồn tại trong tra cứu_range hoặc lỗi # N / A nếu không. Ở đây đề cập đến các sản phẩm trong danh sách của bạn.
- phạm vi tra cứu: Phạm vi ô để so sánh với lookup_value. Ở đây đề cập đến danh sách sản phẩm của nhà cung cấp.
Tim ra nếu tất cả các sản phẩm trong danh sách của bạn tồn tại trong danh sách của nhà cung cấp, vui lòng sao chép hoặc nhập công thức bên dưới vào ô H6 và nhấn đăng ký hạng mục thi để nhận được kết quả:
= IF (ISNA (MATCH (30002,$ B $ 6: $ B $ 10, 0)), "Thiếu", "Đã tìm thấy")
Hoặc, sử dụng tham chiếu ô để làm cho công thức động:
= IF (ISNA (MATCH (G6,$ B $ 6: $ B $ 10, 0)), "Thiếu", "Đã tìm thấy")
√ Lưu ý: Các ký hiệu đô la ($) ở trên biểu thị các tham chiếu tuyệt đối, có nghĩa là tra cứu_range trong công thức sẽ không thay đổi khi bạn di chuyển hoặc sao chép công thức sang các ô khác. Tuy nhiên, không có ký hiệu đô la nào được thêm vào lookup_value vì bạn muốn nó trở nên năng động. Sau khi nhập công thức, hãy kéo chốt điền xuống để áp dụng công thức cho các ô bên dưới.
Giải thích công thức
Ở đây chúng tôi sử dụng công thức dưới đây làm ví dụ:
=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")
- TRẬN ĐẤU (G8, $ B $ 6: $ B $ 10,0): Match_type 0 buộc hàm MATCH trả về một giá trị số cho biết vị trí của kết quả khớp đầu tiên của 3004, giá trị trong ô G8, trong mảng $ B $ 6: $ B $ 10. Tuy nhiên, trong trường hợp này, MATCH không thể tìm thấy giá trị trong mảng tra cứu, vì vậy nó sẽ trả về # N / A lỗi.
- ISNA (TRẬN ĐẤU (G8, $ B $ 6: $ B $ 10,0)) = ISNA (# N / A): ISNA hoạt động để tìm ra giá trị có phải là lỗi “# N / A” hay không. Nếu có, hàm sẽ trả về TURE; Nếu giá trị là bất kỳ thứ gì ngoại trừ lỗi “# N / A”, nó sẽ trả về FALSE. Vì vậy, công thức ISNA này sẽ trả về TURE.
- NẾU(ISNA (TRẬN ĐẤU (G8, $ B $ 6: $ B $ 10,0)), "Thiếu", "Đã tìm thấy") = IF (TRUE, "Thiếu", "Đã tìm thấy"): Hàm IF sẽ trả về Thiếu nếu so sánh được thực hiện bởi ISNA và MATCH là TRUE, nếu không sẽ trả về Tìm thấy. Vì vậy, công thức sẽ trả về Thiếu.
Tìm các giá trị còn thiếu bằng hàm VLOOKUP, ISNA và IF
Để tìm hiểu xem tất cả các sản phẩm trong danh sách của bạn có tồn tại trong danh sách của nhà cung cấp hay không, bạn có thể thay thế hàm MATCH ở trên bằng hàm Vlookup, vì hàm này hoạt động giống như hàm MATCH nên nó sẽ trả về lỗi # N / A nếu giá trị không tồn tại trong một danh sách khác, hoặc chúng tôi nói rằng nó bị thiếu.
Cú pháp chung
=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")
√ Lưu ý: Bạn có thể thay đổi "Thiếu", "Đã tìm thấy" thành bất kỳ giá trị nào khi bạn cần.
- tra cứu_giá trị: Giá trị VLOOKUP được sử dụng để truy xuất vị trí của nó nếu nó tồn tại trong tra cứu_range hoặc lỗi # N / A nếu không. Ở đây đề cập đến các sản phẩm trong danh sách của bạn.
- phạm vi tra cứu: Phạm vi ô để so sánh với lookup_value. Ở đây đề cập đến danh sách sản phẩm của nhà cung cấp.
Để tìm hiểu xem tất cả các sản phẩm trong danh sách của bạn có tồn tại trong danh sách của nhà cung cấp hay không, vui lòng sao chép hoặc nhập công thức bên dưới vào ô H6 và nhấn đăng ký hạng mục thi để nhận được kết quả:
= IF (ISNA (VLOOKUP (30002,$ B $ 6: $ B $ 10, 1, FALSE)), "Thiếu", "Đã tìm thấy")
Hoặc, sử dụng tham chiếu ô để làm cho công thức động:
= IF (ISNA (VLOOKUP (G6,$ B $ 6: $ B $ 10, 1, FALSE)), "Thiếu", "Đã tìm thấy")
√ Lưu ý: Các ký hiệu đô la ($) ở trên biểu thị các tham chiếu tuyệt đối, có nghĩa là tra cứu_range trong công thức sẽ không thay đổi khi bạn di chuyển hoặc sao chép công thức sang các ô khác. Tuy nhiên, không có ký hiệu đô la nào được thêm vào lookup_value vì bạn muốn nó trở nên năng động. Sau khi nhập công thức, hãy kéo chốt điền xuống để áp dụng công thức cho các ô bên dưới.
Giải thích công thức
Ở đây chúng tôi sử dụng công thức dưới đây làm ví dụ:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")
- VLOOKUP (G8, $ B $ 6: $ B $ 10,1, FALSE): Range_lookup KHÔNG ĐÚNG buộc hàm Vlookup phải tra cứu và trả về giá trị khớp chính xác 3004, giá trị trong ô G8. Nếu lookup_value 3004 tồn tại trong 1cột st của mảng $ B $ 6: $ B $ 10, hàm VLOOKUP sẽ trả về giá trị đó; Nếu không, nó sẽ trả về giá trị lỗi # N / A. Ở đây, 3004 không tồn tại trong mảng, vì vậy, kết quả sẽ là # N / A.
- ISNA (VLOOKUP (G8, $ B $ 6: $ B $ 10,1, FALSE)) = ISNA (# N / A): ISNA hoạt động để tìm ra giá trị có phải là lỗi “# N / A” hay không. Nếu có, hàm sẽ trả về TURE; Nếu giá trị là bất kỳ thứ gì ngoại trừ lỗi “# N / A”, nó sẽ trả về FALSE. Vì vậy, công thức ISNA này sẽ trả về TURE.
- NẾU(ISNA (VLOOKUP (G8, $ B $ 6: $ B $ 10,1, FALSE)), "Thiếu", "Đã tìm thấy") = IF (TRUE, "Thiếu", "Đã tìm thấy"): Hàm IF sẽ trả về Thiếu nếu so sánh được thực hiện bởi ISNA và VLOOKUP là TRUE, nếu không sẽ trả về Tìm thấy. Vì vậy, công thức sẽ trả về Thiếu.
Tìm các giá trị bị thiếu với COUNTIF và IF
Để tìm hiểu xem tất cả các sản phẩm trong danh sách của bạn có tồn tại trong danh sách của nhà cung cấp hay không, bạn có thể sử dụng công thức đơn giản hơn với các hàm COUNTIF và IF. Công thức tận dụng lợi thế của thực tế là Excel sẽ đánh giá bất kỳ số nào ngoại trừ số không (0) là TRUE. Vì vậy, nếu một giá trị tồn tại trong một danh sách khác, hàm COUNTIF sẽ trả về số lần xuất hiện của nó trong danh sách đó, sau đó IF sẽ nhận số đó dưới dạng TURE; Nếu giá trị không tồn tại trong danh sách, hàm COUNTIF sẽ trả về 0 và IF sẽ coi nó là FALSE.
Cú pháp chung
=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")
√ Lưu ý: Bạn có thể thay đổi "Đã tìm thấy", "Thiếu" thành bất kỳ giá trị nào bạn cần.
- phạm vi tra cứu: Phạm vi ô để so sánh với lookup_value. Ở đây đề cập đến danh sách sản phẩm của nhà cung cấp.
- tra cứu_giá trị: Giá trị COUNTIF được sử dụng để trả về số lần xuất hiện của nó trong tra cứu_range. Ở đây đề cập đến các sản phẩm trong danh sách của bạn.
Để tìm hiểu xem tất cả các sản phẩm trong danh sách của bạn có tồn tại trong danh sách của nhà cung cấp hay không, vui lòng sao chép hoặc nhập công thức bên dưới vào ô H6 và nhấn đăng ký hạng mục thi để nhận được kết quả:
= IF (COUNTIF ($ B $ 6: $ B $ 10,30002), "Đã tìm thấy", "Thiếu")
Hoặc, sử dụng tham chiếu ô để làm cho công thức động:
= IF (COUNTIF ($ B $ 6: $ B $ 10,G6), "Đã tìm thấy", "Thiếu")
√ Lưu ý: Các ký hiệu đô la ($) ở trên biểu thị các tham chiếu tuyệt đối, có nghĩa là tra cứu_range trong công thức sẽ không thay đổi khi bạn di chuyển hoặc sao chép công thức sang các ô khác. Tuy nhiên, không có ký hiệu đô la nào được thêm vào lookup_value vì bạn muốn nó trở nên năng động. Sau khi nhập công thức, hãy kéo chốt điền xuống để áp dụng công thức cho các ô bên dưới.
Giải thích công thức
Ở đây chúng tôi sử dụng công thức dưới đây làm ví dụ:
=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")
- COUNTIF ($ B $ 6: $ B $ 10, G8): Hàm COUNTIF đếm số lần thực hiện 3004, giá trị trong ô G8, xuất hiện trong mảng $ B $ 6: $ B $ 10. Rõ ràng, 3004 không tồn tại trong mảng, vì vậy, kết quả sẽ là 0.
- NẾU(COUNTIF ($ B $ 6: $ B $ 10, G8), "Tìm thấy", "Thiếu") = IF (0, "Đã tìm thấy", "Thiếu"): Hàm IF sẽ đánh giá 0 là FALSE. Vì vậy, công thức sẽ trả về Thiếu, giá trị sẽ trả về khi phép bổ sung đầu tiên đánh giá là FALSE.
Các chức năng liên quan
Hàm IF là một trong những hàm đơn giản và hữu ích nhất trong sổ làm việc Excel. Nó thực hiện một bài kiểm tra logic đơn giản tùy thuộc vào kết quả so sánh và nó trả về một giá trị nếu kết quả là TRUE hoặc một giá trị khác nếu kết quả là FALSE.
Hàm MATCH trong Excel tìm kiếm một giá trị cụ thể trong một phạm vi ô và trả về vị trí tương đối của giá trị.
Hàm VLOOKUP trong Excel tìm kiếm một giá trị bằng cách khớp trên cột đầu tiên của bảng và trả về giá trị tương ứng từ một cột nhất định trong cùng một hàng.
Hàm COUNTIF là một hàm thống kê trong Excel được sử dụng để đếm số ô đáp ứng một tiêu chí. Nó hỗ trợ các toán tử logic (<>, =,>, và <), và các ký tự đại diện (? Và *) để đối sánh từng phần.
Công thức liên quan
Tra cứu giá trị chứa văn bản cụ thể bằng các ký tự đại diện
Để tìm kết quả khớp đầu tiên có chứa chuỗi văn bản nhất định trong một phạm vi trong Excel, bạn có thể sử dụng công thức INDEX và MATCH với các ký tự đại diện - dấu hoa thị (*) và dấu hỏi (?).
Đối sánh từng phần với VLOOKUP
Đôi khi bạn cần Excel để truy xuất dữ liệu dựa trên một phần thông tin. Để giải quyết vấn đề, bạn có thể sử dụng công thức VLOOKUP cùng với các ký tự đại diện - dấu hoa thị (*) và dấu hỏi (?).
Đối sánh gần đúng với INDEX và MATCH
Đôi khi chúng ta cần tìm các kết quả gần đúng trong Excel để đánh giá hiệu suất của nhân viên, điểm số của học sinh, tính toán bưu phí dựa trên trọng lượng, v.v. Trong hướng dẫn này, chúng ta sẽ nói về cách sử dụng hàm INDEX và MATCH để truy xuất kết quả chúng tôi cần.
Tra cứu giá trị đối sánh gần nhất với nhiều tiêu chí
Trong một số trường hợp, bạn có thể cần phải tra cứu giá trị đối sánh gần nhất hoặc gần đúng nhất dựa trên nhiều tiêu chí. Với sự kết hợp của các hàm INDEX, MATCH và IF, bạn có thể nhanh chóng thực hiện nó trong Excel.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
