[µo°Ý] §ä¥X¨C­Ó¸s²Õ¤¤¡A¤é´Á³Ì¾aªñ¤µ¤Ñªº¦C

gameshop

¦U¦ì¥ý¶i¦n

³Ìªñ¦b¾ã²z¤@­Ó¦³¨â¸U¦CªºExcel

¥Ø¼Ð¬O¨C¤Ñ·|¦Û°Ê§ì¥X³Ì¾aªñ¤µ¤Ñ¤é´Áªº¸ê®Æ(¦ý¤£¥]§t¥¼¨Ó)

¸ê®Æ½d¨Ò¦p¤U
¸s²Õ  ¤é´Á  ¼Æ­È
A  2012/12/15  15
A  2013/1/17  16
A 2013/5/15  10
A 2014/2/12  49
B  2012/2/3  20
B  2013/4/25 30
B  2014/5/22  50
C  2011/3/3  20
C 2012/2/13 5

¨Ò¦p¤µ¤Ñ¬O2013/5/16
«hµ²ªG«h¶]¥X
A 2013/5/15  10
B  2013/4/25 30
C 2012/2/13 5

¥Ø«e·Qªk¬O¥ÎToday()´î±¼¤é´ÁÄæ¦ì¡A¦A¨ú¥X¦U¸s²Õ¤¤min( ¤j©ó©Îµ¥©ó¹sªº¼Æ)
¦A±N¨º¤@¦C§ì¦Ü·sªºWorksheet
¦ý¤£ª¾¹D¦p¦ó°w¹ï¦U¸s²Õ¤À§O¶i¦æ

½Ð¦U¦ì¥ý¶iÀ°¦£«üÂI

«D±`·PÁÂ!!

Hsieh

¦^´_ 5# gameshop

1. Sub ex()
   
2. Set d = CreateObject("Scripting.Dictionary") '³Ð«Ø¦r¨åª«¥ó¬ö¿ý¸s²Õ¤é´Á®t
   
3. Set d1 = CreateObject("Scripting.Dictionary") '³Ð«Ø¦r¨åª«¥ó¬ö¿ý¸s²Õ³Ì¤p¤é´Á®t
   
4. 
   
5. With Sheets(1) '¸ê®Æ¤u§@ªí
   
6. ar = .Range("A1").CurrentRegion '¸ê®Æ©Ò¦³½d³ò°O¿ý¨ì°}¦C
   
7. d1(ar(1, 1)) = Application.Index(ar, 1) '«Ø¥ß¼ÐÃD¦C
   
8.   For i = 2 To UBound(ar, 1) '±q²Ä2¦C¶}©l°j°é
   
9.   s = Date - ar(i, 2) '¤é´Á®t
   
10.   If s >= 0 Then '¤é´Á®t¬°¥¿­È
    
11.      If IsEmpty(d(ar(i, 1))) Then d(ar(i, 1)) = s '°²¦p¸s²ÕÁÙ¨S¸ê®Æ¡A´N±N¤é´Á®t¥[¤J¸s²Õ
    
12.      If s <= d(ar(i, 1)) Then d(ar(i, 1)) = s: d1(ar(i, 1)) = Application.Index(ar, i) '¤ñ¸û¤é´Á®t«á°O¿ý¸Ó¦C¸ê®Æ¨ì¦r¨å¹ïÀ³¸s²Õ¤º
    
13.   End If
    
14.   Next
    
15. End With
    
16. Sheets(2).Range("A1").CurrentRegion.ClearContents '²M°£Â¦³¸ê®Æ
    
17. Sheets(2).[A1].Resize(d1.Count, UBound(ar, 2)) = Application.Transpose(Application.Transpose(d1.items)) '¼g¤J¸ê®Æ
    
18. End Sub

½Æ»s¥N½X

gameshop

©êºp«e½ú
§Úµo²{§Ú¨S¦³§â»Ý­nªºµ²ªG»¡²M·¡
¨ä¹êÀ³¸ÓÁÙ­n¥[¤@­Ó±ø¥ó

¦pªG¸Ó¸s²Õ¨S¦³¹L¥h®É¶¡ªº¸ê®Æ
«h§ï§ì³Ì±µªñªº¥¼¨Ó®É¶¡ÂIªº¸ê®Æ
If s >= 0 And d(a.Value) > s Then ³o¦æ·Q¤£¥X¨Ó­n«ç»ò§ï

¯uªº«Ü³Â·Ð±z

ÁÂÁ«e½ú

gameshop

©êºp«e½ú
µo²{¤@­Ó°ÝÃD
­Y¸Ó¸s²Õªº²Ä¤@­Ó¸ê®ÆÂI´N¬O·í¤Ñªº¸Ü
µ²ªG·|¯Ê¤F¸Ó¸s²Õ¸ê®Æ
½Ð°Ý¸Ó«ç»ò­×¥¿©O

ÁÂÁ«e½ú

gameshop

ÁÂÁ«e½ú¤À¨É
µ{¦¡¥i¥H¨Ï¥Î

¦ý¦pªG§Ú¨C¤@¦C¸ê®Æ³£«Üªø¡A¸Ó¦p¦ó­×§ï©O
§Ú±N d1(a.Value) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value,a.Offset(, 3).Value )
©¹«á¥[¤@­Ó®æ¤l¡A¶]¥X¨Ó·|¦³«¬ºA¿ù»~(À³¸Ó¬O¤U¦C³o¦æ)
Sheets(2).[E1].Resize(d1.Count, 3) = Application.Transpose(Application.Transpose(d1.items))



©êºp¦]¬°¬Ý¤£À´³o¨Çµ{¦¡½X¡A©Ò¥HµLªk¦Û¦æ­×§ï

·Q½Ð±Ð«e½ú

1.IIf¸òIF¦³¤°»ò®t§O©O
2.CreateObject¨ç¼Æ¬O¤°»ò©O
¥t¥~¡A§Ú¦bµ{¦¡½X¤¤§ä¤£¨ì°w¹ï¦U"¸s²Õ"¶i¦æ¤ÀªRªºµ{¦¡½X
½Ð°Ý«e½ú¬O«ç»ò¹F¨ìµ²ªGªº©O

©êºp¦n¹³°ÝÃD¦³ÂI¦h

«D±`·PÁÂ

Hsieh

¦^´_ 1# gameshop

1. Sub ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. d1("¸s²Õ") = Array("¸s²Õ", "¤é´Á", "¼Æ­È")
   
5. With Sheets(1)
   
6. For Each a In .Range(.[A2], .[A2].End(xlDown))
   
7.    s = Date - a.Offset(, 1)
   
8.    d(a.Value) = IIf(IsEmpty(d(a.Value)), s, d(a.Value))
   
9.    If s > 0 And d(a.Value) > s Then
   
10.          d(a.Value) = s
    
11.          d1(a.Value) = Array(a.Value, a.Offset(, 1).Value, a.Offset(, 2).Value)
    
12.    End If
    
13. Next
    
14. End With
    
15. Sheets(2).[E1].Resize(d1.Count, 3) = Application.Transpose(Application.Transpose(d1.items))
    
16. End Sub

½Æ»s¥N½X