Hàm XLOOKUP: Có thể bạn chưa biết hết tính năng và ứng dụng

[ptm0412]

Hàm XLOOKUP: Có thể bạn chưa biết hết tính năng​

Hàm XLOOKUP xuất hiện đã lâu, các bạn đã dùng nhiều, có khi dùng nhiều hơn tôi (vì tôi đã nghỉ việc, mà bản chất công việc của tôi lại không phải là dùng Excel). Nội trong cú pháp hàm:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Không hẳn bạn nào cũng dùng hết các tham số, chưa nói đến dùng hết các giá trị có sẵn của tham số.
Bạn nói Xlookup hay hơn Vlookup vì có thể tìm từ phải qua trái, và ứng dụng thay cho vlookup, nhưng bạn có biết là Xlookup cũng có thể ứng dụng thay cho HLookup nữa không?
Tiếp theo, nếu bạn biết Xlookup vừa có thể tìm theo cột như Vlookup, vừa tìn theo dòng như HLookup, vậy bạn đã thử tìm trong bảng tra 2 chiều, vừa tra dòng vừa tra cột, lấy kết quả như hàm Index chưa?
Còn nữa, nếu bạn biết Xlookup có thể tìm xuôi lấy giá trị đầu, tìm ngược để lấy giá trị cuối, vậy bạn có biết cách lấy nguyên khối kết quả tìm được không?
Hãy đọc 3 mục A, B, C dưới đây xem bạn thuộc nhóm nào nhé.

A. Các tính năng nhiều người đã biết:
1. Hàm có thể dò tìm từ trái qua phải, từ phải qua trái, chèn cột vào giữa mà không bị lỗi công thức như VLOOKUP thông thường.
Thí dụ tìm từ phải qua trái:
=XLOOKUP(E2,C2:C13,B2:B13)



2. Tìm từ dưới lên trên:
Sử dụng tham số Search mode = -1
=XLOOKUP(E3,C2:C13,B2:B13,,,-1)



3. Giá trị nếu tìm không thấy
Sử dụng tham số If not Found
=XLOOKUP(E4,C2:C13,B2:B13,"None",,-1)





B. Tính năng không phải ai cũng biết

1. Tìm theo ký tự đại diện
Sử dụng ký tự *, ?, ~ và tham số Match mode = 2

=XLOOKUP(E5,$C$2:$C$13,$B$2:$B$13,"None",2,-1)



2. Dò tìm theo dòng giống HLOOKUP

=XLOOKUP(B32,C17:N17,C23:N23)



3. Dò tìm ra nhiều kết quả

=XLOOKUP(G3,$B$2:$B$13,$A$2:$D$13)



C. Tính năng có thể rất ít người biết
1. Dò tìm như Index

=XLOOKUP(B32,C17:N17,XLOOKUP(A32,B18:B29,C18:N29))



2. Liệt kê hết kết quả tìm thấy
(Dữ liệu phải được sort theo Category, liệt kê hết các mặt hàng thuộc category Beverage, đơn vị tính và giá)

=XLOOKUP(H5,E2:E13,B2: D13):XLOOKUP(H5,E2:E13,B2: D13,,,-1)



3. Liệt kê kết quả dò tìm 2 chiều
(Dữ liệu phải được sort theo Category, liệt kê hết các giá trị tháng 7 của Beverage)

=XLOOKUP(F32,D17:O17,XLOOKUP(E32,C18:C29,D18:O29):XLOOKUP(E32,C18:C29,D18:O29,,,-1))



4. Tính tổng các kết quả tìm thấy

=SUM(XLOOKUP(G32,D17:O17,XLOOKUP(F32,C18:C29,D18:O29):XLOOKUP(F32,C18:C29,D18:O29,,,-1)))



5. Dò tìm ra kết quả 2 chiều

- Liệt kê mặt hàng theo category (như trên)

=XLOOKUP(J39,C40:C51,B40:B51):XLOOKUP(J39,C40:C51,B40:B51,,,-1)



- Tính doanh số các mặt hàng, sử dụng dấu #

