[µo°Ý] excel §PÂ_¥[Á`

bill740615

§Ú­n¸ß°Ý¡A§Úªº¸ê®Æexcel³sµ²¦bhttp://1drv.ms/1PKFGce

»Ý­n±q³Ì©³¤U2013/12/20¶}©lºâ¡AE525Àx¦s®æÀ³¸Ó¥[Á`D525:D489
¤]´N¬O»¡¡AE525­n¥ý§PÂ_±qA524(©¹«e¤@®æ)¶}©l²Ä2­Ó1¥X²{¬°¤îªº¬O¦bA489
©Ò¥HE525­n§âD525:D489¥þ³¡¥[Á`°_¨Ó¡C

¦AÁ|¨Ò¡A¦pªG¬OE410¡A¦]¬°±qA409¶}©l©¹¤W§PÂ_²Ä2­Ó1¥X²{¦bA388
©Ò¥HE410À³¸Ó¥[Á`D410:D388¡C

½Ð°Ý¦U¦ì¤j¤j¡A³o¼Ëªº¸Ü¡Aexcel¸Ó¦p¦ó¶]? ¸÷¨D°Ú!!!!

ML089

1. Sub ex()
   
2.     Dim stauCalc%, nC%(1 To 3), n%, m%, LR%, i%, j%, k%, xR As Range
   
3.     ThisWorkbook.Sheets("000").Select
   
4. 
   
5.     stauCalc = Application.Calculation
   
6.     Application.Calculation = xlCalculationManual
   
7. 
   
8.     LR = Cells(Rows.Count, 2).End(xlUp).Row
   
9.     Range("G2", "H" & LR).Clear
   
10.     nC(1) = LR
    
11.     For i = LR To 2 Step -1
    
12.         If i = nC(1) Then
    
13.             'AÄæ¥Ñ¤U©¹¤W§ä²Ä1­Ó¤Î²Ä2­Ó "1"
    
14.             n = 0
    
15.             For j = i - 1 To 2 Step -1
    
16.                 If Cells(j, 1) = "1" Then n = n + 1: nC(n) = j
    
17.                 If n = 2 Then Exit For
    
18.             Next
    
19.             '¥Ñ²Ä1­Ó "1" ©¹¤U§ä²Ä3­Ó©P¤@(³Ì«á¤@¤é)
    
20.             m = 0: nC(3) = nC(1)
    
21.             For k = nC(1) To i
    
22.                 Set xR = Cells(k, "C")
    
23.                 If Weekday(xR, 2) < Weekday(xR(2), 2) Then m = m + 1
    
24.                 If m = 3 Then nC(3) = k: Exit For
    
25.             Next
    
26.         End If
    
27.         If n <> 2 Then: Exit For
    
28.         Cells(i, "G") = "=SUM(D$" & nC(2) & ":D" & i & ")"
    
29.         If i <= nC(3) Then Cells(i, "H") = "=G" & i
    
30.     Next
    
31.     Application.Calculation = stauCalc
    
32. End Sub

½Æ»s¥N½X

ML089

¦^´_ 4# bill740615

E525 {=SUM(OFFSET(D525,,,LARGE(A$1:A524*ROW($1:524),2)-ROW()-1))}

F525 =IF(2<FREQUENCY((MOD(C$2:C525,7)<MOD(C$3:C526,7))*ROW($3:526),CHOOSE({1,2,3},ROW(),LOOKUP(1,0/A$2:A525,ROW($2:525)),0)),"",E525)

¤½¦¡©¹¤W½Æ»s

bill740615

¦^´_ 2# ML089


M¤j±z¦n¡AÁÂÁ±z¡A¶]¥X¨Óªº¸ê®Æ¨S¦³¿ù!!!
¥t¥~§Ú·Q·s¼W§PÂ_¥[Á`±ø¥ó¦bFÄæ¡A³o¦¸§PÂ_µy·L½ÆÂø
¦pªG§Ú·Q¦b¨C­Ó1¨º¤@¤Ñ¥X²{«eªº²Ä¤G­Ó¶g¤@¶}©lºâ¡A©óFÄæ¥[Á`¨ì¤U­Ó1¬°¤î(¦pªG¨S¦³¶g¤@¡A´N©¹¤W©µ¦Ü¶g¤G¶}©lºâ¡A¦pªGÁÙ¬O¨S¦³¶g¤G¡A´NÄ~Äò©¹¤W©µ¦Ü©P¤T¶}©lºâ¡A¨Ì¦¹Ãþ±À)

¨BÆJÁ|¨Ò:
1.A508ªº1¨º¤é´Á¬O2014/1/15¡A¨Ì¤é¤lºâ«e­±²Ä¤G­Ó¶g¤@¬OB519ªº2013/12/30¡A©Ò¥HF519Àx¦s®æ­n¥[Á`D519:D489¡A¬G±qF519¥[Á`¨ìF509°±¤î¡AF508¦]¬°¦bA508¦³1¡A´N¤£»Ý­n¥[¤F
2.¨Ì§Ç¤W¥hªº¸Ü¡A¦]¬°¤U­Ó1¥X²{ªº¨º¤@¤Ñ2014/2/19«e¨â­Ó§«ôªº¶g¤@¬O2014/1/27¡A©Ò¥HF508~F501¤£¥Î¥[¡A¦ÓF500~F490´N­n¥[¨ìD470¡C
3.¦pªG¬O2014/6/18¨º¤@¤Ñªº«e¨â­Ó§«ôªº¶g¤@À³¸Ó¬O2014/6/2¡A¦ý¬O¸ê®Æ¨S¦³³o¤@¤Ñ¡A©Ò¥H´N¥u¯à±q2014/6/3¶g¤G¶}©l©¹¤W¥[¡A©Ò¥HF419µ¥©óD419:D388¡A¨Ì§Ç¥[¨ìF409°±¤î

½Ð°ÝM¤j¡A³o¼Ëªº§PÂ_¸Ó¦p¦ó¶i¦æ©O?
¦]¬°¸ê®ÆÁ`¼Æ¹F5000µ§¡A©Ò¥H§Ú»Ý­n¤F¸Ñ¸Ó¦p¦óµ{¦¡¤Æ¶i¦æ
¹q¤lÀɧڤw¸g¥ý¤â°Ê§ó·s¤F¡A¤è«K¤j¤j¤F¸Ñ§Úªº·N«ä

Hsieh

¦^´_ 1# bill740615

µ{¦¡°Ï´N´£¨Ñ¤@­ÓVBA¼gªk

1. Sub ex()
   
2. For i = Application.CountA([C:C]) To 2 Step -1 '©¹¤W°j°é
   
3. r = i: k = 0: x = Cells(i, 4) '¦U¦Cªì©l­È
   
4.   Do Until k = 2 Or r <= 2  'ª½¨ìAÄæ¥X²{2¦¸1©Î¶]¨ì²Ä2¦C´N¸õ¥X°j°é
   
5.      r = r - 1 '©¹¤W¤@¦C
   
6.      k = IIf(Cells(r, 1) = 1, k + 1, k) '¦pªGAÄæ¬O1´N¼W¥[­p¼Æ
   
7.   Loop
   
8.   If k = 2 Then Cells(i, 5) = Application.Sum(Range(Cells(i, 4), Cells(r, 4))) Else Cells(i, 5) = "" '¼g¤J¥[Á`­È
   
9. Next
   
10. End Sub

½Æ»s¥N½X

ML089

¦^´_ 1# bill740615


E525 ¥H°}¦C¤½¦¡¿é¤J (CTRL+SHIFT+ENTER ¤TÁä»ô«ö¤è¦¡¿é¤J)¡A¦A½Æ»s¨ì¨ä¥LÀx¦s®æ
{=SUM(OFFSET(D525,,,LARGE(A$1:A524*ROW($1:524),2)-ROW()-1))}