返回列表 上一主題 發帖

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

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

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

大家好,

想請教各位大大,
螢幕擷取畫面 2023-04-23 225732.png
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)

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

回復 1# gaishutsusuru


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

執行結果:
20230424_1.jpg
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
看得懂是應該的,懂得應用才像學生,臉皮厚點學會更謹慎積極

TOP

回復 2# Andy2483

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

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

TOP

回復 3# gaishutsusuru


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

使用方式與結果:
20230425_1.jpg
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
看得懂是應該的,懂得應用才像學生,臉皮厚點學會更謹慎積極

TOP

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)),"無")
11328.png
隨意窩 "EXCEL迷"  blog  或https://blog.xuite.net/hcm19522/twblog
已收集8500篇 EXCEL函數

TOP

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

可以下拉
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

陣列公式(三键)
=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),"無")

TOP

回復 5# hcm19522

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

TOP

回復 4# Andy2483

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

TOP

回復 6# ML089

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

TOP

        靜思自在 : 【蒙蔽的自由】人常在什麼都可以自由自在的時候,卻被這種隨心所欲的自由蒙蔽,虛擲時光而毫無覺知。
返回列表 上一主題