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

[µo°Ý] ¡´(µo°Ý)¤j¶q¸ê®Æ³sÄò·s¼WÄæ¦ì¥h­pºâªº­Èªº°ÝÃD

[µo°Ý] ¡´(µo°Ý)¤j¶q¸ê®Æ³sÄò·s¼WÄæ¦ì¥h­pºâªº­Èªº°ÝÃD

¥»©«³Ì«á¥Ñ yagami12th ©ó 2012-1-8 12:05 ½s¿è

¤@ª½µLªk§¨±aÀɮ׶K¨ì¤å³¹¸Ì­±¡A¸Õ¤F¦n´X¦¸¡A¦³¤Hª¾¹D­ì¦]¶Ü

½Ð±Ð¤@­Ó¤j¶q¸ê®Æ³sÄò·s¼WÄæ¦ì­pºâªº°ÝÃD¡A¦bGBKEE¤jªºÀ°¦£¤U¡A¼Æ¾Ú¤ÀÃþ¦n¤F¤§«á¡A¸ê®Æª¬ªp¦p¹Ï1¨ì¹Ï4©Ò¥Ü¡G

¤ÀÃþ¦nªº¼Æ¾ÚªþÀɦp¤U¡G
2011_01.rar (512.4 KB)

¸ê®Æ»¡©ú¡G

§Ú­Ì¥H2011/1¤ë¬°¨Ò¡A¸Ì­±¦³2011_01_c¸ò2011_01_p¨â­Ó¸ê®Æ§¨¡A³o¨â­Ó¸ê®Æ§¨¸Ì­±¤À§O¦³34­ÓexcelÀɮסA¦b2011_01_c¸ÌÀY¦³2011_01_6900_c.xls¨ì2011_01_10400_c.xls(¦@34­ÓÀÉ®×)¡A2011_01_pªº¸ê®Æ§¨¸Ì¤]¬O¤@¼Ë¦³34­ÓexcelÀÉ(¦P2011_01_c)
¦p¹Ï1¨ì¹Ï4©Ò¥Ü¡G


¹Ï¤@¡G

¹Ï¤G¡G

¹Ï¤T¡G

¹Ï¥|¡G
-------------------------------------------------------------------------------------------
¥H2011_01_c¸ÌÀYªº2011_01_6900_c.xls¬°¨Ò¡G

¨BÆJ1(­n­pºâªºªF¦è)¡G
§Ú¥ý¦bo1Äæ¦ì·s¼W¦r¦ê"°ª»ù´î§C»ù"¤§«áo2­pºâ¤½¦¡¬°g2-h2¡A­pºâ§¹²¦©¹¤U½Æ»s
¦A¨ìp1Äæ¦ì·s¼W¦r¦ê"¦¨¥æ¶qÅܤÆ"(¦]¬°­nºâÅܤƶq¡A©Ò¥H±qp3Äæ¶}©l¤~¦³­pºâµ²ªG¡Ap2Ä欰ªÅ¥Õ)¡Ap3Äæ=j3-j2¤§«á©¹¤U½Æ»s
§¹¦¨¦sÀÉ¡AÃö³¬ÀɮסA¦A¶}2011_01_7000_c.xls¡A¦P¼Ë¦bo1Äæ·s¼W¦r¦ê"°ª»ù´î§C»ù"¸òp1Äæ·s¼W¦r¦ê"¦¨¥æ¶qÅܤÆ"¡A¤§«áªº­pºâ¦P¤W¡A
§¹¦¨¦sÀÉ¡AÃö³¬ÀɮסA¦A¶}2011_01_7100_c.xls¡A¤@ª½°µ¨ì2011_01_10400_c.xls(2011_01¸ê®Æ§¨¸Ìªº³Ì«á¤@­ÓÀÉ®×)¡Aµ²§ô¡C

¨BÆJ2¡G
±µµÛ¶}2011_01_p¸ê®Æ§¨¡A¦A­pºâ2011_01_6900_p.xls¨ì2011_01_10400_p.xls(­pºâ¤è¦¡¦P¤W)¡Aµ²§ô¡C

¨BÆJ3¡G
¥Ñ©ó2011_01¸Ìªº2011_01_c(¸ÌÀY34­Óexcel¦b¤W­±¤w­pºâ§¹²¦)¡A2011_01_p(¸ÌÀY34­Óexcel¦b¤W­±¤]¤w­pºâ§¹²¦)¡Aµ²§ô
±µµÛ¶}2011_02ªº¸ê®Æ¡A¦A­pºâ§¹2011_02_c¸ò2011_02_pªº¸ê®Æ§¨¸Ì­±ªº¦U30´X­ÓexcelÀÉ¡A¦sÀɧ¹²¦«á¡A¦A±µµÛ°µ2011_03ªº¸ê®Æ¤@ª½°µ¨ì2011_12(­n­pºâªºªF¦è¦P¨BÆJ1)
----------------------------------------------------------------------------------------------
¨BÆJ1 ­pºâµ²ªGªº½d¨Ò(¼Æ¦r¬OÀH«K¥´ªº¡A¦]¬°±Æª©¤@ª½µLªk§¹¦¨¡G(¥u­n­pºâ³Ì¥kÃä¨â­ÓÄæ¦ìªº¸ê®Æ)(µù¡G¥[µùÃC¦â·|¶]±¼±Æª©©Ò¥H¨S¥[µùÃC¦â)
|    A1             |-----|    F1   |  ³Ì°ª»ù(F1) | ³Ì§C»ù(G1)  |  H1    |¦¨¥æ¶q(J1)         |  °ª»ù´î§C»ù(O1)  |  ¦¨¥æ¶qÅܤÆ(P1) |
|2010/12/16  |-----|    F2   |  100            |  20               |  H2    |2000                 |  80                        |                           |
|2010/12/17  |-----|    F3   |  200            |  10               |  H2    |11000                 |  190                      |    9000              |
¡D¡D
¡D¡D
2010/1/19                                                                                                    

---------------------------------------------------------------------------------------
¸É¥R»¡©ú¡A¹Ï¤­¡G

[attach]9122[/attach]

------------------------------------------------------------------------------------------------

§Ú¥Î¥¨¶°¿ý»sªºÀɮסAÀx¦s®æªº«ü©w¤è¦¡¥Î¿ýªº·|Åܪº©Ç©Çªº¡A¦]¬°¸ê®Æ¤ñ¸û¦h¡A©Ò¥H·Q¦V¦U¦ì½Ð±Ð¡A¥ýÁÂÁ¤F¡C
µ{¦¡½X¦p¤U¡G

Sub ¥¨¶°1()

    ChDir "C:\Users\user\Desktop\¿ï¾ÜÅv¸ê®Æ¡A«ö¦~¥÷¤ë¥÷¾ã²z\2011¿ï¾ÜÅv¾ã²z§¹²¦\2011_01\2011_01_C"
    Workbooks.Open Filename:= _
        "C:\Users\user\Desktop\¿ï¾ÜÅv¸ê®Æ¡A«ö¦~¥÷¤ë¥÷¾ã²z\2011¿ï¾ÜÅv¾ã²z§¹²¦\2011_01\2011_01_C\2011_01_6900_C.xlsx" '³o¸Ì¬O¶}±Ò«ü©wªºÀÉ®×
    Range("O1").Select                              '¬O«ü¦bRange(a1)ªºÄæ¦ì¶K¤W¶Ü¡H¤£¤Ó½T©w
     With Selection                                  '±q³o¦æ¨ì²Ä13¦æ³£¬Ý¤£À´¡A¦ý·Pı¤£¤Ó­«­n
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     End With
   
    ActiveCell.FormulaR1C1 = "°ª»ù´î§C»ù"            '¦b­^¤å¦r¥À O1Äæ¦ì(¤£¬O¹s1)¿é¤J¦r¦ê"°ª»ù´î§C»ù"¡A¤£ª¾¹D¬°¤°»ò·|Åܦ¨R1C1³oºØ«ü©w®æ¦¡¡A¥Îcell(1,15)¤ñ¸û®e©ö²z¸Ñ
    Range("O2").Select                               'ÂI¿ïO2(­^¤å¦r¥ÀO2)Äæ¦ì
    ActiveCell.FormulaR1C1 = "=RC[-8]-RC[-7]"        'µM«á¦bO2Äæ¦ì=g2-h2
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O26") 'ºâ¦nµ²ªG½Æ»s¨ìO2¨ìO26
    Range("O2:O26").Select
    Range("P1").Select                               '¦AÂI¿ïP1Äæ¦ì
     With Selection                                  '±q³o¦æ¨ì  .MergeCells=Flase ¬Ý¤£À´
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
    ActiveCell.FormulaR1C1 = "¦¨¥æ¶qÅܤÆ"          '¦b­^¤å¦r¥À p2Äæ¦ì¿é¤J¦r¦ê"¦¨¥æ¶qÅܤÆ"¡A¤£ª¾¹D¬°¤°»ò·|Åܦ¨R1C1³oºØ«ü©w®æ¦¡¡A¥Îcell(1,16)¤ñ¸û®e©ö²z¸Ñ
    Range("P3").Select
    ActiveCell.FormulaR1C1 = "=RC[-6]-R[-1]C[-6]" '¦bp3®æ¦ì¿é¤J¤½¦¡=j3-j2
    Range("P3").Select                             '°±¦bp3Äæ¦ì
    Selection.AutoFill Destination:=Range("P3:P26") '±Np3®æ¦ì­pºâµ²ªG½Æ»s¨ìp26Äæ¦ì
    Range("P3:P26").Select                         'µøµ¡®Ø¦bp3:p26°±¦í
    ActiveWorkbook.Save                            '¦sÀÉ
    ActiveWindow.Close                             'ÃöÀÉ
   
   
   
    Workbooks.Open Filename:= _
        "C:\Users\user\Desktop\¿ï¾ÜÅv¸ê®Æ¡A«ö¦~¥÷¤ë¥÷¾ã²z\2011¿ï¾ÜÅv¾ã²z§¹²¦\2011_01\2011_01_C\2011_01_7000_C.xlsx"  'Ä~Äò¶}2011_01_7000_C.xlsxªºÀɮסA¦P¤W©Ò­z¡AÄ~Äò°µ
    Range("O1").Select
   
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
    ActiveCell.FormulaR1C1 = "°ª»ù´î§C»ù"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=RC[-8]-RC[-7]"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O26")
    Range("O2:O26").Select
    Range("P1").Select
   
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
    ActiveCell.FormulaR1C1 = "¦¨¥æ¶qÅܤÆ"
    Range("P3").Select
    ActiveCell.FormulaR1C1 = "=RC[-6]-R[-1]C[-6]"
    Range("P3").Select
    Selection.AutoFill Destination:=Range("P3:P26")
    Range("P3:P26").Select
    ActiveWorkbook.Save
    ActiveWindow.Close

End Sub

¸ê®Æ§¨¹Ï1¨ì2.rar (634.82 KB)

¸ê®Æ§¨¹Ï3¨ì4.rar (635.06 KB)

¸ê®Æ§¨¹Ï5.rar (362.35 KB)

¸ê®Æ§¨¹Ï5.rar (362.35 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-1-8 13:16 ½s¿è

¦^´_ 1# yagami12th

->   ¥Î§A³o¸Ìµo¤åªºÀÉ®×  °õ¦æ¦¹µ{¦¡
  1. Const ThePath = "d:\You\" '«ü©w¦s©ñªº¥D¸ê®Æ§¨
  2. Sub Ex()
  3. Dim d As Object, SavePath As String, Sh As Worksheet, R As Variant, E As Variant, Newbook As Workbook
  4. Dim MonPath As String, ¿ï¾ÜÅv As String, ¼i¬ù»ù As String
  5. Application.DisplayAlerts = False '°±¤î¨t²Î ªº´£¥Ü
  6. Application.ScreenUpdating = False '°±¤î¿Ã¹õ§ó·s¥\¯à
  7. Set d = CreateObject("scripting.Dictionary") '«Ø¥ß¦r¨åª«¥ó
  8. SavePath = Dir(ThePath, 16) '¶Ç¦^«ü©w¦s©ñªº¥D¸ê®Æ§¨
  9. If SavePath = "" Then MkDir (ThePath) '¦p¥D¸ê®Æ§¨¤£¦s¦b «Ø¥ß¥¦
  10. For Each Sh In Sheets
  11. d.RemoveAll '¦r¨åª«¥ó ²MªÅ¤lª«¥ó
  12. With Sh '¨Ì§Ç³B¸Ì ¨C¤@¤u§@ªí
  13. For Each R In .Range(.[D2], .[D2].End(xlDown)) '¨C¤@¤u§@ªí¤¤¦bdÄæ
  14. d(R.Value) = "" '¦r¨åª«¥ó ³]¥ß¤lª«¥ó(¼i¬ù»ù)
  15. Next
  16. MonPath = Mid(.[c2], 1, 4) & "_" & Mid(.[c2], 5) '¤ë¸ê®Æ§¨
  17. SavePath = Dir(ThePath & MonPath, 16) '´M§ä¤ë¸ê®Æ§¨
  18. If SavePath = "" Then MkDir (ThePath & MonPath) '¦p¤ë¸ê®Æ§¨¤£¦s¦b «Ø¥ß¥¦
  19. For Each E In Array("¶RÅv", "½æÅv") '¨Ì¿ï¾ÜÅv
  20. ¿ï¾ÜÅv = "\" & MonPath & IIf(E = "¶RÅv", "_C\", "_P\") '¤ë¸ê®Æ§¨\¿ï¾ÜÅv¸ê®Æ§¨
  21. SavePath = Dir(ThePath & MonPath & ¿ï¾ÜÅv, 16)
  22. If SavePath = "" Then MkDir (ThePath & MonPath & ¿ï¾ÜÅv)
  23. For Each R In d.KEYS '¦r¨åª«¥ó ¨Ì§Ç³B¸Ì¤lª«¥ó R (¼i¬ù»ù)
  24. .AutoFilterMode = False '¤u§@ªí¤¤¨ú®ø¦Û°Ê¿z¿ï
  25. .Range("A1").AutoFilter Field:=4, Criteria1:=R
  26. .Range("A1").AutoFilter Field:=5, Criteria1:=E
  27. 'AutoFilter ¤èªk[¦Û°Ê¿z¿ï] ¿z¿ï¥X¤@­Ó²M³æ¡C
  28. 'Field:=4 ²Ä4Äæ (¼i¬ù»ù) ,Criteria1:=R ·Ç«h=R (¼i¬ù»ù)
  29. 'Field:=5 ²Ä5Äæ (¿ï¾ÜÅv) ,Criteria1:=E ·Ç«h=E (¿ï¾ÜÅv)
  30. ¼i¬ù»ù = Mid(.[c2], 1, 4) & "_" & Mid(.[c2], 5) & "_" & R & IIf(E = "¶RÅv", "_C", "_P")
  31. SavePath = ThePath & MonPath & ¿ï¾ÜÅv & ¼i¬ù»ù '¦sÀɪº§¹¾ã¸ô®|¦WºÙ
  32. Set Newbook = Workbooks.Add(1) '·s¶}ÀÉ®×(1­¶)
  33. .UsedRange.SpecialCells(xlCellTypeConstants).Copy Newbook.Sheets(1).[a1]
  34. '¦Û°Ê¿z¿ïªº¸ê®Æ ½Æ»s¨ì·s¶}ÀɮײÄ1­¶ªº.[a1]
  35. With Newbook.Sheets(1)
  36. .[O1] = "°ª»ù´î§C»ù"
  37. .[P1] = "¦¨¥æ¶qÅܤÆ"
  38. With .[O2].Resize(.UsedRange.Columns(1).Rows.Count - 1) '¦b³o½d³ò
  39. .Cells = "=RC[-8]-RC[-7]" 'µM«á¦bO2Äæ¦ì=g2-h2: ¨î­q¤½¦¡
  40. .Value = .Value '¨ú­È -> ®ø°£¤½¦¡
  41. End With
  42. With .[P3].Resize(.UsedRange.Columns(1).Rows.Count - 2)
  43. .Cells = "=RC[-6]-R[-1]C[-6]" '¦bp3®æ¦ì¿é¤J¤½¦¡=j3-j2
  44. .Value = .Value
  45. End With
  46. End With
  47. Newbook.Close True, SavePath '·s¶}ÀÉ®×Ãö³¬ ¦sÀÉ
  48. Next
  49. Next
  50. .AutoFilterMode = False 'Â÷¶}¤u§@ªí«ì´_­ìª¬
  51. End With
  52. Next
  53. Application.DisplayAlerts = True '«ì´_¨t²Îªº´£¥Ü
  54. Application.ScreenUpdating = True '¿Ã¹õ§ó·s¥\¯à¬O¶}±Òªº«h¬° True¡C
  55. MsgBox "¤u§@§¹¦¨"
  56. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE

ÁÂÁÂGBKEE¤j¡A

µo²{§Ú¥u·|²³æªº§PÂ_¦¡¸ò²³æªº°j°é(¦p¦bª©¤Wµoªº"¾P°âºÞ²z½d¨Ò")¡A¸ò¤@¨Çª©¤j¤À¨Éªº¤p§Þ¥©¡AÁÙ¦³¿ý»s¦n¥¨¶°¦A¦^¥h½s¿è¡A¦A²¤Æ¦¨¤@¼h°j°é¡A¤£ª¾¹D¬°¤°»ò¡H¬Ý¨ì¨â¼h¥H¤Wªº°j°é´N·|¶}©l¤£À´¡A¨â¼h¥H¤W²[¼ÆÀ³¥Î¤]¬O¦P¼Ëªº±¡ªp¡C

·Q½Ð±Ð¹³¦r¨å¤¸¥ó¤@¨Ç¥Î¦b¤j¶q¸ê®Æ¿z¿ï¸ò¾ã²zªº¤@¨Ç¶i¶¥µ{¦¡½X¡A§Ú±q¹Ï®ÑÀ]­É¨Óªº®Ñ¨S¦³³o­Ó³¡¥÷¡A·Q½Ð±ÐGBKEE¤j¦³¤°»ò±ÀÂ˪º±Ð¾Ç¸ò®Ñ¶Ü¡H
¦pªG·Q¹³¤j¤jµ{¦¡¤@¼Ë³o»ò±jªº¸Ü¡A¯à¤£¯à½Ð¤j¤j¤À¨É¤@¨Ç¾Ç²ßªº¸gÅç¡C

TOP

¦^´_ 3# yagami12th
¥x»y«Z»y : À¸´×¤U¯¸¤[¬O§Aªº
§Ú¨S¤°»ò¸gÅç,¥\¤O¬O¦b³o¸Ì½m²ßªº (¦h¬Ý¦h°Ý¦h½m²ß)
  1. Sub Ex()
  2.     Dim D As Object, AR(), E As Variant
  3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
  4.     AR = Array("AA", "BB", "CC", "DD")
  5.     For Each E In AR
  6.         D(Mid(E, 1, 1)) = E
  7.     Next
  8.     For Each E In D.KEYS
  9.         MsgBox E
  10.     Next
  11.     MsgBox Join(D.KEYS, ":")
  12.     For Each E In D.ItemS
  13.         MsgBox E
  14.     Next
  15.     MsgBox Join(D.ItemS, ":")
  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# GBKEE
ÁÂÁÂGBKEE¤j ¡A§ä¨ì´X­Ó¼v¤ù±Ð¾Ç¡A¥ý¨Ó½m¬Ý¬Ý¡A¤p§Ì¨SÄéVB¡A­n§ä¤H¬Ý¬Ý¦³¨S¦³¥úºÐ¡C

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD