[µo°Ý] VBA¨Ì±ø¥ó¦Û°Ê¿z¿ï

msmplay

·Q½Ð±Ðºô¤j°ª¤â­Ì~~~~~~~

½Ð°Ý¦p¦ó¨Ï¥ÎVBA¹F¨ì¥H¤U¦Û°Ê¿z¿ï¥\¯à¡G
1.«ö¤U¡u¹O´Á¼ÆTOP 5¡v«ö¶s®É¡ACZÄæ¥i¦Û°Ê¿z¿ï¥X¹O´Á¼Æ«e5¦W¡C¨Ò¦p¹Ï¤@
2.«ö¤U¡u§¹¦¨²v§C©ó90%¡v«ö¶s®É¡ADAÄæ¥i¦Û°Ê¿z¿ï¥X§¹¦¨²v§C©ó90%¥H¤U¡C¨Ò¦p¹Ï¤G
3.«ö¤U¡u§@·~¤Ñ¼ÆTOP 5¡v«ö¶s®É¡ADBÄæ¥i¦Û°Ê¿z¿ï¥X§@·~¤Ñ¼Æ«e5¦W¡C¨Ò¦p¹Ï¤T

»¡©ú¡G
1. ²Ä223¦C¤u§@¼ÆÁ`­p¤£¦C¤J¿z¿ï±ø¥ó½d³ò¤º
2. ²Ä1»P²Ä3±ø¥ó¡A¦pªG¦P®É¦³2­Ó¥H¤W§¡¦³²Ä5¦Wªº¬Û¦P¼Æ¾Ú®É¡A«h§¡»Ý¿z¿ï¥X¨Ó
3. ¦]¬°¼Æ¾Ú·|¤£Â_§ó·s¡A©Ò¥H¥H¤W¤T­Ó±ø¥ó¼Æ¾Ú§¡¥i¯à¤£¦P¡A¬GµLªk¨Ï¥Î¥Ø«eªº©T©w¼Æ¾Ú¿ý»s¦¨¥¨¶°



ºÞ²zÁ`ªí.rar (60.22 KB)

¤U¸ü¦¸¼Æ: 16

2016-12-14 20:05

GBKEE

¦^´_ 1# msmplay


   

¤U¸ü (11.96 KB)

2016-12-15 08:21

2003 ¥i¥Î¹Ï¥Ü «e10 ¶µ ¿ý»s§A©Ò»Ýªº¬Ý¬Ý

1. Operator     ¿ï¾Ü©Êªº XlAutoFilterOperator ¸ê®ÆÃþ«¬¡C
   
2. 
   
3. XlAutoFilterOperator ¥i¥H¬O³o¨Ç XlAutoFilterOperator ±`¼Æ¤§¤@¡C
   
4. xlAnd default
   
5. xlBottom10Items           '«á­±(¶µ¥Ø)
   
6. xlBottom10Percent         '«á­±(¦Ê¤À¤ñ)
   
7. xlOr
   
8. xlTop10Items                '«e­±(¶µ¥Ø)   
   
9. xlTop10Percent             '«e­±(¦Ê¤À¤ñ)
   
10. ¥i¨Ï¥Î xlAnd ©M xlOr ±N Criteria1 ©M Criteria2 «Øºc½Æ¦X·Ç«h¡C

½Æ»s¥N½X

¸Õ¸Õ¬Ý

1. Sub ¿z¿ï´ú¸Õ()
   
2.     With Sheets("¤u§@§¹¦¨²v²Î­p")
   
3.         If .FilterMode Then
   
4.             .ShowAllData
   
5.         Else
   
6.             .Range("$B$3:$DB$222").AutoFilter
   
7.         End If
   
8.         .Range("$B$3:$DB$222").AutoFilter Field:=102, Criteria1:="5", Operator:=xlTop10Items
   
9.         '´ú¸Õ«e5¶µ¬ö¿ý¦ý·|®t¤@¶µ,¦]  ¸ê®Æªº³Ì«á¬O¤u§@¼ÆÁ`­p¦C¤]­pºâ¶i¥h¤F
   
10.         ' ©Ò¥H Criteria1:="6" ,¤~·|Åã¥Ü´ú¸Õ«e5¶µ¬ö¿ý
    
11.         .ShowAllData
    
12.         .Range("$B$3:$DB$222").AutoFilter Field:=103, Criteria1:="5", Operator:=xlTop10Items
    
13.         .ShowAllData
    
14.         .Range("$B$3:$DB$222").AutoFilter Field:=104, Criteria1:="<90%"
    
15.         ' ¤u§@¼ÆÁ`­p¦C¤]¦³­pºâ¶i¥h¦ý >90%,·|Åã¥Ü
    
16.        .AutoFilterMode = False
    
17.     End With
    
18. End Sub

½Æ»s¥N½X

msmplay

¦^´_ 2# GBKEE

