excel online - excel vlookup



truy Lượt Xem:530

Cách sử dụng VLOOKUP trong Excel

Khi năm học gần đến, tôi có thể dựa vào một số câu hỏi về bảng tính. Một câu hỏi Excel phổ biến là, "làm thế nào để bạn lấy một giá trị từ một ô Excel và sử dụng nó ở đâu đó trong Excel?" Trong hướng dẫn này, tôi sẽ hướng dẫn bạn cách hàm Excel VLOOKUP đã giúp tôi với một số phân tích dữ liệu. Bài viết này bao gồm phần Tài nguyên bổ sung ở phần cuối bao gồm một video hướng dẫn bằng video và các ví dụ về VLOOKUP có thể tải xuống.
 
- Nếu bạn đang sử dụng Google Trang tính, vui lòng xem Sử dụng Google Trang tính & VLOOKUP .
 
Khi nói đến việc học các hàm của Microsoft Excel, tôi muốn bắt đầu với ví dụ đơn giản. Hướng dẫn này sẽ cung cấp hai kịch bản sử dụng các đối số và giá trị tra cứu khác nhau.

Định nghĩa VLOOKUP

VLOOKUP là một hàm Excel cho phép bạn tìm kiếm và truy xuất nội dung của ô từ một cột khác. Như bạn có thể đoán, "V" là viết tắt của dọc và dựa vào tra cứu dữ liệu từ cột ngoài cùng bên trái của bảng tra cứu .
 
Cột này có thể nằm trên trang tính bạn đang sử dụng hoặc cột khác. Hàm này yêu cầu một trường hoặc khóa chung và bốn đối số . Chức năng cho phép bạn chỉ định có nên sử dụng đối sánh chính xác hoặc đối sánh gần đúng hay không . Một ví dụ phổ biến có thể là tra cứu tên của một sản phẩm từ SKU của nó.
 
Như chúng ta sẽ thấy ngay, Excel cung cấp gợi ý về các đối số nào được yêu cầu.
 
Ví dụ 1 - Một bảng tính và đối sánh gần đúng
 
Trong những năm qua, tôi đã tình nguyện làm việc trong các cuộc bầu cử địa phương. Phần tôi có xu hướng được chỉ định là phân tích dữ liệu về tệp đăng ký cử tri. Đây có xu hướng là các tệp lớn với nhiều thông tin.
 
Một phần tử dữ liệu là ngày sinh của cử tri. Dữ liệu trang tính được cắt lát, thái hạt lựu và được định vị lại. Tuy nhiên, tôi không muốn ngày sinh của cử tri hiển thị trên bất kỳ tệp được phân phối nào.

 

Thay vào đó, tôi quyết định tạo phân đoạn dựa trên độ tuổi. Tôi đã sử dụng tra cứu dọc sẽ trả về giá trị cho ô mong muốn. (Phải, một số người vẫn còn giận tôi vì không tiết lộ tuổi của mọi người.)

Sử dụng VLOOKUP để xác định Phân đoạn

Hiểu hàm VLOOKUP

Hãy bắt đầu với cử tri hư cấu đầu tiên, Sophia Collins. Nếu bạn quét qua cột D, bạn sẽ thấy cô ấy 39 tuổi và trong phân đoạn "Người trưởng thành". Giá trị của "Người trưởng thành" trong Cột E đã được tự động kéo bằng cách sử dụng hàm VLOOKUP của Excel.
 
Bảng nhỏ bên phải với tiêu đề màu xanh là bảng tra cứu . Một số người cũng tham khảo bảng tra cứu này như một mảng bảng . Đây là nơi tôi đã định nghĩa 4 phân đoạn tuổi của mình.
 
Cách phân đoạn hoạt động là nếu tuổi của cử tri dưới 21 tuổi, chúng là "Mới". Từ 21-38, họ là "Trẻ". Từ 39-59, họ là "Người trưởng thành" và nếu họ từ 60 tuổi trở lên, họ là "Cao cấp".
 
Trong trường hợp của Sophia, Excel sẽ lấy 39 tuổi của cô ấy từ ô D2 và tìm đối sánh gần nhất từ ​​Cột H. Cả hai cột này đều chứa dữ liệu về tuổi, đó là khóa chung của chúng tôi .
 
Khi Excel tìm thấy một kết quả phù hợp, nó sẽ đi đến Cột I và lấy Nhãn. Giá trị đó sau đó được sao chép vào ô E2, Phân đoạn. Nó có thể giúp suy nghĩ của cột như là dọc. Xét cho cùng, đây là tra cứu dọc.
 
Bạn có thể nhận thấy rằng bảng tra cứu không liệt kê mọi độ tuổi. Nó không phải vì tôi đang sử dụng một trận đấu gần đúng . Tôi đang nói với Excel để tìm cho tôi độ tuổi gần nhất.
 
Ví dụ, cử tri tiếp theo Evelyn Bennett là 51 nhưng không có giá trị cho 51 trong Cột H. Trong trường hợp này, 51 rơi giữa 39 và 59 vì vậy cô cũng được dán nhãn "Trưởng thành".
 
Như bạn có thể đoán, để sử dụng hàm VLOOKUP cần có một khóa chung . Trong trường hợp này, đó là tuổi tác. Cả hai cột D và H đều chứa các độ tuổi. Tiêu đề và nội dung ô có thể khác nhau 

Phá vỡ chức năng

Hãy loại bỏ một số bí ẩn và hiển thị cách VLOOKUP hiển thị trong thanh công thức. Trong hình minh họa này, tôi đã nhấp vào ô D2.

Hiển thị VLOOKUP trong thanh công thức

 
[A] - Điều này đại diện cho công thức VLOOKUP cho ô D2 
 
[B] - D2 là đối số đầu tiên của chúng tôi được gọi là Lookup_value . 
 
[C] - $ H2 $ 2: $ I $ 5 là Table_array của chúng tôi và đối số thứ hai. 
 
[D] - 2 là Col_index_num từ Table_array của bạn và đối số thứ ba. 
 
[E] - TRUE là Range_lookup và đối số thứ tư.

Hiểu các đối số VLOOKUP

Thuật ngữ "đối số" không phức tạp như âm thanh. Nếu bạn đã quen thuộc với thanh công thức Excel, một đối số là những gì diễn ra giữa các dấu ngoặc đơn () . Nó cung cấp một số loại giá trị đầu vào cho một hàm Excel.
Một số hàm có các đối số bắt buộc và các hàm khác không cần đối số. Ví dụ: để tính toán tuổi của cử tri, tôi cũng đã sử dụng hàm TODAY = TODAY () không sử dụng bất kỳ đối số nào. Một số ví dụ đối số phổ biến bao gồm:
 
phạm vi ô
giá trị logic đúng / sai
con số
Sử dụng công thức từ ô D2, đây là cách các đối số này hoạt động.
 
1. Lookup_value - Hãy nghĩ trường này là điểm bắt đầu của bạn. Trong ví dụ này, tôi muốn tìm kiếm tuổi của Sophia từ ô D2.
 
2. Table_array - Đây là phạm vi ô cho bảng tra cứu của bạn. Việc tìm kiếm phạm vi này có thể nằm trên trang tính hiện có của bạn hoặc một trang tính khác.

Bảng Array Quy tắc & Caveats

Có một số quy tắc cần nhớ về mảng bảng này.
 
Quy tắc 1 - Cột bên trái phải chứa các giá trị đang được tham chiếu . Trái nhất không có nghĩa là nó phải ở trong Cột A. Nó chỉ là cột ngoài cùng bên trái trên mảng. Ví dụ, trên mảng bảng ở trên, cột ngoài cùng bên trái là H.
 
Quy tắc 2 - Bạn không thể có giá trị trùng lặp trong cột ngoài cùng bên trái của phạm vi tìm kiếm. Tôi không thể có hai mục nhập có giá trị “39” với một mục là “Người lớn” và một “39” khác cho “Go Getter”. Excel sẽ phàn nàn.
 
Quy tắc 3 - Khi đề cập đến bảng tra cứu, bạn muốn tham chiếu ô tuyệt đối khi bạn sao chép công thức VLOOKUP sang các ô khác .
Ví dụ: nếu tôi muốn sử dụng cùng một công thức trong các ô từ E3 đến E11, tôi không muốn tham chiếu ô tra cứu của mình thay đổi mỗi lần tôi chuyển xuống ô tiếp theo. Tôi cần tham chiếu ô là hằng số. Đây được gọi là tham chiếu ô tuyệt đối .
 
Sau khi bạn xác định phạm vi tra cứu của các ô, bạn có thể nhấn F4 . Điều này sẽ chu kỳ thông qua các tham chiếu ô tuyệt đối và tương đối. Bạn muốn chọn tùy chọn bao gồm $ trước cột và hàng của bạn. Bạn có thể giải quyết vấn đề này nếu bạn biết cách sử dụng các phạm vi tên Excel .
 
3. Col_index_num - Đây là số cột trong bảng tra cứu của bạn có thông tin bạn cần. Trong ví dụ của chúng tôi, chúng tôi muốn cột 2 từ cột Nhãn. Tên này sẽ trở thành tên Phân đoạn của chúng tôi.
 
Khi chúng tôi đếm, chúng tôi đang đếm các cột trên bảng tra cứu . Mặc dù cột “Nhãn” là Cột I hoặc cột thứ 9, đó là cột thứ 2 trên bảng tra cứu. Một số người gọi đây là chỉ mục cột .
 
4. Tra cứu phạm vi - trường này xác định mức độ phù hợp giữa giá trị Lookup_value (D2) của bạn và giá trị ở cột ngoài cùng bên trái trên bảng tra cứu của chúng tôi. Trong trường hợp của chúng tôi, chúng tôi muốn có một kết quả gần đúng, vì vậy chúng tôi sẽ sử dụng "TRUE".

Sử dụng VLOOKUP trong Công thức của chúng tôi

Thêm vào cột nơi bạn sẽ nhập công thức. Trong trường hợp của tôi, tôi đã thêm Cột E - Phân đoạn.
 
Nhấp vào ô E2.
 
Nhấp vào tab Công thức của bạn từ trình đơn trên cùng.
 
Nhấp vào nút Chèn Hàm .
 
Từ hộp thoại Chèn Hàm , nhập “vlookup” vào hộp văn bản Tìm kiếm hàm . Bạn cũng có thể chọn nó từ danh mục tra cứu & tham khảo .
Nhấn vào Go
 
 

Tìm hàm VLOOKUP

 
Nhấp vào OK .
 
Các Function Arguments thoại sẽ xuất hiện với hộp văn bản cho các đối số cần thiết.
 
 

VLOOKUP Đối số bắt buộc

Trong loại giá trị Lookup_value D2 . Hoặc, bạn có thể nhấp vào ô.
 
Trong loại Table_array $ H $ 2: $ I $ 5 . Lưu ý các dấu $.
 
Trong Col_index_num loại 2 .
 
Trong kiểu Range_lookup đúng .
 
Hộp thoại Đối số hàm của bạn sẽ trông giống như sau. Lưu ý ở phía dưới bên trái, bạn có thể thấy kết quả Công thức .

 


Các đối số VLOOKUP đã hoàn thành

 
Nhấp vào OK . Bây giờ bạn sẽ thấy "Người trưởng thành" trong ô E2.
 
Nhấp vào ô E2.
 
Nhấp vào hình vuông nhỏ màu xanh lá cây (fill handle) ở góc dưới bên phải của ô để sao chép công thức VLOOKUP xuống cột.
 
 
Xác định giá trị đối số
 
Sau khi bạn bấm OK , hộp thoại Đối số Hàm của Excel sẽ xuất hiện và cho phép bạn xác định bốn giá trị. Bạn sẽ thấy rằng ô khởi đầu của bạn và thanh công thức hiển thị phần bắt đầu của hàm = VLOOKUP () . Hộp thoại Hàm đối số thêm các phần tử dữ liệu cần thiết sẽ hiển thị giữa ().
 
Vì mục đích minh họa, tôi đã chồng lên trang tính Mã bên trên để hiển thị các mối quan hệ.
 
 

Lập bản đồ các đối số hàm VLOOKUP

 
Sau khi nhập các đối số bắt buộc, hộp thoại của tôi trông giống như ví dụ bên dưới.
 
 
Giá trị được ánh xạ từ cả hai bảng tính
 
Bạn có thể thấy trong thanh công thức vạch ra màu đỏ ở trên, bây giờ tôi có thêm thông tin dựa trên các mục nhập của tôi trong hộp thoại Hàm đối số. Bạn cũng có thể lưu ý rằng khi tôi nhấp vào trang tính Bảng bên để thêm vào Table_array của mình, Excel đã thêm trước tên tab trước phạm vi ô. Tuy nhiên, tôi cần phải quay lại và nhập vào các ký hiệu $.
 
Mục quan tâm khác là khi bạn xây dựng các hàm này, Excel sẽ hiển thị kết quả trong dòng kết quả Công thức = văn bản. Đây là phản hồi tuyệt vời có thể hiển thị nếu chức năng của bạn nằm trên mục tiêu. Trong ví dụ của chúng tôi, chúng ta có thể thấy Excel tra cứu mã số “A” và trả lại Đảng chính trị “Dân chủ”.
 
VLOOKUP là một hàm Excel mạnh mẽ có thể tận dụng dữ liệu bảng tính từ các nguồn khác. Có nhiều cách bạn có thể hưởng lợi từ chức năng này. Trong ví dụ này, tôi đã sử dụng bản dịch mã 1: 1, nhưng bạn cũng có thể sử dụng nó cho các bài tập nhóm. Ví dụ, bạn có thể gán các mã trạng thái cho một vùng như CT, VT và MA tới một vùng được gọi là “New England”.
 
Nếu bạn đang cố gắng tìm kiếm ngang, bạn sẽ rất vui khi biết rằng Excel có hàm HLOOKUP. Tôi chưa thực hiện hướng dẫn HLOOKUP. Nếu điều này làm bạn quan tâm, hãy cho tôi biết.

Mời bạn xem thêm: Vlookup Offset trong excel  &&  tạo Vlookup sang bên trái


Tags:
Bình luận

Bình luận

Các bài viết mới

Các tin cũ hơn