Cách tạo công thức tra cứu Excel với nhiều tiêu chí

Những điều cần biết

• Đầu tiên, hãy tạo một hàm INDEX, sau đó khởi động hàm MATCH lồng nhau bằng cách nhập đối số Lookup_value.
• Tiếp theo, thêm đối số Lookup_array theo sau là Match_type , sau đó chỉ định phạm vi cột.
• Sau đó, chuyển hàm lồng nhau thành công thức mảng bằng cách nhấn Ctrl + Shift + Nhập . Cuối cùng, thêm các cụm từ tìm kiếm vào trang tính.

Bài viết này giải thích cách tạo công thức tra cứu sử dụng nhiều tiêu chí trong Excel để tìm thông tin trong cơ sở dữ liệu hoặc bảng dữ liệu bằng cách sử dụng công thức mảng. Công thức mảng liên quan đến việc lồng hàm MATCH bên trong hàm INDEX. Thông tin bao gồm Excel cho Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và Excel cho Mac.

Làm theo Hướng dẫn

Để làm theo các bước trong hướng dẫn này, hãy nhập dữ liệu mẫu vào các ô sau, như thể hiện trong hình ảnh bên dưới. Hàng 3 và 4 được để trống để phù hợp với công thức mảng được tạo trong hướng dẫn này. (Lưu ý rằng hướng dẫn này không bao gồm định dạng được thấy trong hình ảnh.)

• Nhập dải dữ liệu hàng đầu vào các ô từ D1 đến F2.
• Nhập dải ô thứ hai vào các ô từ D5 đến F11.

Tạo một hàm INDEX trong Excel

Hàm INDEX là một trong số ít các hàm trong Excel có nhiều biểu mẫu. Hàm có Biểu mẫu mảng và Biểu mẫu tham chiếu. Biểu mẫu Mảng trả về dữ liệu từ cơ sở dữ liệu hoặc bảng dữ liệu. Biểu mẫu Tham chiếu cung cấp tham chiếu ô hoặc vị trí của dữ liệu trong bảng.

Trong hướng dẫn này, Biểu mẫu Mảng được sử dụng để tìm tên của nhà cung cấp cho các vật dụng titan, thay vì tham chiếu ô đến nhà cung cấp này trong cơ sở dữ liệu.

Làm theo các bước sau để tạo hàm INDEX:

1. Chọn ô F3 để biến nó thành ô hoạt động. Ô này là nơi hàm lồng nhau sẽ được nhập.

2. Đi tới Công thức .

   

3. Chọn Tra cứu &Tham khảo để mở danh sách chức năng thả xuống.

4. Chọn INDEX để mở Chọn đối số hộp thoại.

5. Chọn mảng, row_num, column_num .

6. Chọn OK để mở Đối số hàm hộp thoại. Trong Excel cho Mac, Trình tạo Công thức sẽ mở ra.

7. Đặt con trỏ vào Mảng hộp văn bản.

8. Đánh dấu ô D6 thông qua F11 trong trang tính để nhập phạm vi vào hộp thoại.

   Để mở hộp thoại Đối số hàm. Công thức chưa hoàn thành. Bạn sẽ hoàn thành công thức trong hướng dẫn bên dưới.

   

Khởi động Hàm MATCH lồng nhau

Khi lồng một hàm vào bên trong một hàm khác, không thể mở trình tạo công thức của hàm thứ hai hoặc được lồng vào nhau để nhập các đối số cần thiết. Hàm lồng nhau phải được nhập làm một trong các đối số của hàm đầu tiên.

Khi nhập các hàm theo cách thủ công, các đối số của hàm được phân tách với nhau bằng dấu phẩy.

Bước đầu tiên để nhập hàm MATCH lồng nhau là nhập đối số Lookup_value. Lookup_value là vị trí hoặc tham chiếu ô cho cụm từ tìm kiếm được so khớp trong cơ sở dữ liệu.

Lookup_value chỉ chấp nhận một tiêu chí hoặc cụm từ tìm kiếm. Để tìm kiếm nhiều tiêu chí, hãy mở rộng Lookup_value bằng cách nối hoặc nối hai hoặc nhiều tham chiếu ô bằng cách sử dụng ký hiệu và (&).

1. Trong Đối số hàm hộp thoại, đặt con trỏ vào Row_num hộp văn bản.

