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

Bằng cách sử dụng công thức mảng trong Excel, chúng ta có thể tạo công thức tra cứu sử dụng nhiều tiêu chí để tìm thông tin trong cơ sở dữ liệu hoặc bảng dữ liệu.

Công thức mảng bao gồm việc lồng hàm MATCH bên trong hàm INDEX .

Hướng dẫn này bao gồm một ví dụ từng bước về việc tạo một công thức tra cứu sử dụng nhiều tiêu chí để tìm một nhà cung cấp các Widget titan trong một cơ sở dữ liệu mẫu.

Làm theo các bước trong các chủ đề hướng dẫn dưới đây hướng dẫn bạn tạo và sử dụng công thức được thấy trong hình trên.

01/09

Nhập dữ liệu hướng dẫn

Bước đầu tiên trong hướng dẫn này là nhập dữ liệu vào trang tính Excel.

Để làm theo các bước trong hướng dẫn, hãy nhập dữ liệu được hiển thị trong hình trên vào các ô sau.

• Nhập phạm vi dữ liệu hàng đầu vào ô D1 đến F2
• Nhập dãy thứ hai vào ô D5 đến F11

Hàng 3 và 4 được để trống để chứa công thức mảng được tạo trong hướng dẫn này.

Hướng dẫn không bao gồm định dạng được nhìn thấy trong hình ảnh, nhưng điều này sẽ không ảnh hưởng đến cách công thức tra cứu hoạt động.

Thông tin về các tùy chọn định dạng tương tự như các tùy chọn ở trên có sẵn trong Hướng dẫn định dạng Excel cơ bản này.

02/09

Bắt đầu hàm INDEX

Hàm INDEX là một trong số ít hàm trong Excel có nhiều biểu mẫu. Hàm này có một mẫu Array và một Form tham chiếu .

Biểu mẫu mảng trả về dữ liệu thực tế từ cơ sở dữ liệu hoặc bảng dữ liệu, trong khi biểu mẫu tham chiếu cung cấp cho bạn tham chiếu ô hoặc vị trí của dữ liệu trong bảng.

Trong hướng dẫn này, chúng ta sẽ sử dụng Form Array vì chúng ta muốn biết tên của một nhà cung cấp cho các vật dụng titan hơn là tham chiếu ô tới nhà cung cấp này trong cơ sở dữ liệu của chúng ta.

Mỗi biểu mẫu có một danh sách các đối số khác nhau phải được chọn trước khi bắt đầu hàm.

Các bước hướng dẫn

1. Bấm vào ô F3 để biến nó thành ô hiện hoạt . Đây là nơi chúng ta sẽ nhập hàm lồng nhau.
2. Nhấp vào tab Công thức của trình đơn ribbon .
3. Chọn tra cứu và tham khảo từ ruy-băng để mở danh sách thả xuống chức năng.
4. Nhấp vào INDEX trong danh sách để hiển thị hộp thoại Chọn đối số .
5. Chọn mảng, row_num, col_num tùy chọn trong hộp thoại.
6. Nhấn OK để mở hộp thoại hàm INDEX.

03/09

Nhập đối số mảng hàm INDEX

Đối số đầu tiên được yêu cầu là đối số Array. Đối số này xác định phạm vi ô cần tìm kiếm dữ liệu mong muốn.

Đối với hướng dẫn này, đối số này sẽ là cơ sở dữ liệu mẫu của chúng tôi.

Các bước hướng dẫn

1. Trong hộp thoại hàm INDEX, bấm vào dòng Array .
2. Đánh dấu các ô từ D6 đến F11 trong trang tính để nhập phạm vi vào hộp thoại.

04/09

Bắt đầu chức năng MATCH lồng nhau

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

Hàm lồng nhau phải được nhập là một trong các đối số của hàm đầu tiên.

Trong hướng dẫn này, hàm MATCH lồng nhau và các đối số của nó sẽ được nhập vào dòng thứ hai của hộp thoại hàm INDEX - dòng Row_num .

Điều quan trọng cần lưu ý là, khi nhập các hàm theo cách thủ công, các đối số của hàm được tách biệt với nhau bằng dấu phẩy "," .

Nhập đối số Lookup_value của hàm MATCH

Bước đầu tiên khi nhập hàm MATCH lồng nhau là nhập đối số Lookup_value .

Lookup_value sẽ là tham chiếu vị trí hoặc ô cho cụm từ tìm kiếm mà chúng tôi muốn đối sánh trong cơ sở dữ liệu.

Thông thường, hàm Lookup_value chỉ chấp nhận một tiêu chí hoặc thuật ngữ tìm kiếm. Để tìm kiếm nhiều tiêu chí, chúng tôi phải mở rộng Lookup_value .

Điều này được thực hiện bằng cách nối hoặc ghép hai hoặc nhiều tham chiếu ô với nhau bằng ký hiệu " & ".

Các bước hướng dẫn

1. Trong hộp thoại hàm INDEX, bấm vào dòng Row_num .
2. Nhập đối sánh tên hàm theo sau là dấu ngoặc tròn mở " ( "
3. Nhấp vào ô D3 để nhập tham chiếu ô đó vào hộp thoại.
4. Nhập ký tự " & " sau tham chiếu ô D3 để thêm tham chiếu ô thứ hai.
5. Bấm vào ô E3 để nhập tham chiếu ô thứ hai này vào hộp thoại.
6. Nhập dấu phẩy "," sau tham chiếu ô E3 để hoàn thành mục nhập của đối số Lookup_value của hàm MATCH.
7. Để hộp thoại hàm INDEX mở cho bước tiếp theo trong hướng dẫn.

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.

05/09

Thêm hàm Lookup_array cho hàm MATCH

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à phạm vi ô mà hàm MATCH sẽ 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.

Vì chúng ta đã xác định hai trường tìm kiếm trong đối số Lookup_array, chúng ta phải làm tương tự 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, chúng tôi lại sử dụng dấu " & " để ghép các mảng lại với nhau.

Các bước hướng dẫn

Các bước này sẽ được nhập sau khi dấu phẩy được nhập ở bước trước trên dòng Row_num trong hộp thoại hàm INDEX.

1. Nhấp vào dòng Row_num sau dấu phẩy để đặt điểm chèn vào cuối mục nhập hiện tại.
2. Đánh dấu các ô từ D6 đến D11 trong trang tính để nhập phạm vi. Đây là mảng đầu tiên mà hàm tìm kiếm.
3. Nhập ký hiệu " & " sau khi tham chiếu ô D6: D11 vì chúng tôi muốn hàm tìm kiếm hai mảng.
4. Đánh dấu các ô từ E6 đến E11 trong trang tính để nhập phạm vi. Đây là mảng thứ hai hàm tìm kiếm.
5. Nhập dấu phẩy "," sau tham chiếu ô E3 để hoàn thành mục nhập của đối số Lookup_array của hàm MATCH.
6. Để hộp thoại hàm INDEX mở cho bước tiếp theo trong hướng dẫn.

06/09

Thêm loại đối sánh và hoàn thành chức năng MATCH

Đố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 khớp với giá trị Lookup_value với các giá trị trong Lookup_array. Các lựa chọn là: 1, 0 hoặc -1.

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

• nếu Match_type = 1 hoặc bị bỏ qua: MATCH tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị 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 chính xác bằng giá trị Lookup_value. Dữ liệu Lookup_array có thể được sắp xếp theo thứ tự bất kỳ.
• nếu Match_type = -1: MATCH tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị Lookup_value. Dữ liệu Lookup_array phải được sắp xếp theo thứ tự giảm dần.

Các bước hướng dẫn

Các bước này sẽ được nhập sau khi dấu phẩy được nhập ở bước trước trên dòng Row_num trong hộp thoại hàm INDEX.

