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

½Ð°ª¤âÀ°¦£¸Ñµª,ÁÂÁÂ

½Ð°ª¤âÀ°¦£¸Ñµª,ÁÂÁÂ

¤ý¦Ñ¤»À\ÆU¤H­û¦W³æ¡A²Õ§O¡A¾ºÙ¡A©m¦W¡A¾µ¥¦bA5~D32²LÂŦâ°Ï°ì
¦]½ü¯ZªºÃö«Y¡A¥X¶Ô¥i¯à¦³¤£¦Pªº²Õ¦X¥X¶Ô¡A¦ý¤@©w³£¬O¤@²Õ¤@²Õ¤@°_¨ì¾¡A¨C²Õªº¤H¼Æ¤£¤@©w¡A¦Ü©ó¤@¤Ñ¦³´X²Õ¬O¤£¤@©wªº¡AC35~C39¤¤ªº¤U©Ô¿ï³æ¬O¥i¥H¿ï¾Ü¤µ¤é¥X¶Ô»P§_ªº±±¨î¿ï¶µ
²{¦b·Q¦b¶À¦â°Ï°ì³sÄò§e²{¤µ¤Ñ¨ì¶Ôªº¤p²Õ¦W³æ¡A½Ð°Ý­n¦p¦ó§¹¦¨??¦W³æ­n«ö²Õ§O¤j¤p±Æ¦C¥Ñ¤p¨ì¤j¡A¹³½d¨ÒL:N¬O¦pªG¬O2©M5²Õ¨ì¶Ôªº§e²{¡AP:R¬O3,4²Õ¨ì¶Ôªº§e²{¡A¥H¦¹Ãþ±À¡C
§Ú²{¦bªº§@ªk¬O:¦pªGC35=¬O¡A«hE5~G8·|§e²{¡A¥H¦¹Ãþ±À¡A¦ýµLªkÅý¥¦³sÄò¤£¶¡Â_±Æ¦C¨ì¶À¦â°Ï°ì¡A­n¦p¦ó§¹¦¨¡A½Ð°ª¤â«üÂI¤@¤U¡AÁÂÁÂ
½d¨Ò¦bªþ¥ó¡A¦pªG¦³»¡ªº¤£²M·¡ªº¦A½Ðµo°Ý,ÁÂÁ¡Ä
¬¡­¶Ã¯1.rar (11.71 KB)
jeason

(¿é¤J½s¸¹12192) googleºô§}:https://hcm19522.blogspot.com/
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-12-27 12:17 ½s¿è

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

Option Explicit
Sub TEST()
Dim Brr, Z, i&, j%, sR As Range, eR As Range, xR As Range
With ActiveSheet.UsedRange
   Intersect(.Offset(0, 7), .Offset(4, 0)).Delete Shift:=xlUp
End With
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([A1], [C5].End(xlDown)(2, 2))
For i = 1 To UBound(Brr)
   If Trim(Brr(i, 1)) <> "" Or i = UBound(Brr) Then
      Set sR = eR: Set eR = Cells(i, 1)
      If Not sR Is Nothing Then
         Z(sR & "") = Range(sR(1, 2), eR(0, 4))
         Z(sR & "/r") = eR.Row - sR.Row
      End If
   End If
Next
Brr = Range([C35], [B65536].End(xlUp)): Set xR = [H5]
For i = 1 To UBound(Brr)
   If Brr(i, 2) = "¬O" Then
      With xR.Resize(Z(Brr(i, 1) & "/r"), 3)
         .Value = Z(Brr(i, 1))
         .Rows(1).Font.Bold = True
         For j = 7 To 10: .Borders(j).Weight = 4: Next
      End With
      Set xR = xR(Z(Brr(i, 1) & "/r") + 1, 1)
   End If
Next
End Sub
'==========================================================
¥H¤U¬O²Õ§O¤]±a¤Jªº¤è®×
°õ¦æ«e:                                                           °õ¦æµ²ªG:
   

Option Explicit
Sub TEST()
Dim ¸ê®Æ°}¦C, ¨ì¶Ô±±¨î°}¦C, ¦r¨å, i&, j%, °_©l®æ As Range, µ²§ô®æ As Range, µ²ªG°_©l®æ As Range
With ActiveSheet.UsedRange
   Intersect(.Offset(0, 7), .Offset(4, 0)).Delete Shift:=xlUp
End With
Set ¦r¨å = CreateObject("Scripting.Dictionary")
¸ê®Æ°}¦C = Range([A1], [C5].End(xlDown)(2, 2))
For i = 1 To UBound(¸ê®Æ°}¦C)
   If Trim(¸ê®Æ°}¦C(i, 1)) <> "" Or i = UBound(¸ê®Æ°}¦C) Then
      Set °_©l®æ = µ²§ô®æ: Set µ²§ô®æ = Cells(i, 1)
      If Not °_©l®æ Is Nothing Then
         ¦r¨å(°_©l®æ & "") = Range(°_©l®æ(1, 1), µ²§ô®æ(0, 4))
         ¦r¨å(°_©l®æ & "/r") = µ²§ô®æ.Row - °_©l®æ.Row
      End If
   End If
Next
¨ì¶Ô±±¨î°}¦C = Range([C35], [B65536].End(xlUp)): Set µ²ªG°_©l®æ = [H5]
For i = 1 To UBound(¨ì¶Ô±±¨î°}¦C)
   If ¨ì¶Ô±±¨î°}¦C(i, 2) = "¬O" Then
      With µ²ªG°_©l®æ.Resize(¦r¨å(¨ì¶Ô±±¨î°}¦C(i, 1) & "/r"), 4)
         .Value = ¦r¨å(¨ì¶Ô±±¨î°}¦C(i, 1))
         .Rows(1).Font.Bold = True
         .Columns(1).Merge
         For j = 7 To 10: .Borders(j).Weight = 4: Next
      End With
      Set µ²ªG°_©l®æ = µ²ªG°_©l®æ(¦r¨å(¨ì¶Ô±±¨î°}¦C(i, 1) & "/r") + 1, 1)
   End If
Next
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 4# brabus

°}¦C¤½¦¡//¥k©Ô¥|®æ/¤U©Ô
=iferror(INDEX(A:A,SMALL(IF(VLOOKUP(T(IF({1},LOOKUP(ROW($5:$32),ROW($5:$32)/($A$5:$A$32<>""),$A$5:$A$32))),$B$35:$C$39,2,)="¬O",ROW($5:$32)),ROW(A1)))&"","")

1) ¥u¯à¨ú¥X²Ä¤@­Ó"²Ä?²Õ"
2) &"" ·|Åܦ¨¯Â¤å¦r®æ¦¡, "¾µ¥"ªº¼Æ¦r¤]¬O¤å¦r®æ¦¡,
   ­Y¤£·Q¼Æ­ÈÅܤå¦r®æ¦¡, ¥i¥H§R°£³o­Ó, ¦ýÀx¦s®æ®æ¦¡¶·³]©w¬°"#"...§Y0­È¤£Åã¥Ü

TOP

¦^´_ 2# hcm19522
ÁÂÁÂH¤j¤Î­ã¤jªº¦^´_
¦A½Ð°Ý¦pªG³s²Ä´X²Õ³£­n§e²{ªº¸Ü¡A¬O§_¥i¯à??·Pı²o¯A¨ìÀx¦s®æ¦X¨Öªº°ÝÃD¦n¹³¤£¥i¯à¹ï¤£¹ï???
jeason

TOP

H5//°}¦C¤½¦¡(¤TÁ䧹¦¨)
=iferror(INDEX(B:B,SMALL(IF(1-ISNA(MATCH(LOOKUP(ROW($5:$32),ROW($5:$32)/($A$5:$A$32<>""),$A$5:$A$32)&"¬O",$B$35:$B$39&$C$35:$C$39,)),ROW($5:$32)),ROW(A1))),"")

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD