- ©«¤l
- 36
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 77
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN7
- ³nÅ骩¥»
- MICROSOFT
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2015-6-7
- ³Ì«áµn¿ý
- 2024-9-22
|
¦^´_ 4# ¤d·u´M
Sub ¨ú¥XN03µ¥¶i¥X³f¤Îºâ¯d¦s()
Dim N, B, i%, C%, R%, Cn%, Arr, Brr, Crr
Set d = CreateObject("scripting.dictionary")
N = Array(0, 4, 5, 7, 19, 20, 24) '¨ú±o¸ê®ÆªºÄ渹¡A¦]°}¦C±q0¶}©l¡A¸É¤@Ó"0"
Arr = Range([¾ú¥v±ÄÁÊ!X4], [¾ú¥v±ÄÁÊ!D4].End(4))
Brr = Range([¨ú¥X¸ê®Æ!B2], [¨ú¥X¸ê®Æ!A1].End(4))
For i = 1 To UBound(Arr)
d(Arr(i, 1) & "") = d(Arr(i, 1) & "") & "," & i '¥Î¦r¨å°O¿ý³v¤@¨ú¥X²{¦C¸¹
Next i
Sheets("¨ú¥X¸ê®Æ").Activate
Ri = 2 'Ri= ¶K¤W¸ê®Æªº°_©l¦C¸¹.¥B¥H«á·|©T©w¸õ7¦C (6¦C¬°¸ê®Æ°Ï,²Ä7¦C¬°ªÅ¥Õ¦C)
Columns("E:I").Clear '²MªÅ¸ê®Æ&®æ¦¡
For i = 1 To UBound(Brr) '¨Ì§Ç¨Ì½s¸¹(N03,¡K)¥Î¨Ó²£»sCrr°}¦C(¥ÑArr¹ïÀ³Äæ/¦C¨ú¸ê®Æ)
B = Split(d(Brr(i, 1) & ""), ",") ¡¥ ¤À³Î¨ú±o¸Ó½s¸¹¤Î¨ä¥X²{ªº¦C¸¹ ---µù1
Cn = UBound(B) ¡¥§Y¦P¤@½s¸¹¥X²{ªº³Ì°ª¦¸¼Æ½áȵ¹Cn ,¥i¯à¬°1©Î2©Î¡K
Crr = Cells(Ri, 5).Resize(6, Cn) ¡¥©T©w¼g¤J6¦C,Äæ¼Æ«hµø¥X²{¦¸¼Æ¨Ó¨M©w
For C = 1 To Cn ¡¥Cn =1®Éªí¥Ü¸Ó½s¸¹¥u¥X²{1¦¸ Cn·|¨M©wCrr°Ï°ì¤j¤p¤Î¥~°j°é¦¸¼Æ
For R = 1 To 6 ¡¥C¥un¨C´`Àô¤@¦¸,Rn¶]6¦¸(§Y³v¤@¨ú6Ó¤£¦PÄæ¦ì¼g¦b1~6¦C)
Crr(R, C) = Arr(B(C), N(R) - 3) ¡¥³v¤@±qArr¨ú¥X¦U¹ïÀ³Ä椧¸ê®Æ¼g¤JCrr
If R = 3 Then Crr(R, C) = "¦³:" & Crr(R, C)
If R = 4 Then Crr(R, C) = "¥X:" & Crr(R, C) & "½c"
If R = 5 Then Crr(R, C) = "¯d:" & Crr(R, C) & "½c"
Next R
Next C
With Cells(Ri, 5).Resize(6, Cn) '§âCrr¸ê®Æ©ñ¨ì¤u§@ªí+³]©wÀx¦s®æ®æ¦¡
.Value = Crr
.Borders.LineStyle = xlContinuous
End With
Ri = Ri + 7
Next i
Columns("E:I").AutoFit '¦Û°ÊÄæ¼e
End Sub
µù1:¥HN03¬°¨Ò,¥X²{ªº¦C¸¹¤À§O¬°1,7,12,18,23¦@5¦¸,«hd(Arr(i, 1) & "") = d(Arr(i, 1) & "") & "," & i¶]§¹ªºµ²ªG¬°:N03, 1,7,12,18,23
¦A¸gB = Split(d(Brr(i, 1) & ""), ",") ¤À³Îªºµ²ªG:B(0)=¡¨N03¡¨, B(1)= 1,B(2)=7, B(3)=12¡K
B(1), B(2)..§Y¬°¸Ó½s¸¹©Ò¦b¦C¸¹,¥BBªº¤¸¯ÀӼƷ|ÀH¦UÓ½s¸¹¦bArr¥X²{¦¸¼Æ¦ÓÅÜ
Crr(R, C) = Arr(B(C), N(R) - 3) ¡¥©T©w¼g6¦C,¦ý¼g¦h¤ÖÄæ«hµø¥~°j°é¶]ªº¦¸¼Æ¦Ó©w.
N = Array(0, 4, 5, 7, 19, 20, 24) '¨ú±o¸ê®ÆªºÄ渹,¦ý¸ê®Æ¤£¬O±qA1¼g¤J,¦Ó¬O±qD4¼g¤J,©Ò¥Hn´î¥h3 ¬GN(R) ¡V 3 ·|³v¤@¨ú±o1,2,3,16,17,21 §Y¬°Arr°Ï°ìn¨ú¸ê®Æªº¹ïÀ³Äæ¦ì
·PÁªüÀs¤j¤jªºµ{¦¡½X,«Ü¤Î®É¥Bºë·Çªº¸Ñ¨M¤F§Úªº»Ý¨D,¥H¤W¬O§Ú°Ñ¬ã¤F¤@±ßªº¤ß±o,Åý§Ú¹ï°}¦C©M¦r¨å¤S¦³§ó²`¤@¼hªº¤F¸Ñ©M´x´¤,±N¦Û¤v²z¸Ñªº³¡¥÷¼g¦b¤W±¤À¨É,¨ä¤¤¦p¦³²z¸Ñ¤£¨¬©ÎÂÕ»~ªº¦a¤è,½Ð¦U¦ì¤j®v¤£§[½ç±Ð. |
|