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

½Ð°Ý§Ú¦p¦ó±N®w¦sªº¬¡­¶ªº¸ê®Æ,±N¥¦Àx¦s¨ì¦U¬¡­¶Ã¯?

½Ð°Ý§Ú¦p¦ó±N®w¦sªº¬¡­¶ªº¸ê®Æ,±N¥¦Àx¦s¨ì¦U¬¡­¶Ã¯?

½Ð°Ý§Ú¦p¦ó±N®w¦sªº¬¡­¶ªº¸ê®Æ,±N¥¦Àx¦s¨ì¦U¬¡­¶Ã¯°µ¥[Á`,¨Ò¦p:100A¦b6/1·í¤ÑÁ`¨Ñ¶R¤F¦h¤Ö­Ó©Î½æ¤F¦h¤Ö­Ó,§â¥¦¦s¨ì¥x¥_ªº¬¡­¶¤¤¹³¶À¦âªº¼Ë¤l.ÁÂÁ§A¯àÀ°¦£. TEST.rar (5.88 KB)

¦^´_ 1# flask


    §Úı±o§Aªº¸ê®Æ¦pªG¬O³o¼Ë·|¤ñ¸û¦n³B²z
    ¤À¨ì¨ä¥L¤u§@ªí¤§«á­n°µ·JÁ`¤]¤ñ¸û¤è«K
[attach]6490[/attach]
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 2# freeffly


    ¹Ï¶K¤£¥X¨Ó
    ±N¸ê®Æ®æ¬O§ï¦¨ªþÀɪº®æ¬OÀ³¸Ó·|¤ñ¸û¦n
     
¦bAÄæ´¡¤J¤@Äæ·í¤é´ÁÄæ¡A­ì¥»¦³Åã²{¥x¥_¨º¨Çªº¤£­n¥ÎÁY±Æ
³o¼Ë¸ê®Æ·|¤ñ¸û¦n³B²z


[attach]6491[/attach]
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 2# freeffly


    ¹Ï¶K¤£¥X¨Ó
    ±N¸ê®Æ®æ¬O§ï¦¨ªþÀɪº®æ¬OÀ³¸Ó·|¤ñ¸û¦n
     
¦bAÄæ´¡¤J¤@Äæ·í¤é´ÁÄæ¡A­ì¥»¦³Åã²{¥x¥_¨º¨Çªº¤£­n¥ÎÁY±Æ
³o¼Ë¸ê®Æ·|¤ñ¸û¦n³B²z
freeffly¤j¤jÁÂÁ§A,¬Ý¤£¤ÓÀ´§Aªº·N«ä!

TOP

¦^´_ 4# flask
¸ê®ÆÀ³¿í¦u¸ê®Æ®w«Ø¥ß³W«h­n°µ¤ÀªR´N¤ñ¸û¤è«K

¥H§A¥Ø«eªº¸ê®Æ­n¤À­¶À˵øµ{¦¡½X¦p¤U
  1. Sub ex()
  2. Dim a As Range
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. Set d = CreateObject("Scripting.Dictionary")
  5. With Sheets("®w¦s")
  6. i = 1
  7. Do Until .Cells(1, i) = ""
  8.   myday = .Cells(1, i).Value
  9.      For Each a In .Range(.Cells(2, i), .Cells(.Rows.Count, i).End(xlUp))
  10.      If IsEmpty(d(Val(a))) Then d(Val(a)) = a.Offset(, 1).Value
  11.         ar = Array(a.Offset(, 3), a.Offset(, 4))
  12.         If IsEmpty(dic(a & d(Val(a)))) Then
  13.            dic(a & d(Val(a))) = ar
  14.            Else
  15.            dic(a & d(Val(a))) = Array(dic(a & d(Val(a)))(0) + ar(0), dic(a & d(Val(a)))(1) + ar(1))
  16.         End If
  17.      Next
  18.      For Each ky In d.items
  19.         With Sheets(ky)
  20.         s = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1
  21.         .Cells(1, s) = myday: .Cells(2, s) = "¶R": .Cells(2, s + 1) = "½æ"
  22.            For Each a In .Range(.[A3], .Cells(.Rows.Count, 1).End(xlUp))
  23.                .Cells(a.Row, s).Resize(, 2) = dic(a & ky)
  24.            Next
  25.         End With
  26.      Next
  27.      dic.RemoveAll: d.RemoveAll
  28.      i = i + 5
  29.   Loop
  30. End With
  31. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁÂHsieh ¶W¯Åª©¥D,§Úª¾¹D§Aªº·N«ä¤F,¤]ÁÂÁÂfreeffly¤j¤jªºÀ°¦£...

TOP

Hsieh¤j¤j§A¦n,§Ú§â¥¦­×§ï«á,§Ú·Q°µ³]©w­²¦¡¤Æ,¦p¦ó¤£¿E¬¡¦U¬¡­¶¦Ó¯à¹F¨ì­n¨D,

Sub ext()
Dim a As Range, i As Integer, c As Integer
Set dic = CreateObject("Scripting.Dictionary")
Set d = CreateObject("Scripting.Dictionary")
With Sheets("®w¦s")
i = 1
Do Until .Cells(1, i) = ""
  myday = .Cells(1, i).Value
     For Each a In .Range(.Cells(2, i), .Cells(.Rows.Count, i).End(xlUp))
   
        ar = Array(a.Offset(, 3), a.Offset(, 4))
        If IsEmpty(dic(a & d(Val(a)))) Then
           dic(a & d(Val(a))) = ar
           Else
           dic(a & d(Val(a))) = Array(dic(a & d(Val(a)))(0) + ar(0), dic(a & d(Val(a)))(1) + ar(1))
        End If
     Next
   
    For c = 2 To Worksheets.Count
   
        With Sheets(c)

        s = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1

        .Cells(1, s) = myday: .Cells(2, s) = "¶R¶i": .Cells(2, s + 1) = "½æ¥X"

           For Each a In .Range(.[A3], .Cells(.Rows.Count, 1).End(xlUp))

               .Cells(a.Row, s).Resize(, 2) = dic(a & d(Val(a)))
L = a.Row
           Next
       Sheets(c).Select        .UsedRange.Columns.AutoFit
        .Range(.Cells(2, s), .Cells(L, s)).Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="10000"
        Selection.FormatConditions(1).Font.ColorIndex = 3
        .Range(.Cells(2, s + 1), .Cells(L, s + 1)).Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="10000"
        Selection.FormatConditions(1).Font.ColorIndex = 5
        End With

     Next c

     dic.RemoveAll: d.RemoveAll

     i = i + 5

  Loop

End With

End Sub

TOP

.UsedRange.Columns.AutoFit
        .Range(.Cells(2, s), .Cells(L, s)).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="10000"
         .Range(.Cells(2, s), .Cells(L, s)).FormatConditions(1).Font.ColorIndex = 3
        .Range(.Cells(2, s + 1), .Cells(L, s + 1)).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="10000"
       .Range(.Cells(2, s + 1), .Cells(L, s + 1)).FormatConditions(1).Font.ColorIndex = 5
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁÂHsieh ¤j¤j,§Ú²×©ó¤F¸Ñ¤F,ÁÂÁ§A...

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD