Board logo

標題: [發問] 請問如何找出最接近日期 [打印本頁]
作者: gaishutsusuru    時間: 2023-4-23 23:08     標題: 請問如何找出最接近日期

本帖最後由 gaishutsusuru 於 2023-4-23 23:10 編輯

大家好，

想請教各位大大，
[attach]36208[/attach]

問題說明：
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

最後附上檔案：[attach]36209[/attach]

希望各位大大能抽空幫忙，非常謝謝您們。
作者: Andy2483    時間: 2023-4-24 16:40

回復 1# gaishutsusuru


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

執行結果:
[attach]36211[/attach]


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    時間: 2023-4-24 21:25

回復 2# Andy2483

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

想請問如果用公式的話，不知道能否達成呢? 謝謝
作者: Andy2483    時間: 2023-4-25 07:49

回復 3# gaishutsusuru


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

使用方式與結果:
[attach]36214[/attach]


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    時間: 2023-4-25 10:31

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    時間: 2023-4-25 12:10

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)&""""&";;無")

可以下拉
作者: 准提部林    時間: 2023-4-25 14:36

陣列公式(三键)
=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    時間: 2023-4-25 22:13

回復 5# hcm19522

感謝hcm19522大大的幫忙，公式可以成功操作。
作者: gaishutsusuru    時間: 2023-4-25 22:14

回復 4# Andy2483

謝謝Andy2483您的幫忙，我來研究看看
作者: gaishutsusuru    時間: 2023-4-25 22:14

回復 6# ML089

謝謝ML089您的幫忙，公式沒有問題哦。
作者: gaishutsusuru    時間: 2023-4-25 22:17

回復 7# 准提部林

謝謝准提部林大大的幫忙，不過公式有些會出問題：
例：
輸入202307_B 時，公式應該要回傳「202305_B」, 但卻跳出202307_B
輸入202212_A時，公式應該要回傳「無」, 但卻跳出202212_A
作者: Andy2483    時間: 2023-4-26 09:03

回復 9# gaishutsusuru


    謝謝論壇,謝謝前輩回復
後學今天複習方案,心得註解如下,請前輩參考,請各位前輩指教

Option Explicit
Function GetSerial(ST$)
'↑自訂函數GetSerial() 宣告變數:輸入函數的 ST變數是 字串
Dim Brr, X, Q, Z$, K$, V0$, V1$, T0$, T1$, i&, M%, Y%
'↑宣告變數:(Z,K,V0,V1,T0,T1)是字串變數,i是長整數變數,
'(Y,M)是短整數變數,(Brr,X,Q)是通用型變數
Dim D As Date, P As Date, P1 As Date
'↑(D,P,P1)是日期變數
Brr = Range([A1], Cells(Rows.Count, 1).End(3))
'↑令Brr這通用型變數是二維陣列,以[A1]到A欄最後一個有內容儲存格值帶入
T0 = ST: V0 = Mid(T0, InStr(T0, "_") + 1)
'↑令T0這字串變數是 使用者輸入函數裡的儲存格值,
'令V0這字串變數是 T0變數取底線字元(不含)後的所有字元組成的新字串
Y = Left(Val(T0), 4): M = Val(Right(Val(T0), 2))
'↑令Y這短整數是 T0變數轉整數數值後取左側4字元的數值,
'↑令M這短整數是 T0變數轉整數數值後取右側2字元的數值
D = CDate(Y & "/" & M & "/01")
'↑令D這日期變數是 Y變數連接"/",再連接M變數,最後連接"/01"組成的日期
For i = 1 To UBound(Brr)
'↑設順迴圈!i從1到 Brr陣列縱向最大索引列號
   T1 = Brr(i, 1): V1 = Mid(T1, InStr(T1, "_") + 1)
   '↑令T1這字串變數是 i迴圈列第1欄Brr陣列值,
   '令V1這字串變數是 T1變數取底線字元(不含)後的所有字元組成的新字串
   Y = Left(Val(T1), 4): M = Val(Right(Val(T1), 2))
   '↑令Y這短整數是 T1變數轉整數數值後取左側4字元的數值,
   '↑令M這短整數是 T1變數轉整數數值後取右側2字元的數值
   Y = Y + M \ 12: M = M Mod 12 + 1
   '↑令Y變數是 自身+(M變數除12的整數商)
   '令M變數是 自身除12後的餘數,再+1
   P = CDate(Y & "/" & M & "/01") - 1
   '↑令P這日期變數是 Y變數連接"/",再連接M變數,
   '最後連接"/01"組成的前一天日期(求前一個月的最後一天)
   If (T0 = T1) + (V0 <> V1) + (P > D) Then GoTo i01
   '↑如果這三個條件的其中一個不是0!就跳到 i01位置繼續執行
   If P1 - Date < P - Date Then
   '↑如果P1變數-今天日期 小於 P變數-今天日期?
      P1 = P: Z = Format(P1, "YYYYMM") & "_" & V0
      '↑令P1變數裝P變數,令Z這字串變數是 P1變數轉4碼年2碼月,
      '再連接底線字元,最後連接V0變數的新字串
   End If
i01: Next
GetSerial = IIf(Z <> "", Z, "無")
'↑回傳值給自訂函數:
'如果Z變數不是空字元就回傳Z變數,否則回傳"無"字元
Erase Brr
'↑令釋放變數
End Function
作者: 准提部林    時間: 2023-4-26 11:40

回復 11# gaishutsusuru


把 <= 改成 <



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)