Tìm ngày liền trước và sau ngày cần tìm (1 người xem)

[gianghoxaotra]

Dear ACE Diễn đàn,
Mình cần Code VBA để tìm giá trị cận trên và giá trị cận dưới của ngày cần tìm từ bảng dữ liệu các ngày có sẵn.
Ví dụ: Bảng ngày như sau:
[TABLE="width: 77"]
[TR]
[TD="class: xl17, width: 77"]Date Range[/TD]
[/TR]
[TR]
[TD="class: xl17, align: right"]10/04/2017[/TD]
[/TR]
[TR]
[TD="class: xl17, align: right"]15/04/2017[/TD]
[/TR]
[TR]
[TD="class: xl17, align: right"]10/05/2017[/TD]
[/TR]
[TR]
[TD="class: xl17, align: right"]10/06/2017[/TD]
[/TR]
[TR]
[TD="class: xl17, align: right"]20/06/2017[/TD]
[/TR]
[TR]
[TD="align: right"]30/08/2017

[/TD]
[/TR]
[/TABLE]
Ngày giá trị là 12/04/2017 thì ngày cận dưới là 10/04/2017, ngày cận trên là 15/04/2017.
Tương tự cho ngày 21/05/2017...
Mình có gửi kèm file minh họa, nhờ mọi người giúp dùm. Cảm ơn rất nhiều.

 

[dazkangel]

Bạn sử dụng công thức sau:

D5:=VLOOKUP(C5,$A$5:$A$10,1,1)
E6=INDEX($A$5:$A$10,SMALL(IF(C5<$A$5:$A$10,ROW($A$1:$A$6)),1))

Ô E6 bạn kết thúc công thức bằng Ctrl+Shift+Enter nhé.

 

[HieuCD]

dear ace diễn đàn,
mình cần code vba để tìm giá trị cận trên và giá trị cận dưới của ngày cần tìm từ bảng dữ liệu các ngày có sẵn.
Ví dụ: Bảng ngày như sau:
[table="width: 77"]
[tr]
[td="class: Xl17, width: 77"]date range[/td]
[/tr]
[tr]
[td="class: Xl17, align: Right"]10/04/2017[/td]
[/tr]
[tr]
[td="class: Xl17, align: Right"]15/04/2017[/td]
[/tr]
[tr]
[td="class: Xl17, align: Right"]10/05/2017[/td]
[/tr]
[tr]
[td="class: Xl17, align: Right"]10/06/2017[/td]
[/tr]
[tr]
[td="class: Xl17, align: Right"]20/06/2017[/td]
[/tr]
[tr]
[td="align: Right"]30/08/2017
[/td]
[/tr]
[/table]
ngày giá trị là 12/04/2017 thì ngày cận dưới là 10/04/2017, ngày cận trên là 15/04/2017.
Tương tự cho ngày 21/05/2017...
Mình có gửi kèm file minh họa, nhờ mọi người giúp dùm. Cảm ơn rất nhiều.

d5 =vlookup(c5,$a$5:$a$10,1)
e5 =lookup(2,1/(($a$4:$a$10<c5)+($a$5:$a$11=c5)),$a$5:$a$11)

 

[Hoang2013]

Muốn Sub thì có Sub:

Sub MinMaxDate()
 Dim Rng As Range, sRng As Range, Cls As Range
 Dim MyFormat As String
 Dim J As Integer, MinDat As Date, MaxDat As Date
 
 Set Rng = Range([A5], [A5].End(xlDown))
 MyFormat = Rng.NumberFormat
 Rng.NumberFormat = "MM/DD/yyyy"
 For Each Cls In Range([c5], [c5].End(xlDown))
    For J = 0 To 366
        Set sRng = Rng.Find(Format(Cls.Value - J, "MM/dd/yyyy"), , xlValues, xlWhole)
        If Not sRng Is Nothing Then
            Cls.Offset(, 1).Value = sRng.Value
            Exit For
        End If
    Next J
    For J = 0 To 366
        Set sRng = Rng.Find(Format(Cls.Value + J, "MM/dd/yyyy"))
        If Not sRng Is Nothing Then
            Cls.Offset(, 2).Value = sRng.Value
            Exit For
        End If
    Next J
 Next Cls
 Rng.NumberFormat = MyFormat
End Sub

 

[ndu96081631]

d5 =vlookup(c5,$a$5:$a$10,1)

Thay vì VLOOKUP sao không dùng LOOKUP luôn cho khỏe?

 

[HieuCD]

Thay vì VLOOKUP sao không dùng LOOKUP luôn cho khỏe?

do thói quen thôi, chúc bạn một ngày vui

 

[quocgiacan]

do thói quen thôi, chúc bạn một ngày vui

Cụ thể hóa bằng công thức cho chủ thớt:

D5=LOOKUP($C5,$A$5:$A$10)
Và
E5=OFFSET($A$4,MATCH(TRUE,INDEX($A$5:$A$10>=$C5,),),)

Fill xuống.

Chúc anh em ngày thiệt vui.

 

[gianghoxaotra]

Cảm ơn Hoangf2013, nhưng code này mình gặp vấn đề khi bảng ngày tra không chỉ trong năm 2017, các ngày có thể cách nhau 2 năm, 5 năm, 10 năm thì code chạy không đúng.
Bạn có cách nào khắc phục không? Cảm ơn bạn nhiều.

Muốn Sub thì có Sub:

Sub MinMaxDate()
 Dim Rng As Range, sRng As Range, Cls As Range
 Dim MyFormat As String
 Dim J As Integer, MinDat As Date, MaxDat As Date
 
 Set Rng = Range([A5], [A5].End(xlDown))
 MyFormat = Rng.NumberFormat
 Rng.NumberFormat = "MM/DD/yyyy"
 For Each Cls In Range([c5], [c5].End(xlDown))
    For J = 0 To 366
        Set sRng = Rng.Find(Format(Cls.Value - J, "MM/dd/yyyy"), , xlValues, xlWhole)
        If Not sRng Is Nothing Then
            Cls.Offset(, 1).Value = sRng.Value
            Exit For
        End If
    Next J
    For J = 0 To 366
        Set sRng = Rng.Find(Format(Cls.Value + J, "MM/dd/yyyy"))
        If Not sRng Is Nothing Then
            Cls.Offset(, 2).Value = sRng.Value
            Exit For
        End If
    Next J
 Next Cls
 Rng.NumberFormat = MyFormat
End Sub

 

[Hoang2013]

Thì bạn tăng tại số 366 thành số 366*13 hay số 65500 xem sao!

 

[VetMini]

Thay vì VLOOKUP sao không dùng LOOKUP luôn cho khỏe?

Khi dữ liệu đã được sắp xếp thì VLookup rất hiệu quả. Vì nó tìm theo phép nhị phân.

 

[phuyen89]

Khi dữ liệu đã được sắp xếp thì VLookup rất hiệu quả. Vì nó tìm theo phép nhị phân.

Chào Anh.
Làm sao em biết làm nó tìm theo phép nhị phân (binary search).

 

[ndu96081631]

Khi dữ liệu đã được sắp xếp thì VLookup rất hiệu quả. Vì nó tìm theo phép nhị phân.

Mình là dân "lơ tơ mơ" nên không tài nào biết trong "ruột" mấy hàm ấy chứa gì
Mình thường theo phương pháp thực nghiệm để chứng minh. Tức là:
- Với dữ liêu ít, mình sẽ xài công thức nào NGẮN NHẤT
- Với dữ liệu nhiều, nếu phát hiện được độ chênh lệch về tốc độ giữa các hàm, mình sẽ xài công thức nào NHANH NHẤT
-------------------------------------------
Với câu hỏi ở topic này, mình vẫn còn thắc mắc: Nếu giá trị tìm trùng với giá trị trong bảng dò (chẳng hạn tìm ngày 15/4/2017) thì giá trị liền trước và sau sẽ là cái nào?

 

[VetMini]

Tham số thứ tư của Vlookup xác định cách tìm.
Nếu tham này có trị là 0 (False) thì hàm sẽ tìm theo kiểu so sánh chính xác; tức là nó sẽ dò mảng từ 1 đến khi gặp trị cần tìm.
Nếu tham này có trị là 1 (True) thì hàm sẽ tìm theo kiểu gần đúng; lúc đó nó sẽ tự hiểu là mảng cần dò đã được sắp xếp và dùng thuật nhị phân để dò. Lưu ý từ "tự hiểu", nếu mảng chưa sắp xếp mà dùng cách dò gần đúng thì ra kết quả sai ráng chịu.

Thuật nhị phân chỉ áp dụng được cho mảng đã sắp xếp. Đúng không?