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

Bảng mốc thưởng ở A1:B6 và bảng doanh số ở D1:E4
Bảng mốc thưởng đã được sắp xếp tăng dần, và cột E còn trống để gõ công thức.

Ở 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.

Công thức MATCH tìm chính xác trả về lỗi #N/A với 75 và 5
Tham số 0 yêu cầu giá trị phải khớp đúng, nên 75 và 5 không có kết quả.

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.

Công thức MATCH xấp xỉ trả về vị trí 4, 2 và 1
Tham số 1 tìm giá trị lớn nhất nhưng không vượt quá giá trị cần tìm.

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ông thức MATCH với tham số -1 trên danh sách giảm dần
Doanh số 75 khớp với mốc 100, vì đây là mốc nhỏ nhất không thấp hơn 75.

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.

Công thức VLOOKUP với TRUE trả về mức thưởng 5, 1 và 0
Tham số TRUE giúp hàm lấy đúng mức thưởng của mốc đã đạt.

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ông thức VLOOKUP trên danh sách lộn xộn trả về kết quả sai
Danh sách chưa sắp xếp có thể làm hàm trả về số sai hoặc lỗi #N/A.

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.

Công thức XLOOKUP trả về đúng kết quả trên danh sách lộn xộn
Hàm XLOOKUP vẫn lấy đúng mốc dù danh sách chưa sắp xếp.

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

=MATCH(C1,$A$1:$A$200000,0)

khoảng 0,2 đến 0,4 giây

=MATCH(C1,$A$1:$A$200000,1)

khoảng 0,03 giây

=XMATCH(C1,$A$1:$A$200000,0,1)

khoảng 0,3 giây

=XMATCH(C1,$A$1:$A$200000,0,2)

khoảng 0,04 giây

=XLOOKUP(C1,$A$1:$A$200000,$A$1:$A$200000,"",-1)

từ vài chục giây đến khoảng 100 giây

=XLOOKUP(C1,$A$1:$A$200000,$A$1:$A$200000,"",-1,2)

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.