Cách sử dụng hàm VLOOKUP trong Excel
VLOOKUP là một trong những hàm hữu ích nhất của Excel và nó cũng là một trong những hàm ít được hiểu nhất. Trong bài viết này, chúng tôi làm sáng tỏ hàm VLOOKUP bằng một ví dụ thực tế. Chúng tôi 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ó hiểu biết sơ 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, hàm 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 trang tính Excel. Loại gì? Tốt, bất kì đại loại. Bạn có thể có một trang tính chứa danh sách nhân viên, sản phẩm hoặc khách hàng, hoặc đĩa CD trong bộ sưu tập CD của bạn, hoặc những ngôi sao trên bầu trời đêm. Nó không thực sự quan trọng.
Đây là một 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 mã đị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ặt hàng". Lưu ý: Để hàm VLOOKUP hoạt động với 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 khi sử dụng hàm VLOOKUP là hiểu chính xác hàm này dùng để làm gì. Vì vậy, hãy xem liệu chúng ta có thể làm rõ điều đó trước không:
VLOOKUP lấy thông tin từ cơ sở dữ liệu / danh sách dựa trên một phiên bản được cung cấp của số nhận dạng duy nhất.
Trong ví dụ trên, bạn sẽ chèn hàm VLOOKUP vào một bảng tính khác có mã mặt hàng và nó sẽ trả về cho bạn mô tả của mặt hàng tương ứng, giá cả hoặc tình trạng còn hàng (số lượng "Còn hàng") như được mô tả trong tài liệu gốc của bạn danh sách. Phần thông tin nào trong số những phần này sẽ chuyển lại cho bạn? Chà, bạn phải quyết định điều này khi tạo công thức.
Nếu tất cả những gì bạn cần là một phần thông tin từ cơ sở dữ liệu, thì sẽ rất khó để xây dựng công thức với hàm VLOOKUP trong đó. Thông thường, bạn sẽ sử dụng loại chức năng này trong một bảng tính có thể sử dụng lại, chẳng hạn như một mẫu. Mỗi khi có người nhập mã mặt hàng hợp lệ, hệ thống sẽ lấy ra tất cả các thông tin cần thiết về mặt hàng tương ứng.
Hãy tạo một ví dụ về điều này: Mẫu hóa đơn mà chúng tôi có thể sử dụng lại nhiều lần trong công ty hư cấu của mình.
Đầu tiên, chúng tôi khởi động Excel và chúng tôi tạo cho mình một hóa đơn trống:
Đây là cách hoạt động: Người sử dụng mẫu hóa đơn sẽ điền vào một loạt mã mặt hàng trong cột “A” và hệ thống sẽ truy xuất mô tả và giá của từng mặt hàng từ cơ sở dữ liệu sản phẩm của chúng tôi. Thông tin đó sẽ được sử dụng để tính tổng dòng cho từng mặt hàng (giả sử chúng ta nhập số lượng hợp lệ).
Để giữ cho 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 biệt trong cùng một sổ làm việc:
Trong thực tế, nhiều khả năng cơ sở dữ liệu sản phẩm sẽ được đặt trong một sổ làm việc riêng biệt. Nó tạo ra một chút khác biệt đối với hàm VLOOKUP, hàm này không thực sự quan tâm đến việc cơ sở dữ liệu nằm trên cùng một trang tính, một trang tính khác hay một sổ làm việc hoàn toàn khá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:
Để kiểm tra công thức VLOOKUP mà chúng tôi sắp viết, trước tiên, chúng tôi nhập mã mặt hàng hợp lệ vào ô A11 của hóa đơn trống:
Tiếp theo, chúng ta di chuyển ô hiện hoạt đến ô mà chúng ta muốn thông tin được lấy từ cơ sở dữ liệu bằng hàm VLOOKUP được lưu trữ. Điều thú vị là đây là bước mà hầu hết mọi người đều mắc sai lầm. Để giải thích thêm: Chúng tôi sắp tạo một công thức VLOOKUP sẽ truy xuất mô tả tương ứng với mã mặt hàng trong ô A11. Chúng tôi muốn mô tả này đặt ở đâu khi chúng tôi nhận được nó? Tất nhiên là trong ô B11. Vì vậy, đó là nơi chúng tôi viết công thức VLOOKUP: trong ô B11. Chọn ô B11 ngay bây giờ.
Chúng tôi cần xác định danh sách tất cả các hàm có sẵn mà Excel phải cung cấp, để chúng tôi có thể chọn hàm VLOOKUP và nhận được một số hỗ trợ trong việc hoàn thành công thức. Điều này được tìm thấy bằng cách lần đầu tiên nhấp vào Công thức tab, và sau đó nhấp vào Chèn chức năng:
Một hộp xuất hiện cho phép chúng ta chọn bất kỳ hàm nào có sẵn trong Excel.
Để tìm từ khóa chúng tôi đang tìm kiếm, chúng tôi có thể nhập một cụm từ tìm kiếm như "tra cứu" (bởi vì chức năng 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à cái thứ hai trong danh sách. Chọn nó bằng một cú nhấp chuột đồng ý.
Các Đối số hàm hộp xuất hiện, nhắc chúng tôi về tất cả tranh luận (hoặc là thông số) cần thiết để hoàn thành hàm Vlookup. Bạn có thể coi hộp này như một hàm hỏi chúng tôi những câu hỏi sau:
- Bạn đang tìm số nhận dạng duy nhất nào trong cơ sở dữ liệu?
- Cơ sở dữ liệu ở đâu?
- Bạn muốn truy xuất 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?
Ba đối số đầu tiên được hiển thị in đậm, chỉ ra rằng họ đang bắt buộc đối số (hàm VLOOKUP không hoàn chỉnh nếu không có chúng và sẽ không trả về giá trị hợp lệ). Đối số thứ tư không được in đậm, có nghĩa là nó là tùy chọn:
Chúng tôi sẽ hoàn thành các đối số theo thứ tự, từ trên xuống dưới.
Đố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 tô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) mà nó sẽ trả về mô tả. Chúng ta phải chọn mã hàng đã nhập trước đó (trong A11).
Nhấp vào biểu tượng bộ 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ã hàng (A11), và nhấn Đi vào:
Giá trị của “A11” được chèn vào đối số đầu tiên.
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 cho VLOOKUP biết nơi tìm cơ sở dữ liệu / danh sách. Nhấp vào biểu tượng bộ chọn bên cạnh đối số thứ hai:
Bây giờ định vị cơ sở dữ liệu / danh sách và chọn toàn bộ danh sách - không bao gồm dòng tiêu đề. Trong ví dụ của chúng tôi, cơ sở dữ liệu nằm trên một trang tính riêng biệt, vì vậy, trước tiên chúng tôi nhấp vào tab trang tính đó:
Tiếp theo, chúng tôi chọn toàn bộ cơ sở dữ liệu, không bao gồm dòng tiêu đề:
…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 tôi 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 cho VLOOKUP đoạn thông tin nào từ cơ sở dữ liệu, liên kết với mã hàng 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ặt hàng 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ẽ nhận thấy rằng 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 tình cờ bắt đầu trong cột K của trang tính, chúng tôi vẫn sẽ nhập "2" vào 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 xem có nên nhập một giá trị vào đối số VLOOKUP cuối cùng hay không, Range_lookup. Đối số này yêu cầu một thật hoặc là sai giá trị hoặc nó phải được để trống. Khi sử dụng hàm VLOOKUP với cơ sở dữ liệu (đúng 90% thời gian), cách để quyết định những gì cần đưa vào đối số này có thể được suy nghĩ như sau:
Nếu cột đầu tiên của cơ sở dữ liệu (cột chứa các số nhận dạng duy nhất) được sắp xếp theo thứ tự bảng chữ cái / số theo thứ tự tăng dần, thì bạn có thể nhập giá trị là thật vào đối số này hoặc để trống.
Nếu cột đầu tiên của cơ sở dữ liệu là không phải đã sắp xếp, hoặc sắp xếp theo thứ tự giảm dần, thì bạn phải nhập giá trị của sai vào đối số này
Vì 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 để hàm VLOOKUP trả về giá trị mà chúng tôi cần. Nhấn vào đồng ý và nhận thấy rằng mô tả tương ứng với mã mặt hàng “R99245” đã được nhập chính xác vào ô B11:
Công thức được tạo cho chúng tôi trông giống như sau:
Nếu chúng ta nhập một khác nhau mã hàng 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 để phù hợp với mã hàng 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ặt hàng giá bán trở lại ô E11. Lưu ý rằng công thức mới phải được tạo trong ô E11. Kết quả sẽ như thế này:
… Và công thức sẽ 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 truy xuất 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 tôi sẽ nhập một công thức đơn giản vào ô F11 để nhận tổng số dòng:
= D11 * E1
… Trông như thế này…
Hoàn thành Mẫu hóa đơn
Cho đến nay, chúng tôi đã học được rất nhiều về VLOOKUP. Trên thực tế, chúng tôi đã tìm hiểu tất cả những gì chúng tôi sẽ tìm hiểu trong bài viết này. Điều quan trọng cần lưu ý là hàm 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 Hướng dẫn cách làm trong tương lai.
Mẫu hóa đơn của chúng tôi chưa hoàn chỉnh. Để hoàn thành nó, chúng tôi sẽ làm như sau:
- Chúng tôi sẽ xóa mã mặt hàng 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:
Chúng tôi có thể khắc phục điều này bằng cách sử dụng hợp lý các NẾU() và ISBLANK () chức năng. Chúng tôi thay đổi công thức của mình từ điều này… = VLOOKUP (A11, 'Cơ sở dữ liệu sản phẩm'! A2: D7,2, FALSE)… Đế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 mục 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. Chúng tôi có thể khắc phục điều này bằng cách thay đổi các 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 phạm vi 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 phạm vi này thay vì tham chiếu ô. Công thức sẽ thay đổi từ điều này… = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'Cơ sở dữ liệu sản phẩm'! A2: D7,2, FALSE))… Đế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 các hàng mục hóa đơn.
- Chúng tôi có thể sẽ "khóa" các ô chứa công thức của chúng tôi (hoặc đúng hơn là mở khóa các khác ô), và sau đó bảo vệ trang 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 trong công ty chúng tôi có thể sử dụng lại nó
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 về tất cả khách hàng của mình 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 các ô B6, B7 và B8.
Nếu bạn muốn thực hành với hàm VLOOKUP, hoặc chỉ cần xem Mẫu hóa đơn kết quả của chúng tôi, bạn có thể tải xuống từ đây.
