Kiến thức

Hàm VLOOKUP cách sử dụng và ví dụ cụ thể-Quantrimang.com

Hàm VLOOKUP cách sử dụng và ví dụ cụ thể

VLOOKUP là một trong những hàm Excel hữu ích nhất 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 các bạn hiểu rõ về

hàm VLOOKUP

bằng các ví dụ thực tế, như xếp loại học sinh, nhân viên bằng cách dùng hàm VLOOKUP.

  • Đây là những hàm cơ bản nhất trong Excel mà bạn cần nắm rõ

Bạn đang xem: Hàm VLOOKUP cách sử dụng và ví dụ cụ thể-Quantrimang.com

Hàm VLOOKUP là gì?

Hàm VLOOKUP là hàm dò tìm dữ liệu trong Excel. Tôi giả định rằng bạn đã có kiến thức nền về Excel và có thể sử dụng những hàm cơ bản như SUM, AVERAGE, và TODAY. Một trong những chức năng thông dụng nhất của VLOOKUP là một hàm dữ liệu, nghĩa là nó sẽ hoạt động dựa trên những bảng cơ sở dữ liệu hoặc đơn giản hơn là danh sách các hạng mục. Danh sách có rất nhiều loại. Bạn có thể lập bảng về nhân sự trong công ty, các loại mặt hàng sản phẩm, danh sách khách hàng, hoặc bất kỳ cái gì cũng được. Và dưới đây là danh sách những sản phẩm của công ty A đang bán trên thị trường:

Danh sách sản phẩm

Bảng cơ sở dữ liệu thường có những dấu hiệu nhận biết cho từng sản phẩm. Ở bảng dữ liệu trên, dấu hiệu nhận biết đặc biệt là cột “Item Code” (Mã sản phẩm). Chú ý: Để có thể sử dụng hàm VLOOKUP thì bảng dữ liệu phải có cột chứa dấu hiệu nhận biết như mã hoặc ID, và buộc phải là cột đầu tiên như bảng trên.

Cách dùng hàm VLOOKUP

  • Hàm VLOOKUP là gì?

  • Công thức của hàm VLOOKUP trong Excel 2016, 2013, 2010, 2007, 2003

  • VD1: Sử dụng hàm VLOOKUP để đánh giá xếp loại của học sinh, nhân viên:

  • VD2: Sử dụng VLOOKUP dò tìm tuyệt đối để lấy dữ liệu

  • VD3: Dùng VLOOKUP để trích xuất dữ liệu

  • VD4: Dùng hàm VLOOKUP trên 2 sheet Excel khác nhau

  • Sử dụng hàm VLOOKUP để làm gì?

Xem thêm: Số phức nghịch đảo là gì? Giải bài tập số phức nghịch đảo bằng máy tính

Công thức của hàm VLOOKUP trong Excel 2016, 2013, 2010, 2007, 2003

Hàm VLOOKUP trong Excel có công thức như sau:

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

Trong đó:

  • VLOOKUP: Là tên hàm
  • Các tham số in đậm bắt buộc phải có.
  • lookup_value: Giá trị dùng để dò tìm
  • table_array: Bảng chứa giá trị cần dò tìm, để ở dạng giá trị tuyệt đối với dấu $ đằng trước, ví dụ: $A$3:$E$40.
  • col_index_num: Thứ tự của cột chứa giá trị dò tìm trên table_array. Ví dụ trong bảng $A$3:$E$40, cột B chứa giá trị cần dò tìm thì col_index_num ở đây sẽ là 2; bảng $C$3:$F$40, cột E chứa giá trị dò tìm, thì col_index_num ở đây là 3.
  • range_lookup: Là phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối). Tham số này không bắt buộc phải luôn có trong công thức.

Dò tì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. Đối với các giá trị cần dò tìm không thể hoặc chưa được sắp xếp hãy dùng dò tìm tuyệt đối để tìm chính xác giá trị.

Video hướng dẫn dùng hàm VLOOKUP

Xem thêm: Công ước của LHQ về Luật Biển 1982 là nền tảng pháp lý quan trọng cho hợp tác khu vực

VD1: Sử dụng hàm VLOOKUP để đánh giá xếp loại của học sinh, nhân viên:

Giả sử, bạn có bảng điểm học sinh như sau:

STT Họ và tên Điểm TB Xếp loại
1 Nguyễn Hoàng Anh 9.1
2 Trần Vân Anh 8.3
3 Nguyễn Quanh Vinh 9.5
4 Trần Hồng Quang 8.6
5 Đỗ Thanh Hoa 5.0
6 Lê Hồng Ngọc 4.5
7 Đoàn Thanh Vân 7.2
8 Ngô Ngọc Bích 0.0
9 Hoàng Thu Thảo 6.6
10 Đinh Minh Đức 8.7

Và bảng quy định xếp loại như sau:

Quy định xếp loại
0 Yếu
5.5 Trung bình
7 Khá
8.5 Giỏi

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.

Trong Excel 2 bảng này được trình bày như sau:

Bảng điểm học sinh và quy định xếp loại

Ở đây, giá trị cần dò tìm nằm ở cột C, bắt đầu từ dòng thứ 6. Phạm vi tìm kiếm là $A$18:$B$21, thứ tự cột chứa giá trị dò tìm là 2.

Tại ô D6, bạn nhập vào công thức: =VLOOKUP($C6,$A$18:$B$21,2,1). Đây là công thức dò tìm tương đối, bạn có thể thực hiện dò tìm tuyệt đối nếu muốn (hoặc do bảng xếp loại chưa được sắp xếp theo thứ tự) bằng cách thêm 0 vào công thức như thế này: =VLOOKUP($C6,$A$18:$B$21,2,0). Nhấn Enter.

Nhập công thức VLOOKUP để xếp loại học sinh

Nhấp chuột vào ô D6, xuất hiện ô vuông nhỏ ở góc dưới bên phải, bạn nhấp chuột vào đó và kéo dọc xuống hết bảng để sao chép công thức xếp loại cho những học sinh còn lại.

Nhấp vào ô vuông nhỏ để kéo công thức xuống toàn bảng

Khi đó, ta có kết quả dùng hàm VLOOKUP để xếp loại học lực học sinh như sau:

Kết quả xếp loại học sinh bằng VLOOKUP

Bạn có thắc mắc tại sao phải sử dụng $ trước C6 không? $ sẽ giúp cố định cột C, chỉ thay đổi các hàng khi bạn kéo công thức xuống toàn bảng. Còn $A$18:$B$21 để giúp cố định bảng quy định xếp loại, khiến nó không bị thay đổi khi bạn kéo công thức.

VD2: Sử dụng VLOOKUP dò tìm tuyệt đối để lấy dữ liệu

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, chức vụ. Một bảng khác lưu trữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền thông tin quê quán, trình độ học vấn cho từng nhân viên thì phải làm như thế nào?

Giả sử bạn có bảng nhân viên và quê quán nhân viên như sau:

Bảng dữ liệu nhân viên

Giờ bạn muốn điền thông tin quê quán cho nhân viên. Tại ô D4, bạn nhập vào công thức dò tìm tuyệt đối như sau: =Vlookup($A4,$A$16:$C$25,2,0)

Nhập công thức dò tìm thông tin quê quán cho nhân viên

Rồi nhấn Enter. Nhấp vào ô vuông nhỏ xuất hiện dưới góc ô D4 và kéo xuống toàn bảng để sao chép công thức cho các nhân viên khác.

Sao chép công thức VLOOKUP cho toàn bộ nhân viên

Để điền thông tin trình độ cho các nhân viên, tại ô E4 bạn nhập vào công thức dò tìm tuyệt đối: =VLOOKUP($A4,$A$16:$C$25,3,0)

Công thức dò tìm tuyệt đối VLOOKUP

Bạn tiếp tục nhấn Enter và kéo xuống để sao chép công thức cho những nhân viên còn lại, ta sẽ được kết quả như sau:

Sao chép công thức dò tìm trình độ nhân viên

Xem thêm: Khối lượng riêng là gì? Công thức tính khối lượng riêng của một chất

VD3: Dùng VLOOKUP để trích xuất dữ liệu

Tiếp tục với bộ dữ liệu của ví dụ 2, giờ ta sẽ đi tìm quê quán của 3 nhân viên là Nguyễn Hoàng Anh, Trần Vân Anh và Nguyễn Quang Vinh. Mình đã trích xuất ra thành một bảng mới F15:G18.

Việc dò tìm này mình sẽ thực hiện trên bảng A3:E13, sau khi đã được điền quê quán và trình độ. Lúc này, bạn nhập công thức dò tìm tuyệt đối sau vào ô G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > nhấn Enter.

Cách dùng hàm VLOOKUP để dò tìm thông tin

Sao chép công thức cho 2 nhân viên còn lại ta được kết quả như sau:

Kết quả dò tìm với VLOOKUP

Lưu ý trong ví dụ này, giá trị dò tìm nằm ở cột B, vì thế vùng dò tìm ta tính từ cột B mà không tính từ cột A.

VD4: Dùng hàm VLOOKUP trên 2 sheet Excel khác nhau

Quay lại với bộ dữ liệu ở ví dụ 2 sau khi nhân viên được điền xong trình độ và quê quán, ta đặt tên cho sheet là QTM.

Bảng dữ liệu chứa giá trị cần dò tìm với VLOOKUP

Ở một sheet khác của bảng tính, đặt tên là QTM1, bạn cần lấy thông tin về trình độ và chức vụ của nhân viên với thứ tự sắp xếp của nhân viên đã thay đổi. Đây mới là lúc bạn thấy được sức mạnh thực sự của hàm VLOOKUP.

Bảng cần điền dữ liệu với VLOOKUP

Để dò tìm dữ liệu về “Trình độ” của nhân viên, bạn nhập công thức: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) vào ô C4 của sheet QTM1.

Nhập công thức VLOOKUP dò tìm dữ liệu giữa 2 sheet khác nhau

Trong đó:

  • B4 là cột chứa giá trị dùng để dò tìm.
  • QTM! là tên sheet chứa bảng có giá trị cần dò tìm, sau tên sheet bạn thêm dấu !
  • $B$3:$E$13 là bảng chứa giá trị dò tìm và sheet chứa bảng (QTM).
  • 4 là số thứ tự của cột “Trình độ”, tính từ cột “Họ và tên” trên sheet QTM.
  • 0 là dò tìm tuyệt đối.

Nhấn Enter, rồi sao chép công thức cho toàn bộ nhân viên còn lại trong bảng, ta được kết quả như sau:

Kết quả khi dùng VLOOKUP ở 2 sheet khác nhau

Để dò tìm dữ liệu “Chức vụ” của nhân viên, tại ô D4 của sheet QTM1, bạn nhập vào công thức: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), nhấn Enter.

Cách dùng VLOOKUP ở hai sheet khác nhau

Sao chép công thức cho các nhân viên còn lại, ta được như sau:

Bảng dữ liệu được điền với VLOOKUP

Sử dụng hàm VLOOKUP để làm gì?

Trước tiên chúng ta cần tìm câu trả lời chính xác cho câu hỏi “chúng ta sử dụng hàm VLOOKUP để làm gì?”.

Hàm VLOOKUP được sử dụng để hỗ trợ tra cứu thông tin trong một trường dữ liệu hoặc danh sách dựa vào những mã định danh có sẵn.

Ví dụ, chèn hàm VLOOKUP kèm theo mã sản phẩm vào một bảng tính khác, nó sẽ hiển thị thông tin của sản phẩm tương ứng với mã đó. Những thông tin đó có thể là mô tả, giá thành, số lượng tồn kho, tùy theo nội dung công thức mà bạn viết.

Lượng thông tin cần tìm càng nhỏ thì khi viết hàm VLOOKUP sẽ càng khó khăn hơn. Thông thường bạn sẽ sử dụng hàm này vào một bảng tính tái sử dụng như mẫu. Mỗi lần nhập mã sản phẩm thích hợp, hệ thống sẽ truy xuất tất cả những thông tin cần thiết về sản phẩm tương ứng.

Xem thêm:

  • Cách sử dụng hàm IF trong Excel

  • Cách dùng hàm AVERAGEIF trên Excel

  • Cách sửa lỗi Errors were detected while saving file trong Excel 2010

  • Cách khắc phục lỗi VLOOKUP trong Excel

  • Cách đánh số trang trong Excel cực nhanh và dễ

  • Cách sử dụng hàm Hlookup trên Excel

  • Cách tạo danh sách sổ xuống (drop list) trên Excel 2016

  • Hàm SUMIF trong Excel: Cách sử dụng và ví dụ cụ thể

Thứ Ba, 16/04/2019 13:35

444 👨 748.444

0 Bình luận

Sắp xếp theo Mặc địnhMới nhấtCũ nhất
❖

Xóa

Đăng nhập để Gửi

Tham khảo thêm
  • Cách sử dụng lọc AdBlock Plus trong IE8

  • 21 cách sử dụng tính năng Paste trong Excel

  • Hàm Index trong Excel: Công thức và cách sử dụng

  • Cách sử dụng hàm NORMDIST trong Excel

  • Cách sử dụng Bitmoji trong Snapchat

  • Cách sử dụng các items trong Pokemon GO

  • Cách sử dụng hàm Search trong Excel

Bài viết mới nhất
  • Euro 2021 chiếu trên kênh nào? Các kênh xem trực tiếp Euro 2021 Euro 2021 chiếu trên kênh nào? Các kênh xem trực tiếp Euro 2021

  • Rò rỉ điểm benchmark ấn tượng của CPU Intel Core i7-11390H Tiger Lake Rò rỉ điểm benchmark ấn tượng của CPU Intel Core i7-11390H Tiger Lake

  • Cách đăng ký tài khoản LOship, đăng nhập Loship Cách đăng ký tài khoản LOship, đăng nhập Loship

  • Cách chụp màn hình chỉ thanh Dock trên máy Mac Cách chụp màn hình chỉ thanh Dock trên máy Mac

  • Cách tính điện năng tiêu thụ của các thiết bị điện trong nhà với EPoint Cách tính điện năng tiêu thụ của các thiết bị điện trong nhà với EPoint

  • Cách tính tiền điện điều hòa mỗi tháng với EPoint Cách tính tiền điện điều hòa mỗi tháng với EPoint

Microsoft Excel
  • Cách tự động tạo viền ô nhập giá trị trong Excel Cách tự động tạo viền ô nhập giá trị trong Excel

  • Cách thêm máy tính vào Excel Cách thêm máy tính vào Excel

  • Cách căn đều chữ trong ô trên Excel Cách căn đều chữ trong ô trên Excel

  • Cách cập nhật Microsoft Office lên phiên bản mới nhất Cách cập nhật Microsoft Office lên phiên bản mới nhất

  • 7 template Excel miễn phí giúp quản lý ngân sách 7 template Excel miễn phí giúp quản lý ngân sách

  • 10 phông chữ Google miễn phí tốt nhất cho các bài thuyết trình chuyên nghiệp 10 phông chữ Google miễn phí tốt nhất cho các bài thuyết trình chuyên nghiệp

  • Cách xóa smart tag trong Excel Cách xóa smart tag trong Excel

  • Cách xoay khổ giấy trong Excel Cách xoay khổ giấy trong Excel

  • Cách nhập dữ liệu Microsoft Access vào Microsoft Excel Cách nhập dữ liệu Microsoft Access vào Microsoft Excel

Xem thêm

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Close
Back to top button