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

[µo°Ý] Q15:Q26¬°E2©Ò¿é¤J¤é´Á¥H«eªºÁ`©M(¤w¸Ñ¨M)

¦^´_ 10# olisun   
07# µ{¦¡½X¤¤
Set Rng = .[j15:l26]  §ï¦¨ Set Rng = .[Q15:Q26]
§R±¼ 11.                AD = AD - Evaluate("SUMIF(" & ¤u§@ªí17.[A:A].Address(, , , 1) & ",""<" & d - (W - 1) & """, " & ¤u§@ªí17.Columns(1 + (i * 12) + ii).Address(, , , 1) & ")")
´N¥i¥H

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-8-30 22:19 ½s¿è

¥i¯à¦³ÂI»~·|¡A¦]¬°­ì¨ÓªºP15:P26¬O¬£³ø¡AQ15:Q26¬O§¨³ø
¦b"¬£§¨³ø«Å¶Ç¨®"¤¤¤é´Á¬OA:A     ¬£³ø¬OB:M    §¨³ø¬ON:Y
©Ò¥H§Ú¤U¦C¦¡¤l¦bP15:P26¬O¹ïªº¡A¥i¬OQ15:Q26§Ú´N¤£·|³]¤F
Private Sub CommandButton2_Click()
    With Sheets("¤é³øªí")
             Set c = Sheets("¬£§¨³ø«Å¶Ç¨®").Range("a:a").Find(.[E2], lookat:=xlWhole)
              Arr = Sheets("¬£§¨³ø«Å¶Ç¨®").[a3].Resize(c.Row - 2, 13)
        For i = 15 To 26
      Sheets("¤é³øªí").Range("p" & i).Value = Application.Sum(Application.Index(Arr, 0, i - 13))
            Next i
   End With
End Sub

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-8-31 00:14 ½s¿è

¦^´_ 13# olisun
²Ö­p°Ï¶ô­pºâ
  1. Sub ²Ö­p()
  2. Dim d as Object,sh as WorkSheet,r as Long,a as Range,w As String,dt As Date,b As Range,at As String
  3. Set d = CreateObject("Scripting.Dictionary") '«Ø¥ß¦r¨åª«¥ó
  4. For Each sh In Sheets(Array("¬£§¨³ø«Å¶Ç¨®", "NP¡BCF")) '¦b2­Ó¸ê®Æ¤u§@ªí´`Àô
  5. With sh
  6. r = 3
  7. dt = .Cells(r, 1) '¸ê®Æ¤u§@ªíAÄ檺¤é´Á
  8.   Do Until dt > Sheets("¤é³øªí").[E2]  '·í¤é´Á¶W¹L´N¸õÂ÷°j°é
  9.      For Each a In .Range(.[B2], .[B2].End(xlToRight))  '¸ê®Æ¤u§@ªí²Ä2¦C°µ´`Àô
  10.         w = a.Offset(-1, 0).MergeArea.Cells(1, 1)  '²Ä¤@¦C¹ïÀ³¨ìªº¤º®e¦]¬°¦³¦X¨ÖÀx¦s®æ©Ò¥H¨ú¦X¨Ö½d³ò²Ä¤@®æªº­È¼g¤JÅܼÆ
  11.         d(w & a) = d(w & a) + .Cells(r, a.Column).Value  '¥HºØÃþ¸ò°Ï°ì¦r¦ê°µ¯Á¤ÞÀx¦s¹ïÀ³¨ìªº­È
  12.      Next
  13.      r = r + 1
  14.      dt = .Cells(r, 1)  '¼g¤J¤U¤@­Ó¤é´Á
  15.   Loop
  16.   End With
  17. Next
  18. With Sheets("¤é³øªí")
  19. For Each a In .[B15:B26]
  20. at = a
  21.    For Each b In .[P14:U14]
  22.       If b.Column > 18 Then at = ""  '¦]¬°18Äæ¥H«á¨S¦³¦a°Ï©Ò¥H­nÅý¦a°ÏÅܼÆÅܪÅ
  23.       .Cells(a.Row, b.Column) = d(b & at)  '¼g¦^¤é³øªí
  24.    Next
  25. Next
  26. End With
  27. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  olisun
²Ö­p°Ï¶ô­pºâ
Hsieh µoªí©ó 2010-8-30 22:48



    µª®×¦³°ÝÃD¡A¦pªG§Ú¿é¤J¤é´Á¬O2010/8/5¡A«e­±¦³¤@µ§¬O2010/8/4¡A«á­±¤@µ§¬O2010/8/6¡A
¥L²Î­pªº¬O2010/8/4+2010/8/6¡A¦Ó¤£¬O2010/8/4+2010/8/5

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-8-30 23:31 ½s¿è

¦^´_ 15# olisun
¦pªG§A±Ô­z¨S¿ù¨º´N¬Oµ§»~¦h¤@¦ær=r+1(¤w­×¥¿)
¦pªG¬O¦P¤@©P²Ö­p
  1. Sub ¦P©P²Ö­p()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each sh In Sheets(Array("¬£§¨³ø«Å¶Ç¨®", "NP¡BCF"))
  4. With sh
  5. r = 3
  6.   Do Until .Cells(r, 1) = "" 'Sheets("¤é³øªí").[E2]
  7.      dt = .Cells(r, 1)
  8.      If Year(dt) = Year(Sheets("¤é³øªí").[E2]) And Application.WeekNum(dt, 2) = Application.WeekNum(Sheets("¤é³øªí").[E2], 2) Then
  9.      For Each a In .Range(.[B2], .[B2].End(xlToRight))
  10.         w = a.Offset(-1, 0).MergeArea.Cells(1, 1)
  11.         d(w & a) = d(w & a) + .Cells(r, a.Column).Value
  12.      Next
  13.      End If
  14.      r = r + 1
  15.      dt = .Cells(r, 1)
  16.   Loop
  17.   End With
  18. Next
  19. With Sheets("¤é³øªí")
  20. For Each a In .[B15:B26]
  21. at = a
  22.    For Each b In .[P14:U14]
  23.       If b.Column > 18 Then at = ""
  24.       .Cells(a.Row, b.Column) = d(b & at)
  25.    Next
  26. Next
  27. End With
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦]¬°§Ú®M¦b§Úªº¦¡¤l¸Ì¡A¦Ó§Ú¦¡¤l¤¤¦³Dim d As Date³o¤@¥y¡A©Ò¥H§Ú§ï¦¨
Set g = CreateObject("Scripting.Dictionary")
¦ý¬O¥X²{«¬ºA¤£²Å¦X

TOP

¤W¤@­Ó°ÝÃD¬O§Údim¦³¥[¤F¤@¥ydim g as range¡A§ï¤F¤§«á¤S¥X²{¥t¤@­Ó
Set g = CreateObject("Scripting.Dictionary")
For Each sh In Sheets(Array("¬£§¨³ø«Å¶Ç¨®", "NP¡BCF"))
With sh
R = 3
gt = .Cells(R, 1)
  Do Until gt > Sheets("¤é³øªí").[E2]
     For Each a In .Range(.[B2], .[B2].End(xlToRight))
        W = a.Offset(-1, 0).MergeArea.Cells(1, 1)     ----------- «¬ºA¤£²Å
        g(W & a) = g(W & a) + .Cells(R, a.Column).Value
     Next
     R = R + 1
     dt = .Cells(R, 1)
  Loop
  End With
Next
With Sheets("¤é³øªí")
For Each a In .[B15:B26]
at = a
   For Each b In .[P14:U14]
      If b.Column > 18 Then at = ""
      .Cells(a.Row, b.Column) = g(b & at)
   Next
Next
End With

TOP

·PÁ¡A³o¦¸ok

TOP

        ÀR«ä¦Û¦b : §Ñ¥\¤£§Ñ¹L¡A§Ñ«è¤£§Ñ®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD