Hàm match trong excel là một trong những từ khóa được search nhiều nhất trên google về chủ đề thủ thuật excel. Trong bài viết này, Phanmemmienphi.vn sẽ viết bài viết Hướng dẫn toàn tập sử dụng hàm match trong excel mới nhất 2020.
Hướng dẫn toàn tập sử dụng hàm match trong excel mới nhất 2020
Trong bài hướng dẫn này, Học Excel Trực tuyến sẽ giải thích phương pháp dùng hàm MATCH cùng các gợi ý của hàm. cùng lúc, bạn sẽ học được phương pháp tăng trưởng hàm lookup của riêng mình bằng mẹo tạo hàm dựa trên VLOOKUP và MATCH
HÀM MATCH TRONG EXCEL, CÚ PHÁP VÀ mẹo sử dụng
Hàm MATCH trong Excel giúp search một trị giá cụ thể trong một dãy các ô, và mang ra vị trí tương đối của giá trị đó.
Cú pháp của hàm MATCH như sau:
- MATCH (lookup_value, lookup_array, [match_type])
- Lookup_value(bắt buộc): là giá trị bạn mong muốn tìm. có thể là số, chữ, giá trị logic hoặc ô tham chiếu
- Lookup_array (bắt buộc): là dãy các ô cần search
Match_type (tuỳ chọn): định hình loại kiếm tìm. có thể nhận một trong các trị giá sau: 1, 0, -1. Khi đặt match_type bằng 0, giá trị trả về là chuẩn xác, trong khi hai loại còn lại cho trị giá xấp xỉ.
- 1 hoặc không ghi gì (mặc định): tìm giá trị lớn nhất trong dãy search sao cho giá trị đó nhỏ hơn hoặc bằng giá trị search. Loại này yêu cầu sắp đặt dãy tìm kiếm theo thứ tự tăng dần, từ nhỏ nhất đến lớn nhất hoặc từ A đến Z
- 0: tìm trị giá đầu tiên trong dãy bằng đúng giá trị search. không yêu cầu sắp đặt dãy kiếm tìm
- -1: tìm giá trị nhỏ nhất trong dãy to hơn hoặc bằng trị giá search. Dãy search nên được sắp đặt theo thứ tự giảm dần, từ lớn nhất đến nhỏ nhất hoặc từ Z đến A.
Xem thêm: Hướng dẫn gạch chéo ô trong excel mới nhất 2020
Để hiểu hơn hàm MATCH, các bạn đủ nội lực tạo một công thức dễ dàng dựa trên dữ liệu sau: Tên học sinh ở cột A và điểm ở cột B, sắp đặt theo thứ tự giảm dần. Để tìm ra vị trí một học sinh cụ thể (ví dụ Hoàng), dùng phương thức sau:
=MATCH (“Hoàng”, A2:A8, 0)
Trong bức ảnh trên, tên học sinh được nhập vào dãy một mẹo ngẫu nhiên, do đó chúng ta đặt match_type bằng 0 vì loại này k yêu cầu bố trí trị giá trong dãy kiếm tìm. phương pháp MATCH cho chúng ta biết rằng Hoàng đứng ở vị trí thứ 7 trong dãy giá trị kiếm tìm.
4 ĐIỀN BẠN CẦN BIẾT VỀ HÀM MATCH
giống như bạn đang biết, dùng hàm MATCH trong Excel không hề khó. ngoài ra, bạn nên chú ý những điều sau đây:
- Hàm MATCH chỉ trả lại vị trí tương đối của trị giá cần tìm kiếm trong một dãy, chẳng hề trị giá của chính nó
- Hàm MATCH k phân biệt giữa chữ in hoa và k in hoa khi xử lý dữ liệu dạng chữ
- Nếu chuỗi tìm kiếm chứa một vài trị giá tìm kiếm, vị trí của trị giá trước tiên sẽ được trả về
- Nếu trị giá search k được tìm thấy trong chuỗi search, lỗi #N/A sẽ được trả về
áp dụng CỦA HÀM MATCH TRONG EXCEL
HÀM MATCH VỚI CÁC KÝ TỰ ĐẠI DIỆN
giống như các hàm Excel không giống, hàm MATCH hiểu những kí tự đại diện sau:
- Dấu hỏi (?): thay thế cho một ký tự
- Dấu hoa thị (*): thay thế cho một chuỗi các kí tự
Lưu ý: Ký tự đại diện chỉ được dùng tỏng phương thức hàm MATCH với match_type là 0.
phương pháp Match với kí tự đại diện được sử dụng trong trường hợp bạn mong muốn tìm vị trí của một vài kí tự hoặc một phần của chuỗi văn bản. Để minh hoạ rõ hơn, chúng ta hãy xem gợi ý sau
Giả sử bạn có một danh mục những người bán trong khu vực và doanh số sale của họ trong tháng vừa rồi. Bạn mong muốn tìm vị trí tương đối của một người bán nhất định trong danh mục nhưng k thể nhớ chính xác tên anh ta, nhưng bạn nhớ được một vài chữ cái trong tên của anh ấy.
Tên của người bán nằm trong dãy A2:A11, và bạn vừa mới kiếm tìm tên khởi đầu với từ “Ho”, công thức sẽ giống như sau:
=MATCH (“Ho*”, A2:A11, 0)
Để cách thức linh động hơn, bạn có thể phân tích trị kiếm tìm vào một vài ô (như ô E1 trong ví dụ này), và ghép ô đó với kí tự đại diện giống như sau
=MATCH (E1& “*”, A2:A11, 0)
giống như ảnh chụp bên dưới, phương pháp trả lại giá trị , là vị trí của “Hoàng”
Xem thêm: Tổng hợp cách sử dụng excel 2010 mới nhất 2020
Để thay thế một kí tự trong trị giá tìm kiếm, sử dụng kí tự đại diện “?” như sau
=MATCH (“m?nh”, A2:A11, 0)
cách thức trên hợp với tên Minh và chạy lại vị trí tương đối của nó là 5
HÀM MATCH PHÂN BIỆT KIỂU CHỮ
như vừa mới đề cập lúc đầu, hàm MATCH trong Excel không phân biệt được chữ in hoa và thường. Để tạo bí quyết Match phân biệt được hai loại kí tự này, các bạn nên dùng hàm MATCH phối hợp với hàm EXACT so sánh các ô chuẩn xác, gồm có dạng kí tự.
bí quyết phân biệt dạng chữ để trùng lặp với dữ liệu trong Excel là
=MATCH (TRUE, EXACT (dãy tìm kiếm, trị giá search), 0)
cách thức chạy được khi
- Hàm EXACT so sánh giá trị kiếm tìm với mỗi yếu tố của dãy tìm kiếm. Nếu ô được so sánh bằng chuẩn xác, hàm trả giá trị TRUE, trái lại là FALSE
- Sau đó, hàm MATCH so sánh TRUE (giá trị search của nó) với trị giá trong dãy được trả lại bởi hàm EXACT, và trả lại vị trí của giá trị trùng lặp trước hết.
Các bạn nên nhớ đây là bí quyết chuỗi nên các bạn cần phải ấn tổ hợp phím Ctrl + Shift + Enter
Giả sử giá trị search của bạn ở ô E1 và chuỗi search là A2:A9, công thức như sau
=MATCH (TRUE, EXACT (A2:A9, E1), 0)
ảnh chụp dưới đây minh hoạ cho công thức Match phân biệt dạng chữ trong Excel
SO SÁNH HAI CỘT TÌM SỰ GIỐNG VÀ không giống NHAU BẰNG HÀM ISNA/MATCH
Cú pháp giống như sau:
=IF (ISNA (MATCH (giá trị trước tiên trong cột 1, cột 2, 0)), “Không có trong cột 1”, “”)
so với bất kì giá trị nào của cột 2 mà không có trong cột 1, phương pháp trả lại là “Không có trong cột 1”. cho biết phương pháp này giống như sau:
- Hàm Match sẽ tìm từng trị giá ở cột 1 trong cột 2, nếu trị giá này được tìm thấy, hàm Match sẽ trả về vị trí tương đối của giá trị đó, nếu không tìm thấy, lỗi #N/A sẽ được trả về.
- Hàm ISNA rà soát nhìn thấy hiệu quả trả về từ hàm Match có phải là #N/A hay k. Nếu hàm ISNA trả về trị giá đúng, nghĩa là giá trị k được tìm thấy, hàm trả về là TRUE, trái lại là FALSE. Trong gợi ý này, TRUE nghĩa là một trị giá ở cột 1 không tìm thấy trong cột 2 do đó lỗi #N/A được trả về bởi hàm MATCH
- Việc nhìn thấy TRUE cho các giá trị không xuất hiện ở cột 1 đủ sức gây lầm lẫn cho người sử dụng, bạm có thể tận dụng hàm IF để hiển thị “Không có trong cột 1” hoặc bất cứ chữ nào bạn muốn
Giả sử bạn đang so sánh giá trị ở cột B với các trị giá trong cột A, công thức có dạng sau:
=IF (ISNA (MATCH (B2, A:A, 0)), “Không có trong cột 1”, “”)
Hàm MATCH trong Excel k phân biệt được dạng chữ. vì vậy, để phân biệt dạng chữ, ta gắn thêm hàm EXACT vào chuỗi search lookup_array, và nhớ ấn tổ hợp Ctrl + Shift + Enter
=IF (ISNA (MATCH (TRUE, EXACT (A:A, B2), 0)), “Không có trong cột 1”, “”)
ảnh chụp dưới đây cho thấy cả hai công thức:
xem thêm: hàm Excel cơ bản
HÀM VLOOKUP VÀ MATCH
Ở post này mặc định bạn vừa mới có kiếm kiến thức cơ bản về hàm VLOOKUP trong Excel.
Một trong những khuyết điểm lớn nhất của hàm VLOOKUP là nó dừng làm việc sau khi chèn hoặc xoá một cột trong bảng search. Đó là vì VLOOKUP dẫn một giá trị trùng dựa trên số của cột mà bạn định hình, Excel sẽ k thể điều chỉnh số khi một hay nhiều cột mới được thích hợp hoặc xoá giảm thiểu khỏi bảng.
Hàm MATCH được dùng để định hình vị trí tương đối của giá trị search, vì thế hoàn toàn thêm vào đối với col_index_num của hàm VLOOKUP. Nói hướng dẫn khác, thay vì chỉ rõ cột trả lại như một số không cải thiện, bạn dùng hàm MATCH để biết vị trí hiện tại của cột đó.
Để hiểu dễ dàng hơn, chúng ta hãy cùng xem lại ví dụ về điểm của học sinh nhưng lần này chúng ta sẽ gọi điểm của học sinh mà không hề vị trí tương đối như lần trước.
Giả sử giá trị kiếm tìm ở ô F1, dãy bảng là A1:C2, bí quyết giống như sau:
=VLOOKUP (F1, A1:C8, 3, FALSE)
Tham số thứ 3 (col_index_num) được đặt là 3 vì Điểm toán mà chúng ta muốn kéo nằm ở cột thứ 3 trong bảng. Trong ảnh dưới đây, hàm Vlookup làm việc bình thường
cho đến khi bạn chèn thêm hoặc xoá bớt cột
Vậy tại sao lỗi #REF lại xảy ra? Đó là vì tham số col_index_num khi được đặt bằng 3 đã thông báo cho Excel để lấy một giá trị từ cột thứ 3, trong khi hiện giờ lại chỉ có 2 cột trong bảng.
Để giải quyết chủ đề này, bạn có thể phát triển hàm Vlookup bằng cách thêm hàm Match:
MATCH (E2, A1:C1, 0)
Với:
- E2 là giá trị tìm kiếm, là tên chính xác của cột trả lại, gợi ý cột mà bạn mong muốn quét trị giá, ở đây là cột “Điểm Toán”
- A1:C1 là dãy kiếm tìm chứa bảng
Sau đó gộp hàm Match vào tham số col_index_num của hàm Vlookup như sau
=VLOOKUP (F1, A:C8, MATCH (E2, A1:C1, O), FALSE)
Và tất nhiên rằng hàm sẽ khiến việc tốt dù bạn có thêm hay xoá bao nhiêu cột
Trong hình chụp trên, Học Excel Trực tuyến đang xoá all các ô tham chiếu để hàm hoạt động tốt kể cả khi người sử dụng chuyển tới vị trí không giống trong worksheet. giống như bạn đủ nội lực thấy, cách thức hoạt động được sau khi xoá một cột và Excel thậm chí còn đủ sức tự điều chỉnh tham chiếu trong trường hợp này.
HÀM HLOOKUP VÀ MATCH
Tương tự như Vlookup, bạn cũng đủ sức dùng hàm Match để tăng trưởng công thức HLOOKUP của mình. nguyên tắc chung cũng như Vlookup: bạn sử dụng hàm Match để quét vị trí tương đối của cột cần trả lại, và cung cấp số của cột đó cho tham số row_index_number của hàm Hlookup
Giả sử giá trị search ở ô B5, bảng B1:h3, tên của hàng trả lại (cũng là trị giá tìm kiếm của hàm MATCH) ở ô A6 và tittle hàng là A1:A3, cách thức hoàn chỉnh giống như sau:
=HLOOKUP (B5, B1:H3, MATCH (A6, A1:A3, 0), FALSE)
giống như vậy, sự hòa hợp giữa Hlookup/Vlookup với Match đã giúp phát triển hàm Hlookup và Vlookup. không những thế, hàm MATCH không thể loại bỏ hết tất cả các khuyết điểm. Để khắc phục chủ đề này, bạn nên thử dùng hàm INDEX MATCH, một công cụ thực sự mạnh và linh hoạt giúp search trong Excel và vượt trội hơn Vlookup và Hlookup trên nhiều phương diện.
Nguồn: https://blog.hocexcel.online/
