Đối số thứ ba trong hàm eg chứa gì? Điều kiện tìm kiếm. Khi nào nên sử dụng VLOOKUP

Hàm VLOOKUP (Tra cứu theo chiều dọc) tìm kiếm một bảng dữ liệu và dựa trên tiêu chí truy vấn tìm kiếm, trả về giá trị tương ứng từ một cột cụ thể. Rất thường xuyên cần phải sử dụng nhiều điều kiện cùng một lúc trong truy vấn tìm kiếm. Nhưng theo mặc định Chức năng này không thể xử lý nhiều hơn một điều kiện. Vì vậy, bạn nên sử dụng rất công thức đơn giản, điều này sẽ mở rộng khả năng của hàm VLOOKUP trên nhiều cột cùng một lúc.

Hoạt động của hàm VLOOKUP theo một số tiêu chí

Để rõ ràng, hãy phân tích công thức VLOOKUP bằng một ví dụ về một số điều kiện. Ví dụ: chúng tôi sẽ sử dụng báo cáo sơ đồ về doanh thu của đại diện bán hàng trong quý:

Trong báo cáo này, bạn cần tìm số liệu doanh thu của một đại diện bán hàng cụ thể vào một ngày cụ thể. Với các điều kiện tìm kiếm, yêu cầu của chúng tôi phải có 2 điều kiện:

– Ngày giao tiền thu được cho thủ quỹ.
– Họ của người đại diện bán hàng.

Để giải quyết vấn đề này, chúng ta sẽ sử dụng hàm VLOOKUP trong một số điều kiện và tạo công thức sau:

Trong ô C1, nhập giá trị đầu tiên cho tiêu chí đầu tiên truy vấn tìm kiếm. Ví dụ: ngày: 22/03/2017.
Trong ô C2, nhập họ của đại diện bán hàng (ví dụ: Novikov). Giá trị này sẽ được sử dụng làm đối số thứ hai của truy vấn tìm kiếm.
Trong ô C3, chúng ta sẽ nhận được kết quả tìm kiếm, để thực hiện việc này, hãy nhập công thức vào đó:

Sau khi nhập công thức, nhấn tổ hợp phím nóng CTRL+SHIFT+Enter để xác nhận, vì công thức phải được thực thi trong một mảng.

Kết quả tìm kiếm trong bảng sử dụng hai điều kiện:

Đã tìm thấy số tiền doanh thu của một đại diện bán hàng cụ thể vào một ngày cụ thể.

Phân tích nguyên lý hoạt động của công thức hàm VLOOKUP với một số điều kiện:

Đối số đầu tiên của hàm =VLOOKUP() là điều kiện đầu tiên để tìm kiếm một giá trị trong bảng báo cáo doanh thu của đại diện bán hàng. Đối số thứ hai chứa bảng ảođược tạo ra như là kết quả của sự tính toán lớn hàm logic=IF(). Mỗi họ trong phạm vi ô B6:B12 được so sánh với giá trị trong ô C2. Do đó, một mảng dữ liệu có điều kiện được tạo trong bộ nhớ với các phần tử có giá trị TRUE và FALSE.

Sau đó, nhờ công thức, trong bộ nhớ chương trình, mỗi phần tử đúng được thay thế bằng tập dữ liệu 3 phần tử:

phần tử – Ngày.
phần tử – Họ.
yếu tố – Doanh thu.

Và mỗi phần tử sai trong bộ nhớ được thay thế bằng tập hợp 3 phần tử gồm các giá trị văn bản trống (""). Kết quả là nó được tạo trong bộ nhớ chương trình bảng mới, mà hàm VLOOKUP sẽ hoạt động. Nó bỏ qua tất cả các bộ dữ liệu phần tử trống. Và các phần tử không trống được khớp với giá trị của ô C1, được sử dụng làm tiêu chí đầu tiên của truy vấn tìm kiếm (Ngày). Tóm lại, bảng trong bộ nhớ được kiểm tra bằng hàm VLOOKUP với một điều kiện tìm kiếm. Nếu kết quả khớp là dương, hàm trả về giá trị của phần tử từ cột thứ ba (doanh thu) của bảng điều kiện. Điều này xảy ra vì đối số thứ ba chỉ định số cột 3 mà từ đó các giá trị được lấy. Điều đáng chú ý là để xem, toàn bộ bảng được chỉ định trong các đối số của hàm (trong đối số thứ hai), nhưng bản thân việc tìm kiếm luôn đi qua cột đầu tiên trong bảng được chỉ định.

Và cột nào sẽ lấy giá trị trả về đã được chỉ định trong đối số thứ ba.

Số 0 trong đối số cuối cùng của hàm cho biết kết quả khớp phải hoàn toàn chính xác.

Bài viết này được dành riêng cho Hàm VLOOKUP. Nó sẽ xem xét hướng dẫn từng bước Hàm VLOOKUP, được phép "". Trong bài viết này chúng ta sẽ xem xét chi tiết về mô tả, cú pháp và Ví dụ về hàm VLOOKUP trong Excel. Chúng ta cũng sẽ xem xét cách sử dụng nó cũng như những lỗi chính và lý do. Chức năng Vlookup không hoạt động.

Cú pháp và mô tả hàm VLOOKUP trong Excel

Vì vậy, vì tiêu đề thứ hai của bài viết này là “ Hàm VLOOKUP trong Excel dành cho người chưa biết", chúng ta hãy bắt đầu bằng việc tìm hiểu hàm VLOOKUP là gì và nó có tác dụng gì nhé? Hàm VLOOKUP trong tiếng Anh VLOOKUP, tra cứu giá trị đã chỉ định và trả về giá trị tương ứng từ cột khác.

Hàm VLOOKUP hoạt động như thế nào?? Hàm VLOOKUP trong Excel tìm kiếm danh sách dữ liệu của bạn dựa trên định danh duy nhất và cung cấp cho bạn một phần thông tin liên quan đến mã định danh duy nhất đó.

Chữ "V" trong VPR là viết tắt của "dọc". Nó được dùng để phân biệt Hàm VLOOKUP và GLOOKUP, tra cứu giá trị trong dòng trên cùng mảng (“H” là viết tắt của “ngang”).

Hàm VLOOKUP có sẵn ở tất cả Các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Cú pháp Hàm VLOOKUP như sau:

VLOOKUP(giá_trị tra cứu, bảng, số_cột, [khoảng_tra cứu])

Bạn có thể thấy, hàm VLOOKUP có 4 tham số hoặc đối số. Ba tham số đầu tiên là bắt buộc, tham số cuối cùng là tùy chọn.

tra cứu_value là giá trị tìm kiếm

Đây có thể là một giá trị (số, ngày hoặc văn bản) hoặc tham chiếu ô(tham chiếu đến ô chứa giá trị tìm kiếm) hoặc giá trị được trả về bởi một số khác hàm Excel. Ví dụ:

Tìm kiếm một số: =VLOOKUP(40; A2:B15; 2) - công thức sẽ tìm kiếm số 40.
Tìm kiếm văn bản: =VLOOKUP(“táo”, A2:B15, 2) - công thức sẽ tìm kiếm văn bản “táo”. Xin lưu ý rằng bạn luôn bao gồm giá trị văn bản trong "dấu ngoặc kép".
Tìm kiếm giá trị từ một ô khác: =VLOOKUP(C2, A2:B15, 2) - công thức sẽ tìm kiếm giá trị trong ô C2.

một bảng là hai hoặc nhiều cột dữ liệu.

nhớ lấy hàm VLOOKUP luôn tìm kiếm giá trị mong muốn ở cột đầu tiên của bảng. Bảng của bạn có thể chứa nhiều giá trị khác nhau như văn bản, ngày tháng, số hoặc giá trị boolean. Giá trị trường hợp không nhạy cảm, có nghĩa là chữ hoa và chữ thườngđược coi là giống hệt nhau.

Vì vậy, công thức =VLOOKUP(40, A2:B15, 2) của chúng tôi sẽ tìm kiếm "40" trong các ô từ A2 đến A15 vì A là cột đầu tiên của bảng A2:B15.

số_cột - số cột trong bảng mà từ đó giá trị trong hàng tương ứng sẽ được trả về.

Cột ngoài cùng bên trái trong bảng đã chỉ định là 1, cột thứ hai là 2, cột thứ ba là 3, v.v.

4. khoảng_view xác định xem bạn đang tìm kiếm kết quả khớp chính xác (FALSE) hay kết quả khớp gần đúng (TRUE hoặc bị bỏ qua). Tham số cuối cùng này là tùy chọn nhưng rất quan trọng.

Ví dụ về hàm VLOOKUP trong Excel

Bây giờ hãy xem xét một số trường hợp sử dụng Hàm VLOOKUP cho dữ liệu thực tế.

Hàm VLOOKUP trên các trang tính khác nhau

