Hướng dẫn XMATCH trong Excel: Tổng quan, Cú pháp và Ứng dụng Thực tiễn

Trong quá trình phân tích dữ liệu, có rất nhiều tình huống bạn cần tìm vị trí tương đối của một mục trong một mảng hoặc phạm vi ô dựa trên một giá trị hoặc mẫu nhất định. 

Cách hiệu quả nhất để thực hiện các thao tác khớp mẫu trong Excel là sử dụng hàm XMATCH. Đơn giản nhưng mạnh mẽ, XMATCH có nhiều tính năng cho phép khớp chính xác và linh hoạt. 

Trong hướng dẫn này, chúng ta sẽ tìm hiểu những điều cơ bản về hàm XMATCH trong Excel, phân tích cú pháp và các tham số khác nhau, đồng thời cung cấp các ví dụ thực tiễn để minh họa khả năng của nó. Chúng ta cũng sẽ so sánh XMATCH với MATCH, một hàm phổ biến khác dùng cho các tác vụ tương tự. Cuối cùng, chúng ta sẽ xem các lỗi thường gặp khi sử dụng XMATCH và đưa ra các thực hành tốt để đảm bảo sử dụng hiệu quả và chuẩn xác.

Nếu bạn đang trên hành trình học Excel và muốn tìm hiểu thêm, hãy xem lộ trình kỹ năng Excel Fundamentals để học mọi điều thiết yếu. 

XMATCH là gì?

XMATCH là một hàm linh hoạt và mạnh mẽ cho các thao tác khớp. XMATCH() cho phép bạn thực hiện mọi kiểu khớp, từ tra cứu dọc và ngang đến khớp chính xác, xấp xỉ và khớp một phần. 

Đối với những người đã dùng Excel lâu năm, hẳn bạn quen với hàm MATCH. Trước khi có bản cập nhật giới thiệu XMATCH, MATCH là hàm mặc định cho các tác vụ khớp. Bạn có thể tìm hiểu về chúng trong hướng dẫn về các hàm tra cứu trong Excel. 

Tuy nhiên, như chúng ta sẽ thấy, MATCH có thể hạn chế trong một số trường hợp. Ở khía cạnh này, XMATCH có thể được xem là phiên bản nâng cấp được mong đợi từ lâu của MATCH truyền thống, cung cấp khả năng tìm kiếm theo mọi hướng và nhiều kiểu khớp, giúp dễ dùng hơn và phù hợp với nhiều tình huống hơn.

Lưu ý hàm này chỉ có trong Excel for Microsoft 365 và Excel 2021. Với các phiên bản Excel cũ hơn, MATCH vẫn là hàm duy nhất có sẵn.

Giờ hãy cùng xem cú pháp và tham số của XMATCH. 

Cú pháp và Tham số của XMATCH

Đây là cú pháp cơ bản của hàm XMATCH:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Được hỗ trợ bởi

Hàm có hai đối số bắt buộc (tức là lookup_value và lookup_array) và hai đối số tùy chọn (match_mode và search_mode) để cho phép thao tác chính xác và linh hoạt hơn.

Dưới đây là mô tả về bốn tham số:

• Lookup_value: Giá trị cần tìm.
• Lookup_array: Mảng hoặc danh sách để tìm kiếm. Có thể là mảng tra cứu dọc hoặc ngang.
• Match_mode: cho phép bạn chọn kiểu khớp. Có bốn chế độ gồm:
• 0: Khớp chính xác (mặc định)
• -1: Khớp chính xác hoặc mục nhỏ hơn gần nhất
• 1: Khớp chính xác hoặc mục lớn hơn gần nhất
• 2: Khớp ký tự đại diện, như * và ?.
• Search_mode: cho phép bạn chỉ định chế độ tìm kiếm, gồm hướng tìm và thuật toán. Có bốn giá trị:
• 1: Tìm từ đầu đến cuối (mặc định)
• -1: Tìm ngược từ cuối lên đầu.
• 2:  Tìm kiếm nhị phân tăng dần. Việc tìm được thực hiện giả định mảng tra cứu đã được sắp xếp tăng dần, nếu không sẽ trả về kết quả không hợp lệ.
• -2:  Tìm kiếm nhị phân giảm dần. Việc tìm được thực hiện giả định mảng tra cứu đã được sắp xếp giảm dần, nếu không sẽ trả về kết quả không hợp lệ.

Chế độ khớp: Độ chính xác và Tính linh hoạt

XMATCH có tham số tùy chọn match_mode cho phép bạn chọn kiểu khớp. Các tùy chọn này rất hữu ích, cung cấp cách thực hiện cả khớp chính xác lẫn khớp xấp xỉ. Hãy phân tích chi tiết các chế độ khớp có sẵn:

Khớp chính xác

Khớp chính xác (match_mode = 0) là chế độ mặc định của XMATCH. Nghĩa là Excel sẽ tìm giá trị khớp chính xác của giá trị tra cứu trong mảng tra cứu, nếu không sẽ trả về lỗi NA. Khớp chính xác là thiết yếu khi độ chính xác là then chốt, chẳng hạn tìm các mục cụ thể trong cơ sở dữ liệu.

Dưới đây là ví dụ cơ bản về cách dùng XMATCH để tìm vị trí chính xác của “Excel” trong danh sách công nghệ có trong Danh mục khóa học DataCamp. Chúng ta cung cấp giá trị tra cứu ở D3 và mảng tra cứu (A3:A14), và Excel trả về đúng vị trí 6. 

Khớp xấp xỉ

Có thể có những tình huống bạn cần khớp linh hoạt, hoặc vì bạn không biết chính xác giá trị tra cứu, hoặc vì bạn đang tìm kết quả xấp xỉ. 

May mắn thay, XMATCH có hai chế độ khớp cho phép bạn thực hiện khớp chính xác hoặc mục nhỏ hơn gần nhất và khớp chính xác hoặc mục lớn hơn gần nhất. 

Trong ví dụ sau, chúng ta dùng khớp xấp xỉ để tìm số lượng khóa học gần nhất với giá trị tra cứu 15. 

Khi dùng mục nhỏ hơn gần nhất (match_mode = -1), Excel trả về vị trí 6, vì 12 khóa học là số nhỏ hơn gần nhất trong danh sách. Với mục lớn hơn gần nhất, Excel trả về 5, vì 16 khóa học là số lớn hơn gần nhất.

Chúng ta cũng dùng khớp chính xác để minh họa điều gì xảy ra khi Excel không tìm được khớp chính xác.

Khớp ký tự đại diện và regex

Một tính năng mới trong XMATCH là khả năng dùng các ký tự đại diện cơ bản khi đặt match_mode bằng 2, cũng như các biểu thức chính quy nâng cao nếu đặt bằng 3. Các chế độ khớp này chỉ hoạt động với dữ liệu dạng văn bản.

XMATCH cho phép bạn dùng hai ký tự đại diện phổ biến:

• ?: khớp bất kỳ một ký tự đơn nào.
• *: khớp bất kỳ chuỗi ký tự nào.

Dưới đây là các ví dụ về cách dùng khớp regex. Ở ví dụ đầu, chúng ta tìm các công nghệ bắt đầu bằng chữ E. Tiếp theo, chúng ta tìm các công nghệ có chữ A trong tên. Cuối cùng, chúng ta tìm các công nghệ có ba chữ cái.

Lưu ý XMATCH sẽ luôn trả về lần xuất hiện đầu tiên trong mảng tra cứu, trừ khi chúng ta chỉ định khác đi (xem phần tiếp theo).

Nếu bạn muốn dùng các biểu thức chính quy phức tạp hơn, hãy đặt match_mode bằng 3. Biểu thức chính quy, thường viết tắt là "regex" hoặc "regexp", là cách định nghĩa một mẫu tìm kiếm, có thể dùng cho nhiều tác vụ xử lý văn bản như tìm kiếm, phân tích và/hoặc thay thế văn bản. 

Để tìm hiểu thêm về khớp mẫu, Hướng dẫn Excel Regex: Làm chủ khớp mẫu với Biểu thức Chính quy là điểm khởi đầu tuyệt vời.

Đây là một ví dụ khá đơn giản: Chúng ta muốn tìm vị trí của một khóa học có mã gồm ba chữ cái ở đầu, ba chữ cái ở cuối và hai chữ số ở giữa (31).

Chế độ tìm kiếm của XMATCH: Hướng và Hiệu suất

XMATCH giới thiệu tham số mới cho phép bạn kiểm soát hướng tìm kiếm. Tính năng này rất hữu ích khi bạn quan tâm đến lần xuất hiện cuối cùng hơn là lần đầu tiên, ví dụ trong một số tác vụ phân tích chuỗi thời gian. 

Hãy cùng xem chi tiết các chế độ tìm kiếm.

Tìm ngược

Để thực hiện tìm ngược, bạn có thể đặt tham số search_mode bằng -1. Ví dụ, dưới đây là cách tìm khóa học đầu tiên và cuối cùng mà người dùng Peter đã hoàn thành.

Tìm kiếm nhị phân

Cuối cùng, tham số search_mode cũng cho phép dùng thuật toán tìm kiếm nhị phân để thực hiện thao tác khớp. Tìm kiếm nhị phân là một thuật toán sắp xếp hoạt động bằng cách liên tục chia đôi khoảng tìm kiếm. Đây là một trong những thuật toán hiệu quả nhất, nhưng yêu cầu dữ liệu đầu vào phải được sắp xếp trước. 

