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

[µo°Ý] ¤U©Ô¿ï³æ¼Ò½k·j´M«á¬Û¦P¦WºÙ¤£¦P­È¤§§x´b...

¦^´_ 11# cypd

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr, xD, C%, T$, m&, i&
If Target.Column = 3 Then C = 3
If Target.Column = 4 Then C = 4
Set xD = CreateObject("Scripting.Dictionary")
With Sheets("«È¤á°ò¥»¸ê®Æ")
    Arr = .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3))
    .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3)).Font.ColorIndex = 0
    .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3)).Interior.ColorIndex = 0
    For i = 1 To UBound(Arr)
        T = Arr(i, 1)
        If xD.Exists(T) Then
            m = xD(T)
            Cells(m, C).Font.ColorIndex = 3
            Cells(m, C).Interior.ColorIndex = 36
            Cells(i, C).Font.ColorIndex = 3
            Cells(i, C).Interior.ColorIndex = 36
        End If
        xD(T) = i
    Next
End With
End Sub

TOP

¦^´_  samwang

·PÁ±zªº¦^ÂÐ
©Ò´£¨Ñ¤§¤è¦¡¦³¸Õ¹L...µ§¼Æ·U¦h®É·|³y¦¨©Ò­z¶°é°é°ÝÃD
cypd µoªí©ó 2022-7-23 09:18


10¼Ó¤è¦¡¬O¥Î¤º«Ø¡A§Aªº11¼Ó¤è¦¡¬O¥Î¤½¦¡¡A©Ò¥H¦³¥i¯à·|¤ñ¸ûºC
½Ð¦b½T»{¬Ý¬Ý¡AÁÂÁÂ

TOP

¦^´_ 10# samwang

·PÁ±zªº¦^ÂÐ
©Ò´£¨Ñ¤§¤è¦¡¦³¸Õ¹L...µ§¼Æ·U¦h®É·|³y¦¨©Ò­z¶°é°é°ÝÃD

TOP

¦^´_  cypd

¤£¦n·N«ä¦U¦ì¥ý¶i...
°w¹ïªì©lÀɮײ{¦³¤@°ÝÃD¥X²{



Àɮ׳¡¤À C Äæ¤á¦W­ì³]¦³®æ¦¡¤Æ±ø ...
cypd µoªí©ó 2022-7-22 17:00


³]©w®æ¦¡¤Æ-->­«½Æ­È¡A³o¼ËÀ³¸Ó¥i¥H§a?

1.JPG (153.57 KB)

1.JPG

TOP

¦^´_ 1# cypd

¤£¦n·N«ä¦U¦ì¥ý¶i...
°w¹ïªì©lÀɮײ{¦³¤@°ÝÃD¥X²{



Àɮ׳¡¤À C Äæ¤á¦W­ì³]¦³®æ¦¡¤Æ±ø¥ó­«ÂЬۦP¤á¦Wªº¸ê®Æ·|¦³¦â©³+¬õ¦â¦r«¬¤§°Ï§O
¥t D Äæ±b¸¹¦P¼Ë³]¦³®æ¦¡¤Æ±ø¥ó­«ÂЬۦP±b¸¹ªº¸ê®Æ·|¦³¦â©³+¬õ¦â¦r«¬¤§°Ï§O(­«­n¤§°Ñ¦Ò...¬O§_¦³­«½Æ¼Æ¾Ú§PÂ_§R°£¨Ì¾Ú)
¦]µ§¼Æ¸ê®Æ¼W¦h  ³y¦¨¨ç¼Æ COUNTIF Åã¥Ü¯S§OºC, ¸ê®Æµ§¼Æ¼W¥[¤@¦h, ·|¶°é°é¥dÀɤ§µ~ªp!

¬O§_¯à¥H  VBA µ{¦¡³B²z¥[§Ö³t«×
°w¹ï C¡BD ¨âÄæ¿é¤J®É¯à¦³¦Û°Ê§e²{¦p¤W(¦â©³+¬õ¦â¦r«¬¤§°Ï§O)...ªí¥Ü¦³­«½Æ¤§¤á¦W©Î±b¸¹¼Æ¾Ú

0722.rar (48.57 KB)

TOP

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

«D±`·PÁ  ­ã´£³¡ªL  ªº«ü¾É ...^^

°w¹ï©Ò¦^ÂФ§¬ÛÃö¤½¦¡...
¥u¿é¤J²µu¤å¦r©Î¼Æ¦r,¥H¦WºÙ©Î±b¸¹¬d¸ß...¥¿¬O©Ò»Ý¤§®ÄªG

¨Ã¦Ò¶q±b¸¹³Ì¦h14½X
¶}ÀY¦³0//...¦]¨C®a¾÷ºc½X¼Æ¤£¦P©Ò¦Ü
=TRIM(TEXT(MID(Q7,FIND("_",Q7&"_")+1,99),REPT("0 ",MAX(,LEN(Q7)-FIND("_",Q7&"_")))))
©Ò­z°ÝÃD¤wÀò¸Ñ´b...·P®¦

TOP

¦^´_ 6# cypd

¶}ÀY¦³0//
=TRIM(TEXT(MID(Q7,FIND("_",Q7&"_")+1,99),REPT("0 ",MAX(,LEN(Q7)-FIND("_",Q7&"_")))))

¶}ÀY¨S0
=TRIM(TEXT(MID(Q7,FIND("_",Q7&"_")+1,99),REPT("# ",14)))

±b¸¹³Ì¦h14½X

TOP

¦^´_ 5# ­ã´£³¡ªL
«D±`·PÁ  ­ã´£³¡ªL  ªº«ü¾É ...^^


§Æ±æ A3 Àx¦s®æ®æ¦¡¯à¹³ A8 ¤@¼Ëªº¶¡®æ(¦]®M¦Lªí³æ¦ì¸m©Ò»Ý)
¦r«¬ Arial Narrow 20
½Ð°Ý­ì A3¤½¦¡...MID(Q7,FIND("_",Q7&"_")+1,99) ¸Ó¦p¦ó­×¥¿¤~¯à²Å¦X¸Ó¼Ë¦¡?

0603.rar (47.78 KB)

TOP

¦^´_ 4# cypd

­ìÀɤw¦³»²§UÄæ¥i¥Î//
Xl0000074.rar (48.62 KB)

TOP

¦^´_ 3# zz5151353
«D±`·PÁ ª©¥D zz5151353   ¼ö¤ß¦^ÂÐ
Ãö©ó¨Ì·Ó©Ò¥Ü

¼Ò½k¬d§ä
Q7 ¿é¤J ¥ÃÂ×

Q8 Àx¦s®æ¤½¦¡¤½¦¡,¤U©Ô
1.        =IFERROR(INDEX(«È¤á°ò¥»¸ê®Æ!C:C,AGGREGATE(15,6,ROW($2:$1501)/((ISNUMBER(FIND($Q$7,«È¤á°ò¥»¸ê®Æ!$C$2:$C$1501)))*(--MATCH(«È¤á°ò¥»¸ê®Æ!$C$2:$C$1501,«È¤á°ò¥»¸ê®Æ!$C$2:$C$1501,)=ROW($1:$1500))),ROW(1:1))),"")

¸g¹ê´ú¯àÅã¥Ü¬ÛÃö¥ÃÂפ§¤á¦W¦WºÙ
½Ð°ÝQ8 Àx¦s®æ¤½¦¡¤½¦¡¤U©Ô¬O§_¯à¥¿½TÅã¥Ü©ó«È¤á°ò¥»¸ê®Æ¬ÛÃö¥ÃÂפ§¤á¦W¥þ³¡§¹¾ã§e²{¤§µ§¼Æ¡K4µ§

TOP

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