返回列表 上一主題 發帖

[發問] VBA依條件自動篩選

[發問] VBA依條件自動篩選

本帖最後由 msmplay 於 2016-12-14 20:11 編輯

想請教網大高手們~~~~~~~

請問如何使用VBA達到以下自動篩選功能:
1.按下「逾期數TOP 5」按鈕時,CZ欄可自動篩選出逾期數前5名。例如圖一
2.按下「完成率低於90%」按鈕時,DA欄可自動篩選出完成率低於90%以下。例如圖二
3.按下「作業天數TOP 5」按鈕時,DB欄可自動篩選出作業天數前5名。例如圖三

說明:
1. 第223列工作數總計不列入篩選條件範圍內
2. 第1與第3條件,如果同時有2個以上均有第5名的相同數據時,則均需篩選出來
3. 因為數據會不斷更新,所以以上三個條件數據均可能不同,故無法使用目前的固定數據錄製成巨集



管理總表.rar (60.22 KB)

圖一

圖一.PNG

圖一完成

圖一完成.PNG

圖二

圖二.PNG

圖二完成

圖二完成.PNG

圖三

圖三.PNG

圖三完成

圖三完成.PNG
*宅女一枚無誤*
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

回復 1# msmplay


   
未命名.GIF
2016-12-15 08:21

2003 可用圖示 前10 項 錄製你所需的看看
  1. Operator     選擇性的 XlAutoFilterOperator 資料類型。

  2. XlAutoFilterOperator 可以是這些 XlAutoFilterOperator 常數之一。
  3. xlAnd default
  4. xlBottom10Items           '後面(項目)
  5. xlBottom10Percent         '後面(百分比)
  6. xlOr
  7. xlTop10Items                '前面(項目)   
  8. xlTop10Percent             '前面(百分比)
  9. 可使用 xlAnd 和 xlOr 將 Criteria1 和 Criteria2 建構複合準則。
複製代碼
試試看
  1. Sub 篩選測試()
  2.     With Sheets("工作完成率統計")
  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.         '測試前5項紀錄但會差一項,因  資料的最後是工作數總計列也計算進去了
  10.         ' 所以 Criteria1:="6" ,才會顯示測試前5項紀錄
  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.         ' 工作數總計列也有計算進去但 >90%,會顯示
  16.        .AutoFilterMode = False
  17.     End With
  18. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

回復 2# GBKEE

G大~~~~感謝熱心幫忙ㄛ!不過小妹試了不行ㄟ,按一下任何一個鍵,都會有在出現篩選的動作,但最後又回復到沒有篩選的樣子!你測試可以成功嗎?
*宅女一枚無誤*
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

TOP

回復 3# msmplay

將你3個按鈕的程式放在一起了,你可拆開成你要的3個巨集,
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

TOP

本帖最後由 msmplay 於 2016-12-17 00:40 編輯

回復 4# GBKEE

   原來如此丫~~~~~真是太感謝您了G大!!不過可以再請教一下~~~~就是小妹自己改好之後如附件,但遇到以下問題
1.逾期數會顯示223列總計,完成率、作業天數是否也可以同樣篩選後固定顯示223列總計呢?
2.作業天數篩選後發現同樣TOP10的數字10共有3個,但因為顯示為TOP10,所以並列為第10大的數字10只顯示了2個,列193反而沒辮法顯示(已反紅),請問這是否有其他解決方法呢?


非常感謝~~~~~~~
管理總表.rar (62.15 KB)
*宅女一枚無誤*
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

TOP

回復 5# msmplay
193列時際上是9.5,因你格式上採用數值小數點0位,所以顯示為10
可修改公式=IF(SUM(G193:CX193)=0,"-",ROUND(AVERAGE(G193:CX193),0))
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

TOP

假如第1名有2個, 第2名有3個, 前5大, 應是8個???
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

TOP

回復 7# 准提部林

准大沒錯沒錯!!!
*宅女一枚無誤*
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

TOP

回復 6# GBKEE

原來如此丫!!謝謝你ㄛG大~~~~~
*宅女一枚無誤*
請詳閱 論壇版規愛惜帳號,一起創造美好的學習討論空間。

TOP

  1. Dim FilArea As Range

  2. Sub 逾期數()
  3. Dim R&, xClmn As Range, i&, LG, GG, N%
  4. Call 取消篩選
  5. Set xClmn = FilArea.Columns(103)
  6. R = Application.CountIf(xClmn, ">0")
  7. If R = 0 Then Exit Sub
  8. Sheets("工作完成率統計").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 低於90()
  17. Call 取消篩選
  18. Sheets("工作完成率統計").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 取消篩選
  24. Set xClmn = FilArea.Columns(105)
  25. R = Application.Count(xClmn)
  26. If R = 0 Then Exit Sub
  27. Sheets("工作完成率統計").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 取消篩選()
  36. Dim R&
  37. With Sheets("工作完成率統計")
  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
複製代碼
[attach]26115[/attach]

管理總表_V1.rar (60.33 KB)

EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

        靜思自在 : 能善用時間的人,必能掌握自己努力的方向。
返回列表 上一主題