格式 AutoFilter 方法第 8 章排序與篩選資料解說參數 Field 要執行自動篩選功能可使用 Range 的 AutoFilter 方法, 並以參數指定篩選條件指定 Range 以編號指定作為條件的欄位從列表最左側的欄位開始, 依序以的順序指定 Criteria1 指

Size: px

Start display at page:

Download "格式 AutoFilter 方法第 8 章排序與篩選資料解說參數 Field 要執行自動篩選功能可使用 Range 的 AutoFilter 方法, 並以參數指定篩選條件指定 Range 以編號指定作為條件的欄位從列表最左側的欄位開始, 依序以的順序指定 Criteria1 指"

梨杜
6 years ago
Views:

AutoFilter Field:=3, Criteria1:="T-001" ❶ 參照儲存格 A3 執行自動篩選功能篩選條件設定為左側數來第三欄為 T-001 Memo 使用自動篩選功能這次篩選的是商品編號為

1 U n i t 篩選資料一定要記住的關鍵字 AutoFilter 方法 AdvancedFilter 方法 ListObject 要從列表格式的資料篩選出符合條件的資料時, 可使用自動篩選功能或是進階篩選功能 VBA 也能利用 AutoFilter 方法或 AdvancedFilter 方法完成相同的處理 1 使用自動篩選功能篩選資料以自動篩選功能篩選資料 Sub 篩選資料 () Range ("A3").AutoFilter Field:=3, Criteria1:="T-001" ❶ 參照儲存格 A3 執行自動篩選功能篩選條件設定為左側數來第三欄為 T-001 Memo 使用自動篩選功能這次篩選的是商品編號為 T-001 的資料在 Excel 的環境下, 要快速從列表格式的資料篩選出需要的資料時, 可使用篩選功能 VBA 則可使用 Range 的 AutoFilter 方法完成同樣的目的 ❶ 執行巨集後 ❷ 以自動篩選功能篩選出商品編號為 T-001 的資料

2 格式 AutoFilter 方法第 8 章排序與篩選資料解說參數 Field 要執行自動篩選功能可使用 Range 的 AutoFilter 方法, 並以參數指定篩選條件指定 Range 以編號指定作為條件的欄位從列表最左側的欄位開始, 依序以的順序指定 Criteria1 指定篩選條件範例 " 商品 A" 商品 A "* 商品 A*" 包含商品 A " 商品 A*" 以商品 A 為開頭 " < > 商品 A" 商品 A 以外 " < >* 商品 A*" 不包含商品 A "=3" 等於 3 ">3" 大於 3 Operator 範例 ">=3" 大於等於 3 " < 3" 小於 3 " < =3" 小於等於 3 " < >3" 除了 3 之外 "" 空白儲存格 " < >" 非空白的儲存格從下列指定篩選條件的指定方法例如, 希望將篩選條件指定為 "10", 並且希望是從前面數來的 10% 時, 可將此參數指定為 xltop10percent xland 以 AND 條件指定篩選條件 1 與篩選條件 2 xlbottom10items 顯示到從下方數來第筆 ( 由篩選條件 1 指定的數值 ) 資料 xlbottom10percent 顯示到從下方數來前 % ( 由篩選條件 1 指定的數值 ) 資料 xlor 以 OR 條件指定篩選條件 1 與篩選條件 2 xltop10items 顯示到從上方數來第筆 ( 由篩選條件 1 指定的數值 ) 資料 xltop10percent 顯示到從上方數來前 % ( 由篩選條件 1 指定的數值 ) 資料 xlfiltercellcolor 指定儲存格的顏色 xlfilterdynamic 指定動態篩選 xlfilterfontcolor 指定字型的顏色 xlfiltericon 指定篩選圖示 xlfiltervalues 指定篩選值 Criteria2 指定第二個篩選條件這個參數能與篩選條件的指定方法搭配使用可於利用 AND 條件以及 OR 條件指定多項條件時使用 VisibleDropDown 若希望顯示下拉式按鈕則設定為 True, 希望隱藏則指定為 False Step up 指定以上以下這種篩選條件要指定兩個篩選條件可將條件指定給 Criteria1 與 Criteria2, 此外也可利用 AND 條件或 OR 條件指定舉例來說, 若要從左側數來第一個欄位篩選出大於等於 1003 且小於等於 1007 的資料, 可將程式碼寫成 Range ("A3").AutoFilter Field:=1, Criteria1:=">=1003", Operator:=xlAnd, Criteria2:=" < =1007" 8-15

Hint 關閉自動篩選的設定使用 Range 的 AutoFilter 方法之後, 就能切換是否執行自動篩選功能

關閉自動篩選功能 Sub 解除篩選 () If ActiveSheet.AutoFilterMode = True Then Range ("A3").

