Cách Tạo Công thức Tra cứu Excel Với Nhiều Tiêu chí

2024 Tác giả: Abigail Brown | [email protected]. Sửa đổi lần cuối: 2024-01-07 19:07

Điều cần biết

• Đầu tiên, 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à đối số 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.

Theo dõi Cùng 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 dưới đây. 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 trên cùng vào các ô từ D1 đến F2.
• Nhập dải ô thứ hai vào các ô từ D5 đến F11.

Tạo 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để làm ô 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ở hộp thoại Chọn Đối số.
5. Chọn mảng, hàng_num, cột_num.
6. Chọn OK để mở hộp thoại Function Arguments. Trong Excel cho Mac, Trình tạo Công thức sẽ mở ra.
7. Đặt con trỏ vào hộp văn bản Array.

8. Đánh dấu các ô từ D6 đến F11trong 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 Chức năng TRÒ CHUYỆN 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 để 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 hộp thoại Đối số Hàm, đặt con trỏ vào hộp văn bản Row_num.
2. Nhập TRẬN (.
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, các giá trị Tra cứu sẽ được nhập vào các ô D3 và E3 của trang tính.

Hoàn thành Chức năng TRÒ CHUYỆN 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, nên điều này cũng 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ỏ vào cuối dữ liệu trong hộp văn bản Row_num. 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 các ô từ D6 đến D11trong 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 D6: D11. Biểu tượng này khiến hàm tìm kiếm hai mảng.
4. Đánh dấu các ô từ E6 đến E11trong 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 tất việc nhập đối số Tra cứu_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 TRẬN ĐẤU

Đối số thứ ba và cuối cùng của hàm MATCH là đối số Match_type. Đố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 giá trị Tra cứu. 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 hộp văn bản Row_num. Số này khiến 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 chức năng 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 các vật dụng titan.

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

2. Nhập 3(số ba). Con số này cho biết công thức 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: Tiện ích từ cột 1 và Titan từ cột 2.

Để tạo công thức mảng trong Excel, hãy nhấn phím CTRL,SHIFTvà ENTERphí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ảng, nhấn CTRL+ SHIFT+ ENTER.
4. LỗiAN / A xuất hiện trong ô F3. Đây là ô nơi hàm được nhập.

5. LỗiN / 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ụm từ tìm kiếm vào trang tính. Bước này khớp với các điều khoản Widget từ cột 1 và Titanium từ cột 2.

Nếu công thức tìm thấy kết quả phù hợp cho cả hai thuật ngữ trong các cột thích hợp trong cơ sở dữ liệu, nó 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. Gõ Titaniumvà nhấn Nhập.
5. Tên 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ê có bán 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 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 trả về bởi hàm.