Vlookup Trong Excel Là Gì / TOP 3 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

Có nhiều người luôn thắc mắc về hàm Vlookup là gì? Nên các chuyên gia HP Connect sẽ giúp các bạn giải đáp vấn đề sau đây:

V chính là từ viết tắt trong tiếng Anh của Vertical (có nghĩa là theo chiều dọc). Còn Lookup có nghĩa là tìm kiếm. Do đó, Vlookup chính là một hàm cho phép người dùng thực hiện tìm kiếm về một giá trị nào đó thuộc trong một cột dữ liệu. Sau đó, kết quả được trả về là một giá trị phù hợp trong cột hay là giá trị từ cột nào đó với giá trị tương ứng.

Hàm Vlookup có điều kiện này đã được tích hợp trên Excel 2000, Excel 2007, Excel 2010 và Excel 2013. Nên tất cả người dùng đều có thể sử dụng hàm Vlookup có 2 điều kiện này một cách tiện lợi nhất.

Cú pháp của hàm Vlookup Excel là gì?

Hàm Vlookup Excel có cú pháp như sau:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]

Ở cú pháp của hàm Vlookup, các bạn sẽ điền vào tất cả là 4 giá trị. Bao gồm:

Lookup_value: Đây là giá trị bắt buộc

Lookup_value là một giá trị tương đương với một ô nhất định tại bảng Excel. Ví dụ như ô A1, D2, E5,… Là một giá trị ban đầu được sử dụng để tìm kiếm.

Table_array: Đây là giá trị bắt buộc

Cột đầu tiên thường sẽ là cột chứa giá trị tương tự như Lookup_value. Tại cột tiếp theo sẽ có số thứ tự là cột 2 và cứ lần lượt cho các cột sau là cột 3 và cột 4 nếu có.

Thông thường, bảng quy ước đó đã được cố định. Vì vậy, khi đã thực hiện quá trình kéo chọn bảng hoàn tất. Các bạn hãy nhấn tổ hợp phím Fn + F4. Như vậy, giá trị trong bảng sẽ không bị thay đổi.

Col_index_num: Đây là giá trị bắt buộc

Đối với trường hợp các bạn muốn kết quả của giá trị tìm kiếm tại cột 1 thuộc trong bảng Excel sẽ trả về giá trị tại cột 2. Vậy bạn gõ số 2 vào giá trị Col_index_num này. Còn đối với trường hợp bạn muốn được trả về giá trị tại cột 3 trong bảng, bạn chỉ cần nhập vào số 3. Với quy ước về số thứ tự của cột trong bảng đã được đề cập ở trên.

Range_lookup: Đây là giá trị tùy chọn

Range_lookup chính là một phương pháp dò tìm với quy ước như sau:

Quy ước 0: là phương pháp dò tìm tuyệt đối

Quy ước 1: là phương pháp dò tìm tương đối.

Thông thường, hầu hết người dùng sẽ chọn 0 để thực hiện phương pháp dò tìm tuyệt đối.

Hàm Vlookup được người dùng sử dụng trong trường hợp muốn dò tìm thông tin tại một trường dữ liệu bất kỳ. Hoặc là dò tìm danh sách thông qua các mã định danh đã có sẵn.

Ví dụ, các bạn thực hiện nhập hàm Vlookup cùng với mã sản phẩm sang bảng tính khác. Như vậy, thông tin sản phẩm tương ứng của mã sản phẩm đó sẽ được hiển thị. Thường thì các thông tin hiển thị đó sẽ là tên sản phẩm, giá sản phẩm, mô tả, số lượng hàng tồn kho,… Điều đó sẽ tùy thuộc vào nội dung của hàm mà các bạn đã biết.

Ví dụ về hàm Vlookup trong Excel

Excel hàm Vlookup với phương pháp dò tìm tương đối

Dựa vào bảng quy định về xếp loại theo số điểm đã được đưa ra cụ thể. Hãy thực hiện sắp xếp lại loại học lực cho tất cả những học sinh thuộc danh sách sau đây:

Các bạn áp dụng cú pháp hàm Vlookup Excel đối với cột D6 như sau:

=VLOOKUP(C6,$C$16:$D$20,2,1)

Như vậy, kết quả mà các bạn sẽ nhận được là:

Excel hàm Vlookup với phương pháp dò tìm tuyệt đối

Đối với phương pháp dò tìm tuyệt đối trong hàm Vlookup. Các bạn sẽ nhận được kết quả chi tiết hơn so với phương pháp dò tìm tương đối như ở trên.

Ví dụ: Hãy điền vào bảng sau đây các thông tin về Quê quán và Trình độ của tất cả các nhân viên trong bảng. Dựa vào mã nhân viên đã được quy định sẵn ở bảng dưới.

Để thực hiện điền vào thông tin về quê quán của tất cả nhân viên trong danh sách. Các bạn áp dụng cú pháp hàm Vlookup đối với ô E6 cụ thể như sau:

=VLOOKUP(A6,$D$12:$F$17,2,0)

Trong đó:

A6 chính là giá trị được sử dụng để tiến hành dò tìm

$D$12:$F$17 chính là bảng để dò tìm

2 là số thứ tự của cột thuộc trong bảng dữ liệu mà bạn đang dò tìm

0 chính là loại dò tìm chính xác nhất.

Như vậy, việc điền vào ô Trình độ của các nhân viên trong bảng cũng được thực hiện tương tự. Với cú pháp hàm Vlookup ngược cho ô F6 như sau:

Thông qua cách dò tìm với giá trị tuyệt đối, các bạn sẽ được phép tìm kiếm với kết quả hiển thị cụ thể nhất. Và tất nhiên là các giá trị tuyệt đối sẽ được giữ nguyên cho dù hiển thị kết quả tại những ô khác hay cột khác. Vì vậy, đối với những trường hợp cần dò tìm kết quả chính xác, các bạn hãy sử dụng cách dò tìm với giá trị tuyệt đối. Còn giá trị tương đối sẽ được sử dụng trong trường hợp bạn muốn dò tìm với kết quả hiển thị gần chính xác. Có lẽ ví dụ ở trên đã giúp các bạn hiểu hơn về vấn đề này.

Hàm Vlookup kết hợp với hàm Hlookup, Left, Right và Match

Với dữ liệu được cho là bảng bên dưới với quy ước cụ thể:

Bảng 1: Bảng thống kê về tiêu thụ gạo

Bảng 2: Bảng tra tên hàng, tên hãng sản xuất và đơn giá

Một số vấn đề cần được giải quyết như sau:

Vấn đề 1: Dựa vào 2 ký tự phía bên trái cùng với 2 ký tự phía bên phải thuộc trong cột Mã Sp tại bảng 1. Các bạn hãy dò tìm tại bảng 2 để có thể tìm ra giá trị của cột Tên Hàng – Tên hãng sản xuất của bảng 1. Có nghĩa là bạn cần tính dữ liệu chính xác cho cột C5:C10 trong bảng 1.

Ví dụ như Mã SP BH-TB có nghĩa là loại gạo Bắc Hương – Thái Bình

Vấn đề 2: Hãy tiến hành nhập vào đơn giá đối với các mặt hàng căn cứ vào từng Mã SP tại bảng 1 cùng với việc tra tại bảng 2. Có nghĩa là bạn cần tính dữ liệu chính xác cho cột D5:D10.

Vấn đề 3: Hãy tính thành tiền cho mỗi mã sản phẩm tại bảng 1?

Giải quyết từng vấn đề: Vấn đề 1:

Trước hết, các bạn cần phải tìm ra công thức để lấy được dữ liệu về Tên hành cùng với Tên tỉnh sản xuất. Từ đó, các bạn ghép 2 công thức đó lại với nhau để tìm ra được câu trả lời cho vấn đề 1.

+ Lấy Tên Hàng: các bạn sẽ lấy 2 ký tự phía bên trái của mã sản phẩm nằm ở bảng 1 (từ A5:A10). Được so với 2 ký tự thuộc cột mã hàng thuộc bảng thứ 2. Tuy nhiên, vùng dữ liệu mà các bạn lấy qua cột Tên hàng tại Bảng 2 là (A15:B20) hay là (A15:E20).

Các bạn áp dụng hàm Vlookup như sau: Tại một ô bất kỳ trong bảng chưa có dữ liệu, bạn tiến hành nhập vào công thức: =VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)

Sau khi bạn đã nhập công thức ở trên vào, bạn nhấn Enter để xem kết quả. Nếu như kết quả là “Khang Dân”. Có nghĩa là các bạn đã thực hiện được 40% cho vấn đề 1. Hãy tiếp tục bên dưới nhé!

+ Lấy Tên Tỉnh Sản xuất: Bạn tiến hành lấy 2 ký tự nằm phía bên phải thuộc cột mã SP trong bảng 1 (A5:A10) so với 2 ký tự thuộc trong cột Mã gạo – Tên tỉnh sản xuất tại bảng 2. Vùng dữ liệu mà các bạn sẽ lấy là (A16:E20).

Vì chúng ta sẽ lấy dữ liệu theo hàng nên sẽ sử dụng đến hàm Vlookup Hlookup. Các bạn cũng nhập vào 1 ô trống bất kỳ chưa chứa dữ liệu với công thức như sau:

= HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)

Sau khi bạn dã nhập xong công thức trên vào, bạn nhấn Enter để tiếp tục. Nếu kết quả hiển thị ra là “Nam Định”. Có nghĩa là bạn đã thực hiện thêm 40% câu trả lời cho vấn đề 1. Việc tiếp theo mà các bạn cần làm là hãy ghép 2 công thức của 2 hàm ở trên lại để tiến hành tính dữ liệu cho cột Tên Hàng – Tên Tỉnh Sản xuất.

Ghép 2 công thức: Có rất nhiều công thức để các bạn sử dụng khi bạn muốn ghép hay nối các công thức lại với nhau. Ở ví dụ này, các chuyên gia HP Connect sẽ chia sẻ đến các bạn thực hiện cách nối chuỗi để áp dụng hàm &. Ở vấn đề 1 này, chúng tôi sẽ lấy dấu gạch ngang kèm theo dấu cách ở giữa 2 công thức đã có kết quả ở phía trên. Có nghĩa là kết quả của Tên Hàng và Tên tỉnh sản xuất. Với hàm cụ thể sau đây:

Sau khi ghép 2 hàm lại các bạn sẽ có được hàm mới như sau:

=+vlookup&” – “&hlookup

Chú ý: Tại hàm Hlookup sẽ không có dấu = như ban đầu.

Tại C5, các bạn hãy nhập vào công thức như sau:

C5=+VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)&” – “&HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)

Khi bạn nhấn phím Enter, kết quả sẽ hiển thị ngay. Nếu như kết quả cuối cùng là “Hàng Dân – Nam Định”. Như vậy, các bạn đã nhận được kết quả chuẩn nhất.

Tiếp theo là tại ô C6:C10 trong bảng. Các bạn chỉ cần thực hiện thao tác đưa trỏ chuột vào bên cạnh công thức tại ô C5 rồi bạn kéo xuống đến ô C10. Cũng tương tự như trên, các bạn nhập vào ô C6 với công thức như sau:

C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&” – “&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE)

Như vậy là vấn đề 1 đã được bạn giải quyết hoàn thành. Bây giờ hãy tiếp tục đi đến vấn đề 2 nhé!

Vấn đề 2: Tính đơn giá

Các bạn sẽ tính đơn giá theo công thức như sau:

Tại ô D5 trong bảng, các bạn hãy nhập vào công thức:

D5=+VLOOKUP(LEFT(A5,2),$A$16:$E$20,MATCH(RIGHT(A5,2),$A$16:$E$16,0),FALSE)

Vấn đề 3: Tính thành tiền

Vấn đề này hay có trong các bài toán về tính các khoản chi phí, tính tiền lương,… Vấn đề này sẽ được giải quyết rất dễ dàng.

Các bạn chỉ cần nhập vào ô F5 công thức như sau khi muốn tính thành tiền:

E5=+D5*E5

Cách sửa Lỗi #NA trong hàm Vlookup như thế nào?

