Excel cung cấp nhiều hàm tìm kiếm mạnh mẽ như VLOOKUP, HLOOKUP, INDEX/MATCH, XLOOKUP và XMATCH. Một điểm chung của các hàm này là tham số tùy chọn cho phép tìm kiếm “Chính xác” hoặc “Xấp xỉ”. Bài viết này sẽ đi sâu vào tìm kiếm xấp xỉ trong Excel, giúp bạn hiểu rõ cơ chế hoạt động và cách tối ưu tốc độ xử lý file.
Chúng ta sẽ bắt đầu với hàm MATCH quen thuộc:
=MATCH(lookup_value,lookup_array,[match_type])Hàm MATCH trả về vị trí của một giá trị trong mảng. Ví dụ, với mảng:
["a","c","b","z","e","f","g"]Để tìm vị trí của “b”, ta sử dụng:
=MATCH("b",mảng_trên,0)Kết quả là 2, vì “b” nằm ở vị trí thứ hai từ trái sang.
Tại sao match_type bắt buộc phải là 0 trong trường hợp này? Theo tài liệu của Microsoft và kinh nghiệm thực tế, nếu bỏ trống hoặc dùng 1/-1, kết quả có thể sai hoặc báo lỗi.
Xét một ví dụ khác với mảng số:
[5,10,15,20,25,30]Để tìm số 15, ta có thể dùng:
=MATCH(15,mảng_trên,0)
hoặc
=MATCH(15,mảng_trên)
hoặc
=MATCH(15,mảng_trên,1)Cả ba công thức đều trả về 3, là vị trí của số 15. Vậy tại sao trong trường hợp này có thể dùng match_type = 1?
Lưu ý: Nếu để mặc định, match_type sẽ là 1. Khi tìm kiếm xấp xỉ với match_type = 1, mảng phải được sắp xếp theo thứ tự tăng dần. Nếu mảng không được sắp xếp, kết quả sẽ sai. Ví dụ, với mảng [10,5,30,25,20,15], tìm kiếm xấp xỉ sẽ cho kết quả không chính xác.
Nhưng tại sao lại như vậy?
Việc chọn match_type (tương tự lookup_type) ảnh hưởng đến thuật toán tìm kiếm mà Excel sử dụng.
type = 0: Excel dùng Linear Search (tìm kiếm tuyến tính).type = 1: Excel dùng Binary Search (tìm kiếm nhị phân).type = -1(cho MATCH, XLOOKUP, XMATCH): Vẫn là Binary Search nhưng trên mảng được đảo ngược.
Bạn có thể tìm hiểu thêm về Linear Search và Binary Search trên Google.
Vì Binary Search chỉ hoạt động chính xác khi mảng được sắp xếp tăng dần, nên tài liệu Excel luôn yêu cầu sắp xếp mảng trước khi dùng tìm kiếm xấp xỉ.
Đến đây, bạn đã hiểu lý do tại sao cần sắp xếp mảng khi tìm kiếm xấp xỉ.
Tuy nhiên, nếu muốn tìm hiểu sâu hơn, hãy đọc tiếp.
Lưu ý: Phần tiếp theo là suy luận dựa trên kinh nghiệm, vì Microsoft không công khai chi tiết về cách các hàm được viết.
Kết luận
Việc hiểu rõ về tìm kiếm xấp xỉ và các thuật toán tìm kiếm mà Excel sử dụng giúp bạn tối ưu hóa tốc độ xử lý file, đặc biệt khi làm việc với dữ liệu lớn. Hãy nhớ sắp xếp dữ liệu khi sử dụng tìm kiếm xấp xỉ với match_type = 1 hoặc -1 để đảm bảo kết quả chính xác và hiệu quả. Hy vọng bài viết này hữu ích cho bạn. Hãy chia sẻ bài viết nếu bạn thấy nó bổ ích!
Discussion about this post