Công thức Excel: VLOOKUP nhanh hơn với 2 VLOOKUPS
Công thức Excel: VLOOKUP nhanh hơn với 2 VLOOKUPS

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ú:

  1. 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.
  2. 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.
  3. 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 các 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 hợ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.

Hàm 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, là vỡ nợ đáng sợ, nhưng nó 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ể có nghĩa là kịch tính tăng tốc độ.

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 trong 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 nữa, 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 của bạn.

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 hàm VLOOKUP chỉ đơn giản là tìm kiếm giá trị tra cứu ( Tôi 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 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ả lại 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.

https://exceljet.net/formula/faster-vlookup-with-2-vlookups