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

[µo°Ý] ¦p¦ó§ì¨ú²Å¦X±ø¥óªº¹ïÀ³Äæ¦ì­È

[µo°Ý] ¦p¦ó§ì¨ú²Å¦X±ø¥óªº¹ïÀ³Äæ¦ì­È

½Ð±Ð¦U¦ì¥ý¶i~
²{¦³¤@¥÷®w¦s»P­q³æ²Î­pªº»Ý¨Dªí¡A
§Ú·QÅý¡u¥æ³f¤é´Á¡vªºÄæ¦ì¡A¯à°÷±q¡u¯ÊÃB¡vÄæ¦ì¡A¥Ñ¥ª¦Ü¥k·j´M¨ìªº²Ä¤@µ§­t¼Æ¡A¦Û°Ê±a¥X¨Ó©Ò¹ïÀ³ªº¥æ³f¤é´Á

­ì¥»¬O±Ä¥ÎIFªº¤½¦¡¡A¦ý¦]¬°Äæ¦ì·|·U¨Ó·U¦h¡Aıªº³o­Ó¤è¦¡«D±`¤£OK
¤£ª¾¥ý¶i­Ì ¬O§_¯à«üÂI«ü±Ð¦³¨S¦³§ó²©öªº§@ªk¡AÁÂÁ±z­Ì~

! ­q³æ»Ý¨Dªí.zip (10.97 KB)

°}¦C¤½¦¡:
=iferror(INDEX(SUBTOTAL(4,OFFSET(B$2,,,,COLUMN(A:O))),MATCH(1,(C4:P4<0)*(C$3:P$3="¯ÊÃB"),)),"")

TOP

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


   ÁÂÁÂ¥ý¶i~
   ¯uªº¤Ó¯«©_¤F¡A¤@¬q°}¦C¤½¦¡´N¸Ñ¨M¤F~
   §Ú±o¦n¦n®ø¤Æ¤@¤U¤F....
   «D±`ªº·PÁÂ~±z

TOP

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

TOP

¦^´_ 4# hcm19522


    ÁÂÁ¤j¤j~
    ¤S¦h¾Ç¨ì¤@ºØ¤è¦¡¤F~

TOP

¦^´_ 5# chi830



    2¼Ó "­ã¤j®v" ¬O¤j®v¤¤ªº¤j®v  ¦¹¤è¦¡´¿½Ð±Ð©ó¥L
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-12-1 16:21 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
20231201_1.jpg
2023-12-1 08:15


°õ¦æµ²ªG:
20231201_2.jpg
2023-12-1 08:15


Option Explicit
Sub TEST()
Dim Brr, Crr, v, Z, i&, j, C, T$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO¦r¨å
C = Application.Match("¥æ³f¤é´Á", [3:3], 0)
'¡ô¥OCÅܼƬO°õ¦æªí®æ¨ç¼Æ Match()ªº¦^¶Ç­È (²Ä3¦C¸Ì§ä Àx¦s®æ­È¬O "¥æ³f¤é´Á")
If IsError(C) Then Exit Sub
'¡ô¦pªGCÅܼƬO¿ù»~­È (Match()§ä¤£¨ì²Å¦XªºÀx¦s®æ!·|¦^¶Ç¿ù»~­È)
'©Ò¥HCÅܼƥ²¶·«Å§i¬°³q¥Î«¬ÅܼÆ

Brr = Range(Cells(1, C), [A65536].End(xlUp))
'¡ô¥OBrrÅܼƬO¸Ë¤JÀx¦s®æ­Èªº¤Gºû°}¦C
For j = 1 To C
'¡ô³]¶¶°j°é!j±q1 ¨ìCÅܼÆ
   T = T & Trim(Brr(2, j))
   '¡ô¥OTÅܼƬO³s±µ °j°é¦C2Äæ°}¦C­È¥h°£«e«áªÅ¥Õ¦r¤¸«áªº·s¦r¦ê
   If Trim(Brr(3, j)) = "¯ÊÃB" Then Z(j) = T: T = ""
   '¡ô¦pªG°j°é¦C2Äæ°}¦C­È¥h°£«e«áªÅ¥Õ¦r¤¸«áªº·s¦r¦ê¬O "¯ÊÃB"?,
   'True´N¥OjÅܼƬ°Key,Item¬O TÅܼÆ,¯Ç¤JZ¦r¨å¸Ì,µM«áTÅܼƲMªÅ

Next
For i = 4 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q4¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   Brr(i - 3, 1) = ""
   '¡ô¥O°}¦C¸Ìªº1Äæ¬Û¹ï¦ì¸m¦C°}¦C­È¬OªÅ¦r¤¸
   For Each j In Z.KEYS
   '¡ô³]³v¶µ°j°é!¥OjÅܼƬO Z¦r¨å¸ÌªºKeys¤§¤@
      If Val(Brr(i, j)) < 0 Then Brr(i - 3, 1) = Z(j): Exit For
      '¡ô¦pªG°j°é¦C¯ÊÃBÄæ¸Ìªº¼Æ­È¤p©ó0?
      'True´N¥O°}¦C¸Ìªº1Äæ¬Û¹ï¦ì¸m¦C°}¦C­È¬O jÅܼƬdZ¦r¨å¦^¶Ç­È

   Next
Next
Cells(4, C).Resize(UBound(Brr) - 2, 1) = Brr
'¡ô¥Oµ²ªG¦ì¸mÀx¦s®æ½d³ò¼g¤JBrr°}¦C­È
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤H­n¦Û·R¡A¤~¯à·R´¶¤Ñ¤Uªº¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD