ªð¦^¦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»~*

¦^´_ 1# msmplay


   
¥¼©R¦W.GIF
2016-12-15 08:21

2003 ¥i¥Î¹Ï¥Ü «e10 ¶µ ¿ý»s§A©Ò»Ýªº¬Ý¬Ý
  1. Operator     ¿ï¾Ü©Êªº XlAutoFilterOperator ¸ê®ÆÃþ«¬¡C

  2. XlAutoFilterOperator ¥i¥H¬O³o¨Ç XlAutoFilterOperator ±`¼Æ¤§¤@¡C
  3. xlAnd default
  4. xlBottom10Items           '«á­±(¶µ¥Ø)
  5. xlBottom10Percent         '«á­±(¦Ê¤À¤ñ)
  6. xlOr
  7. xlTop10Items                '«e­±(¶µ¥Ø)   
  8. xlTop10Percent             '«e­±(¦Ê¤À¤ñ)
  9. ¥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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¦^´_ 3# msmplay

±N§A3­Ó«ö¶sªºµ{¦¡©ñ¦b¤@°_¤F,§A¥i©î¶}¦¨§A­nªº3­Ó¥¨¶°,
·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

¦^´_ 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

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

TOP

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

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

TOP

¦^´_ 6# GBKEE

­ì¨Ó¦p¦¹¤X¡I¡IÁÂÁ§A£¬G¤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

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD