[µo°Ý] °}¦C¿z¿ï°ÝÃD(¤w¸Ñ¨M)

softsadwind

½Ð±Ð¦U¦ì¥ý¶i
¦p·~°È¤é³øªí¦p¤U

¤U¸ü (65.33 KB)

2011-8-25 11:57

¥Î¤½¦¡¿z¿ï¥X¤£­«½Æªº«È¤á¦WºÙ
'=T(INDEX('2011'!B:B,MIN(IF(COUNTIF(B$1:B1,'2011'!$B$2:$B$1536),65536,ROW('2011'!$B$2:$B$1536)))))

¤U¸ü (58.52 KB)

2011-8-25 11:57

¦A¥Î=OFFSET('2011'!A$1,MATCH(«È¤á¤@Äý!B2,'2011'!$B:$B,0)-1,0,1,6)
¥h§P§O¸Ó«È¤á³Ì«á¤@¦¸Ápôªº®É¶¡

¤U¸ü (39.64 KB)

2011-8-25 11:57

µM«á¥Î°}¦C¥h§P§O n¤Ñ«e ³Ì«á¤@¦¸Ápô
'=IF(³Ìªñ¤@¦¸Ápô!$A$2:$A$600<(TODAY()-B1),³Ìªñ¤@¦¸Ápô!A2:F600,"")

¤U¸ü (46.66 KB)

2011-8-25 11:57

ªþ¥ó ¤u§@¤é³øªía.rar (11 KB)

¤U¸ü¦¸¼Æ: 17

2011-8-25 11:57

¦]¬°³o¼Ë¤l°µ ¦pªG¶W¹L2000¦æ¤§«á¡A¥u­n¦³¤@¹j¸ê®ÆÅܧó¡A´N·|­«·s­pºâ¤@¦¸¡A¹q¸£´N¤@ª½¹y¡C§Ú¥u¯à¥ÎÀx¦s­Èªº¤è¦¡¡A¥h´î¤Öµ{¦¡§PÂ_ªº¦¸¼Æ¡A©ÎªÌ§â«á­±¨â­Ó¤À­¶²¾¨ì¥t¥~¤@¦UÀɮסA¦³»Ý­n¦A¥´¶}¡A¤~¯à´î¤Ö¤@ª½§PÂ_ªº°ÝÃD¡C¤£ª¾¹D¦³¨S¦³°ª¤â¥i¥H«ü¾É¤@¤U¡A«ç¼Ë¥Îvba¨Ó¼g¡C·P¿E¤£ºÉ¡C

oobird

§P§O n¤Ñ«e ³Ì«á¤@¦¸Ápô

³Ì²×¥Øªº¬O­n±o¨ì¤°»ò¼Ëªºµ²ªG¡H

softsadwind

¦^´_ 2# oobird
³o¬O¶}µo¥Îªº¤é³øªí
¶¡¹jN¤Ñ¡]¤@¯ë¬O30¤Ñ)¦AÁpô¤@¦¸¡A¬O¬°¤FÁקK¹L«×±K¶°ªº¥á«H¡A³Q·í¦¨©U§£«H¡C
©Ò¥H³o­Ó¿z¿ï¬O¤@ª½«ùÄòªº
¨CN¤Ñ´N·|¥X²{«È¤á¦WºÙ¡A¤è«K·~°È¤H­û¥h¹LÂo¦W³æ©M±H¥X¼s§i«H¥ó¡C

§Ú·Q¥Î­Ó¨Ò¤l¨Ó¬Ý¡A¥i¯à·|§ó²M·¡

A«È¤á 8/25Ápô¹L¡A¦]¬°¬O¶}µo¤¤..©Ò¥H¤£¨£±o·|¦³¦^À³...¦]¦¹¥Lªº³Ì«á¤@¦¸Ápôªº¤é¤lÁÙ¬O¦b8/25
¨ì9/25¨º¤Ñ¡A§P§ON¤Ñ(N=30) ¡AA«È¤á´N·|¦b¥X²{¤@¦¸¡C ·~°È®Ú¾Ú³o­Ó¦W³æ¡A¦A±Hµo¤@¦¸¼s§i«H¡C³o®É­Ô¡A´N·|§â³Ì«á¤@¦¸Ápôªº¤é´Á§ï¦¨9/25¡A¤â°Ê¶×¤J¤é³øªí¤º¡C

©Ò¥HA«È¤á¦b¤é³øªí·|¦³¨âÄæ
9/25  a«È¤á
8/25 a«È¤á

µ¥¨ì10/25¡A¤S·|³Q¿z¿ï¥X²{¤@¦¸¡A¦A¤â°Ê§ó·sa«È¤áªºÁpô¤é´Á¬°10/25
¤é³øªí´NÅܦ¨
10/25 a«È¤á .......
9/25 a«È¤á.....
8/25 a«È¤á......

¦pªG¤£¥ÎN ¨º´N¬Oª½±µ¥Î30±a¶i¥h¡A¥H30¤Ñ¬°¤@­Ó´`Àô¡C

GBKEE

¦^´_ 3# softsadwind

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim i As Double
   
4.     With Sheets("2011")
   
5.         .AutoFilterMode = False
   
6.         For i = .Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
   
7.             If Date = DateAdd("M", 1, .Cells(i, "A")) Then  '¶¡¹j1­Ó¤ë
   
8.                 With .Cells(i, "A").Resize(, 6)
   
9.                     .Copy
   
10.                     .Insert xlShiftDown
    
11.                     .Cells(0, 1) = Date
    
12.                     .Cells(0, 5) = ""
    
13.                 End With
    
14.             End If
    
15.         Next
    
16.         .Range("A1").AutoFilter 5, "="
    
17.     End With
    
18. End Sub

½Æ»s¥N½X

softsadwind

·PÁ¡A¤£¹L³oµ{¦¡¶]¥X¨Ó¬O·|ª½±µÀ°§A§â­è¦nº¡¤@­Ó¤ëªº¦C¥X¨Ó..
¨Ãª½±µÀ°§A§â¸ê®Æ¥H¤µ¤Ñªº¦W¸q·s¼W¤W¥h¡A¨Ã³æ¿WÅã¥Ü¥X¨Ó¡C³o¼Ë¤l¬O¤è«Kª½±µ¥Î¸Ó­¶±Hµo«H¥ó¡C

¤£¹L·|µo¥Í¤@­Ó°ÝÃD¡A´N¬O¹J¨ì¤»¤é¨S¤W¯Z¡A³o¨â¤Ñªº¸ê®Æ ´N·|¸õ¹L Åý¹L..
¿z¿ï±ø¥ó­nÅܦ¨30¤é¤§«eªº³£­n¦C¥X¨Ó¡A§â³o¨Ç­pºâµ²ªG½Æ»s¨ì·s¤À­¶¡A
µM«á¦A¥Î½Æ»sªº¤è¦¡ ±µÄò2011­¶­±ªº³Ì«á¤@¦æ
2011¨º¤@­¶´Nºû«ù­ì»ª¡C

GBKEE

¦^´_ 5# softsadwind
µM«á¦A¥Î½Æ»sªº¤è¦¡ ±µÄò2011­¶­±ªº³Ì«á¤@¦æ,2011¨º¤@­¶´Nºû«ù­ì»ª¡C  ½Ð§A¨Ó§¹¦¨

1. Sub Ex()
   
2.     Dim D As Object, Rng As Range, E As Variant
   
3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
4.     Set Rng = Sheets("2011").[A2]
   
5.     Do While Rng <> ""
   
6.         If D.exists(Rng.Cells(1, 2).Value) = 0 Then
   
7.             Set D(Rng.Cells(1, 2).Value) = Rng.Resize(, 6)
   
8.         Else
   
9.         If D(Rng.Cells(1, 2).Value).Cells(1) < Rng Then Set D(Rng.Cells(1, 2).Value) = Rng.Resize(, 6)
   
10.         End If
    
11.         Set Rng = Rng.Offset(1)
    
12.     Loop
    
13.     Sheets("³Ìªñ¤@¦¸Ápô").UsedRange.Offset(1).Clear
    
14.     Sheets("Last - N").UsedRange.Offset(2).Clear
    
15.     For Each E In D.Items
    
16.         If Date >= DateAdd("M", 1, E.Cells(1)) Then E.Copy Sheets("Last - N").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
17.         E.Copy Sheets("³Ìªñ¤@¦¸Ápô").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
18.     Next
    
19.     Set D = Nothing
    
20.     Set Rng = Nothing
    
21. End Sub

½Æ»s¥N½X

softsadwind

¦^´_ 6# GBKEE


    ÁÂÁ §Ú¦^¤½¥q¦A¨Ó´ú¸Õ¡A´ú¸Õ«á¦A¦^õX

Hsieh

¦^´_ 7# softsadwind
·ÓµÛ§Aªº¤½¦¡·N¸q¡A§ï¦¨¨Ï¥ÎVBA±Ô­z

1. Sub ex()
   
2. Dim Ar()
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. With ¤u§@ªí1
   
6.    For Each a In .Range(.[B2], .[B65536].End(xlUp))
   
7.       d(a.Value) = a.Offset(, -1).Value
   
8.       d1(a.Value) = a.Offset(, -1).Resize(, 6).Value
   
9.    Next
   
10. End With
    
11. With ¤u§@ªí2
    
12. .[B2:B65536] = ""
    
13. .[B2].Resize(d.Count, 1) = Application.Transpose(d.keys)
    
14. End With
    
15. With ¤u§@ªí3
    
16. .[A2:F65536] = ""
    
17. .[A2].Resize(d1.Count, 6) = Application.Transpose(Application.Transpose(d1.items))
    
18. End With
    
19. With ¤u§@ªí4
    
20. n = .[B1]
    
21. For Each ky In d.keys
    
22. If Date - n >= d(ky) Then
    
23. ReDim Preserve Ar(s)
    
24. Ar(s) = d1(ky)
    
25. s = s + 1
    
26. End If
    
27. Next
    
28. .[A3:F65536] = ""
    
29. .[A3].Resize(s, 6) = Application.Transpose(Application.Transpose(Ar))
    
30. End With
    
31. End Sub

½Æ»s¥N½X

softsadwind

¦^´_ 6# GBKEE

·PÁª©¤j¡A³o¤@ª©´N°÷¥Î¤F...
´ú¸Õ°_¨Ó¬Ookªº...
¦p¤£¤¶·N §Ú·|§â³o­Ó½d¨Ò©ñ¦b­Ó¤HÁy®Ñ¤W
²¦³º°Ý¨Óªº ¤ñ¸û®e©ö§Ñ°O
±o§ä­Ó¦a¤è¼Ð°O°_¨Ó:>

softsadwind

¦^´_ 8# Hsieh

·PÁ¡A "ÁÂ"¤j¤j¡A§Ú¦b2003¨Ï¥Î¦¹µ{¦¡½X¡A¥X²{¡y¨S¦³ª«¥ó¡z¡A±À´úÀ³¸Ó¬O2010ªº»yªk¡C©Ò¥H¼È®É¨S¿ìªk¦^À³³o¤@½g¡C