Nội dung
Trước đây chúng tôi đã giải thích cho người mới bắt đầu cách sử dụng các hàm cơ bản của hàm VLOOKUP (tiếng Anh là VLOOKUP, viết tắt của cụm từ “hàm tra cứu theo chiều dọc”). Và những người dùng có kinh nghiệm đã được hiển thị một số công thức phức tạp hơn.
Và trong bài viết này, chúng tôi sẽ cố gắng cung cấp thông tin về một phương pháp làm việc khác với tìm kiếm theo chiều dọc.
Bạn có thể tự hỏi: “Tại sao điều này lại cần thiết?”. Và điều này là cần thiết để hiển thị tất cả các phương pháp tìm kiếm có thể. Ngoài ra, nhiều hạn chế về hàm VLOOKUP thường ngăn cản việc thu được kết quả mong muốn. Về mặt này, INDEX () MATCH () có nhiều chức năng và đa dạng hơn, đồng thời chúng cũng có ít hạn chế hơn.
Khái niệm cơ bản TRẬN ĐẤU INDEX
Vì mục đích của hướng dẫn này là cho thấy tính năng này tốt như thế nào, chúng tôi Chúng ta hãy xem xét các thông tin cơ bản liên quan đến các nguyên tắc hoạt động của nó. Và chúng tôi sẽ đưa ra các ví dụ và cũng xem xét lý do tại sao, nó tốt hơn hàm VLOOKUP ().
Cú pháp và cách sử dụng hàm INDEX
Chức năng này giúp tìm giá trị mong muốn trong số các khu vực tìm kiếm được chỉ định dựa trên số cột hoặc dòng. Cú pháp:
= INDEX (mảng, số hàng, số cột):
- mảng - khu vực mà việc tìm kiếm sẽ diễn ra;
- số dòng - số dòng được tìm kiếm trong mảng được chỉ định. Nếu số hàng không xác định, thì số cột phải được chỉ định;
- số cột - số cột được tìm thấy trong mảng được chỉ định. Nếu giá trị không xác định, số dòng là bắt buộc.
Một ví dụ về một công thức đơn giản:
= INDEX (A1: S10,2,3)
Hàm sẽ tìm kiếm trong phạm vi từ A1 đến C10. Các con số hiển thị hàng (2) và cột (3) để hiển thị giá trị mong muốn từ đó. Kết quả sẽ là ô C2.
Khá đơn giản phải không? Nhưng khi bạn làm việc với các tài liệu thực, bạn không chắc có thông tin liên quan đến số cột hoặc ô. Đó là những gì hàm MATCH () dành cho.
Cú pháp và cách sử dụng hàm MATCH
Hàm MATCH () tìm kiếm giá trị mong muốn và hiển thị số gần đúng của nó trong vùng tìm kiếm được chỉ định.
Cú pháp searchpos () trông giống như sau:
= MATCH (giá trị cần tra cứu, mảng để tra cứu, loại đối sánh)
- giá trị tìm kiếm - số hoặc văn bản được tìm thấy;
- mảng đã tìm kiếm - khu vực mà việc tìm kiếm sẽ diễn ra;
- loại đối sánh - chỉ định xem nên tìm kiếm giá trị chính xác hay các giá trị gần nhất với nó:
- 1 (hoặc không có giá trị được chỉ định) - trả về giá trị lớn nhất bằng hoặc nhỏ hơn giá trị đã được chỉ định;
- 0 - hiển thị khớp chính xác với giá trị được tìm kiếm. Trong tổ hợp INDEX () MATCH (), bạn sẽ hầu như luôn cần một kết hợp chính xác, vì vậy chúng tôi viết 0;
- -1 - Hiển thị giá trị nhỏ nhất lớn hơn hoặc bằng giá trị được chỉ định trong công thức. Việc sắp xếp được thực hiện theo thứ tự giảm dần.
Ví dụ, trong phạm vi B1: B3 New York, Paris, London được đăng ký. Công thức dưới đây sẽ hiển thị số 3 vì London đứng thứ ba trong danh sách:
= EXPOSE (Luân Đôn, B1: B3,0)
Cách làm việc với hàm INDEX MATCH
Bạn có thể đã bắt đầu hiểu nguyên tắc mà hoạt động chung của các chức năng này được xây dựng. Tóm lại, sau đó INDEX () tìm kiếm giá trị mong muốn trong số các hàng và cột được chỉ định. Và MATCH () hiển thị số lượng các giá trị này:
= INDEX (cột mà từ đó giá trị được trả về, MATCH (giá trị để tìm kiếm, cột để tìm kiếm, 0))
Bạn vẫn gặp khó khăn trong việc hiểu nó hoạt động như thế nào? Có thể một ví dụ sẽ giải thích tốt hơn. Giả sử bạn có một danh sách các thủ đô trên thế giới và dân số của họ:
Để tìm ra quy mô dân số của một thủ đô nào đó, ví dụ như thủ đô của Nhật Bản, chúng ta sử dụng công thức sau:
= INDEX (C2: C10, MATCH (Nhật Bản, A2: A10,0))
Giải thích:
- Hàm MATCH () tìm kiếm giá trị - “Japan” trong mảng A2: A10 và trả về số 3, vì Japan là giá trị thứ ba trong danh sách.
- Con số này đi đếnsố dòng”Trong công thức INDEX () và yêu cầu hàm in một giá trị từ hàng này.
Vì vậy công thức trên trở thành công thức chuẩn CHỈ SỐ (C2: C10,3). Công thức tìm kiếm từ các ô C2 đến C10 và trả về dữ liệu từ ô thứ ba trong phạm vi này, nghĩa là C4, vì quá trình đếm ngược bắt đầu từ hàng thứ hai.
Bạn không muốn ghi tên thành phố trong công thức? Sau đó, viết nó vào bất kỳ ô nào, chẳng hạn F1, và sử dụng nó làm tham chiếu trong công thức MATCH (). Và bạn kết thúc với một công thức tìm kiếm động:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Quan trọng! Số dòng trong mảng INDEX () phải giống với số hàng trong được coi là mảng trong MATCH (), nếu không bạn sẽ nhận được kết quả sai.
Chờ một chút, tại sao không chỉ sử dụng công thức VLOOKUP ()?
= VLOOKUP (F1, A2: C10, 3, Sai)
Có ích gì khi lãng phí thời gian để tìm ra tất cả những phức tạp này của INDEX MATCH?
Trong trường hợp này, không quan trọng là sử dụng chức năng nào. Đây chỉ là một ví dụ để hiểu cách hàm INDEX () và MATCH () hoạt động cùng nhau. Các ví dụ khác sẽ cho thấy những chức năng này có khả năng gì trong các tình huống mà VLOOKUP không hoạt động.
INDEX MATCH hoặc VLOOKUP
Khi quyết định sử dụng công thức tìm kiếm nào, nhiều người đồng ý rằng INDEX () và MATCH () vượt trội hơn hẳn so với hàm VLOOKUP. Tuy nhiên, nhiều người vẫn sử dụng hàm VLOOKUP (). Thứ nhất, hàm VLOOKUP () đơn giản hơn, và thứ hai, người dùng không hiểu đầy đủ tất cả các lợi ích khi làm việc với INDEX () và MATCH (). Nếu không có kiến thức này, sẽ không ai đồng ý dành thời gian của họ để nghiên cứu một hệ thống phức tạp.
Dưới đây là những ưu điểm chính của INDEX () và MATCH () so với hàm VLOOKUP ():
- Tìm kiếm từ phải sang trái. VLOOKUP () không thể tìm kiếm từ phải sang trái, vì vậy các giá trị bạn đang tìm phải luôn nằm trong các cột ngoài cùng bên trái của bảng. Nhưng INDEX () và MATCH () có thể xử lý điều này mà không gặp vấn đề gì. Bài viết này sẽ cho bạn biết nó trông như thế nào trong thực tế: cách tìm giá trị mong muốn ở phía bên trái.
- Thêm hoặc bớt cột an toàn. Công thức VLOOKUP () hiển thị kết quả không chính xác khi xóa hoặc thêm cột vì hàm VLOOKUP () cần số cột chính xác để thành công. Đương nhiên, khi các cột được thêm hoặc bớt, số lượng của chúng cũng thay đổi.
Và trong công thức INDEX () và MATCH (), một loạt các cột được chỉ định, không phải các cột riêng lẻ. Do đó, bạn có thể thêm và xóa các cột một cách an toàn mà không cần phải cập nhật công thức mỗi lần.
- Không có giới hạn về khối lượng tìm kiếm. Khi sử dụng hàm VLOOKUP (), tổng số tiêu chí tìm kiếm không được vượt quá 255 ký tự, nếu không bạn sẽ nhận được lỗi #VALUE! Vì vậy, nếu dữ liệu của bạn chứa một số lượng lớn các ký tự, INDEX () và MATCH () là lựa chọn tốt nhất.
- Tốc độ xử lý cao. Nếu các bảng của bạn tương đối nhỏ, thì bạn khó có thể nhận thấy bất kỳ sự khác biệt nào. Tuy nhiên, nếu bảng chứa hàng trăm hoặc hàng nghìn hàng, và theo đó, có hàng trăm và hàng nghìn công thức, thì INDEX () và MATCH () sẽ xử lý nhanh hơn nhiều so với hàm VLOOKUP (). Thực tế là Excel sẽ chỉ xử lý các cột được chỉ định trong công thức, thay vì xử lý toàn bộ bảng.
Tác động hiệu suất của hàm VLOOKUP () sẽ đặc biệt đáng chú ý nếu trang tính của bạn chứa một số lượng lớn các công thức như VLOOKUP () và SUM (). Cần kiểm tra riêng các hàm VLOOKUP () để phân tích cú pháp từng giá trị trong một mảng. Vì vậy, Excel phải xử lý một lượng lớn thông tin và điều này làm chậm công việc một cách đáng kể.
Ví dụ về Công thức
Chúng ta đã tìm ra sự hữu ích của các hàm này, vì vậy chúng ta có thể chuyển sang phần thú vị nhất: ứng dụng kiến thức vào thực tế.
Công thức tìm kiếm từ phải sang trái
Như đã đề cập, hàm VLOOKUP không thể thực hiện hình thức tìm kiếm này. Vì vậy, nếu các giá trị mong muốn không nằm ở cột ngoài cùng bên trái, hàm VLOOKUP () sẽ không tạo ra kết quả. Các hàm INDEX () và MATCH () linh hoạt hơn và vị trí của các giá trị không đóng vai trò lớn để chúng hoạt động.
Ví dụ, chúng tôi sẽ thêm một cột xếp hạng vào bên trái của bảng và cố gắng tìm ra thứ hạng nào về dân số mà thủ đô của Đất nước Chúng tôi chiếm giữ.
Trong ô G1, chúng tôi viết giá trị cần tìm, sau đó sử dụng công thức sau để tìm kiếm trong phạm vi C1: C10 và trả về giá trị tương ứng từ A2: A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Lời nhắc. Nếu bạn định sử dụng công thức này cho nhiều ô, hãy đảm bảo rằng bạn sửa các phạm vi bằng cách sử dụng địa chỉ tuyệt đối (ví dụ: $ A $ 2: $ A $ 10 và $ C $ 2: 4C $ 10).
CHỈ SỐ ĐƯỢC MỞ RỘNG HƠN ĐƯỢC MỞ RỘNG HƠN để tìm kiếm trong các cột và hàng
Trong các ví dụ trên, chúng tôi đã sử dụng các hàm này thay thế cho hàm VLOOKUP () để trả về các giá trị từ một phạm vi hàng được xác định trước. Nhưng nếu bạn cần thực hiện tìm kiếm ma trận hoặc hai mặt thì sao?
Nghe có vẻ phức tạp, nhưng công thức cho các phép tính như vậy tương tự như công thức INDEX () MATCH () tiêu chuẩn, chỉ có một điểm khác biệt: công thức MATCH () phải được sử dụng hai lần. Lần đầu tiên để lấy số hàng và lần thứ hai để lấy số cột:
= INDEX (mảng, MATCH (giá trị tìm kiếm dọc, cột tìm kiếm, 0), MATCH (giá trị tìm kiếm ngang, hàng tìm kiếm, 0))
Hãy xem bảng dưới đây và thử lập công thức INDEX () EXPRESS () EXPRESS () để hiển thị nhân khẩu học ở một quốc gia cụ thể trong một năm đã chọn.
Quốc gia mục tiêu nằm trong ô G1 (tra cứu theo chiều dọc) và năm mục tiêu nằm trong ô G2 (tra cứu theo chiều ngang). Công thức sẽ như thế này:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Công thức này hoạt động như thế nào
Như với bất kỳ công thức phức tạp nào khác, chúng dễ hiểu hơn bằng cách chia nhỏ chúng thành các phương trình riêng lẻ. Và sau đó bạn có thể hiểu những gì từng chức năng riêng lẻ làm:
- TRẬN ĐẤU (G1, A2: A11,0) - tìm kiếm một giá trị (G1) trong phạm vi A2: A11 và hiển thị số lượng của giá trị này, trong trường hợp của chúng tôi là 2;
- TÌM KIẾM (G2, B1: D1,0) - tìm kiếm một giá trị (G2) trong phạm vi B1: D1. Trong trường hợp này, kết quả là 3.
Số hàng và số cột được tìm thấy được gửi đến giá trị tương ứng trong công thức INDEX ():
= INDEX (B2: D11,2,3)
Kết quả là chúng ta có một giá trị nằm trong một ô ở giao điểm của 2 hàng và 3 cột trong phạm vi B2: D11. Và công thức hiển thị giá trị mong muốn, nằm trong ô D3.
Tìm kiếm theo nhiều điều kiện với INDEX và MATCH
Nếu bạn đã đọc hướng dẫn của chúng tôi về hàm VLOOKUP (), bạn có thể đã thử nhiều công thức tìm kiếm. Nhưng phương pháp tìm kiếm này có một hạn chế đáng kể - cần phải thêm một cột bổ trợ.
Nhưng tin tốt là Với INDEX () và MATCH (), bạn có thể tìm kiếm nhiều điều kiện mà không cần phải chỉnh sửa hoặc thay đổi trang tính của mình.
Đây là công thức tìm kiếm nhiều điều kiện chung cho INDEX () MATCH ():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
Lưu ý: công thức này phải được sử dụng cùng với phím tắt CTRL + SHIFT + ENTER.
Giả sử bạn cần tìm giá trị bạn đang tìm dựa trên 2 điều kiện: người mua и Sản phẩm.
Điều này yêu cầu công thức sau:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
Trong công thức này, C2: C10 là phạm vi mà tìm kiếm sẽ diễn ra, F1 - điều kiện này, A2: A10 - là phạm vi để so sánh điều kiện, F2 - điều kiện 2, V2: V10 - phạm vi để so sánh điều kiện 2.
Đừng quên nhấn tổ hợp ở cuối tác phẩm với công thức CTRL+SHIFT+ENTER - Excel sẽ tự động đóng công thức bằng dấu ngoặc nhọn, như trong ví dụ:
Nếu bạn không muốn sử dụng công thức mảng cho công việc của mình, hãy thêm một INDEX () khác vào công thức và nhấn ENTER, nó sẽ giống như trong ví dụ:
Cách các công thức này hoạt động
Công thức này hoạt động giống như công thức INDEX () MATCH () tiêu chuẩn. Để tìm kiếm nhiều điều kiện, bạn chỉ cần tạo nhiều điều kiện Sai và Đúng đại diện cho các điều kiện riêng lẻ đúng và không chính xác. Và sau đó các điều kiện này áp dụng cho tất cả các phần tử tương ứng của mảng. Công thức chuyển đổi các đối số Sai và Đúng thành 0 và 1 tương ứng và xuất ra một mảng trong đó 1 là các giá trị phù hợp được tìm thấy trong chuỗi. MATCH () sẽ tìm giá trị đầu tiên khớp với 1 và chuyển nó vào công thức INDEX (). Và nó, đến lượt nó, sẽ trả về giá trị đã mong muốn trong dòng được chỉ định từ cột mong muốn.
Một công thức không có mảng phụ thuộc vào khả năng INDEX () tự xử lý chúng. INDEX () thứ hai trong công thức khớp với falsy (0), vì vậy nó chuyển toàn bộ mảng có các giá trị đó sang công thức MATCH ().
Đây là một lời giải thích khá dài dòng về logic đằng sau công thức này. Để biết thêm thông tin, hãy đọc bài báo “INDEX MATCH với nhiều điều kiện'.
AVERAGE, MAX và MIN trong INDEX và MATCH
Excel có các hàm đặc biệt riêng để tìm giá trị trung bình, số tối đa và số tối thiểu. Nhưng nếu bạn muốn lấy dữ liệu từ ô được liên kết với các giá trị đó thì sao? Trong trường hợp này AVERAGE, MAX và MIN phải được sử dụng cùng với INDEX và MATCH.
INDEX MATCH và MAX
Để tìm giá trị lớn nhất trong cột D và hiển thị nó trong cột C, hãy sử dụng công thức:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
INDEX MATCH và MIN
Để tìm giá trị nhỏ nhất trong cột D và hiển thị nó trong cột C, hãy sử dụng công thức sau:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
TÌM KIẾM INDEX và SERPENT
Để tìm giá trị trung bình trong cột D và hiển thị giá trị này trong C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Tùy thuộc vào cách dữ liệu của bạn được ghi, đối số thứ ba của MATCH () là 1, 0 hoặc -1:
- nếu các cột được sắp xếp theo thứ tự tăng dần, hãy đặt 1 (khi đó công thức sẽ tính giá trị lớn nhất, nhỏ hơn hoặc bằng giá trị trung bình);
- nếu sắp xếp giảm dần, thì -1 (công thức sẽ xuất giá trị nhỏ nhất lớn hơn hoặc bằng giá trị trung bình);
- nếu mảng tra cứu chứa một giá trị chính xác bằng giá trị trung bình, thì hãy đặt nó thành 0.
Trong ví dụ của chúng tôi, dân số được sắp xếp theo thứ tự giảm dần, vì vậy chúng tôi đặt -1. Và kết quả là Tokyo, vì giá trị dân số (13,189) là gần nhất với giá trị trung bình (000).
VLOOKUP () cũng có thể thực hiện các phép tính như vậy, nhưng chỉ dưới dạng công thức mảng: VLOOKUP với AVERAGE, MIN và MAX.
INDEX MATCH và ESND / IFERROR
Bạn có thể đã nhận thấy rằng nếu công thức không thể tìm thấy giá trị mong muốn, nó sẽ gây ra lỗi # N / A. Bạn có thể thay thế thông báo lỗi tiêu chuẩn bằng thông báo nào đó nhiều thông tin hơn. Ví dụ: đặt đối số trong công thức Trong ngày XNUMX:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Với công thức này, nếu bạn nhập dữ liệu không có trong bảng, biểu mẫu sẽ cung cấp cho bạn thông báo được chỉ định.
Nếu bạn muốn bắt tất cả các lỗi, thì ngoại trừ Trong ngày XNUMX có thể được sử dụng SỐ PHIẾU:
= IFERROR (INDEX (C2: C10, MATCH (F1, A2: A10,0)), “Đã xảy ra lỗi!”)
Nhưng hãy nhớ rằng che các lỗi theo cách này không phải là một ý kiến hay, vì các lỗi tiêu chuẩn báo cáo các vi phạm trong công thức.
Chúng tôi hy vọng bạn thấy hướng dẫn sử dụng hàm INDEX MATCH () của chúng tôi hữu ích.