Trong lần cập nhật mới đây, Microsoft đã đưa cho người dùng thêm một số hàm mới, với mục đích nhằm khắc phục cũng như bổ sung thêm các tính năng mới cho Excel. Và hàm XLOOKUP là một trong những hàm xuất hiện trong lần cập nhật này. Vậy hàm này có những điểm gì nổi bật và thú vị hơn các hàm tìm kiếm khác như VLOOKUP, HLOOKUP,…
Trong đó:
Lưu ý:
Nếu như bạn nắm được các hàm tìm kiếm VLOOKUP hay HLOOKUP thì mình nghĩ sẽ không khó khăn gì với việc đón nhận hà m XLOOKUP này. Các đối số gần như tuong tự. Vậy hàm XLOOKUP này nó làm được những gì?
Với đối số thứ 5 của hàm là -1 (Search last-to-first).
Với cùng hàng hoá 1, công thức ở ô E3 có tham số thứ 5 là 1 (Search first-to-last) thì sẽ tìm từ đầu đến cuối trong lookup_array (tương tự hàm VLOOKUP).
Còn công thức ở ô E5 có tham số thứ 5 là -1 (Search last-to-first) thì hàm sẽ thực hiện bắt đầu tìm kiếm từ cuối lên đầu của lookup_array (Điều này hàm VLOOKUP không thể làm theo một cách thông thường).
Công thức ở ô I4 như sau:
=XLOOKUP(H2,A4:A7,A4:F7)
Hàm sẽ thực hiện tìm kiếm giá trị trong ô H2 trong vùng A4:A7 và vùng trả về kết quả ở đây là vùng từ A4:F7. Tương tự với các công thức ở ô I6 và I8.
Nếu như bạn chưa kịp cập nhật để có hàm này thì Học Excel Online gợi ý cho bạn để cũng có thể ra kết quả với việc tìm kiếm khi nối các giá trị như trên bằng hàm INDEX + MATCH:
Mình có sự dụng hàm UNIQUE – một hàm để loại bỏ giá trị trùng lặp và trả về danh sách duy nhất.
Một lưu ý khi tìm kiếm theo cả một mảng giá trị là: Kết quả trả về là 1 mảng giá trị theo chiều dọc (tức gồm nhiều hàng nhưng chỉ có 1 cột).
Từ đây bạn có thể rút ra là:
Bạn có thể hiểu mảng trả về này là mảng 1 chiều, hoặc 2 chiều nhưng trong 2 chiều này phải có 1 chiều với kích thước bằng 1. Hàm XLOOKUP sẽ không trả về được 1 mảng giá trị gồm nhiều hàng và nhiều cột (>=2 hàng và >=2 cột), bạn có thể thấy điều đó qua ví dụ trên (ô I12).
Sử dụng các hàm khác kết hợp với XLOOKUP
Excel cho phép bạn kết hợp các hàm lồng vào nhau một cách rất linh hoạt, bạn hoàn toàn có thể tuỳ biến công thức theo ý của mình để có được kết quả mong muốn. Hàm XLOOKUP cũng không ngoại lệ.
Vận dụng việc hàm XLOOKUP có thể trả về cả mảng giá trị trên, ta có thể tính tổng số lượng của những hàng hoá phát sinh vào lần cuối cùng.
Với hàm trên bạn có thể thấy kết quả trả về không chỉ là những con số khô khan nữa. Vận dụng việc hàm trả về cả mảng giá trị ta kết hợp thêm các chuỗi TEXT lồng vào trong kết quả trả về để xem số lượng và thành tiền phát sinh lần đầu tiên cũng như cuối cùng của các hàng hoá có phát sinh.
Trong ví dụ dưới, khi mình thực hiện chèn thêm cột vào giữa cột A và B thì kết quả ở ô D3 sử dụng hàm XLOOKUP không bị thay đổi còn với ô D2 sử dụng hàm VLOOKUP thì kết quả đã bị thay đổi (dẫn tới sai). Tương tự với hàm HLOOKUP, khi mình chèn thêm 1 dòng vào giữa dòng 7 và 8 thì kết quả ở ô H6 sử dụng hàm XLOOKUP không bị thay đổi còn ô H5 sử dụng hàm HLOOKUP thì kết quả đã bị thay đổi (dẫn tới sai).
Với phần Comnent bên dưới, chúng tôi hy vọng sẽ nhận được thêm những góp ý cho bài viết cũng như nếu như bạn phát hiện ra các điều thú vị khác nữa về hàm này 😊
Một số điểm thú vị về hàm XLOOKUP
Chúng ta đã biết đến các hàm dò tìm như VLOOKUP, HLOOKUP. Những hàm này trước giờ hầu như đã được sử dụng khác phổ biến và nếu như bạn đang cần thì có thể tham khảo ngay dưới đây.Cú phát hàm XLOOKUP
Hàm XLOOKUP sẽ có 5 đối số, tuy nhiên trong đó chỉ có 3 đối số đầu tiên là bắt buộc còn 2 đối số sau là tuỳ chọn, cụ thể như sau:
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)
Nếu như bạn nắm được các hàm tìm kiếm VLOOKUP hay HLOOKUP thì mình nghĩ sẽ không khó khăn gì với việc đón nhận hà m XLOOKUP này. Các đối số gần như tuong tự. Vậy hàm XLOOKUP này nó làm được những gì?
Một số điểm thú vị của hàm XLOOKUP
Hàm Excel có thể tìm kiếm “bên TRÁI”
Ta đã biết thì một trong những hạn chế của hàm VLOOKUP là kết quả trả về của hàm chỉ có thể là 1 cột nào đó nằm bên phải của cột đầu tiên trong bảng dò, tương tự với HLOOKUP, ta cũng chỉ có thể trả về kết quả là 1 hàng nào đó nằm dưới hàng đầu tiên trong bảng dò. Mặc dù cũng có một số cách để xử lý được việc này VD như kết hợp thêm hàm CHOOSE, tuy nhiên nó khá phức tạp và sẽ không tối ưu trong nhiều trường hợp.
Hàm XLOOKUP có thể tìm kiếm từ cuối lên đầu (Search last to first)
Tiếp theo, vói hàm XLOOKUP ta có thể linh động trong tìm kiếm dữ liệu, đơn cử là ta có thể tìm kiếm từ dưới lên. Điêu này cũng khắc phục được những hạn chế của hàm VLOOKUP hay HLOOKUP.Với đối số thứ 5 của hàm là -1 (Search last-to-first).
Với cùng hàng hoá 1, công thức ở ô E3 có tham số thứ 5 là 1 (Search first-to-last) thì sẽ tìm từ đầu đến cuối trong lookup_array (tương tự hàm VLOOKUP).
Còn công thức ở ô E5 có tham số thứ 5 là -1 (Search last-to-first) thì hàm sẽ thực hiện bắt đầu tìm kiếm từ cuối lên đầu của lookup_array (Điều này hàm VLOOKUP không thể làm theo một cách thông thường).
Hàm XLOOKUP trả về 1 mảng giá trị
Hàm XLOOKUP cũng có thể trả về một mảng giá trị, cụ thể như sau:
Công thức ở ô I4 như sau:
=XLOOKUP(H2,A4:A7,A4:F7)
Hàm sẽ thực hiện tìm kiếm giá trị trong ô H2 trong vùng A4:A7 và vùng trả về kết quả ở đây là vùng từ A4:F7. Tương tự với các công thức ở ô I6 và I8.
Nối các giá trị tìm kiếm trong hàm XLOOKUP
Tiếp nữa, hàm XLOOKUP sẽ cho phép bạn nối chuỗi trong giá trị tìm kiếm, vùng tìm kiếm cũng như vùng kết quả. Khi bạn muốn tìm kiếm với nhiều điều kiện khác nhau bạn có thể nối các giá trị đó lại để tìm kiếm. Có thể thấy với hàm XLOOKUP bạn hoàn toàn linh hoạt trong việc tìm kiếm giá trị để có được kết quả mong muốn.
Nếu như bạn chưa kịp cập nhật để có hàm này thì Học Excel Online gợi ý cho bạn để cũng có thể ra kết quả với việc tìm kiếm khi nối các giá trị như trên bằng hàm INDEX + MATCH:
Tìm kiếm một mảng giá trị với hàm XLOOKUP
Vừa rồi ta vừa thực hiện tìm kiếm với nhiều điều kiện khác nhau bằng cách nối các giá trị đó lại để tìm kiếm. Không những thế hàm XLOOKUP còn có thể tìm kiếm cả mảng giá trị. Bạn tham khảo ví dụ sau đây để hiểu được rõ hơn.
Mình có sự dụng hàm UNIQUE – một hàm để loại bỏ giá trị trùng lặp và trả về danh sách duy nhất.
Một lưu ý khi tìm kiếm theo cả một mảng giá trị là: Kết quả trả về là 1 mảng giá trị theo chiều dọc (tức gồm nhiều hàng nhưng chỉ có 1 cột).
Từ đây bạn có thể rút ra là:
- Hàm XLOOKUP có thể về 1 mảng giá trị theo chiều ngang (tức theo hàng trong Excel, gồm 1 hàng và có nhiều cột).
- Hàm cũng có thể trả về 1 mảng giá trị theo chiều dọc (tức nhiều hàng và 1 cột).
Bạn có thể hiểu mảng trả về này là mảng 1 chiều, hoặc 2 chiều nhưng trong 2 chiều này phải có 1 chiều với kích thước bằng 1. Hàm XLOOKUP sẽ không trả về được 1 mảng giá trị gồm nhiều hàng và nhiều cột (>=2 hàng và >=2 cột), bạn có thể thấy điều đó qua ví dụ trên (ô I12).
Sử dụng các hàm khác kết hợp với XLOOKUP
Excel cho phép bạn kết hợp các hàm lồng vào nhau một cách rất linh hoạt, bạn hoàn toàn có thể tuỳ biến công thức theo ý của mình để có được kết quả mong muốn. Hàm XLOOKUP cũng không ngoại lệ.
Vận dụng việc hàm XLOOKUP có thể trả về cả mảng giá trị trên, ta có thể tính tổng số lượng của những hàng hoá phát sinh vào lần cuối cùng.
Nối chuỗi linh hoạt trong kết quả trả về của hàm XLOOKUP
Ở phần này, Học Excel Online sẽ giới thiệu tới các bạn sự linh hoạt của hàm XLOOKUP này ở kết quả trả về của nó. Ví dụ sau sẽ làm điều đó
Với hàm trên bạn có thể thấy kết quả trả về không chỉ là những con số khô khan nữa. Vận dụng việc hàm trả về cả mảng giá trị ta kết hợp thêm các chuỗi TEXT lồng vào trong kết quả trả về để xem số lượng và thành tiền phát sinh lần đầu tiên cũng như cuối cùng của các hàng hoá có phát sinh.
Một số điểm thú vị khác
Sử dụng XLOOKUP lồng XLOOKUP
Ví dụ sau sẽ thực hiện tính tổng số lượng của hàng hoá tính từ lúc hàng hoá C có phát sinh đầu tiên tới lúc hàng hoá C có phát sinh lần cuối cùng.
Khắc phục được lỗi chèn dòng, cột trong hàm VLOOKUP và HLOOKUP
Một điểm hạn chế nữa của hàm VLOOKUP cũng như HLOOKUP là khi chúng ta thực hiện chèn thêm 1 cột (với hàm VLOOKUP) hay chèn thêm 1 dòng (với hàm HLOOKUP) thì lúc này kết quả trả về của hàm sẽ bị sai. Hàm XLOOKUP đã khắc phục được điều đó.Trong ví dụ dưới, khi mình thực hiện chèn thêm cột vào giữa cột A và B thì kết quả ở ô D3 sử dụng hàm XLOOKUP không bị thay đổi còn với ô D2 sử dụng hàm VLOOKUP thì kết quả đã bị thay đổi (dẫn tới sai). Tương tự với hàm HLOOKUP, khi mình chèn thêm 1 dòng vào giữa dòng 7 và 8 thì kết quả ở ô H6 sử dụng hàm XLOOKUP không bị thay đổi còn ô H5 sử dụng hàm HLOOKUP thì kết quả đã bị thay đổi (dẫn tới sai).
Ta cũng khắc phục được điều đó với hàm INDEX + MATCH, VLOOKUP + MATCH, HLOOKUP + MATCH,….
Kết luận
Như vậy qua bài viết này của Học Excel Online bạn đã có thể nắm được cách vận dụng hàm XLOOKUP – một hàm mới được Microsoft cập nhật cho Office 365 bản Insider. Về cơ bản có hàm sẽ có cách thức hoạt động tương đối giống với các hàm dò tìm mà ta đã biết như VLOOKUP, HLOOKUP,…Tuy nhiên cũng có một số điểm khác biệt, một số điểm mới, thú vị đặc biệt là linh hoạt hơn trong giá trị tìm kiếm cũng như là giá trị trả về. Điều này đã khắc phục được những hạn chế của những hàm dò tìm khác.Với phần Comnent bên dưới, chúng tôi hy vọng sẽ nhận được thêm những góp ý cho bài viết cũng như nếu như bạn phát hiện ra các điều thú vị khác nữa về hàm này 😊
Cái này có vẻ tiện hơn Xlookup