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

¸óÄæ¹ï¤ñ

¸óÄæ¹ï¤ñ


¦³¤T­Óªí®æ¡A¦bªí®æ¡§1¡¨°õ¦æ¡A
ªí®æ¡§1¡¨ ÄæB ©MÄæG ¨â¼Ë¤À§O¦bªí®æ¡§KP" & "KH" §ä´M¡A¦pªG§ä¨ì¬Û¦Pªº¼Æ¾Ú¡C

Á|¨Ò¡G
96089 & 5580 ²Õ¦X¦bKP ªíùاä¨ì
¡§1¡¨ªíªºÄæM (Project) ¶Ç¦^"KP"ªíªº "$A$1"
¡§1¡¨ªíªºÄæO (KP) ¶Ç¦^"KP"ªíªº "$C$1"

96183 & 4780 ²Õ¦X¦b KP & KH ªíùس£§ä¨ì
"1¡¨ªíªºÄæM (Project) ¶Ç¦^"KP"ªíªº "$H$1" & "KH" ªíªº¡§$H$1", ¨âªÌ³£¬O"BH" ¬Û¦P¡A©Ò¥HÅã¥Ü"BH"
"1¡¨ªíªºÄæN (KH) ¶Ç¦^"KH"ªíªº "$J$1"
"1¡¨ªíªºÄæO (KP) ¶Ç¦^"KP"ªíªº "$J$1"

94851 & 5370 ²Õ¦X¦b KH ªíùس£§ä¨ì
"1¡¨ªíªºÄæM (Project) ¶Ç¦^"KH"ªíªº "$A$1" &  "$H$1", ¨âªÌ¤£ ¬Û¦P¡A©Ò¥HÅã¥Ü"BF/BH"
"1¡¨ªíªºÄæN (KH) ¶Ç¦^"KH"ªíªº "$C$1" &  "$J$1", ¨âªÌ³£¬O¡§¼Qªo¡¨¬Û¦P¡A©Ò¥HÅã¥Ü¡§¼Qªo¡¨

96093 & 5260 ²Õ¦X¦b KH & KPªíùس£§ä¨ì
"1¡¨ªíªºÄæM (Project) ¶Ç¦^"KP"ªíªº"$A$1" & "$H$1" & "KH" ªíªº"$A$1" & "$H$1", ¥|³£¬O"BF/ BH/ BF/ BH" ¬Û¦P¡A¦]爲­«½Æ¡A©Ò¥HÅã¥Ü"BF/ BH"
"1¡¨ªíªºÄæN (KH) ¶Ç¦^"KH"ªíªº "$C$1" &  "$J$1", ¨âªÌ³£¬O¡§¼Qªo¡¨¬Û¦P¡A©Ò¥HÅã¥Ü¡§¼Qªo¡¨
"1¡¨ªíªºÄæO (KP) ¶Ç¦^"KP"ªíªº  "$C$1" &  "$J$1", ¨âªÌ³£¬O¡§¼Qªo¡¨¬Û¦P¡A©Ò¥HÅã¥Ü¡§¼Qªo¡¨

¹ï¤ñ.rar (15.59 KB)

¦^´_ 1# 198188


    ¹Ï¤ù¬O°õ¦æ«e¤Î°õ¦æ¦Z®ÄªG¡A¥H¤Î¨â­Ó¸ê®Æ®w KP & KH¡C

TOP

¦^´_ 2# 198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
½Ð°Ý[M5]¸Ìªº BF/BH/BJ  ,³o BJ±q¦ó¨Ó?
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 3# Andy2483


    KH ¸ê®Æªíªº H1 Àx¦s®æ

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-3-4 16:03 ½s¿è

¦^´_ 1# 198188


Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, i&, T$, TT$, A, xA As Range, N%
Set Z = CreateObject("Scripting.Dictionary")
Set xA = Range([1!G1], [1!A65536].End(3))
xA.Offset(1, 12).ClearContents:  Brr = xA
For i = 2 To UBound(Brr)
   T = Format(Trim(Brr(i, 2)), "0000000"): V = Format(Val(Brr(i, 7)), "0000000"): TT = T & "/" & V
   Z(TT) = i
Next
Arr = [1!M1].Resize(UBound(Brr), 3)
A = Array(Range([KH!C1], [KH!A65536].End(3)), Range([KH!J1], [KH!H65536].End(3)), Range([KP!C1], [KP!A65536].End(3)), Range([KP!K1], [KP!I65536].End(3)))
For Each Crr In A
   Crr = Crr: N = N + 1
   For i = 3 To UBound(Crr)
      T = Format(Trim(Crr(i, 1)), "0000000"): V = Format(Val(Crr(i, 2)), "0000000"): TT = T & "/" & V
      If Z.Exists(TT) Then
         If Arr(Z(TT), 1) = "" Then
            Arr(Z(TT), 1) = Crr(1, 1)
            ElseIf InStr("/" & Arr(Z(TT), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
            Arr(Z(TT), 1) = Arr(Z(TT), 1) & "/" & Crr(1, 1)
         End If
         Arr(Z(TT), N \ 3 + 2) = Crr(1, 3)
      End If
   Next
Next
[1!M1].Resize(UBound(Brr), 3) = Arr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 5# Andy2483



    ¹B¦æVBA¦Z¡AKP内¬O H- J Äæ¨S¦³Åª¨ú¥X¨Ó¡A¥H¦Ü¶À¦â³¡¤À¤£¥¿½T¡C
KP & KH ¨â­Óªí®æùØ¡A³£¦³¤À§O¦³¨â¥÷¸ê®Æ¡AÄæ A - C   & Äæ H - J

TOP

¦^´_ 6# 198188


    «e½ú¦Û¤v´ú¸Õªº½d¨Ò»P1#ªº½d¨Ò¤£¦P,«á¾Ç²q¿ù»Ý¨D,½Ð¤W¶Ç¯u¥¿ªº»Ý¨D½d¨Ò
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ 198188 ©ó 2024-3-4 18:20 ½s¿è

¦^´_ 7# Andy2483



³W«h¬O¤@¼Ëªº¡A¤£¹L²Ä¤@½d¨Ò¥i¯à¬Ý¨ì®ÄªG¨S¦³¨º麽²M·¡¡A©Ò¥H¤W¸ü²Ä¤G½d¨Ò¡A½Ð¥H³o­Ó¦^´_ªº¹Ï¤ù爲¨Ò

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-3-4 19:12 ½s¿è

¦^´_ 8# 198188


    ÁÂÁ «e½ú«ü¾É
­ì½d¨ÒKP¬OIJKÄæ¡A½Ð«e½ú¸Õ¤F¸Ñ¸Õ§ï¬Ý¬Ý
Range([KP!K1], [KP!I65536].End(3))
§ï¬°
Range([KP!J1], [KP!H65536].End(3))
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-3-5 08:41 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½Æ²ß¤F¤@¤U,¤è®×µù¸Ñ¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, i&, T$, TT$, A, xA As Range, N%
'¡ô«Å§iÅܼÆ:&¬Oªø¾ã¼Æ,%¬Oµu¾ã¼Æ,$¬O¦r¦êÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Set xA = Range([1!G1], [1!A65536].End(3))
'¡ô¥OxAÅܼƬO°Ï°ìÀx¦s®æ(¤u§@ªí "1"¸ÌªºG1Àx¦s®æ¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ)
xA.Offset(1, 12).ClearContents:  Brr = xA
'¡ô¥OxAÀx¦s®æ°¾²¾¤U1¦C¥k12Ä檺°Ï°ìÀx¦s®æ¤º®e²M°£,¥OBrrÅܼƬO¥HxAÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!¥Oi±q2 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Format(Trim(Brr(i, 2)), "0000000"): V = Format(Val(Brr(i, 7)), "0000000"): TT = T & "/" & V
   '¡ô¥OTÅܼƬOi°j°é¦C2ÄæBrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á,Âà¤Æ¬°7½X¼Æ­Èªº¦r¦ê,¦pªG¤£¨¬7½X«h¥ª°¼¥H0 ¸É¨¬
   '¡ô¥OVÅܼƬOi°j°é¦C7ÄæBrr°}¦C­ÈÂà¤Æ¬°¼Æ­È«á,¦AÂà¤Æ¬°7½X¼Æ­Èªº¦r¦ê,¦pªG¤£¨¬7½X«h¥ª°¼¥H0 ¸É¨¬
   '¡ô¥OTTÅܼƥH"/"²Å¸¹³s±µT»PVÅܼƩҲզ¨ªº·s¦r¦ê

   Z(TT) = i
   '¡ô¥O¥HTTÅܼƬ°key,item¬OiÅܼÆ(¦C¸¹)¯Ç¤JZ¦r¨å¤¤
Next
Arr = [1!M1].Resize(UBound(Brr), 3)
'¡ô¥OArrÅܼƬO ¤u§@ªí "1"ªºM1Àx¦s®æÂX®i¦V¤U/¦V¥k°Ï°ìÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C
A = Array(Range([KH!C1], [KH!A65536].End(3)), Range([KH!J1], [KH!H65536].End(3)), Range([KP!C1], [KP!A65536].End(3)), Range([KP!K1], [KP!I65536].End(3)))
'¡ô¥OAÅܼƬO 4­Ó°Ï°ìÀx¦s®æªº¤@ºû°}¦C
For Each Crr In A
'¡ô³]³v¶µ°j°é!¥OCrrÅܼƬO A°}¦C¸Ìªº°}¦C­È(°Ï°ìÀx¦s®æ)
   Crr = Crr: N = N + 1
   '¡ô¥OCrrÅܼÆÂàÅܬ° ¥HCrr°Ï°ìÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C (PS:CrrÅܼƫŧi¬O³q¥Î«¬ÅܼÆ,©Ò¥H¥i¥H¥ÑÀx¦s®æÅܬ°¤Gºû°}¦C)
   For i = 3 To UBound(Crr)
   '¡ô³]¶¶°j°é!¥Oi±q3¨ìCrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
      T = Format(Trim(Crr(i, 1)), "0000000"): V = Format(Val(Crr(i, 2)), "0000000"): TT = T & "/" & V
      '¡ô¦¹¼Ðªº¬°Crr°}¦C,T.V.TTÅܼƷN¸q¦P¤W
      If Z.Exists(TT) Then
      '¡ô¦pªGZ¦r¨å¸Ì¦³ TTÅܼƪºkey?
         If Arr(Z(TT), 1) = "" Then
         '¡ô¤S¦pªGArr°}¦C¸Ì(Z¦r¨å©Ò°O¾Ðªº¦C¸¹1Äæ)°}¦C­È¬OªÅ¦r¤¸?
            Arr(Z(TT), 1) = Crr(1, 1)
            '¡ô¥O¸Ó°}¦C­È¥H 1¦C1ÄæCrr°}¦C­È¼g¤J
            ElseIf InStr("/" & Arr(Z(TT), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
            '¡ô§_«h¦pªG¸Ó°}¦C­È¨S¦³ 1¦C1ÄæCrr°}¦C­È
            Arr(Z(TT), 1) = Arr(Z(TT), 1) & "/" & Crr(1, 1)
            '¡ô¥O¸Ó°}¦C­È¬O¥H ¦Û¨­¦r¦ê³s±µ 1¦C1ÄæCrr°}¦C­È©Ò²Õ¦¨ªº·s¦r¦ê
         End If
         Arr(Z(TT), N \ 3 + 2) = Crr(1, 3)
         '¡ô¥O¦bArr°}¦C¸Ì¼g¤J 1¦C3ÄæCrr°}¦C­È
      End If
   Next
Next
[1!M1].Resize(UBound(Brr), 3) = Arr
'¡ô¥O¤u§@ªí "1"ªºM1Àx¦s®æÂX®iªº½d³ò¸Ì¼g¤JArr°}¦C­È
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD