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

[µo°Ý] Excel¬O§_¥i¥H¦Û°Ê¿z¿ï¡H

[µo°Ý] Excel¬O§_¥i¥H¦Û°Ê¿z¿ï¡H

«Ý¿ì¨Æ¶µ.xlsx.zip (7.05 KB)

·Q¸ß°Ý¤j¦U°ª¤â
«Ý¿ì¨Æ¶µ²M³æ­Y³Æµù¥´¤W§¹¦¨¡A¨º¦æ·|¦Û°Ê®ø¥¢¡Aª½¨ì¦Û¤v¿z¿ï®É¤U©Ô"§¹¦¨"¤~Åã¥Ü¥X¨Ó
·Q¸ß°Ý¦U¦ì°ª¤â¡A¬O§_¦³¤èªk¥i¥H³]©w©O¡H
ÁÂÁÂ~

¦^´_ 1# ÄÉ¡C


¦n¹³¨S¿ìªk§a!
¥i¯à­n½Ð±Ð¨ç¼Æ¤j¯«¤F¡C
¥ÎWorksheet_Change¬O¥i¥H°µ¨ì¡A
¤£¹L­n¼gVBA¥¨¶°¡C
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

B2:B5=IF((SUMPRODUCT(N(C$2:C$5="§¹¦¨"))=4)+(C2=""),E2,"")

B9:B12=IF((SUMPRODUCT(N(C$9:C$12="§¹¦¨"))=4)+(C9=""),CHOOSE(ROW(A1),"µo¨ç¦Ü¦U³æ¦ì","¬d¬Ý¦a¤U°±¨®³õ«OÅ@ªO","¤F¸Ñ¬I¤u¤á§O","¾ã²z¹Ï®Ñ"),"")

5164.png (21.11 KB)

5164.png

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

TOP

B2:B5=IF((SUMPRODUCT(N(C$2:C$5="§¹¦¨"))=4)+(C2=""),E2,"")

B9:B12=IF((SUMPRODUCT(N(C$9:C$12="§¹¦¨" ...
hcm19522 µoªí©ó 2018-10-28 11:08



    ¤j¤j±z¦n~ÁÂÁ±zÄ@·NÀ°§Ú¸Ñ¨M³o­Ó°ÝÃD

TOP

¤£ª¾¬O§_¦pªþ¥ó:

«Ý¿ì¨Æ¶µ.zip (8.54 KB)

TOP

¤u§@ªíIJµoVBA:
Dim Ur As Range

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$C$1" Then
   Cancel = True
   Call FilterAreaChk
   If Ur.SpecialCells(xlCellTypeVisible).Count = Ur.Count Then
      Ur.AutoFilter Field:=3, Criteria1:="<>§¹¦¨"
   Else
      Ur.AutoFilter Field:=3
   End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Item(1)
     If .Column <> 3 Or .Row = 1 Or .Value <> "§¹¦¨" Then Exit Sub
     Call FilterAreaChk
     Ur.AutoFilter Field:=3, Criteria1:="<>§¹¦¨"
End With
End Sub

Sub FilterAreaChk()
If Me.AutoFilterMode = False Then
   Range([A1:C1], Me.UsedRange).AutoFilter
End If
Set Ur = Me.AutoFilter.Range
End Sub

Xl0000227(¦Û°Ê¿z¿ïÁôÂÃ).rar (13.26 KB)

TOP

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

½Ð°Ý­ã¤j
¦b«Ý¿ì¨Æ¶µ²M³æ¤¤, ¦p¦³ºò«æ¥ô°È­nÀu¥ý³B²z,                       
¥i§_¦b¥´¶}¤åÀÉ®É, ¦³°T®§µ¡¤f¼u¥X³qª¾                       
                       
Á|¨Ò:                       
        ºò«æ!!               
        CC,        <----°õ¦æªÌ¦W¦r½ÐÅã¥Ü"ÂŦâ"       
        µo¨ç¦Ü¦U³æ¦ì               
        AA,               
        ¬d¬Ý¦a¤U°±¨®³õ«OÅ@ªO

Xl0000227(¦Û°Ê¿z¿ïÁôÂÃ).rar (13.6 KB)

TOP

¦^´_ 7# Qin

¥Î"®æ¦¡¤Æ±ø¥ó"§a~~~
=($C2="CC")*($D2="ºò«æ")*($E2<>"Done")
=($C2="AA")*($D2="ºò«æ")*($E2<>"Done")

Xl0000227(¦Û°Ê¿z¿ïÁôÂÃ)-1.rar (13.93 KB)

TOP

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

¦nªº, ÁÂÁ­ã¤j

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD