Chức năng tương tự như ex. Phạm vi được đặt tên và toán tử giao lộ. Tìm kết quả khớp chính xác bằng VLOOKUP

Tất cả chúng ta đều biết nó hữu ích như thế nào. Có lẽ một nửa số hành động trong Excel được thực hiện bằng cách sử dụng nó. Tuy nhiên Chức năng này có một số hạn chế. Ví dụ: hàm VLOOKUP chỉ xem xét cột ngoài cùng bên trái trong bảng hoặc chỉ tìm kiếm một điều kiện. Nhưng nếu chúng ta cần trả về một giá trị thỏa mãn hai điều kiện thì sao? Trong trường hợp này, chúng ta sẽ phải dùng đến một số thủ thuật. Chúng ta sẽ nói về những thủ thuật này trong bài viết hôm nay.

Vì vậy, chúng ta sẽ xem xét bốn tùy chọn để tạo hàm thay thế với hai điều kiện:

Sử dụng hàm SUMPRODVEL

Chà, chúng ta sẽ bắt đầu với điều đơn giản nhất.

Sử dụng cột bổ sung

Trong hầu hết các trường hợp vấn đề phức tạp trở nên dễ dàng và dễ quản lý hơn khi được chia thành nhiều phần nhỏ. Điều tương tự cũng áp dụng khi xây dựng công thức trong Excel.

Hãy xem một ví dụ cổ điển. Chúng tôi có một bảng với doanh số bán hàng theo tháng và thành phố. Và chúng ta cần xác định giá trị bán hàng tương ứng với hai điều kiện: tháng - Tháng 2 và thành phố - Samara.

Sử dụng hàm VLOOKUP trong hình thức cổ điển sẽ không giúp ích gì cho chúng ta vì nó sẽ có thể trả về một giá trị chỉ khớp với một điều kiện. Một cột bổ sung sẽ giúp chúng ta thoát khỏi tình trạng này, trong đó chúng ta sẽ kết hợp các giá trị của cột Tháng và Thành phố. Để thực hiện việc này, hãy viết công thức =B2&C2 vào ô A2 và mở rộng công thức này sang ô A13. Bây giờ chúng ta có thể sử dụng các giá trị của cột A để trả về giá trị được yêu cầu. Chúng ta viết công thức ở ô G3:

VLOOKUP(G1A2:D13;4;0)

Công thức này kết hợp hai điều kiện của ô G1 và G2 thành một hàng và xem xét nó trong cột A. Sau Điều kiện cần thiếtđược tìm thấy, công thức trả về giá trị từ cột thứ tư của bảng A1:D13, tức là. cột Việc bán hàng.

Sử dụng hàm SELECT để tạo bảng tra cứu mới

Nếu vì lý do nào đó việc sử dụng cột bổ sung không phải là một lựa chọn đối với chúng tôi, chúng tôi có thể sử dụng .

Sử dụng chức năng SELECT liên quan đến việc tạo bảng mớiđể xem các giá trị cột ở đâu Tháng Và Thành phốđã sáp nhập rồi. Công thức của chúng tôi sẽ trông như thế này:

VLOOKUP(G1SELECT((1,2),B2:B13&C2:C13,D2:D13),2,0)

Điểm chính của công thức này là phần CHOICE((1;2);B2:B13&C2:C13;D2:D13), thực hiện hai việc:

Kết hợp các giá trị cột Tháng Và Thành phố trong một mảng: JanMoscow, FebMoscow...
Kết hợp hai mảng thành một bảng hai cột.

Kết quả của hàm này sẽ là một bảng trông như thế này:

Bây giờ công thức đã trở nên rõ ràng hơn.

QUAN TRỌNG: Vì chúng ta đã sử dụng công thức mảng nên khi bạn nhập xong công thức, hãy nhấn Ctrl+Shift+Enter để cho chương trình biết ý định của chúng ta. Sau khi nhấn tổ hợp phím này, chương trình sẽ tự động cài đặt dấu ngoặc nhọn ở đầu và cuối công thức.

Sử dụng hàm INDEX và MATCH

Phương pháp thứ ba mà chúng ta sẽ xem xét cũng liên quan đến việc sử dụng công thức mảng và sử dụng các hàm INDEX và SEARCH.

Công thức sẽ trông như thế này.

CHỈ SỐ(D2:D13,MATCH(1,(B2:B13=G1)*(C2:C13=G2),0))

Chúng ta hãy xem từng phần của công thức này có tác dụng gì.

Đầu tiên, hãy xem xét hàm MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). TRONG trong trường hợp này tuần tự so sánh giá trị của ô G1 với từng giá trị của ô trong phạm vi B2:B13 và trả về TRUE nếu các giá trị khớp và FALSE nếu không khớp. Sự so sánh tương tự được thực hiện với giá trị của ô G2 và phạm vi C2:C13. Tiếp theo chúng ta so sánh cả hai mảng này bao gồm TRUE và FALSE. Tổ hợp TRUE * TRUE cho ta kết quả 1 (TRUE). Chúng ta hãy nhìn vào hình ảnh dưới đây sẽ giúp giải thích nguyên lý hoạt động rõ ràng hơn.

Bây giờ chúng ta có thể biết chuỗi thỏa mãn cả hai điều kiện nằm ở đâu. Hàm MATCH tìm vị trí của số 1 trong mảng kết quả và trả về 6 vì số 1 xuất hiện ở hàng thứ sáu. Tiếp theo, hàm INDEX trả về giá trị của hàng thứ sáu của phạm vi D2:D13.

Sử dụng TÓM TẮT

Một trong những người mạnh mẽ nhất Công thức Excel. Tôi thậm chí còn có một bài viết riêng dành riêng cho công thức này. Cách thứ tư để sử dụng nhiều điều kiện là viết công thức bằng hàm SUMPRODVEL. Và nó sẽ trông như thế này:

TỔNG HỢP((B2:B13=G1)*(C2:C13=G2);D2:D13)

Nguyên lý hoạt động của công thức này tương tự như nguyên lý hoạt động của phương pháp trước. Tạo bảng ảo, so sánh giá trị của các ô G1 và G2 với các phạm vi B2:B13 và C2:C13 tương ứng. Tiếp theo, cả hai mảng này được so sánh và thu được một mảng gồm các số 1 và 0, trong đó một mảng được gán cho hàng trong đó cả hai điều kiện đều khớp nhau. Tiếp theo, mảng ảo này được nhân với phạm vi D2:D13. Vì mảng ảo của chúng ta sẽ chỉ có một số 1 ở hàng thứ sáu nên công thức sẽ trả về kết quả 189.

Chức năng này sẽ không hoạt động nếu có giá trị văn bản trong phạm vi D2:D13.

Để hiểu nó hoạt động như thế nào công thức này, Tôi khuyên bạn nên đọc bài viết về hàm SUMPROIZ.

KẾT QUẢ

Vậy bạn nên sử dụng phương pháp nào? Mặc dù tất cả chúng đều hoạt động đáng tin cậy nhưng tôi thích phương pháp đầu tiên hơn. Trong của anh ấy công việc hàng ngày, Tôi thích làm việc với các tệp dễ hiểu và có thể thay đổi. Cả hai yêu cầu này đều đáp ứng các điều kiện của phương pháp đầu tiên.

Để hiểu rõ hơn về các công thức, bạn có thể xem chúng trong bài viết hôm nay.

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 thuận tiện lắm. 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 tra cứu ngược, chỉ cần nhập 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ều ngược lại là đúng với rLOOKUP, theo mặc định 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 số cột được chỉ định trong các đối số số âm-2, cho phép bạn tìm kiếm ở 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!

Lời nói đầu cần thiết

Nếu bạn chưa từng làm việc với chức năng này trước đây VLOOKUP, thì bạn đã thua rất nhiều, tôi thực sự khuyên bạn nên đọc nó trước.

Vấn đề

Như nhiều người đã biết, chức năng VLOOKUP có thể trả về dưới dạng các giá trị kết quả nằm hoàn toàn ở bên phải của cột nơi thực hiện tìm kiếm. Ôi, sẽ tuyệt vời biết bao nếu đối số thứ ba của hàm này (số cột mà các giá trị được trả về) có thể được đặt thành âm, nhưng không.

Trong thực tế, các tình huống thường phát sinh khi bạn cần tìm kiếm dữ liệu trong một cột nằm ở bên phải chứ không phải ở bên trái của cột kết quả, ví dụ:

