Công thức chung
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Tóm lược
Với bộ dữ liệu lớn, hàm VLOOKUP đối sánh chính xác có thể chậm một cách đáng kể, nhưng bạn có thể làm cho hàm VLOOKUP sáng lên nhanh chóng bằng cách sử dụng hai hàm VLOOKUPS, như được giải thích bên dưới.
Ghi chú:
- Nếu bạn có một bộ dữ liệu nhỏ hơn, thì cách tiếp cận này là quá mức cần thiết. Chỉ sử dụng nó với các tập dữ liệu lớn khi tốc độ thực sự quan trọng.
- Bạn phải sắp xếp dữ liệu theo giá trị tra cứu để thủ thuật này hoạt động.
- Ví dụ này sử dụng phạm vi được đặt tên. Nếu bạn không muốn sử dụng các phạm vi đã đặt tên, hãy sử dụng tham chiếu tuyệt đối để thay thế.
VLOOKUP đối sánh chính xác chậm
Khi bạn sử dụng hàm VLOOKUP ở "chế độ đối sánh chính xác" trên một tập dữ liệu lớn, nó thực sự có thể làm chậm thời gian tính toán trong trang tính. Ví dụ, với 50.000 bản ghi hoặc 100.000 bản ghi, việc tính toán có thể mất vài phút.
Đối sánh chính xác được đặt bằng cách cung cấp FALSE hoặc 0 làm đối số thứ tư:
=VLOOKUP(val,data,col,FALSE)
Lý do VLOOKUP trong chế độ này chậm là vì nó phải kiểm tra từng bản ghi trong tập dữ liệu cho đến khi tìm thấy kết quả khớp. Điều này đôi khi được gọi là tìm kiếm tuyến tính.
VLOOKUP đối sánh gần đúng rất nhanh
Ở chế độ so khớp gần đúng, hàm VLOOKUP cực kỳ nhanh. Để sử dụng hàm VLOOKUP so khớp gần đúng, bạn phải sắp xếp dữ liệu của mình theo cột đầu tiên (cột tra cứu), sau đó chỉ định TRUE cho đối số thứ 4:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP mặc định là true, đây là một mặc định đáng sợ, nhưng đó là một câu chuyện khác).
Với bộ dữ liệu rất lớn, việc thay đổi thành hàm VLOOKUP đối sánh gần đúng có thể đồng nghĩa với việc tăng tốc độ đáng kể.
Vì vậy, không có trí tuệ, phải không? Chỉ cần sắp xếp dữ liệu, sử dụng đối sánh gần đúng và bạn đã hoàn tất.
Không quá nhanh (heh).
Vấn đề với VLOOKUP ở chế độ "đối sánh gần đúng" là: VLOOKUP sẽ không hiển thị lỗi nếu giá trị tra cứu không tồn tại. Tệ hơn, kết quả có thể trông hoàn toàn bình thường, mặc dù nó hoàn toàn sai (xem ví dụ). Không phải điều gì bạn muốn giải thích với sếp.
Giải pháp là sử dụng hàm VLOOKUP hai lần, cả hai lần trong chế độ đối sánh gần đúng:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Giải trình
Phiên bản đầu tiên của VLOOKUP chỉ đơn giản là tìm kiếm giá trị tra cứu ( id trong ví dụ này):
=IF(VLOOKUP(id,data,1,TRUE)=id
và chỉ trả về TRUE khi giá trị tra cứu được tìm thấy. Trong trường hợp đó,
công thức sẽ chạy lại hàm VLOOKUP ở chế độ đối sánh gần đúng để truy xuất giá trị từ bảng đó:
VLOOKUP(id,data,col,TRUE)
Không có nguy cơ thiếu giá trị tra cứu, vì phần đầu tiên của công thức đã được kiểm tra để đảm bảo rằng nó ở đó.
Nếu không tìm thấy giá trị tra cứu, phần "giá trị nếu FALSE" của hàm IF sẽ chạy và bạn có thể trả về bất kỳ giá trị nào bạn muốn. Trong ví dụ này, chúng tôi sử dụng NA (), chúng tôi trả về lỗi # N / A, nhưng bạn cũng có thể trả về một thông báo như "Thiếu" hoặc "Không tìm thấy".
Hãy nhớ rằng: bạn phải sắp xếp dữ liệu theo giá trị tra cứu để thủ thuật này hoạt động.
