Tìm kiếm thông tin trên các bảng khác nhau là một trong những tác vụ phổ biến nhất trong Excel. Đáng tiếc, hàm VLOOKUP cổ điển lại khét tiếng về sức mạnh xử lý và tính linh hoạt. Không có gì ngạc nhiên khi trong những năm qua, người dùng Excel đã đưa ra các giải pháp của riêng họ, chẳng hạn như công thức INDEX MATCH. May mắn thay, Microsoft cuối cùng đã nhận ra rằng hàm VLOOKUP có quá nhiều điểm yếu và phát hành một phiên bản kế nhiệm mạnh mẽ hơn – hàm XLOOKUP. Vì vậy, cái nào là tốt nhất để sử dụng?
Nếu bảng của bạn chỉ có vài chục hàng, thì rất có thể bất kỳ phương pháp nào cũng sẽ đủ nhanh và bạn sẽ không nhận thấy sự khác biệt. Nhưng nếu số hàng được đo bằng hàng nghìn, thì việc lựa chọn đúng hàm là rất quan trọng – sự khác biệt về hiệu suất có thể hơn 10 lần! Vì vậy, chúng ta hãy bắt đầu hẹn giờ và xem ai là người nhanh nhất 
Nguồn dữ liệu
Trong tất cả các ví dụ, chúng tôi cũng đang sử dụng tập dữ liệu sau:
- Bảng chính chứa 500.000 hàng
- Bảng tra cứu chứa 500 hàng
Mục tiêu của chúng tôi là khớp tên các mặt hàng và kéo giá từ bảng tra cứu vào bảng chính.
Đối với mỗi phương pháp, chúng tôi sẽ nhập công thức vào C2 và sao chép nó xuống thông qua C500001, đo thời gian Excel cần để tính nửa triệu ô.
Đương nhiên, kết quả sẽ phụ thuộc vào nhiều yếu tố như hiệu suất CPU, dung lượng RAM, phiên bản Excel, v.v. Trong thử nghiệm này, không phải con số tuyệt đối mới là vấn đề. Điều quan trọng hơn là phải hiểu hiệu suất của từng công thức so sánh, ưu điểm và nhược điểm của chúng.
Các ví dụ của chúng tôi giả định rằng bạn có kiến thức cơ bản về các hàm và chúng tôi sẽ không tập trung nhiều vào cú pháp của chúng. Liên kết đến các hướng dẫn chuyên sâu được bao gồm để thuận tiện cho bạn.
Tất cả các bài kiểm tra đều được thực hiện trên máy tính xách tay Dell của tôi có cài đặt Microsoft 365 Apps for Business; Excel 32-bit, phiên bản 2011, bản dựng 13415, Kênh Beta. Các bài kiểm tra tương tự cũng được thực hiện bởi đồng nghiệp của tôi trên Excel 64-bit và một số kết quả khác nhau đáng kể!
Công thức VLOOKUP
Khi nói đến việc tìm kiếm và truy xuất dữ liệu phù hợp trong Excel, chức năng đầu tiên được nghĩ đến là hàm VLOOKUP cũ tốt. Trong thực tế, nó không phải là tốt như nó có thể được, nhưng chúng ta sẽ nói chuyện này một chút sau
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Để kéo giá từ bảng tra cứu (E2: F501) sang bảng chính (A2: B500001), chúng tôi xác định các đối số sau cho công thức VLOOKUP của chúng tôi:
- Lookup_value : B2 – giá trị cần tìm trong bảng tra cứu.
- Table_array : $ E $ 3: $ F $ 501 – vui lòng lưu ý rằng chúng tôi khóa các tham chiếu bằng dấu $ để chúng không thay đổi khi sao chép công thức.
- Col_index_num : 2 – dữ liệu sẽ được truy xuất từ cột thứ 2 của bảng tra cứu.
- Range_lookup : FALSE – tìm kiếm đối sánh chính xác.
Công thức hoàn chỉnh là:
=VLOOKUP(B2, $E$3:$F$501, 2, FALSE)
Công thức trên chuyển đến C2, sau đó chúng ta nhấp đúp vào dấu cộng ở góc dưới bên phải để sao chép công thức trên toàn bộ cột. Tại thời điểm nhấp đúp, chúng tôi chạy đồng hồ bấm giờ và thấy rằng phép tính này mất 6,6 giây.
Khi thực hiện Vlookup trong các trang tính thực, nhiều người cung cấp toàn bộ cột cho table_array để phục vụ cho việc bổ sung có thể trong tương lai:
=VLOOKUP(B2, E:F, 2, FALSE)
Nó có ảnh hưởng đến hiệu suất không? Có, nó có. Mất 14,2 giây để tính toán toàn bộ cột. Điều đó thật khó tin, vì vậy tôi đã kiểm tra lại. Kết quả tương tự – ít hơn một nửa tốc độ của các dãy.
Ngoài sự nhanh chóng, công thức của bạn cũng cần phải mạnh mẽ và bền, phải không? Đáng tiếc, VLOOKUP không thể tự hào về độ tin cậy và khả năng phục hồi.
Giới hạn của hàm VLOOKUP
Như đã đề cập, hàm VLOOKUP trong Excel có một số ràng buộc khó chịu. Những điều cần thiết nhất là:
- Không thể nhìn vào bên trái của nó. Hàm VLOOKUP chỉ có thể tìm ở cột ngoài cùng bên trái của mảng bảng và trả về thông tin từ bên phải.
- Không tồn tại khi chèn hoặc xóa cột . Vì cột trả về được chỉ định dưới dạng số chỉ mục, công thức VLOOKUP ngừng hoạt động ngay sau khi cột mới được thêm vào hoặc xóa khỏi mảng bảng.
- Giá trị tra cứu được giới hạn trong 255 ký tự.
Thời gian tính toán : phạm vi – 6,6 giây; toàn bộ cột – 14,2 giây.
Công thức INDEX MATCH
Đối với nhiều người dùng, một dạng tra cứu nâng cao trong Excel là công thức INDEX MATCH. Ở dạng chung, nó trông như thế này:
INDEX (return_column, MATCH (lookup_value, lookup_column, 0))
Đối với tập dữ liệu của chúng tôi, công thức có dạng sau:
=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))
Như với hàm VLOOKUP, hãy nhớ sử dụng tham chiếu tuyệt đối cho phạm vi tra cứu và trả về để đảm bảo rằng công thức sao chép chính xác vào các ô bên dưới.
Với cả hai bảng trên cùng một trang tính, INDEX MATCH hoạt động chậm hơn nhiều so với hàm VLOOKUP (8,9 so với 6,6 giây).
Nhưng nếu chúng ta di chuyển bảng tra cứu sang một trang tính khác , công thức sẽ bắt đầu hoạt động nhanh hơn nhiều (~ 5 giây), tốt hơn so với hàm VLOOKUP.
Dù sao, INDEX MATCH tạo nên thời gian với một số lợi ích quan trọng.
Ưu điểm của INDEX MATCH
- Có thể nhìn từ phải sang trái . Đúng vậy, công thức INDEX MATCH không quan tâm đến vị trí của cột tra cứu, bởi vì không giống như hàm VLOOKUP, nó xác định rõ ràng một phạm vi tra cứu chứ không phải một mảng bảng.
- Miễn dịch với việc chèn và xóa cột. Với INDEX MATCH, bạn có thể thêm và xóa các cột một cách an toàn vì bạn chỉ định một phạm vi trả về chứ không phải một số chỉ mục.
- Không có giới hạn cho kích thước của giá trị tra cứu. Trong khi hàm VLOOKUP bị giới hạn ở 255 ký tự, hàm INDEX MATCH không gặp vấn đề gì với việc xử lý các chuỗi dài hơn.
- Có thể thực hiện Vlookup với nhiều tiêu chí như trong ví dụ liên kết ở trên.
- Có thể thực hiện tra cứu 2 chiều và trả về giá trị tại giao điểm của một hàng và cột cụ thể.
Thời gian tính toán : phạm vi – 8,9 giây; toàn bộ cột – 17,7 giây.
Hướng dẫn chuyên sâu : Công thức INDEX MATCH trong Excel
Công thức OFFSET MATCH
Đây là một công thức khác để tra cứu theo chiều dọc trong Excel, không có nhiều hạn chế của hàm VLOOKUP:
OFFSET(lookup_table, MATCH(lookup_value, OFFSET(lookup_table, 0, n, ROWS(lookup_table), 1) ,0) -1, m, 1, 1)
Ở đâu:
- n – là độ lệch cột tra cứu xác định có bao nhiêu cột để di chuyển từ đầu bảng đến cột tra cứu.
- m – là độ lệch cột trả về xác định có bao nhiêu cột để di chuyển đến cột trả về.
Trong trường hợp của chúng tôi, độ lệch cột tra cứu (n) là 0 vì chúng tôi đang tìm kiếm trong cột đầu tiên, do đó không cần thay đổi. Độ lệch cột trả về (m) là 1 vì các kết quả phù hợp ở cột thứ hai và chúng ta cần di chuyển 1 cột sang bên phải để đến được chúng:
=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, ROWS($E$2:$F$501), 1), 0) -1, 1, 1, 1)
So với các giải pháp trước đây, công thức quá rườm rà phải không? Tuy nhiên, nó nhanh hơn nhiều so với hàm VLOOKUP hoặc INDEX MATCH. Tính toán 500 nghìn hàng chỉ mất chưa đầy 3 giây! Trên toàn bộ các cột, OFFSET chỉ chậm hơn một chút – 3,5 giây.
=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, ROWS(E:F), 1), 0) -1, 1, 1, 1)
Tuy nhiên, trong Excel 64-bit, kết quả không quá ấn tượng – khoảng 7,5 giây. Tại sao vậy? Một câu hỏi hay cho các bạn Microsoft
Ưu điểm của OFFSET MATCH
Ngoài tốc độ, công thức này còn có một số ưu điểm khác:
- Có thể thực hiện một Vlookup bên trái và Hlookup trên .
- Có thể thực hiện tra cứu hai chiều dựa trên giá trị cột và hàng.
- Không bị vỡ khi cột được chèn vào hoặc bị xóa khỏi bảng tra cứu.
Hạn chế của OFFSET MATCH
Cú pháp phức tạp.
Thời gian tính toán : phạm vi – 2,9 giây; toàn bộ cột – 3,5 giây.
Công thức XLOOKUP
Người đăng ký Microsoft 365 được cung cấp một chức năng mới và mạnh mẽ hơn để tra cứu thông tin trong trang tính của họ:
XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])
Đối với mục đích của chúng tôi, các giá trị mặc định của 3 đối số tùy chọn cuối cùng hoạt động tốt, vì vậy chúng tôi chỉ xác định 3 tham số đầu tiên là bắt buộc. Tên của các đối số rất trực quan và tôi tin rằng bạn có thể hiểu công thức mà không cần giải thích thêm:
=XLOOKUP(B2, $E$2:$E$501, $F$2:$F$501)
Ưu điểm của XLOOKUP
So với hàm VLOOKUP truyền thống, hàm XLOOKUP có nhiều cải tiến như:
- Cú pháp đơn giản và có ý nghĩa hơn
- Khả năng tra cứu theo chiều dọc và chiều ngang theo bất kỳ hướng nào: phải, trái, từ dưới lên hoặc lên trên.
- Đối với dữ liệu được sắp xếp, nó có chế độ tìm kiếm nhị phân đặc biệt nhanh hơn rất nhiều so với tìm kiếm thông thường.
- Tìm kiếm theo thứ tự ngược lại để có lần xuất hiện cuối cùng.
- Khả năng trả về nhiều giá trị .
- Vlookup với nhiều tiêu chí .
- Chức năng lỗi Inbuilt If .
Hạn chế của XLOOKUP
XLOOKUP chỉ khả dụng trong Excel 365. Trong Excel 2019, Excel 2016 và các phiên bản cũ hơn, nó không được hỗ trợ.
Và bây giờ, chúng ta hãy xem chức năng mới này nhanh như thế nào. 11,2 giây – khá thất vọng 
Điều gì sẽ xảy ra nếu chúng tôi sử dụng tham chiếu cột thay vì phạm vi?
=XLOOKUP(B2, E:E, F:F)
24,5 giây. Không có lời nào… Chậm gần gấp đôi so với hàm VLOOKUP.
Thời gian tính toán : khoảng – 11,2 giây; toàn bộ cột – 24,1 giây.
Vlookup trong bảng Excel
Như bạn có thể biết, dữ liệu trong bảng Excel có thể được tham chiếu theo một cách đặc biệt – bằng cách sử dụng tên bảng và cột thay vì địa chỉ ô. Đây được gọi là tham chiếu có cấu trúc và tôi tự hỏi liệu nó có ảnh hưởng gì đến tốc độ tính toán hay không.
Để kiểm tra điều này, hãy chuyển đổi phạm vi thành bảng và kiểm tra lại công thức của chúng tôi.
Để thuận tiện, các bảng của chúng tôi được đặt tên là Main_table (A1: C500001) và Lookup_table (E1: F5001).
Để tạo tham chiếu bảng, hãy bắt đầu nhập công thức vào ô đầu tiên (C2), chọn ô và phạm vi bạn muốn tham chiếu đến và Excel sẽ tự động chèn tham chiếu có cấu trúc.
Ví dụ, đây là cách công thức VLOOKUP trông như thế nào:
=VLOOKUP([@Item], Lookup_table, 2, FALSE)
Một tính năng tuyệt vời của bảng Excel là ngay sau khi bạn nhập một công thức vào chỉ một ô, nó sẽ được điền ngay vào tất cả các ô khác trong cùng một cột. Bên cạnh đó, bảng có tính chất động và tự động mở rộng để bao gồm bất kỳ dữ liệu mới nào mà bạn nhập vào bên cạnh bảng.
Trong bảng của chúng tôi, công thức VLOOKUP được tính trong 2,3 giây, INDEX MATCH trong 2,6 giây, OFFSET và MATCH trong 2,7 giây và XLOOKUP trong 3,3 giây. Như bạn thấy, tốc độ tính toán tăng đáng kể so với phạm vi.
Các công thức được liệt kê dưới đây để bạn tham khảo:
=INDEX(Lookup_table[Price], MATCH([@Item], Lookup_table[Item], 0))
=OFFSET(Lookup_table, MATCH([@Item], OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)
=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[Price])
Điều thú vị là các bảng trong Excel rất nhanh ngay cả với các tham chiếu thông thường . Nghĩa là, nếu bạn chỉ chuyển đổi phạm vi đầu tiên (A1: C500001) thành một bảng và sử dụng công thức VLOOKUP thông thường để lấy dữ liệu từ phạm vi tra cứu , toàn bộ cột trong bảng chính sẽ được tính trong khoảng 2,5 giây!
Tốc độ tính toán : từ 2,3 đến 3,3 giây, tùy thuộc vào công thức.
Calculation speed: from 2.3 to 3.3 seconds, depending on the formula.
End-to-end tutorials:
Vlookup với mảng động
Thay đổi đột phá trong công cụ tính toán Excel 365 xảy ra vào tháng 1 năm 2020 đã thêm hỗ trợ cho cái gọi là mảng động . Tóm lại, đây là các mảng có thể thay đổi kích thước, tính toán tự động và trả về giá trị vào nhiều ô dựa trên công thức được nhập vào một ô.
Một trong những điều tốt nhất về mảng động là chúng có thể được sử dụng với hầu hết mọi hàm Excel truyền thống. Đối với công thức VLOOKUP của chúng tôi, nó sẽ giống như sau:
=VLOOKUP(B2:B500001, E2:F500001, 2, FALSE)
Sự khác biệt với hàm VLOOKUP cổ điển là bạn cung cấp toàn bộ mảng tra cứu cho đối số đầu tiên, không phải một giá trị tra cứu duy nhất. Bởi vì công thức được nhập chỉ trong một ô, bạn không cần phải lo lắng về việc khóa các phạm vi có tham chiếu tuyệt đối.
Về hiệu suất, mảng động hoạt động nhanh hơn cả bảng Excel! Nửa triệu ô được lấp đầy với kết quả gần như ngay lập tức: 1,8 giây – rất ấn tượng!
Các kết quả khác được liệt kê dưới đây:
CHỈ SỐ TRẬN ĐẤU – 4,4 giây
=INDEX(F2:F501, MATCH(B2:B500001, E2:E501, 0))
XLOOKUP – 7,3 giây
=XLOOKUP(B2:B500001, E2:E501, F2:F501)
Hmm… XLOOKUP được cho là năng động theo thiết kế lại hoạt động kém hơn các chức năng cũ hơn. Kỳ dị!
Tốc độ tính toán : từ 1,8 đến 7,3 giây, tùy thuộc vào công thức.
Kéo các trận đấu với Power Query
Để hoàn thiện, hãy thử nghiệm thêm một giải pháp khả thi cho nhiệm vụ của chúng ta – Power Query. Tất nhiên, không hoàn toàn chính xác khi so sánh việc tính toán các công thức với việc cập nhật truy vấn, nhưng tôi chỉ tò mò rằng cái nào nhanh hơn
Các bước chi tiết của việc sử dụng Power Query được mô tả trong một hướng dẫn riêng được đề cập bên dưới. Ở đây, chúng tôi sẽ chỉ đánh giá kết quả:
Bảng đã hợp nhất đã tải từ Power Query Editor vào Excel trong 8,5 giây. Không giống như công thức, truy vấn không cập nhật tự động. Sau mỗi lần thay đổi dữ liệu nguồn, bạn phải cập nhật bảng kết quả theo cách thủ công bằng cách nhấp vào nút Làm mới trên tab Dữ liệu hoặc Truy vấn . 500.000 hàng của chúng tôi được làm mới trong khoảng 7 giây. Không tệ, nhưng các công thức Excel có thể làm tốt hơn. Xem xét việc thiết lập truy vấn không phải là quá trình một cú nhấp chuột, đây có lẽ là phương pháp cuối cùng tôi sẽ sử dụng, chỉ khi không có gì khác hoạt động.
Hiệu suất : tải sang Excel 8,5 giây; làm mới 7,6 giây
Extra bonus: Merge Tables Wizard
Phần thưởng bổ sung: Hợp nhất bảng Wizard
Người dùng Ultimate Suite của chúng tôi có thêm một công cụ trong hộp công cụ Excel của họ để hợp nhất hai bảng dựa trên một cột chung. Hãy xem nó so sánh như thế nào với Excel.
Để chạy Trình hướng dẫn Hợp nhất Bảng, hãy nhấp vào nút Hợp nhất Hai Bảng trên tab Dữ liệu Ablebits . Và sau đó, chỉ cần làm theo các bước của trình hướng dẫn, và nó sẽ hướng dẫn bạn qua quy trình.
Chà, công cụ này mất khoảng 3 giây để hoàn thành. Không quá tệ cho nửa triệu bản ghi!
Xem kỹ thông báo ở trên, bạn có thể nhận thấy rằng không phải tất cả các kết quả trùng khớp đều được tìm thấy. Tuy nhiên, nó không có nghĩa là công cụ này bị lỗi. Nó chỉ cho bạn biết rằng một số mục (giá trị tra cứu) không tồn tại trong bảng tra cứu. Hàm VLOOKUP trả về lỗi # N / A trong trường hợp này, trong khi Trình hướng dẫn hợp nhất bảng để trống một ô.
Hiệu suất : 3,2 giây
Tóm tắt và kết luận
Nếu bạn đọc kỹ tất cả các ví dụ, thì rất có thể bạn đã rút ra kết luận của riêng mình. Nếu bạn bỏ qua chi tiết, thì bạn có thể tìm thấy tóm tắt nhanh trong bảng so sánh này:
| Function |
Calculation speed in seconds |
| Ranges |
Entire columns |
Table |
Dynamic arrays |
| VLOOKUP |
6.6 |
14.2 |
2.3 |
1.8 |
| INDEX MATCH |
8.9 |
17.7 |
2.6 |
4.4 |
| OFFSET MATCH |
2.9 |
3.5 |
2.7 |
– |
| XLOOKUP |
11.2 |
24.1 |
3.3 |
7.3 |
| Power Query |
8.5 |
| Merge Tables |
3.2 |
Dưới đây là một vài nhận xét mà tôi thực hiện dựa trên kết quả thử nghiệm. Có lẽ, chúng cũng sẽ hữu ích cho bạn.
- Bất chấp tất cả những hạn chế và nhược điểm của nó, VLOOKUP hoạt động khá tốt, đặc biệt là với các mảng động.
- INDEX MATCH không nhanh như mong đợi. Đối với tôi, điều này có vẻ rất lạ vì nó xử lý các cột riêng lẻ, không phải một mảng bảng như hàm VLOOKUP.
- XLOOKUP có nhiều khả năng tuyệt vời nhưng chậm hơn so với VLOOKUP và INDEX MATCH trên các tập dữ liệu khổng lồ. Hy vọng rằng Microsoft sẽ cải thiện hiệu suất của nó trong các phiên bản tương lai.
- OFFSET MATCH là nhanh nhất trong Excel 32-bit. Nhưng vì cú pháp phức tạp của nó, rất có thể xảy ra sai sót. Ngoài ra, nó không hoạt động với mảng động, ít nhất tôi đã không thể buộc nó.
- Không có ý nghĩa để tính toán toàn bộ các cột trừ khi thực sự cần thiết. Điều này làm cho công thức chậm hơn hai lần.
- Bảng Excel tuyệt vời! Để tận dụng tối đa Excel của bạn, hãy sử dụng chúng bất cứ khi nào có thể.
- Mảng động là tương lai.
Xin lưu ý rằng những quan sát này dựa trên các thử nghiệm của tôi trong Excel 365 động , tôi không có cơ hội thử nghiệm trong các phiên bản khác. Nếu bạn đã làm, nhận xét của bạn được hoan nghênh và sẽ được đánh giá rất cao!
Ghi chú. Đừng bỏ qua bước này vì về sau thư mục này sẽ đóng vai trò đầu mối trong trao đổi thông tin.
