Bảng thưởng theo doanh số thường chỉ có các mốc 10, 20, 50, trong khi doanh số thực tế là 75. Tìm kiếm xấp xỉ trong Excel giúp bạn tìm đúng mốc đã đạt thay vì báo không tìm thấy, và một số cách còn chạy nhanh hơn nhiều trên dữ liệu lớn.
Bài viết này hướng dẫn 3 cách, giải thích vì sao danh sách phải được sắp xếp và đưa ra số đo tốc độ thực tế. Bài không kèm file vì bạn chỉ cần tự gõ bảng. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, và màu tiêu đề trong ảnh chỉ để dễ nhìn.
Bạn cần gõ bảng này trước khi làm theo
Bạn gõ 2 bảng nhỏ vào một sheet trống. Bảng mốc thưởng bắt đầu từ ô A1, còn bảng doanh số bắt đầu từ ô D1. Thưởng được tính theo đơn vị triệu đồng.
Mốc doanh số | Thưởng |
|---|---|
0 | 0 |
10 | 1 |
20 | 2 |
50 | 5 |
100 | 8 |
Doanh số | Kết quả |
|---|---|
75 | |
10 | |
5 |

Ở mỗi mục, bạn gõ công thức vào ô E2 rồi nhấn Enter. Sau đó, bạn chọn lại E2, đưa chuột vào chấm vuông nhỏ ở góc dưới bên phải ô cho đến khi con trỏ thành dấu cộng mảnh màu đen, rồi kéo xuống E4.
Nếu Excel báo lỗi khi bạn gõ dấu phẩy trong công thức, máy của bạn đang dùng dấu chấm phẩy. Khi đó, bạn đổi mọi dấu phẩy ngăn cách trong công thức thành dấu chấm phẩy.
Tìm chính xác và tìm xấp xỉ khác nhau ở điểm nào?
Bạn gõ công thức tìm chính xác sau vào E2 và kéo xuống E4:
=MATCH(D2,$A$2:$A$6,0)
Chỉ doanh số 10 có kết quả là vị trí 2. Hai dòng còn lại báo #N/A vì 75 và 5 không có trong danh sách mốc.

Hai cách tìm hoạt động khác nhau:
Tìm tuần tự xem lần lượt từng dòng cho đến khi gặp giá trị cần tìm. Hàm MATCH với tham số 0 dùng cách này.
Tìm nhị phân xem dòng ở giữa danh sách đã sắp xếp, bỏ đi nửa không cần xem, rồi lặp lại. Hàm MATCH với tham số 1 dùng cách này.
Vì tìm nhị phân dựa vào thứ tự của danh sách, kết quả chỉ đúng khi danh sách đã được sắp xếp.
Cách 1: Hàm MATCH tìm vị trí của mốc đã đạt
Bạn đổi tham số cuối thành 1 và gõ công thức sau vào E2, rồi kéo xuống E4:
=MATCH(D2,$A$2:$A$6,1)
Các dòng trả về 4, 2 và 1. Doanh số 75 chưa đến mốc 100, nên hàm lấy mốc 50. Vị trí 4 được đếm từ đầu vùng A2:A6, không phải số dòng của sheet.

Nếu danh sách sắp xếp giảm dần, bạn dùng tham số -1. Hàm sẽ tìm giá trị nhỏ nhất nhưng không thấp hơn giá trị cần tìm. Để thử, bạn gõ "Mốc giảm dần" vào G1, gõ các số 100, 50, 20, 10, 0 vào G2:G6, rồi gõ công thức sau vào E2 và kéo xuống E4:
=MATCH(D2,$G$2:$G$6,-1)
Các dòng trả về 1, 4 và 4. Bạn cần lưu ý rằng doanh số 75 ở đây lấy mốc 100, vì tham số -1 của MATCH tìm lên trên chứ không tìm xuống dưới.

Cách 2: Hàm VLOOKUP tra bảng bậc thang
Đây là cách thường dùng nhất để tính thưởng theo mốc. Bạn xóa E2:E4 trước rồi mới xóa G1:G6, để công thức cũ không báo lỗi #REF!. Sau đó, bạn gõ công thức sau vào E2 và kéo xuống E4:
=VLOOKUP(D2,$A$2:$B$6,2,TRUE)
Các dòng trả về 5, 1 và 0.

Dữ liệu chưa sắp xếp có thể làm kết quả sai, và có dòng không hề báo lỗi. Để thử, bạn gõ bảng mốc lộn xộn dưới đây vào G1:H6:
Mốc lộn xộn | Thưởng |
|---|---|
50 | 5 |
0 | 0 |
100 | 8 |
10 | 1 |
20 | 2 |
Sau đó, bạn gõ công thức sau vào E2 và kéo xuống E4:
=VLOOKUP(D2,$G$2:$H$6,2,TRUE)
Các dòng trả về 0, #N/A và #N/A, trong khi kết quả đúng phải là 5, 1 và 0. Dòng đầu nguy hiểm nhất, vì ô vẫn hiện một con số nhưng con số đó sai.

Cách 3: Hàm XLOOKUP không cần danh sách đã sắp xếp
Hàm XLOOKUP có chế độ tìm giá trị khớp hoặc nhỏ hơn liền kề, và chế độ này không yêu cầu sắp xếp. Hàm này cần phiên bản Excel mới như Excel 365. Bạn giữ nguyên bảng lộn xộn và gõ công thức sau vào E2, rồi kéo xuống E4:
=XLOOKUP(D2,$G$2:$G$6,$H$2:$H$6,"Không có",-1)
Các dòng trả về đúng 5, 1 và 0. Tham số -1 của XLOOKUP nghĩa là tìm khớp chính xác, nếu không có thì lấy giá trị nhỏ hơn liền kề. Tham số này ngược chiều với -1 của MATCH, nên bạn cần đọc kỹ từng hàm.

Bài XLOOKUP và VLOOKUP khác nhau thế nào có thêm so sánh chi tiết giữa 2 hàm.
Tìm nhị phân nhanh hơn bao nhiêu trên dữ liệu lớn?
Chúng tôi đo thử trên một cột 200.000 số đã sắp xếp, với 20.000 công thức tra cứu. Số liệu dưới đây là thời gian tính của cả 20.000 công thức trên một máy cụ thể. Máy và dữ liệu của bạn sẽ cho số khác. Trong hàm XMATCH và XLOOKUP, số 0 hoặc -1 là kiểu khớp, còn số 1 là tìm tuần tự và số 2 là tìm nhị phân.
Công thức | Thời gian tính |
|---|---|
| khoảng 0,2 đến 0,4 giây |
| khoảng 0,03 giây |
| khoảng 0,3 giây |
| khoảng 0,04 giây |
| từ vài chục giây đến khoảng 100 giây |
| khoảng 0,04 giây |
Tìm nhị phân nhanh hơn khoảng 7 lần trở lên so với tìm tuần tự trong lần đo này. Tìm xấp xỉ không phải lúc nào cũng nhanh, vì XLOOKUP với -1 mặc định là chậm nhất. Nếu danh sách đã sắp xếp, bạn thêm số 2 vào cuối để công thức chạy nhanh hơn rất nhiều.
Bạn không nên đổi cách tìm chỉ để tăng tốc khi cần khớp đúng mã. Trên danh sách 10, 20, 20, 30, công thức MATCH với tham số 1 tra số 25 trả về 3, trong khi tham số 0 báo #N/A. Vì vậy, mã không tồn tại vẫn có thể ra một dòng sai. Nếu cần vừa khớp đúng vừa nhanh, bạn dùng =XMATCH(25,{10;20;20;30},0,2), và công thức này báo #N/A đúng như mong đợi.
Bạn thường gặp những lỗi nào khi tìm kiếm xấp xỉ?
Giá trị nhỏ hơn mốc đầu tiên: Công thức
=MATCH(-1,A2:A6,1)báo #N/A vì không có mốc nào nhỏ hơn hoặc bằng -1. Bạn nên có mốc 0 ở đầu bảng.VLOOKUP bị bỏ tham số cuối: Công thức
=VLOOKUP(75,A2:B6,2)tự hiểu là tìm xấp xỉ và trả về 5. Nếu cần khớp đúng, bạn phải ghi rõ FALSE.Mốc lấy được không phải mốc gần nhất: Doanh số 95 vẫn lấy mốc 50, dù mốc 100 gần hơn. Tìm xấp xỉ lấy mốc đã đạt, không lấy mốc gần nhất.
Danh sách có thêm dòng mới ở cuối: Dòng mới có thể làm danh sách mất thứ tự. Bạn cần sắp xếp lại sau khi thêm dữ liệu.
Người đọc thường hỏi gì về tìm kiếm xấp xỉ trong Excel?
Khi nào tôi nên dùng tìm kiếm xấp xỉ?
Bạn nên dùng khi tra bảng bậc thang như thưởng, hoa hồng hoặc thuế. Với mã hàng hoặc mã nhân viên, bạn nên dùng tìm chính xác để tránh lấy nhầm một mã gần giống.
Làm sao tôi sắp xếp danh sách để tìm đúng?
Bạn chọn cả bảng, vào tab Data và chọn sắp xếp tăng dần theo cột cần tìm. Bài sắp xếp trong Excel có hướng dẫn chi tiết hơn.
Bạn muốn dùng Excel thành thạo cho công việc văn phòng?
Khi hiểu cách Excel tìm kiếm, bạn sẽ biết lúc nào dùng tham số nào, và tránh được những kết quả sai trông có vẻ đúng.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.