Công thức của hàm XLOOKUP

 

Hàm XLOOKUP được ra đời với mục đích thay thế cho hàm VLOOKUP mà chúng ta vẫn hay sử dụng. Khắc phục được một vài hạn chế của hàm VLOOKUP và mang đến một số công dụng hiệu quả hơn. Tuy nhiên, vì đây là một hàm mới nên nhiều người vẫn chưa hiểu rõ về nó cũng cách sử dụng cơ bản. Đừng lo lắng bài viết dưới đây sẽ cung cấp đến bạn một vài kiến thức hữu ích về hàm XLOOKUP.

Hàm XLOOKUP trong Excel là gì?

Vậy hàm XLOOKUP trong Excel là gì? Vì sao hàm XLOOKUP lại được xuất hiện và nhận được sự đánh giá cao như vậy? Đây là một hàm mới được Microsoft cho ra mắt để thực hiện những tính năng giống như VLOOKUP. Nó sẽ khắc phục được những lỗi mà VLOOKUP không thể thực hiện được. Đồng thời hàm này còn mang đến cho người dùng nhiều tính năng tiện ích hơn.

Thêm vào đó, XLOOKUP còn có thể được thay thế cho hàm HLOOKUP. Ví dụ hàm XLOOKUP mới này có thể nhìn sang trái, cho phép chỉ định phạm vi ô. Bên cạnh đó còn có thể tự dò tìm kết quả một cách chính xác nhất. Những điều này hầu như VLOOKUP hay HLOOKUP không thể thực hiện được tốt như vậy.

Cách dùng hàm XLOOKUP hiệu quả, dễ dàng

Chỉ những ai dùng chương trình Insiders thì mới truy cập được vào hàm XLOOKUP. Chương trình Insiders cho phép người dùng sử dụng tính năng mới nhất của Excel và Google Sheet . Người dùng Office 365 đều sẽ được Microsoft cho quyền sử dụng XLOOKUP. Mặc khác đối với những ai dùng phiên bản Office 2010, 2013, 2016 và 2019 thì hàm XLOOKUP sẽ không khả dụng.

Vì thế nếu bạn muốn sử dụng được XLOOKUP thì thiết bị phải được nâng cấp lên Microsoft 365. Đối với ai đang sử dụng Microsoft 365 thì bạn sẽ dễ dàng truy cập được vào hàm XLOOKUP. Tùy chọn hàm này sẽ được bật lên trong Excel, với Office 365 Online thì XLOOKUP cũng có sẵn.

Cú pháp của hàm XLOOKUP

Dưới đây là cú pháp cụ thể của hàm XLOOKUP trong Excel mà bạn nên biết:

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

Các giá trị trong hàm XLOOKUP sẽ thể hiện những ý nghĩa như sau đây.

lookup_value (bắt buộc): Giá trị mà bạn đang muốn tìm.

lookup_array (bắt buộc): Mảng/phạm vi nào đó mà bạn muốn tìm kiếm giá trị.

return_array (bắt buộc): Mảng/dải ô mà bạn muốn lấy giá trị.

[if_not_found] (không bắt buộc): Giá trị trả về khi không thể tìm thấy kết quả phù hợp.

[match_mode] (không bắt buộc): Bạn được phép chỉ định loại đối sánh mà bạn đang tìm:

• 0: Tìm kết quả chính xác, đồng thời giá trị cũng phải khớp chính xác với lookup_array (mặc định).
• -1: Tìm kết quả phù hợp và chính xác. Nếu không tìm được kết quả chính xác, trở về giá trị nhỏ hơn.
• 1: Tìm kiếm kết quả chính xác. Nếu không tìm được giá trị chính xác phù hợp thì trả về giá trị lớn hơn.
• 2: Đối sánh từng phần với nhau, dùng các ký tự đại diện đặc biệt như *, ? và ~.

[search_mode] (không bắt buộc): Dùng để chỉ định thứ tự tìm kiếm:

• 1: Tìm kiếm từ mục đầu tiên đến mục cuối cùng (mặc định).
• -1: Tìm kiếm giá trị từ mục cuối cùng đến mục đầu tiên.
• 2: Tìm kiếm nhị phân của lookup_array, sắp xếp theo các giá trị tăng dần. Trường hợp sắp xếp không đúng thì sẽ không hợp lệ.
• -2: Tìm kiếm nhị phân của lookup_array, sắp xếp các giá trị giảm dần. Nếu không sắp xếp đúng sẽ dẫn đến kết quả bị sai.

Những ưu điểm và nhược điểm của hàm XLOOKUP

Để biết chi tiết hơn về đặc điểm của hàm XLOOKUP, bạn hãy xem qua các thông tin dưới đây. Sẽ giúp bạn có được cái nhìn cụ thể hơn về ưu, nhược điểm trước khi sử dụng hàm này.

Ưu điểm của hàm XLOOKUP Excel

• Hàm XLOOKUP có thể hoạt động, sử dụng được ở cả chiều dọc và chiều ngang.
• Nếu hàm VLOOKUP và INDEX MATCH cần đến 4 đối số thì hàm XLOOKUP chỉ cần có 3 đối số.
• Bạn có thể dễ dàng sử dụng các ký tự đại diện để kiểm tra từng phần.
• Thực hiện được thuận tiện việc tra cứu theo thứ tự giảm dần.
• Với hàm INDEX và hàm MATCH bạn sẽ phải sử dụng hai hàm nhưng với XLOOKUP thì bạn chỉ cần sử dụng một hàm.

Nhược điểm của hàm XLOOKUP Excel

• Khi mới bắt đầu việc sử dụng XLOOKUP thì các đối số có thể khá phức tạp.
• Sẽ gây mất nhiều thời gian thực hiện hơn, vì có nhiều ô hiển thị trong bảng tính.
• Người dùng cần phải nhớ kỹ phạm vi tra cứu cũng như phạm vi trả về.

Cách dùng hàm XLOOKUP trong Excel

Để biết được cách sử dụng hàm XLOOKUP chi tiết nhất, bạn hãy xem qua ví dụ cụ thể ngay dưới đây. Ví dụ yêu cầu trả lại các bộ phận/giá trị từ cột F cho mỗi ID có tại cột A.

Đây là một ví dụ về tra cứu đối sánh thường gặp ở hàm XLOOKUP và hàm chỉ yêu cầu 3 loại thông tin. Như hình bên dưới thì ta thấy XLOOKUP có 6 đối số. Tuy nhiên, chỉ dùng đến 3 đối số đầu tiên để thực hiện, do đó bạn hãy quan tâm chủ yếu đến chúng.

• Lookup_value: Chính là những giá trị mà bạn đang tìm kiếm.
• Lookup_array: Vị trí tìm kiếm là ở đâu.
• Return_array: Phạm vi, khu vực sẽ chứa giá trị được trả về.

Đối với ví dụ này thì công thức chính xác sẽ được áp dụng như sau: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Sau đây là những cách sử dụng và lợi thế của hàm XLOOKUP mà bạn nên biết.

Không có thêm số chỉ mục cột

Hàm XLOOKUP sẽ cho phép người sử dụng chọn được phạm vi giá trị được trả về. Còn đối với VLOOKUP thì bạn sẽ không thực hiện được việc này. Thêm vào đó, đối với những ô đã chọn thì XLOOKUP cho phép bạn xem được dữ liệu. Đồng thời còn giúp hạn chế được các lỗi về công thức khi thực hiện thêm cột mới. Do đó, bạn có thể thấy XLOOKUP sẽ hàm sử dụng thuận tiện và hiệu quả cho công việc.

Khớp tuyệt đối theo mặc định

Khi người dùng sử dụng XLOOKUP sẽ được mặc định là khớp tuyệt đối. Việc này có tác dụng giúp giảm đối số thứ bốn và hỗ trợ người dùng mới ít bị mắc lỗi. Nhờ vậy mà ta sẽ thấy được rằng XLOOKUP yêu cầu ít câu hỏi hơn so với VLOOKUP. Đồng thời XLOOKUP cũng mang đến cho người dùng sự thân thiện và tiện lợi.

XLOOKUP có thể tìm đến bên trái

Đối với XLOOKUP thì ta có thể tìm đến bên trái một cách dễ dàng. Vì XLOOKUP có thể chọn phạm vi tra cứu linh hoạt hơn VLOOKUP. Đồng thời thứ tự của những cột trong bảng lúc này cũng không cần thiết. Ở ví dụ ngay bên dưới đây bạn sẽ phải tìm ID (cột E) rồi trả về chỗ tên người (cột D). Công thức bạn nên sử dụng như sau:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Tra cứu phạm vi

Hãy lấy một ví dụ cụ thể như: Người dùng đang muốn trả lại chiết khấu dựa vào số tiền đã được chi tiêu. Với trường hợp này chúng ta sẽ không tìm một giá trị nhất định. Mà thay vào đó là biết được giá trị của cột B nằm trong mức nào của cột E. Nhằm để xác định được mức chiết khấu chính xác nhất.

So với VLOOKUP thì XLOOKUP có thể tìm ra được kết quả phù hợp và gần đúng hơn. Ở hàm này bạn sẽ có thể tùy chọn tìm được kết quả gần nhất nhỏ hơn (-1). Hoặc là tìm được kết quả gần nhất và lớn hơn (1). Bên cạnh đó bạn còn có thể lựa chọn để dùng những ký tự đại diện (2) đặc biệt.

Công thức sẽ cho ra giá trị gần nhất nhỏ hơn giá trị bạn đang tìm nếu không tìm ra được kết quả khớp nhất. Công thức cụ thể như sau: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Trong hình có một lỗi ở ô C7 nên sẽ trả về lỗi #N/A, nếu đúng thì chiết khấu là 0%. Tuy nhiên do 64 quá nhỏ nên không đạt được mức nhận chiết khấu.

Để lỗi ở ô C7 được khắc phục bạn hãy nhập một hàng mới ngay tại cuối bảng. Bạn tạo công thức và mở rộng phạm vi đã dùng bằng việc click vào và kéo thả các góc.

Như vậy, công thức đã được sửa lỗi và giá trị chiết khấu đã được hoàn thiện chính xác nhất.

XLOOKUP thay thế được hàm HLOOKUP

Với nhiều công dụng vô cùng hữu ích nên hàm XLOOKUP có thể thay thế được hàm HLOOKUP. Như vậy, ta có thể thấy XLOOKUP có thể thay thế được hai hàm. Điều này góp phần giúp mang đến cho người dùng sự thuận tiện và làm việc hiệu quả hơn. Về hàm HLOOKUP, đây là hàm thực hiện tra cứu theo hàng ngang. Vì thế, hàm này được sử dụng chủ yếu trong việc tìm kiếm theo hàng. Tuy hàm này không được phổ biến và thông dùng bằng hàm VLOOKUP. Nhưng sẽ hữu dụng khi xuất hiện trường hợp tiêu đề ở cột A và dữ liệu ở hàng 4,5.

Còn khi sử dụng hàm XLOOKUP thì bạn sẽ thực hiện được việc tra cứu ở hai chiều. Bạn sẽ sử dụng tra cứu được ngay ở cột hoặc theo hàng đều được. Lấy ví dụ này bạn có thể thấy công thức dùng cho việc trả về giá trị liên quan đến tên ở cột A2. Bắt đầu nhìn vào hàng 4 tìm tên rồi trả giá trị từ hàng 5.

Công thức: =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP có thể nhìn từ dưới lên

Bình thường để có thể thấy được sự hiện diện đầu tiên của giá trị thì bạn sẽ cần nhìn từ trên xuống dưới. Với XLOOKUP thì sẽ có một đối số thứ 5 chính là search_mode. Sử dụng đối số này thì bạn sẽ có thể thực hiện việc tìm kiếm giá trị từ dưới lên trên. Từ đó bạn sẽ thấy được sự xuất hiện cuối cùng của một giá trị nào đó.

Ở ví dụ ngay dưới đây, bạn cần phải tìm giá trị tồn kho của sản phẩm nằm ở cột A. Đồng thời bảng này sẽ được sắp xếp theo mục ngày tháng. Bạn muốn trả về giá trị tồn kho ở lần cuối cùng được kiểm tra.

Ở đối số thứ 5 của trong hàm XLOOKUP sẽ xuất hiện bốn lựa chọn như trong hình. Bạn sẽ thực hiện lựa chọn Search last-to-first. Công thức cho ví dụ này cụ thể như sau:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

Bạn hãy lưu ý trong công thức này thì đối số thứ bốn sẽ được bỏ qua. Vì đây chính là lựa chọn mà bạn muốn nó mặc định là khớp tuyệt đối.

Qua bài viết ở trên, mình vừa gửi đến bạn những thông tin rất hữu ích về hàm XLOOKUP. Góp phần giúp bạn có được những kiến thức cơ bản về cách sử dụng hàm XLOOKUP trong công việc. Hy vọng bạn sẽ có thể sử dụng hàm này một cách hiệu quả và tối ưu nhất. Nếu còn thắc mắc nào cần được giải đáp về hàm XLOOKUP, bạn hãy để lại bình luận bên dưới nhé.

Công thức của hàm SEQUENCE

 

Hàm SEQUENCE là gì? 

Ngoài những hàm cơ bản trong Excel mà chúng ta vẫn thường hay sử dụng, phiên bản Microshop 365, Excel 2021 và Google Sheet bổ sung thêm hàm SEQUENCE, cho phép tạo lập và sắp xếp các dãy số có sẵn hoặc các mảng dãy số theo thông số được chỉ định từ người dùng, theo một trật tự nhất định.

Lưu ý: đối với các bản Excel 2016, 2019, tính năng của hàm này chưa được cập nhật.

Công thức của hàm SEQUENCE

Công thức

Để sử dụng được hàm Sequence, bạn cần ghi nhớ công thức cơ bản sau:

=Sequence (rows, [columns], [start], [step])

Giải thích thông số

• Sequence: Tên gọi của hàm.
• Rows: Số hàng nhất định cho dãy số. Thông số này bắt buộc nhập.
• Columns: Số cột của dãy số. Nếu không cho giá trị, theo công thức sẽ tự mặc định là 1.
• Start: Giá trị bắt đầu của dãy số. Nếu không cho giá trị, theo công thức sẽ tự mặc định là 1.
• Step: Khoảng cách giữa các giá trị. Nếu không cho giá trị, theo công thức sẽ tự mặc định là 1.

Hướng dẫn sử dụng hàm Sequence trong Google Sheet

Đánh số thứ tự siêu nhanh với hàm Sequence

Theo hàng dọc

Trường hợp bạn muốn đánh số thứ tự cho danh sách có nội dung khá dài, đôi khi lên đến 1000 hay 2000 số. Việc thao tác kéo thủ công trong Google Sheet vừa mất thời gian đôi khi có thể xảy ra nhầm lẫn sai sót. Khi đó, bạn có thể dùng hàm Sequence để đánh số thứ tự nhanh chóng.

Cách sử dụng hàm Sequence trong trường hợp này khá đơn giản, bạn chỉ cần thay số thứ tự cần đánh vào thông số Rows đầu tiên trong hàm tương ứng, các thông số còn lại không nhập, hàm tự mặc định là 1.

Ví dụ: Số thứ tự cần đánh cho một danh sách là 1000.

Công thức =SEQUENCE(1000)

Theo hàng ngang

Tương tự như vậy, hàm Sequence còn được ứng dụng tạo lập các số theo hàng ngang, phụ thuộc vào việc thay đổi Columns.