XMATCH cho phép bạn tận dụng tìm kiếm nhị phân khi mảng được sắp xếp tăng dần (search_mode = 2) hoặc giảm dần (search_mode = -2). Nếu mảng tra cứu không được sắp xếp, hàm sẽ báo lỗi.

Ứng dụng Thực tiễn và Ví dụ về XMATCH

XMATCH phù hợp với nhiều tác vụ Excel, từ cơ bản đến nâng cao. Phần này đề cập một số ứng dụng thực tiễn nổi bật.

Tra cứu hai chiều với INDEX/XMATCH

Hai hàm INDEX và XMATCH có thể phối hợp để thực hiện tra cứu hai chiều, tức tra cứu đồng thời theo hàng và cột. Bằng cách kết hợp hai hàm này, chúng ta có thể tạo tra cứu động rất hữu ích trong các lĩnh vực như phân tích tài chính, nơi việc so sánh biến theo danh mục rất phổ biến. 

Ví dụ, trong hình dưới, chúng ta kết hợp INDEX và MATCH để trích số người dùng ghi danh vào các khóa học Excel trong quý 2. INDEX nhận ba đối số: mảng cần tìm giá trị, số hàng và số cột. Giá trị của hai đối số sau được cung cấp bởi nhiều hàm XMATCH.

 Nếu muốn thay đổi công nghệ hoặc Quý, bạn chỉ cần đổi các giá trị tra cứu trong các hàm XMATCH, và kết quả của INDEX sẽ thay đổi theo.

Cặp INDEX/XMATCH hoạt động rất giống INDEX/MATCH. Nếu bạn muốn biết thêm về cặp mạnh mẽ này cùng ví dụ thực tế, hãy xem hướng dẫn cách INDEX MATCH nhiều tiêu chí trong Excel.

Tra cứu đa cột

Một cách dùng hay khác của XMATCH là kết hợp nhiều tiêu chí để tạo tra cứu đa cột. Điều này có vẻ không trực quan, vì XMATCH cần một giá trị tra cứu và một mảng tra cứu để hoạt động. Nhưng chúng ta có thể dùng logic Boolean để tạo một mảng tạm thời trong công thức XMATCH chứa các số 0 và 1 để biểu thị các hàng khớp với tiêu chí bạn chỉ định. 

Ví dụ, giả sử bạn muốn tìm công nghệ đầu tiên trong danh sách có dưới 1000 người ghi danh mỗi quý. Bạn có thể kết hợp các điều kiện bằng cách kết hợp kết quả ở mỗi cột với ký hiệu *. Excel sẽ xác định ngầm mảng chứa toàn số 1, trong trường hợp này là Tableau, do đó vị trí là 5.

Lọc mảng động

XMATCH cũng có thể kết hợp với các hàm FILTER và ISNUMBER để tăng cường lọc mảng động.

Dưới đây là cách hoạt động. Chúng ta sẽ dùng các hàm này để lọc thông tin về các khóa học người dùng đã hoàn thành dựa trên tên người dùng. 

Đầu tiên, chúng ta dùng XMATCH để tính vị trí tương đối của từng người dùng trong bảng dựa trên mảng tra cứu chứa các tên sẽ dùng để lọc. Điều này sẽ cho một bảng với vị trí tương đối của từng tên, bao gồm giá trị null cho những người dùng không có trong danh sách lọc.

Sau đó, chúng ta chuyển cột chứa vị trí tương đối và giá trị null thành TRUE hoặc FALSE bằng hàm ISNUMBER.

Cuối cùng, chúng ta dùng FILTER để chọn tất cả các cột muốn hiển thị, và dùng cột chứa TRUE và FALSE để lọc hàng.

Xử lý giá trị null với ISNA

Khi XMATCH không tìm được khớp, hàm sẽ trả về lỗi NA. Dù điều này đã mang tính thông tin, bạn có thể làm kết quả rõ ràng hơn và tránh hiểu sai bằng cách dùng các hàm ISNA và IF.

Hãy tưởng tượng chúng ta có bảng các khóa học Sam và Peter đã hoàn thành, và muốn biết họ có những khóa học chung nào. Nếu chỉ dùng XMATCH, Excel sẽ trả về vị trí tương đối của các khóa học chung, và giá trị null cho trường hợp còn lại. Nếu chúng ta truyền XMATCH làm đối số cho ISNA, hàm sẽ trả về TRUE hoặc FALSE. Sau đó, với hàm IF, ta có thể thay các giá trị boolean bằng thông điệp khác.

Tìm kiếm nhị phân cho bộ dữ liệu lớn

Chúng ta đã đề cập XMATCH cho phép dùng thuật toán tìm kiếm nhị phân trong các thao tác khớp. Tính năng này có thể tạo khác biệt lớn khi làm việc với bộ dữ liệu lớn. Miễn là mảng tra cứu đã được sắp xếp, lựa chọn bộ máy tìm kiếm nhị phân có thể tăng tốc đáng kể các thao tác khớp của bạn.

XMATCH so với MATCH: Những khác biệt chính

Như đã đề cập, XMATCH được thiết kế để thay thế hàm MATCH truyền thống. Cả hai có cùng mục đích, nhưng XMATCH có thêm nhiều khả năng mới, khiến nó mạnh hơn MATCH. Dưới đây là danh sách các khác biệt chính giữa XMATCH và MATCH:

Lỗi thường gặp và Cách khắc phục

XMATCH có rất nhiều cách sử dụng. Tuy nhiên, cần lưu ý những vấn đề thường gặp khi dùng. Dưới đây là danh sách các lỗi phổ biến và mẹo xử lý.

• Kiểm tra kiểu dữ liệu: XMATCH sẽ có hành vi bất ngờ nếu bạn áp dụng cho phạm vi có kiểu dữ liệu không đúng. Do đó, trước khi áp dụng hàm, hãy đảm bảo bạn đã đặt đúng kiểu dữ liệu cho các cột.
• Tính nhất quán của phạm vi: Dù XMATCH cho phép phạm vi tra cứu ngang và dọc, việc trộn lẫn có thể gây vấn đề và giảm hiệu năng. Khi có thể, hãy nhất quán và chọn hoặc phạm vi ngang hoặc dọc.
• Tận dụng các chế độ khớp: XMATCH có nhiều chế độ khớp. Chế độ mặc định là khớp chính xác, nhưng bạn cũng có thể tinh chỉnh tra cứu bằng cách dùng tìm kiếm xấp xỉ và thao tác regex.
• Hiệu quả hơn với mảng đã sắp xếp: Nhìn chung, hiệu năng của XMATCH sẽ luôn cao hơn nếu bạn áp dụng cho mảng đã sắp xếp. Điều này đúng với hàm mặc định, và là điều kiện bắt buộc nếu bạn muốn dùng tìm kiếm nhị phân. 
• Xử lý lỗi N/A:  Như đã nói, XMATCH sẽ trả lỗi NA nếu không có khớp trong tra cứu. Cách tốt nhất để xử lý và tăng tính dễ đọc là truyền kết quả cho tổ hợp hàm IF và ISNA, hoặc dùng hàm IFNA.

Lưu ý về hiệu năng

XMATCH không phải là hàm duy nhất để thực hiện tra cứu trong Excel. Ví dụ, bạn có thể cân nhắc dùng VLOOKUP, HLOOKUP, hoặc thậm chí các hàm cũ như MATCH. Tuy nhiên, về tốc độ tìm kiếm, XMATCH có lẽ nhanh nhất, đặc biệt khi áp dụng cho các mảng đã sắp xếp, nơi bạn cũng có thể tận dụng sức mạnh của tìm kiếm nhị phân.

Cũng đáng lưu ý rằng XMATCH được thiết kế như một công thức mảng động, nghĩa là nó có thể trả về các mảng với kích thước thay đổi tùy dữ liệu đầu vào. Các công thức mảng động đặc biệt phù hợp khi làm việc với bộ dữ liệu lớn hoặc phép tính phức tạp. 

Kết luận

Chúc mừng bạn đã đọc đến cuối. XMATCH là một hàm mạnh mẽ, và bạn không thể bỏ lỡ trong các phân tích dữ liệu bằng Excel. Dù dùng độc lập hay kết hợp với các hàm Excel khác, XMATCH sẽ nâng tầm các thao tác tra cứu của bạn. 

Nhưng còn rất nhiều điều để khám phá trong thế giới Excel tuyệt vời. Hãy xem các khóa học, blog và hướng dẫn chuyên sâu để trở thành “phù thủy” Excel:

• XLOOKUP() vs. VLOOKUP(): So sánh dành cho người dùng Excel
• Cheat Sheet: Thao tác dữ liệu trong Excel 
• Excel | Khóa học Làm chủ Kỹ thuật Nâng cao
• Power Query Trung cấp trong Excel 
• Cheat Sheet: Phím tắt Excel 
• Cheat Sheet: Công thức Excel 
• Cách INDEX MATCH nhiều tiêu chí trong Excel 
• Xử lý dữ liệu với INDEX-MATCH trong bảng tính 
• Hướng dẫn Excel Regex: Làm chủ khớp mẫu với Biểu thức Chính quy