Kiến thức

BÀI TẬP EXCEL VỚI CÁCH SỬ DỤNG HÀM VLOOKUP VÀ ỨNG DỤNG NÂNG CAO THƯỜNG GẶP (PHẦN 2)

BÀI TẬP EXCEL VỚI CÁCH SỬ DỤNG HÀM VLOOKUP VÀ ỨNG DỤNG NÂNG CAO THƯỜNG GẶP (PHẦN 2)

Gitiho Learning 2

Jan 21 2021

Nội dung chính

  • 1 Cách đặt tên cho vùng dữ liệu, dải ô hay bảng dữ liệu trong công thức hàm VLOOKUP

  • 2 Sử dụng ký tự đại diện trong công thức VLOOKUP

    • 2.1 Ví dụ 1: Tìm kiếm bằng một số ký tự bắt đầu hay kết thúc của từ khóa cần tìm

    • 2.2 Ví dụ 2: Tìm kiếm bằng ký tự đại diện dựa trên giá trị ô

  • 3 Giá trị tìm kiếm chính xác và tương đối trong hàm VLOOKUP

    • 3.1 Ví dụ 1: Dùng VLOOKUP với kết quả tìm kiếm tuyệt đối

    • 3.2 Ví dụ 2: Dùng hàm VLOOKUP với kết quả tìm kiếm tương đối

  • 4 Kết luận: Những điều cần ghi nhớ!

Trong bài viết trước, Gitiho đã giới thiệu cho bạn đọc những điều căn bản về cách sử dụng hàm VLOOKUP qua bài 

BÀI TẬP EXCEL VỚI CÁCH SỬ DỤNG HÀM VLOOKUP VÀ ỨNG DỤNG NÂNG CAO THƯỜNG GẶP (PHẦN 1)

. Trong phần 2 này, hãy cùng Gitiho làm sáng tỏ những khía cạnh khác, nâng cao hơn về hàm này nhé!

Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ

Bạn đang xem: BÀI TẬP EXCEL VỚI CÁCH SỬ DỤNG HÀM VLOOKUP VÀ ỨNG DỤNG NÂNG CAO THƯỜNG GẶP (PHẦN 2)

Cách đặt tên cho vùng dữ liệu, dải ô hay bảng dữ liệu trong công thức hàm VLOOKUP

Thông thường khi công thức yêu cầu nhập table_array, thì bạn sẽ bôi đen bảng hoặc dãy chứa dữ liệu đó. Nhưng nếu như bạn chỉ cần nhập tên bảng hoặc dãy dự liệu đó thì sao, có vẻ đơn giản hơn nhỉ? Hãy cùng thử nhé.

Để tạo tên cho vùng dữ liệu, bạn chỉ cần chọn dãy ô chứa dữ liệu đó và gõ bất kỳ tên nào vào Name box (Hộp tên) – ở bên trái thanh Công thức như hình dưới đây:

Khi đó, công thức có thể nhập như sau:

=VLOOKUP(“Product 1”,Products,2)

Hầu hết tên vùng dữ liệu trong Excel đều được sử dụng cho toàn bộ trang tính, nên bạn không cần định rõ tên của trang tính trong câu lệnh table_array, dù cho dải ô bạn cần tìm nằm ở trang tính khác. Nếu nó nằm ở sổ làm việc khác (file khác), thì bạn phải đặt tên file đó phía trước tên vùng dữ liệu đã đặt tên, ví dụ:

=VLOOKUP(“Product 1”, PriceList.xlsx!Products,2)

Bên cạnh đó, việc sử dụng tên cho vùng dữ liệu là một sự thay thế tuyệt vời cho tham chiếu ô tuyệt đối. Bởi vì tên vùng dữ liệu không đổi khi sao chép công thức sang các ô khác, nên bạn có thể chắc rằng bạn sẽ luôn tìm đúng vùng dữ liệu cần tìm.
Nếu bạn đã đổi vùng dữ liệu thành bảng với đầy đủ tính năng (dùng tab Insert > Table) thì bạn có thể dùng chuột chọn vùng dữ liệu đó, và  Excel sẽ tự động thêm tên các cột (hay tên bảng trong trường hợp bạn đã chọn toàn bộ bảng) vào công thức:
=VLOOKUP(“Product 1”, Table46[[Product]:[Price]],2)
hay thậm chí là có dạng như thế này:
=VLOOKUP(“Product 1”, Table46,2)

Sử dụng ký tự đại diện trong công thức

VLOOKUP

Có một số kỹ tự có thể đại diện trong

hàm VLOOKUP

  • Dấu chấm hỏi (?) để khớp với bất cứ ký tự riêng lẻ nào, và
  • Dấu sao (*) để khớp với bất kỳ dãy ký tự nào.

Việc sử dụng ký tự đại diện trong công thức

VLOOKUP

có thể rất hữu ích trong nhiều trường hợp:

  1. Khi bạn không nhớ chính xác chuỗi ký tự bạn đang tìm kiếm.
  2. Khi bạn muốn tìm một từ nào đó. Từ đó là một phần của nội dung ô. Hãy chú ý rằng

    hàm VLOOKUP

    tìm kiếm toàn bộ nội dung ô.

  3. Khi cột cần tìm có khoảng trống thụt đầu dòng hay cuối dòng. Nếu gặp trường hợp này, thì bạn có thể vắt óc cố tìm ra lý do tại sao công thức thông thường của bạn lại không được thực hiện.

Ví dụ 1: Tìm kiếm bằng một số ký tự bắt đầu hay kết thúc của từ khóa cần tìm

Giả sử bạn phải tìm tên của 1 khác hàng trong khi bạn không nhớ đầy đủ tên của anh ta, bạn chỉ nhớ tên anh ta bắng đầu bằng vài ký tự như ”ack”. Việc còn lại, hãy để

hàm VLOOKUP

lo!

Sử dụng công thức sau để tìm kiếm:

=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)

Sau khi bạn thấy kết quả là tên bạn tìm kiếm, bạn có thể sử dụng công thức

VLOOKUP

tương tự để tìm số tiền người khách hàng ấy đã trả. Bạn chỉ cần thay đổi thông số thứ ba trong công thức thành số thứ cột mà bạn cần tìm, đó là cột C trong trường hợp này:

=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)

Sau đây là thêm vài ví dụ về việc sử dụng hàm VLOOKUP bằng ký tự bắt đầu và kết thúc:
  • =VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – tìm tên kết thúc bằng “man”.
  • =VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE) – tìm tên bắt đầu bằng “ad” và kết thúc bằng “son”.
  • =VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – tìm họ có 5 ký tự.
Lưu ý: Để công thức

VLOOKUP

có ký tự đại diện chạy chính xác, bạn phải luôn thêm FALSE là tham số cuối cùng (như đã phân tích ở trên). Nếu dải ô cần tìm của bạn có hơn một mục nhập khớp với tiêu chuẩn đại diện, thì giá trị tìm thấy đầu tiên sẽ được trả về.

Ví dụ 2: Tìm kiếm bằng ký tự đại diện dựa trên giá trị ô

Làm thế nào để tìm kiếm  giá trị ở 1 ô nào đó? Giả sử, bạn có mã bản quyền ở cột A (ví dụ là 3MLHK-ABCD-7ERQV) và tên bản quyền ở cột B (là Jeremy Hill), và 1 vài ký tự của mã bản quyền đó ở ô C1 (ví dụ là ABCD)
Bạn có thể dùng

hàm VLOOKUP

để tìm kiếm như sau:

=VLOOKUP(“*”&C1&”*”, $A$2:$B$12, 2, FALSE)

Công thức này tìm kiếm tbất kỳ mã bản quyền nào có chứa ký tự như ở ô C1 (là ABCD) và sau đó trả kết quả cột B tương ứng.Xin hãy chú ý rằng chúng ta sử dụng dấu và (&) trước và sau một tham chiếu ô trong thông số đầu tiên nhằm nối chuỗi ký tự.
Như bạn có thể thấy trong ảnh chụp màn hình bên dưới,

hàm VLOOKUP

của tôi trả về “Jeremy Hill” bởi vì mã bản quyền của anh ấy có một nhóm ký tự nằm trong ô C1:

 

Lưu ý: Bảng dữ liệu (table_array) chính là Table 7 trong hình trên, vùng dữ liệu này đã được đặt tên theo cách làm ở mục trên (hãy đọc lại nếu chưa biết làm nhé).

Giá trị tìm kiếm chính xác và tương đối trong

hàm VLOOKUP

Như phân tích ở Phần 1 của bài viết, yếu tố tùy chọn cuối cùng là range_lookup. Bạn có thể nhận được kết quả khác nhau tùy vào lựa chọn nhập TRUE hay FALSE.

Đầu tiên, hãy xem ý nghĩa của chúng nhé:

  1. Nếu range_lookup là FALSE, thì công thức sẽ tìm giá trị chính xác. Ví dụ, công thức sẽ tìm chính xác giá trị cần tìm khi bạn nhập giá trị đó làm thamsố thứ nhất (lookup_value).

Nếu có 2 hay nhiều hơn 2 giá trị từ cột đầu tiên của bảng dữ liệu (table_array) khớp với giá trị cần tìm, thì kết quả tìm thấy đầu tiên sẽ được trả về.

Nếu không thể tìm thấy giá trị tìm kiếm chính xác, thì lỗi #N/A sẽ được trả về.

Ví dụ, nếu bạn sử dụng công thức =VLOOKUP(4, A2:B15,2,FALSE), nhưng dữ liệu của bạn không chứa giá trị 4 từ ô A2 đến cột A15, thì công thức sẽ trả về lỗi #N/A.

2. Nếu range_lookupTRUE hay bỏ trống, thì công thức sẽ tìm kết quả tương đối. Nói cách khác, công thức của bạn sẽ tìm kiếm kết quả tuyệt đối đầu tiên và nếu không được tìm thấy, thì nó sẽ trả về kết quả tương đối. Giá trị tìm kiếm tương đối là giá trị lớn nhất tiếp theo, chỉ nhỏ hơn lookup_value.

Nếu bạn nhập TRUE hay bỏ trống tham số này, thì các giá trị ở cột đầu tiên của bảng dữ liệu cần tìm phải được sắp xếp theo thứ tự TĂNG DẦN, đó là từ nhỏ nhất đến lớn nhất. Nếu không thì, hàm VLOOKUP trong Excel có thể sẽ không tìm được giá trị chính xác.

Hãy xem các ví dụ dưới đây để hiểu rõ hơn nhé.

Ví dụ 1: Dùng VLOOKUP với kết quả tìm kiếm tuyệt đối

Để phục vụ mục đích này, bạn cần đặt FALSE làm câu lệnh cuối cùng trong công thức.

Thử làm với bảng “Animal speed” để làm ví dụ đầu tiên và tìm ra loài vật nào có thể chạy 50mph xem nhé. Bạn có công thức dưới đây:

=VLOOKUP(50, $A$2:$B$15, 2, FALSE)


Lưu ý: Bảng dữ liệu chứa 2 ô có giá trị 50 là A5 và A6 nhưng công thức chỉ cho kết quả là ô A5? Vì sao? Vì công thức sẽ trả kết quả tuyệt đối chính là đầu tiên tìm thấy, khớp với giá trị cần tìm.

Ví dụ 2: Dùng

hàm VLOOKUP

với kết quả tìm kiếm tương đối

Điều đầu tiên bạn cần làm đó là lọc cột đầu tiên trong bảng dữ liệu tham chiếu theo thứ tự tăng dần. Sau đó, bạn cần nhập TRUE hoặc bỏ qua tham số cuối cùng trong công thức hàm. Lý do của việc này đã được Gitiho giải thích cặn kẽ ở phần lý thuyết của mục này.
Bây giờ, bạn biết phải viết công thức thế nào chứ?
=VLOOKUP(69, $A$2:$B$15, 2, TRUE)
hay
=VLOOKUP(69,$A$2:$B$15,2)

Cả 2 công thức đều đúng. 

Dưới đây là 1 ví dụ minh họa với yêu cầu ”Tìm loài vật có tốc độ chạy gần 60mph nhất“, và ta có kết quả như sau:

Ở ví dụ trên, có lẽ bạn sẽ thắc mắc, 70 gần với 69 hơn, tại sao kết quả lại trả về “Antelope” với tốc độ 61?, Bởi vì với

hàm VLOOKUP

trả về giá trị tương đối (mà không phải tuyệt đối) kết quả hiển thị sẽ trả về giá trị lớn nhất tiếp theo, và giá trị này nhỏ hơn giá trị cần tìm. 

Kết luận: Những điều cần ghi nhớ!

  1. Hàm

    VLOOKUP

    không thể tìm ở bên trái. Nó luôn luôn tìm giá trị nằm trong cột cận trái của bảng dữ liệu cần tìm (table_array).

  2. Trong công thức

    VLOOKUP

    , tất cả giá trị đều không phân biệt dạng chữ, có nghĩa là ký tự viết HOA và viết thường đều được xử lý như nhau.

  3. Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của dải ô cần tìm, thì hàm

    VLOOKUP

    sẽ trả về lỗi #N/A.

  4. Nếu câu lệnh thứ ba (col_index_num) nhỏ hơn 1, thì công thức

    VLOOKUP

    sẽ trả về lỗi #VALUE!. Trong trường hợp, nó lớn hơn số cột trong dải ô cần tìm (table_array), thì công thức sẽ trả về lỗi #REF!.

  5. Hãy sử dụng tham chiếu ô tuyệt đối trong tham số table_array của công thức

    VLOOKUP

    để có đúng dải ô cần tìm khi xử lý công thức. Hãy cân nhắc việc sử dụng tên cho vùng dữ liệu hay cho bảng trong Excel như một phương án thay thế.

  6. Khi tìm kiếm kết quả tương đối (range_lookup được cải đặt thành TRUE hay bỏ trống), hãy luôn lọc dữ liệu ở cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.
  7. Và cuối cùng, hãy nhớ tầm quan trọng của tham số cuối cùng (thứ 4). Sử dụng TRUE hay FALSE khi thích hợp và bạn sẽ không phải đau đầu nữa.

Hy vọng qua bài viết trên, bạn đọc đã nắm kỹ các thông tin và ứng dụng của

hàm VLOOKUP

để tra cứu và tìm kiếm trong Excel. Hãy luyện tập thêm các

bài tập Excel kèm đáp án

mà Gitiho đã thiết kế nhắm giúp bạn đọc thành thạo các kĩ năng liên quan đến

hàm VLOOKUP

này.

Chúc bạn học tốt! Đừng quên rằng chúng mình có những khóa học vô cùng hiệu quả và bổ ích về tin học văn phòng nhé.







Gitiho.com

 – Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm

Với sứ mệnh: “Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển 

Gitiho.com

 đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Gitiho tự hào khi được đồng hành cùng:
  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
  • 100.000+ học viên trên khắp Việt Nam

Nếu bạn quan tâm đến các khóa tin học văn phòng của Gitiho, với mong muốn giỏi tin học để tăng năng suất làm việc lên gấp 5 lần, hãy truy cập ngay

 

TẠI ĐÂY

 để

 tìm cho mình khóa học phù hợp nhé!




Tin học văn phòng

Excel Cơ Bản

Bài tập Excel

Excel Nâng Cao

Thủ thuật Excel

0 câu trả lời
953 lượt xem
vỗ tay

0 Bình luận

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