31/08/2019

Hàm XLookup

Sử dụng hàm Xlookup khi bạn cần tìm mọi thứ trong bảng hoặc một phạm vi theo hàng .  Ví dụ, tra cứu giá của một phần ô tô bằng số phần, hoặc... thumbnail 1 summary
Sử dụng hàm Xlookup khi bạn cần tìm mọi thứ trong bảng hoặc một phạm vi theo hàng
Ví dụ, tra cứu giá của một phần ô tô bằng số phần, hoặc tìm tên nhân viên dựa trên ID nhân viên của họ. Với XLOOKUP, bạn có thể nhìn vào một cột cho thuật ngữ tìm kiếm và trả về kết quả từ cùng một hàng trong một cột khác, bất kể cột trả về bên nào.

Trong ví dụ này, chúng tôi đang tìm kiếm thông tin nhân viên dựa trên số ID nhân viên. Không giống như VLOOKUP, XLOOKUP có thể trả về một mảng với nhiều mục, cho phép một công thức duy nhất để trả về cả tên nhân viên và bộ phận.
Ví dụ về hàm XLOOKUP được sử dụng để trả về một tên nhân viên và bộ phận dựa trên ID nhân viên. Công thức là = XLOOKUP (B2, B5: B14, C5: C14).

Cú pháp

Hàm XLOOKUP tìm kiếm một phạm vi hoặc một mảng và trả về một mục tương ứng với khớp đầu tiên nó tìm thấy. Nếu khớp không tồn tại, thì XLOOKUP có thể trả về kết quả khớp gần nhất (xấp xỉ).

XLOOKUP (lookup_value, mảng tìm kiếm, return_array, [match_mode], [search_mode])
Đối số
Mô tả
lookup_value
Giá trị tra cứu
mảng tìm kiếm
Mảng hoặc dải ô cần tìm kiếm
return_array
Mảng hoặc dải ô cần trả về
match_mode
Tùy chọn
Xác định kiểu khớp:
0-khớp chính xác. Nếu không tìm thấy gì, hãy trả về #N/A. Đây là tùy chọn mặc định.
-1-khớp chính xác. Nếu không tìm thấy gì, hãy trả về mục nhỏ hơn tiếp theo.
1-khớp chính xác. Nếu không tìm thấy gì, hãy trả về mục lớn hơn tiếp theo.
2-một ký tự đại diện trong đó *,?, và ~ có ý nghĩa đặc biệt.
search_mode
Tùy chọn
Xác định chế độ tìm kiếm để sử dụng:
1-thực hiện tìm kiếm bắt đầu từ mục đầu tiên. Đây là tùy chọn mặc định.
-1-thực hiện tìm kiếm đảo ngược bắt đầu ở mục cuối cùng.
2-thực hiện tìm kiếm nhị phân dựa trên mảng tìm kiếm đang được sắp xếp theo thứ tự tăng dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.
-2-thực hiện tìm kiếm nhị phân dựa trên mảng tìm kiếm đang được sắp xếp theo thứ tự giảm dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.

Ví dụ

Ví dụ 1
Ví dụ sau đây sử dụng XLOOKUP đơn giản để tìm kiếm một tên quốc gia và trả về mã quốc gia của điện thoại. Nó chỉ bao gồm lookup_value (ô F2), mảng tìm kiếm (phạm vi B2: B11), và return_array (phạm vi D2: D11) đối số. Nó không bao gồm đối số match_mode, như nó mặc định là kết quả khớp chính xác.
Hình ảnh của hàm XLOOKUP được sử dụng để trả về mã quay số quốc gia từ một bảng.
Lưu ý: XLOOKUP khác với hàm VLOOKUP trong đó nó dùng các mảng tra cứu và trả về riêng biệt, trong đó VLOOKUP sử dụng một mảng bảng đơn, theo sau là một số chỉ số cột. Công thức VLOOKUP tương đương trong trường hợp này sẽ là: = VLOOKUP (F2, B2: D11, 3, FALSE)

Ví dụ 2

Ví dụ sau đây sẽ hiển thị trong cột C cho thu nhập cá nhân được nhập vào ô E2 và tìm thấy tỷ suất thuế kết hợp trong cột B. Nó dùng đối số match_mode đặt thành 1, có nghĩa là hàm sẽ tìm kiếm kết quả phù hợp chính xác và nếu nó không thể tìm thấy một, nó sẽ trả về mục lớn hơn tiếp theo.
Hình ảnh của hàm XLOOKUP được sử dụng để trả về lãi suất dựa trên thu nhập tối đa. Đây là kết quả khớp xấp xỉ.
Lưu ý: Không giống như VLOOKUP, cột mảng tìm kiếm nằm bên phải của cột return_array, trong đó VLOOKUP chỉ có thể nhìn từ trái sang phải.

Ví dụ 3

Tiếp theo, chúng tôi sẽ sử dụng hàm XLOOKUP lồng nhau để thực hiện cả khớp dọc và ngang. Trong trường hợp này, trước tiên, nó sẽ tìm kiếm lợi nhuận gộp trong cột B, sau đó tìm Qtr1 ở hàng trên cùng của bảng (phạm vi C5: F5) và trả về giá trị tại giao điểm của hai. Điều này tương tự như việc sử dụng các hàm index và match kết hợp .
Hình ảnh của hàm XLOOKUP được sử dụng để trả về dữ liệu ngang từ một bảng bằng cách làm tổ 2 Xtra cứu.
Công thức trong các ô D3: F3 là: = XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17)).

Ví dụ 4

Ví dụ này sử dụng hàm Sumvà hai hàm xlookup được lồng với nhau để tính tổng tất cả các giá trị giữa hai phạm vi. Trong trường hợp này, chúng tôi muốn tính tổng các giá trị cho nho, chuối và lê, nằm giữa hai.
Hình ảnh sử dụng lồng XLOOKUPs bằng tính tổng để thêm giá trị giữa các điểm bắt đầu và kết thúc.
Công thức trong ô E3 là: = SUM (XLOOKUP (C3, C6: C10, F6: F10): XLOOKUP (D3, C6: C10, F6: F10))
Tính năng này hoạt động như thế nào? XLOOKUP trả về một phạm vi, vì vậy khi tính năng này tính toán, công thức sẽ kết thúc việc trông như thế này: = SUM ($F $7: $F $9). Bạn có thể thấy cách làm việc này trên riêng của bạn bằng cách chọn một ô có công thức XLOOKUP tương tự như phần này, rồi đi đến công thức > kiểm nghiệm công thức > đánh giá công thứcvà nhấn nút đánh giá để bước qua tính toán.
Lưu ý: Nhờ Microsoft Excel MVP, Bill Jelen, cho thấy ví dụ này.
===================================================Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

Không có nhận xét nào

Đăng nhận xét