Nội dung
Hôm qua trong cuộc thi marathon 30 hàm Excel trong 30 ngày chúng tôi đã tìm thấy các chuỗi văn bản bằng cách sử dụng hàm TÌM KIẾM (TÌM KIẾM) và cũng được sử dụng SỐ PHIẾU (IFERROR) và THÁNG NĂM (ISNUMBER) trong các tình huống mà hàm ném ra một lỗi.
Vào ngày thứ 19 của cuộc thi marathon của chúng ta, chúng ta sẽ nghiên cứu về chức năng Đội hình thi đấu (TÌM KIẾM). Nó tìm kiếm một giá trị trong một mảng và nếu một giá trị được tìm thấy, nó sẽ trả về vị trí của nó.
Vì vậy, chúng ta hãy chuyển sang thông tin tham khảo về hàm Đội hình thi đấu (TRẬN ĐẤU) và xem một vài ví dụ. Nếu bạn có ví dụ hoặc cách tiếp cận của riêng mình để làm việc với chức năng này, vui lòng chia sẻ chúng trong phần bình luận.
Chức năng 19: MATCH
Chức năng Đội hình thi đấu (MATCH) trả về vị trí của một giá trị trong một mảng hoặc một lỗi #TẠI (# N / A) nếu không tìm thấy. Một mảng có thể được sắp xếp hoặc không được sắp xếp. Hàm số Đội hình thi đấu (MATCH) không phân biệt chữ hoa chữ thường.
Làm thế nào bạn có thể sử dụng chức năng MATCH?
Chức năng Đội hình thi đấu (MATCH) trả về vị trí của một phần tử trong một mảng và kết quả này có thể được sử dụng bởi các hàm khác như INDEX (INDEX) hoặc VLOOKUP (VPR). Ví dụ:
- Tìm vị trí của một phần tử trong danh sách chưa được sắp xếp.
- Dùng với CHỌN (CHỌN) để chuyển đổi kết quả học tập của học sinh sang điểm chữ cái.
- Dùng với VLOOKUP (VLOOKUP) để chọn cột linh hoạt.
- Dùng với INDEX (INDEX) để tìm giá trị gần nhất.
MATCH cú pháp
Chức năng Đội hình thi đấu (MATCH) có cú pháp sau:
MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
- lookup_value (lookup_value) - Có thể là văn bản, số hoặc boolean.
- tra cứu_array (lookup_array) - một tham chiếu mảng hoặc mảng (các ô liền kề trong cùng một cột hoặc cùng một hàng).
- Loại so khớp (match_type) có thể nhận ba giá trị: -1, 0 or 1. Nếu đối số bị bỏ qua, nó tương đương với 1.
Bẫy MATCH (TRẬN ĐẤU)
Chức năng Đội hình thi đấu (MATCH) trả về vị trí của phần tử được tìm thấy, nhưng không trả về giá trị của nó. Nếu bạn muốn trả về một giá trị, hãy sử dụng Đội hình thi đấu (MATCH) cùng với hàm INDEX (MỤC LỤC).
Ví dụ 1: Tìm một phần tử trong danh sách chưa được sắp xếp
Đối với danh sách chưa được sắp xếp, bạn có thể sử dụng 0 như giá trị đối số Loại so khớp (match_type) để tìm kiếm đối sánh chính xác. Nếu bạn muốn tìm một kết hợp chính xác của một chuỗi văn bản, bạn có thể sử dụng các ký tự đại diện trong giá trị tìm kiếm.
Trong ví dụ sau, để tìm vị trí của một tháng trong danh sách, chúng ta có thể viết tên của tháng, toàn bộ hoặc một phần, sử dụng ký tự đại diện.
=MATCH(D2,B3:B7,0)
=ПОИСКПОЗ(D2;B3:B7;0)
Như một lập luận tra cứu_array (lookup_array) bạn có thể sử dụng một mảng các hằng số. Trong ví dụ sau, tháng mong muốn được nhập vào ô D5 và tên của các tháng được thay thế làm đối số thứ hai cho hàm Đội hình thi đấu (MATCH) dưới dạng một mảng các hằng số. Ví dụ: nếu bạn nhập một tháng sau đó vào ô D5, Tháng Mười (Tháng XNUMX), khi đó kết quả của hàm sẽ là #TẠI (# Không / A).
=MATCH(D5,{"Jan","Feb","Mar"},0)
=ПОИСКПОЗ(D5;{"Jan";"Feb";"Mar"};0)
Ví dụ 2: Thay đổi điểm của học sinh từ tỷ lệ phần trăm sang chữ cái
Bạn có thể chuyển đổi điểm của sinh viên sang hệ thống chữ cái bằng cách sử dụng hàm Đội hình thi đấu (TRẬN ĐẤU) giống như bạn đã làm với VLOOKUP (VPR). Trong ví dụ này, hàm được sử dụng cùng với CHỌN (CHOICE), trả về ước tính mà chúng ta cần. Tranh luận Loại so khớp (match_type) được đặt bằng -1, bởi vì điểm số trong bảng được sắp xếp theo thứ tự giảm dần.
Khi đối số Loại so khớp (match_type) là -1, kết quả là giá trị nhỏ nhất lớn hơn hoặc tương đương với giá trị mong muốn. Trong ví dụ của chúng tôi, giá trị mong muốn là 54. Vì không có giá trị nào như vậy trong danh sách điểm, phần tử tương ứng với giá trị 60 được trả về. Vì 60 đứng ở vị trí thứ tư trong danh sách nên kết quả của hàm CHỌN (SELECT) sẽ là giá trị ở vị trí thứ 4, tức là ô C6, chứa điểm D.
=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)
=ВЫБОР(ПОИСКПОЗ(B9;B3:B7;-1);C3;C4;C5;C6;C7)
Ví dụ 3: Tạo một lựa chọn cột linh hoạt cho hàm VLOOKUP (VLOOKUP)
Để mang lại sự linh hoạt hơn cho chức năng VLOOKUP (VLOOKUP) Bạn có thể sử dụng Đội hình thi đấu (MATCH) để tìm số cột, thay vì mã hóa cứng giá trị của nó vào hàm. Trong ví dụ sau, người dùng có thể chọn một vùng trong ô H1, đây là giá trị họ đang tìm kiếm VLOOKUP (VPR). Tiếp theo, họ có thể chọn một tháng trong ô H2 và hàm Đội hình thi đấu (MATCH) sẽ trả về số cột tương ứng với tháng đó.
=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)
=ВПР(H1;$B$2:$E$5;ПОИСКПОЗ(H2;B1:E1;0);ЛОЖЬ)
Ví dụ 4: Tìm giá trị gần nhất bằng INDEX (INDEX)
Chức năng Đội hình thi đấu (MATCH) hoạt động tuyệt vời khi kết hợp với chức năng INDEX (INDEX), chúng ta sẽ xem xét kỹ hơn một chút sau trong cuộc đua marathon này. Trong ví dụ này, hàm Đội hình thi đấu (MATCH) được sử dụng để tìm số gần nhất với số chính xác từ một số số đã đoán.
- Chức năng ABS trả về mô-đun của sự khác biệt giữa mỗi số đoán và số đúng.
- Chức năng MIN (MIN) tìm sự khác biệt nhỏ nhất.
- Chức năng Đội hình thi đấu (MATCH) tìm địa chỉ của sự khác biệt nhỏ nhất trong danh sách các điểm khác biệt. Nếu có nhiều giá trị phù hợp trong danh sách, giá trị đầu tiên sẽ được trả về.
- Chức năng INDEX (INDEX) trả về tên tương ứng với vị trí này từ danh sách tên.
=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))
=ИНДЕКС(B2:B5;ПОИСКПОЗ(МИН(ABS(C2:C5-F1));ABS(C2:C5-F1);0))
