Tăng tốc VLOOKUP với dữ liệu lớn

giaiphapvba

Administrator
Thành viên BQT
Khi thiết định VLOOKUP với dữ liệu lớn, việc tính toán nhiều khi xảy ra rất lâu, có cảm giác Excel đang bị treo.
Tôi nghĩ những người có kinh nghiệm về việc này đều cảm thấy muốn cải thiện tốc độ tính toán này.
Trong trường hợp như thế, sau đây tôi xin giới thiệu một phương pháp cải thiện tốc độ VLOOKUP như dưới đây.

Đây là dữ liệu sheet 1 có 20.000 dòng dữ liệu.
Bạn cần đăng nhập để thấy đính kèm


Và đây là dữ liệu sheet 2: Trong đó cột B là kết quả tìm kiếm từ khóa là cột A có trên cột A của sheet 1 hay không, nếu có thì lấy giá trị cột B tương ứng trên sheet 1 để điền vào cột B của sheet 2.
Bạn cần đăng nhập để thấy đính kèm

Cột A của sheet 2 chứa các từ khóa cần tìm kiếm. Chúng ta cần tìm kiếm các từ khóa này có xuất hiện trên cột A của sheet 1 hay không, nếu có thì lấy dữ liệu của cột B dòng tương ứng trên sheet 1 và điền vào cột B của sheet 2.
Trường hợp không tìm thấy từ khóa thì trả về là #N/A.

Công thức cho ô B2 trên sheet 2 là:
Mã:
=IF(INDEX(Sheet1!$A$2:$A$20001,
MATCH($A2,Sheet1!$A$2:$A$20001,1),1)=$A2,
VLOOKUP($A2,Sheet1!$A$2:$B$20001,2,TRUE),NA())
Công thức tuy hơi dài, trình tự xử lý của nó như sau:
Hàm MATCH sẽ tìm kiếm giá trị cận dưới (gần đúng hoặc bằng) với giá trị cần tìm kiếm.
Giá trị trả về của hàm MATCH sẽ được dùng cho hàm INDEX để lấy giá trị.
Giá trị trả về của hàm INDEX lúc này sẽ so sánh với chính từ khóa mà ta đang tìm kiếm (cells A2 của sheet 2)
, nếu khớp thì dùng hàm VLOOKUP lấy giá trị gần đúng, nếu không khớp thì lấy giá trị là #NA.

Sau đây, tôi phân tách kỹ hơn để giúp các bạn sáng tỏ vấn đề:
IF(Xác nhận giá trị tìm kiếm có tồn tại trên vùng tìm kiếm hay không, Thực thi VLOOKUP,NA())

Hàm IF là hàm ngoài cùng trong công thức của chúng ta. Nó hỏi có tồn tại từ khóa tìm kiếm trên cột A của sheet 1 hay không?
Nếu có thì thực thi VLOOKUP và lấy giá trị trả về của VLOOKUP là kết quả cuối cùng.
Nếu không có thì lấy giá trị trả về là #NA là kết quả cuối cùng.

Để xác nhận từ khóa tìm kiếm có trên cột A của sheet 1 hay không, đầu tiên hàm MATCH sẽ tìm giá trị gần đúng, ta được vị trí hàng của dòng dữ liệu trên sheet 1. Thong qua hàm INDEX ta lấy giá trị của cột A trên hàng đó (sheet 1).
So sánh giá trị này với bản thân từ khóa tìm kiếm (cells A2 sheet 2).

INDEX(Sheet1!$A$2:$A$20001,
MATCH($A2,Sheet1!$A$2:$A$20001,1),1)=$A2


Việc tìm kiếm gần đúng sẽ cho tốc độ rất nhanh, đây chính là vấn đề chính để giải quyết bài toán tốc độ của chúng ta.
MATCH($A2,Sheet1!$A$2:$A$20001,1)
Ở đây có lưu ý, vì tham số thứ ba của hàm MATCH là 1 cho nên dữ liệu của vùng tìm kiếm, mà ở đây là sheet 1 từ ô A2 tới ô A20001 dữ liệu phải xếp theo thứ tự tăng dần.

INDEX(Sheet1!$A$2:$A$20001,Kết quả của hàm MATCH,1)=$A2
Vì hàm MATCH chỉ cho ta biết kết quả tìm kiếm được ở dòng thứ bao nhiêu, cho nên cần thông qua hàm INDEX để lấy giá trị. Giá trị này là kết quả của việc tìm kiếm gần đúng để ưu tiên tốc độ xử lý nhanh, cho nên một lần nữa nó được so sánh với từ khóa tìm kiếm $A2 của sheet 2.

VLOOKUP($A2,Sheet1!$A$2:$B$20001,2,TRUE)
Hàm VLOOKUP thực hiện tìm kiếm gần đúng (chú ý tham số cuối cùng là TRUE), đây chính là điểm mấu chốt tăng tốc độ tìm kiếm. Bởi đây là phép tìm kiếm gần đúng. Tuy nhiên bạn không cần lo lắng kết quả tìm kiếm có chính xác hay không, bởi vì từ khóa thực sự đã tồn tại do đã được kiểm tra trước đó bằng hàm IF.

Tóm lại, cả hàm MATCH và VLOOKUP đều thực hiện tìm kiếm gần đúng cho nên tốc độ thực thi rất nhanh. Mặc dù tìm kiếm gần đúng, nhưng thông qua logic trên như tôi đã giải thích cho các bạn, chúng ta vẫn đảm bảo được rằng kết quả tìm kiếm là đúng đắn.

Không chỉ là 20.000 dòng dữ liệu, tôi đã thí nghiệm với 200.000 dòng dữ liệu, tốc độ tìm kiếm cũng rất nhanh.
Nguồn tham khảo:
 

ThaiTQ

Yêu THVBA
tôi đang muốn tối ưu hóa , những cách của bạn tôi thấy không khả thi
 

tuhocvba

Administrator
Thành viên BQT
Các ý kiến phản biện phải có dữ liệu dẫn chứng minh, các phát biểu không có căn cứ sẽ bị ban nick. Vì vậy nick ThaitTQ sẽ bị ban nick vĩnh viễn.
 
Top