01/03
Tìm dữ liệu ở bên trái
Tổng quan về công thức tìm kiếm Excel bên trái
Hàm VLOOKUP của Excel được sử dụng để tìm và trả về thông tin từ một bảng dữ liệu dựa trên giá trị tra cứu mà bạn chọn.
Thông thường, VLOOKUP yêu cầu giá trị tra cứu nằm trong cột ngoài cùng bên trái của bảng dữ liệu và hàm trả về một trường dữ liệu khác nằm trong cùng hàng ở bên phải của giá trị này.
Bằng cách kết hợp VLOOKUP với hàm CHOOSE ; tuy nhiên, có thể tạo công thức tra cứu trái để :
- cho phép giá trị tra cứu được chọn từ bất kỳ cột nào trong bảng dữ liệu
- trả về thông tin nằm trong bất kỳ cột nào ở bên trái của giá trị tra cứu
Ví dụ: Sử dụng hàm VLOOKUP và CHOOSE trong Công thức tra cứu trái
Các bước chi tiết bên dưới tạo công thức tra cứu bên trái được nhìn thấy trong hình trên.
Công thức
= VLOOKUP ($ D $ 2, CHỌN ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)làm cho nó có thể tìm thấy phần được cung cấp bởi các công ty khác nhau được liệt kê trong cột 3 của bảng dữ liệu.
Công việc của hàm CHOOSE trong công thức là lừa VLOOKUP tin rằng cột 3 thực sự là cột 1. Do đó, tên của Công ty có thể được sử dụng làm giá trị tra cứu để tìm tên của bộ phận do mỗi công ty cung cấp.
Các bước hướng dẫn - Nhập dữ liệu hướng dẫn
- Nhập các tiêu đề sau vào các ô được chỉ định: D1 - Nhà cung cấp E1 - Phần
- Nhập bảng dữ liệu được nhìn thấy trong hình trên vào ô D4 đến F9
- Hàng 2 và 3 được để trống để chứa các tiêu chí tìm kiếm và công thức tra cứu bên trái được tạo trong hướng dẫn này
Bắt đầu công thức tra cứu trái - Mở hộp thoại VLOOKUP
Mặc dù có thể chỉ cần gõ công thức trên trực tiếp vào ô F1 trong bảng tính, nhiều người gặp khó khăn với cú pháp của công thức.
Một cách khác, trong trường hợp này, là sử dụng hộp thoại VLOOKUP. Hầu như tất cả các chức năng của Excel đều có một hộp thoại cho phép bạn nhập mỗi đối số của hàm trên một dòng riêng biệt.
Các bước hướng dẫn
- Nhấp vào ô E2 của trang tính - vị trí nơi kết quả của công thức tra cứu bên trái sẽ được hiển thị
- Nhấp vào tab Công thức của dải băng
- Nhấp vào tùy chọn tra cứu & tham chiếu trong 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ố vào hộp thoại VLOOKUP - Nhấn vào đây để xem hình ảnh lớn hơn
Các đối số của VLOOKUP
Đối số của hàm là các giá trị được sử dụng bởi hàm để tính kết quả.
Trong hộp thoại của hàm, tên của mỗi đối số được đặt trên một dòng riêng biệt theo sau là một trường để nhập giá trị.
Nhập các giá trị sau cho mỗi đối số của VLOOKUP trên dòng chính xác của hộp thoại như trong hình trên.
Giá trị tra cứu
Giá trị tra cứu là trường thông tin được sử dụng để tìm kiếm mảng bảng. VLOOKUP trả về một trường dữ liệu khác từ cùng hàng với giá trị tra cứu.
Ví dụ này sử dụng tham chiếu ô đến vị trí nơi tên công ty sẽ được nhập vào trang tính. Ưu điểm của việc này là nó giúp bạn dễ dàng thay đổi tên công ty mà không cần chỉnh sửa công thức.
Các bước hướng dẫn
- Nhấp vào dòng lookup_value trong hộp thoại
- Nhấp vào ô D2 để thêm tham chiếu ô này vào dòng lookup_value
- Nhấn phím F4 trên bàn phím để làm cho tham chiếu ô tuyệt đối - $ D $ 2
Lưu ý: Tham chiếu ô tuyệt đối được sử dụng cho giá trị tra cứu và đối số mảng bảng để ngăn lỗi nếu công thức tra cứu được sao chép sang các ô khác trong trang tính.
Bảng Array: Nhập chức năng CHOOSE
Đối số mảng bảng là khối dữ liệu liền kề mà từ đó thông tin cụ thể được lấy ra.
Thông thường, VLOOKUP chỉ nhìn bên phải đối số giá trị tìm kiếm để tìm dữ liệu trong mảng bảng. Để làm cho nó trông bên trái, VLOOKUP phải được lừa bằng cách sắp xếp lại các cột trong mảng bảng bằng cách sử dụng hàm CHOOSE.
Trong công thức này, hàm CHOOSE hoàn thành hai nhiệm vụ:
- nó tạo ra một mảng bảng chỉ có hai cột rộng - cột D và F
- nó thay đổi thứ tự từ phải sang trái của các cột trong mảng bảng sao cho cột F đến trước và cột D là thứ hai
Chi tiết về cách hàm CHOOSE thực hiện các nhiệm vụ này có thể được tìm thấy trên trang 3 của hướng dẫn .
Các bước hướng dẫn
Lưu ý: Khi nhập các hàm theo cách thủ công, mỗi đối số của hàm phải được phân tách bằng dấu phẩy "," .
- Trong hộp thoại hàm VLOOKUP, bấm vào dòng Table_array
- Nhập hàm CHOOSE sau
- CHỌN ({1,2}, $ F: $ F, $ D: $ D)
Số chỉ mục cột
Thông thường, số chỉ mục cột cho biết cột của mảng bảng nào chứa dữ liệu bạn đang theo dõi. Trong công thức này; tuy nhiên, nó đề cập đến thứ tự các cột được đặt bởi hàm CHOOSE.
Hàm CHOOSE tạo ra một mảng bảng rộng hai cột với cột F trước sau cột D. Vì thông tin được tìm kiếm - tên phần - nằm trong cột D, giá trị của đối số chỉ mục cột phải được đặt thành 2.
Các bước hướng dẫn
- Nhấp vào dòng Col_index_num trong hộp thoại
- Nhập số 2 vào dòng này
Tra cứu phạm vi
Đối số Range_lookup của VLOOKUP là một giá trị logic (TRUE hoặc FALSE) cho biết bạn muốn VLOOKUP tìm chính xác hoặc khớp gần đúng với giá trị tra cứu.
- Nếu TRUE hoặc nếu đối số này bị bỏ qua, VLOOKUP trả về kết quả khớp chính xác với giá trị Lookup_value hoặc nếu không tìm thấy kết hợp chính xác, hàm VLOOKUP trả về giá trị lớn nhất tiếp theo. Đối với công thức để làm điều này, dữ liệu trong cột đầu tiên của Table_array phải được sắp xếp theo thứ tự tăng dần .
- Nếu FALSE, VLOOKUP sẽ chỉ sử dụng đối sánh chính xác với Lookup_value. Nếu có hai hoặc nhiều giá trị trong cột đầu tiên của Table_array khớp với giá trị tra cứu, giá trị đầu tiên được tìm thấy sẽ được sử dụng. Nếu không tìm thấy kết quả khớp chính xác, lỗi # N / A được trả lại.
Trong hướng dẫn này, vì chúng ta đang tìm kiếm một tên phần cụ thể, Range_lookup sẽ được đặt thành False để chỉ các kết quả khớp chính xác được trả về bởi công thức.
Các bước hướng dẫn
- Nhấp vào dòng Range_lookup trong hộp thoại
- Nhập từ False vào dòng này để cho biết rằng chúng tôi muốn VLOOKUP trả lại kết quả khớp chính xác cho dữ liệu mà chúng tôi đang tìm kiếm
- Nhấn OK để hoàn thành công thức tra cứu bên trái và hộp thoại đóng
- Vì chúng ta chưa nhập tên công ty vào ô D2, lỗi # N / A sẽ xuất hiện trong ô E2
03/03
Kiểm tra công thức tra cứu trái
Trả về dữ liệu bằng công thức tra cứu trái
Để tìm công ty nào cung cấp các bộ phận nào, hãy nhập tên công ty vào ô D2 và nhấn phím ENTER trên bàn phím.
Tên phần sẽ được hiển thị trong ô E2.
Các bước hướng dẫn
- Nhấp vào ô D2 trong trang tính của bạn
- Gõ Gadgets Plus vào ô D2 và nhấn phím ENTER trên bàn phím
- Tiện ích văn bản - phần được cung cấp bởi công ty Gadgets Plus - sẽ được hiển thị trong ô E2
- Kiểm tra công thức tìm kiếm thêm bằng cách nhập tên công ty khác vào ô D2 và tên phần tương ứng sẽ xuất hiện trong ô E2
Thông báo lỗi VLOOKUP
Nếu một thông báo lỗi như # N / A xuất hiện trong ô E2, trước tiên hãy kiểm tra lỗi chính tả trong ô D2.
Nếu chính tả không phải là vấn đề, danh sách các thông báo lỗi VLOOKUP có thể giúp bạn xác định vị trí của vấn đề.
Breaking the LỰA CHỌN CÔNG VIỆC
Như đã đề cập, trong công thức này, hàm CHOOSE có hai công việc:
- nó tạo ra một mảng bảng chỉ có hai cột rộng - cột D và F
- nó thay đổi thứ tự từ phải sang trái của các cột trong mảng bảng sao cho cột F đến trước và cột D là thứ hai
Tạo một mảng bảng hai cột
Cú pháp cho hàm CHOOSE là:
= CHỌN (Số chỉ mục, Giá trị 1, Giá trị2, ... Value254)
Hàm CHOOSE thường trả về một giá trị từ danh sách các giá trị (Value1 đến Value254) dựa trên số chỉ mục đã nhập.
Nếu số chỉ mục là 1, hàm trả về Value1 từ danh sách; nếu số chỉ mục là 2, hàm trả về Value2 từ danh sách, v.v.
Bằng cách nhập nhiều số chỉ mục; tuy nhiên, hàm sẽ trả về nhiều giá trị theo bất kỳ thứ tự mong muốn nào. Việc chọn CHỌN để trả về nhiều giá trị được thực hiện bằng cách tạo một mảng .
Việc nhập một mảng được thực hiện bằng cách bao quanh các số được nhập với dấu ngoặc nhọn hoặc dấu ngoặc nhọn. Hai số được nhập cho số chỉ mục: {1,2} .
Cần lưu ý rằng CHỌN không giới hạn trong việc tạo ra một bảng hai cột. Bằng cách bao gồm một số bổ sung trong mảng - chẳng hạn như {1,2,3} - và một phạm vi bổ sung trong đối số giá trị, có thể tạo một bảng ba cột.
Các cột bổ sung sẽ cho phép bạn trả về các thông tin khác nhau với công thức tra cứu trái chỉ đơn giản bằng cách thay đổi đối số số chỉ mục cột của VLOOKUP thành số cột chứa thông tin mong muốn.
Thay đổi thứ tự các cột bằng chức năng CHOOSE
Trong hàm CHOOSE được sử dụng trong công thức này: CHỌN ({1,2}, $ F: $ F, $ D: $ D) , phạm vi cho cột F được liệt kê trước cột D.
Vì hàm CHOOSE thiết lập mảng bảng của VLOOKUP - nguồn dữ liệu cho hàm đó - việc chuyển đổi thứ tự của các cột trong hàm CHOOSE được chuyển tới VLOOKUP.
Bây giờ, như xa như VLOOKUP là có liên quan, mảng bảng chỉ có hai cột rộng với cột F bên trái và cột D ở bên phải. Vì cột F chứa tên của công ty chúng tôi muốn tìm kiếm và vì cột D chứa các tên phần, VLOOKUP sẽ có thể thực hiện các nhiệm vụ tra cứu thông thường của nó trong việc tìm kiếm dữ liệu nằm ở bên trái của giá trị tra cứu.
Kết quả là, VLOOKUP có thể sử dụng tên công ty để tìm phần họ cung cấp.