RANGE有相當多的屬性跟方法，AutoFilter即為RANGE的方法之一，MSDN查詢結果，小小編由YAHOO FINANCE下載了某間公司的過去歷史股價，合計5200多筆收盤價資料，以此案例來實際操作一。範例下載
P.S範例資料有略為簡化。
如果你經常在用YAHOO FINANCE下載資料來用，可以看看怎操作😀
至於批次找出各公司的最高最低價，有空在單獨寫一篇。
一、目的：找出該公司每一年最高與最低股價。
二、IPO發想與評估：
每一年，所以有期間限制、最高最低價內建函數可以解決、是否要使用陣列的方式堆壘資料
2.1 IPO拆解步驟：
A.資料來源：在A到F行，A行為日期，B到E行為股價資料。
B.怎處理：
1.觀察資料與問題：
(1)資料為連續的，沒有空白，有完整日期標示可以免除資料前處理。
(2)現況資料有幾年?怎知道是那一年開始的？
(3)找出最大與最小分別可以透過MAX與MIN等內建函數完成。
(4)透過內建函數MAX與MIN，但對應的範圍怎設定才能是特定期間？
2.滿足問題：
(1)有幾年這部分，因為資料是連續的，可以透過取的最後一筆資料跟第一筆資料來判斷需要執行幾年的期間，來設計迴圈。
(2)內建函數範圍設定這部分，回想到VBA：Cells.SpecialCells 簡單應用+ 資料排序一文，透過設定為xlCellTypeConstants方式來取的位置即可解決。
C.輸出 ：
把年份找出來後，新增一頁工作表名為OUT，做各年份最高最低股價結果顯示與儲存用。
2.2 評估：
每一年，所以有期間限制：OK
最高最低價內建函數可以解決：OK
是否要使用陣列的方式堆壘資料：股價不用天天整理最高最低，所以免用陣列。
三、動作寫：
AutoFilter 簡單說明：
Sheets("還原股價").Range("$A$1:$f$" & END_ROW).AutoFilter Field:=1, Criteria1:= _
">=" & year_begin & "/1/1", Operator:=xlAnd, Criteria2:="<=" & year_begin & "/12/31"
Field：設定篩選的條件是第幾行。
Criteria1、Criteria1：因為是區間，所以要設定2個條件。
Operator：篩選類型設定，因為有兩個條件所以設為xlAnd。
year_begin：是透過迴圈控制的篩選變數，配合 "/1/1"與"/12/31" 組合成年頭與年末的篩選條件。
先作一個ACTIVEX按鈕插入以下VBA：
| Private Sub CommandButton1_Click() | |
| Sheets("OUT").Cells.Clear | |
| Sheets("還原股價").Activate | |
| END_ROW = Sheets("還原股價").Range("a" & 10000).End(xlUp).Row | |
| end_year = Split(Sheets("還原股價").Range("a" & END_ROW).Value, "/") | |
| NOW_YEAR = Split(Sheets("還原股價").Range("a" & 2).Value, "/") | |
| NOW_YEAR = NOW_YEAR(0) | |
| fh = 2 '開始位置 | |
| Sheets("OUT").Range("A1:C1") = Array("股價(年)", "最高", "最低") | |
| For year_begin = NOW_YEAR To end_year(0) Step -1 | |
| Sheets("還原股價").Range("$A$1:$f$" & END_ROW).AutoFilter Field:=1, Criteria1:= _ | |
| ">=" & year_begin & "/1/1", Operator:=xlAnd, Criteria2:="<=" & year_begin & "/12/31" | |
| Set A = ActiveSheet.Range("b:e").SpecialCells(xlCellTypeVisible) | |
| Max = Application.Max(ActiveSheet.Range(A.Address)) | |
| Min = Application.Min(ActiveSheet.Range(A.Address)) | |
| Sheets("OUT").Range("A" & fh) = year_begin | |
| Sheets("OUT").Range("B" & fh) = Max | |
| Sheets("OUT").Range("C" & fh) = Min | |
| fh = fh + 1 | |
| Next year_begin | |
| Sheets("還原股價").AutoFilterMode = False | |
| End Sub |
小小編突發奇想，想要知道股價日期，追加修改了一下：
在作一個ACTIVEX按鈕插入以下VBA：
| Private Sub CommandButton2_Click() | |
| Sheets("OUT").Cells.Clear | |
| Sheets("還原股價").Activate | |
| END_ROW = Sheets("還原股價").Range("a" & 10000).End(xlUp).Row | |
| end_year = Split(Sheets("還原股價").Range("a" & END_ROW).Value, "/") | |
| NOW_YEAR = Split(Sheets("還原股價").Range("a" & 2).Value, "/") | |
| NOW_YEAR = NOW_YEAR(0) | |
| fh = 2 '開始位置 | |
| Sheets("OUT").Range("A1:E1") = Array("股價(年)", "最高", "最低", "最高日期", "最低日期") | |
| For year_begin = NOW_YEAR To end_year(0) Step -1 | |
| ActiveSheet.Range("$A$1:$f$" & END_ROW).AutoFilter Field:=1, Criteria1:= _ | |
| ">=" & year_begin & "/1/1", Operator:=xlAnd, Criteria2:="<=" & year_begin & "/12/31" | |
| Set A = ActiveSheet.Range("b:e").SpecialCells(xlCellTypeVisible) | |
| Max = Application.Max(ActiveSheet.Range(A.Address)) | |
| Min = Application.Min(ActiveSheet.Range(A.Address)) | |
| Sheets("OUT").Range("A" & fh) = year_begin | |
| Sheets("OUT").Range("B" & fh) = Max | |
| Sheets("OUT").Range("C" & fh) = Min | |
| Set A_FIND = A.Find(WHAT:=Max) | |
| If A_FIND Is Nothing Then | |
| Else | |
| Sheets("OUT").Range("D" & fh) = ActiveSheet.Range("A" & A_FIND.Row).Value | |
| End If | |
| Set A_FIND = A.Find(WHAT:=Min) | |
| If A_FIND Is Nothing Then | |
| Else | |
| Sheets("OUT").Range("E" & fh) = ActiveSheet.Range("A" & A_FIND.Row).Value | |
| End If | |
| fh = fh + 1 | |
| Next year_begin | |
| Sheets("還原股價").AutoFilterMode = False | |
| End Sub |
沒有留言:
張貼留言