01 trên 06
Bắt đầu chức năng MATCH lồng nhau
Nhập hàm MATCH làm đối số số chỉ mục cột
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 .
Tuy nhiên, trong ví dụ này, chúng tôi có ba cột mà chúng tôi muốn tìm dữ liệu để 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 MATCH được phát. Nó sẽ cho phép chúng ta kết hợp một số cột với tên trường - tháng 1, tháng 2 hoặc tháng 3 - chúng ta nhập vào ô E2 của trang tính.
Hàm lồng nhau
Do đó, hàm MATCH 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 MATCH bên trong hàm VLOOKUP vào dòng Col_index_num của hộp thoại.
Nhập hàm MATCH 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 MATCH phải được nhập theo cách thủ công trong dòng Col_index_num .
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 "," .
Các bước hướng dẫn
Nhập đối số Lookup_value của hàm MATCH
Bước đầu tiên khi nhập hàm MATCH lồng nhau là nhập đối số Lookup_value .
Lookup_value sẽ là tham chiếu vị trí hoặc ô cho cụm từ tìm kiếm mà chúng tôi muốn đối sánh trong cơ sở dữ liệu.
- Trong hộp thoại hàm VLOOKUP, bấm vào dòng Col_index_num .
- Nhập đối sánh tên hàm theo sau là dấu ngoặc tròn mở " ( "
- Nhấp vào ô E2 để nhập tham chiếu ô đó vào hộp thoại.
- Nhập dấu phẩy "," sau tham chiếu ô E3 để hoàn thành mục nhập của đối số Lookup_value của hàm MATCH.
- Để hộp thoại hàm VLOOKUP mở cho bước tiếp theo trong hướng dẫn.
Trong bước cuối cùng của hướng dẫn, Lookup_values sẽ được nhập vào các ô D2 và E2 của trang tính .
02/06
Thêm hàm Lookup_array cho hàm MATCH
Thêm hàm Lookup_array cho hàm MATCH
Bước này bao gồm việc thêm đối số Lookup_array cho hàm MATCH lồng nhau.
Lookup_array là phạm vi ô mà hàm MATCH sẽ tìm kiếm để tìm đối số Lookup_value được thêm vào trong bước trước của hướng dẫn.
Trong ví dụ này, chúng tôi muốn hàm MATCH tìm kiếm các ô từ D5 đến G5 cho phù hợp với tên của tháng sẽ được nhập vào ô E2.
Các bước hướng dẫn
Các bước này sẽ được nhập sau khi dấu phẩy được nhập ở bước trước trên dòng Col_index_num trong hộp thoại hàm VLOOKUP.
- Nếu cần, hãy nhấp vào dòng Col_index_num sau dấu phẩy để đặt điểm chèn vào cuối mục nhập hiện tại.
- Đánh dấu các ô D5 đến G5 trong trang tính để nhập các tham chiếu ô này làm phạm vi mà hàm cần tìm kiếm.
- Nhấn phím F4 trên bàn phím để thay đổi phạm vi này thành tham chiếu ô tuyệt đối . Làm như vậy sẽ làm cho nó có thể sao chép công thức tra cứu đã hoàn thành đến các vị trí khác trong trang tính trong bước cuối cùng của hướng dẫn
- Nhập dấu phẩy "," sau tham chiếu ô E3 để hoàn thành mục nhập của đối số Lookup_array của hàm MATCH.
03/06
Thêm loại đối sánh và hoàn thành chức năng MATCH
Thêm loại đối sánh và hoàn thành chức năng MATCH
Đối số thứ ba và cuối cùng của hàm MATCH là đối số Match_type.
Đối số này cho Excel biết cách khớp với giá trị Lookup_value với các giá trị trong Lookup_array. Các lựa chọn là: -1, 0 hoặc 1.
Đối số này là tùy chọn. Nếu bỏ qua, hàm sử dụng giá trị mặc định là 1.
- nếu Match_type = 1 hoặc bị bỏ qua: MATCH tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị Lookup_value. Nếu giá trị này được chọn, dữ liệu Lookup_array phải được sắp xếp theo thứ tự tăng dần.
- nếu Match_type = 0: MATCH tìm giá trị đầu tiên chính xác bằng giá trị Lookup_value. Dữ liệu Lookup_array có thể được sắp xếp theo thứ tự bất kỳ.
- nếu Match_type = 1: MATCH tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị Lookup_value. Nếu giá trị này được chọn, dữ liệu Lookup_array phải được sắp xếp theo thứ tự giảm dần.
Các bước hướng dẫn
Các bước này sẽ được nhập sau khi dấu phẩy được nhập trong bước trước trên dòng Row_num trong hộp thoại hàm VLOOKUP.
- Theo dấu phẩy thứ hai trên dòng Col_index_num , gõ số 0 "" vì chúng ta muốn hàm lồng nhau trả về kết quả khớp chính xác với tháng nhập vào ô E2.
- Gõ một dấu ngoặc tròn " ) " để hoàn thành hàm MATCH.
- Để hộp thoại hàm VLOOKUP mở cho bước tiếp theo trong hướng dẫn.
04/06
Nhập đối số tra cứu phạm vi VLOOKUP
Đối số tra cứu phạm vi
Đố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 ta đang tìm kiếm các số liệu bán hàng cho một tháng cụ thể, chúng ta 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 hai chiề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 các ô D2 và E2, lỗi # N / A sẽ xuất hiện trong ô F2
- Lỗi này sẽ được sửa chữa trong bước tiếp theo trong hướng dẫn khi chúng ta sẽ thêm các tiêu chí tìm kiếm trong bước tiếp theo của hướng dẫn.
05/06
Kiểm tra công thức tra cứu hai chiều
Kiểm tra công thức tra cứu hai chiều
Để sử dụng công thức tra cứu hai chiều để tìm dữ liệu bán hàng hàng tháng cho các cookie khác nhau được liệt kê trong mảng bảng, hãy nhập tên cookie vào ô D2, tháng vào ô E2 và nhấn phím ENTER trên bàn phím.
Dữ liệu bán hàng sẽ được hiển thị trong ô F2.
Các bước hướng dẫn
- Nhấp vào ô D2 trong trang tính của bạn
- Gõ bột yến mạch vào ô D2 và nhấn phím ENTER trên bàn phím
- Nhấp vào ô E2
- Nhập tháng 2 vào ô E2 và nhấn phím ENTER trên bàn phím
- Giá trị $ 1,345 - số tiền bán cho cookie Oatmeal trong tháng 2 - sẽ được hiển thị trong ô F2
- Tại thời điểm này, trang tính của bạn phải khớp với ví dụ trên trang 1 của hướng dẫn này
- Kiểm tra công thức tìm kiếm thêm bằng cách nhập bất kỳ kết hợp nào của các loại cookie và tháng có trong Table_array và các số liệu bán hàng sẽ được hiển thị trong ô F2
- 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 bằng cách sử dụng Fill Handle .
Nếu một thông báo lỗi như #REF! xuất hiện trong ô F2, 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 đề.
06 trên 06
Sao chép Công thức tìm kiếm hai chiều với trình xử lý điền
Sao chép Công thức tìm kiếm hai chiều với trình xử lý điền
Để đơn giản hóa việc so sánh dữ liệu cho các tháng khác nhau hoặc các cookie khác nhau, công thức tra cứu có thể được sao chép sang các ô khác để có thể hiển thị nhiều số cùng một lúc.
Vì dữ liệu được trình bày theo 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 ô F2 sang ô F3.
Khi công thức được sao chép, Excel sẽ cập nhật các tham chiếu ô tương đối để phản ánh vị trí mới của công thức. Trong trường hợp này D2 trở thành D3 và E2 trở thành E3,
Đồng thời, Excel giữ tham chiếu ô tuyệt đối giống nhau nên phạm vi tuyệt đối $ D $ 5: $ G $ 5 vẫn giữ nguyên 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 ô D3 trong trang tính của bạn
- Gõ bột yến mạch vào ô D3 và nhấn phím ENTER trên bàn phím
- Nhấp vào ô E3
- Nhập tháng 3 vào ô E3 và nhấn phím ENTER trên bàn phím
- Bấm vào ô F2 để 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à Fill Handle
- Nhấp vào nút chuột trái và kéo thanh điều khiển điền xuống ô F3
- Nhả nút chuột và ô F3 phải chứa công thức tra cứu hai chiều
- Giá trị $ 1,287 - số tiền bán cho cookie Oatmeal trong tháng 3 sẽ được hiển thị trong ô F3