=XLOOKUP($J40#,$B$40:$B$51,XLOOKUP(K39,$D$39:$H$39,$D$40:$H$51))


- Tính các cột khác

 

[ptm0412]

THÍ DỤ ỨNG DỤNG XLOOKUP
1. Tính tổng, tính doanh thu cao nhất và mặt hàng có DT cao nhất
Mặc dù có thể dùng Pivot table để tính tổng (sum), doanh thu cao nhất (Max) rất gọn gàng, nhanh chóng, nhưng pivot table không lấy ra được tên mặt hàng có doanh thu cao nhất.

Tính tổng: tương tự như bài trên cho Food, fill xuống cho các category khác
=SUM(XLOOKUP($J47,$C$40:$C$51,D$40: D$51):XLOOKUP($J47,$C$40:$C$51,D$40: D$51,,,-1))



Tính Doanh thu cao nhất (dùng Max thay vì Sum)
=MAX(XLOOKUP($J47,$C$40:$C$51,D$40: D$51):XLOOKUP($J47,$C$40:$C$51,D$40: D$51,,,-1))



Lấy tên mặt hàng có doanh thu cao nhất: Dùng XLookup dò tìm doanh thu cao nhất trong vùng doanh thu xác định bởi category, tra ra mặt hàng cũng trong vùng đã xác định

=XLOOKUP(L47,XLOOKUP(J47,$C$40:$C$51,$D$40:$D$51):XLOOKUP(J47,$C$40:$C$51,$D$40:$D$51,,,-1),XLOOKUP(J47,$C$40:$C$51,$B$40:$B$51):XLOOKUP(J47,$C$40:$C$51,$B$40:$B$51,,,-1))




2. Tra bảng giá nhiều tiêu chí
Giả sử có bảng giá áo theo loại, kích thước và màu sắc như sau:



Và có đơn hàng như sau, tìm giá để tính giá trị đơn hàng

=XLOOKUP(J58&K58&L58,$B$56:$B$100&$C$56:$C$100&$D$56:$D$100,$E$56:$E$100)



3. Thống kê theo tháng bất kỳ của các mặt hàng và tính tổng
Chọn tháng bắt đầu ở H31 và tháng cuối ở J31

I33 =XLOOKUP(G33,$B$18:$B$29,XLOOKUP($H$31,$D$17:$N$17,$D$18:$N$29):XLOOKUP($J$31,$D$17:$N$17,$D$18:$N$29))

Tính tổng = Sum I33

H33=SUM(XLOOKUP(G33,$B$18:$B$29,XLOOKUP($H$31,$D$17:$N$17,$D$18:$N$29):XLOOKUP($J$31,$D$17:$N$17,$D$18:$N$29)))

hoặc đơn giản là =SUM(I33#)



Nếu chọn lại từ tháng 7 đến tháng 12 (Jul to Dec)



Nói chung, ứng dụng của hàm XLookup là rất nhiều, chưa kể kết hợp với những hàm khác.

 

[dinhhuykbbl]

Cho mình hỏi, sau khi mình dùng hàm Xlookup thì excel tính toán và chạy rất chậm, cho hỏi cách xử lý như thế nào

 

[Longnt192]

Cho em hỏi là hàm Xlookup này có gì tối ưu hơn Index-match khi tìm và trả kết quả theo 1 điều kiện không ạ? Và nếu Tìm nhiều điều kiện trong tệp dữ liệu lớn (~300k dòng) theo 2 điều kiện trở lên, thì hàm nào sẽ tối ưu hơn ạ? Em cảm ơn.

 

[ptm0412]

Cho em hỏi là hàm Xlookup này có gì tối ưu hơn Index-match khi tìm và trả kết quả theo 1 điều kiện không ạ? Và nếu Tìm nhiều điều kiện trong tệp dữ liệu lớn (~300k dòng) theo 2 điều kiện trở lên, thì hàm nào sẽ tối ưu hơn ạ? Em cảm ơn.

- Tìm và trả kết quả (1 hay nhiều điều kiện), Index và Match có trả về nhiều hơn 1 kết quả không? Ngoài ra, tất cả các hàm sẽ được dùng chung (kết hợp) với các hàm khác cho ra kết quả cuối. Nếu kết quả dò tìm nhiều hơn 1 thì có thể liệt kê, tính tổng, ... và hơn nữa.
- Nếu bạn có dữ liệu 300 ngàn dòng thì bạn hãy tự trải nghiệm, tôi không có.

 

[thaihung85]

Dạ anh ơi em dùng Excel 2016, em cần lấy dữ liệu trong bảng tham chiếu như này thì có cách nào không ạ?
Em xin cảm ơn rất nhiều!

 

[ptm0412]

Dạ anh ơi em dùng Excel 2016, em cần lấy dữ liệu trong bảng tham chiếu như này thì có cách nào không ạ?

Index và 2 Match nhé.

 

[thaihung85]

Index và 2 Match nhé.

Dạ anh có thể hỗ trợ công thức giúp em được không ạ?
Em cần lấy giá cho CỘT H theo 3 tiêu chỉ của CỘT C, F, G từ bảng tham chiếu ở Sheet BIỂU GIÁ

Cảm ơn anh.

 

[fuuko]

Cho mình hỏi, sau khi mình dùng hàm Xlookup thì excel tính toán và chạy rất chậm, cho hỏi cách xử lý như thế nào

Mình cũng gặp trường hợp như này. Không biết các cao nhân đánh giá như nào về tốc độ xử lý hàm này ạ
Ví dụ mình chạy khoảng 3000 hàm xlookup trên 1 sheet là file đơ liền, k tính toán được.

 

[fuuko]

@zzzsxxx01 đúng là mình chưa biết hết tính năng và ứng dụng của hàm này nên mình mới có thắc mắc. Trên thực tế khi mình sử dụng bị tình trạng như vậy nên mình hỏi thêm kinh nghiệm của các b để nâng cấp thêm cách sử dụng của mình
Bạn có đóng góp gì cho câu hỏi của mình ngoài câu "Có thể bạn chưa biết hết tính năng và ứng dụng with Haha." k nhỉ? Nếu có mong nhận phản hồi chi tiết của b nhiều hơn.

 

[VetMini]

Rồi, lão ct bị khích tướng.
Lót dép ngồi xem. Lão ct phải làm cách nào vừa chứng minh hàm, vừa cho kẻ thách một bài học mới hay.

Ủa, mà giờ này thì té ra mình mới là kẻ thách đố.

 

[ptm0412]

Rồi, lão ct bị khích tướng.
...
Ủa, mà giờ này thì té ra mình mới là kẻ thách đố.

Tôi khó bị khích lắm. Vả chăng bài 10 (hình như là bài khích tướng), trả lời cho 1 anh ba giét ba ích chứ không phải tôi.
Anh thách thì tôi luôn luôn chịu thua.

 

[VetMini]

Tôi khó bị khích lắm. Vả chăng bài 10 (hình như là bài khích tướng), trả lời cho 1 anh ba giét ba ích chứ không phải tôi.
Anh thách thì tôi luôn luôn chịu thua.

Thôi lỡ lắm mồm thì tôi làm thân người bị khích vậy.

Mình cũng gặp trường hợp như này. Không biết các cao nhân đánh giá như nào về tốc độ xử lý hàm này ạ
Ví dụ mình chạy khoảng 3000 hàm xlookup trên 1 sheet là file đơ liền, k tính toán được.

Gặp người hay viết tắt thì hàm tôi cũng đơ. Phải đặt tô mì hoành thánh nóng hổi trước mắt chúng mới chịu hoạt động. Dân "sử tô" (*) mờ.
Cứ nói 3000 hàm xlookup thì ai biết nổi. Chúng ở đâu? Làm cách nào bạn gõ được 3000 hàm? Tôi rất siêng gõ nhưng cỡ con số này chì thua.

Gợi ý: lút lút cái gì đó to tổ bố thì dùng Data Model. Làm việc với dữ liệu hàng khủng, nếu không biết Data Model thì dụ sếp mua phần mềm khác đi, đừng dùng Excel càng lúc càng bí.

(*) nói lái.

 

[ptm0412]

Nếu chỉ dùng xlookup như là hàm vlookup tra ngược thì 3000 x cũng treo như 3000 v. Thế mạnh của những hàm mới của 365 là hàm cho kết quả mảng. Thậm chí 1 công thức ra cả 1 báo cáo chứ không phải 1 ô. Nghĩa là chỉ nên dùng khi hiểu rõ thế mạnh của nó và dùng cái sức mạnh của nó đúng chỗ, chứ không phải dùng đại đao mà chém gà. Cứ tưởng tượng nâng lên hạ xuống thanh đại đao 3 ngàn lần á. Dao nhỏ chém 3000 nhát cũng phải mỏi tay mà?
Dữ liệu nhiều thì phải dùng cách khác chứ công thức nào chịu nổi.

 

[fuuko]

Mình k có ý khích tướng gì ở đây, mình đang cầu thị để được học kiến thức mới. Nếu mình nói chưa rõ ràng hoặc cần cũng cấp thêm thông tin thì mong được đóng góp. Mọi người có đang bị nhạy cảm quá k?
File của mình chạy 3 cột dò tìm thông tin, mỗi cột 1000 dòng x 3 = 3000 dòng hàm xlookup hoạt động
-> Trước đó mình sử dụng hàm vlookup hoặc dàm index kết match thì k gặp tình trạng treo file.

Bài đã được tự động gộp: 9/10/24

Thay vì tranh luận, đả kích mọi người cho mình 1 cái link gợi mở cách sử dụng hàm. Hay 1 tips nhỏ nào đây trong lúc sử dụng mọi người phát hiện ra thì thật cảm ơn quá.
Vừa tiết kiệm thời gian cho mọi người, vừa cho mình thêm kiến thức

Bài đã được tự động gộp: 9/10/24

Thôi lỡ lắm mồm thì tôi làm thân người bị khích vậy.


Gặp người hay viết tắt thì hàm tôi cũng đơ. Phải đặt tô mì hoành thánh nóng hổi trước mắt chúng mới chịu hoạt động. Dân "sử tô" (*) mờ.
Cứ nói 3000 hàm xlookup thì ai biết nổi. Chúng ở đâu? Làm cách nào bạn gõ được 3000 hàm? Tôi rất siêng gõ nhưng cỡ con số này chì thua.

Gợi ý: lút lút cái gì đó to tổ bố thì dùng Data Model. Làm việc với dữ liệu hàng khủng, nếu không biết Data Model thì dụ sếp mua phần mềm khác đi, đừng dùng Excel càng lúc càng bí.

(*) nói lái.

dạ mình k gõ, gõ 1 dòng rồi kéo công thức xuống thôi mà @@

 

[ptm0412]

File của mình chạy 3 cột dò tìm thông tin, mỗi cột 1000 dòng x 3 = 3000 dòng hàm xlookup hoạt động ... gõ 1 dòng rồi kéo công thức xuống

Nếu gõ rồi kéo xuống 1000 dòng thì gõ 1 công thức ở ô đầu tiên thôi, tự nó giãn ra 1000 ô. Nghĩa là chỉ gõ 3 ô 3 công thức.

 

[VetMini]

...
Thay vì tranh luận, đả kích mọi người cho mình 1 cái link gợi mở cách sử dụng hàm. Hay 1 tips nhỏ nào đây trong lúc sử dụng mọi người phát hiện ra thì thật cảm ơn quá.
Vừa tiết kiệm thời gian cho mọi người, vừa cho mình thêm kiến thức

dạ mình k gõ, gõ 1 dòng rồi kéo công thức xuống thôi mà @@

Bạn hỏi hay chỉ dẫn cho tôi vậy? Mấy người như bạn cứ thấy khó nghe là lo to họng rồi. Có chịu đọc hết bàu đâu mà biết chuyện chủ dẫn. Cầu như vây thì lấy ở đâu. Chả nhẽ bắt người ta nấu cơm dâng tận họng mới thấy à?

Ở bài #13 tôi đã gợi ý là tìm hiểu về Data Model rồi. Hay là bạn cho rằng từ "gợi ý" nó quá xa vời với bạn so với "1 tips [sic]". Xin lỗi, tiếng Tây của tôi thì 1 là số ít. Tip là từ đếm được, nói vậy đủ hiểu chưa?

Nhắc lại: làm việc kiểu của bạn mà không biêt Data Model thì cả đời sẽ chỉ nhờ mãi.

 

[fuuko]

Nếu gõ rồi kéo xuống 1000 dòng thì gõ 1 công thức ở ô đầu tiên thôi, tự nó giãn ra 1000 ô. Nghĩa là chỉ gõ 3 ô 3 công thức.

View attachment 304588

ồ, vậy mình chỉ chạy 3 ô công thức (chạy copy xuống 1000 dòng) nó cũng bị treo. File mình có kết hợp dùng cả query để load dữ liệu và sử dụng sheet thường để chạy công thức
Do file nội bộ nên mình k share được, mà dựng 1 file khác làm ví dụ thì thấy k đúng dữ liệu có khi cũng k đơ (do mình đã thử chạy ở file đơn giản hơn thì k bị treo) -> vì lý do này nên chạy tới các diễn đàn để xem có ai bị tình trạng giống mình để xem ngoài việc dùng đúng công thức thì nó còn tips gì để tránh lỗi k ^^

Bài đã được tự động gộp: 9/10/24

Bạn hỏi hay chỉ dẫn cho tôi vậy? Mấy người như bạn cứ thấy khó nghe là lo to họng rồi. Có chịu đọc hết bàu đâu mà biết chuyện chủ dẫn. Cầu như vây thì lấy ở đâu. Chả nhẽ bắt người ta nấu cơm dâng tận họng mới thấy à?

Ở bài #13 tôi đã gợi ý là tìm hiểu về Data Model rồi. Hay là bạn cho rằng từ "gợi ý" nó quá xa vời với bạn so với "1 tips [sic]". Xin lỗi, tiếng Tây của tôi thì 1 là số ít. Tip là từ đếm được, nói vậy đủ hiểu chưa?

Nhắc lại: làm việc kiểu của bạn mà không biêt Data Model thì cả đời sẽ chỉ nhờ mãi.

ah vì chủ đề đang bàn luận là xlookup nên mình hỏi chi tiết vì xlookup thôi. k dám chỉ dẫn bất kì ai trong này vì biết năng lực có hạn
cảm ơn #13 của bạn, data model mình đang sử dụng rồi, nhưng có những ứng dụng sử dụng data model k cần thiết hoặc phù hợp nên mình k dùng.
Biển học là bao la, được nhờ được chỉ dẫn được học mình vẫn lăn xả để học thôi để nhờ thôi
Họng mình yếu nên chẳng to họng bao giờ, lại tốn tiền thuốc ý. Nên đoạn nào b đọc thấy mình to họng thì chắc hiểu nhầm chăng?

 

[ptm0412]

ồ, vậy mình chỉ chạy 3 ô công thức (chạy copy xuống 1000 dòng) nó cũng bị treo. File mình có kết hợp dùng cả query để load dữ liệu và sử dụng sheet thường để chạy công thức

Nếu đã dùng query thì phải biết dùng merge query để lấy thông tin. Lookup lấy 3 thông tin cho hàng ngàn dòng là xưa lắc rồi

 

[fuuko]

Nếu đã dùng query thì phải biết dùng merge query để lấy thông tin. Lookup lấy 3 thông tin cho hàng ngàn dòng là xưa lắc rồi

Cái đấy thì mình biết, chỉ là đang muốn học thêm hàm mới thôi. Còn 1 vấn đề đúng là có nhiều cách xử lý. Cách này k hợp vs mình thì mình sẽ tìm cách khác ^^