[µ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

msmplay

¦^´_ 12# GBKEE


   ÁÂÁÂG¤j¼ö¤ßÀ°¦££°~~~~~~~~~~

GBKEE

¦^´_ 11# msmplay


  

¤U¸ü (30.19 KB)

2016-12-18 08:09

1. Option Explicit
   
2. Sub Ex_§@·~¤Ñ¼Æ()
   
3.     Ex_¿z¿ï "§@·~¤Ñ¼Æ", 10, 3   'xlTop10Items=3 ½Ð¬Ý¹Ï¥Ü  
   
4. End Sub
   
5. Sub Ex_§¹¦¨²v()
   
6.     Ex_¿z¿ï "§¹¦¨²v", "<0.9", 0
   
7. End Sub
   
8. Sub Ex_¹O´Á¼Æ()
   
9.     Ex_¿z¿ï "¹O´Á¼Æ", 10, 3
   
10. End Sub
    
11. Sub Ex_¿z¿ï(¿z¿ï As String, ¿z¿ï·Ç«h As String, xl_Operator As Integer)
    
12.     Dim Rng As Range
    
13.     With Sheets("¤u§@§¹¦¨²v²Î­p")
    
14.         Set Rng = .Rows("1:3").Find(¿z¿ï, lookat:=xlWhole)
    
15.         If Rng Is Nothing Then MsgBox "§ä¤£¨ì  " & ¿z¿ï: Exit Sub
    
16.         Set Rng = Range(Rng.Range("A3"), Rng.Range("A4").End(xlDown).Offset(-1))
    
17.         If .FilterMode Or .AutoFilterMode Then .AutoFilterMode = False   ' ¨ú®ø [¦Û°Ê¿z¿ï]
    
18.         If xl_Operator > 0 Then
    
19.             Rng.AutoFilter Field:=1, Criteria1:=¿z¿ï·Ç«h, Operator:=xl_Operator ', VisibleDropDown:=False
    
20.         Else
    
21.             Rng.AutoFilter Field:=1, Criteria1:=¿z¿ï·Ç«h ', VisibleDropDown:=False
    
22.         End If
    
23.         '**  VisibleDropDown:=False :¤£¦b¤u§@ªí¤WÅã¥Ü¦³ [¦Û°Ê¿z¿ï] ¤U©Ô½b¸¹
    
24.         Rng(1).Select
    
25.     End With
    
26. End Sub

½Æ»s¥N½X

msmplay

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


   ÁÂÁ­ã¤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]

msmplay

¦^´_ 6# GBKEE

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

msmplay

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

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

­ã´£³¡ªL

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

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))

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