G¤j~~~~·PÁ¼ö¤ßÀ°¦££¬¡I¤£¹L¤p©f¸Õ¤F¤£¦æ£°¡A«ö¤@¤U¥ô¦ó¤@­ÓÁä¡A³£·|¦³¦b¥X²{¿z¿ïªº°Ê§@¡A¦ý³Ì«á¤S¦^´_¨ì¨S¦³¿z¿ïªº¼Ë¤l¡I§A´ú¸Õ¥i¥H¦¨¥\¶Ü¡H

GBKEE

¦^´_ 3# msmplay

±N§A3­Ó«ö¶sªºµ{¦¡©ñ¦b¤@°_¤F,§A¥i©î¶}¦¨§A­nªº3­Ó¥¨¶°,

msmplay

¦^´_ 4# GBKEE

   ­ì¨Ó¦p¦¹¤X~~~~~¯u¬O¤Ó·PÁ±z¤FG¤j!!¤£¹L¥i¥H¦A½Ð±Ð¤@¤U~~~~´N¬O¤p©f¦Û¤v§ï¦n¤§«á¦pªþ¥ó¡A¦ý¹J¨ì¥H¤U°ÝÃD
1.¹O´Á¼Æ·|Åã¥Ü223¦CÁ`­p¡A§¹¦¨²v¡B§@·~¤Ñ¼Æ¬O§_¤]¥i¥H¦P¼Ë¿z¿ï«á©T©wÅã¥Ü223¦CÁ`­p©O?
2.§@·~¤Ñ¼Æ¿z¿ï«áµo²{¦P¼ËTOP10ªº¼Æ¦r10¦@¦³3­Ó¡A¦ý¦]¬°Åã¥Ü¬°TOP10¡A©Ò¥H¨Ã¦C¬°²Ä10¤jªº¼Æ¦r10¥uÅã¥Ü¤F2­Ó¡A¦C193¤Ï¦Ó¨SÄ|ªkÅã¥Ü(¤w¤Ï¬õ)¡A½Ð°Ý³o¬O§_¦³¨ä¥L¸Ñ¨M¤èªk©O?


«D±`·PÁÂ~~~~~~~
ºÞ²zÁ`ªí.rar (62.15 KB)

¤U¸ü¦¸¼Æ: 17

2016-12-17 00:40

GBKEE

¦^´_ 5# msmplay
193¦C®É»Ú¤W¬O9.5,¦]§A®æ¦¡¤W±Ä¥Î¼Æ­È¤p¼ÆÂI0¦ì,©Ò¥HÅã¥Ü¬°10
¥i­×§ï¤½¦¡=IF(SUM(G193:CX193)=0,"-",ROUND(AVERAGE(G193:CX193),0))

­ã´£³¡ªL

°²¦p²Ä1¦W¦³2­Ó, ²Ä2¦W¦³3­Ó, «e5¤j, À³¬O8­Ó???

msmplay

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

­ã¤j¨S¿ù¨S¿ù¡I¡I¡I

msmplay

¦^´_ 6# GBKEE

­ì¨Ó¦p¦¹¤X¡I¡IÁÂÁ§A£¬G¤j~~~~~

­ã´£³¡ªL

1. Dim FilArea As Range
   
2. 
   
3. Sub ¹O´Á¼Æ()
   
4. Dim R&, xClmn As Range, i&, LG, GG, N%
   
5. Call ¨ú®ø¿z¿ï
   
6. Set xClmn = FilArea.Columns(103)
   
7. R = Application.CountIf(xClmn, ">0")
   
8. If R = 0 Then Exit Sub
   
9. Sheets("¤u§@§¹¦¨²v²Î­p").AutoFilterMode = False
   
10. For i = 1 To R
    
11.     LG = Application.Large(xClmn, i)
    
12.     If GG <> LG Then N = N + 1: GG = LG
    
13.     If N = 5 Then Exit For
    
14. Next
    
15. FilArea.AutoFilter Field:=103, Criteria1:=">=" & GG
    
16. End Sub
    
17. 
    
18. Sub §C©ó90()
    
19. Call ¨ú®ø¿z¿ï
    
20. Sheets("¤u§@§¹¦¨²v²Î­p").AutoFilterMode = False
    
21. FilArea.AutoFilter Field:=104, Criteria1:="<90%"
    
22. End Sub
    
23. 
    
24. Sub §@·~¤Ñ¼Æ()
    
25. Dim R&, xClmn As Range, i&, LG, GG, N%
    
26. Call ¨ú®ø¿z¿ï
    
27. Set xClmn = FilArea.Columns(105)
    
28. R = Application.Count(xClmn)
    
29. If R = 0 Then Exit Sub
    
30. Sheets("¤u§@§¹¦¨²v²Î­p").AutoFilterMode = False
    
31. For i = 1 To R
    
32.     LG = Application.Large(xClmn, i)
    
33.     If GG <> LG Then N = N + 1: GG = LG
    
34.     If N = 5 Then Exit For
    
35. Next
    
36. FilArea.AutoFilter Field:=105, Criteria1:=">=" & GG
    
37. End Sub
    
38. 
    
39. Sub ¨ú®ø¿z¿ï()
    
40. Dim R&
    
41. With Sheets("¤u§@§¹¦¨²v²Î­p")
    
42.      .AutoFilterMode = False
    
43.      R = .UsedRange.Rows.Count - 1
    
44.      If R <= 4 Then Exit Sub
    
45.      Set FilArea = .Range("B3:DB" & R)
    
46.      FilArea.AutoFilter
    
47. End With
    
48. End Sub

½Æ»s¥N½X

[attach]26115[/attach]