Hàm VLookup dò tìm ngược

  • Thread starter Do Tam
  • Ngày gửi
D

Do Tam

Thành viên sơ cấp
7/6/06
1
0
0
H21 Quang Trung
#1
Chào các bạn,

Mình đang rất cần viết gấp một user-defined function trong Excel có tính năng tương tự như VLookup nhưng chỉ khác ở chỗ là hàm này sẽ dò tìm ngược từ dưới lên của Table_array.

Rất mong các bậc cao thủ chỉ giáo cho.
Xin cảm ơn rất nhiếu.
Do Tam
 
I

iloveyou

Guest
#2
Dò tìm ngược

Hình nhưng bạn hơi phức tạp hóa công việc. Tại sao lại phải do tìm ngược trong khi có thể Sort rồi sau đó là VLookup. Nếu muốn viết hàm thì gợi ý như sau.

1) Đến ô cuối cùng
2) Bắt đầu vòng lặp
Kiểm tra ô hiện hành có bằng giá trị cần tìm không.
Nếu không thì đi ngược lên 1 ô.
Nếu bằng thì trả về giá trị tại cột bạn muốn, thóat vòng lặp.
Lặp lại
 
QDuc

QDuc

Thành viên thân thiết
3/6/06
254
18
18
Biển khơi
#3
Mình đang một user-defined function trong Excel có tính năng tương tự như VLookup nhưng chỉ khác ở chỗ là hàm này sẽ dò tìm ngược từ dưới lên của Table_array.Rất mong các bchỉ giáo.
???Lý do!
Vì hàm LOOKUP() sẽ trả về value chứa trong địa chỉ được yêu cầu;
Còn tìm từ dưới lên bằng UDF để nhanh hơn Excel, nói chung là chưa có gì đảm bảo đâu!
 
I

iloveyou

Guest
#4
Su dung ham UDF sau:

Function MyVlookup(gtt As Variant, bdl As Range, vt As Integer) As Variant
d = bdl.Row 'Dong dau tien cua bang
j = bdl.Column 'Cot dau tien cua bang
c = d + bdl.Rows.Count 'Dong cuoi cua bang
For i = c To d Step -1
If Cells(i, j) = gtt Then Exit For 'Neu tim duoc thi thoat lap
Next
MyVlookup = Cells(i, j + vt)
End Function
 
T

thanhvo31

Thành viên sơ cấp
17/7/05
36
0
0
44
Haiphong
#5
Bạn hãy thử sử dụng hàm MATCH và INDEX xem
HÀM này tốt gấp 2 lần VLOOKUP hay HLOOKUP

Thứ hai:
native worksheet function luôn chạy nhanh hơn user defined function

MATCH(lookup_value,lookup_array,match_type)

INDEX(array,row_num,column_num)

=INDEX(F2:F21,MATCH(B1,D2:D21,0))

Chúc vui:dzo:
 
I

iLike

Thành viên sơ cấp
6/12/06
11
0
1
47
Saigon
#6
- Vì một lý do nào đó bạn không thể / không được phép sort database theo chiều ngược lại thì dùng hàm của iloveyou là hợp lý nhất
- Nếu là mình thì mình cố tìm cách để có thể Sort được cơ, khi nào không thể nào không thì mình mới dùng hàm tự tạo, hàm tự tạo có tốc độ rất chậm so với wsheet functions và mang đi gắn trên máy khác chủ máy cũng phải cân nhắc virus warning (excellent VBA is no VBA)

@thanhvo31, hàm của bạn đưa ra làm sao giải quyết được vấn đề này nhỉ, theo mình thấy thì hàm của bạn chính là hàm vlookup thông thường, hàm này không thể nhanh hơn vlookup được bạn à (bạn dùng 2 hàm thì chậm hơn 1 hàm của wsheet là không tránh khỏi):wall:
 
H

hmanhha

Thành viên sơ cấp
2/10/07
4
0
1
40
Ha Noi
#7
Su dung ham UDF sau:

Function MyVlookup(gtt As Variant, bdl As Range, vt As Integer) As Variant
d = bdl.Row 'Dong dau tien cua bang
j = bdl.Column 'Cot dau tien cua bang
c = d + bdl.Rows.Count 'Dong cuoi cua bang
For i = c To d Step -1
If Cells(i, j) = gtt Then Exit For 'Neu tim duoc thi thoat lap
Next
MyVlookup = Cells(i, j + vt)
End Function
Nếu em muốn tìm giá trị đó trong một sheet khác thì làm thế nào.Bác giúp em cái.Tìm xuôi hay ngược đều được.
 
S

SA_DQ

Thành viên thân thiết
29/6/05
435
38
28
61
HCM city
#8
Option Explicit
Function GVLooKup(LookUpVar, Rng As Range, Col As Byte, Optional Nguoc As Boolean)
On Error GoTo ErrGVLK
Dim vValues()
Dim lLong As Long
vValues = Rng
If Nguoc Then
For lLong = UBound(vValues) To 1 Step -1
2 If LookUpVar = vValues(lLong, 1) Then
3 GVLooKup = vValues(lLong, Col)
4 Exit For
5 End If
Next
Else
7 For lLong = 1 To UBound(vValues)
8 If LookUpVar = vValues(lLong, 1) Then
9 GVLooKup = vValues(lLong, Col)
Exit For
End If
Next
End If

LoiGVLK: Exit Function
ErrGVLK: GVLooKup = "Xem Lai!"
End Function
(ú pháp hàm như sau:
GVLooKup(LookUpVar, Rng As Range, Col As Byte, Optional Nguoc As Boolean)
LookUpVar - giá trị cần tìm;
Rng- Vùng cần tìm; nếu là Sheets khác thì nhập cả tên sheets
Col : Cột cần lấy giá trị trong vùng nêu trên;
Nguoc - Hướng tìm; nếu tìm như trong excel bình thường thì không cần ghi


* Tìm ngược từ dưới lên trên: =GVLooKup(24.5,'S2'!B31:D46,3,TRUE)
Tìm như hàm của excel =GVLooKup(24.5,'S2'!B31:D46,3)
(trường hợp này nên dùng hàm của excel thì hợp hơn!)
* (òn muốn tìm & hiển thị tất cả các giá trị thỏa thì xem hàm DVLOOKUP() có trong diễn đàn
 
S

SA_DQ

Thành viên thân thiết
29/6/05
435
38
28
61
HCM city
#9
Mình có í tưởng rút gọn lại hàm trên, nhưng bị lỗi!

/)/(ình hỏi diễn đàn khác &ới câu: Where Is Error In My Function?
Option Explicit
Function G_VLooKup(LookUpVar, Rng As Range, Col As Byte, Optional Nguoc As Boolean)

On Error GoTo ErrGVLK
Dim vValue(): Dim Dau, Cuoi, lLong As Long

vValue = Rng
If Nguoc Then
Dau = UBound(vValue): Cuoi = 1
Else
Dau = 1: Cuoi = UBound(vValue)
End If
For lLong = Dau To Cuoi
If LookUpVar = vValue(lLong, 1) Then
G_VLooKup = vValue(lLong, Col)
Exit For
End If
Next
Exit Function
ErrGVLK: G_VLooKup = "Error!"
End Function
họ trả lời như sau:

Because the start value for the loop is greater than the end value it does not go through the loop at all.
You need to adjust the step index in order to go from top to bottom or vice versa.
 
hieu1563

hieu1563

Thành viên sơ cấp
#10
Sử dụng Index kết hợp với Match cho ta sự linh hoạt vì tính đa chiều của nó (Khác hẳn với Vlookup và Hlookup). Nhưng nó lại luôn cho ta giá trị đầu tiên tìm thấy. Vậy tìm ngược thế nào ??

Lấy ý tưởng của hàm Index kết hợp với Match nhưng ta có thể tìm xuôi (0) hay ngược (1) đều được hết, mình chế ra 1 hàm để thực hiện công việc đó:

Các bác tham khảo nhé :

Function TimMa(Ma As String, MangMa As Range, MangGT As Range, Optional m As Byte)
On Error GoTo Thoat
Dim i As Long, iBD As Long, iKT As Long, iST As Integer
If MangMa.Rows.Count <> MangGT.Rows.Count Then Exit Function
If MangMa.Rows.Count < 1 Or MangGT.Rows.Count < 1 Then Exit Function
If m <> 0 And m <> 1 Then Exit Function
If m = 0 Then
iBD = 1: iKT = MangMa.Rows.Count: iST = 1
ElseIf m = 1 Then
iBD = MangMa.Rows.Count: iKT = 1: iST = -1
End If
For i = iBD To iKT Step iST
If MangMa(i, 1) = Ma Then
TimMa = MangGT(i, 1)
Exit For
End If
Next i
Thoat:
End Function
Thân!
 

Đính kèm

C

colennha

Thành viên sơ cấp
28/6/08
1
0
0
32
binh duong
#11
ham cua anh rat hay, sao anh khong nghi den luc nao do minh co khoang tren 3 gia tri minh lay gia tri thu 2 thi sao em cung hoi to mo hihihi
thanks,
thao
 
N

ngoccanhbp08

Thành viên sơ cấp
1/7/08
1
0
0
29
dong nai
#12
excel

anh oi cho em hoi erm hoc phan excel do em chang hieu zi ca ah . ma khi nao dung IF. vlookup, nhieu lam ah.anh co the chi chop em ko . em hoi to mo do
 
Phượng Hồng Dương

Phượng Hồng Dương

Thành viên sơ cấp
12/10/14
1
0
1
33
#13
Mình gặp một khó khăn mà làm mãi vẫn không ra. Việc gấp quá nên phải nhờ tới webketoan. Mình muốn lookup 1 giá trị nhưng cho ra 2 kết quả, xem file đính kèm. Mong được giúp sức.
 

Đính kèm

P

phongky2013

Thành viên sơ cấp
28/5/14
92
10
8
26
Hà Nội
#14

Có 2 cách để dò tìm ngược dữ liệu từ phải qua trái (tức VLookup ngược):


Cách 1: Hàm Vlookup kết hợp công thức mảng của hàm Choose:


Cách 2: Hàm Offset kết hợp hàm Match:


Mình gặp một khó khăn mà làm mãi vẫn không ra. Việc gấp quá nên phải nhờ tới webketoan. Mình muốn lookup 1 giá trị nhưng cho ra 2 kết quả, xem file đính kèm. Mong được giúp sức.
Cái đó bạn dùng Macro kết hợp với Advanced filter là đc nhé:

 
Sửa lần cuối:
Espresso

Espresso

Thành viên thân thiết
30/10/15
108
34
28
68
#15
Giả sử :
ABC 888
ABC 2
ABC 3
ABC 4
ABC 5
ABC 6
ABC 12

Muốn tìm đầu, đuôi, các khúc 2, 3 v.v...
Đầu =INDEX(B:B,SUMPRODUCT(MAX(("ABC"=A1)*ROW(A1))))
Đầu = =INDEX(A1:B7,MATCH("ABC",A1:A7,0),2)
Đầu=LOOKUP(9.99999999999999E+307,FIND("ABC",$A$1:$A$10),$B$1:$B$10)

Ở row nào ???
=MATCH(2,1/(A1:A7="ABC")) -->cse
=MIN(ROW(A1:A7))+ROWS(A1:A7)-1
=SUMPRODUCT(MAX((A1:A7="ABC")*(ROW(A1:A7))))

Đuôi =LOOKUP(2,1/(A1:A7="ABC"),B1:B7) --> CSE
Đuôi=SUMPRODUCT(MAX((A1:A7="ABC")*(ROW(A1:A7))))
Đuôi=LOOKUP("ABC",A1:A7,B1:B7)

Tìm các lần 1, 2, 3 hoặc XẢY RA thứ nth của một giá trị


TỰ TÌM ĐI
Đơn giản , đại khái là vậy
 

Thành viên trực tuyến

Không có thành viên trực tuyến.

Xem nhiều