Thật dễ dàng để tìm thấy chi phí theo mã đơn hàng - hàm VLOOKUP thông thường sẽ trợ giúp ở đây một hoặc hai lần. Nhưng làm sao để tìm được tên sản phẩm theo mã? Tại các khóa đào tạo, tôi thường nghe thấy câu hỏi này trong công thức “làm thế nào để thực hiện VLOOKUP bên trái”?

Hãy xem xét một số cách.

Cách 1. Tấn công trực diện

Nếu bạn làm theo nguyên tắc của Occam và không làm phức tạp nó một cách không cần thiết, bạn chỉ cần sao chép cột mong muốn sang bên phải (hoặc tạo liên kết) và sử dụng cột như thông thường. VLOOKUP:

Rẻ và vui vẻ, nhưng yêu cầu hoàn thiện bàn bằng tay. Ngoài ra, thường có những trường hợp không thể thay đổi một bảng: nó được bảo vệ bằng mật khẩu, điều này mẫu công ty, bảng trong truy cập công cộng vân vân. Khi đó cần có một cách tiếp cận khác.

Cách 2. Sắp xếp lại ảo các cột bằng hàm SELECT

Nếu không thể sắp xếp lại các cột trên trang tính thì việc này có thể được thực hiện qua mạng, tức là. "nhanh chóng" ngay trong công thức. Đối với điều này, chúng ta cần một chức năng CHỌN. Mục đích chính của nó là chọn phần tử mong muốn từ danh sách theo một số nhất định. Ví dụ: nó có thể được sử dụng để thay thế số ngày trong tuần bằng văn bản tương ứng của nó:

Thoạt nhìn không có gì siêu nhiên, nhưng có một số khoảnh khắc khó khăn ở đây.

Trước hết, thay vì tên văn bản của các thành phần danh sách đã chọn ("Thứ Hai", "Thứ Ba", v.v.), bạn có thể sử dụng địa chỉ phạm vi. Và sau đó hàm sẽ trả về một liên kết đến phạm vi đã chọn. Vì vậy, ví dụ, công thức:

CHỌN(2; A1:A10 ; D1:D10 ; B1:B10 )

Thứ hai, thay vì một số đơn giản của phần tử cần truy xuất trong đối số đầu tiên của hàm SỰ LỰA CHỌN Bạn có thể chỉ định một mảng các hằng số trong dấu ngoặc nhọn, ví dụ như sau:

SỰ LỰA CHỌN( {1;2} ; A1:A10 ; Đ1:D10 ; B1:B10)

Sau đó, ở đầu ra, chúng ta sẽ nhận được hai phạm vi đầu tiên (A1:A10 và D1:D10), được dán lại với nhau thành một tổng thể duy nhất.

Và bây giờ tất cả điều này có thể được đặt bên trong của chúng tôi VLOOKUPđể thực hiện "tìm kiếm bên trái":

Nó khác với “VLOOKUP cổ điển”, như bạn có thể thấy, chỉ ở chỗ phạm vi được chỉ định bằng cách dán hai cột Mã đặt hàng Và Sản phẩm sử dụng chức năng SỰ LỰA CHỌN. Nếu không thì mọi thứ vẫn như bình thường.

Nhược điểm của phương pháp này là tốc độ (chậm hơn khoảng 5-7 lần so với VLOOKUP thông thường) và một số đồng nghiệp chưa quen (và có thể đây thậm chí còn là một điểm cộng!)

Cách 3. Liên kết hàm INDEX và SEARCH

Nếu bạn không sử dụng hàm VLOOKUP, bạn có thể sử dụng chức năng tương tự mạnh mẽ hơn của nó - sự kết hợp của hai hàm rất hữu ích MỤC LỤC Và CUỘC THI ĐẤU:

Chức năng TÌM KIẾM tìm kiếm đặt giá trị(C2, tức là mã của thứ tự chúng ta cần) trong phạm vi một chiều (cột mã trong bảng C10: C25) và kết quả là số sê-ri của ô nơi nó tìm thấy thứ chúng ta đang tìm kiếm - trong trường hợp của chúng tôi nó sẽ là số 4, bởi vì Mã đơn hàng chúng tôi cần ở vị trí thứ tư trong bảng.

Và sau đó chức năng phát huy tác dụng MỤC LỤC, có thể trích xuất dữ liệu từ một mảng cột dọc (tên sản phẩm trong B10:B25) theo số sê-ri (mà nó đã tìm thấy trước đó TÌM KIẾM). Như vậy, MỤC LỤC sẽ cung cấp cho chúng tôi nội dung của ô thứ tư tính từ cột Sản phẩm, đó là những gì được yêu cầu.

So sánh với cách trước đó, tùy chọn này được tính toán lại nhanh hơn nhiều (gần như nhanh như VLOOKUP thông thường), điều này rất quan trọng đối với các bảng lớn.

Tôi đã phân tích một ví dụ tương tự (có video) trước đó. Và về chức năng MỤC LỤC bạn có thể nói :)

Cách 4. Hàm SUMIF(MN)

Nếu bạn cần trích xuất chính xác một số từ một bảng (ví dụ: thể tích tính bằng lít), thì đôi khi, việc sử dụng hàm tính tổng chọn lọc để thực hiện “VLOOKUP bên trái” sẽ dễ dàng hơn SUMIF hoặc chị gái của nó - chức năng TỔNG KẾT:

Nhược điểm của phương pháp này là rõ ràng - nó chỉ hoạt động với các số và với điều kiện là không có giá trị trùng lặp trong cột. Nếu có các bản sao (một số đơn hàng có cùng mã), thì hàm này sẽ cộng tất cả các tập và sẽ không trả về tập đầu tiên, như VLOOKUP sẽ làm. Chà, tốc độ của phương pháp này cũng không tốt lắm - chậm hơn khoảng 3-4 lần so với VLOOKUP thông thường.

Bài viết lớn về hàm đếm chọn lọc dựa trên một hoặc nhiều điều kiện.

Cách 5: Hàm macro tạo sẵn từ PLEX

Nếu bạn không ngại sử dụng macro thì bạn có thể sử dụng macro làm sẵn chức năng tùy chỉnh TRÊN Ngôn ngữ lập trình, được bao gồm trong Microsoft Excel. So với VLOOKUP thông thường, nó có thể:

tìm kiếm trong nhiều cột cùng một lúc (tối đa 3)
tạo ra kết quả từ bất kỳ cột nào (bên trái hoặc bên phải - không thành vấn đề)
xuất ra không chỉ giá trị đầu tiên gặp phải mà còn giá trị được yêu cầu theo thứ tự
bạn có thể chỉ định nội dung sẽ hiển thị nếu không tìm thấy gì thay vì lỗi #N/A

Phương pháp này có hai nhược điểm: bạn cần lưu tệp có hỗ trợ macro (XLSM) và tốc độ của bất kỳ chức năng macro nào không cao lắm - ở mức bàn lớn có thể chậm lại đáng kể.

Nhưng là một trong những lựa chọn, nó sẽ hoạt động :)

Liên kết liên quan

Cách tuyệt vời. Cảm ơn.
Dạy: Làm thế nào bạn có thể sử dụng tìm kiếm chỉ mục và vị trí để chọn các giá trị từ cột đầu tiên khi bạn đang tìm kiếm ở cột thứ hai, nhưng đồng thời bạn cần tìm các giá trị ở cột thứ hai có ngày tối đa trong thứ tư?
Tôi nghĩ đến hàm lớn nhất, nhưng chèn nó vào đâu...))) đây là một bảng

tuyến đường	eid	tiếp đầu ngữ	sự chi trả
tây2	251458	BR7652I	16.01.2017
Kashira1	252412	BR7652I	17.01.2017
S5-1	253016	BR7272I	18.01.2017
S3-1	254392	BR7652I	19.01.2017
Kashira1	255031	BR7249I	20.01.2017
Kashira1	257321	BR7759I	21.01.2017
SV1-1	257569	BR7761I	22.01.2017
Pushkino1	259373	BR7647I	23.01.2017
Kolomna1	259591	BR7315I	24.01.2017
Kashira1	260300	BR7544I	25.01.2017

đó là những gì tôi đang tìm kiếm

tiếp đầu ngữ	eid
BR7652I	CHỈ SỐ(A2:D11,MATCH(D20,C2:C11,0),MATCH(E19,A1:D1,0))

nhưng tôi cần kết quả có ngày thanh toán tối đa chứ không phải ngày đầu tiên theo thứ tự

Khi bạn cần tìm dữ liệu theo hàng trong bảng hoặc dải ô, hãy sử dụng hàm VLOOKUP, một trong các hàm liên kết và tìm kiếm. Ví dụ: bạn có thể tìm thấy giá những bộ phận xe hơi theo số của cô ấy.

Khuyên bảo: Hãy xem những video này để có được Thông tin thêm về hàm VLOOKUP!

Hàm VLOOKUP đơn giản nhất có nghĩa như sau:

VLOOKUP(giá trị tìm kiếm; phạm vi tìm kiếm giá trị; số cột trong phạm vi có giá trị được trả về; kết quả khớp chính xác hoặc gần đúng - được chỉ định là 0/FALSE hoặc 1/TRUE).

Khuyên bảo: Chức năng bí mật VLOOKUP đang sắp xếp dữ liệu sao cho giá trị tra cứu (trái cây) xuất hiện ở bên trái giá trị (giá trị) trả về mà bạn muốn tìm.

Sử dụng hàm VLOOKUP để tra cứu một giá trị trong bảng.

Cú pháp

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

Ví dụ:

VLOOKUP(105,A2:C7,2,TRUE)

VLOOKUP("Ivanov";B2:E7;2;FALSE)

Tên đối số	Sự miêu tả
tra cứu_value(yêu cầu)	Giá trị tìm kiếm. Giá trị bạn đang tìm kiếm phải nằm ở cột đầu tiên của phạm vi ô được chỉ định trong *bàn* . Ví dụ, nếu *Bảng mảng* bao gồm các ô B2:D7 thì lookup_value phải nằm trong cột B. Xem hình bên dưới. *Tìm kiếm_giá trị* có thể là một giá trị hoặc một tham chiếu ô.
bàn(yêu cầu)	Phạm vi ô mà tìm kiếm sẽ được thực hiện *giá trị tìm kiếm* và giá trị trả về bằng hàm VLOOKUP. Cột đầu tiên trong phạm vi ô phải chứa *tra cứu_value* (ví dụ: họ, như trong hình bên dưới). Phạm vi ô cũng phải chứa giá trị trả về (chẳng hạn như tên như trong hình bên dưới) mà bạn muốn tìm.
số_cột(yêu cầu)	Số cột (bắt đầu bằng 1 cho cột ngoài cùng bên trái *những cái bàn* ) chứa giá trị trả về.
khoảng_view(không bắt buộc)	Giá trị Boolean chỉ định nội dung khớp với hàm cần tìm VLOOKUP, - gần đúng hoặc chính xác. Lựa chọn ĐÚNG VẬY giả định rằng cột đầu tiên trong bảng được sắp xếp theo thứ tự thứ tự ABC hoặc theo số, sau đó tìm kiếm giá trị gần nhất. Đây là phương pháp mặc định trừ khi được chỉ định. Lựa chọn NÓI DỐI tìm kiếm giá trị chính xácở cột đầu tiên.

Bắt đầu công việc

Để xây dựng cú pháp hàm VLOOKUP bạn sẽ cần các thông tin sau:

Giá trị bạn cần tìm chính là giá trị bạn đang tìm.

Phạm vi chứa giá trị tìm kiếm. Hãy nhớ điều đó cho vận hành chính xác Trong hàm VLOOKUP, giá trị bạn đang tìm kiếm phải luôn nằm ở cột đầu tiên của dải ô. Ví dụ: nếu giá trị bạn đang tìm kiếm nằm trong ô C2 thì phạm vi phải bắt đầu từ C.

Số cột trong phạm vi chứa giá trị trả về. Ví dụ: nếu bạn chỉ định B2:D11 làm phạm vi của mình, bạn sẽ coi B là cột đầu tiên, C là cột thứ hai, v.v.

Theo tùy chọn, bạn có thể chỉ định từ TRUE nếu bạn muốn kết quả khớp gần đúng hoặc từ FALSE nếu bạn muốn kết quả khớp chính xác với giá trị trả về. Nếu bạn không chỉ định bất cứ điều gì thì giá trị mặc định luôn là TRUE, đây là kết quả khớp gần đúng.

Bây giờ kết hợp tất cả các đối số trên như sau:

VLOOKUP(giá trị tìm kiếm; dải ô có giá trị tìm kiếm; số cột trong dải ô có giá trị trả về; tùy chọn TRUE cho kết quả khớp gần đúng hoặc FALSE cho kết quả khớp chính xác).

Ví dụ

Dưới đây là một số ví dụ về hàm VLOOKUP:

ví dụ 1

Ví dụ 2

Ví dụ 3

Ví dụ 4

Ví dụ 5

Những vấn đề chung

Vấn đề	Lý do có thể
Giá trị trả về không hợp lệ	Nếu lập luận *khoảng_view* là TRUE hoặc không được chỉ định, cột đầu tiên phải được sắp xếp theo thứ tự bảng chữ cái hoặc số. Nếu cột đầu tiên không được sắp xếp thì giá trị trả về có thể không mong đợi. Sắp xếp cột đầu tiên hoặc sử dụng FALSE để khớp chính xác.
#N/A trong ô	Nếu lập luận *khoảng_view* đánh giá là TRUE và giá trị của đối số *tra cứu_value* nhỏ hơn giá trị nhỏ nhất ở cột đầu tiên *những cái bàn* , giá trị lỗi #N/A sẽ được trả về. Nếu lập luận *khoảng_view* là FALSE, giá trị lỗi #N/A có nghĩa là không thể tìm thấy số chính xác. thông tin thêmĐể khắc phục lỗi #N/A trong hàm VLOOKUP, hãy xem bài viết Sửa lỗi #N/A trong hàm VLOOKUP.
	Nếu giá trị " *Cột_số* " lớn hơn số cột trong *bàn* , bạn sẽ nhận được #REF! Nếu không, TE102825393 sẽ tạo ra lỗi “#VALUE!”
#GIÁ TRỊ! trong một tế bào	Nếu như *bảng thông tin* nhỏ hơn 1, bạn sẽ nhận được #VALUE! Nếu không, TE102825393 sẽ tạo ra lỗi “#VALUE!” Thông tin thêm về cách khắc phục sự cố #VALUE! trong hàm VLOOKUP, hãy xem bài viết Sửa lỗi #VALUE! trong hàm VLOOKUP.
#TÊN? trong một tế bào	Giá trị lỗi #NAME? thường xuất hiện nhất khi thiếu dấu ngoặc kép trong công thức. Khi tìm kiếm tên nhân viên, hãy đảm bảo rằng tên trong công thức nằm trong dấu ngoặc kép. Ví dụ: trong hàm =VLOOKUP("Ivanov";B2:E7;2;FALSE), tên phải được chỉ định theo định dạng "Ivanov" và không có gì khác.

Hoạt động	Kết quả
	Cách sử dụng liên kết tuyệt đối cho phép bạn điền vào một công thức để nó luôn hiển thị cùng một phạm vi thay thế chính xác.
Không lưu giá trị số hoặc ngày dưới dạng văn bản.	Khi tìm kiếm Giá trị kiểu số hoặc giá trị ngày tháng, hãy đảm bảo dữ liệu nằm ở cột đầu tiên của đối số *bàn* không phải là giá trị văn bản. Nếu không, hàm VLOOKUP có thể trả về giá trị không chính xác hoặc không mong muốn.
Sắp xếp cột đầu tiên	Nếu để tranh luận *khoảng_view* được chỉ định là TRUE, sắp xếp cột đầu tiên trước khi sử dụng hàm VLOOKUP những cái bàn .
Sử dụng ký tự đại diện	Nếu như *Thời gian trôi đi_view* điều quan trọng là sự dối trá, và *Tìm kiếm_giá trị* - văn bản, ký tự đại diện có thể được sử dụng - dấu chấm hỏi(_km_) và dấu hoa thị () - trong Tìm kiếm_giá trị* . Dấu chấm hỏi khớp với một ký tự. Dấu hoa thị khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hoặc dấu hoa thị thực sự, hãy nhập dấu ngã (~) trước ký tự. Ví dụ, sử dụng chức năng =VLOOKUP("Phông chữ?",B2:E7,2,FALSE) Bạn có thể tìm kiếm tất cả các cách sử dụng họ Ivanov ở nhiều dạng trường hợp khác nhau.
Đảm bảo dữ liệu không chứa các ký tự sai.	Khi tìm kiếm giá trị văn bản Trong cột đầu tiên, hãy đảm bảo rằng dữ liệu không chứa dấu cách ở đầu hoặc cuối, dấu ngoặc thẳng (" hoặc ") hoặc dấu ngoặc kép (' hoặc ") hoặc ký tự không in không hợp lệ. Trong những trường hợp này, hàm VLOOKUP có thể trả về giá trị không mong muốn. Để có được kết quả chính xác, hãy thử sử dụng các hàm

VLOOKUP là một hàm Excel cho phép bạn tìm kiếm một cột cụ thể bằng cách sử dụng dữ liệu trong cột khác. Hàm VLOOKUP trong Excel còn dùng để chuyển dữ liệu từ bảng này sang bảng khác. Có ba điều kiện:

Các bảng phải được đặt trong một Sổ làm việc Excel.
Bạn chỉ có thể tìm kiếm trong số dữ liệu tĩnh (không phải công thức).
Cụm từ tìm kiếm phải nằm trong cột đầu tiên của dữ liệu được sử dụng.

Công thức VLOOKUP trong Excel

Cú pháp VLOOKUP trong Excel được Nga hóa, nó trông giống như:

VLOOKUP (tiêu chí tìm kiếm; phạm vi dữ liệu; số cột có kết quả; điều kiện tìm kiếm)

Các đối số cần thiết để tìm kiếm kết quả cuối cùng được chỉ định trong ngoặc đơn.

Tiêu chí tìm kiếm

Địa chỉ ô bảng tính Excel, trong đó chỉ định dữ liệu cần tìm kiếm trong bảng.

Phạm vi dữ liệu

Tất cả các địa chỉ trong đó việc tìm kiếm được thực hiện. Cột đầu tiên phải là cột chứa tiêu chí tìm kiếm.

Số cột cho tổng giá trị

Số cột mà giá trị sẽ được lấy nếu tìm thấy kết quả khớp.

Điều kiện tìm kiếm

Giá trị Boolean (true/1 hoặc false/0) chỉ định xem nên tìm kết quả khớp gần đúng (1) hay kết quả khớp chính xác (0).

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

Nguyên lý hoạt động của chức năng rất đơn giản. Đối số đầu tiên chứa tiêu chí tìm kiếm. Ngay sau khi tìm thấy kết quả khớp trong bảng (đối số thứ hai), thông tin sẽ được lấy từ cột mong muốn (đối số thứ ba) của hàng tìm thấy và chèn vào ô có công thức.
Đơn giản ứng dụng VPR– tìm kiếm các giá trị trong bảng Excel. Nó có ý nghĩa trong khối lượng lớn dữ liệu.

Hãy tìm số lượng sản phẩm thực sự được phát hành theo tên của tháng.
Kết quả sẽ được hiển thị ở bên phải của bảng. Tại ô có địa chỉ H3 chúng ta sẽ nhập giá trị mong muốn. Trong ví dụ, tên của tháng sẽ được chỉ định ở đây.
Trong ô H4, chúng ta nhập chính hàm đó. Việc này có thể được thực hiện thủ công hoặc bạn có thể sử dụng trình hướng dẫn. Để gọi, đặt con trỏ lên ô H4 và nhấp vào biểu tượng Fx bên cạnh thanh công thức.

Một cửa sổ thuật sĩ sẽ mở ra Hàm Excel. Bạn cần tìm VLOOKUP trong đó. Chọn “Danh sách theo thứ tự bảng chữ cái đầy đủ” từ danh sách thả xuống và bắt đầu nhập VLOOKUP. Đánh dấu chức năng tìm thấy và nhấp vào OK.

Một cửa sổ VLOOKUP sẽ xuất hiện để bảng excel.

Để chỉ định đối số đầu tiên (tiêu chí), đặt con trỏ ở hàng đầu tiên và nhấp vào ô H3. Địa chỉ của cô ấy sẽ xuất hiện trong dòng. Để chọn một phạm vi, đặt con trỏ vào dòng thứ hai và bắt đầu chọn bằng chuột. Cửa sổ sẽ được thu nhỏ thành một dòng. Điều này được thực hiện để cửa sổ không cản trở khả năng nhìn toàn bộ phạm vi và không cản trở hành động của bạn.

Khi bạn đã chọn xong và thả nút trái chuột, cửa sổ sẽ trở về trạng thái bình thường và địa chỉ của dải ô sẽ xuất hiện ở dòng thứ hai. Nó được tính từ ô trên cùng bên trái đến ô dưới cùng bên phải. Địa chỉ của chúng được phân tách bằng toán tử “:” - tất cả địa chỉ giữa địa chỉ đầu tiên và địa chỉ cuối cùng đều được lấy.

Di chuyển con trỏ đến dòng thứ ba và đọc dữ liệu sẽ được lấy từ cột nào nếu tìm thấy kết quả khớp. Trong ví dụ của chúng tôi nó là 3.

Để trống dòng cuối cùng. Theo mặc định, giá trị sẽ là 1, hãy xem hàm của chúng ta sẽ xuất ra giá trị nào. Bấm vào đồng ý.

Kết quả là nản lòng. "Không áp dụng" có nghĩa là dữ liệu không hợp lệ cho chức năng. Chúng tôi chưa chỉ định giá trị trong ô H3 và hàm đang tìm kiếm giá trị trống.

Nhập tên của tháng và giá trị sẽ thay đổi.

Chỉ có điều nó không tương ứng với thực tế, vì số lượng sản phẩm thực tế sản xuất trong tháng 1 là năm 2000.
Đây là tác dụng của đối số Điều kiện tìm kiếm. Hãy thay đổi nó thành 0. Để thực hiện việc này, hãy đặt con trỏ lên ô có công thức và nhấn Fx lần nữa. Trong cửa sổ mở ra, nhập “0” vào dòng cuối cùng.

Nhấp vào "OK". Như bạn có thể thấy, kết quả đã thay đổi.

Để kiểm tra điều kiện thứ hai từ đầu bài viết (hàm không tìm kiếm trong các công thức), hãy thay đổi điều kiện của hàm. Hãy tăng phạm vi và cố gắng lấy giá trị từ một cột có các giá trị được tính toán. Nhập các giá trị như trong ảnh chụp màn hình.

Bấm vào đồng ý. Như bạn có thể thấy, kết quả tìm kiếm hóa ra là 0, mặc dù giá trị trong bảng là 85%.

VLOOKUP trong Excel chỉ “hiểu” giá trị cố định.

So sánh dữ liệu từ hai bảng Excel

VLOOKUP trong Excel có thể được sử dụng để so sánh dữ liệu từ hai bảng. Ví dụ: giả sử chúng ta có hai trang chứa dữ liệu về sản phẩm đầu ra từ hai xưởng. Chúng ta có thể sánh đôi phát hành thực tế cho cả hai. Hãy để chúng tôi nhắc bạn rằng để chuyển đổi giữa các trang tính, hãy sử dụng các phím tắt của chúng ở cuối cửa sổ.

Trên hai trang tính, chúng ta có các bảng giống hệt nhau với dữ liệu khác nhau.

Như bạn có thể thấy, kế hoạch phát hành của họ giống nhau, nhưng kế hoạch thực tế thì khác. Chuyển đổi và so sánh từng dòng ngay cả đối với khối lượng nhỏ dữ liệu rất bất tiện. Trên trang thứ ba, tạo một bảng có ba cột.

Trong ô B2, chúng ta nhập hàm VLOOKUP. Đối số đầu tiên, chúng ta sẽ chỉ ra ô có tháng trên trang tính hiện tại và chọn phạm vi từ trang tính “Workshop1”. Để ngăn phạm vi dịch chuyển khi sao chép, hãy nhấn F4 sau khi chọn phạm vi. Điều này sẽ làm cho liên kết trở nên tuyệt đối.

Mở rộng công thức để bao gồm toàn bộ cột.

Tương tự, nhập công thức vào cột tiếp theo, chỉ chọn phạm vi trên bảng “Workshop2”.

Sau khi sao chép, bạn sẽ nhận được báo cáo tóm tắt từ hai tờ.

Thay thế dữ liệu từ bảng Excel này sang bảng Excel khác

Hành động này được thực hiện theo cách tương tự. Trong ví dụ của chúng tôi, bạn không cần tạo bàn riêng, mà chỉ cần nhập hàm vào một cột của bất kỳ bảng nào. Hãy hiển thị nó bằng ví dụ đầu tiên. Đặt con trỏ vào cột cuối cùng.

Và trong ô G3 đặt hàm VLOOKUP. Chúng tôi lại lấy phạm vi từ bảng liền kề.

Kết quả là cột của bảng thứ hai sẽ được sao chép sang cột đầu tiên.

Đó là tất cả thông tin về cái vô hình, nhưng chức năng hữu ích VLOOKUP trong Excel dành cho người mới bắt đầu. Chúng tôi hy vọng nó sẽ giúp bạn giải quyết vấn đề.

Có một ngày tuyệt vời!