VLOOKUP là một trong những chức năng hữu ích nhất của Excel, và nó cũng là một trong những chức năng ít được hiểu nhất. Trong bài viết này, chúng tôi làm sáng tỏ VLOOKUP bằng một ví dụ thực tế. Chúng ta sẽ tạo ra một Mẫu hóa đơn cho một công ty hư cấu.
VLOOKUP là một Excel chức năng. Bài viết này sẽ giả định rằng người đọc đã có một sự hiểu biết đi qua về các hàm Excel và có thể sử dụng các hàm cơ bản như SUM, AVERAGE và TODAY. Trong cách sử dụng phổ biến nhất, VLOOKUP là một cơ sở dữ liệu, nghĩa là nó hoạt động với các bảng cơ sở dữ liệu - hoặc đơn giản hơn, danh sách của những thứ trong một bảng tính Excel. Loại gì? Tốt, bất kì loại điều. Bạn có thể có một bảng tính có chứa một danh sách các nhân viên, hoặc sản phẩm, hoặc khách hàng, hoặc đĩa CD trong bộ sưu tập đĩa CD của bạn, hoặc các ngôi sao trên bầu trời đêm. Nó không thực sự quan trọng.
Dưới đây là ví dụ về danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, đó là danh sách các sản phẩm mà công ty hư cấu của chúng tôi bán:
Thông thường các danh sách như thế này có một số loại định danh duy nhất cho mỗi mục trong danh sách. Trong trường hợp này, số nhận dạng duy nhất nằm trong cột “Mã mục”. Ghi chú: Đối với hàm VLOOKUP để làm việc với một cơ sở dữ liệu / danh sách, danh sách đó phải có một cột chứa mã định danh duy nhất (hoặc “khóa”, hoặc “ID”), và cột đó phải là cột đầu tiên trong bảng. Cơ sở dữ liệu mẫu của chúng tôi ở trên đáp ứng tiêu chí này.
Phần khó nhất của việc sử dụng VLOOKUP là hiểu chính xác nó là gì. Vì vậy, hãy xem liệu chúng ta có thể hiểu điều đó rõ ràng trước không:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Trong ví dụ trên, bạn sẽ chèn hàm VLOOKUP vào một bảng tính khác với mã mặt hàng và nó sẽ trả lại cho bạn mô tả của mục tương ứng, giá của nó hoặc tính sẵn có của nó (số lượng "Còn hàng") như được mô tả trong bản gốc của bạn danh sách. Những thông tin nào trong số những thông tin này sẽ truyền lại cho bạn? Vâng, bạn có thể quyết định điều này khi bạn đang tạo công thức.
Nếu tất cả những gì bạn cần là một mẩu thông tin từ cơ sở dữ liệu, sẽ rất khó khăn để xây dựng một công thức có hàm VLOOKUP trong đó. Thông thường, bạn sẽ sử dụng loại chức năng này trong bảng tính có thể tái sử dụng, chẳng hạn như mẫu. Mỗi lần ai đó nhập mã mặt hàng hợp lệ, hệ thống sẽ truy xuất tất cả thông tin cần thiết về mục tương ứng.
Hãy tạo một ví dụ về điều này: An Mẫu hóa đơn chúng ta có thể tái sử dụng nhiều lần trong công ty hư cấu của mình.
Đầu tiên chúng ta bắt đầu Excel, và chúng ta tạo ra một hóa đơn trống:
Với mục đích giữ ví dụ này đơn giản, chúng tôi sẽ định vị cơ sở dữ liệu sản phẩm trên một trang tính riêng trong cùng một sổ làm việc:
Vì vậy, chúng tôi đã tạo cơ sở dữ liệu sản phẩm của mình, trông giống như sau:
Chúng ta cần xác định danh sách tất cả các hàm sẵn có mà Excel cung cấp, để chúng ta có thể chọn VLOOKUP và nhận được một số trợ giúp trong việc hoàn thành công thức. Điều này được tìm thấy bằng cách nhấp vào Công thức và sau đó nhấp vào Chèn chức năng:
Để tìm thứ chúng tôi đang tìm kiếm, chúng tôi có thể nhập cụm từ tìm kiếm như "tra cứu" (vì chức năng mà chúng tôi quan tâm là tra cứu chức năng). Hệ thống sẽ trả về cho chúng tôi danh sách tất cả các hàm liên quan đến tra cứu trong Excel. VLOOKUP là thứ hai trong danh sách. Chọn một nhấp chuột được.
Các Đối số chức năng hộp xuất hiện, nhắc chúng tôi cho tất cả lập luận (hoặc là thông số) cần thiết để hoàn thành hàm VLOOKUP. Bạn có thể nghĩ hộp này là hàm yêu cầu chúng tôi các câu hỏi sau:
- Bạn đang tìm kiếm mã định danh duy nhất nào trong cơ sở dữ liệu?
- Cơ sở dữ liệu ở đâu?
- Phần thông tin nào từ cơ sở dữ liệu, được liên kết với số nhận dạng duy nhất, bạn có muốn lấy lại cho bạn không?
Ba đối số đầu tiên được hiển thị in đậm, cho biết rằng họ bắt buộc các đối số (hàm VLOOKUP không đầy đủ nếu không có chúng và sẽ không trả lại một giá trị hợp lệ). Đối số thứ tư không được in đậm, có nghĩa là không bắt buộc:
Đối số đầu tiên chúng ta cần hoàn thành là Lookup_value tranh luận. Hàm cần chúng ta nói cho nó biết nơi tìm mã định danh duy nhất ( mã hàng trong trường hợp này) rằng nó phải trả về mô tả. Chúng ta phải chọn mã mục chúng ta đã nhập trước đó (trong A11).
Nhấp vào biểu tượng chọn ở bên phải của đối số đầu tiên:
Sau đó nhấp một lần vào ô chứa mã mục (A11) và nhấn Đi vào:
Bây giờ chúng ta cần nhập một giá trị cho Table_array tranh luận. Nói cách khác, chúng ta cần nói cho VLOOKUP biết nơi tìm cơ sở dữ liệu / danh sách. Nhấp vào biểu tượng chọn bên cạnh đối số thứ hai:
…và hãy nhấn Đi vào. Phạm vi ô đại diện cho cơ sở dữ liệu (trong trường hợp này là "Cơ sở dữ liệu sản phẩm"! A2: D7 ") được nhập tự động cho chúng ta vào đối số thứ hai.
Bây giờ chúng ta cần nhập đối số thứ ba, Col_index_num. Chúng tôi sử dụng đối số này để chỉ định VLOOKUP có phần thông tin nào từ cơ sở dữ liệu, liên kết với mã mục của chúng tôi trong A11, chúng tôi muốn trả lại cho chúng tôi. Trong ví dụ cụ thể này, chúng tôi muốn có mục sự miêu tả trả lại cho chúng tôi. Nếu bạn nhìn vào trang tính cơ sở dữ liệu, bạn sẽ thấy cột "Mô tả" là thứ hai trong cơ sở dữ liệu. Điều này có nghĩa là chúng ta phải nhập giá trị “2” vào Col_index_num cái hộp:
Điều quan trọng cần lưu ý là chúng tôi không nhập “2” ở đây vì cột “Mô tả” nằm trong B trên trang tính đó. Nếu cơ sở dữ liệu xảy ra để bắt đầu trong cột K của bảng tính, chúng tôi vẫn sẽ nhập “2” trong trường này vì cột “Mô tả” là cột thứ hai trong tập hợp các ô mà chúng tôi đã chọn khi chỉ định “Table_array”.
Cuối cùng, chúng ta cần quyết định có nên nhập giá trị vào đối số VLOOKUP cuối cùng hay không, Range_lookup. Đối số này yêu cầu hoặc là thật hoặc là sai giá trị, hoặc nó phải để trống. Khi sử dụng VLOOKUP với các cơ sở dữ liệu (đúng là 90% thời gian), cách quyết định những gì cần đưa vào đối số này có thể được nghĩ như sau:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Như cột đầu tiên của cơ sở dữ liệu của chúng tôi là không phải sắp xếp, chúng tôi nhập sai vào đối số này:
Đó là nó! Chúng tôi đã nhập tất cả thông tin cần thiết cho VLOOKUP để trả lại giá trị chúng tôi cần. Nhấn vào được và thông báo rằng mô tả tương ứng với mã mục “R99245” đã được nhập chính xác vào ô B11:
Nếu chúng ta nhập khác nhau mã mục vào ô A11, chúng ta sẽ bắt đầu thấy sức mạnh của hàm VLOOKUP: Ô mô tả thay đổi để khớp với mã mục mới:
Chúng tôi có thể thực hiện một loạt các bước tương tự để có được mục giá bán trở lại vào ô E11. Lưu ý rằng công thức mới phải được tạo trong ô E11. Kết quả sẽ trông như thế này:
Lưu ý rằng sự khác biệt duy nhất giữa hai công thức là đối số thứ ba (Col_index_num) đã thay đổi từ “2” thành “3” (vì chúng tôi muốn dữ liệu được lấy từ cột thứ 3 trong cơ sở dữ liệu).
Nếu chúng tôi quyết định mua 2 trong số các mặt hàng này, chúng tôi sẽ nhập "2" vào ô D11. Sau đó chúng ta sẽ nhập một công thức đơn giản vào ô F11 để lấy tổng số dòng:
=D11*E11
… Trông như thế này…
Hoàn thành mẫu hóa đơn
Chúng tôi đã học được rất nhiều về VLOOKUP cho đến nay. Trên thực tế, chúng tôi đã học được tất cả những gì chúng ta sẽ học trong bài viết này. Điều quan trọng cần lưu ý là VLOOKUP có thể được sử dụng trong các trường hợp khác ngoài cơ sở dữ liệu. Điều này ít phổ biến hơn và có thể được đề cập trong các bài viết How-To Geek trong tương lai.
Mẫu hóa đơn của chúng tôi chưa hoàn thành. Để hoàn thành nó, chúng tôi sẽ làm như sau:
-
Chúng tôi sẽ loại bỏ mã mục mẫu khỏi ô A11 và “2” khỏi ô D11. Điều này sẽ làm cho các công thức VLOOKUP mới được tạo của chúng tôi hiển thị thông báo lỗi:
Image Chúng tôi có thể khắc phục điều này bằng cách sử dụng Excel một cách thận trọng NẾU() và ISBLANK () chức năng. Chúng tôi thay đổi công thức của chúng tôi từ này… = VLOOKUP (A11, 'Cơ sở dữ liệu sản phẩm'! A2: D7,2, FALSE) … cho điều này … = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Cơ sở dữ liệu sản phẩm'! A2: D7,2, FALSE))
- Chúng tôi sẽ sao chép các công thức trong các ô B11, E11 và F11 xuống phần còn lại của các hàng của hóa đơn. Lưu ý rằng nếu chúng ta làm điều này, các công thức kết quả sẽ không còn tham chiếu chính xác đến bảng cơ sở dữ liệu nữa. Chúng tôi có thể sửa lỗi này bằng cách thay đổi tham chiếu ô cho cơ sở dữ liệu thành tuyệt đối tham chiếu ô. Ngoài ra - và thậm chí tốt hơn - chúng tôi có thể tạo tên dải ô cho toàn bộ cơ sở dữ liệu sản phẩm (chẳng hạn như "Sản phẩm") và sử dụng tên dải ô này thay vì tham chiếu ô. Công thức sẽ thay đổi từ… = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Cơ sở dữ liệu sản phẩm'! A2: D7,2, FALSE)) … cho điều này … = IF (ISBLANK (A11),””, VLOOKUP (A11, Sản phẩm, 2, FALSE)) … và sau đó sao chép các công thức xuống phần còn lại của hàng hóa đơn.
- Chúng tôi có thể sẽ “khóa” các ô có chứa các công thức của chúng tôi (hoặc đúng hơn là mở khóa các khác tế bào), và sau đó bảo vệ bảng tính, để đảm bảo rằng các công thức được xây dựng cẩn thận của chúng tôi không vô tình bị ghi đè khi ai đó đến để điền vào hóa đơn.
- Chúng tôi sẽ lưu tệp dưới dạng bản mẫuđể mọi người có thể sử dụng lại trong công ty của chúng tôi
Nếu chúng ta cảm thấy có thật không thông minh, chúng tôi sẽ tạo cơ sở dữ liệu của tất cả khách hàng trong một trang tính khác, sau đó sử dụng ID khách hàng được nhập vào ô F5 để tự động điền tên và địa chỉ của khách hàng vào ô B6, B7 và B8.
