Chào mừng bạn đến với bolamarketing.com! Hôm nay chúng ta sẽ tìm hiểu về cách sử dụng công thức Index với Match để tìm kết quả mảng 2D trong Google Sheets. Điều đó có thể được thực hiện như thế nào?
Giới thiệu về công thức Index và Match
Thường thì công thức Index được sử dụng để trả về nội dung của một ô bằng cách chỉ định cột và hàng của nó. Ngoài ra, nó cũng có thể trả về một cột hoặc hàng duy nhất.
Chẳng hạn, công thức =index(A2:B,5,2) sẽ trả về nội dung từ cột thứ hai trong hàng thứ năm trong phạm vi A2:B.
Thay thế số 2 bằng 0, tức là =index(A2:B,5,0), để lấy hàng thứ năm và số 5 bằng 0, tức là =index(A2:B,0,2), để lấy cột thứ hai.
Vậy làm sao chúng ta có thể sử dụng công thức Index để tìm kết quả mảng 2D trong Google Sheets?
Cách sử dụng Index để tìm kết quả mảng 2D trong Google Sheets
Trong bảng tính, như bạn có thể biết, chúng ta chỉ định một phạm vi ô bằng cách đặt dấu hai chấm (:) giữa hai tham chiếu ô.
Chẳng hạn, B2:D10 là một phạm vi ô trong đó B2 và D10 là tham chiếu ô.
Tương tự, chúng ta có thể đặt dấu hai chấm (:) giữa hai kết quả Index để có kết quả ma trận trong Google Sheets.
Chúng ta có thể sử dụng tính năng này trong một số kịch bản thực tế khác nhau trong Google Sheets.
Ví dụ về việc sử dụng Index với Match để tìm kết quả mảng 2D trong Google Sheets
Chúng ta sẽ xem hai đến ba ví dụ về cách trả về một mảng 2D bằng cách sử dụng INDEX và MATCH trong Google Sheets.
1. Sao chép một bảng cho đến hàng đầu tiên khớp (Nhãn Field)
Có nhiều cách để thực hiện việc này trong Google Sheets.
Cách đơn giản nhất là sử dụng công thức Choosecols với Match hoặc Xmatch.
Chúng ta sẽ tìm hiểu công thức Index và Match trước khi đi vào phần cách sao chép bảng.
=index(B2:B8):index(B2:F8,0,match(“Mar”,B2:F2,0))
Đây là cách công thức hoạt động.
- Phần công thức bên trái dấu hai chấm trả về cột đầu tiên trong phạm vi.
- Phần công thức bên phải trả về cột thứ 4 (nhãn trường khớp bằng công thức Match).
Công thức này tương đương với =index(B2:B8,0,0):index(B2:F8,0,4).
Vì chúng ta đặt hai công thức Index hai bên dấu hai chấm, nó trả về kết quả ma trận.
Chúng ta có thể sử dụng công thức thay thế CHOOSECOLS + Match để sao chép một bảng, lên đến cột khớp với tiêu đề hàng trong Google Sheets.
Dưới đây là công thức đó.
=choosecols(B2:F8,sequence(match(“Mar”,B2:F2,0)))
2. Khớp một giá trị và trả về các hàng bổ sung
Trong cùng một bảng, làm thế nào để khớp “b” trong cột đầu tiên và trả về hàng đó cộng với 2 hàng bổ sung?
Giải pháp:
=index(B2:B8,match(“b”,B2:B8,0)):index(B2:F8,match(“b”,B2:B8,0)+2,5)
Phần bên trái dấu hai chấm trả về “b” vì vị trí hàng # 3 (trả về từ Match). Phạm vi để tìm vị trí là B2:B8.
Phần bên phải của dấu hai chấm sử dụng cùng công thức, nhưng vị trí hàng là 5 (3+2), và vị trí cột cũng là 5.
Vì vậy, công thức trả về phạm vi B4:F6.
Đây là một ví dụ khác về việc sử dụng Index và Match để trả về kết quả mảng 2D trong Google Sheets.
Ví dụ về việc sử dụng Index với Xmatch để tìm kết quả mảng 2D trong Google Sheets
Tương tự như hàm Match, chúng ta cũng có thể sử dụng hàm Xmatch cùng với Index.
Tôi sử dụng Xmatch độc lập chủ yếu trong hai kịch bản sau.
- Khi tôi muốn khớp một khóa trong một cột từ vị trí xuất hiện cuối cùng đến vị trí xuất hiện đầu tiên.
- Khi sử dụng từ đại diện (khớp một phần).
Dưới đây chúng ta sẽ sử dụng những tính năng này của Xmatch và tìm hiểu cách trả về một ma trận bằng cách sử dụng cùng lúc Index và Xmatch.
1. Trả về các hàng giữa lần xuất hiện đầu tiên và cuối cùng của một giá trị
Tôi có dữ liệu mẫu không cấu trúc gây khó khăn cho việc áp dụng các hàm như FILTER, QUERY, v.v.
Vì vậy tôi không thể trích xuất các hàng chứa một sản phẩm cụ thể.
Trong ví dụ dưới đây, tôi muốn trích xuất các hàng từ “Orange” và “Orange Total”.
Tiêu chí đầu tiên sẽ là khớp chính xác và tiêu chí cuối cùng sẽ là khớp một phần (từ đại diện) vì tôi không biết từ khóa thứ hai là “Orange Total” hay “Total of Orange”.
Chúng ta có thể sử dụng Index với Xmatch cho việc này, và kết quả sẽ là một mảng 2D.
=index(B:B,xmatch(“Orange”,B:B,0)):index(B:D,xmatch(“Orange*”,B:B,2,-1),3)
Xmatch đầu tiên tìm kiếm từ trên xuống dưới trong cột B để tìm từ khóa “Orange”.
Xmatch thứ hai tìm kiếm từ dưới lên trên trong cùng cột để tìm từ khảo “Orange” một phần.
Chúng ta có thể trả về một kết quả ma trận bằng cách sử dụng hai công thức Index hai bên dấu hai chấm.
Index với Match hoặc Xmatch cho kết quả mảng 2D sẽ hữu ích nhất khi bạn có dữ liệu không cấu trúc.
Đọc thêm: INDEX MATCH Every Nth Column in Google Sheets
Chúng ta đã khám phá cách sử dụng công thức Index với Match và Xmatch để tìm kết quả mảng 2D trong Google Sheets. Hy vọng bạn đã hài lòng với những bí quyết của chúng tôi!