Trên thực tế Công thức VLOOKUP hiếm khi được sử dụng để tìm kiếm dữ liệu trên một trang tính. Thông thường, bạn sẽ phải tìm kiếm và lấy dữ liệu liên quan từ một trang tính khác.

ĐẾN sử dụng hàm VLOOKUP từ một trang tính Excel khác, bạn phải nhập tên trang tính và Dấu chấm than trong đối số bảng trước phạm vi ô, ví dụ: =VLOOKUP(40;Sheet2!A2:B15;2). Công thức chỉ ra rằng phạm vi tìm kiếm A2:B15 nằm trong Trang tính 2.

Tất nhiên, bạn không cần phải nhập tên trang tính theo cách thủ công. Chỉ cần bắt đầu nhập công thức và khi nói đến đối số bảng, hãy chuyển sang bảng tra cứu và chọn phạm vi bằng chuột.

Công thức bạn thấy trong hình bên dưới tìm kiếm văn bản trong ô A2 ("Sản phẩm 3") trong cột A (cột đầu tiên của phạm vi tìm kiếm A2:B9) trên trang tính "Giá":

VLOOKUP(A2,Giá!$A$2:$B$8,2,FALSE)

Hàm VLOOKUP trong Excel - Hàm VLOOKUP trên các sheet khác nhau

Cách sử dụng phạm vi hoặc bảng được đặt tên trong công thức VLOOKUP

Nếu định sử dụng cùng một phạm vi tra cứu trong nhiều công thức VLOOKUP, bạn có thể tạo và nhập tên cho phạm vi đó trực tiếp vào đối số bảng của công thức VLOOKUP.

Để tạo một phạm vi được đặt tên, chỉ cần chọn các ô và nhập bất kỳ tên nào vào hộp Tên ở bên trái bảng Công thức.

Hàm VLOOKUP trong Excel - Đặt tên dãy

Bây giờ bạn có thể viết công thức VLOOKUP sau để biết giá của Sản phẩm 1:

VLOOKUP("Sản phẩm 1";Sản phẩm;2)

Hàm VLOOKUP trong Excel - Ví dụ về hàm VLOOKUP có tên dải ô

Hầu hết các tên phạm vi trong Excel đều áp dụng cho toàn bộ sổ làm việc, do đó bạn không cần phải chỉ định tên trang tính, ngay cả khi phạm vi tìm kiếm của bạn nằm trên một trang tính khác. Những công thức như vậy dễ hiểu hơn nhiều. Ngoài ra, việc sử dụng các phạm vi được đặt tên có thể là một giải pháp thay thế tốt cho các ô. Vì phạm vi được đặt tên không thay đổi khi công thức được sao chép sang các ô khác nên bạn có thể chắc chắn rằng phạm vi tìm kiếm của mình sẽ luôn chính xác.

Nếu bạn đã chuyển đổi một dải ô thành đầy đủ chức năng bảng tính Excel(Chèn tab --> Bảng), bạn có thể chọn phạm vi tìm kiếm bằng chuột và Microsoft Excel sẽ tự động thêm tên cột hoặc tên bảng vào công thức:

Hàm VLOOKUP trong Excel - Ví dụ hàm VLOOKUP với tên bảng

Công thức đầy đủ có thể trông giống như thế này:

VLOOKUP("Sản phẩm 1";Bảng6[[Sản phẩm]:[Giá]];2)

hoặc thậm chí =VLOOKUP("Sản phẩm 1";Bảng6;2).

Giống như các phạm vi được đặt tên, tên cột là hằng số và tham chiếu ô sẽ không thay đổi cho dù công thức VLOOKUP được sao chép ở đâu.

Hàm VLOOKUP có nhiều điều kiện

Hãy xem xét ví dụ về hàm VLOOKUP với một số điều kiện. Chúng ta có dữ liệu đầu vào sau:

Hàm VLOOKUP trong Excel - Bảng dữ liệu nguồn

Giả sử chúng ta cần sử dụng Hàm VLOOKUP với một số điều kiện. Ví dụ: để tìm kiếm giá của một sản phẩm bằng hai tiêu chí: tên sản phẩm và loại sản phẩm.

Để sử dụng Hàm VLOOKUP với một số điều kiện cần chèn thêm một cột ở đầu sẽ lưu trữ thông tin tên, loại sản phẩm.

Vì vậy, trên trang “Giá”, hãy chèn một cột và trong ô A2, nhập công thức sau:

Sử dụng công thức này, chúng ta thu được giá trị của cột “Sản phẩm” và “Loại”. Điền vào tất cả các ô.

Bây giờ bảng tra cứu trông như thế này:

Hàm VLOOKUP trong Excel - Thêm Cột Trợ Giúp

Bây giờ, trong ô C2 trên trang “Bán hàng”, chúng ta sẽ viết công thức VLOOKUP sau:

VLOOKUP(A2Prices!$A$1:$D$8,4,FALSE)

Chúng tôi điền vào các ô còn lại và kết quả là chúng tôi nhận được giá cho từng sản phẩm theo loại:

Hàm VLOOKUP trong Excel - Ví dụ VLOOKUP với một số điều kiện

Bây giờ chúng ta hãy nhìn vào nó Lỗi hàm VLOOKUP.

Tại sao hàm VLOOKUP không hoạt động?

Trong phần này của bài viết chúng ta sẽ xem xét lý do tại sao Chức năng Vlookup không hoạt động và có thể Lỗi hàm VLOOKUP.

Loại lỗi	Gây ra	Giải pháp
	Vị trí của cột đang được tìm kiếm không chính xác	Cột trong bảng nơi thực hiện tìm kiếm PHẢI là cột ngoài cùng bên trái. Di chuyển cột bạn đang tìm kiếm sang phía bên trái của bảng. Hoặc tạo cột trùng lặp phụ ở bên trái trong bảng.
	Phạm vi bảng không cố định	Nếu giá trị đầu tiên được hiển thị chính xác và sau khi kéo dãn công thức VLOOKUP, gặp lỗi #N/A ở một số ô thì phạm vi bảng chưa được sửa. Sử dụng ($) để khóa một phạm vi bảng để công thức sử dụng cùng một phạm vi khi được điền. Hoặc dùng
	Không thể tìm thấy kết quả khớp chính xác (nếu trong xem tua nhanh thời gian tìm kiếm giá trị chính xác đã chọn (0)
	TRONG xem tua nhanh thời gian việc tìm kiếm giá trị gần nhất (1) được thực hiện và bảng mà tìm kiếm được thực hiện không được sắp xếp.	Sắp xếp cột đầu tiên của bảng theo thứ tự tên tăng dần.
		Sử dụng các hàm TYPE hoặc SPACE.
	Giá trị số cột vượt quá số cột trong bảng	Kiểm tra số cột chứa giá trị trả về.
	Dấu ngoặc kép bị thiếu trong công thức	Nếu bạn đang sử dụng như giá trị mong muốn không phải là liên kết ô mà là văn bản thì nó phải được đặt trong dấu ngoặc kép. Ví dụ: VLOOKUP("Sản phẩm 1"; Giá!$A$2:$B$8,2,0)

Tôi hy vọng rằng ngay cả bây giờ cho hàm giả VLOOKUP trong Excel sẽ rõ ràng.

Bất kỳ người dùng nào làm việc với hàm LOOKUP sau một thời gian sẽ nhận ra rằng việc làm việc với hàm chỉ tìm kiếm ở bên phải cột chứa giá trị tìm kiếm không mấy thuận tiện. Các hàm MATCH và INDEX có thể giải quyết vấn đề này, nhưng công thức tổng thể sẽ dài hơn và phức tạp hơn nhiều so với hàm VLOOKUP đơn giản hơn.

Tiện ích bổ sung tùy chỉnh rLOOKUP hoạt động giống như VLOOKUP nhưng cho phép bạn thực hiện tìm kiếm ngược lại. Hàm này chỉ sử dụng các phương thức lập chỉ mục và so khớp trong VBA. Nó được cung cấp trong Tiện ích bổ sung Excelđể dễ sử dụng.

Tải xuống tiện ích bổ sung

Lưu tệp vào máy tính của bạn và cài đặt nó trong Excel bằng cách chọn Tệp -> Tùy chọn -> Tiện ích bổ sung. Nhấn nút Đi..., trong cửa sổ mở ra, chọn Ôn tập. Chọn file đã lưu và nhấn OK để hoàn tất cài đặt. Sau khi cài đặt, thông báo "Reverse_Lookup" sẽ xuất hiện trong danh sách các tiện ích bổ sung có sẵn và chức năng rLOOKUP sẽ có sẵn trong tất cả các sách.

Mô tả chức năng

Các đối số cho hàm rLOOKUP cũng giống như đối với hàm VLOOKUP, hãy xem ảnh chụp màn hình bên dưới. Để thực hiện tìm kiếm ngược lại, chỉ cần nhập một số âm vào đối số "Col_Index_Num" (xem ví dụ bên dưới). Công thức sẽ Tra cứu về phía bên trái.

Một trong những khác biệt giữa hai chức năng này là nếu Thời gian trôi đi_view bỏ qua trong hàm VLOOKUP chắc chắn sẽ không có kết quả. Tuy nhiên, đối với rLOOKUP thì ngược lại, theo mặc định hàm này trả về giá trị chính xác. Một lần nữa, điều này là do người dùng thường muốn nhận được giá trị từ kết quả tìm kiếm. Các giá trị lỗi được hàm rLOOKUP trả về tương tự như các giá trị được VLOOKUP trả về.

Ví dụ về hàm

Kế tiếp Dữ liệu Excel hiển thị địa chỉ văn phòng, tên nhân viên và vị trí.

Để trả lời câu hỏi “Ai làm việc ở văn phòng B43?” chúng ta có thể sử dụng hàm VLOOKUP:

VLOOKUP("B43"; A1:C6;2; FALSE) trả về "Dilbert".

Tuy nhiên, nếu chúng ta muốn biết “Văn phòng của Dilbert ở đâu?” thì hàm VLOOKUP sẽ không hoạt động. Đây là lúc hàm rLOOKUP phát huy tác dụng:

rLOOKUP("Dilbert"; A1:B6, -2).

Xin lưu ý rằng trong các đối số, số cột được chỉ định là số âm -2, cho phép bạn tìm kiếm trong cột bên trái.

Nói chung, hàm này hoạt động theo cả hai hướng:

rLOOKUP("B43";A1:C6;2) trả về "Dilbert" và

rLOOKUP("Dilbert"; A1:B6; -2) trả về "B43".

Các ví dụ khác được đưa ra dưới đây:

Hãy nhớ cài đặt tiện ích bổ sung trước, nếu không sẽ báo lỗi #TÊN?.

Chức năng hoạt động như thế nào

Hàm sử dụng hàm MATCH và CHỈ SỐđể chạy tìm kiếm ngược lại.

Xem mã nguồn của hàm bên dưới.

Hàm rLOOKUP(Giá trị tra cứu, Mảng_bảng dưới dạng phạm vi, Col_index_num dưới dạng số nguyên, Phạm vi_lookup tùy chọn dưới dạng Boolean)
"
"Chức năng tùy chỉnh cho phép tra cứu theo chiều dọc tiến và lùi
"Hoạt động giống như VLookup cho Col_index_num dương và thực hiện tra cứu ngược cho Col_index_num âm
"Không giống như VLOOKUP, nó tìm giá trị chính xác theo mặc định
"
"Nitin Mehta, www.Engineers-Excel.com
"
"
Dim Source_Col dưới dạng phạm vi, Dest_Col_num dưới dạng số nguyên

"Cột Nguồn là Cột chứa Lookup_value. Đây là cột ngoài cùng bên trái nếu
"Col_index_number là số dương. Nếu Col_index_number âm thì đó là cột ngoài cùng bên phải
Đặt Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))

"Dest_col_num là số cột trong Phạm vi Table_array từ nơi chúng ta phải chọn mục nhập theo Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)

"Sử dụng hàm MATCH và INDEX để tra cứu.
"Việc sử dụng CVErr(xlErrRef) do Brian Canes đề xuất
rLOOKUP = IIf(Dest_Col_num< 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))

Chức năng kết thúc

Để lại bình luận của bạn!

Xin chào các bạn. Bạn có thường xuyên phải tìm kiếm giá trị khớp trong bảng tính Excel không? Ví dụ: bạn cần tìm địa chỉ của một người trong một thư mục hoặc giá của một sản phẩm trong bảng giá. Nếu những nhiệm vụ như vậy xảy ra, bài đăng này chỉ dành cho bạn!

Tôi thực hiện các quy trình tương tự hàng ngày và nếu không có các chức năng được mô tả bên dưới, tôi sẽ thực sự gặp khó khăn. Hãy note lại và áp dụng chúng vào công việc của bạn nhé!

Tìm kiếm trong bảng Excel, hàm VLOOKUP và GLOOKUP

Vai trò của các chức năng này trong cuộc sống Người sử dụng thường xuyên khó đánh giá quá cao. Bây giờ bạn có thể dễ dàng tìm thấy trong bảng dữ liệu lối vào phù hợp và trả về giá trị tương ứng.

Cú pháp của hàm VLOOKUP là: =VLOOKUP( tra cứu_value; bảng tra cứu; đầu ra_column_number; [mapping_type]). Chúng ta hãy xem xét các lập luận:

Giá trị tìm kiếm– giá trị mà chúng ta sẽ tìm kiếm. Đây là một đối số bắt buộc;
Bảng tìm kiếm– mảng ô mà việc tìm kiếm sẽ diễn ra. Cột có giá trị được tìm kiếm phải là cột đầu tiên trong mảng này. Đây cũng là một đối số bắt buộc;
Số cột cần hiển thị– số thứ tự của cột (bắt đầu từ cột đầu tiên trong mảng), từ đó hàm sẽ hiển thị dữ liệu nếu giá trị yêu cầu trùng khớp. Đối số bắt buộc;
Loại phù hợp– chọn “1” (hoặc “TRUE”) cho kết quả khớp yếu, “0” (“FALSE”) cho kết quả khớp hoàn toàn. Đối số là tùy chọn; nếu bị bỏ qua, việc tìm kiếm sẽ được thực hiện trận đấu không nghiêm ngặt.

Tìm kết quả khớp chính xác bằng VLOOKUP

Chúng ta hãy xem một ví dụ về cách hoạt động của hàm VLOOKUP khi loại đối sánh là FALSE, tìm kiếm kết quả khớp chính xác. Mảng B5:E10 cho biết tài sản cố định của một công ty nhất định, giá trị sổ sách, số lượng hàng tồn kho và vị trí của chúng. Ô B2 chứa tên mà bạn cần tìm số hàng tồn kho trong bảng và đặt nó vào ô C2.

Hàm VLOOKUP trong Excel

Hãy viết công thức: =VLOOKUP(B2,B5:E10,3,FALSE) .

Ở đây, đối số đầu tiên chỉ ra rằng trong bảng bạn cần tìm giá trị từ ô B2, tức là. từ "Fax". Đối số thứ hai cho biết bảng cần tìm nằm trong phạm vi B5:E10 và bạn cần tìm từ “Fax” trong cột đầu tiên, tức là. trong mảng B5:B10. Đối số thứ ba cho chương trình biết rằng kết quả của phép tính được chứa trong cột thứ ba của mảng, tức là. D5:D10. Đối số thứ tư là FALSE, tức là một trận đấu hoàn chỉnh là cần thiết.

Và như vậy, hàm sẽ nhận chuỗi “Fax” từ ô B2 và sẽ tìm chuỗi đó trong mảng B5:B10 từ trên xuống dưới. Khi tìm thấy kết quả khớp (dòng 8), hàm sẽ trả về giá trị tương ứng từ cột D, tức là Nội dung D8. Đây chính xác là những gì chúng tôi cần, vấn đề đã được giải quyết.

Nếu không tìm thấy giá trị tìm kiếm, hàm sẽ trả về .

Tìm kết quả khớp không chính xác bằng VLOOKUP

Nhờ tùy chọn này trong công việc VPR, chúng ta có thể tránh được những công thức phức tạp để tìm được kết quả mong muốn.

Trong mảng B5:C12 được chỉ định lãi suất cho vay tùy theo số tiền vay. Trong ô B2, chúng tôi cho biết số tiền cho vay và muốn nhận tỷ giá cho giao dịch đó trong C2. Nhiệm vụ khó khăn ở chỗ số lượng có thể là bất kỳ và khó có thể trùng với số lượng được chỉ định trong mảng; việc tìm kiếm một kết quả khớp chính xác là không phù hợp:

Sau đó, chúng tôi viết công thức cho tìm kiếm không nghiêm ngặt: =VLOOKUP(B2,B5:C12,2,TRUE). Bây giờ, từ tất cả dữ liệu được trình bày trong cột B, chương trình sẽ tìm dữ liệu nhỏ hơn gần nhất. Tức là với số tiền 8.000, giá trị 5.000 sẽ được chọn và tỷ lệ phần trăm tương ứng sẽ được hiển thị.

lỏng lẻo Tìm kiếm VLOOKUP trong Excel

Vì hoạt động chính xác Hàm cần sắp xếp cột đầu tiên của bảng theo thứ tự tăng dần. Nếu không, nó có thể cho kết quả sai.

Hàm GLOOKUP có cú pháp tương tự như VLOOKUP, nhưng tìm kiếm kết quả theo hàng thay vì cột. Nghĩa là, nó quét các bảng không phải từ trên xuống dưới mà từ trái sang phải và hiển thị số hàng được chỉ định chứ không phải cột.

Tìm kiếm dữ liệu bằng hàm VIEW

Hàm LOOKUP hoạt động tương tự như hàm VLOOKUP nhưng có cú pháp khác. Tôi sử dụng nó khi bảng dữ liệu chứa vài chục cột và để sử dụng VLOOKUP bạn cần tính thêm số cột đầu ra. Trong những trường hợp như vậy, chức năng VIEW giúp công việc trở nên dễ dàng hơn. Và như vậy, cú pháp: =XEM( tra cứu_value; mảng_to_search; Mảng_to_display) :

Giá trị tìm kiếm– dữ liệu hoặc liên kết đến dữ liệu cần tìm kiếm;
Mảng để tìm kiếm– một hàng hoặc cột mà chúng ta đang tìm kiếm một giá trị tương tự. Mảng này Hãy chắc chắn sắp xếp theo thứ tự tăng dần;
Mảng để hiển thị– một phạm vi chứa dữ liệu để hiển thị kết quả. Đương nhiên, nó phải có cùng kích thước với mảng tìm kiếm.

Với mục nhập như vậy, bạn không đưa ra liên kết tương đối mảng kết quả. Và bạn chỉ thẳng vào nó, tức là. trước tiên không cần phải tính số cột đầu ra. Chúng tôi sử dụng hàm VIEW trong ví dụ đầu tiên cho hàm VLOOKUP (tài sản cố định, số lượng hàng tồn kho): =XEM(B2,B5:B10,D5:D10). Vấn đề đã được giải quyết thành công!

Hàm VIEW trong Microsoft Excel

Tìm kiếm theo tọa độ tương đối. Hàm MATCH và INDEX

Một cách khác để tìm kiếm dữ liệu là kết hợp các hàm MATCH và INDEX.

Cái đầu tiên được sử dụng để tìm kiếm một giá trị trong một mảng và lấy số sê-ri của nó: SEARCH( tra cứu_value; đã xem_array; [Loại phù hợp] ). Đối số chức năng:

Giá trị tìm kiếm- đối số bắt buộc
Mảng cần xem– một hàng hoặc cột mà chúng ta đang tìm kiếm kết quả khớp. Đối số bắt buộc
Loại phù hợp– chỉ định “0” để tìm kiếm kết quả khớp chính xác, “1” cho kết quả khớp nhỏ hơn gần nhất, “-1” cho kết quả khớp lớn hơn gần nhất. Vì hàm tìm kiếm từ đầu đến cuối danh sách nên khi tìm kiếm nhỏ hơn gần nhất, hãy sắp xếp cột tìm kiếm theo thứ tự giảm dần. Và khi tìm kiếm nhiều hơn, hãy sắp xếp nó theo thứ tự tăng dần.

Vị trí của giá trị yêu cầu đã được tìm thấy, bây giờ bạn có thể hiển thị nó trên màn hình bằng chức năng MỤC LỤC( Mảng; số dòng; [Số_cột]) :

Mảng– đối số chỉ định giá trị sẽ được chọn từ mảng ô nào
Số dòng– cho biết số thứ tự của dòng (bắt đầu từ ô đầu tiên của mảng) mà bạn muốn hiển thị. Tại đây bạn có thể viết giá trị theo cách thủ công hoặc sử dụng kết quả của phép tính hàm khác. Ví dụ: TÌM KIẾM.
Số cột– đối số tùy chọn, được chỉ định nếu mảng bao gồm nhiều cột. Nếu một đối số bị bỏ qua, công thức sẽ sử dụng cột đầu tiên của bảng.

Bây giờ hãy kết hợp các chức năng này để có được kết quả:

Hàm MATCH và INDEX trong Excel

Đây là những phương pháp tìm kiếm và hiển thị dữ liệu có sẵn trên Excel. Hơn nữa, bạn có thể sử dụng chúng trong tính toán, sử dụng chúng trong bài thuyết trình, thực hiện các thao tác với chúng, chỉ định chúng làm đối số cho các hàm khác, v.v.

Bạn có cảm thấy kiến thức và kỹ năng của mình đang phát triển và củng cố như thế nào không? Vậy thì đừng dừng lại, hãy tiếp tục đọc! Trong bài tiếp theo chúng ta sẽ xem xét: nó sẽ khó khăn và thú vị!

Nếu bạn tìm kiếm các hàm ký tự đại diện, Google sẽ hiển thị rằng VLOOKUP phổ biến hơn hàm INDEX rất nhiều. Điều này có thể hiểu được, vì để cung cấp cho hàm INDEX chức năng tương tự như VLOOKUP, bạn cần sử dụng một công thức khác - TÌM KIẾM. Đối với tôi, việc thử và thành thạo hai tính năng mới cùng một lúc luôn là một thử thách. Nhưng chúng mang lại nhiều cơ hội và tính linh hoạt hơn trong việc tạo ra bảng tính. Nhưng điều đầu tiên trước tiên.

