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

[µo°Ý] ¦h±ø¥ó¤ñ¹ï

[µo°Ý] ¦h±ø¥ó¤ñ¹ï

½Ð°Ý¦p¦ó«ö³¡ªù¡B¦~¸ê¤ÎÁ~¸ê¤ñ¹ï¥Xµ¥¯Å¡AÁÂÁ¡C

¦~¸êÁ~¸êµ¥¯Å.zip (7.27 KB)

½Ð°Ý¦p¦ó«ö³¡ªù¡B¦~¸ê¤ÎÁ~¸ê¤ñ¹ï¥Xµ¥¯Å¡AÁÂÁ¡C
shootingstar µoªí©ó 2024-3-28 11:58



    ¤£¦n·N«ä¡AÀ³¥H¦¹ªí­pºâµ¥¯Å

¦~¸êÁ~¸ê¤ñ¹ïµ¥¯Å.jpg (103.48 KB)

¦~¸êÁ~¸ê¤ñ¹ïµ¥¯Å.jpg

TOP

¦^´_ 2# shootingstar

ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ßVBA°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub TEST()
Dim Brr, Crr, Z, i&, j%, R&, C%, TT$, T$, T1$, T2$, xR As Range
Set Z = CreateObject("Scripting.Dictionary")
Set xR = [F65536].End(3): Brr = Range([I1], xR): R = UBound(Brr): C = UBound(Brr, 2)
For i = 2 To UBound(Brr): For j = 1 To 3: T = T & "/" & Brr(i, j): Next: Z(T) = i: T = "": Next
Range([D2], [A65536].End(3)).Copy xR(2)
With Range([I1], [F65536].End(3))
   .Sort KEY1:=.Item(1), Order1:=1, Key2:=.Item(2), Order2:=1, Key3:=.Item(3), Order3:=2, Header:=1
   Crr = .Value: .ClearContents: [F1].Resize(R, C) = Brr
End With
For i = 2 To UBound(Crr)
   If Crr(i, 4) <> "" And T <> Crr(i, 4) Then T = Crr(i, 4): T1 = Crr(i, 1): T2 = Crr(i, 2): GoTo i01
   TT = "/" & T1 & "/" & T2 & "/" & Crr(i, 3)
   If T1 <> Crr(i, 1) Or T2 <> Crr(i, 2) Or Not Z.Exists(TT) Then MsgBox "¸ê®Æ¿ù»~": Exit Sub
   Brr(Z(TT), 4) = T
i01: Next
[F1].Resize(R, C) = Brr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2024-3-28 15:50 ½s¿è

°Ñ¦Ò (·j´M¿é¤J½s¸¹12565) googleºô§}:https://hcm19522.blogspot.com/
{=OFFSET(D1,MATCH(1,(A2:A11=F2)*(B2:B11=G2)*(C2:C11>=H2),),)
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 3# Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÀ˵ø¨ì¦pªGÁ~¸ê­è¦n¦P¹ï·Óªíª÷ÃB,·|»~§P¬°¸ê®Æ¿ù»~,­×¥¿¦p¤U:

Option Explicit
Sub TEST()
Dim Brr, Crr, Z, i&, j%, R&, C%, TT$, T$, T1$, T2$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO¦r¨å
Brr = Range([I1], [F65536].End(3)): R = UBound(Brr): C = UBound(Brr, 2)
'¡ô¥OBrrÅܼƬO¥HÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C,¥OR/CÅܼƬO°}¦CÁa/¾î¦V³Ì¤j¯Á¤Þ¸¹
For i = 2 To UBound(Brr): For j = 1 To 3: T = T & "/" & Brr(i, j): Next: Z(T) = i: T = "": Next
'¡ô³]¶¶°j°é±N3Äæ°}¦C­È¥H"/"¦r¤¸¦êÁp¦¨ªº·s¦r¦ê·íkey,item¬O¦C¸¹,¯Ç¤JZ¦r¨å¸Ì
Range([D2], [A65536].End(3)).Copy: [F2].Insert Shift:=xlDown
'¡ô¥O±N¹ï·Ó¸ê®Æ½Æ»s´¡¤J¥Ø¼Ð¸ê®Æ¤W¤è
With Range([I1], [F65536].End(3))
   .Sort KEY1:=.Item(1), Order1:=1, Key2:=.Item(2), Order2:=1, Key3:=.Item(3), Order3:=2, Header:=1
   '¡ô¥O¸ê®Æ°µ¤T¼h±Æ§Ç¦³¼ÐÃD¦C±Æ§Ç,1/2¼h°µº¥¼W,²Ä3¼hº¥´î
   Crr = .Value: .ClearContents: [F1].Resize(R, C) = Brr
   '¡ô¥OCrrÅܼƬO¥H¸Ó½d³òÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C
End With
For i = 2 To UBound(Crr)
'¡ô³]¶¶°j°é!¥Oi±q2 ¨ìCrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   If Crr(i, 4) <> "" And T <> Crr(i, 4) Then T = Crr(i, 4): T1 = Crr(i, 1): T2 = Crr(i, 2): GoTo i01
   '¡ô¦pªG°j°éµ¥¯ÅÄæ°}¦C­È¤£¬OªÅ¦r¤¸,¥B»PTÅܼƤ£¦P! ´N¥OTÅܼƬO °j°éµ¥¯ÅÄæ°}¦C­È,
   '¥OT1ÅܼƬO°j°é³¡ªùÄæ°}¦C­È,T2ÅܼƬO°j°é¦~¸êÄæ°}¦C­È,µM«á¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ

   TT = "/" & T1 & "/" & T2 & "/" & Crr(i, 3)
   '¡ô¥OTTÅܼƬO·s²Õ¦X¦r¦ê
   If T1 <> Crr(i, 1) Or T2 <> Crr(i, 2) Or Not Z.Exists(TT) Then MsgBox "¸ê®Æ¿ù»~": Exit Sub
   '¡ô¦pªG¥Ø¼Ð¸ê®Æ¦³²§±`©Î¹ï·Ó¸ê®Æ²§±`,´N¸õ¥X´£¥Üµ¡~~,µ²§ôµ{¦¡°õ¦æ
   Brr(Z(TT), 4) = T
   '¡ô¥OZ¦r¨å¸Ì°O¿ý¦C¸¹²Ä4Ä檺Brr°}¦C­È¬O TÅܼÆ
i01: Next
[F1].Resize(R, C) = Brr
'¡ô¥O[F1]ÂX®iR¦CCÄæ½d³òÀx¦s®æ­È¥H Brr°}¦C­È¼g¤J
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD