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

EXCELªí³æ¦Û°Ê¥Í¦¨ªí³æ©ÎªÌWORDÀÉ®×

EXCELªí³æ¦Û°Ê¥Í¦¨ªí³æ©ÎªÌWORDÀÉ®×

±qNÄæ¿z¿ï³æ¦ì¡A»s§@·í¤ëªº³øªí¦p¤U­±wordÀɮסA¥Ñ¤U¹Ï1 NÄæ¶i¦æ¿z¿ï¡Aµ²ªG¦p¹Ï2¡AµM«á»s§@¦¨¹Ï3¡C
¥Ø«e§Ú¬O¥Î¿z¿ïªº¬Ý¦³´X­Ó³æ¦ì¨C­Ó³æ¦ì»s§@¦¨¤@±iªí³æ¡A¦³¨S¦³¿ìªk¥ÎVBA¦Û°Ê¥Í¦¨¦U³æ¦ìªºªí³æ¡C





´ú¸Õ (2).zip (46.88 KB)
m06o2~~~~

¦^´_ 1# m06o2


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
´ú¸Õ_20230921_1.zip (64.17 KB)

¸ê®Æªí:


µ²ªGªí:



Option Explicit
Sub TEST()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Arr(1 To 16, 1 To 12), Brr, Crr, V, D, E, Z, Q, i&, j%, R&
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([Á`ªí!A4], [Á`ªí!O65536].End(3))
For i = 1 To UBound(Brr)
   If Trim(Brr(i, 7)) <> "" And Brr(i, 15) = "" Then MsgBox "¸ê®Æ¤£§¹¾ã": Exit Sub
   If Val(Brr(i, 15)) > 0 Then
      For j = 1 To 9
         If Trim(Brr(i, j)) = "" Then MsgBox "¸ê®Æ¤£§¹¾ã": Exit Sub
      Next
   End If
Next
D = Array(1, 2, 3, 7, 11, 12)
E = Split("2,4,6,8,9,15", ",")
For i = 1 To UBound(Brr)
   V = Z(Brr(i, 7))
   If Not IsArray(V) Then V = Arr
   R = Z(Brr(i, 7) & "|R") + 1: Z(Brr(i, 7) & "|R") = R
   For j = 0 To UBound(D): V(R, D(j)) = Brr(i, E(j)): Next
   Z(Brr(i, 7)) = V
Next
Crr = Range([»²§Uªí!C1], [»²§Uªí!A65536].End(3))
For i = 1 To UBound(Crr): Z(Crr(i, 1) & "|") = i: Next
For i = Sheets.Count To 1 Step -1
   If InStr(Sheets(i).Name, ".") Then Sheets(i).Delete
Next
For Each Q In Z.KEYS
   If Not IsArray(Z(Q)) Then GoTo Q01
   With Sheets("¦C¦L").Copy(after:=Worksheets(Sheets.Count))
      R = Z(Q & "|R"): ActiveSheet.Name = Q & "."
      [B3] = Q: [B4] = Crr(Z(Q & "|"), 2): [B5] = Crr(Z(Q & "|"), 3)
      With [A9].Resize(R, 12)
         .Value = Z(Q): .Borders.LineStyle = 1
         .Item(.Count + 11) = "Á`­p:": .Item(.Count + 11).Font.Bold = True
         .Item(.Count + 12) = "=SUM(L9:L" & 8 + R & ")"
         .Item(.Count + 12).Font.Bold = True
         With Range(.Item(.Count + 25), [L27])
            .Merge: .Value = "³Æµù:"
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .Borders.LineStyle = 1
         End With
      End With
   End With
Q01: Next
Set Z = Nothing: Erase Arr, Brr, Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483


    ¥¿¦b¬ã¨s«e½úªºµ{¦¡½X¤¤!!
m06o2~~~~

TOP

¦^´_ 2# Andy2483


   ­è­è´ú¸Õ§â³æ¦ì¦WºÙ§ï¦¨DDD µo²{µ{¦¡´N·|¥X²{¿ù»~

[B3] = Q: [B4] = Crr(Z(Q & "|"), 2): [B5] = Crr(Z(Q & "|"), 3)
m06o2~~~~

TOP

¦^´_ 4# m06o2
»²§Uªí»Ý­n¥ý«ØDDDªº°ò¥»¸ê®Æ
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 5# Andy2483


   À´¤F!!§Ú¥ß¨è¥h¸Õ¸Õ¬Ý
m06o2~~~~

TOP

¦^´_ 6# m06o2


    ¥Ø«e·Pı¥L¬O·j´MÁ`ªíªºGÄæ¶i¦æ±Æ¦C¡A§Ú·Q­n¥L¥ÎNÄæ¶i¦æ±Æ¦C¡A¦ý§ä¤£¨ì¦a¤è¥i¥H§ï
m06o2~~~~

TOP

¦^´_ 7# m06o2


    GÄæ»P NÄæ¸ê®Æ¬O¬Û¦Pªº
¤°»ò­ì¦]·|¥X²{¤£¤@¼Ë¡H
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 8# Andy2483


    ­n·j´MNÄæ¤~¥i¥H·íªì§Ú¬O·Q»¡Â²³æ¤ÆÅý¥L¤@¼Ë~¦ý¬O¥¿±`¬O¥ÎNÄæ°µªí³æ³q±`³£¤£¤@¼Ë
m06o2~~~~

TOP

¦^´_ 9# m06o2
§Ú§ä¨ì¤F~·PÁÂ!


      V = Z(Brr(i, 14))
   If Not IsArray(V) Then V = Arr
   R = Z(Brr(i, 14) & "|R") + 1: Z(Brr(i, 14) & "|R") = R
   For j = 0 To UBound(D): V(R, D(j)) = Brr(i, E(j)): Next
   Z(Brr(i, 14)) = V
m06o2~~~~

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD