Ứng dụng và định nghĩa của hàm Excel này
VLOOKUP là một hàm Excel mà người dùng có thể tìm kiếm và đánh giá nội dung bảng. Chức năng này có sẵn trong các phiên bản từ Excel 2007 dành cho Windows và Mac.
VLOOKUP là gì?
Các cách sử dụng có thể có của hàm VLOOKUP sẽ được giải thích ở đây bằng cách sử dụng một ví dụ: Trong ví dụ này, bạn là một người rất yêu thích văn học và do đó đã tạo bảng tính Excel của riêng mình, trong đó bạn có thể sắp xếp cẩn thận các sách bạn đã thu thập. Mỗi tác phẩm được nhập thông tin về các loại sau:
-
tác giả
-
chức vụ
-
Số trang
-
Năm xuất bản
Bây giờ bạn muốn cung cấp cho một người bạn một mẹo về cuốn sách để mang theo trong cuộc họp tiếp theo. Thật không may, bạn chỉ có thể nghĩ về tác giả, không phải tên của cuốn sách. Đây là lúc VLOOKUP phát huy tác dụng, vì nó có thể sử dụng giá trị đầu vào này để đưa ra thông tin bạn đang tìm kiếm trong một lần lặp lại.
VLOOKUP được sử dụng như thế nào?
Trước khi nghĩ đến việc xây dựng công thức, cần xác định vị trí của trường đầu vào và các trường đầu ra khác nhau sau này. Để làm điều này, điều hợp lý là tạo một bảng riêng ban đầu trống và do đó cho phép không gian cho thông tin được đề cập. Nếu bạn thiết kế bảng mới này dựa trên ví dụ của bảng hiện có, bạn sẽ có lợi thế tiết kiệm thời gian sau này.
Trên cơ sở này, công thức VLOOKUP có thể được tạo theo cách thủ công hoặc được tạo tự động bởi Excel. Đối với người mới bắt đầu, bạn nên sử dụng cách tiếp cận sau để dần dần biết cấu trúc và tác dụng của công thức. Để làm điều này, nút cho "Chèn chức năng" được chọn trên tab "Công thức". VLOOKUP bị ẩn trong cửa sổ mở ra. Sau khi xác nhận, một cửa sổ lại mở ra, trong đó có thể điền bốn thông số của công thức vào. Đó là:
-
Tiêu chí tìm kiếm
-
ma trận
-
Chỉ mục cột
-
Area_reference
Do đó, bản nháp thô của công thức trông giống như sau:
= VLOOKUP (tiêu chí tìm kiếm, ma trận, chỉ mục cột, phạm vi_ liên kết)
và trong một ứng dụng khả thi như thế này:
= VLOOKUP (H3; A3: E40; 5)
Tiêu chí tìm kiếm
Để hàm biết giá trị nào nên được sử dụng làm điểm bắt đầu, dòng được chọn làm trường nhập hai bước trước đó được ghi chú trong trường "Tiêu chí tìm kiếm". Trong ví dụ của chúng tôi, tên của tác giả cuốn sách “Phillip Pulmann” được nhập vào đó. Điều này làm cho công thức linh hoạt và không phải điều chỉnh lại ngay sau khi giá trị đã nhập thay đổi.
ma trận
Trường đầu vào "Ma trận" mô tả bảng trong đó thông tin được xuất ra có thể được tìm thấy. Do đó, ma trận đặc biệt này cũng chứa các cột cho tên sách, số trang và năm xuất bản.
Ma trận được chọn hoàn toàn một lần mà không có các tiêu đề từ trên cùng bên trái đến lề dưới cùng bên phải. Bằng cách này, Excel biết nội dung nào phải được tính đến khi đánh giá.
Chỉ mục cột
Trường đầu vào cho "chỉ mục cột" nhắc người dùng xác định cột của ma trận trong đó chỉ giá trị được tìm kiếm được liệt kê. Việc gán các cột được đánh số thứ tự thời gian. Điều này có nghĩa là cột đầu tiên của bảng nhận giá trị 1, cột thứ hai nhận giá trị 2, v.v. Trong ví dụ của chúng tôi, điều này tương ứng với chỉ mục cột 1 cho tác giả, chỉ mục cột 2 cho tiêu đề, chỉ mục cột 3 cho số trang và chỉ số cột 4 cho năm xuất bản.
Để giữ cho bảng linh hoạt nhất có thể, tiêu đề cột có thể được liên kết thay vì số. Điều này có lợi thế là công thức cũng có thể được chuyển sang các hàng khác mà không gặp bất kỳ vấn đề gì, vì tiêu đề cột có thể được điều chỉnh linh hoạt mỗi lần.
Chú ý: Hàm VLOOKUP đọc ma trận từ trái sang phải, đó là lý do tại sao chỉ mục cột phải được đặt ở bên phải cột cho tiêu chí tìm kiếm để được tính đến bởi hàm!
Area_reference
Tham số "Range_Lookup" hoàn thành công thức VLOOKUP bằng cách chỉ định độ chính xác mà bảng được đánh giá. Tuy nhiên, nó khác với các thành phần đã đề cập trước đó của công thức vì nó là tùy chọn. Nếu giá trị 0 được nhập cho "không chính xác", Excel chỉ tìm kiếm giá trị được chỉ định làm tiêu chí tìm kiếm. Tuy nhiên, với giá trị 1 cho "true", việc tìm kiếm vẫn tiếp tục cho các giá trị hiển nhiên nếu không thể tìm thấy giá trị chính xác.
Việc chỉ định tham số này là tùy chọn vì giá trị 1 được đặt theo mặc định. Cài đặt này sẽ hữu ích sau này trong VLOOKUP nâng cao với nhiều tiêu chí tìm kiếm.
Sự hợp nhất
Ngay sau khi tất cả các tham số cần thiết đã được thiết lập, hàm VLOOKUP có thể được sử dụng. Sau khi nhập tiêu chí tìm kiếm và xác nhận chức năng, giá trị bạn đang tìm sẽ xuất hiện trong dòng đã được xác định là trường đầu ra.
Trong ví dụ của chúng tôi, tên sách “Chiếc la bàn vàng” hiện được hiển thị, tương ứng với tác giả đã nhập. Để nhanh chóng tìm ra số trang và năm xuất bản, không cần làm gì hơn là kéo công thức VLOOKUP hiện có vào các ô tiếp theo. Điều này rất dễ dàng vì chỉ mục cột của hàm VLOOKUP đã được liên kết với tiêu đề cột của bảng đầu tiên và bảng thứ hai cũng được cấu trúc theo cùng một thứ tự.
Trong trường hợp các bảng khác nhau hoặc xảy ra lỗi bất chấp mọi thứ, công thức VLOOKUP cũng có thể được thay đổi theo cách thủ công. Để làm điều này, chữ số áp chót cho chỉ mục cột phải được khớp với cột của giá trị mới được xuất.
VLOOKUP với nhiều tiêu chí tìm kiếm
Thường thì điều đó xảy ra là một tiêu chí tìm kiếm đơn lẻ không đủ để đánh giá chính xác một bảng Excel lớn. Sau đó, chạy hàm VLOOKUP với một số tiêu chí tìm kiếm là hợp lý. Để làm được điều này, công thức hiện có phải được bổ sung bằng một hàm IF bổ sung. Bằng cách này, có thể tính đến tám tiêu chí tìm kiếm khác nhau trong quá trình đăng ký.
VLOOKUP trong nhiều bảng tính Excel
Nếu tiêu chí tìm kiếm không chỉ có thể được tìm thấy trong một bảng mà còn có thể được tìm thấy trong bảng khác, công thức VLOOKUP có thể được điều chỉnh cho phù hợp. Đối với điều này, cả hàm if và hàm ISERROR phải được đặt trước công thức hiện có. Năm tham số được yêu cầu cho việc này:
-
Tiêu chí tìm kiếm
-
Ma trận1 và Ma trận 2
-
Chỉ mục cột1 và chỉ mục cột2
Kết quả trông như thế này:
= IF (ISERROR (VLOOKUP (tìm kiếm-tiêu chí, ma trận1, cột-chỉ mục1, 0));
VLOOKUP (tiêu chí tìm kiếm; ma trận2; chỉ mục cột2,0); VLOOKUP (tiêu chí tìm kiếm; ma trận1; chỉ mục cột1;))
và trong một ứng dụng khả thi như thế này:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Tiêu chí tìm kiếm được sử dụng để chèn giá trị được tìm kiếm trong hai bảng. Matrix1 và Matrix2 xác định các vùng ô tương ứng của hai bảng. Chỉ mục cột1 và chỉ mục cột2 được sử dụng để xác định chi tiết hơn cột nào của các bảng tương ứng cần được tìm kiếm.
Nếu giá trị bạn đang tìm kiếm xuất hiện trong cả hai bảng, Excel sẽ xuất kết quả từ bảng đầu tiên. Tuy nhiên, nếu giá trị không được tìm thấy trong một trong hai bảng, một thông báo lỗi sẽ xuất hiện. Ưu điểm của công thức là hai danh sách không nhất thiết phải có cấu trúc giống nhau hoặc có cùng kích thước.
Gán giá trị cho các danh mục bằng hàm VLOOKUP
Một chức năng bổ sung của VLOOKUP cho phép các giá trị được liệt kê tự động được chia thành các chữ cái và vị ngữ mà bạn chọn. Trong ví dụ trước của chúng tôi, một cột bảng bổ sung sẽ được chèn cho loại sách. Sách có độ dài lên đến 50 trang nên thuộc thể loại truyện ngắn, trong khi sách từ 51 đến 150 trang được xếp vào tiểu thuyết và từ 151 trang cho tiểu thuyết. Để thực hiện điều này, không cần công thức bổ sung nào trong hàm VLOOKUP, chỉ cần sử dụng dấu ngoặc nhọn “{}”. Công thức đã hoàn thành trông như thế này:
= VLOOKUP (B1; {1. "Truyện ngắn"; 51. "Novella"; 151. "Tiểu thuyết"}; 2)
Nội dung của dấu ngoặc nhọn chỉ ra một ma trận xác định khu vực của một loại sách tương ứng. Do đó, việc gán chiều dài cạnh cho chi thích hợp được đặt trong dấu ngoặc nhọn. Công thức sử dụng các cặp giá trị, mỗi cặp cách nhau một điểm. Ma trận {1. "Truyện ngắn"; 51. "Novella"; 151. "Tiểu thuyết"} được đọc như sau:
"Từ 1 hiển thị một câu chuyện ngắn, từ 51 hiển thị một tiểu thuyết, từ 151 hiển thị một tiểu thuyết."
Ma trận này có thể dễ dàng được điều chỉnh cho các nhiệm vụ khác nhau. Điều này một mặt liên quan đến kích thước và số lượng của ma trận cũng như chỉ định của chúng. Vì vậy, có thể xuất ra các chuỗi hoặc số thay vì các chữ cái riêng lẻ. Tất cả những gì bạn phải làm là điều chỉnh các chữ cái trong công thức.
VLOOKUP trên nhiều trang tính
Một chức năng khác của VLOOKUP cho phép người dùng liên kết nội dung nằm trên các bảng tính khác nhau. Đối với ví dụ của chúng tôi, tùy chọn này có thể hữu ích khi lần đầu tiên thông tin được sắp xếp trong các trang tính khác nhau và sau đó được cập nhật trong bảng tóm tắt.
Hãy tưởng tượng rằng, ngoài những cuốn sách của bạn, bạn cũng liệt kê những bộ phim đã sưu tầm của mình trong một bảng tính Excel. Sau đó, bạn kết hợp cả hai bộ sưu tập trong một bảng lớn.
Ưu điểm của thủ tục này không chỉ nằm ở thứ tự tăng lên, mà còn ở việc tránh các sai sót tiềm ẩn. Nếu bạn muốn tạo một mục mới hoặc cập nhật một mục hiện có, bạn không phải tìm kiếm trong bảng lớn mà có thể truy cập những mục nhỏ hơn. Các giá trị sau đó được tự động chuyển sang bảng Excel tóm tắt. Điều này làm cho việc viết lại trong bảng lớn trở nên thừa thãi, điều này tốt nhất là tránh một hành động đáng tiếc và chuỗi các thông báo lỗi tiếp theo.
Công thức trông như thế nào?
Chức năng này có thể hoạt động trở lại bằng cách chèn một công thức khác. Trong khi tìm kiếm với nhiều tiêu chí yêu cầu một công thức IF bổ sung, làm việc với nhiều trang tính yêu cầu một công thức INDIRECT. Điều này cho phép một phạm vi từ bảng tính khác được chỉ định cho ma trận VLOOKUP.
= VLOOKUP (tiêu chí tìm kiếm; INDIRECT (ma trận); chỉ mục cột; phạm vi_ liên kết)
Chú ý: Công thức này sẽ chỉ hoạt động nếu các bảng riêng lẻ trong các trang tính khác nhau có cùng tên với các tiêu đề cột của bảng chung. Toàn bộ bảng có thể được đặt tên trong "Trường tên" ở trên cùng bên trái phía trên lưới ô. Có thể xem các bảng đã được đặt tên bằng tổ hợp phím Ctrl + F3.
Xử lý các thông báo lỗi mới xuất hiện
Làm việc với các bảng Excel được liên kết có thể dẫn đến các sự cố không mong muốn. Điều này đặc biệt bao gồm đầu ra của các giá trị sai. Trong trường hợp đầu ra giá trị 0 sai, có một vấn đề nhỏ trong cài đặt của Excel, có thể được khắc phục nhanh chóng.
Mặt khác, thông báo lỗi phổ biến #NV là một hàm có chủ ý của hàm VLOOKUP, nó chỉ ra cho người dùng biết rằng giá trị bắt buộc không có sẵn. Ghi chú này có thể được thiết kế khác với sự trợ giúp của công thức.
VLOOKUP - tổng quan
VLOOKUP là một hàm Excel hữu ích có thể được sử dụng để tìm kiếm và đánh giá các bảng. Ưu điểm của nó thể hiện rõ ở tính thân thiện và ứng dụng linh hoạt. Bằng cách này, bất kỳ ai thường xuyên làm việc với bảng Excel đều có thể hưởng lợi từ hàm. Có thể là người thu thập tư nhân tạo ra các bảng nhỏ của riêng mình hoặc công ty lớn xử lý các tập dữ liệu quan trọng hơn đáng kể.
Mặt khác, nếu bạn vẫn có các yêu cầu chưa được trả lời mà VLOOKUP không thể đáp ứng, bạn có thể mong đợi một tùy chọn Excel bổ sung: Microsoft đã cung cấp cho người dùng Excel 365 XLOOKUP mới kể từ đầu năm 2022-2023. Điều này được xây dựng dựa trên các năng lực của hàm VLOOKUP và bổ sung chúng bằng các hàm bổ sung, đôi khi thậm chí còn đơn giản hơn. Do đó, một quy trình mới trong đánh giá dữ liệu cũng mở ra vào thời điểm này.