Ví dụ: Cần lập bảng 15 cột và đánh số tăng dần tương ứng theo hàng ngang.

Công thức =SEQUENCE(1;15)

Trong đó:

• Thông số 1 là giá trị tương ứng số hàng (Rows)
• Thông số 15 là giá trị cho số cột (Columns)
• Các thông số còn lại mặc định là 1, có nghĩa là bắt đầu đếm từ số 1 và tăng dần 1 đơn vị.

Dùng hàm Sequence tạo dãy số liên tiếp tăng dần hoặc giảm dần

Lập bảng tăng dần chẵn hoặc lẻ

Thay đổi thông số Start và Step trong công thức để có được các dãy số liên tiếp tăng dần theo chẵn hoặc lẻ.

Ví dụ: Dùng hàm SEQUENCE Tạo một chuỗi các số chẵn tăng dần theo hàng dọc, với công thức =SEQUENCE(10;1;2;2)

Trong công thức hiển thị trên màn hình, các thông số Start và Step đã được biến đổi phù hợp để ra được dữ liệu số chẵn, khi bắt đầu bằng số 2 và tăng 2 đơn vị. Tương tự như vậy bạn cũng sẽ tạo ra được bảng có các chữ số bằng số lẻ khi bắt đầu bằng 1 và tăng 2 đơn vị.

Đánh số thứ tự giảm dần trong Google Sheet

Giống như các bước ở trên, FPT Shop hướng dẫn cách dùng hàm Sequence tạo chuỗi số giảm dần bằng cách thay đổi Step thành -1.

Ví dụ: Tạo số thứ tự giảm dần trong Google Sheet bằng hàm Sequence theo công thức =SEQUENCE(10;1;10;-1)

Dịch chuyển số Steps về -1, ta sẽ được 1 dãy các đơn vị số đi lui, bắt đầu từ 10 và kết thúc ở 1. Bạn có thể ứng dụng công thức này cho các bảng đánh số xếp hạng phân loại từ thấp tới cao.

Hàm Sequence kết hợp với một số hàm thông dụng khác

1.SEQUENCE kết hợp hàm ROMAN tạo bảng chữ số la mã

Đổi mới trong việc lấy số tự nhiên làm số thứ tự, bạn có thể kết hợp thêm hàm ROMAN vào bảng tính để tạo ra chuỗi các số đếm La Mã.

Ví dụ: Đánh số La Mã từ 1 đến 10 trong Google Sheet.

Ta có công thức như sau: =ARRAYFORMULA(ROMAN(SEQUENCE(10;1;1;1);0))

Giải thích công thức

• ARRAYFORMULA: thường được sử dụng trong Google Sheet, cho phép áp dụng một công thức hàng cho hàng loạt dữ liệu được chọn trên trang tính.
• Hàm ROMAN: cho phép chuyển đổi giá trị số tự nhiên sang số La Mã theo dạng =ROMAN(number, [form]).
• Hàm SEQUENCE: tạo ra 1 dãy giá trị các số bao gồm 10 hàng và bắt đầu từ số 1.

2.Sử dụng thêm hàm COUNTA đánh số thứ tự tự động theo nội dung

Tình huống bạn nhập nội dung vào cột bên cạnh và muốn số thứ tự hiển thị kèm theo nội dung, bạn có thể sử dụng hàm COUNTA với SEQUENCE.

Ví dụ: Nhập lệnh =SEQUENCE(COUNTA(C3:C12)) để vừa nhập văn bản vừa hiện số thứ tự.

3.Dùng hàm TRANSPOSE kết hợp SEQUENCE

Trường hợp bạn nhập công thức nhầm cột hoặc nhầm hàng, bạn có thể thay đổi thông số cột và hàng hoặc chuyển đổi chúng bằng hàm TRANSPOSE. Khi đó số hàng và số cột trong công thức SEQUENCE sẽ được chuyển đổi cho nhau.

Ví dụ: Chuyển đổi số Rows và Columns với SEQUENCE và TRANSPOSE.

Công thức =TRANSPOSE(SEQUENCE(3;6;1;1))

Khi đó, giá trị 6 đại diện cho Columns sẽ thay thế cho giá trị 3 đại diện cho Rows, như hình ảnh minh hoạ kèm theo.

4.Tạo lập ngày tháng với hàm DATE

Bạn muốn tạo một danh sách các ngày trong tháng để lên kế hoạch làm việc cá nhân hoặc thời gian biểu cho công ty. Bạn có thể áp dụng hàm SEQUENCE cùng với hàm DATE cho ra kết quả ngày, tháng, năm, nhanh chóng và chính xác nhất.

Mẫu công thức áp dụng tạo hàm SEQUENCE và hàm DATE trên Google Sheet như sau: =SEQUENCE(365,1,(DATE(2023,01,01))

Trong đó:

• Thông số 365 sẽ đại diện cho số hàng và tương ứng với 365 ngày trong năm.
• Hàm DATE(ngày;tháng;năm) sẽ hiển thị tương ứng với số bắt đầu từ ngày 1/1/2023.

5.Kết hợp SEQUENCE với hàm TIME xác định chuỗi thời gian

Nếu như bạn có kế hoạch cần mô tả thời gian, thì hàm SEQUENCE kết hợp với TIME sẽ là sự trợ giúp lớn giúp bạn sắp xếp thời gian trên Google Sheet thuận tiện hơn.

Ví dụ: Bạn có kế hoạch đi 10 địa điểm mỗi nơi dao động tầm 1 giờ đồng hồ.

Công thức sẽ là =ARRAYFORMULA(TIME(SEQUENCE(10);0;0))

Hỏi đáp và những điểm cần lưu ý dùng hàm Sequence

Có dùng hàm Sequence cho số thập phân được không và cách làm?

Trả lời: Một số trường hợp, bạn cần đánh số thập phân, từ 0.1 đến 1 chẳng hạn.

Bạn có thể áp dụng công thức sau: =ARRAYFORMULA(SEQUENCE(10)/10). Việc chia cho 10 sẽ đảm bảo kết quả hiển thị trên Google Sheet sẽ là số thập phân.

Tạo lập bảng cửu chương với hàm SEQUENCE như thế nào?

Trả lời: Đầu tiên bạn tạo dãy số từ 1 đến 10 bằng công thức =SEQUENCE(1;10), sau đó cố định hàng và cột từ phía đầu về cuối bằng cách nhập lệnh =SEQUENCE($K$1,$B$1,B$1,B$1)

Một số lưu ý cần thiết khi sử dụng hàm Sequence

Lưu ý 1: Cần xác định trước số lượng phần tử muốn tạo lập, gồm bao nhiêu hàng, bao nhiêu cột để đánh số thứ tự hợp lý.

Lưu ý 2: Khi nhập công thức cần kiểm tra dấu bằng, dấu ngoặc, tên hàm và các dấu phẩy đã đúng định dạng hay chưa, như vậy giúp giảm khả năng biểu thức xuất hiện lỗi báo #ERROR.

Lưu ý 3: Nếu muốn định dạng thứ tự theo số thập phân, cần bổ sung thêm chia cho thập phân phía sau công thức SEQUENCE.

Lưu ý 4: Hiện nay các phiên bản Excel cũ chưa cập nhật tính năng này, nếu muốn sử dụng bạn cần tải bộ phần mềm Microshop 365 hoặc cập nhật bản Excel 2021, tiện dụng hơn là thao tác hàm Sequence trên Google Sheet.

Tạm kết

Như vậy, mình đã giới thiệu đến bạn đọc khái niệm về hàm SEQUENCE và các cách sử dụng hàm SEQUENCE trong Google Sheet hiệu quả. Hy vọng bài viết đã giải đáp được phần nào thắc mắc của các bạn về hàm SEQUENCE.