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

[µo°Ý] ¸ê®Æ½d³ò½Æ»s¨ì¤£¦PÀÉ®×&¨Ì¤é´Á¤À§O½Æ»s

[µo°Ý] ¸ê®Æ½d³ò½Æ»s¨ì¤£¦PÀÉ®×&¨Ì¤é´Á¤À§O½Æ»s

½Ð°Ý¦U¦ì¤j¤j¡G
Àµ¨DÀ°¦£~~~
§Ú¦³¨â­ÓÀɮסG
AÀÉ¡G­t³d°µ¤ä²¼®M¦L
BÀÉ¡G¤ä²¼¥»¦sÀÉ&¤ä²¼ª¬ªp
§Ú¦bAÀɤu§@ªí¡u¤ä²¼®M¦L¡v§¹¦¨«á¡A¥i¥H¦bAÀÉ«ö¤U¤@­Ó«ö¶s¥¨¶°«á¡A¥i¥H°õ¦æ¥H¤U°Ê§@¡G
1.¥ý¬d¸ßBÀɤ¤¡u¤ä²¼¥»¡v¤u§@ªí¸Ì¦³¥»¤ë¨ì´Áªº¤ä²¼(EX:²{¦b¬O106¦~12¤ë¡A´N±N106¦~12¤ëªº¸ê®Æ½Æ»s¨ì¡u¥»¤ë¨ì´Á¡v¤u§@ªí¡B¦¸¤ë¨ì´Áªº¤ä²¼¸ê®Æ(107¦~1¤ë)¡A½Æ»s¨ì¡u¦¸¤ë¨ì´Á¡v¤u§@ªí¡C
2.¦A¨ìAÀɤ¤¡A¡u¤ä²¼®M¦L¡v¤u§@ªí¤º·sªº¤ä²¼¸ê®Æ½Æ»s¨ìBÀɪº¡u¤ä²¼¥»¡v³Ì«á¸ê®Æªº¤U¤@µ§¡AEX:¤ä²¼¸¹½XA0004±µA0005
3.±NBÀÉ¡u¥»¤ë¨ì´Á¡v¤u§@ªí¸ê®Æ¡A½Æ»s¨ì¡u¥Ø«e¤ä²¼ª¬ªp¡v¤u§@ªí¡AA1¦ì¸m
4.±NBÀÉ¡u¦¸¤ë¨ì´Á¡v¤u§@ªí¸ê®Æ¡A½Æ»s¨ì¡u¥Ø«e¤ä²¼ª¬ªp¡v¤u§@ªí¡A±µµÛ¥»¤ë¨ì´Áªº¸ê®Æ¤U¤è¶K¤W
½Ð°Ñ¦ÒªþÀÉ¡AÁÂÁÂ

¤ä²¼.rar (15.72 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-12-27 14:55 ½s¿è

¦^´_ 1# takeshilin88
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Dim WB As Workbook, AR(), D As Object
  3. Sub Main()
  4.     Ex_yymm
  5.     Ex_Copy
  6. End Sub
  7. Private Sub Ex_yymm()
  8.     Dim i As Integer, YM As String
  9.     Set D = CreateObject("SCRIPTING.DICTIONARY") '¦r¨åª«¥ó
  10.     With Workbooks("A.XLSM").Sheets("¤ä²¼®M¦L")
  11.         i = 6
  12.         Do
  13.             YM = Format(.Cells(i, "D"), "ee/mm")
  14.             D(YM) = "=AND(YEAR(¨ì´Á¤é)=" & Format(.Cells(i, "D"), "YYYY") & ",MONTH(¨ì´Á¤é)=" & Format(.Cells(i, "D"), "mm") & ")" 'Format(.Cells(i, "D"), "ee/mm")  'YM : ¦r¨åª«¥óªºkey­È (Ū¨ú¤ë¥÷)
  15.             i = i + 1
  16.         Loop Until .Cells(i, "D") = ""
  17.         i = Application.SheetsInNewWorkbook
  18.         Application.SheetsInNewWorkbook = D.Count + 1
  19.         Set WB = Workbooks.Add
  20.         Application.SheetsInNewWorkbook = 1
  21.         .Copy WB.Sheets(1)
  22.         WB.Sheets(1).Rows("1:4").Delete
  23.         WB.Sheets(1).Name = .Name
  24.     End With
  25.     AR = D.keys
  26. End Sub
  27. Private Sub Ex_Copy()
  28.     Dim Sh As Worksheet, Rng As Range, i As Integer, xRow As Integer
  29.     Set Sh = WB.Sheets(1)
  30.     Set Rng = Sh.Cells(1, Columns.Count).Resize(2)
  31.     Rng.Cells(1) = "AAA"
  32.     For i = 0 To UBound(AR)
  33.         Rng.Cells(2) = D(AR(i))
  34.         Sh.Range("A:D").AdvancedFilter xlFilterCopy, Rng, WB.Sheets(i + 2).[A2]
  35.         'AdvancedFilter    ¶i¶¥¿z¿ï    , ¿z¿ï:½Æ»s ,¿z¿ï·Ç«h,   ½Æ»s¨ìªº¦a¤è
  36.         With WB.Sheets(i + 2)
  37.             .Name = Replace(AR(i), "/", "_") & " ¨ì´Á"
  38.             .[A1] = AR(i) & " ¨ì´Á:"
  39.             .[d1] = Application.Evaluate("sum(" & .[c:c].Address(, , , 1, 1) & ")")
  40.             .[d1].NumberFormatLocal = "#,##0_ "
  41.             xRow = WB.Sheets(WB.Sheets.Count).Cells(Rows.Count, "a").End(xlUp).Row
  42.             If xRow > 1 Then xRow = xRow + 1
  43.             .Range("a1").CurrentRegion.Copy WB.Sheets(WB.Sheets.Count).Cells(xRow, "A")
  44.         End With
  45.     Next
  46.     Rng.Clear
  47.     With WB
  48.         .Sheets(WB.Sheets.Count).Name = "¥Ø«e¤ä²¼ª¬ªp"
  49.         .SaveAs "D:\B.XLSX"    '¦sÀÉ
  50.     End With
  51. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE

GBKEE¤j¤j¡G
´ú¸Õ¹L«á¥i¥H¨Ï¥Î¤F¡A¦ý¬O¦³¨â¶µ»Ý­n¤p¤p­×§ïªº¦a¤è¡G
1.¨C­Ó¤ë¨ì´Áªº¥[Á`ª÷ÃBÀx¦s®æ ( ¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567
2.¦pªG¦b¡u¨ì´Á¤é¡vDÄæ´¡¤J¤@Äæ¡u»¡©úÄæ¡v¡G§Y¡u»¡©úÄæ¡v¦¨¬°DÄæ¡A¡u¨ì´Á¤é¡v¦¨¬°EÄæ¡A
    §Ú±Nµ{¦¡½XªºD³£§ï¬°E¡A·|¥X²{°}¦C¯Á¤Þ¶W¥X½d³ò...
½ÐÀ°À°¦£±Ð§Ú¦p¦ó½Õ¾ãµ{¦¡½X¡A
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ takeshilin88 ©ó 2017-12-12 17:28 ½s¿è

§ó¥¿¡G

GBKEE¤j¤j¡G
´ú¸Õ¹L«á¥i¥H¨Ï¥Î¤F¡A¦ý¬O¦³¨â¶µ»Ý­n¤p¤p­×§ïªº¦a¤è¡G
1.¨C­Ó¤ë¨ì´Áªº¥[Á`ª÷ÃBÀx¦s®æ ( ¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567
2.¦pªG¦b¡u¨ì´Á¤é¡vDÄæ´¡¤J¤@Äæ¡u»¡©úÄæ¡v¡G§Y¡u»¡©úÄæ¡v¦¨¬°DÄæ¡A¡u¨ì´Á¤é¡v¦¨¬°EÄæ¡A
    §Ú±Nµ{¦¡½XªºD³£§ï¬°E¡A·|¥X²{°}¦C¯Á¤Þ¶W¥X½d³ò.......
   ²Ä2¶µ¤w¸g¸Ñ¨M¤F
½ÐÀ°À°¦£±Ð§Ú¦p¦ó½Õ¾ãµ{¦¡½X¡A
ÁÂÁÂ

TOP

¦^´_ 4# takeshilin88

À°¦£³]©w¡G
    ¨C­Ó¤u§@ªíªº¥[Á`ª÷ÃBÀx¦s®æ ( ¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567

½Ð°Ñ¦ÒªþÀÉ¡AÁÂÁÂ

¤ä²¼-¥[Á`Äæ¼Æ¦r®æ¦¡.rar (25.42 KB)

TOP

¦^´_ 5# takeshilin88

¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567
§A¥i¥Î¿ý»s¥¨¶°±o¨ìªºµ{¦¡½X®M¤W
¦Û¦æ¸Õ¸Õ¬Ý
  1. .[d1] = Application.Evaluate("sum(" & .[c:c].Address(, , , 1, 1) & ")")
  2.             .[d1] =????
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# GBKEE

GBKEE¤j¤j¡G
    §Ú¦b
    .[d1] = Application.Evaluate("sum(" & .[c:c].Address(, , , 1, 1) & ")")
    ªº¤U¤@¦æ³]©w
    .[d1] = Selection.NumberFormatLocal = "#,##0_ "   
    µ²ªG¨C­Ó¤u§@ªíªºd1Àx¦s®æ³£Åܦ¨FALSE¤F¡A
    ½Ð°Ý¬O­þ¸Ì¥X¤F°ÝÃD©O¡H

TOP

¦^´_ 7# takeshilin88
Selection §A¥i¬d¬Ývbaªº»¡©ú¬Ý¬Ý
  1. .[d1] .NumberFormatLocal = "#,##0_ "   
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE


    ÁÂÁÂGBKEE¤j¤j¡A
    ¤w¸g¥i¥H¨Ï¥Î¤F¡AÁÂÁÂ~~
    ¸U¤À·P¿E......

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2017/12/21 15:00): 2#ªºµ{¦¡½X¤w§ó·s

¥»©«³Ì«á¥Ñ takeshilin88 ©ó 2017-12-20 17:00 ½s¿è

¨D±Ï~~
¦pªG¡u¨ì´Á¤é¡vªºÀx¦s®æ®æ¦¡§ï¦¨¬°¤¤µØ¥Á°ê¾ä¡A
«h²£¥ÍªºBÀÉ·|§ì¤£¨ì¨ì´Á¤é¥[Á`ªº¸ê®Æ¡A
¦n¹³¬O¤U­±³o­Óµ{¦¡½X»Ý­n­×§ï¡G
YM = Mid(.Cells(i, "D"), 1, InStrRev(.Cells(i, "D"), "/") - 1)
½Ð¦U¦ì¤j¤jÀ°À°¦£~~

ªþÀÉ
1061220-§ï¤é´Á®æ¦¡.rar (27.22 KB)

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD