Các hàm lấy dữ liệu có điều kiện trong Excel Chắc hẳn các bạn cũng biết, trong các bài Excel nâng cao đều có 1 phần câu hỏi là tìm kiếm dữ liệu phải không nhỉ. Ngoài ra, khi làm việc cũng vậy, việc tìm kiếm dữ liệu thực sự rất quan trọng, nó sẽ quyết định đến năng suất làm việc của các bạn. Sau đây, mình sẽ hướng dẫn cho các bạn về các công thức dò tìm dữ liệu của Excel. 1, Dò tìm một chiều: Mình có ví dụ sau đây và mình quy ước đặt tên vùng: $A$2: $A$8: MS $B$2: $B$8: Ten Ở đây, chắc hẳn trong số các bạn cũng biết, muốn tìm tên thông qua Mã số thì đơn giản chỉ dùng hàmVLOOKUP phải không nào. Ngoài ra, vẫn có rất nhiều cách để dò tìm, mình sẽ dùng hàm OFFSET kết hợp hàm MATCH ** Cú pháp hàm OFFSET =OFFSET (reference, rows, cols, [height], [width]) NHỮNG ĐỐI SỐ BẮT BUỘC: Reference– một ô hoặc một dải ô liền kề mà sau đó nó có thể bị thêm/bớt (Có thể nói đó là điểm khởi đầu/làm mốc) Rows – Số hàng di chuyển từ điểm làm gốc, lên hoặc xuống. Nếu các hàng là một số dương, nghĩa là công thức di chuyển xuống phía dưới tham chiếu, trong trường hợp một số âm thì di chuyển lên phía trên tham chiếu. Cols – Số cột bạn muốn công thức di chuyển, lấy gốc là từ điểm xuất phát. Cũng như rows, cols có thể là số dương (ở bên phải tham chiếu) hoặc số âm (ở bên trái tham chiếu). NHỮNG ĐỐI SỐ TÙY CHỌN: height – chiều cao, tính bằng số hàng, mà bạn muốn tham chiếu trả về Width – chiều rộng, tính bằng số cột, mà bạn muốn tham chiếu trả về ** Cú pháp hàm MATCH: =MATCH (Lookup_value, Lookup_array, [Match_type] ). Trong đó: Lookup_value: Giá trị tìm kiếm trong mảng Lookup_array. Giá trị này có thể là số, văn bản, giá trị logic hoặc một tham chiếu ô đến một số, văn bản hay giá trị logic, bắt buộc phải có. Lookup_array: Mảng hay phạm vị ô được tìm kiếm, bắt buộc có. Match_type: Kiểu tìm kiếm, không nhất thiết phải có. Có 3 kiểu tìm kiếm trong hàm Match trên Excel: [Match_type] 1 hoặc bỏ qua (Less than) : Hàm Match tìm kiếm giá trị lớn nhất mà giá trị đó nhỏ hơn hoặc bằng với lookup_value. Nếu người dùng chọn kiểu tìm kiếm này thì lookup_array phải được sắp xếp theo thứ tự tăng dần. 0 (Exact Match) : Hàm Match sẽ tìm kiếm giá trị thứ nhất bằng chính xác với lookup_value. Các giá trị trong lookup_array có thể được sắp xếp theo bất kỳ giá trị nào. -1 (Greater than) : Hàm Match tìm kiếm giá trị nhỏ nhất mà giá trị đó lớn hoặc bằng với lookup_value. Giá trị trong lookup_array phải được sắp xếp theo thứ tự giảm dần. \ =OFFSET (A1, MATCH ($E$2, MS, 0), 1) Theo hàm này, chúng ta sẽ bắt đầu từ ô A1, theo hàm MATCH đi theo hàng. Như các bạn thấy, Mã 1254 sẽ là vị trí của ô đầu tiên bắt đầu từ A2, tức số 1. Như vậy, theo lệnh của hàm OFFSET sẽ xuống 1 ô, và theo cột thì sẽ qua 1 ô. Ngoài hàm OFFSET ra, các bạn cung có thể dùng hàm INDEX kết hợp với hàm MATCH =INDEX (A1: B8, MATCH (E2, A1: A8, 0), MATCH (D4, A1: B1, 0)) Hàm INDEX sẽ lấy giá trị giao nhau giữa hai cột và hàng, vì thế khi ta kết hợp hàm MATCH sẽ chỉ ra sẵn vị trí của ô giao nhau. Các bạn nên nhớ khi dùng hàm INDEX, các bạn phải có lấy luôn trường của nó chính là [Mã số] và [tên] 2, dò tìm ngược chiều. Dò tìm ngược chiều nó cũng giống như dò tìm 1 chiều, nhưng có sự kết hợp hiều hơn A, dò tìm ngược chiều với hàm VLOOKUP (hàm VLOOKUP ngược) Hàm VLOOKUP chỉ có 1 cách dò tìm đó là dò tìm từ trái sang phải, nhưng nếu bạn muốn dò tìm từ phải sang trái, các bạn phải dùng hàm CHOOSE, thực chất, việc này không giúp cho hàm VLOOKUP có thể dò tìm ngược mà chỉ là hàm CHOOSE đảo ngược bảng tính mà thôi, hàm VLOOKUP vẫn phải tìm từ trái sang phải. =VLOOKUP (E7, CHOOSE ({2, 1}, MS, ten), 2, 0) Khi ta khai triển hàm CHOOSE, sẽ được: B, Hàm OFFSET dò ngược Hàm OFFSET có kiểu đường đi rất đa dạng, nó có thể nhận đối số là số âm, vì thế nó có thể đi ngược Ở dò tìm 1 chiều, đối số là Col, bạn chỉ cần gán đối số là -1 thì nó có thể đi từ phải sang trái C, Hàm XLOOKUP Hàm XLOOKUP chỉ hỗ trợ trên phiên bản của office 365, đây là một hàm đa năng, nó có thể cho phép dò tìm ngược, hoặc dò tìm xuôi đều được = XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] ) Trong đó: lookup_value: Là giá trị tìm kiếm lookup_array: Là vùng (mảng) tìm kiếm return_array: Là vùng (mảng) mà ở đó bạn muốn trả về kết quả [match_mode]: Là lựa chọn chế độ khớp giá trị khi tìm kiếm [search_mode]: Là lựa chọn chế độ tìm kiếm Lưu ý: lookup_value: Có thể là 1 giá trị hoặc 1 mảng giá trị bạn muốn tìm kiếm lookup_array và return_array: Cần khớp nhau về kích thước [match_mode]: Mặc định là tìm kiếm chính xác (0 – Exact match) [search_mode]: Mặc định là tìm kiếm từ đầu đến cuối (1 – Search first-to-last)