Trước khi đi vào tìm hiểu về cách sửa lỗi, các bạn hãy cùng chúng tôi tìm hiểu xem #NA trong hàm Vlookup là lỗi gì nhé!

#NA chính là một lỗi thường xuất hiện khi công thức bạn nhập vào Excel không tìm ra giá trị phù hợp.

Trong quá trình sử dụng đến hàm Vlookup, các bạn sẽ gặp lỗi #NA khi bạn không tìm thất được điều kiện tìm kiếm thuộc vùng điều kiện. Hay nói theo cách chính xác hơn là tại cột đầu tiên của vùng điều kiện khi sửa dụng hàm Vlookup.

Cách sửa hàm Vlookup bị lỗi #NA

Bước 1: Tiến hành kiểm tra công thức so sánh tại các cột, hàng ở bảng Excel.

Với công thức của hàm VlookUP

= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Ý nghĩa của các giá trị đã được giải thích rõ ở trên.

Dựa vào bảng ở trên, các bạn có thể thấy được ô C10 chính là giá trị dò tìm. Còn giá trị tuyệt đối là $F$10:$G$13 và số 2 chính là thứ tự hiển thị tại bảng đang dùng để dò tìm. Cuối cùng, 0 là giá trị dò tìm tuyệt đối.

Từ đó, các bạn có thể thấy lỗi xảy ra không phải là do công thức. Hàm vlookup bị lỗi #N/A là do tại bảng tra không có bất kỳ một giá trị nào có mã là A005.

Bước 2: Các bạn hãy thêm vào giá trị cho mã A005 theo tên của mặt hàng mà các bạn muốn. Thay đổi lại công thức có cột giá trị dò tìm tuyệt đối sẽ được kéo dài hơn. Đồng thời, mã A005 sẽ là kem tại ô G14 trong bảng Excel.

Bước 3: Chọn Enter rồi bạn kéo thả xuống phía dưới để thực hiện việc sao chép công thức một cách hàng loạt. Cuối cùng, kết quả sẽ được hiển thị như sau:

Bên cạnh hàm Vlookup thì trong Excel còn có hàm vlookup if, hàm vlookup và if kết hợp với nhau,… Tất cả sẽ cho các bạn những kết quả chính xác và nhanh chóng nhất.

Trong Excel hàm Vlookup là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc (theo cột), nó giúp chúng ta thống kê, dò tìm dữ liệu theo cột một cách nhanh chóng và tiện lợi. Đây là một trong những hàm phổ biến và hữu ích nhất trong Excel, nhưng lại ít người hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp bạn hiểu và sử dụng hàm Vlookup một cách thành thạo qua các ví dụ thực tế nhất.

1. Chức năng của hàm Vlookup là gì?

Chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.

2. Cú pháp hàm Vlookup trong excel.

Trong đó:

Lookup_value(bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.

Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.

Row_index_num(bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.

Range_lookup(tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.

Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookupcủa bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.

Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A.

3. Ví dụ về hàm VLOOKUP trong Excel.

3.1. Các trường hợp sử dụng hàm Vlookup trong Excel.

Hàm Vlookup trong Excel có 2 cách sử dụng. Khi khi lập công thức ta có thể lựa chọn 1 trong 2 bằng cách gán giá trị cho Range_lookup là TRUE (1) hoặc FALSE (0).

Hàm Vlookup để tìm kiếm chính xác (Range_lookup = FALSE). Cách tìm kiếm này được áp dụng trong những trường hợp cần tìm kiếm kết quả khớp chính xác. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ báo lỗi. VD: Tìm kiếm tên, tuổi, quê quán, … với mã nhân viên khớp với mã nhân viên cho trước.

Hàm Vlookup để tìm kiếm tương đối (Range_lookup = TRUE). Cách tìm kiếm này được áp dụng trong những trường hợp tìm kiếm theo khoảng giá trị kết quả khớp tương đối. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value. VD: Xếp loại học lực của học sinh theo điểm tổng kết.

3.2. Sử dụng hàm Vlookup để tìm kiếm chính xác là gì?

Ví dụ: Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, quê quán. Một bảng khác có sẵn mã nhân viên và cột quê quán bị bỏ chống. Giờ bạn muốn điền thông tin quê quán cho từng nhân viên thì phải làm như thế nào?

Hình 1: Hàm Vlookup là gì?

Để điển thông tin quê quán cho nhân viên, tại ô G4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP( F4,$B$4:$D$10,3,0 )

Trong đó:

Sau khi điền xong công thức cho ô G4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại. Quan sát hình để hiểu rõ về công thức hơn.

Hình 2: Hàm Vlookup là gì?

3.3. Hàm Vlookup để tìm kiếm tương đối là gì.

Tìm kiếm tương đối chỉ có thể áp dụng khi giá trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể dùng dò tìm tương đối, khi đó nó tương tự như dùng hàm IF vô hạn vậy.

Ví dụ: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách:

Hình 3: Hàm Vlookup là gì?

Giờ ta sẽ sử dụng hàm VLOOKUP để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng Quy định xếp loại đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong trường hợp này ta có thể dùng dò tìm tương đối.

Tại ô E4, ta nhập vào công thức là: =VLOOKUP( D4,$B$14:$C$17,2,1 )

Trong đó:

Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những học sinh còn lại. Quan sát hình để hiểu rõ hơn về công thức và cách dò tìm tương đối.

Hình 4: Hàm Vlookup là gì?

Dễ thấy, khi không do tìm được kết quả nào trùng khớp với giá trị dò tìm thì hàm Vlookup dò tìm tương đối sẽ lấy giá trị lớn nhất kế tiếp nhỏ hơn nó.

4. Những điều cần biết khi sử dùng hàm Vlookup là gì?

4.1. Hàm Vlookup tìm kiếm từ phải qua trái.

Hàm Vlookup luôn tra cứu giá trị ở cột ngoài cùng bên trái của bảng và trả về giá trị tương ứng từ cột bên phải.

Hình 5: Hàm Vlookup là gì?

Lưu ý: Trong ví dụ này, hàm VLOOKUP không thể tra cứu Trình độ và trả về Mã NV. Hàm VLOOKUP chỉ nhìn sang bên phải. Nếu muốn dò tìm ngược lại, đừng lo lắng bạn có thể sử dụng LOOKUP trong Excel để thực hiện tra cứu ngược.

4.2. Sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup.

Trong excel có 3 loại địa chỉ:

Địa chỉ tương đối: Là địa chỉ bị thay đổi tương ứng với mỗi dòng và cột khi chúng ta thực hiện sao chép công thức. (VD: B5 là địa chỉ của hàng 5 cột B).

Địa chỉ tuyệt đối: Là địa chỉ được cố định lại, không thay đổi khi ta copy công thức. (VD: $A$1- địa chỉ tuyệt đối của 1 ô, $B$17:$C$20 – địa chỉ tuyệt đối của 1 vùng)

Để tạo địa chỉ tuyệt đối, thì bạn nhấn phím F4, lúc này sẽ có dấu đô la ($) ở trước chỉ số cột và dòng.

Tóm lại nếu là địa chỉ tuyệt đối thì bạn thấy có dấu đô la ($) trước chỉ số cột và dòng.

Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là địa chỉ chỉ cố định dòng hoặc cột mà thôi.

Cố định cột: Ví dụ: $A1, thì bạn thấy chỉ số cột được cố định, còn chỉ số dòng không được cố định.

Cố định dòng: Ví dụ: A$1 thì bạn thấy chỉ số cột không được cố định, còn chỉ số dòng cố định.

Khi sử dùng hàm Vlookup trong Excel bạn thường phải tìm kiếm cho cả cột nên việc copy công thức là không tránh khỏi. Lúc này bạn cần lưu ý để địa chỉ của vùng tìm kiếm là địa chỉ tuyệt đối để khi ta copy công thức cho những hàng khác thì vùng tìm kiếm của ta không bị thay đổi.

4.3. Hàm Vlookup trả về giá trị đầu tiền được tìm thấy.

Hình 6: Hàm Vlookup là gì?

Giải thích: Hàm VLOOKUP trả về quê của Nguyễn Huy Tưởng, không phả trả về quê của Nguyễn Huy Trạch.

4.4. Hàm vlookup không phân biệt chữ hoa chữ thường.

Thực hiện tra cứu không phân biệt chữ hoa chữ thường. Ví dụ, ở bên dưới tra cứu NGUYỄN HUY (ô G4) ở cột ngoài cùng bên trái của bảng.

Hình 7: Hàm Vlookup là gì?

Giải thích: Hàm VLOOKUP không phân biệt chữ hoa chữ thường nên nó sẽ tra cứu NGUYỄN HUY hoặc Nguyễn Huy hoặc nguyễn huy, v.v. Kết quả là hàm VLOOKUP trả về tiền quê của Nguyễn Huy Tưởng (trường hợp đầu tiên).

4.5. Hàm vlookup bị lỗi #N/A khi tìm kiếm.

Khi dò tìm nếu hàm VLOOKUP không thể tìm thấy kết quả phù hợp, nó sẽ trả về lỗi # N / A.

Nếu bạn tìm kiếm chính xác, thi giá trị bạn tìm phải khớp với trong bảng dò tìm.

Nếu bạn tìm kiếm tương đối thì giá trị bạn tìm phải lớn hợp giá trị nhỏ nhất trong bảng dò tìm.

Lỗi này rất thường gặp nếu bạn không nắm chắc về cách sử dụng hàm.

Để tìm hiều nguyên nhân và cách khắc phục lỗi này bạn tham khảo bài viết: Hàm Vlookup bị lỗi #N/A

Video hướng dẫn: Gợi ý học tập mở rộng.

Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản

Hàm LOOKUP Dùng để dò tìm một giá trị từ một dòng hoặc một cột trong một dãy ô hoặc một mảng giá trị. Hàm LOOKUP() có hai dạng: Vec-tơ (vector form) và Mảng (array form) – Dạng Vec-tơ: LOOKUP() tìm kiếm trên một dòng hoặc một cột, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (hoặc cột) được chỉ định. – Dạng Mảng: LOOKUP() tìm kiếm trên dòng (hoặc cột) đầu tiên của một mảng giá trị, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (hoặc cột) cuối cùng trong mảng đó. Vector form (dạng vec-tơ) – Cú pháp: LOOKUP(lookup_value, lookup_vector, result_vector) lookup_value: Là giá trị LOOKUP() sẽ tìm kiếm trong lookup_vector. Nó có thể là một số, một ký tự, một giá trị logic, một tên đã được định nghĩa của một vùng ô hoặc một tham chiếu đến một giá trị. lookup_vector: Là một vùng mà chỉ gồm một dòng (hoặc một cột) có chứa lookup_value. Những giá trị chứa trong vùng này có thể là một số, một ký tự hoặc một giá trị logic. – lookup_vector phải được sắp xếp theo thứ tự tăng dần, nếu không, LOOKUP() có thể cho kết quả không chính xác. – Nếu không tìm thấy lookup_value trong lookup_vector thì LOOKUP() sẽ lấy giá trị lớn nhất mà nhỏ hơn hoặc bằng lookup_value. – Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong lookup_vector thì LOOKUP() sẽ báo lỗi #NA! result_vector: Là một vùng mà chỉ gồm một dòng (hoặc một cột) chứa giá trị trả về. Kích thước của result_vector bắt buộc phải bằng kích thước của lookup_vector. Array form (dạng mảng) Cú pháp: LOOKUP(lookup_value, array) lookup_value: Là giá trị LOOKUP() sẽ tìm kiếm trong array. Nó có thể là một số, một ký tự, một giá trị logic, một tên đã được định nghĩa của một vùng ô hoặc một tham chiếu đến một giá trị. – Nếu không tìm thấy lookup_value trong array thì LOOKUP() sẽ lấy giá trị lớn nhất mà nhỏ hơn hoặc bằng lookup_value. – Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột hoặc hàng đầu tiên trong array thì LOOKUP() sẽ báo lỗi #NA! array: Là một vùng chứa lookup_value, có thế là số, ký tự, hoặc giá trị logic. Dạng mảng của LOOKUP() gần tương đương như hàm VLOOKUP() hoặc HLOOKUP(). Khác biệt ở chỗ VLOOKUP() và HLOOKUP() tìm kiếm trên cột (hoặc dòng) đầu tiên, còn LOOKUP() tìm kiếm trên cột hoặc trên dòng tùy thuộc vào dạng mảng được khai báo: – Nếu array là mảng có số cột nhiều hơn số dòng thì LOOKUP() sẽ tìm trên dòng đầu tiên. – Nếu array là mảng có số dòng nhiều hơn số cột thì LOOKUP() sẽ tìm trên cột đầu tiên. – Trường hợp array là mảng có số dòng bằng số cột thì LOOKUP() sẽ tìm trên cột đầu tiên. – VLOOKUP() và HLOOKUP() lấy kết quả trên cột (hoặc) dòng được chỉ định, còn LOOKUP() luôn luôn lấy kết quả trên dòng (hoặc cột) cuối cùng. – Các giá trị trên dòng (hoặc cột) đầu tiên của array phải được sắp xếp theo thứ tự tăng dần, nếu không, LOOKUP() có thể cho kết quả không chính xác.

Tên khác:

Hàm lấy số dư của phép chia

Hàm MOD trả về số dư sau khi chia một số cho ước số, kết quả sẽ cùng dấu với ước số (không phụ thuộc vào dấu của số bị chia).

Hàm MOD là một trong những hàm Excel cơ bản, nên nó sử dụng được trên các phiên bản Excel 2007, 2010, 2013, 2016 trở lên. Bên cạnh đó, trên phiên bản Excel cho máy tính bảng hay trên điện thoại iPhone hoặc android thì bạn vẫn có thể sử dụng hàm này.

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

Cú pháp: = MOD(number, divisor).

Lưu ý: Tùy theo cài đặt trên máy tính của bạn, mà bạn sử dụng dấu phẩy “,” hay dấu chấm phẩy “;” để ngăn cách.

Trong đó:

Number: Là số bị chia ( số muốn chia để tìm số dư), number là tham số bắt buộc

Divisor: Là số chia, là tham số bắt buộc.

Một số chú ý khi sử dụng hàm MOD

Nếu số chia bằng 0, thì hàm MOD trả về giá trị lỗi #DIV/0!

Hàm MOD trả về kết quả có cùng dấu với số chia , kết quả không quan tâm tới dấu của số bị chia.

Hàm MOD có thể được biểu đạt bằng các số hạng của hàm INT:

MOD(n, d) = n – d*INT(n/d)

Ứng dụng của hàm MOD trong Excel

Ứng dụng của hàm MOD trong đánh dấu các hàng

Ví dụ 2: Hãy đánh dấu các hàng thứ hai trong bảng số liệu

Ứng dụng hàm MOD để đánh dấu hàng

Để thực hiện đánh dấu, các bạn làm theo các bước sau.

Bước 1: Vào Conditional Formatting, chọn New rule

Bước 2: Khi bảng New Formatting rule mở ra, các bạn chọn Use a formula to detemine which cells to format

Bước 3: Nhập công thức =MOD(ROW(A1),2)=0 vào ô Edit the rule như hình bên dưới.

Bước 4 : Kích chuột vào ô Format để chọn kiểu chữ, màu chữ, màu nền đánh dấu

Sau đó nhấn OK chúng ta có kết quả, các hàng thứ 2 đã được đánh dấu.

Ứng dụng hàm MOD trong tìm số ngày lẻ

Ví dụ 3: Tính số ngày lẻ dựa vào ngày check in và check out.

Số ngày lẻ là số ngày trong khoảng thời gian đó không đủ 7 ngày( 1 tuần). Đồng nghĩa với việc là số dư của phép tính

(Thời gian Check out trừ đi thời gian Check in):7

Sử dụng hàm MOD chúng ta có công thức: Tại D2 =MOD(B2-A2,7)

Sao chép công thức ở D2 xuống các ô còn lại có kết quả:

Ứng dụng hàm MOD tính số ngày lẻ Người đăng: hoyTime: 2020-10-20 19:10:21