Bằng cách kết hợp hàm VLOOKUP của Excel với hàm COLUMN, chúng ta có thể tạo một công thức tra cứu cho phép bạn trả về nhiều giá trị từ một hàng của một cơ sở dữ liệu hoặc một bảng dữ liệu.
Trong ví dụ được hiển thị trong hình trên, công thức tra cứu giúp dễ dàng trả về tất cả các giá trị - chẳng hạn như giá, số bộ phận và nhà cung cấp - liên quan đến các phần cứng khác nhau.
01 trên 10
Trả về nhiều giá trị với Excel VLOOKUP
Thực hiện theo các bước được liệt kê dưới đây tạo công thức tra cứu được nhìn thấy trong hình ảnh ở trên sẽ trả về nhiều giá trị từ một bản ghi dữ liệu duy nhất.
Công thức tra cứu yêu cầu hàm COLUMN được lồng bên trong hàm VLOOKUP.
Làm tổ chức năng liên quan đến việc nhập hàm thứ hai làm một trong các đối số cho hàm đầu tiên.
Trong hướng dẫn này, hàm COLUMN sẽ được nhập làm đối số số chỉ mục cột cho VLOOKUP.
Bước cuối cùng trong hướng dẫn này bao gồm việc sao chép công thức tra cứu sang các cột bổ sung để lấy các giá trị bổ sung cho phần đã chọn.
Nội dung hướng dẫn
- Nhập dữ liệu hướng dẫn
- Tạo phạm vi được đặt tên cho bảng dữ liệu
- Bắt đầu hàm VLOOKUP
- Nhập đối số giá trị tìm kiếm bằng cách sử dụng tham chiếu ô tuyệt đối
- Nhập đối số mảng bảng
- Nhập hàm COLUMN lồng nhau
- Hoàn thành hàm VLOOKUP
- Sao chép Công thức tra cứu bằng Trình xử lý điền
- Lấy dữ liệu bằng công thức tra cứu
02 trên 10
Nhập dữ liệu hướng dẫn
Bước đầu tiên trong hướng dẫn này là nhập dữ liệu vào trang tính Excel.
Để làm theo các bước trong hướng dẫn, hãy nhập dữ liệu được hiển thị trong hình trên vào các ô sau.
- Nhập phạm vi dữ liệu hàng đầu vào ô D1 đến G1
- Nhập dãy thứ hai vào ô D4 đến G10
Các tiêu chí tìm kiếm và công thức tra cứu được tạo ra trong hướng dẫn này sẽ được nhập vào hàng 2 của trang tính.
Hướng dẫn không bao gồm định dạng được nhìn thấy trong hình ảnh, nhưng điều này sẽ không ảnh hưởng đến cách công thức tra cứu hoạt động.
Thông tin về các tùy chọn định dạng tương tự như các tùy chọn ở trên có sẵn trong Hướng dẫn định dạng Excel cơ bản này.
Các bước hướng dẫn
- Nhập dữ liệu như trong hình trên vào ô D1 đến G10
03 trên 10
Tạo phạm vi được đặt tên cho bảng dữ liệu
Phạm vi được đặt tên là một cách dễ dàng để tham chiếu đến một loạt dữ liệu trong một công thức. Thay vì nhập vào tham chiếu ô cho dữ liệu, bạn chỉ có thể nhập tên của phạm vi.
Một lợi thế thứ hai để sử dụng một phạm vi được đặt tên là các tham chiếu ô cho phạm vi này không bao giờ thay đổi ngay cả khi công thức được sao chép sang các ô khác trong trang tính.
Do đó, các tên dải ô thay thế cho việc sử dụng các tham chiếu ô tuyệt đối để ngăn lỗi khi sao chép công thức.
Lưu ý: Tên dải ô không bao gồm tiêu đề hoặc tên trường cho dữ liệu (hàng 4) mà chỉ bao gồm dữ liệu.
Các bước hướng dẫn
- Đánh dấu các ô D5 đến G10 trong trang tính để chọn chúng
- Nhấp vào Hộp Tên nằm phía trên cột A
- Nhập "Bảng" (không có dấu ngoặc kép) vào Hộp Tên
- Nhấn phím ENTER trên bàn phím
- Các ô D5 đến G10 hiện có tên dãy là "Bảng". Chúng ta sẽ sử dụng tên cho đối số mảng bảng VLOOKUP sau trong hướng dẫn
04 trên 10
Mở hộp thoại VLOOKUP
Mặc dù có thể chỉ cần gõ công thức tra cứu của chúng ta trực tiếp vào một ô trong một trang tính, nhiều người cảm thấy khó giữ cú pháp thẳng - đặc biệt là một công thức phức tạp như công thức chúng ta đang sử dụng trong hướng dẫn này.
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 hai chiều 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 để mở hộp thoại của hàm
05 trên 10
Nhập đối số giá trị tìm kiếm bằng cách sử dụng tham chiếu ô tuyệt đối
Thông thường, giá trị tra cứu khớp với một trường dữ liệu trong cột đầu tiên của bảng dữ liệu.
Trong ví dụ của chúng tôi, giá trị tra cứu đề cập đến tên của phần phần cứng mà chúng tôi muốn tìm thông tin.
Các loại dữ liệu cho phép cho giá trị tra cứu là:
- dữ liệu văn bản
- giá trị logic (chỉ TRUE hoặc FALSE)
- một số
- tham chiếu ô tới giá trị trong trang tính
Trong ví dụ này, chúng ta sẽ nhập tham chiếu ô tới nơi đặt tên phần - ô D2.
Tham chiếu ô tuyệt đối
Trong bước sau trong hướng dẫn, chúng ta sẽ sao chép công thức tra cứu trong ô E2 đến các ô F2 và G2.
Thông thường, khi các công thức được sao chép trong Excel, tham chiếu ô thay đổi để phản ánh vị trí mới của chúng.
Nếu điều này xảy ra, D2 - tham chiếu ô cho giá trị tra cứu - sẽ thay đổi khi công thức được sao chép tạo lỗi trong các ô F2 và G2.
Để ngăn chặn các lỗi, chúng tôi sẽ chuyển đổi D2 tham chiếu ô thành tham chiếu ô tuyệt đối .
Tham chiếu ô tuyệt đối không thay đổi khi công thức được sao chép.
Tham chiếu ô tuyệt đối được tạo bằng cách nhấn phím F4 trên bàn phím. Làm như vậy sẽ thêm các ký hiệu đô la xung quanh tham chiếu ô như $ D $ 2
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 . Đây là ô mà chúng tôi sẽ nhập tên phần mà chúng tôi đang tìm kiếm thông tin
- Không di chuyển điểm chèn, nhấn phím F4 trên bàn phím để chuyển đổi D2 sang tham chiếu ô tuyệt đối $ D $ 2
- Để hộp thoại hàm VLOOKUP mở cho bước tiếp theo trong hướng dẫn
06 trên 10
Nhập đối số mảng bảng
Mảng bảng là bảng dữ liệu mà công thức tra cứu tìm kiếm để tìm thông tin chúng tôi muốn.
Mảng bảng phải chứa ít nhất hai cột dữ liệu .
- cột đầu tiên chứa đối số giá trị tra cứu (bước trước trong hướng dẫn)
- cột thứ hai và bất kỳ cột bổ sung nào, sẽ được công cụ tra cứu tìm kiếm để tìm thông tin chúng tôi chỉ định.
Đối số mảng bảng phải được nhập vào dưới dạng phạm vi chứa tham chiếu ô cho bảng dữ liệu hoặc dưới dạng tên dải ô .
Trong ví dụ này, chúng ta sẽ sử dụng tên dãy được tạo trong bước 3 của hướng dẫn.
Các bước hướng dẫn
- Nhấp vào dòng table_array trong hộp thoại
- Nhập "Bảng" (không có dấu ngoặc kép) để nhập tên dãy cho đối số này
- Để hộp thoại hàm VLOOKUP mở cho bước tiếp theo trong hướng dẫn
07 trên 10
Làm tổ chức năng COLUMN
Thông thường VLOOKUP chỉ trả về dữ liệu từ một cột của một bảng dữ liệu và cột này được thiết lập bởi đối số số chỉ mục cột .
Trong ví dụ này, tuy nhiên, chúng tôi có ba cột mà chúng tôi muốn trả lại dữ liệu vì vậy chúng tôi cần một cách để dễ dàng thay đổi số chỉ mục cột mà không cần chỉnh sửa công thức tra cứu của chúng tôi.
Đây là nơi hàm COLUMN xuất hiện. Bằng cách nhập nó làm đối số số chỉ mục cột , nó sẽ thay đổi khi công thức tra cứu được sao chép từ ô D2 sang ô E2 và F2 sau trong hướng dẫn.
Hàm lồng nhau
Do đó, hàm COLUMN hoạt động như đối số số chỉ mục cột của VLOOKUP.
Điều này được thực hiện bằng cách lồng hàm COLUMN bên trong hàm VLOOKUP trong dòng Col_index_num của hộp thoại.
Nhập hàm COLUMN theo cách thủ công
Khi các hàm lồng nhau, Excel không cho phép chúng ta mở hộp thoại của hàm thứ hai để nhập các đối số của nó.
Do đó, hàm COLUMN phải được nhập thủ công trong dòng Col_index_num .
Hàm COLUMN chỉ có một đối số - tham số tham chiếu là tham chiếu ô.
Chọn đối số tham chiếu hàm của hàm COLUMN
Công việc của hàm COLUMN là trả về số cột được cho làm đối số Tham chiếu .
Nói cách khác, nó chuyển đổi các ký tự cột thành một số với cột A là cột đầu tiên, cột B thứ hai và vân vân.
Vì trường dữ liệu đầu tiên chúng ta muốn trả về là giá của mục - nằm trong cột hai của bảng dữ liệu - chúng ta có thể chọn tham chiếu ô cho bất kỳ ô nào trong cột B làm Đối số tham chiếu để lấy số 2 cho đối số Col_index_num .
Các bước hướng dẫn
- Trong hộp thoại hàm VLOOKUP, bấm vào dòng Col_index_num
- Nhập cột tên hàm theo sau là một dấu ngoặc tròn mở " ( "
- Nhấp vào ô B1 trong trang tính để nhập tham chiếu ô đó làm đối số Tham chiếu
- Nhập dấu ngoặc tròn " ) " để hoàn thành hàm COLUMN
- Để hộp thoại hàm VLOOKUP mở cho bước tiếp theo trong hướng dẫn
08 trên 10
Nhập đối số tra cứu phạm vi VLOOKUP
Đối số Range_lookup của VLOOKUP là một giá trị lô-gic (TRUE hoặc FALSE) cho biết bạn muốn VLOOKUP tìm chính xác hay khớp gần đúng với giá trị Lookup_value.
- 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 tôi đang tìm kiếm thông tin cụ thể về một mục phần cứng cụ thể, chúng tôi sẽ đặt Range_lookup bằng False .
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 và hộp thoại đóng
- Vì chúng ta chưa nhập các tiêu chí tra cứu vào ô D2, lỗi # N / A sẽ xuất hiện trong ô E2
- Lỗi này sẽ được sửa khi chúng ta sẽ thêm tiêu chí tìm kiếm trong bước cuối cùng của hướng dẫn
09 trên 10
Sao chép Công thức tra cứu bằng Trình xử lý điền
Công thức tra cứu nhằm lấy dữ liệu từ nhiều cột của bảng dữ liệu cùng một lúc.
Để thực hiện việc này, công thức tra cứu phải nằm trong tất cả các trường mà chúng tôi muốn thông tin từ đó.
Trong hướng dẫn này, chúng tôi muốn nó lấy dữ liệu từ cột 2, 3 và 4 của bảng dữ liệu - đó là giá, số bộ phận và tên của nhà cung cấp khi chúng tôi nhập tên phần là Lookup_value.
Vì dữ liệu được trình bày trong một mẫu thông thường trong bảng tính , chúng ta có thể sao chép công thức tra cứu trong ô E2 đến các ô F2 và G2.
Khi công thức được sao chép, Excel sẽ cập nhật tham chiếu ô tương đối trong hàm COLUMN (B1) để phản ánh vị trí mới của công thức.
Đồng thời, Excel không thay đổi tham chiếu ô tuyệt đối $ D $ 2 và phạm vi được đặt tên Bảng khi công thức được sao chép.
Có nhiều cách để sao chép dữ liệu trong Excel, nhưng có lẽ cách dễ nhất là sử dụng Fill Handle .
Các bước hướng dẫn
- Nhấp vào ô E2 - nơi công thức tra cứu được đặt - để biến nó thành ô hiện hoạt
- Đặt con trỏ chuột lên hình vuông màu đen ở góc dưới cùng bên phải. Con trỏ sẽ thay đổi thành dấu cộng " + " - đây là nút điều khiển điền
- Nhấp vào nút chuột trái và kéo thanh điều khiển điền vào ô G2
- Nhả nút chuột và ô F3 phải chứa công thức tra cứu hai chiều
- Nếu được thực hiện đúng, các ô F2 và G2 bây giờ cũng chứa lỗi # N / A có trong ô E2
10 trên 10
Nhập tiêu chí tra cứu
Khi công thức tra cứu đã được sao chép vào các ô được yêu cầu, nó có thể được sử dụng để lấy thông tin từ bảng dữ liệu.
Để làm như vậy, hãy nhập tên của mục bạn muốn truy xuất vào ô Lookup_value (D2) và nhấn phím ENTER trên bàn phím.
Sau khi thực hiện, mỗi ô chứa công thức tra cứu phải chứa một phần dữ liệu khác về mục phần cứng bạn đang tìm kiếm.
Các bước hướng dẫn
- Bấm vào ô D2 trong bảng tính
- Nhập Widget vào ô D2 và nhấn phím ENTER trên bàn phím
- Thông tin sau sẽ được hiển thị trong các ô từ E2 đến G2:
- E2 - 14,76 đô la - giá của tiện ích con
- F2 - PN-98769 - số bộ phận cho tiện ích con
- G2 - Widgets Inc. - tên của nhà cung cấp cho widget
- Kiểm tra công thức mảng VLOOKUP hơn nữa bằng cách nhập tên của các phần khác vào ô D2 và quan sát kết quả trong các ô từ E2 đến G2
Nếu một thông báo lỗi như #REF! xuất hiện trong các ô E2, F2 hoặc G2, danh sách các thông báo lỗi VLOOKUP này có thể giúp bạn xác định vị trí của vấn đề.