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

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

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

¥»©«³Ì«á¥Ñ msmplay ©ó 2016-12-14 20:11 ½s¿è

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

¹Ï¤@

¹Ï¤@.PNG

¹Ï¤@§¹¦¨

¹Ï¤@§¹¦¨.PNG

¹Ï¤G

¹Ï¤G.PNG

¹Ï¤G§¹¦¨

¹Ï¤G§¹¦¨.PNG

¹Ï¤T

¹Ï¤T.PNG

¹Ï¤T§¹¦¨

¹Ï¤T§¹¦¨.PNG
*¦v¤k¤@ªTµL»~*

¦^´_ 12# GBKEE


   ÁÂÁÂG¤j¼ö¤ßÀ°¦££°~~~~~~~~~~
*¦v¤k¤@ªTµL»~*

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-12-18 08:11 ½s¿è

¦^´_ 11# msmplay


  
¥¼©R¦W.JPG
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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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


   ÁÂÁ­ã¤j~~~~~~~~~~~~~~~~
*¦v¤k¤@ªTµL»~*

TOP

  1. Dim FilArea As Range

  2. Sub ¹O´Á¼Æ()
  3. Dim R&, xClmn As Range, i&, LG, GG, N%
  4. Call ¨ú®ø¿z¿ï
  5. Set xClmn = FilArea.Columns(103)
  6. R = Application.CountIf(xClmn, ">0")
  7. If R = 0 Then Exit Sub
  8. Sheets("¤u§@§¹¦¨²v²Î­p").AutoFilterMode = False
  9. For i = 1 To R
  10.     LG = Application.Large(xClmn, i)
  11.     If GG <> LG Then N = N + 1: GG = LG
  12.     If N = 5 Then Exit For
  13. Next
  14. FilArea.AutoFilter Field:=103, Criteria1:=">=" & GG
  15. End Sub

  16. Sub §C©ó90()
  17. Call ¨ú®ø¿z¿ï
  18. Sheets("¤u§@§¹¦¨²v²Î­p").AutoFilterMode = False
  19. FilArea.AutoFilter Field:=104, Criteria1:="<90%"
  20. End Sub

  21. Sub §@·~¤Ñ¼Æ()
  22. Dim R&, xClmn As Range, i&, LG, GG, N%
  23. Call ¨ú®ø¿z¿ï
  24. Set xClmn = FilArea.Columns(105)
  25. R = Application.Count(xClmn)
  26. If R = 0 Then Exit Sub
  27. Sheets("¤u§@§¹¦¨²v²Î­p").AutoFilterMode = False
  28. For i = 1 To R
  29.     LG = Application.Large(xClmn, i)
  30.     If GG <> LG Then N = N + 1: GG = LG
  31.     If N = 5 Then Exit For
  32. Next
  33. FilArea.AutoFilter Field:=105, Criteria1:=">=" & GG
  34. End Sub

  35. Sub ¨ú®ø¿z¿ï()
  36. Dim R&
  37. With Sheets("¤u§@§¹¦¨²v²Î­p")
  38.      .AutoFilterMode = False
  39.      R = .UsedRange.Rows.Count - 1
  40.      If R <= 4 Then Exit Sub
  41.      Set FilArea = .Range("B3:DB" & R)
  42.      FilArea.AutoFilter
  43. End With
  44. End Sub
½Æ»s¥N½X
[attach]26115[/attach]

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

TOP

¦^´_ 6# GBKEE

­ì¨Ó¦p¦¹¤X¡I¡IÁÂÁ§A£¬G¤j~~~~~
*¦v¤k¤@ªTµL»~*

TOP

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

­ã¤j¨S¿ù¨S¿ù¡I¡I¡I
*¦v¤k¤@ªTµL»~*

TOP

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

TOP

¦^´_ 5# msmplay
193¦C®É»Ú¤W¬O9.5,¦]§A®æ¦¡¤W±Ä¥Î¼Æ­È¤p¼ÆÂI0¦ì,©Ò¥HÅã¥Ü¬°10
¥i­×§ï¤½¦¡=IF(SUM(G193:CX193)=0,"-",ROUND(AVERAGE(G193:CX193),0))
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ msmplay ©ó 2016-12-17 00:40 ½s¿è

¦^´_ 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)
*¦v¤k¤@ªTµL»~*

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD