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

[µo°Ý] Âù±ø¥ó·j´M»PÁ`³~µ{¤½¦¡

[µo°Ý] Âù±ø¥ó·j´M»PÁ`³~µ{¤½¦¡

¨t²ÎWIP¸ê®Æ¥u¦³³~µ{½s¸¹¡A·Q­nª¾¹D¥Ø«eWIP¤w¸g¥Í²£¨ì²Ä´X¹D³~µ{&Á`¦@¦³´X­Ó³~µ{
¥Ø«e¬O¥Î¤U¹Ïªº¤½¦¡¡A¦ý¦]¬°¸ê®Æ¶q·U¨Ó·U¤j(®Æ¸¹¦³¤W¤dµ§*³~µ{¬ù35~65¤£µ¥)¾É­P¦³®É¶}±ÒÀÉ®×·|«Ü½wºC
·Q½Ð±Ð¦U¦ì°ª¤â¦³µL¨ä¥L¸û¤£¦Y¸ê·½ªº¤½¦¡©ÎªÌ¨ä¥L¤èªk(©ÎªÌ¬O­n¨Ï¥Î¥¨¶°¶]§¹Åܦ¨¤å¦r?)
1.³~µ{¸ê®Æ
°ÝÃD1.jpg
2021-6-13 02:08

2.WIP(DÄæ¬O¥Ø«eªº¤½¦¡)
°ÝÃD2.jpg
2021-6-13 02:08

³~µ{°ÝÃD20210613.rar (11.2 KB)
Adam

®Æ¸¹¦³¤W¤dµ§*³~µ{¬ù35~65¤£µ¥
__¸ê®Æ¤j¬ù3~6¸U¦æ

¥i¥Î©w¸q¦WºÙ, ¦ý¤£¨£±o§Ö¦h¤Ö, offset¥»¨­´N·|¤zÂZ¤u§@ªí¾Þ§@~~
¥Îvba§a!

TOP

¨âºØ¤è¦¡:
Xl0000558.rar (14.44 KB)

TOP

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

ª©¥D¯u¬O¤Ó±j¤j¤F¡A¤£¹L¥¨¶°¤º®e¬Ý¤£¤ÓÀ´§Ú¦b¬O¬O¬Ý®M¨ì¥¿¦¡ªºÀɮ׬ݬݡAÁÂÁÂ
Adam

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2021-6-13 16:36 ½s¿è

D2=LOOKUP(,0/((A2=³~µ{¸ê®Æ!A:A)*(B2=³~µ{¸ê®Æ!C:C)),COUNTIF(OFFSET(³~µ{¸ê®Æ!A$2,,,ROW($1:$1998)),³~µ{¸ê®Æ!A$2:A$1999))-1&"/"&COUNTIF(³~µ{¸ê®Æ!A:A,A2)

https://blog.xuite.net/hcm19522/twblog/589831005
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½úVBA¤è®×,¤è®×¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É

°õ¦æ«e:
20230525_1.jpg
2023-5-25 09:48


°õ¦æµ²ªG:
20230525_2.jpg
2023-5-25 09:49



Sub TEST_A1()
Dim Arr, xD, T1$, T2$, i&
'¡ô«Å§iÅܼÆ:(Arr,xD)¬O³q¥Î«¬ÅܼÆ,(T1,T2)¬O¦r¦êÅܼÆ,i¬Oªø¾ã¼ÆÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxD³o³q¥Î«¬ÅܼƬO ¦r¨å
Arr = Range(Sheet1.[c1], Sheet1.Cells(Rows.Count, 1).End(xlUp))
'¡ô¥OArr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥Hªí1ªºA~CÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é
    T1 = Arr(i, 1): T2 = Arr(i, 3)
    '¡ô¥OT1³o¦r¦êÅܼƬO i°j°é¦C²Ä1ÄæArr°}¦C­È
    xD(T1) = xD(T1) + 1
    '¡ô¥O¥HT1ÅܼƷíkey,item²Ö¥[1,¯Ç¤JxD¦r¨å¸Ì
    xD(T1 & "/" & T2) = Arr(i, 2)
    '¡ô¥OT1ÅܼƳs±µ"/",¦A³s±µT2ÅÜ¼Æ ©Ò²Õ¦¨ªº·s¦r¦ê·íkey,
    'item¬O i°j°é¦C²Ä2ÄæArr°}¦C­È

Next i
Arr = Range(Sheet2.[c1], Sheet2.Cells(Rows.Count, 1).End(xlUp))
'¡ô¥OArr°}¦C­Ë±¼Â°}¦C­È,´«¸Ëªí2ªºA~CÄæÀx¦s®æ­È
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é
    T1 = xD(Arr(i, 1) & "")
    '¡ô¥OT1ÅܼƬO (¥Hi°j°é¦CArr°}¦C­È)¬d xD¦r¨å¦^¶Çitem­È
    'Arr(i, 1) & "":°}¦C­È¥[ªÅ¦r¤¸,©w¸qÅý¥¦¬O¦r¦ê ¬d¦r¨å

    T2 = xD(Arr(i, 1) & "/" & Arr(i, 2))
    '¡ô¥OT2ÅܼƬO i°j°é¦C²Ä1ÄæArr°}¦C­È³s±µ"/",¦A³s±µi°j°é¦C²Ä2ÄæArr°}¦C­È,
    '©Ò²Õ¦¨ªº·s¦r¦ê

    Arr(i - 1, 1) = T2 & IIf(T2 = "", "", "/") & T1
    '¡ô¥OT2ÅܼƳs±µ"/"¦A³s±µT1ÅܼƩҲզ¨ªº·s¦r¦ê,
    '¼g¨ìArr°}¦C¸Ì(Âл\±¼­ì°}¦C­È)

Next i
With Sheet2.[e2].Resize(UBound(Arr) - 1)
'¡ô¥H¤U¬OÃö©ó[E2]Àx¦s®æÂX®i¦V¤UArr°}¦C³Ì¤j¯Á¤Þ¸¹-1®æ,
'¦]¬°¦³®Äµ²ªG­È¬O±q1¯Á¤Þ¸¹¶}©l©ñ,©Ò¥H­n-1

     .NumberFormatLocal = "@"
     '¡ô¥O³o½d³òÀx¦s®æ®æ¦¡¬O ¤å¦r
     .Value = Arr
     '¡ô¥O³o½d³òÀx¦s®æ¥HArr°}¦C­È¼g¤JÀx¦s®æ¸Ì,¶W¹L³o½d³òªº°}¦C­È©¿²¤
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST()
Dim Brr, Crr, Y, i&, TT$, T1$, T2$, T3$
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([³~µ{¸ê®Æ!C1], [³~µ{¸ê®Æ!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~CÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = UBound(Brr) To 2 Step -1
'¡ô³]°f°j°é
   T1 = Brr(i, 1): T2 = Brr(i, 2): T3 = Brr(i, 3): TT = T1 & "|" & T3
   '¡ô¥OÅܼƸˤJ°}¦C­È©Î°}¦C­È©Ò²Õ¦¨ªº¦r¦ê
   If Y(T1) = "" Then Y(T1) = T2
   '¡ô¦pªGT1ÅܼƬd¦r¨å ¨äitem­È¬OªÅªº!´N¥O¨äitem­È¬O T2ÅܼÆ(³Ì¤j³~µ{§Ç¸¹)
   Y(TT) = T2 & "/" & Y(T1)
   '¡ô¥OTT³o²Õ¦X¦r¦ê·íkey,item¬O T2ÅܼƳs±µ "/" ¦A³s±µ,
   '¦A³s±µ¥HT1ÅܼƬdY¦r¨å±o¨ìªºitem­È©Ò²Õ¦¨ªº·s¦r¦ê

Next
Brr = Range([WIP!C2], [WIP!A65536].End(3))
'¡ô¥OBrr°}¦C­Ë±¼Â°}¦C­È,´«¸Ë(WIP)ªíªºA~CÄæÀx¦s®æ­È
ReDim Crr(1 To UBound(Brr), 1 To 1)
'¡ô«Å§iCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr°}¦C,¾î¦V1~1
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   Crr(i, 1) = Y(Brr(i, 1) & "|" & Brr(i, 2))
   '¡ô¥Oi°j°é¦C²Ä1ÄæCrr°}¦C­È¬O ²Ä1Äæ»P²Ä2ÄæBrr°j°é°}¦C­È,
   '¥H"|"¶¡¹j ©Ò²Õ¦¨ªº·s¦r¦ê,¬dY¦r¨å©Ò±o¨ìªºitem­È

Next
With [WIP!F2].Resize(UBound(Crr), 1)
'¡ô¥H¤U¬OÃö©ó[F2]Àx¦s®æÂX®i¦V¤UCrr°}¦C³Ì¤j¯Á¤Þ¸¹¦C
     .NumberFormatLocal = "@"
     '¡ô¥O³o½d³òÀx¦s®æ®æ¦¡¬O ¤å¦r
     .Value = Crr
     '¡ô¥O³o½d³òÀx¦s®æ¥HCrr°}¦C­È¼g¤JÀx¦s®æ¸Ì,¶W¹L³o½d³òªº°}¦C­È©¿²¤
End With
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD