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

[µo°Ý] °õ¦æ¶¥¬q¿ù»~13

[µo°Ý] °õ¦æ¶¥¬q¿ù»~13

¤w¸gªñ¤@­Ó¬P´Á¤F¡A§Ú²ª½­n³Q³o­Ó "°õ¦æ¶¥¬q¿ù»~13"·d±o§ÖºÆ¤F¡A¤@ª½¤£Â_¦a­×§ïµ{¦¡½X¡A¦ý¬O¿ù»~¤@ª½¤£«È®ð¦a¥X²{¡A½Ð¦U¦ì±Ï±Ï§Ú§a!
¥H¤U¬Oµ{¦¡½X¡A¬õ¦r¬O¥X²{°»¿ùªº³¡¤À¡AªþÀɸ̥t¦³¸Ô²Ó»¡©ú

Sub C_MERGE()
If [T7] = "" Then Exit Sub
[X7:AG100].ClearContents
Dim cRow&, T As Range

Set d = CreateObject("Scripting.Dictionary") '¤¤¤å«~¶µ¦X¨Ö
For Each T In Range([T7], [T300].End(xlUp))
   If d(T.Value) = "" Then '¦pªGTÄæ¸ê®Æ¥u¦³¤@µ§¤£­«½Æ
      d(T.Value) = T.Offset(, -6) & " " & T.Offset(, -4) & " " & T.Offset(, -3)
      Else
      d(T.Value) = d(T.Value) & ", " & T.Offset(, -6) & " " & T.Offset(, -4) & " " & T.Offset(, -3)

    End If
Next
[Y7].Resize(d.Count, 1) = Application.Transpose(d.keys)
[AF7].Resize(d.Count, 1) = Application.Transpose(d.items)
Set d = Nothing

'--------------------------
Set d1 = CreateObject("Scripting.Dictionary") '­^¤å«~¶µ¦X¨Ö
For Each T In Range([T7], [T300].End(xlUp))
If T.Offset(, 2) = "" Then GoTo AA
   If d1(T.Value) = "" Then
      d1(T.Value) = T.Offset(, -5) & " " & T.Offset(, 2) & " " & T.Offset(, -3)
      Else
      d1(T.Value) = d1(T.Value) & ", " & T.Offset(, -5) & " " & T.Offset(, 2) & " " & T.Offset(, -3)
    End If
AA: Next

[AG7].Resize(d1.Count, 1) = Application.Transpose(d1.items)
Set d1 = Nothing

cRow = Range("Y100").End(xlUp).Row
     If cRow < 7 Then Exit Sub
     Range("X7:X" & cRow).Formula = "=VLOOKUP(Y7,T:U,2,FALSE)"
     Range("Z7:Z" & cRow).Formula = "=COUNTIF(T$7:T$490,Y7)"
     Range("AA7:AA" & cRow).Formula = "=SUMIF(T$7:T$490,Y7,S$7:S$490)"
     Range("AB7:AB" & cRow).Formula = "=ROUND(AA7/$AC$5*$AC$4,0)"
     Range("AC7:AC" & cRow).Formula = "=SUMIF(T$7:T$390,Y7,R$7:R$390)"
     Range("AD7:AD" & cRow).Formula = "=IF(AE7=""TOOLING"",AC7+Z7*10,AC7+Z7*2)"
     
End Sub
test.rar (24.77 KB)
Jess

¦^´_ 2# ­ã´£³¡ªL

­ã¤j¡A±z¦n¡A³o­Ó°ÝÃD§Ú¤]·Q¹L¡A¦ý¬O¦P¼Ëªº»yªk¡A¤¤¤åªº¸ê®Æªø«×§óªø¡A¦X¨Ö«o¨S°ÝÃD¡C
Jess

TOP

¦^´_ 5# Kubi
·PÁÂK¤j¡A§Úª¦¤F«Ü¦h¤å¡A¤]½T»{¤F³o¤@ÂI¡C¦ý¬O²{¦b¦³­ÓÀY¯kªº¦a¤è¡A¦]¬°§Úªº¥N½XùØÁÙ¦³¨ä¥L´X­Ó¦a¤è³£¥Î¤FTranspose³o­ÓÂà¸m¨ç¼Æ¡A±N¨Ó¥i¯à·|ºÆ¨g°»¿ù¡A¸Ó¦p¦ó§ï¼g©O?­ã¤j¨â­Ó°}¦C¨Ã¥Îªº¼gªk¡A¥®¨à¶éµ{«×ªº§Ú¤£¤Ó·|¥Î?ªþ¤W§ó·sªºÀɮסAùØ­±¦³§Ú­ì©lªº¼gªk¡C
test1.rar (31.11 KB)
Jess

TOP

·PÁ­ã¤jªºÀ°¦£¡A¥Î±z¤¤­^¦X¨Öªº¤è¦¡½T¹ê¤S§Ö¤S¥¿½T¡A¤£¹L§Ú¦³­Ó°ÝÃD
    s1$ = T(, -5) & " " & T(, -3) & " " & T(, -2)
    s2$ = T(, -4) & " " & T(, 3) & " " & T(, -2)
T¬A¸¹¸Ìªº¼Æ­È¨ì©³¬O¥H­þ¤@Äæ°µ°ò·Ç±Àºâ¥X¨Óªº?
Jess

TOP

­ã¤j¡A¯à§_¦AÀ°§Ú¬Ý¤@¤U¡A§Ú§ï¼g«á¤u§@ªí1ªº¸ê®Æ­nÂà¨ì¤u§@ªí2ªº¥N½X¡A¦]¬°¤ñ¸û¤Ö¥Î³o¼ËªºÂà¸ê®Æªk¡A©Ò¥H»yªk¤£¬O«ÜÀ´¡A°õ¦æ°_¨ÓÁöµM¨S°»¿ù¡A¦ý¬O¸ê®Æ¤]¨SÂন¡C
test2.rar (31.05 KB)
Jess

TOP

¦^´_ 14# ­ã´£³¡ªL

ÁÂÁ­ã¤jªºÀ°¦£¡A§Ú¦A¦n¦n¬ã¨s¾Ç²ß¤@¤U¡C
Jess

TOP

¦^´_ 16# Kubi
ÁÂÁÂK¤j¡A³o¼Ë§Ú´N¤£¥Î§ï¼g¥þ³¡ªºµ{¦¡¤F:'(
Jess

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD