Cách sử dụng chỉ mục và đối sánh trong Excel để tra cứu nâng cao

Tìm ra thông tin chi tiết có thể hành động là mục tiêu chính của bất kỳ cơ sở dữ liệu nào. Microsoft Excel cung cấp các hàm VLOOKUP và HLOOKUP để tìm dữ liệu từ một bảng tính lớn. Tuy nhiên, các chức năng này không đủ linh hoạt.

Bạn có thể sử dụng INDEX và MATCH trong Excel để thực hiện tác vụ khám phá dữ liệu của mình dễ dàng hơn. Bạn sẽ tận hưởng khả năng truy xuất dữ liệu không cần lo lắng, ngay cả trong các tình huống nâng cao và phức tạp. Bài viết này giúp bạn hiểu hai hàm này bằng các thuật ngữ đơn giản và ví dụ dễ hiểu.

Hàm INDEX là gì?

Hàm INDEX của Microsoft Excel tìm nạp một giá trị từ một phạm vi ô nhất định. Giống như thiết bị GPS tìm vị trí trên bản đồ, chức năng này tìm giá trị ô trong phạm vi ô.

Trong khi đối với GPS, bạn cần nhập vĩ độ và kinh độ, để hàm INDEX hoạt động, bạn cần cung cấp phạm vi ô, số hàng và số cột. Đây là cú pháp:

=INDEX(array, row_num, [column_num])
                    

Liên quan: Cách tạo sơ đồ tư duy trong Excel: Phương pháp dễ dàng

Bạn có thể thử các bước sau để áp dụng hàm INDEX trong một trường hợp có liên quan:

1. Trong một ô, hãy đặt một Dấu bằng và sau đó gõ MỤC LỤC.

2. nhấn Chuyển hướng và nhập đối số đầu tiên.

3. Nó phải là một phạm vi ô được theo sau bởi một dấu phẩy.

   
   

4. Nhập số hàng mà bạn mong đợi dữ liệu và đặt dấu phẩy.

5. Số cột không cần thiết vì chỉ có một cột nguồn.

6. Đặt một dấu ngoặc đơn ở cuối và nhấn Enter.

7. Công thức sẽ giống như sau:

=INDEX(C5:C8,3,0)
                    

Tải xuống: INDEX và MATCH trong sổ làm việc hướng dẫn Excel (Miễn phí)

Chức năng MATCH là gì?

Nếu bạn muốn biết số hàng hoặc số cột của một giá trị ô hoặc địa chỉ ô, bạn cần sử dụng hàm MATCH trong Excel. Nó hữu ích khi bạn cần tự động hóa một tác vụ trong Excel, chẳng hạn như định vị giá trị ô cho hàm INDEX.

Nó yêu cầu ba đầu vào để tìm nạp vị trí của ô; giá trị tra cứu, phạm vi ô nơi giá trị tồn tại và tiêu chí đối sánh. Các loại đối sánh có thể có là 0 đối với đối sánh chính xác, 1 đối với nhỏ hơn giá trị tra cứu và -1 đối với lớn hơn giá trị tra cứu. Đây là cú pháp:

=MATCH(lookup_value, lookup_array, [match_type])

Đây là cách bạn có thể thử chức năng MATCH:

1. Nhập một Dấu bằng trong bất kỳ ô nào và nhập CUỘC THI ĐẤU.

2. nhấn Chuyển hướng.

   
   

3. Bây giờ hãy chọn một ô làm tham chiếu tra cứu hoặc nhập bất kỳ thứ gì trong dấu ngoặc kép.

4. Chọn một phạm vi ô cho dữ liệu nguồn và sau đó nhập 0 cho một kết hợp chính xác.

5. nhấn đi vào để lấy vị trí ô. Sau đây là công thức hoạt động:

=MATCH(C7,C5:C8,0)
                    
=MATCH("active listening",C5:C8,0)
                    

Liên quan: Các phương pháp trực quan hóa dữ liệu tốt nhất giúp tăng giá trị cho bất kỳ báo cáo nào

Lợi ích của INDEX và MATCH trong Excel là gì?

Đối với các tác vụ tra cứu dữ liệu phức tạp, bạn cần sử dụng cả hai chức năng để phát huy hết khả năng của chúng. Lợi ích của việc sử dụng kết hợp công thức này là:

1. Không có hạn chế trong tra cứu từ trái sang phải hoặc từ phải sang trái

2. Xóa, chèn hoặc cập nhật cột dễ dàng hơn

3. Đối với các bảng tính lớn, công thức kết hợp nhanh hơn nhiều so với hàm VLOOKUP

4. Nó có thể tìm nạp các kết quả phù hợp gần đúng

5. Bạn có thể tạo các công thức lồng nhau dài hơn bằng cách sử dụng hai hàm này

Cách áp dụng INDEX và MATCH trong một công thức

MATCH và INDEX rất có lợi khi bạn áp dụng chúng cùng nhau. Giả sử bạn muốn tìm hiểu Doanh thu và Lợi nhuận của bất kỳ ứng dụng nào từ cơ sở dữ liệu nhất định. Đây là cách bạn có thể làm điều đó:

1. Mang lên MỤC LỤC chức năng.

2. Chọn các ô C3: C13 cho Doanh thu nguồn dữ liệu.

   
   

3. Loại CUỘC THI ĐẤU và hãy nhấn Chuyển hướng.

4. Lựa chọn G2 là giá trị tra cứu, B3: B13 dưới dạng dữ liệu nguồn và 0 cho một trận đấu hoàn chỉnh.

   
   

5. Đánh đi vào để tìm nạp thông tin doanh thu cho ứng dụng đã chọn.

6. Thực hiện theo các bước tương tự và thay thế MỤC LỤC nguồn với D3: D13 để có được Lợi nhuận.

   
   

7. Sau đây là công thức làm việc:

=INDEX(C3:C13,MATCH(G2,B3:B13,0))
                    
=INDEX(D3:D13,MATCH(G2,B3:B13,0))

Phần trên là một ví dụ về việc sử dụng một chiều của INDEX MATCH trong Excel. Bạn cũng có thể chọn các hàng và cột cùng nhau để thực hiện các tìm kiếm phức tạp hơn.

Ví dụ: bạn có thể muốn sử dụng một menu thả xuống thay vì các ô riêng biệt cho Doanh thu và Lợi nhuận. Bạn có thể làm theo các bước sau để thực hành:

1. Chọn Doanh thu ô và nhấp vào Dữ liệu trên dải băng.

2. Bấm vào Xác nhận dữ liệu, và dưới Cho phéplựa chọn Danh sách.

   
   

3. Chọn Doanh thu và Lợi nhuận tiêu đề cột như Nguồn.

4. Nhấp chuột VÂNG.

   
   

5. Trong công thức này, bạn sẽ sử dụng CUỘC THI ĐẤU hàm hai lần để tìm nạp cả giá trị hàng và cột cho giá trị cuối cùng MỤC LỤC chức năng.

6. Sao chép-dán công thức sau bên cạnh Doanh thu ô để nhận các giá trị. Chỉ cần sử dụng menu thả xuống để chọn giữa Doanh thu và Lợi nhuận.

=INDEX(C3:D13,MATCH(G2,B3:B13,0),MATCH(F4,C2:D2,0))

Trong công thức trên, MATCH lồng nhau đầu tiên cho Excel biết tên của ứng dụng cần dữ liệu doanh thu và lợi nhuận. Giá trị thứ hai tương ứng với tiêu đề cột doanh thu và lợi nhuận và phản ánh các giá trị có liên quan khi bạn hoán đổi chúng trong menu thả xuống.

Một tình huống phức tạp hơn nữa có thể là tìm kiếm dữ liệu theo ba tiêu đề cột, như tên của ứng dụng, doanh thu dự báo và doanh thu thực. Ở đây, bạn cần sử dụng INDEX và MATCH hai lần để cung cấp cho nhau các đầu vào mà chúng yêu cầu. Bạn có thể thực hành theo các bước sau:

1. Tạo ba menu thả xuống cho bộ chọn tên ứng dụng, true / dự báo và doanh thu / lợi nhuận.

   
   

2. Bây giờ, hãy áp dụng công thức sau bên cạnh tên của ứng dụng mà bạn muốn giá trị tra cứu.

=INDEX(C4:F14,MATCH(I4,B4:B14,0),MATCH(J2&J3,INDEX(C2:F2&C3:F3,0),0))

Đối với cả hai hàm MATCH và INDEX, bạn có thể tách nhiều đầu vào cho các đối số giống nhau bằng cách sử dụng & ra hiệu. Khi bạn cần thực thi một hàm MATCH để xác định vị trí của hai giá trị tra cứu, hãy sử dụng & giữa các lần tra cứu và một INDEX lồng nhau cho một nguồn tra cứu.

Nếu bạn nhận thấy hàm INDEX thứ hai, & dấu hiệu ngăn cách hai mảng nguồn. Không có hàng hoặc cột nào, vì chúng ta cần coi toàn bộ mảng là các giá trị có thể.

Dễ dàng tra cứu dữ liệu quan trọng

Bất cứ khi nào bạn cần tạo trang tổng quan hoặc tra cứu dữ liệu từ một ma trận lớn, hãy thử làm theo các ví dụ trên và áp dụng công thức INDEX MATCH kết hợp để thuận tiện. Hàm kết hợp này mạnh mẽ và linh hoạt hơn so với công thức VLOOKUP thông thường.

Bạn không cần phải lo lắng về những thay đổi của cột hoặc hàng vì Excel sẽ tự động điều chỉnh nguồn dữ liệu đã chọn. Do đó, bạn có thể tìm kiếm hiệu quả hơn trong sổ làm việc Excel của mình.

Đọc tiếp

Giới thiệu về tác giả