01/03
Tìm đối sánh gần đúng với dữ liệu bằng VLOOKUP của Excel
Hàm VLOOKUP hoạt động như thế nào
Hàm VLOOKUP của Excel, viết tắt của tra cứu dọc , có thể được sử dụng để tìm kiếm thông tin cụ thể nằm trong bảng dữ liệu hoặc cơ sở dữ liệu.
VLOOKUP thường trả về một trường dữ liệu duy nhất làm đầu ra của nó. Làm thế nào nó là:
- Bạn cung cấp tên hoặc lookup_value cho VLOOKUP biết hàng hoặc bản ghi nào của bảng dữ liệu để tìm kiếm dữ liệu mong muốn
- Bạn cung cấp số cột - được gọi là col_index_num - của dữ liệu bạn tìm kiếm
- Hàm tìm kiếm lookup_value trong cột đầu tiên của bảng dữ liệu
- VLOOKUP sau đó định vị và trả về thông tin bạn tìm kiếm từ một trường khác của cùng một bản ghi bằng cách sử dụng số cột được cung cấp
Sắp xếp dữ liệu trước
Mặc dù không phải lúc nào cũng được yêu cầu, nhưng cách tốt nhất để sắp xếp hàng loạt dữ liệu mà VLOOKUP đang tìm kiếm theo thứ tự tăng dần bằng cách sử dụng cột đầu tiên của phạm vi cho khóa sắp xếp.
Nếu dữ liệu không được sắp xếp, VLOOKUP có thể trả lại kết quả không chính xác.
Cú pháp và đối số của hàm VLOOKUP
Cú pháp của hàm liên quan đến cách bố trí của hàm và bao gồm tên, khung và đối số của hàm .
Cú pháp cho hàm VLOOKUP là:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (bắt buộc) giá trị để tìm kiếm - chẳng hạn như số lượng được bán trong hình trên
table_array - (bắt buộc) đây là bảng dữ liệu mà VLOOKUP tìm kiếm để tìm thông tin bạn đang theo dõi.
- Table_array phải chứa ít nhất hai cột dữ liệu
- Cột đầu tiên thường chứa lookup_value
col_index_num - (bắt buộc) số cột của giá trị bạn muốn tìm thấy.
- Việc đánh số bắt đầu với cột search_key dưới dạng cột 1
- Nếu col_index_num được đặt thành một số lớn hơn số cột được chọn trong đối số table_array thì #REF! lỗi được trả về bởi hàm
range_lookup - (tùy chọn) cho biết có hay không phạm vi được sắp xếp theo thứ tự tăng dần.
- Dữ liệu trong cột đầu tiên được sử dụng làm khóa sắp xếp
- Giá trị Boolean - TRUE hoặc FALSE là các giá trị chỉ chấp nhận được
- Nếu bỏ qua, giá trị được đặt thành TRUE theo mặc định
- Nếu được đặt thành TRUE hoặc bị bỏ qua và cột đầu tiên của dải ô không được sắp xếp theo thứ tự tăng dần, kết quả không chính xác có thể xảy ra
- Nếu được đặt thành TRUE hoặc bỏ qua và đối sánh chính xác cho tìm kiếm _value không được tìm thấy, kết quả phù hợp gần nhất có kích thước hoặc giá trị nhỏ hơn được sử dụng làm search_key
- Nếu được đặt thành FALSE, VLOOKUP chỉ chấp nhận đối sánh chính xác cho _value tra cứu . Nếu có nhiều giá trị khớp, giá trị khớp đầu tiên sẽ được trả về
- Nếu được đặt thành FALSE và không tìm thấy giá trị khớp nào cho khóa tìm kiếm, lỗi # N / A được trả về bởi hàm
Ví dụ: Tìm tỷ lệ chiết khấu cho số lượng đã mua
Ví dụ trong hình trên sử dụng hàm VLOOKUP để tìm tỷ lệ chiết khấu thay đổi tùy thuộc vào số lượng mặt hàng đã mua.
Ví dụ cho thấy chiết khấu cho việc mua 19 mặt hàng là 2%. Điều này là do cột Số lượng chứa phạm vi giá trị. Kết quả là VLOOKUP không thể tìm được kết quả khớp chính xác. Thay vào đó, phải tìm thấy một kết hợp gần đúng để trả lại tỷ lệ chiết khấu chính xác.
Để tìm các kết quả phù hợp:
- sắp xếp dữ liệu trong table_array theo thứ tự tăng dần;
- đặt đối số range_lookup thành TRUE
Trong ví dụ này, công thức sau đây có hàm VLOOKUP được sử dụng để tìm giảm giá cho số lượng hàng hóa đã mua.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Mặc dù công thức này chỉ có thể được gõ vào một ô trang tính, một tùy chọn khác, như được sử dụng với các bước được liệt kê bên dưới, là sử dụng hộp thoại của hàm để nhập đối số của nó.
- Sử dụng hộp thoại thường làm cho việc nhập đối số của hàm trở nên dễ dàng hơn.
Mở hộp thoại VLOOKUP
Các bước được sử dụng để nhập hàm VLOOKUP được hiển thị trong hình trên vào ô B2 là:
- Nhấp vào ô B2 để đặt ô hiện hoạt - vị trí nơi kết quả của hàm VLOOKUP được hiển thị
- Nhấp vào tab Công thức .
- Chọn tra cứu và tham chiếu từ ruy-băng để mở danh sách thả xuống chức năng
- Nhấp vào VLOOKUP trong danh sách để hiển thị hộp thoại của hàm
02/03
Nhập các đối số của hàm VLOOKUP của Excel
Chỉ đến tham chiếu ô
Các đối số cho hàm VLOOKUP được nhập vào các dòng riêng biệt của hộp thoại như trong hình trên.
Các tham chiếu ô được sử dụng làm đối số có thể được nhập vào dòng chính xác hoặc được thực hiện theo các bước bên dưới, chỉ liên quan đến đánh dấu phạm vi ô mong muốn bằng con trỏ chuột, có thể được sử dụng để nhập chúng vào hộp thoại .
Những lợi thế của việc sử dụng chỉ bao gồm:
- Nó nhanh hơn gõ;
- Ít sai lầm hơn được đưa vào tham chiếu ô chính xác.
Sử dụng tham chiếu ô tương đối và tuyệt đối với các đối số
Nó không phải là không phổ biến để sử dụng nhiều bản sao của VLOOKUP để trả về các thông tin khác nhau từ cùng một bảng dữ liệu. Để làm điều này dễ dàng hơn, thường VLOOKUP có thể được sao chép từ ô này sang ô khác. Khi các hàm được sao chép sang các ô khác, cần phải cẩn thận để đảm bảo rằng các tham chiếu ô kết quả là đúng cho vị trí mới của hàm.
Trong hình trên, ký hiệu đô la ( $ ) bao quanh tham chiếu ô cho đối số table_array cho biết rằng chúng là tham chiếu ô tuyệt đối , có nghĩa là chúng sẽ không thay đổi nếu hàm được sao chép sang một ô khác. Điều này là mong muốn vì nhiều bản sao của VLOOKUP sẽ tất cả tham chiếu cùng một bảng dữ liệu như là nguồn thông tin.
Tham chiếu ô được sử dụng cho lookup_value, mặt khác , không được bao quanh bởi các ký hiệu đô la, mà làm cho nó trở thành một tham chiếu ô tương đối. Các tham chiếu ô tương đối thay đổi khi chúng được sao chép để phản ánh vị trí mới của chúng so với vị trí của dữ liệu mà chúng tham chiếu đến.
Nhập các đối số chức năng
- Nhấp vào dòng tra cứu _value trong hộp thoại VLOOKUP
- Bấm vào ô C2 trong trang tính để nhập tham chiếu ô này làm đối số search_key
- Nhấp vào dòng Table_array của hộp thoại
- Đánh dấu các ô từ C5 đến D8 trong trang tính để nhập phạm vi này làm đối số Table_array - các tiêu đề bảng không được bao gồm
- Nhấn phím F4 trên bàn phím để thay đổi phạm vi thành tham chiếu ô tuyệt đối
- Nhấp vào dòng Col_index_num của hộp thoại
- Nhập một 2 vào dòng này làm đối số Col_index_num , vì tỷ lệ chiết khấu được đặt trong cột 2 của đối số Table_array
- Nhấp vào dòng Range_lookup của hộp thoại
- Nhập từ đúng như đối số Range_lookup
- Nhấn phím Enter trên bàn phím để đóng hộp thoại và quay lại trang tính
- Câu trả lời 2% (tỷ lệ chiết khấu cho số lượng đã mua) sẽ xuất hiện trong ô D2 của bảng tính
- Khi bạn bấm vào ô D2, hàm đầy đủ = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) xuất hiện trong thanh công thức phía trên trang tính
Tại sao VLOOKUP trả lại 2% là kết quả
- Trong ví dụ, cột Số lượng không chứa kết quả khớp chính xác cho giá trị search_key là 19.
- Vì đối số is_sorted được đặt thành TRUE, VLOOKUP sẽ tìm thấy một kết quả gần đúng với giá trị search_key .
- Giá trị gần nhất trong kích thước vẫn nhỏ hơn giá trị search_key của 19 là 11.
- VLOOKUP, do đó, tìm phần trăm chiết khấu trong hàng có chứa 11, và kết quả là trả về tỷ lệ chiết khấu là 2%.
03/03
Excel VLOOKUP không hoạt động: Lỗi # N / A và #REF
Thông báo lỗi VLOOKUP
Các thông báo lỗi sau được liên kết với VLOOKUP.
Lỗi # N / A ("không có giá trị") được hiển thị nếu:
- Không tìm thấy _value tra cứu trong cột đầu tiên của đối số phạm vi
- Đối số Table_array không chính xác. Ví dụ: đối số có thể bao gồm các cột trống ở bên trái của phạm vi
- Đối số Range_lookup được đặt thành FALSE và không thể tìm thấy đối sánh chính xác cho đối số search_key trong cột đầu tiên của phạm vi
- Đối số Range_lookup được đặt thành TRUE và tất cả các giá trị trong cột đầu tiên của phạm vi đều lớn hơn search_key
#REF! ("tham chiếu ngoài phạm vi") Lỗi được hiển thị Nếu:
- Đối số Col_index_num lớn hơn số cột trong Table_array