[µo°Ý] ½Ð°Ý¦p¦ó±N¤º®e¦³¤¤µØ¥Á°ê¤é´Áªº¦r²´§ì¥X¨ÓÂন¦è¤¸¤é´Á?

freeffly

§Ú·Q­n¦bBÄæÅã¥ÜAÄæ¹ïÀ³ªº¦è¤¸¤é´Á
½Ð°Ý¦³¿ìªk°µ¨ì¶Ü?

Book1.rar (5.6 KB)

¤U¸ü¦¸¼Æ: 23

2013-5-30 16:05

freeffly

¦^´_ 23# Hsieh


    ÁÂÁ¶Wª©¦^ÂÐ
   ¥­±`´X¥G¤£·|¥h¥Î¦Û­q¨ç¼Æ¤è¦¡
   ¤S¦h¤@­Ó¥i¥H¬ã¨sªºªF¦è¤F

c_c_lai

¦^´_ 23# Hsieh
¦^´_ 21# GBKEE
§Ú±N GBKEE ª©¤jªº¼Ò²Õ¡B¥[¤W Hsieh ª©¤j´£¨Ñªº Function¡A
¨âªÌ²Õ¦X«áÁÙÆZ¹ê¥Îªº¡G

1. Sub Ex_¤é´Á¼Æ­È3()
   
2.     Dim i  As Long, xl_Year As Variant
   
3.     With Range("B1:B" & [A1].End(xlDown).Row)
   
4.         For i = 1 To .Count
   
5.             .Cells(i) = ChDate(.Cells(i).Offset(, -1))
   
6.             If .Cells(i) <> "" Then .Cells(i).Offset(, 1) = CEDate(.Cells(i))
   
7.         Next
   
8.         '  .Offset(,-1)¡G ¥ª²¾ 1 Äæ (B -> AÄæ)¡F Resize(,3)¡G ±qAÄæ°_ÂX¥R¬°¤TÄæ (A¡BB¡BC ¤TÄæ)
   
9.         '  .Cells(1) = "2012¦~10¤ë12¤é"  (B Äæ)¡A .Cells(1).Offset(, 1) = "2012/10/12"  (C Äæ)
   
10.         '  .Offset(, -1).Resize(, 3).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    
11.         .Offset(, -1).Resize(, 3).Sort Key1:=.Cells(1).Offset(, 1), Order1:=xlAscending, Header:=xlNo
    
12.     End With
    
13. End Sub

½Æ»s¥N½X

1. Function ChDate(DateStr As String)
   
2.     '  DateStr¥²¶·¬O¥]§t"¤¤µØ¥Á°ê¦~¤ë¤é"ªº¦r¦ê
   
3.     Dim Mystr As String, s%
   
4.    
   
5.     s = InStr(DateStr, "¤¤µØ¥Á°ê") + 4
   
6.     If s = 4 Then ChDate = "": Exit Function
   
7.     Mystr = Mid(DateStr, s, InStr(s, DateStr, "¤é") - s + 1)
   
8.     ChDate = Replace(Mystr, Val(Mystr) & "¦~", Val(Mystr) + 1911 & "¦~")
   
9. End Function
   
10. 
    
11. Function CEDate(DateStr As String)    '  ¡uC.E.¡v¬O¡uCommon Era¡vªºÁY¼g¡A·N¬°¡u¤½¤¸¡v
    
12.     CEDate = CDate(DateStr)
    
13. End Function

½Æ»s¥N½X

ÁÂÁ¨â¦ìª©¤jªº¹©¤OÀ°¦£¡C

c_c_lai

¦^´_  freeffly
Hsieh µoªí©ó 2013-6-6 23:37

ÆZ¹ê¥Îªº¥\¯à¨ç¼Æ¡A·PÁ±z¡C¥t¥~§Ú¥[¤W¤F Instr() ±ø¥ó¤£¦¨¥ß®É¤§³B¸Ì¡G

1. Sub Test()
   
2.     Dim txt As String
   
3.    
   
4.     txt = CEDate([A1])
   
5.     MsgBox IIf(txt = "", """¤¤µØ¥Á°ê"" ¦r¦ê¨Ã¤£¦s¦b¡I", "Year = " & txt)
   
6. End Sub
   
7. 
   
8. Function CEDate(DateStr As String)    ' ¡uC.E.¡v¬O¡uCommon Era¡vªºÁY¼g¡A·N¬°¡u¤½¤¸¡v
   
9.     '  DateStr¥²¶·¬O¥]§t"¤¤µØ¥Á°ê¦~¤ë¤é"ªº¦r¦ê
   
10.     Dim Mystr As String, s%
    
11.    
    
12.     s = InStr(DateStr, "¤¤µØ¥Á°ê") + 4
    
13.     If s = 4 Then CEDate = "": Exit Function
    
14.     Mystr = Mid(DateStr, s, InStr(s, DateStr, "¤é") - s + 1)
    
15.     CEDate = CDate(Replace(Mystr, Val(Mystr) & "¦~", Val(Mystr) + 1911 & "¦~"))
    
16. End Function

½Æ»s¥N½X

§_«h·|²£¥Í¥H¤Uªº¿ù»~°T®§¡G
°õ¦æ¶¥¬q¿ù»~ '13'¡G
«¬ºA¤£²Å¦X

Hsieh

¦^´_ 1# freeffly

¤U¸ü (96.98 KB)

2013-6-6 23:34

1. Function ChDate(DateStr As String)
   
2. 'DateStr¥²¶·¬O¥]§t"¤¤µØ¥Á°ê¦~¤ë¤é"ªº¦r¦ê
   
3. Dim Mystr As String, s%
   
4.    s = InStr(DateStr, "¤¤µØ¥Á°ê") + 4
   
5.    Mystr = Mid(DateStr, s, InStr(s, DateStr, "¤é") - s + 1)
   
6.    ChDate = CDate(Replace(Mystr, Val(Mystr) & "¦~", Val(Mystr) + 1911 & "¦~"))
   
7. End Function

½Æ»s¥N½X

c_c_lai

¦^´_ 21# GBKEE
·PÁ±zªº«ü¾É¡A§Ú¤S¾Ç¨ì¤F .Offset(, -1).Resize(, 2)
ªºÀ³¥Î¡A¹ê¦b«D±`¹ê¥Î¡C¦A¦¸»¡ÁnÁÂÁ¡I

GBKEE

¦^´_ 20# c_c_lai

1. Sub Ex_¤é´Á¼Æ­È()
   
2.     Dim i  As Long, xl_Year As Variant
   
3.     With Range("B1:B" & [A1].End(xlDown).Row)
   
4.         .Cells = "=MID(RC[-1],10,10)"
   
5.         .Value = .Value
   
6.         .Replace "µo", ""
   
7.         For i = 1 To .Count
   
8.             xl_Year = Split(.Cells(i), "¦~")
   
9.             xl_Year(0) = xl_Year(0) + 1911 & "¦~"
   
10.             .Cells(i) = Trim(Join(xl_Year, ""))
    
11.         Next
    
12.         .Cells.Replace "¦~", "/", xlPart
    
13.         .Cells.Replace "¤ë", "/"
    
14.         .Cells.Replace "¤é", ""
    
15.         .Offset(, -1).Resize(, 2).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    
16.         .Offset(, -1).Resize(, 2).Select         'Offset(, -1) ->¥ª²¾1Äæ 'Resize(, 2)  ->ÂX¥R¬°¨âÄæ
    
17.         '.Cells.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    
18.     End With
    
19. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 18# GBKEE
§Ú¦Û¤vºN¯Á¡A¤w¸g§ä¨ìµª®×¤F¡AÁÂÁ±z¡I

1. Sub Ex_¤é´Á¼Æ­È()
   
2.     Dim i  As Long, xl_Year As Variant
   
3.    
   
4.     With Range("B1:B" & [A1].End(xlDown).Row)
   
5.         .Cells = "=MID(RC[-1],10,10)"
   
6.         .Value = .Value
   
7.         .Replace "µo", ""
   
8.         
   
9.         For i = 1 To .Count
   
10.             xl_Year = Split(.Cells(i), "¦~")
    
11.             xl_Year(0) = xl_Year(0) + 1911 & "¦~"
    
12.             .Cells(i) = Trim(Join(xl_Year, ""))
    
13.             .Cells(i).Offset(, 1) = .Cells(i)
    
14.         Next
    
15.         
    
16.         .Cells.Offset(, 1).Replace "¦~", "/", xlPart
    
17.         .Cells.Offset(, 1).Replace "¤ë", "/"
    
18.         .Cells.Offset(, 1).Replace "¤é", ""
    
19.         ' .Cells.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    
20.     End With
    
21.    
    
22.     '  With Sheet3
    
23.     '      .Range("A1").CurrentRegion.Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlNo
    
24.     '  End With
    
25.     '  Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo
    
26.     Range("A1").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlNo
    
27. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 18# GBKEE
±zÀ³¥Î Split »P Replace ªº§Þ¥©¡Aµ¹§Ú«Ü¤jªº±Ò¥Ü¤Î¡AÀ°§U¡BÁA¸Ñ¡A
ÁÂÁ±z¡I
¥t¥~¦A¶¶±a½Ð±Ð¡G .Cells.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
¥u Sort BÄ檺³¡¤À¡A¦pªG§Ú·Q­n¦P®É¯à±N AÄæ¤@¨Ö¦P®É¥¿½T¦a¤@¦¸§¹¦¨ Sort¡A
¨º¦p¦ó³B²z¡H

GBKEE

¦^´_ 13# c_c_lai

1. Sub Ex_¤é´Á¼Æ­È()
   
2.     Dim i  As Long, xl_Year As Variant
   
3.     With Range("B1:B" & [A1].End(xlDown).Row)
   
4.         .Cells = "=MID(RC[-1],10,10)"
   
5.         .Value = .Value
   
6.         .Replace "µo", ""
   
7.         For i = 1 To .Count
   
8.             xl_Year = Split(.Cells(i), "¦~")
   
9.             xl_Year(0) = xl_Year(0) + 1911 & "¦~"
   
10.             .Cells(i) = Trim(Join(xl_Year, ""))
    
11.         Next
    
12.         .Cells.Replace "¦~", "/", xlPart
    
13.         .Cells.Replace "¤ë", "/"
    
14.         .Cells.Replace "¤é", ""
    
15.         .Cells.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    
16.     End With
    
17. End Sub

½Æ»s¥N½X