1. Theo dấu phẩy trên dòng Row_num , nhập số 0 "" vì chúng ta muốn hàm lồng nhau trả về kết quả khớp chính xác với các thuật ngữ chúng ta nhập vào ô D3 và E3.
2. Gõ một dấu ngoặc tròn " ) " để hoàn thành hàm MATCH.
3. Để hộp thoại hàm INDEX mở cho bước tiếp theo trong hướng dẫn.

07/09

Quay lại hàm INDEX

Bây giờ chức năng MATCH được thực hiện, chúng ta sẽ chuyển sang dòng thứ ba của hộp thoại mở và nhập đối số cuối cùng cho hàm INDEX.

Đối số thứ ba và cuối cùng này là đối số Column_num cho Excel biết số cột trong phạm vi từ D6 đến F11, nơi nó sẽ tìm thấy thông tin mà chúng ta muốn 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 .

Các bước hướng dẫn

1. Nhấp vào dòng Column_num trong hộp thoại.
2. Nhập số ba " 3 " (không có dấu ngoặc kép) trên dòng này vì chúng tôi đang tìm kiếm dữ liệu trong cột thứ ba của phạm vi từ D6 đến F11.
3. Không Nhấp vào OK hoặc đóng hộp thoại hàm INDEX. Nó vẫn phải mở cho bước tiếp theo trong hướng dẫn - tạo công thức mảng .

08/09

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

Trước khi đóng hộp thoại, chúng ta cần chuyển hàm lồng vào một công thức mảng .

Công thức mảng là thứ cho phép nó tìm kiếm nhiều cụm từ trong bảng dữ liệu. Trong hướng dẫn này, chúng tôi đang tìm kiếm để phù hợp với hai thuật ngữ: Widget từ cột 1 và titan từ cột 2.

Tạo một công thức mảng trong Excel được thực hiện bằng cách nhấn các phím CTRL , SHIFT và ENTER trên bàn phím cùng một lúc.

Ảnh hưởng của việc nhấn các phím này lại với nhau là bao quanh hàm với các dấu ngoặc nhọn: {} chỉ ra rằng nó bây giờ là một công thức mảng.

Các bước hướng dẫn

1. Với hộp thoại hoàn thành vẫn mở từ bước trước của hướng dẫn này, hãy bấm và giữ phím CTRL và SHIFT trên bàn phím, sau đó bấm và nhả phím ENTER .
2. Nếu được thực hiện đúng, hộp thoại sẽ đóng lại và lỗi # N / A sẽ xuất hiện trong ô F3 - ô mà chúng tôi đã nhập hàm.
3. Lỗi # N / A xuất hiện trong ô F3 vì các ô D3 và E3 trống. D3 và E3 là các ô mà chúng ta đã nói với hàm để tìm các giá trị Lookup_values ​​trong bước 5 của hướng dẫn. Khi dữ liệu được thêm vào hai ô này, lỗi sẽ được thay thế bằng thông tin từ cơ sở dữ liệu .

09/09

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

Bước cuối cùng trong hướng dẫn là thêm cụm từ tìm kiếm vào trang tính của chúng tôi.

Như đã đề cập trong bước trước, chúng tôi đang tìm kiếm để phù hợp với các điều khoản Widget từ cột 1 và Titanium từ cột 2.

Nếu, và chỉ khi, công thức của chúng ta tìm thấy một kết quả phù hợp cho cả hai từ 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.

Các bước hướng dẫn

1. Nhấp vào ô D3.
2. Nhập Widgets và nhấn phím Enter trên bàn phím.
3. Bấm vào ô E3.
4. Nhập Titanium và nhấn phím Enter trên bàn phím.
5. Tên của nhà cung cấp Widgets Inc. sẽ xuất hiện trong ô F3 - vị trí của hàm vì nó là nhà cung cấp duy nhất được liệt kê bán Widget Titanium.
6. Khi bạn bấm vào ô F3, hàm hoàn chỉnh
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   xuất hiện trong thanh công thức phía trên trang tính .

Lưu ý: Trong ví dụ của chúng tôi 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, nhà cung cấp được liệt kê đầu tiên trong cơ sở dữ liệu được hàm trả về.