CLASS 3：基本資料分析任務 開始
3.1 數據篩選和排序
使用VBA篩選和排序Excel資料。
3.2 計算和公式
使用VBA錄製巨集和編輯Excel公式。
3.3 簡單的資料視覺化
創建基本的圖表和圖形。
二、講解：
3.1 資料篩選和排序
先來看看資料篩選，篩選有常使用篩選功能的每個excel大神，一定都很清楚可以區分為進階篩選跟篩選，都很好用，但我們來用用vba來強化這項應用
先來看篩選，也就是autofilter，其實小編也發過類似的文，可以參考看看
從基本的autofilter 這個物件的"方法"，來玩玩吧
先來看看autofilter是啥?可以參圖1，其餘EXCEL怎操作不解釋了，偷個懶。
圖1.篩選圖示
小編引用MSDN，MSDN講了好多阿，小編來個入門版教學一下：
快速錄了一個錄製版巨集，如下：
圖2.資料樣貌
Sub 巨集1()
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$13").AutoFilter Field:=3, Criteria1:="=1", _
Operator:=xlAnd
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$1:$E$13").AutoFilter Field:=5, Criteria1:="=1", _
Operator:=xlAnd
ActiveSheet.ShowAllData
End Sub
節錄一段code來看看
針對工作表中a1:a13 範圍：ActiveSheet.Range("$A$1:$E$13")
篩選第3行：.AutoFilter Field:=3,
圖3.資料行別對照
篩選條件為1：Criteria1:="=1",
篩選方式為：Operator:=xlAnd
這樣執行巨集1結果如下圖：
圖4.巨集1運作
圖4說明：先跑b行，然後復原在跑d行。
取消篩選：ActiveSheet.ShowAllData
執行次行命令後，即恢復篩選前狀態。
以上是篩選的基本入門操作，至於更多的用法，可以參考小編的其他文。
接著來看排序功能，排序會使用到sort這個方法，老樣子先來個msdn一下
到這不免很多看官會說，啥都msdn，那你整理文章做啥，小編是想，你要先知道官方有技術指導，看無官方版不打緊，來看看小編的入門篇之後，普出一點道理之後，再回去看官方的，或許能幫到一點點忙；畢竟講語法實在是太讓人想滑鼠點上一頁閃了。畢竟網路上也很多人寫類似的，選你好看好懂得，up to you。
言歸正傳，啥是排序，排序就是基本排序資料的一個功能，可以參圖5，其餘EXCEL怎操作不解釋了，偷個懶。
圖5.排序按鈕
小編來個sort入門版教學一下，快速錄了一個錄製版巨集，如下：
錄得滿多的，a行的排序，a、b雙行的排序等
某一關鍵參數：大到小，小到大列舉
Sub 巨集2()
'a行的排序 小到大
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Add Key:=Range("B2:B13"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("工作表1").Sort
.SetRange Range("A1:E13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'a行的排序 大到小
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Add Key:=Range("B2:B13"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("工作表1").Sort
.SetRange Range("A1:E13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'a行與b行接排序 大到小
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Add Key:=Range("B2:B13"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Add Key:=Range("C2:C13"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("工作表1").Sort
.SetRange Range("A1:E13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
小編先說哇，錄了好長一串，來慢慢看過來
用錄製取得的CODE明顯好像跟上面提的SORT MSND內容不一樣
小編在補一下應外一塊MSDN，對照範例與小編錄製的內容
小編：
'a行的排序 小到大
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Add Key:=Range("B2:B13"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("工作表1").Sort
.SetRange Range("A1:E13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
微軟MSDN：
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Column1]]"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
其實除了工作表與儲存格範圍外，範例跟錄製的內容真的長的很像ㄝ，但還是有不同，先回到主軸上排序上
回想一下，透過滑鼠點選操作排序時，有幾個必要設定的地方，分別是要排序那些儲存格，根據那些儲存格當依據做排序，先來看當使用Sort.SortFields時，主要必設定的部分：
清除篩選： ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Clear
增加範圍： ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Add
設定排序條件範圍：Key:=Range("B2:B13"), _
設定排序條件範圍參數：SortOn:=xlSortOnValues, Order:=xlAscending,DataOption:=xlSortNormal
相關參數就不多再談了，先拉回到排序sort的重點整理一下:
1.資料範圍
2.資料排序依據
3.排序方法選擇(大到小......)
小編想，可能需要寫一篇專文了，呵呵，這邊還是以引入觀念為主。
不然就都泡在程式碼裏頭，引用當兵學長的話，"歐，強哥，這很難吞下的下去說"。
小編遇到難免會多補充很多，接下小編端上速成版排序做參考:
3原則
1.資料範圍
2.資料排序依據
3.排序方法選擇(大到小......)
單一排序依據:
activesheet.Range(資料範圍).Sort _
Key1:=activesheet.Range(資料範圍排序依據行別), _
Order1:=資料排序方式, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod :=xlStroke, _
DataOption1:=xlSortNormal
2組排序依據:
activesheet.Range(資料範圍).Sort _
Key1:=activesheet.Range(資料範圍排序依據行別1), _
Order1:=資料排序方式1, Header:=xlYes, _
Key2:=activesheet.Range(資料範圍排序依據行別2), _
Order2:=資料排序方式2, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod :=xlStroke, _
DataOption1:=xlSortNormal
對，就是這樣簡單!!!! 怎我前面一堆廢話 (>__<)
排序 演練一下:
Private Sub CommandButton2_Click()
ActiveSheet.Range("b1:e30").Sort _
Key1:=ActiveSheet.Range("b1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod:=xlStroke, _
DataOption1:=xlSortNormal
ActiveSheet.Range("b1:e30").Sort _
Key1:=ActiveSheet.Range("b1"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod:=xlStroke, _
DataOption1:=xlSortNormal
End Sub
Private Sub CommandButton3_Click()
ActiveSheet.Range("b1:e30").Sort _
Key1:=ActiveSheet.Range("b1"), _
Order1:=xlAscending, Header:=xlYes, _
Key2:=ActiveSheet.Range("c1"), _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod:=xlStroke, _
DataOption1:=xlSortNormal
ActiveSheet.Range("b1:e30").Sort _
Key1:=ActiveSheet.Range("b1"), _
Order1:=xlDescending, Header:=xlYes, _
Key2:=ActiveSheet.Range("c1"), _
Order2:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod:=xlStroke, _
DataOption1:=xlSortNormal
End Sub
沒有留言:
張貼留言