Hàm index và match trong excel
|
INDEX MATCH với nhiều tiêu chí cho phép bạn thực hiện tra cứu thành công khi có nhiều giá trị tra cứu khớp Show
Nói cách khác, bạn có thể tra cứu và trả về các giá trị ngay cả khi không có giá trị duy nhất nào để tìm kiếm Điều này không thể đạt được với bất kỳ công thức tra cứu nào khác mà không chèn cột trợ giúp😲 Thực hiện theo 3 bước đơn giản sau để tạo INDEX MATCH của riêng bạn với nhiều tiêu chí trong vài phút Nếu bạn muốn tag cùng thì tải file Excel mẫu tại đây Mục lục Ví dụ về INDEX MATCH với nhiều tiêu chíVì vậy, bạn đã có cơ sở dữ liệu nhân viên này Bạn muốn làm cho cơ sở dữ liệu dễ tìm kiếm hơn, vì vậy bạn đang tạo một công cụ nhỏ (ở bên phải) Trong công cụ đó, bất kỳ ai cũng có thể nhập tên và bộ phận của một nhân viên và nó sẽ tìm thấy mức lương của người đó (và hiển thị nó trong ô G4) “Thật dễ dàng, tôi chỉ cần sử dụng VLOOKUP. ” Đợi chút✋ Thật không may, nó không phải là dễ dàng Bạn thấy đấy, vấn đề là thực sự có 2 nhân viên được gọi là “Steve Jones”. Điều đó có nghĩa là giá trị tra cứu có 2 giá trị khớp trong cột tra cứu Không có gì độc đáo về tên của Steve Jones Trong thuật ngữ Excel, 'Tên' sẽ là 1 tiêu chí Vì vậy, 1 tiêu chí đã không cắt nó Nhưng nếu bạn bao gồm một tiêu chí khác, chẳng hạn như 'Bộ phận', bạn sẽ khiến Steve Jones trở nên độc nhất Bây giờ, trong khi “Steve Jones” xuất hiện nhiều lần trong danh sách, thì chỉ có một “Steve Jones từ bộ phận bán hàng” Đây là loại phép thuật bạn có thể làm với INDEX MATCH với nhiều tiêu chí Bước 1. Chèn công thức INDEX MATCH bình thườngINDEX MATCH với nhiều tiêu chí là một ‘công thức mảng’ được tạo từ hàm INDEX và MATCH
Sự phối hợp giữa các hàm INDEX và MATCH là
Bắt đầu công thứcTrước tiên, hãy bắt đầu hàm MATCH, sau đó đặt hàm INDEX xung quanh hàm MATCH để hoàn thành công thức Bắt đầu với =TRẬN ĐẤU( 1. Là đối số đầu tiên trong hàm MATCH, hãy nhập giá trị tra cứu. Đây là thứ bạn đang tìm kiếm. Trong trường hợp này, bạn đang tìm kiếm một nhân viên có tên “Steve Jones” Chọn (hoặc nhập thủ công) ô G2 làm giá trị tra cứu, sau đó phân tách bằng dấu phẩy để chuyển sang mảng tra cứu 2. Mảng tra cứu là cột mà hàm MATCH tìm kiếm giá trị tra cứu Chọn cột có tên, rồi nhập dấu phẩy để chuyển sang [match_type] Bây giờ công thức của bạn sẽ trông như thế này =MATCH(G2,A. MỘT, 3. Một danh sách thả xuống nhỏ xuất hiện cho bạn lựa chọn giữa 1, 0 và -1 Tùy chọn 0 là tùy chọn “đối sánh chính xác” và được sử dụng phổ biến nhất. -1 và 1 tương tự như phương pháp “so khớp gần đúng” của VLOOKUP Viết 0 hoặc nhấp đúp vào tùy chọn '0 – Khớp chính xác' trong menu thả xuống và nhập dấu ngoặc đơn kết thúc Công thức của bạn bây giờ sẽ trông như thế này =MATCH(G2,A. A,0) 4. Bọc chức năng INDEX xung quanh chức năng MATCH Bây giờ công thức của bạn sẽ trông như thế này =INDEX(MATCH(G2,A. A,0) Nhưng chúng ta vẫn chưa xong✋ Cú pháp của hàm INDEX đi INDEX(mảng, số hàng, số cột) Hàm MATCH phải là đối số thứ 2 trong cú pháp INDEX Ngay bây giờ, đó là đối số đầu tiên Vì vậy, hãy bắt đầu viết đối số đầu tiên thực sự. mảng Mảng INDEX là cột mà bạn muốn trả về các giá trị từ đó Mục đích của nhiều tiêu chí INDEX MATCH là để tìm mức lương của một nhân viên cụ thể Vậy mảng là cột lương (cột D) Bây giờ công thức của bạn sẽ trông như thế này =INDEX(D. D,TRẬN ĐẤU(G2,A. A,0) Và chỉ cần đặt thêm một dấu ngoặc đơn ở cuối để kết thúc hàm INDEX Vì vậy, công thức cuối cùng trông như thế này =INDEX(D. D,TRẬN ĐẤU(G2,A. A,0)) GIẢI TRÌNH Hàm MATCH tìm kiếm giá trị trong G2 (“Steve Jones”) trong cơ sở dữ liệu rồi trả về một số Số này là hàng trong dữ liệu có tên “Steve Jones” Số hàng này sau đó được đưa vào cú pháp của hàm INDEX Vấn đề. Có nhiều hơn một nhân viên được gọi là “Steve Jones”. Chúng ta đang thực sự nhìn thấy tiền lương của ai? Các công thức tra cứu trong Excel luôn tìm kiếm từ trên xuống dưới, vì vậy bạn đang thấy mức lương của Steve Jones trên cùng (ở hàng 3) Đó là Steve mà bạn đang tìm kiếm Nhưng thật may mắn khi bạn tìm thấy anh ấy 🍀 Để đảm bảo chúng tôi luôn tìm thấy Steve Jones từ việc bán hàng, hãy làm theo các bước còn lại bên dưới Bước 2. Thay đổi giá trị tra cứu thành 1Bây giờ, bạn cần thay đổi công thức INDEX MATCH bình thường thành công thức mảng Nghe có vẻ khó? Đừng lo lắng, tôi sẽ hướng dẫn bạn từng bước😊 1. Thay đổi giá trị tra cứu của hàm MATCH thành 1. Nó chỉ đơn giản như âm thanh Vì vậy, công thức thay đổi từ =INDEX(D. D,TRẬN ĐẤU(G2,A. A,0)) Đến =INDEX(D. D,TRẬN ĐẤU(1,A. A,0)) “Lý thuyết” đằng sau điều này không đơn giản như việc thay đổi giá trị tra cứu Vì bạn đang thay đổi công thức từ công thức bình thường sang công thức mảng nên cấu trúc của công thức cũng thay đổi một chút. Bằng cách thay đổi giá trị tra cứu thành 1, bạn không thực sự yêu cầu hàm MATCH tìm kiếm số 1 trong mảng tra cứu (cột họ)
Khi bạn nhập hai tiêu chí của chúng tôi trong bước tiếp theo, số 1 trong hàm MATCH chỉ có nghĩa là “Xem qua các hàng trong dữ liệu và trả về số hàng có tất cả các tiêu chí là TRUE” Nếu bạn viết số 0, công thức sẽ tìm kiếm một hàng mà tất cả tiêu chí của chúng tôi là SAI – và điều đó sẽ không thực sự . Bước 3. Viết các tiêu chíTiêu chí thay thế đối số thứ 2 của hàm MATCH, với cấu trúc này (phạm vi=tiêu chí1)*(phạm vi=tiêu chí2)*(phạm vi=tiêu chí3)*… Bằng cách này, bạn có thể có nhiều tiêu chí như bạn cần INDEX MATCH với 2 tiêu chíThông thường, chỉ cần sử dụng 2 tiêu chí để làm cho giá trị tra cứu của bạn là duy nhất Tiêu chí 1 = tên Tiêu chí 2 = chia Hãy xem liệu bạn có thể tìm thấy “Steve Jones từ việc bán hàng” hay liệu anh ấy có bị lạc trong rừng không🌳 Thay thế cấu trúc trên bằng các tiêu chí thực tế (phạm vi=tiêu chí1)*(phạm vi=tiêu chí2) (MỘT. A=G2)*(B. B=G3) A. A là cột có tên. G2 là cái tên bạn đang tìm kiếm B. B là cột có phép chia. G3 là bộ phận bạn đang tìm kiếm Viết nó làm đối số thứ 2 trong hàm MATCH, thay thế những gì hiện có Công thức của bạn bây giờ sẽ trông như thế này =INDEX(D. D,TRẬN ĐẤU(1,(A. A=G2)*(B. B=G3),0)) Có vẻ lạ khi gõ dấu ngoặc đơn ngẫu nhiên vào công thức, nhưng đây là cách bạn cấu trúc các tiêu chí để công thức mảng hiểu được điều đó
Nếu bạn đang sử dụng Microsoft 365 chỉ cần nhấn Enter và xem tra cứu nhiều tiêu chí tuyệt đẹp của bạn💡 Giải trình. Ctrl + Shift + EnterNếu bạn không sử dụng Microsoft 365, đừng không nhấn Enter khi bạn hoàn thành công thức. Nó sẽ không hoạt động. Thay vào đó, nhấn và giữ Ctrl và Shift rồi nhấn Enter Cảnh báo. {dấu ngoặc nhọn} sẽ xuất hiện xung quanh công thức của bạn. Họ được cho là ở đó Mỗi khi bạn thay đổi công thức này, bạn phải kết thúc bằng Ctrl + Shift + Enter (Thay vì chỉ “Enter” thông thường như bạn có thể đã quen) Đó là nó - Bây giờ thì sao?Ồ… Bạn vừa học cách sử dụng INDEX MATCH với nhiều tiêu chí… Rốt cuộc nó không quá đáng sợ, phải không? Bây giờ, bạn đã tạo một công cụ để dễ dàng tra cứu nhân viên và trả lương cho họ – ngay cả khi có nhiều nhân viên có cùng tên. Tất cả được thúc đẩy bởi các chức năng INDEX và MATCH Các công thức (và hàm) tra cứu nói chung cực kỳ hữu ích và tăng tốc đáng kể công việc của bạn🏃 Nhưng tra cứu nhiều tiêu chí thực sự tiết kiệm thời gian của bạn khi giá trị tra cứu không phải là duy nhất Nhưng nếu bạn thực sự muốn làm việc hiệu quả hơn trong Excel, bạn cần đi sâu vào macro Macro tự động hóa các quy trình làm việc, vì vậy bạn có thể thực hiện 50 việc chỉ bằng một cú nhấp chuột Tôi hứa với bạn, nó không khó như bạn nghĩ Tham gia khóa học video 30 phút miễn phí của tôi và bắt đầu với macro (dành cho người mới bắt đầu) Các tài nguyên liên quan khácINDEX MATCH không phải là công thức tra cứu duy nhất hiện có, mặc dù đây là công thức duy nhất bạn có thể biến thành công thức mảng. Nếu bạn muốn mở rộng hộp công cụ của mình, bạn chắc chắn nên biết VLOOKUP và XLOOKUP mới Nếu bạn nghĩ điều này khó hiểu, bạn nên đi sâu vào tìm hiểu riêng về hàm INDEX và hàm MATCH để hiểu rõ hơn về chúng Tại sao khớp INDEX tốt hơn VLOOKUP?Vlookup sẽ chỉ hoạt động nếu giá trị tra cứu nằm trong cột đầu tiên. VLOOKUP không thể nhìn sang trái. Tuy nhiên, INDEX MATCH giải quyết vấn đề này vì nó thực hiện tra cứu theo cả chiều ngang và chiều dọc . Vì vậy, nó không yêu cầu giá trị tra cứu phải ở cột đầu tiên, nó có thể ở bất cứ đâu.
Tôi nên sử dụng khớp INDEX hay VLOOKUP?Mặc dù VLookup rất phù hợp để tìm kiếm các trường mà bạn biết hàng, nhưng Kết hợp chỉ mục linh hoạt hơn nhiều vì bạn có thể tìm kiếm theo hàng, theo cột hoặc theo cả hai. In these situations its best to use Index Match instead of VLookup.
Việc sử dụng hàm INDEX và đối sánh là gì?Hàm INDEX có thể trả về một mục từ một vị trí cụ thể trong danh sách. Hàm MATCH có thể trả về vị trí của một giá trị trong danh sách . Các hàm INDEX / MATCH có thể được sử dụng cùng nhau như một công cụ linh hoạt và mạnh mẽ để trích xuất dữ liệu từ một bảng.
INDEX và so khớp có tốt hơn Xlookup không?Chúng tôi đã thấy rằng INDEX/MATCH nhanh hơn rất nhiều so với XLOOKUP . |
