ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

§Ú­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°Ú!!!!

000.rar (27.25 KB)

excel §PÂ_¥[Á`

¦^´_ 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))}
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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«ä

000-2.rar (29.92 KB)

§PÂ_¥[Á`(FÄæ)

TOP

¦^´_ 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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-2-10 00:27 ½s¿è
  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.     stauCalc = Application.Calculation
  5.     Application.Calculation = xlCalculationManual

  6.     LR = Cells(Rows.Count, 2).End(xlUp).Row
  7.     Range("G2", "H" & LR).Clear
  8.     nC(1) = LR
  9.     For i = LR To 2 Step -1
  10.         If i = nC(1) Then
  11.             'AÄæ¥Ñ¤U©¹¤W§ä²Ä1­Ó¤Î²Ä2­Ó "1"
  12.             n = 0
  13.             For j = i - 1 To 2 Step -1
  14.                 If Cells(j, 1) = "1" Then n = n + 1: nC(n) = j
  15.                 If n = 2 Then Exit For
  16.             Next
  17.             '¥Ñ²Ä1­Ó "1" ©¹¤U§ä²Ä3­Ó©P¤@(³Ì«á¤@¤é)
  18.             m = 0: nC(3) = nC(1)
  19.             For k = nC(1) To i
  20.                 Set xR = Cells(k, "C")
  21.                 If Weekday(xR, 2) < Weekday(xR(2), 2) Then m = m + 1
  22.                 If m = 3 Then nC(3) = k: Exit For
  23.             Next
  24.         End If
  25.         If n <> 2 Then: Exit For
  26.         Cells(i, "G") = "=SUM(D$" & nC(2) & ":D" & i & ")"
  27.         If i <= nC(3) Then Cells(i, "H") = "=G" & i
  28.     Next
  29.     Application.Calculation = stauCalc
  30. End Sub
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD