s3526369

¦^´_ 34# samwang


    ·PÁ«e½ú ~´ú¸Õ¥i¥H!

s3526369

¦^´_ 36# Andy2483
ÁÂÁ§A ¦³­×§ï¤F
¦^´_ 40# Andy2483
«e½ú~¤]¤Ó¼F®`¤F!«Ü¦hºØ¤èªk,¦³ÂI¬Ý¤£¤ÓÀ´,¦ýºCºC¬ã¨sÀ³¸Ó¥i¥H²z¸Ñ!

Andy2483

¦^´_ 40# Andy2483
¤µ¤Ñ¦^ÅU¦¹©«§â¦¹©«ªº¤ß±oµù¸Ñ¤@¤U
·íªì¬O¶Ã¸Õ¦¨¥\·|¶]ªº! ¯uªº¬Oé¤Wªº!
½Ð¦U¦ì«e½ú«ü¥¿¨Ã«ü¾É!

Option Explicit
Sub A»Ý¨D_20220919()
Application.ScreenUpdating = False
Dim x&, i&, QA, QB, T, S, Srr, Arr, Ac, xR, C
Dim Trr, Brr, Crr, Rs, Rq1s, Rq1n, Ras, Ran, B, ¯Srr, Drr
Dim Rq2s, Rq2n, XA
'¡ô«Å§iÅܼÆ
T = Timer
Set Srr = CreateObject("Scripting.Dictionary")
Set Trr = CreateObject("Scripting.Dictionary")
Set ¯Srr = CreateObject("Scripting.Dictionary")
'¡ô¥OSrr,Trr,¯Srr¬O¦r¨å
S = Split("A»Ý¨D,¤J®w©ú²Ó,¥X®w©ú²Ó,¥þ¾÷ºØBOM,«ü¹Ï©ú²Ó,¤½¥q½LÂI,¤½¥q½LÂI,¤½¥q½LÂI", ",")
'¡ô¥OS¬O¤@ºû°}¦C!¸Ë¤J ¤u§@ªí¦W¦r¦ê¥Î "," ²Å¸¹©î¸Ñ¦¨8­Ó¦r¦ê,±q0~7
For i = 1 To UBound(S)
'¡ô³]¶¶°j°é³]©w«á7­Ó¦r¦ê¬O¤À§O¬O¤T­Ó¦r¨åªºKEY
   Set Srr(i) = Sheets(S(i)).Cells
   '¡ôSrrªºItem¬O7­Ó¤u§@ªí
   Set Trr(i) = CreateObject("Scripting.Dictionary")
   '¡ôTrrªºItem¬O7­Ó·s¦r¨å
   Set ¯Srr(i) = CreateObject("Scripting.Dictionary")
   '¡ô¯SrrªºItem¬O7­Ó·s¦r¨å
Next
Rs = Rows.Count
'¡ô¥ORs¬O³oªíªº·¥­­¦C¼Æ 1048576
Ac = Sheets(S(0)).Cells(Rs, 1).End(3).Row
'¡ô¥OAc¬O "A»Ý¨D"ªíªºAÄæ³Ì«á¤@­Ó¦³¤º®e®æ
Arr = Range(Sheets(S(0)).[H4], Sheets(S(0)).Cells(Ac, 1))
'¡ô¥OArr¬O°}¦C¸Ë¤J Ac »P "A»Ý¨D"ªíªº[H4] ,
'³o¨â­Ó¹ï¨¤®æ²[»\ªº¤è¥¿³Ì¤p°Ï°ìÀx¦s®æ­È
¯Srr(1) = Array("", 1, 18, 1, 15, 0, 1, 1, 19, "A­Ü") '¤J®w¦X­p
'¡ô±N°}¦C­È·íITEM,KEY¬O0~9 ­Ë¤J ¯Srr(1)³o¦r¨å¤¤ªº¦r¨å
'¡ô¦r¨å¤¤ªº¦r¨åKEY 0 ªºITEM ¬O"" ªÅ¦r¤¸,¬O«á­±µ{§Ç¨S¦³¥Î¨ìªº
'¯Âºé¬O­nÅý«á­±µ{§Ç±qkey 1 ¶}©l¤Þ¥Î
'¡ô¦r¨å¤¤ªº¦r¨åKEY 1 ,KEY 2 ITEM(1, 18)
',¬O¥Î¨Ó«ü¤Þ²Ä1­Óªí "¤J®w©ú²Ó" ªí­n¨úRÄæ¸ê®Æ
'¡ô¦r¨å¤¤ªº¦r¨åKEY 3 ,KEY 4 ITEM(1, 15)
',¬O¥Î¨Ó«ü¤Þ²Ä1­Óªí "¤J®w©ú²Ó" ªí­n¨úOÄæ¸ê®Æ
'¡ô¦r¨å¤¤ªº¦r¨åKEY 5 ,KEY 6 ITEM(0, 1)
',¬O³Æ¥Îªº!¦pªG¼Ó¥Dªº»Ý¨D¦bµ²ªGªíÁÙ­n¼W¥[±ø¥ó¥Îªº
'¡ô¦r¨å¤¤ªº¦r¨åKEY 7 ,KEY 8 ITEM(1, 19)
',¬O¥Î¨Ó«ü¤Þ²Ä1­Óªí "¤J®w©ú²Ó" ªí­n¨úSÄæ¸ê®Æ
'¡ô¦r¨å¤¤ªº¦r¨åKEY 9 ITEM¬O "A­Ü" (²Ä¤G­Ó§PÂ_±ø¥óÃöÁä¦r)

'¡õ«áÄò¨Ì¤W­zÃþ±À, ¸Ì­±ªº 99 ¬OCUÄ檺·N«ä
¯Srr(2) = Array("", 2, 18, 2, 15, 0, 1, 2, 19, "A­Ü") '¥X®w¦X­p
¯Srr(3) = Array("", 3, 26, 3, 16, 0, 1, 3, 20, "A­Ü") '¥þ¾÷ºØBOM-Á`»Ý¨D
¯Srr(4) = Array("", 4, 12, 4, 6, 0, 1, 4, 10, "A­Ü")  '«ü¹Ï©ú²Ó-Á`¥X³f
¯Srr(5) = Array("", 5, 6, 5, 1, 0, 1, 5, 99, "") '¤½¥q½LÂI-A­Ü
¯Srr(6) = Array("", 6, 11, 6, 1, 0, 1, 6, 99, "")  '¤½¥q½LÂI-A­Ü½Õ¾ã
¯Srr(7) = Array("", 7, 7, 7, 1, 0, 1, 7, 99, "")  '½LÂIªí

For i = 1 To UBound(S)
'¡ô³]¥~¶¶°j°é±q 1 ¨ì S°}¦Cªº³Ì«á¤@­Ó 7
   Set Rq1s = Srr(¯Srr(i)(3))(1, ¯Srr(i)(4))
   Set Rq1n = Srr(¯Srr(i)(3))(Rs, ¯Srr(i)(4)).End(3)
   Brr = Srr(¯Srr(i)(3)).Range(Rq1s, Rq1n)
   '¡ô¥OBrr¬O°}¦C ±N±ø¥ó1ªºÀx¦s®æ­È¸ê®Æ­Ë¤J,·í³Q·j´MªºÃöÁä¦r
   
   Set Rq2s = Srr(¯Srr(i)(7))(1, ¯Srr(i)(8))
   Set Rq2n = Srr(¯Srr(i)(7))(Rq1n.Row, ¯Srr(i)(8))
   Drr = Srr(¯Srr(i)(7)).Range(Rq2s, Rq2n)
   '¡ô¥ODrr¬O°}¦C ±N±ø¥ó2ªºÀx¦s®æ­È¸ê®Æ­Ë¤J,·í³Q·j´MªºÃöÁä¦r

   Set Ras = Srr(¯Srr(i)(1))(1, ¯Srr(i)(2))
   Set Ran = Srr(¯Srr(i)(1))(Rq1n.Row, ¯Srr(i)(2))
   Crr = Srr(¯Srr(i)(1)).Range(Ras, Ran)
   '¡ô¥OCrr¬O°}¦C µ²ªGÀx¦s®æ­È¸ê®Æ­Ë¤J
   For x = 1 To UBound(Brr)
   '¡ô³]¤º¶¶°j°é±q 1 ¨ì ²Ä1±ø¥óªº³Ì«á­Ó
      B = Brr(x, 1)
      '¡ô³f«~½s¸¹
      If InStr(Drr(x, 1), ¯Srr(i)(9)) Or Drr(x, 1) & ¯Srr(i)(9) = "" Then
      '¡ô¦pªG²Ä¤G±ø¥ó¦¨¥ß ©Î
      '²Ä¤G±ø¥óªºÃöÁä¦rÄæ®æ­È»P ¯Srr(i)²Ä9­ÓITEM ²Õ¦Xªº¦r¦ê¬OªÅ¦r¤¸
      
      '¦]¬° ¦pªG¨S¦³²Ä¤G±ø¥ó§PÂ_ªº¤u§@ªí¸ê®Æ!¤]­n³Ð¥ß¦r¨å¨Ñ«áÄò¤Þ¥Î
      ''¦¹½d¨ÒCUÄæ¤@©w¬OªÅ®æ,»P¯Srr(i)(9) = ""²Õ¦X¦r¦ê¤]¬OªÅ®æ!
      '©Ò¥H²Ä¤G±ø¥ó¤@©w·|¦¨¥ß!
      '¦]¬°²Ä¤@±ø¥ó´N¬O ³f«~½s¸¹ ¬O¦r¨å¤@©w·|¯Ç¤J
         Trr(i)(B) = Trr(i)(B) + Crr(x, 1)
         '¡ô±ø¥ó¦¨¥ß´N§â ³f«~½s¸¹·íkey¥h°£­«½Æ,µ²ªGÀx¦s®æ­È²Ö¥[·íitem
      End If
   Next
Next
For i = 1 To Ac - 3
'¡ô³]¶¶°j°é±N¸ê®Æ±a¤J©Î­pºâ«á¦A±a¤J!
   xR = Arr(i, 1)
   Arr(i, 4) = Trr(7)(xR)
   Arr(i, 5) = Trr(3)(xR)
   Arr(i, 6) = Trr(1)(xR) + Trr(2)(xR)
   Arr(i, 8) = Trr(5)(xR) + Trr(6)(xR)
   If Trr(3)(xR) = 0 Then Arr(i, 5) = 0
   If Trr(7)(xR) = 0 Then Arr(i, 4) = 0
   If Trr(1)(xR) + Trr(2)(xR) = 0 Then Arr(i, 6) = 0
   If Trr(5)(xR) + Trr(6)(xR) = 0 Then Arr(i, 8) = 0
   Arr(i, 7) = Trr(5)(xR) + Trr(6)(xR) + Trr(1)(xR) + Trr(2)(xR) - Trr(3)(xR)
   If Arr(i, 7) >= 0 Then Arr(i, 7) = 0
Next i
Sheets(S(0)).[A4].Resize(UBound(Arr), 8) = Arr
MsgBox "¦@¯Ó®É¡G" & Timer - T & " ¬í"
End Sub

singo1232001

Sub ­Ü®w®w¦s()
Set d = CreateObject("Scripting.Dictionary")
r = Sheets("§÷®Æªí").Cells(Rows.Count, 1).End(3).Row
For Each Z In Sheets("§÷®Æªí").Range("a2:a" & r)
d(Z.Value) = Z.Row - 1
Next
            '1            '2     '3      8      7    9     10     11
sA = Split("¥þ¾÷ºØBOM,¤½¥q½LÂI,¤J®w©ú²Ó,A»Ý¨D,B»Ý¨D,°h®w,¼o®Æ­Ü,«ü¹Ï©ú²Ó", ",")
sB = Split("p:z,a:g,o:r,a:h,a:h,a:c,a:c,f:l", ",")
ReDim Ar(1 To d.Count, 1 To 11) As Double

For i = 0 To UBound(sA) '©ñ¸ê®Æ
Set s = Sheets(sA(i))
sC = Split(sB(i), ":")
r = s.Cells(Rows.Count, sC(0)).End(3).Row
    c = Split("1,2,3,8,7,9,10,11", ",")(i)
    For j = 1 To r
     If d.exists(s.Cells(j, sC(0)).Value) Then
        Ar(d(s.Cells(j, sC(0)).Value), c) = Ar(d(s.Cells(j, sC(0)).Value), c) + s.Cells(j, sC(1)).Value
     End If
    Next
Next

For i = 1 To UBound(Ar)  '­pºâ¤@¤U
    a23910 = Ar(i, 3) + Ar(i, 2) - Ar(i, 9) - Ar(i, 10)
    Ar(i, 6) = a23910 - Ar(i, 11) - Ar(i, 8) - Ar(i, 7)
    Ar(i, 5) = a23910 - Ar(i, 11)
    Ar(i, 4) = a23910 - Ar(i, 1)
    If Ar(i, 4) >= 0 Then Ar(i, 4) = 0
Next
Sheets("­Ü®w®w¦s").Range("c4").Resize(UBound(Ar) - 1, 11) = Ar
End Sub

singo1232001

¦^´_ 44# singo1232001

'¦³¿ù ­×¥¿¤@¤U

    Sub ­Ü®w®w¦s2()
Set d = CreateObject("Scripting.Dictionary")
Set s = Sheets("§÷®Æªí")
For Each Z In s.Range("a2:a" & s.Cells(Rows.Count, 1).End(3).Row)
d(Z.Value) = Z.Row - 1: Next
            '1            '2     '3      8      7    9     10     11
sA = Split("¥þ¾÷ºØBOM,¤½¥q½LÂI,¤J®w©ú²Ó,A»Ý¨D,B»Ý¨D,°h®w,¼o®Æ­Ü,«ü¹Ï©ú²Ó", ",")
sB = Split("p:z,a:g,o:r,a:h,a:h,a:c,a:c,f:l", ",")
ReDim Ar(1 To d.Count, 1 To 11) As Double

For i = 0 To UBound(sA) '©ñ¸ê®Æ
Set s = Sheets(sA(i))
sC = Split(sB(i), ":")
Lr = s.Cells(Rows.Count, sC(0)).End(3).Row
    c = Split("1,2,3,8,7,9,10,11", ",")(i)
    For j = 1 To Lr
     r = s.Cells(j, sC(0)).Value
      If d.exists(r) Then Ar(d(r), c) = Ar(d(r), c) + s.Cells(j, sC(1)).Value
    Next
Next

For i = 1 To UBound(Ar)  '­pºâ¤@¤U
    a23910 = Ar(i, 3) + Ar(i, 2) - Ar(i, 9) - Ar(i, 10)
    Ar(i, 4) = a23910 - Ar(i, 1): If Ar(i, 4) >= 0 Then Ar(i, 4) = 0
    Ar(i, 5) = a23910 - Ar(i, 11)
    Ar(i, 6) = Ar(i, 5) - Ar(i, 8) - Ar(i, 7)
Next
Sheets("­Ü®w®w¦s").Range("c4").Resize(UBound(Ar) - 1, 11) = Ar
End Sub

Andy2483

¦^´_ 45# singo1232001


    ÁÂÁ«e½ú«ü¾É
¥H¤U¬O¤µ¤Ñ¾Ç²ß¤ß±oµù¸Ñ!¦p¦³«_¥Ç½Ð¨£½Ì!
½Ð«e½ú«ü¥¿¨Ã«ü¾É!ÁÂÁÂ!
Option Explicit
Sub ­Ü®w®w¦s2()
Dim d, S, sA, sB, Ar, Z, sC, Lr, j, i, C, r, a23910
Set d = CreateObject("Scripting.Dictionary")
'¡ô¥Od¬O¦r¨å
Set S = Sheets("§÷®Æªí")
'¡ô¥Od¬Oª«¥ó "§÷®Æªí" ¤u§@ªí!¥H¤UºÙ §÷®Æªí
For Each Z In S.Range("a2:a" & S.Cells(Rows.Count, 1).End(3).Row)
'¡ô#³]¶¶°j°é¥OZ¬O §÷®Æªí [A2]¨ìAÄ檺³Ì«á¤@®æ¤¤ªº¤@®æ,©Ò¥HZ¬Oª«¥óÀx¦s®æ
   d(Z.Value) = Z.Row - 1   '@
   '¡ô§â¤W­z#Àx¦s®æ­È·íkey­Ë¤Jd¦r¨å¸Ì,item¬OZ©Ò¦bªº¦C¦ì¼Æ-1
Next
sA = Split("¥þ¾÷ºØBOM,¤½¥q½LÂI,¤J®w©ú²Ó,A»Ý¨D,B»Ý¨D,°h®w,¼o®Æ­Ü,«ü¹Ï©ú²Ó", ",")
'¡ô¥O sA¬O¤@ºû°}¦C,­Ë¤J¥Î "," ¤À³Î¤u§@ªí¦r¦ê²Õ,¦¨¬°8­Ó¦r¦ê ±q0~7
sB = Split("p:z,a:g,o:r,a:h,a:h,a:c,a:c,f:l", ",")
'¡ô¥O sB¬O¤@ºû°}¦C,­Ë¤J¥Î "," ¤À³ÎÀx¦s®æÄæ¦ì ÃöÁä¦rÄæ:·j´Mµ²ªGÄæ
ReDim Ar(1 To d.Count, 1 To 11) As Double
'¡ô«Å§i Ar¬O¼Æ¦r°}¦C,Áa¦V±q1 ¨ìd¦r¨å¸Ì¤¸¯À¼Æ¦C,¾î¦V±q1 ¨ì11Äæ
For i = 0 To UBound(sA) '©ñ¸ê®Æ
'¡ô³]¥~¶¶°j°é,±q0¶}©l¨ì sA¤@ºû°}¦Cªº³Ì«á¤@­Ó¼Æ 7
   Set S = Sheets(sA(i))
   '¡ô¥OS¬O ª«¥ó °j°é¸Ìªº¤u§@ªí ¥H¤UºÙ(°j°éªí)
   sC = Split(sB(i), ":")
   '¡ô¥O sC¬O¤@ºû°}¦C ­Ë¤J¥Î ":" ¤À³ÎsB¤@ºû°}¦C¸Ìªº°j°é«ü©w¦r¦ê
   Lr = S.Cells(Rows.Count, sC(0)).End(3).Row
   '¡ô¥O Lr¬O°j°éªí¸Ì«ü©wªº §÷®Æ®Æ¸¹Äæ ¦³¤º®eªº³Ì«á¦C¼Æ
   C = Split("1,2,3,8,7,9,10,11", ",")(i)
   '¡ô¥O C¬O¤@ºû°}¦C ­Ë¤J¥Î "," ¤À³Îµ²ªGªíÄæ¦ì¦r¦ê
   For j = 1 To Lr
   '¡ô³]¤º¶¶°j°é ±q1 ¨ì °j°éªí¸Ì«ü©wªº §÷®Æ®Æ¸¹Äæ ¦³¤º®eªº³Ì«á¦C¼Æ
      r = S.Cells(j, sC(0)).Value
     '¡ô¥Or¬O °j°éªí¸Ì §÷®Æ®Æ¸¹Ä椺°j°éÀx¦s®æªº­È,¥H¤UºÙ(ÃöÁä¦r)
      If d.exists(r) Then
      '¡ô¦pªG ÃöÁä¦r¦b¦r¨å¸Ì¬d±o¨ì
         Ar(d(r), C) = Ar(d(r), C) + S.Cells(j, sC(1)).Value
         '¡ôAr°}¦C¦ì§}: @¼Ð¥Ü³B¦r¨åd,key¬°ÃöÁä¦r,ªºItem¦C¦ì,µ²ªGªíÄæ¦ì
         'Åý°}¦C¤¤ªºµ²ªG­È²Ö¥[·j´MÃöÁä¦r±o¨ìªºµ²ªGÄæ¼Æ¶q­È
      End If
   Next
Next
For i = 1 To UBound(Ar)  '­pºâ¤@¤U
    a23910 = Ar(i, 3) + Ar(i, 2) - Ar(i, 9) - Ar(i, 10)
    Ar(i, 4) = a23910 - Ar(i, 1): If Ar(i, 4) >= 0 Then Ar(i, 4) = 0
    Ar(i, 5) = a23910 - Ar(i, 11)
    Ar(i, 6) = Ar(i, 5) - Ar(i, 8) - Ar(i, 7)
Next
Sheets("­Ü®w®w¦s").Range("c4").Resize(UBound(Ar), 11) = Ar
'¡ô±Nµ²ªG°}¦C­È±q"­Ü®w®w¦s"ªí[C4]¶K¤J!
'¥Î§÷®ÆªíªºÃöÁä¦r§ä¸ê®Æ!¶K¨ì"­Ü®w®w¦s"ªí!­·ÀI­å¤j!
End Sub