Vlookup là một trong những hàm thông dụng nhất trong Excel nhưng bạn không biết hàm Vlookup là gì? Bài tập hàm Vlookup? Trong nội dung này, tinhocvanphongs.com sẽ phân phối hướng dẫn từng bước về cách sử dụng, tính năng, các ví dụ và lời khuyên về cách sửa các lỗi Vlookup thông dụng mà bạn hay gặp phải. Từ đó, cho phép người dùng tận dụng tối đa lợi nhuận của việc sử dụng hàm Vlookup trong việc tìm kiếm tìm kiếm cũng như ghi chép.
Table of Contents
Đôi nét về hàm Vlookup trong Excel
Hàm Vlookup là gì? Bài tập hàm Vlookup có gì đặc biệt? Vlookup là viết tắt của Vertical lookup nghĩa là tra cứu dọc, là một hàm Excel tích hợp. Hiểu đơn giản, hàm VLOOKUP tìm kiếm một giá trị cụ thể trong dữ liệu và khi xác nhận được giá trị đó, nó có thể tìm thấy và hiển thị một số thông tin khác có liên quan đến giá trị đó từ một cột khác.
Khái niệm này sẽ được trổ tài rõ ràng thông qua ví dụ dưới đây:
– Hãy tưởng tượng rằng bạn đang giữ một bảng tính về mức lương theo giờ của nhân viên (xem bảng tính ‘Trả theo giờ’ bên trái). Vào cuối mỗi tuần, người quản lý nhóm bán hàng sẽ gửi cho bạn một danh sách các giờ làm việc của mỗi nhân viên trong tuần (xem bảng tính ‘Giờ của đội bán hàng’ bên phải). Công việc của bạn là hoàn thiện bảng tính ‘Giờ của đội bán hàng’, để hiển thị số vốn phải trả cho từng thành viên.
Bảng tính lương nhân viên của nhóm bán hàng theo giờ
– Để tính toán khoản lương phải trả cho từng thành viên trong nhóm bán hàng, trước tiên bạn cần tra cứu mức lương hàng giờ cho mỗi người và chèn số vốn này vào cột C của bảng tính ‘Giờ của nhóm bán hàng’. Điều này có thể được thực hiện bằng cách sử dụng tính năng của hàm Vlookup.
Cú pháp và quy tắc của hàm Vlookup
Để làm quen với hàm Vlookup là gì? Bài tập hàm Vlookup, bạn cần nắm được cú pháp của hàm Vlookup trong Microsoft Office Excel. Cú pháp của công thức Vlookup chứa bốn đối số hoặc tham số thiết yếu để nó hoạt động:
VLOOKUP (lookup_value, table_array, col_index_num, [Range_lookup])
Trong số đó các đối số của hàm bao gồm:
- lookup_value: Giá trị mà bạn muốn tìm kiếm trong cột trước nhất của table_array và muốn các giá trị hàng tương ứng được trả về. Nếu Excel tìm thấy giá trị tra cứu trong table_array, nó sẽ trả về giá trị tương ứng của cột đã cho. Nếu không, nó sẽ trả về lỗi # N / A.
- table_array: là một phạm vị trong bảng tính Excel chứa các giá trị mà người dùng muốn tìm kiếm.
- col_index_num: Là thứ tự của cột cần lấy dữ liệu trên bảng giá trị table_array mà bạn muốn tìm kiếm. Cột trước nhất của vùng dữ liệu được tính là 1.
- [Range_lookup]: trả về kết quả khớp gần đúng hoặc chuẩn xác. Ở đây, ta có:
- TRUE tương ứng với 1 là tìm kiếm giá trị tương đối hoặc bằng giá trị này
- FALSE tương ứng với 0 là tìm kiếm chuẩn xác.
Quy tắc của hàm Vlookup
Để sử dụng thành thục hàm Vlookup, bên cạnh việc hiểu rõ công thức bạn cần phải nắm kỹ những quy tắc sau:
- Dữ liệu trong table_array được phân phối phải tổ chức theo cột.
- Hàm xem văn bản chữ hoa và chữ thường là bằng nhau. Nghĩa là tra cứu chuỗi “Văn bản” sẽ khớp với “văn bản”.
- Hàm Vlookup không nhận các giá trị số và văn bản là bằng nhau.
- Khi xác minh xem hai ký tự có khớp nhau hay không, hàm Vlookup sẽ so sánh toàn bộ các kí tự bao gồm cả khoảng trắng ở đầu hoặc cuối. Do đó, với hai chuỗi ký tự là “văn bản” (không dấu cách) và “văn bản ” (có dấu cách)không phải là hai giá trị chuẩn xác.
Trong Vlookups liên quan đến văn bản, trong đó bắt buộc phải khớp chuẩn xác, lookup_value có thể chứa các ký tự đại diện sau:
- (?): thích hợp với bất kỳ ký tự đơn nào
- (*): khớp với bất kỳ chuỗi ký tự nào
Ví dụ: lookup_value “t*” sẽ khớp với bất kì chuỗi văn bản nào khởi đầu bằng ký tự “t”
Ví dụ về Vlookup với giá trị chuẩn xác
Trong ví dụ này, hàm Vlookup của Excel được sử dụng để tra cứu số vốn phải trả cho mỗi thành viên của nhóm bán hàng trong tuần trước.Bên dưới là mức lương hàng giờ của nhân viên được lưu trữ trong bảng tính thứ nhất và một danh sách các giờ làm việc được lưu trữ trong bảng tính thứ hai.
Lương theo giờ
Giờ làm của nhóm bán hàng
Để bảng tính này hiển thị số vốn phải trả cho từng thành viên trong nhóm bán hàng, trước tiên bạn cần phải tìm kiếm lương một giờ của mỗi người từ bảng tính “Lương theo giờ”. Trong bảng tính bên dưới, các ô C2 – C4 của bảng tính “Giờ làm của nhóm bán hàng” đã được update với tính năng Vlookup, tìm kiếm tỷ lệ thanh toán hàng giờ cho mỗi thành viên.
Giờ làm của nhóm bán hàng với tính năng tìm kiếm hàm Vlookup
Sau khoảng thời gian vận dụng đúng công thức của hàm Vlookup, kết quả về mức lương theo giờ của mỗi thành viên đã được hiển thị trong cột C.
Kết quả tìm kiếm được chèn ở vào ô tương ứng ở cột C
Giải thích công thức:
Công thức được sử dụng trong bảng tính trên là:
= VLOOKUP (A2, ‘Hourly Pay’! A:B, 2, FALSE)
Trong số đó:
- A2: Ô A2 là lookup_value tức là giá trị cần tìm chính là chuỗi văn bản “Benson, Paul”.
- ‘Hourly Pay’! A:B: table_array được tạo thành từ các cột A và B của bảng tính “Lương theo giờ”. Hàm Vlookup sẽ tìm kiếm xuống cột ngoài cùng bên trái (tức là cột A) của table_array này để tìm kiếm chuỗi văn bản “Benson, Paul”.
- 2: Đối số col_index_num được đặt thành giá trị 2 cho biết, giá trị được trả về phải được lấy từ cột 2 của table_array được phân phối (nghĩa là từ cột B của bảng tính “Lương theo giờ “).
- FALSE: Đối số [range_lookup] được đặt thành FALSE, cho biết hàm Vlookup tìm kiếm giá trị chuẩn xác với lookup_value .
Nếu không tìm thấy kết quả khớp chuẩn xác, thì hàm Vlookup sẽ trả về lỗi.
Ví dụ về Vlookup với giá trị tương đối
Trái ngược với ví dụ trên, ví dụ dưới đây sẽ hiển thị cách sử dụng Hàm Vlookup khi đối số trong [range_lookup] được đặt thành TRUE hoặc bị bỏ qua.
Hãy tưởng tượng một tài khoản ngân hàng có lời suất thay đổi phụ thuộc vào số dư của nó được hiển thị theo bảng bên dưới:
Giá trị
Lãi suất
$ 0 – $ 999,99
1%
$ 1.000 – $ 4,999,99
3%
$ 5.000 – $ 19.999,99
4%
Hơn 20.000 đô la
5%
Bảng lãi suất theo số dư
Bảng tính “Lãi suất” và “Tài khoản ngân hàng” cùng số dư hiện tại của họ được trổ tài ở bảng dưới đây:
Tài khoản ngân hàng
Lãi suất
Cột C của bảng tính “Tài khoản ngân hàng” hiện trống, nhưng cột này cần được update với lãi suất sẽ được vận dụng cho từng tài khoản ngân hàng, tùy thuộc vào số dư hiện tại của tài khoản. Điều này có thể được thực hiện bằng cách sử dụng tính năng Vlookup, như hiển thị bên dưới:
Bảng tính tài khoản ngân hàng khi vận dụng hàm Vlookup
So với mỗi tài khoản ngân hàng trong bảng tính ở trên, hàm Vlookup tìm giá trị gần nhất của số dư hiện tại, trong cột A của bảng tính “Lãi suất”. Hàm sau đó trả về giá trị tương ứng từ cột C của bảng tính này. Kết quả khi sử dụng hàm Vlookup được hiển thị dưới đây:
Kết quả “Tài khoản ngân hàng” sau khoảng thời gian vận dụng hàm Vlookup
Giải thích công thức:
Công thức được sử dụng trong bảng tính trên là:
= VLOOKUP (B2, ‘Interest Rates’! A2:C5, 3, TRUE)
Trong số đó:
- B2: Ô B2 là lookup_value tức là giá trị cần tìm chính là giá trị 5.69.
- ‘Interest Rates’! A2:C5: table_array là phạm vi được tạo thành từ A2 và C5 của bảng tính “Lãi suất”. Hàm Vlookup sẽ tìm kiếm xuống cột bên trái (tức là cột A) của table_array, để tìm kiếm kết quả bằng hoặc khớp gần nhất.
- 3: : Đối số col_index_num được đặt thành giá trị 3 cho biết giá trị được trả về bởi hàm VLOOKUP nên được lấy từ cột 3 của table_array (tức là từ cột C của bảng tính Lãi suất”).
- TRUE: Đối số [range_lookup] được đặt thành TRUE, cho biết hàm Vlookup để tìm kết quả khớp gần nhất hoặc bằng với lookup_value. Tức là nếu một phối hợp chuẩn xác không được tìm thấy, thì hàm sẽ tự động trả về giá trị gần nhất.
Khắc phục lỗi Vlookup
Nếu bạn gặp lỗi Vlookup, bước trước nhất là xác minh xem bạn đã tuân thủ đúng Quy tắc Vlookup chưa. Nếu bạn tự tin rằng công thức của mình là chuẩn xác, hãy sử dụng những gợi ý dưới đây để xác nhận nguyên nhân có thể gây ra lỗi mà bạn đang gặp phải:
Lỗi thường gặp
Nguyên nhân
#N /A
Xảy ra khi hàm Vlookup không tìm thấy kết quả khớp với lookup_value được phân phối.
Nguyên nhân của điều này thường phụ thuộc vào đối số [phạm vi_lookup] được phân phối:
● Giá trị nhỏ nhất trong cột bên trái của table_array to hơn lookup_value được phân phối
● Cột bên trái của table_array không theo thứ tự tăng dần.
● Không tìm thấy kết quả khớp chuẩn xác với lookup_value trong cột bên trái của table_array
#REF!
Xảy ra nếu một trong hai:
● Đối số col_index_num được phân phối to hơn số lượng cột trong table_array được phân phối
● Tham chiếu các ô không tồn tại.
#VALUE
Xảy ra nếu một trong hai:
Đối số col_index_num được phân phối là <1 hoặc không được nhận dạng dưới dạng giá trị số.
Đối số [Range_lookup] được phân phối không được xác nhận là một trong các giá trị logic của TRUE hoặc FALSE.
Trả về giá trị không chuẩn xác
Nếu hàm Vlookup trả về giá trị sai, hãy xác minh các mục sau:
Các giá trị bạn đang tìm kiếm phải nằm ở cột bên trái của table_array
Nếu đối số [range_lookup] được đặt thành TRUE (hoặc bị bỏ qua), hàm sẽ trả về kết quả khớp gần nhất bên dưới lookup_value. Để điều này hoạt động chuẩn xác, cột bên trái của table_array phải theo thứ tự tăng dần.
Xác minh xem đối số col_index_num có tính từ cột trước nhất của table_array . Nó không nhất thiết giống như số cột của bảng tính.
Nếu đối số [range_lookup] được đặt thành FALSE, hàm Vlookup yêu cầu giá trị chuẩn xác. Xác minh xem chỉ có một kết quả khớp với lookup_value trong cột bên trái của table_array. Lưu ý rằng nếu có nhiều hơn một giá trị trùng khớp, tính năng Vlookup sẽ nhận giá trị trước nhất mà nó gặp.
Bài tập hàm Vlookup
Hàm Vlookup là gì? Bài tập hàm Vlookup? Trong phần này, tinhocvanphongs.com sẽ gửi đến bạn hai bài tập cơ bản dành cho người mới khởi đầu biết tới hàm Vlookup:
Bài tập 1
Dưới đây, tất cả chúng ta có một bảng danh sách học sinh. Nhiệm vụ của bạn là tìm tên cuối cùng của một học sinh có tên là ‘Emmanuel’.
Danh sách học sinh
Lời giải:
Chúng tôi sẽ sử dụng hàm Vlookup như sau =VLOOKUP(E4,A3:C16,2,FALSE) và kết quả sẽ là “Schulyuly”.
Trong số đó:
- Đối số trước nhất cho hàm tức là ‘ lookup_value’ = E4 (Emueluel).
- Đối số thứ hai tức là ‘ table_array’ = A3:C16 (Phạm vi bảng học sinh).
- Đối số thứ ba tức là ‘ column_index’ = 2 (số cột có giá trị mà hàm VLOOKUP sẽ trả về).
- Đối số thứ tư tức là ‘ range_lookup’ = FALSE (Biểu thị rằng chúng tôi chỉ muốn giá trị chuẩn xác).
Bài tập 2
Trong bài tập này, chúng tôi sẽ sử dụng các ký tự đại diện cùng với tra cứu dọc. Dựa vào bảng tính dưới đây, hãy tìm điểm của học sinh có tên khởi đầu bằng ‘A’.
Bảng tính khi sử dụng công thức của hàm Vlookup
Lời giải:
Công thức được sử dụng là =VLOOKUP(“A*”,A3:C16,3,FALSE) và cho kết quả 66.
Trong số đó:
- Đối số trước nhất cho hàm tức là ‘ lookup_value’ = A * (Bất kỳ từ nào khởi đầu bằng bảng chữ cái ‘A’)
- Đối số thứ hai tức là ‘ table_array’ = A3: C16 (Phạm vi bảng học sinh)
- Đối số thứ ba tức là ‘ column_index’ = 3 (số cột có giá trị mà hàm tra cứu dọc sẽ trả về)
- Đối số thứ tư tức là ‘ range_lookup’ = FALSE (Biểu thị rằng chúng tôi chỉ muốn giá trị khớp tương ứng)
Trên đây là những hướng dẫn và giải thích về hàm Vlookup là gì? Bài tập hàm Vlookup mà thủ thuật Excel gửi đến cho bạn. Kì vọng thông qua nội dung này, bạn sẽ trở nên tự tin và thành thục khi vận dụng hàm Vlookup vào trong các bảng tính nhằm mang ra kết quả nhanh và chuẩn xác nhất.