3 Hint 關閉自動篩選的設定使用 Range 的 AutoFilter 方法之後, 就能切換是否執行自動篩選功能下面的範例會偵測自動篩選功能的篩選模式是否啟動, 並在啟動時才切換自動篩選的設定有關條件判斷的方法請參考 Unit 57 ❶ 自動篩選功能啟用時 ❷ 關閉自動篩選功能 Sub 解除篩選 () If ActiveSheet.AutoFilterMode = True Then Range ("A3").AutoFilter End If 2 利用進階篩選功能篩選資料篩選出符合條件的資料 Sub 進階篩選的設定 () Range ("A5").CurrentRegion.AdvancedFilter _ Action:=xlFilterInPlace, _ criteriarange:=range ("A2:G3"), unique:=false ❶ 利用進階篩選功能從包含儲存格 A5 的啟用中儲存格範圍篩選出資料篩選條件指定為儲存格 A2:G3 ❶ 指定搜尋條件之後 8-16

第 8 章排序與篩選資料 ❷ 篩選出與搜尋條件 ( 範例設定的條件是商品編號為 S-001 且數量大於等於 2) 一致的資料使用進階 Memo 篩選功能這次要根據儲存格 A2:G3 的篩選條件篩選資料若是在 Excel 環境底下, 要從列表資料篩選出符合條件的資料時, 可仿照下列 Hint 的說明使用進階篩選功能要利用 VBA 設定進階篩選功能時,

在列表內顯示篩選結果 CriteriaRange 指定輸入篩選條件的範圍 CopyToRange 若篩選結果的顯示方式設定為 xlfiltercopy, 即可透過此參數設定篩選結果的顯示位置 Unique 若要忽略重複的篩選結果則設定為 True, 若要顯示重複的篩選結果則設定為 False Hint 使用進階篩選功能在時的操作 Action 要在 Excel

4 第 8 章排序與篩選資料 ❷ 篩選出與搜尋條件 ( 範例設定的條件是商品編號為 S-001 且數量大於等於 2) 一致的資料使用進階 Memo 篩選功能這次要根據儲存格 A2:G3 的篩選條件篩選資料若是在 Excel 環境底下, 要從列表資料篩選出符合條件的資料時, 可仿照下列 Hint 的說明使用進階篩選功能要利用 VBA 設定進階篩選功能時, 可使用 Range 的 AdvancedFilter 方法格式 AdvancedFilter 方法解說要使用進階篩選功能可使用 AdvancedFilter 方法參數可設定篩選條件的範圍指定 Range 參數 Action 搜尋結果是在其他位置顯示或是在列表的位置顯示 xlfiltercopy 將篩選結果複製到另外的位置 xlfilterinplace 在列表內顯示篩選結果 CriteriaRange 指定輸入篩選條件的範圍 CopyToRange 若篩選結果的顯示方式設定為 xlfiltercopy, 即可透過此參數設定篩選結果的顯示位置 Unique 若要忽略重複的篩選結果則設定為 True, 若要顯示重複的篩選結果則設定為 False Hint 使用進階篩選功能在時的操作 Action 要在 Excel 環境底下從列表資料篩選出符合多項條件的資料時, 可於進階篩選交談窗設定條件要使用進階篩選功能可先在不同於列表的位置撰寫篩選條件, 接著點選要篩選資料的列表的儲存格, 再按下資料頁次的進階, 然後在進階篩選交談窗設定篩選條件此交談窗的選項與 Range 的 AdvancedFilter 方法能指定的參數對應 CriteriaRange CopyToRange Unique 8-17

Hint 解除進階篩選的設定要解除進階篩選的設定, 顯示所有資料, 可使用 Worksheet 的 ShowAllData 方法若要在套用篩選功能時解除篩選, 必須先判斷篩選模式再執行處理有關條件判斷的方法請參考 Unit 57 的說明 ❶ 判斷是否套用了篩選功能, 然後只在套用時解除進階篩選的設定, 顯示所有資料 Sub

ShowAllData 3 將儲存格範圍轉換為表格將儲存格範圍轉換為表格 Sub 轉換成表格 () 根據包含儲存格 A3 的作用中儲存格範圍建立資料, 再撰寫與該表格有關的處理 With ActiveSheet.ListObjects.

5 Hint 解除進階篩選的設定要解除進階篩選的設定, 顯示所有資料, 可使用 Worksheet 的 ShowAllData 方法若要在套用篩選功能時解除篩選, 必須先判斷篩選模式再執行處理有關條件判斷的方法請參考 Unit 57 的說明 ❶ 判斷是否套用了篩選功能, 然後只在套用時解除進階篩選的設定, 顯示所有資料 Sub 解除篩選設定 () If ActiveSheet.FilterMode = True Then End If ActiveSheet.ShowAllData 3 將儲存格範圍轉換為表格將儲存格範圍轉換為表格 Sub 轉換成表格 () 根據包含儲存格 A3 的作用中儲存格範圍建立資料, 再撰寫與該表格有關的處理 With ActiveSheet.ListObjects.Add (SourceType:=xlSrcRange, _ Source:=Range ("A3").CurrentRegion).TableStyle = "TableStylemedium7".Name = " 業績清單 " End With ❶ 將表格的樣式設定為表格樣式 ( 中等深淺 7) ❷ 將表格名稱設定為業績清單 Memo 將儲存格範圍轉換成表格接下來要將包含儲存格 A3 的作用中範圍轉換成表格代表表格的是 ListObject, 而我們要使用 ListObject 的集合, 也就是 ListObjects 集合的 Add 方法增加表格 ❶ 將包含儲存格 A3 的作用中儲存格範圍 ❷ 轉換成表格 8-18

6 格式 Add 方法第 8 章排序與篩選資料解說建立新的表格指定 ListObjects 集合參數 SourceType 指定原始資料的種類 xlsrcexternal 外部資料 (Microsoft SharePoint Foundation 網站 ) xlsrcmodel PointPivot 模組 xlsrcquery 查詢佇列 xlsrcrange 儲存格範圍 ( 預設值 ) xlsrcxml XML Source 指定原始資料若 SourceType 參數為 xlsrcrange 可省略指定此參數 LinkSource 指定是否讓外部資料與 ListObject 連結若 SourceType 為 xlsrcrange, 此參數就無效 XlListObjectHasHeaders 指定首列是否有欄標籤 xlguess 自動判斷是否有欄標籤 xlno 無 ( 預設值 ) xlyes 有 Destination 指定建立列表的位置若 SourceType 為 xlsrcrange 時則忽略此參數 TableStyleName 指定表格套用的樣式 Hint 將表格還原為原本的儲存格範圍要將表格還原為原本的儲存格範圍可使用 ListObject 的 Unlist 方法例如要將業績清單表格還原為儲存格範圍可將程式碼寫成下列 Sub 將表格轉換成儲存格範圍 () With ActiveSheet.ListObjects (" 業績清單 ").TableStyle = "".ShowTotals = False.Unlist End With Keyword ListObjects 集合 ListObject 集合就是代表表格的 ListObject 的集合體, 可利用 Worksheet 的 ListObjects 屬性取得 8-19

4 篩選出表格的資料從表格篩選資料在此撰寫與業績清單表格有關的處理 Sub 篩選表格的資料 () ❶ 篩選出表格範圍左側數來第三欄為 With ActiveSheet.ListObjects (" 業績清單 ") S-001 的資料.Range.AutoFilter Field:=3, Criteria1:="S-001".ShowTotals = True ❷ 顯示統計列.

TotalsCalculation = xltotalscalculationsum End With ❸ 加總數量欄的統計方法 Memo 篩選資料要篩選出表格的資料可使用 ListObject 的屬性與方法在此指定了篩選條件也顯示了統計列 ❶ 從表格資料 Hint AutoFilter 方法要從表格篩選出資料可使用 ListObject 的 Range 屬性取得列表範圍的 Range,

7 4 篩選出表格的資料從表格篩選資料在此撰寫與業績清單表格有關的處理 Sub 篩選表格的資料 () ❶ 篩選出表格範圍左側數來第三欄為 With ActiveSheet.ListObjects (" 業績清單 ") S-001 的資料.Range.AutoFilter Field:=3, Criteria1:="S-001".ShowTotals = True ❷ 顯示統計列.ListColumns (" 數量 ").TotalsCalculation = xltotalscalculationsum End With ❸ 加總數量欄的統計方法 Memo 篩選資料要篩選出表格的資料可使用 ListObject 的屬性與方法在此指定了篩選條件也顯示了統計列 ❶ 從表格資料 Hint AutoFilter 方法要從表格篩選出資料可使用 ListObject 的 Range 屬性取得列表範圍的 Range, 再利用 AutoFilter 方法篩選資料 ❷ 篩選出商品編號為 S-001 的資料 ❸ 顯示統計列, 並顯示數值的總和格式 TotalsCalculation 屬性解說指定表格欄位的統計列的計算方式 xltotalscalculationnone 不計算 xltotalscalculationmin 最小值 xltotalscalculationaverage 平均值 xltotalscalculationsum 總和 xltotalscalculationcount 資料筆數 xltotalscalculationstddev 標準差 xltotalscalculationcountnums 數值個數 xltotalscalculationvar 變異數 xltotalscalculationmax 最大值 xltotalscalculationcustom 其他函數指定 ListColumn 8-20

840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00

Excel - - Excel - -4-5 840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00 ( 0 ) 智慧標籤相關說明提示 -5 -- Excel 4 5 6 7 8 + - * / % ^ = < >= & 9 0 (:) (,) ( ) Chapter - :,

格式 AutoFilter 方法 第 8 章排序與篩選資料 解說參數 Field 要執行自動篩選功能可使用 Range 的 AutoFilter 方法, 並以參數指定篩選條件 指定 Range 以編號指定作為條件的欄位 從列表最左側的欄位開始, 依序以 的順序指定 Criteria1 指

格式 AutoFilter 方法第 8 章排序與篩選資料解說參數 Field 要執行自動篩選功能可使用 Range 的 AutoFilter 方法, 並以參數指定篩選條件指定 Range 以編號指定作為條件的欄位從列表最左側的欄位開始, 依序以的順序指定 Criteria1 指