Hàm VLOOKUP()

Giả sử bạn có một bảng chứa dữ liệu nhân viên. Cột đầu tiên lưu trữ mã số nhân viên, phần còn lại - dữ liệu khác (tên đầy đủ, bộ phận, v.v.). Nếu bạn có mã số nhân sự, bạn có thể sử dụng hàm VLOOKUP để trả về thông tin nhất định về nhân viên. Cú pháp công thức =VLOOKUP(giá_trị tra cứu, bảng, số_cột, [khoảng_tra cứu]). Cô ấy nói với Excel: "Tìm trong bàn một hàng có ô đầu tiên khớp giá trị tìm kiếm, và trả về giá trị của ô có số sê-ri số_cột».

Nhưng có những trường hợp bạn có tên nhân viên và bạn cần trả lại mã số nhân sự. Trong hình ở ô A10 là tên nhân viên và bạn cần xác định mã số nhân sự ở ô B10.

Khi trường khóa ở bên phải dữ liệu bạn muốn, VLOOKUP sẽ không giúp ích gì. Tất nhiên, nếu có thể hỏi số_cột-1 thì sẽ không có vấn đề gì. Một giải pháp phổ biến là thêm cột A mới, sao chép tên nhân viên vào cột này, điền mã số nhân sự bằng sử dụng VLOOKUP, lưu chúng dưới dạng giá trị và xóa cột tạm thời A.

hàm INDEX()

Để giải quyết vấn đề trong một bước, chúng ta cần sử dụng công thức INDEX và MATCH. Độ phức tạp cách tiếp cận này là bạn cần sử dụng hai chức năng mà có thể bạn chưa từng sử dụng trước đây. Để đơn giản hóa sự hiểu biết, chúng tôi sẽ giải quyết vấn đề này theo hai giai đoạn.

Hãy bắt đầu với hàm INDEX. Một cái tên khủng khiếp. Khi ai đó nói “chỉ mục”, không một liên tưởng nào hiện lên trong đầu tôi về chức năng này. Và nó đòi hỏi ba đối số: =INDEX(mảng, số_hàng, [số_cột]).

Nói một cách đơn giản, Excel đi vào một mảng dữ liệu và trả về giá trị tại giao điểm của một hàng và cột được chỉ định. Nó có vẻ đơn giản. Do đó, công thức =INDEX($A$2:$C$6,4,2) sẽ trả về giá trị tìm thấy trong ô B5.

Liên quan đến vấn đề của chúng ta, để trả về mã số nhân sự của nhân viên, công thức sẽ như thế này =INDEX($A$2:$A$6;?;1). Nghe thì có vẻ vô nghĩa nhưng nếu thay thế dấu chấm hỏi bằng công thức MATCH thì chúng ta sẽ có lời giải.

Hàm MATCH()

Cú pháp của hàm này là: =MATCH(giá trị tra cứu, mảng tra cứu, [loại_khớp]).

Cô ấy nói với Excel: "Tìm tra cứu_value trong mảng dữ liệu và trả về số hàng của mảng chứa giá trị này." Do đó, để tìm tên nhân viên ở dòng nào trong ô A10, bạn cần viết công thức =MATCH(A10, $B$2:$B$6, 0). Nếu tên "Colin Farel" nằm trong ô A10 thì MATCH sẽ trả về hàng thứ 5 của mảng B2:B6.

Ừm, về cơ bản thì chỉ vậy thôi. Hàm MATCH cho hàm INDEX biết hàng nào cần tìm giá trị. Thay thế dấu chấm hỏi trong công thức INDEX bằng công thức TÌM KIẾM và bạn sẽ có được kết quả tương đương với VLOOKUP với khả năng tìm kiếm dữ liệu nằm ở bên trái của cột khóa. Nó sẽ diễn ra như thế này:

INDEX($A$2:$B$6,MATCH(A10,$B$2:$B$6,0),1)

Lúc đầu, điều đó thật bất thường, có một sự cám dỗ rất lớn là chèn một cột khác và làm việc như mọi khi. Nhưng theo thời gian, việc sử dụng hàm INDEX đã trở thành thói quen. Hóa ra nó nhanh hơn và đòi hỏi ít thao tác hơn. Vì vậy, lần tới khi bạn muốn đặt số cột thành số âm trong VLOOKUP, hãy sử dụng kết hợp hai hàm lạ, INDEX và MATCH, để giải quyết vấn đề của bạn.