2. Nhập MATCH ( .

3. Chọn ô D3 để nhập tham chiếu ô đó vào hộp thoại.

4. Nhập & (dấu và) sau tham chiếu ô D3 để thêm tham chiếu ô thứ hai.

5. Chọn ô E3 để nhập tham chiếu ô thứ hai.

6. Nhập , (dấu phẩy) sau tham chiếu ô E3 để hoàn tất việc nhập đối số Lookup_value của hàm MATCH.

   

   Trong bước cuối cùng của hướng dẫn, Lookup_values ​​sẽ được nhập vào các ô D3 và E3 của trang tính.

Hoàn thành Hàm MATCH lồng nhau

Bước này bao gồm việc thêm đối số Lookup_array cho hàm MATCH lồng nhau. Lookup_array là dải ô mà hàm MATCH tìm kiếm để tìm đối số Lookup_value được thêm vào trong bước trước của hướng dẫn.

Bởi vì hai trường tìm kiếm đã được xác định trong đối số Lookup_array, điều tương tự phải được thực hiện cho Lookup_array. Hàm MATCH chỉ tìm kiếm một mảng cho mỗi thuật ngữ được chỉ định. Để nhập nhiều mảng, hãy sử dụng dấu và để nối các mảng với nhau.

1. Đặt con trỏ ở cuối dữ liệu trong Row_num hộp văn bản. Con trỏ xuất hiện sau dấu phẩy ở cuối mục nhập hiện tại.

2. Đánh dấu ô D6 thông qua D11 trong trang tính để nhập phạm vi. Phạm vi này là mảng đầu tiên mà hàm tìm kiếm.

3. Nhập & (dấu và) sau ô tham chiếu đến D6:D11 . Biểu tượng này làm cho hàm tìm kiếm hai mảng.

4. Đánh dấu ô E6 thông qua E11 trong trang tính để nhập phạm vi. Phạm vi này là mảng thứ hai mà hàm tìm kiếm.

5. Nhập , (dấu phẩy) sau tham chiếu ô E3 để hoàn thành việc nhập đối số Lookup_array của hàm MATCH.

   

6. Để hộp thoại mở cho bước tiếp theo trong hướng dẫn.

Thêm đối số loại MATCH

Đối số thứ ba và cuối cùng của Hàm MATCH là Match_type lý lẽ. Đối số này cho Excel biết cách đối sánh Lookup_value với các giá trị trong Lookup_array. Các lựa chọn có sẵn là 1, 0 hoặc -1.

Đối số này là tùy chọn. Nếu nó bị bỏ qua, hàm sẽ sử dụng giá trị mặc định là 1.

• Nếu Match_type =1 hoặc bị bỏ qua, MATCH sẽ tìm giá trị lớn nhất nhỏ hơn hoặc bằng Lookup_value. Dữ liệu Lookup_array phải được sắp xếp theo thứ tự tăng dần.
• Nếu Match_type =0, MATCH tìm giá trị đầu tiên bằng với Lookup_value. Dữ liệu Lookup_array có thể được sắp xếp theo bất kỳ thứ tự nào.
• Nếu Match_type =-1, MATCH tìm giá trị nhỏ nhất lớn hơn hoặc bằng Lookup_value. Dữ liệu Lookup_array phải được sắp xếp theo thứ tự giảm dần.

Nhập các bước này sau dấu phẩy đã nhập ở bước trước trên dòng Row_num trong hàm INDEX:

1. Nhập 0 (số 0) sau dấu phẩy trong Row_num hộp văn bản. Số này làm cho hàm lồng nhau trả về kết quả khớp chính xác với các cụm từ được nhập trong ô D3 và E3.

2. Nhập ) (dấu ngoặc tròn đóng) để hoàn thành hàm MATCH.

   

3. Để hộp thoại mở cho bước tiếp theo trong hướng dẫn.

Kết thúc Hàm INDEX

Chức năng MATCH được thực hiện. Đã đến lúc chuyển đến hộp văn bản Column_num của hộp thoại và nhập đối số cuối cùng cho hàm INDEX. Đối số này cho Excel biết số cột nằm trong phạm vi từ D6 đến F11. Phạm vi này là nơi nó tìm thấy thông tin được trả về bởi hàm. Trong trường hợp này, một nhà cung cấp cho các vật dụng titan.

1. Đặt con trỏ vào Column_num hộp văn bản.

2. Nhập 3 (số ba). Con số này cho công thức biết để tìm kiếm dữ liệu trong cột thứ ba của dải ô từ D6 đến F11.

   

3. Để hộp thoại mở cho bước tiếp theo trong hướng dẫn.

Tạo công thức mảng

Trước khi đóng hộp thoại, hãy chuyển hàm lồng nhau thành công thức mảng. Mảng này cho phép hàm tìm kiếm nhiều từ trong bảng dữ liệu. Trong hướng dẫn này, hai thuật ngữ được khớp với nhau:Widget từ cột 1 và Titanium từ cột 2.

Để tạo công thức mảng trong Excel, hãy nhấn CTRL , SHIFT và ENTER các phím đồng thời. Sau khi được nhấn, hàm được bao quanh bởi dấu ngoặc nhọn, cho biết rằng hàm bây giờ là một mảng.

1. Chọn OK để đóng hộp thoại. Trong Excel cho Mac, chọn Xong .

2. Chọn ô F3 để xem công thức, sau đó đặt con trỏ vào cuối công thức trong Thanh công thức.

3. Để chuyển đổi công thức thành một mảng, nhấn CTRL + SHIFT + ENTER .

4. A # N / A lỗi xuất hiện trong ô F3. Đây là ô nơi hàm đã được nhập.

5. Lỗi # N / A xuất hiện trong ô F3 vì ô D3 và E3 trống. D3 và E3 là các ô mà hàm sẽ tìm kiếm Lookup_value. Sau khi dữ liệu được thêm vào hai ô này, lỗi được thay thế bằng thông tin từ cơ sở dữ liệu.

   

Thêm tiêu chí tìm kiếm

Bước cuối cùng là thêm các cụm từ tìm kiếm vào trang tính. Bước này khớp với các thuật ngữ Tiện ích từ cột 1 và Titan từ cột 2.

Nếu công thức tìm thấy kết quả phù hợp cho cả hai cụm từ trong các cột thích hợp trong cơ sở dữ liệu, công thức sẽ trả về giá trị từ cột thứ ba.

1. Chọn ô D3 .

2. Nhập Tiện ích .

3. Chọn ô E3 .

4. Nhập Titan và nhấn Enter .

5. Tên của nhà cung cấp, Widgets Inc., xuất hiện trong ô F3. Đây là nhà cung cấp duy nhất được liệt kê bán các vật dụng Titanium.

6. Chọn ô F3 . Hàm xuất hiện trong thanh công thức phía trên trang tính.

   {=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}
   
   

   Trong ví dụ này, chỉ có một nhà cung cấp cho các vật dụng titan. Nếu có nhiều hơn một nhà cung cấp, thì nhà cung cấp được liệt kê đầu tiên trong cơ sở dữ liệu sẽ được hàm trả về.