[發問] 請問如何找出最接近日期

gaishutsusuru

大家好，

想請教各位大大，

下載 (7.12 KB)

2023-4-23 22:58

問題說明：
1. A1~A11：是資料，但實際上資料會更多
2. C1是輸入的值。
3. C4是要設定公式，符合以下條件
(1) "_"底線後是長度不一樣的字串。且字串也可能會含有底線。例：202312_B。B是字串的簡化例子，實際上可能會包含底線
(2) 判斷要符合字串後，才能進行日期的判斷(找出日期最接近且小於C1儲存格的條件)。
(3) 例子舉例如下：
① 當C1輸入：202312_B 時，C4會回傳202307_B。(因為在A1~A11符合字串B的且日期最接近且小於202312的是202307)
② 當C1輸入：202301_A 時，C4會回傳202212_A。(因為在A1~A11符合字串A的且日期最接近且小於202301的是202212)
③ 如果找不到，就回傳「無」。例：當C1輸入：202212_A 時，C4會回傳「無」。(因為在A1~A11符合字串A的且日期最接近且小於202212的沒有值)
4. 目前使用的EXCEL 的版本是2010、2013

最後附上檔案： 問題.zip (6.03 KB)

下載次數: 6

2023-4-23 23:08

希望各位大大能抽空幫忙，非常謝謝您們。

Andy2483

回復 1# gaishutsusuru


    謝謝論壇,謝謝前輩發表此主題與範例
後學藉此帖練習VBA的方法,學習方案如下,請前輩參考

執行結果:

下載 (34.94 KB)

2023-4-24 16:40

Option Explicit
Sub TEST()
Dim Brr, Z$, X, Q, K$, V0$, V1$, T0$, T1$, i&, M%, Y%
Dim D As Date, P As Date, P1 As Date
Brr = Range([A1], Cells(Rows.Count, 1).End(3))
T0 = [C1]: V0 = Mid(T0, InStr(T0, "_") + 1)
Y = Left(Val(T0), 4): M = Val(Right(Val(T0), 2))
Y = Y + M \ 12: M = M Mod 12 + 1
D = CDate(Y & "/" & M & "/01")
For i = 1 To UBound(Brr)
   T1 = Brr(i, 1): V1 = Mid(T1, InStr(T1, "_") + 1)
   Y = Left(Val(T1), 4): M = Val(Right(Val(T1), 2))
   Y = Y + M \ 12: M = M Mod 12 + 1
   P = CDate(Y & "/" & M & "/01") - 1
   If (T0 = T1) + (V0 <> V1) + (P > D) Then GoTo i01
   If P1 - Date < P - Date Then
      P1 = P: Z = Format(P1, "YYYYMM") & "_" & V0
   End If
i01: Next
[C4] = IIf(Z <> "", Z, "無")
Erase Brr
End Sub

gaishutsusuru

回復 2# Andy2483

謝謝Andy2483大大的抽空幫忙。我再來試看看。

想請問如果用公式的話，不知道能否達成呢? 謝謝

Andy2483

回復 3# gaishutsusuru


    謝謝論壇,謝謝前輩回復
公式很難,後學還沒那個功力設計,以下是將#2樓改成自訂函數的學習方案,請前輩參考

使用方式與結果:

下載 (59.56 KB)

2023-4-25 07:48

Option Explicit
Function GetSerial(ST$)
Dim Brr, Z$, X, Q, K$, V0$, V1$, T0$, T1$, i&, M%, Y%
Dim D As Date, P As Date, P1 As Date
Brr = Range([A1], Cells(Rows.Count, 1).End(3))
T0 = ST: V0 = Mid(T0, InStr(T0, "_") + 1)
Y = Left(Val(T0), 4): M = Val(Right(Val(T0), 2))
D = CDate(Y & "/" & M & "/01")
For i = 1 To UBound(Brr)
   T1 = Brr(i, 1): V1 = Mid(T1, InStr(T1, "_") + 1)
   Y = Left(Val(T1), 4): M = Val(Right(Val(T1), 2))
   Y = Y + M \ 12: M = M Mod 12 + 1
   P = CDate(Y & "/" & M & "/01") - 1
   If (T0 = T1) + (V0 <> V1) + (P > D) Then GoTo i01
   If P1 - Date < P - Date Then
      P1 = P: Z = Format(P1, "YYYYMM") & "_" & V0
   End If
i01: Next
GetSerial = IIf(Z <> "", Z, "無")
Erase Brr
End Function

hcm19522

D1{=IFERROR(INDEX(A:A,RIGHT(LARGE(IF((LEFT(A$1:A$11,6)-LEFT(C1,6)<0)*(MID(A$1:A$11,8,99)=MID(C1,8,99)),LEFT(A$1:A$11,6)/1%+ROW($1:$11)),1),2)),"無")

ML089

D1 陣列公式，公式複製編輯列(游標在上)，先按Shit+Ctrl再按ENTER，輸入成功公式外增加 {...}
=TEXT(MAX((MID(A$1:A$20,7,9)=MID(C1,7,9))*(LEFT(A$1:A$20,6)<LEFT(C1,6))*(0&LEFT(A$1:A$20,6))),0&""""&MID(C1,7,9)&""""&";;無")

可以下拉

准提部林

陣列公式(三键)
=TEXT(MAX(--TEXT(SUBSTITUTE(A$1:A$99,MID(C1,7,9),),"[<="&LEFT(C1,6)&"]0;-1;-1;\-\1")),"0"""&MID(C1,7,9)&""";無")

日期有排序
=IFERROR(LOOKUP(,-TEXT(SUBSTITUTE(A$1:A$99,MID(C1,7,9),),"[<="&LEFT(C1,6)&"]0;;;"),A:A),"無")

gaishutsusuru

回復 5# hcm19522

感謝hcm19522大大的幫忙，公式可以成功操作。

gaishutsusuru

回復 4# Andy2483

謝謝Andy2483您的幫忙，我來研究看看

gaishutsusuru

回復 6# ML089

謝謝ML089您的幫忙，公式沒有問題哦。