River's Footmar And Note

2020年12月28日星期一

VBA：AutoFilter應用：找特定時間區間，股價最高與最低值(YAHOO FINANCE CSV為例)

RANGE有相當多的屬性跟方法，AutoFilter即為RANGE的方法之一，MSDN查詢結果，小小編由YAHOO FINANCE下載了某間公司的過去歷史股價，合計5200多筆收盤價資料，以此案例來實際操作一。範例下載

P.S範例資料有略為簡化。

如果你經常在用YAHOO FINANCE下載資料來用，可以看看怎操作😀

至於批次找出各公司的最高最低價，有空在單獨寫一篇。

一、目的：找出該公司每一年最高與最低股價。

圖1.原始資料

二、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：

圖2.按鈕一測試結果

小小編突發奇想，想要知道股價日期，追加修改了一下：

在作一個ACTIVEX按鈕插入以下VBA：

圖3.按鈕二測試結果

2020年12月27日星期日

IPO：寫VBA時的基本流程

何謂IPO參閱

小編滿常寫VBA的，不論是外包或是心血來潮寫自己東西的時候。
曾有個學生問："你怎馬上知道這問題怎做？"、"你怎思考的?"
這問題小小編的我也想了很久，回歸到基本原則就是流程化拆解，小編本科是念I.E的，寫程式完全是半路出家，單純自我實現，然後不知不覺越寫越多，在這自我實現中，以流程化拆解，來觀察問題，進而解決問題小小編自覺是最基本的原則。

以VBA來說，不外乎資料來源、怎處理、預計結果等3原則來拆解一個VBA的問題。
小小編簡單說明一下，來引導思考：

1.資料來源：今天要整理一份資料，資料在那一頁工作表？在多少頁工作表？在多少儲存格中？在多少檔案中？在多少電腦的分享資料夾中；這樣來想像一下不管是整理資料，還是要做簡單計算，"資料來源"在那，怎掌握怎取得，是第一個思考點。

2.怎處理：有資料了，怎處理!然後可以變成預期的結果，最好的情況是EXCEL內建函數就可以算最好了，若不是則要自己寫函數或是公式來計算；或是你的處理是要把相同資料整理在一起；講白話一點，就是你打算對資料做那些加更工拉，這加工方式能否以EXCEL內建功能完成，不然就要自己想方法。

3.預計結果：
這邊說的預計結果跟怎處理，似乎有點類似，但小小編要說的是預計結果，分成2塊；3.1資料處理完輸出為下一個處理的預計結果，直到所有處理都完成才結束。3.2有指定表單做結果輸出的部分，需要把預計結果寫到表單上；或是前者則與2.怎處理有關聯。反之就是做最後的表單輸出。

VBA是微軟在OFFICE產品線中，內建的一個巨集的功能，透過巨集功能錄製的操作，則是以VBA方式記錄成巨集，讓使用者可以實現單一作業重複，但錄製功能卻十分十分十分地受限；更早之前在VB的時代中，微軟把物件寫好提供給各開發者，能夠直接使用，讓開發人員能快速完成視窗工具的開發，但也因為這樣開發也常常因為物件而受限(能力超強的軟體工程師會自己寫物件的除外XD)；回到VBA，雖然錄製功能十分受限，但其實VBA很早就內建在OFFICE的軟體中，不論WORD、EXCEL、POWERPOINT、OUTLOOK，直到最新的OFFICE都還是有他的影子歐，那VBA到底是????

整理一下：

簡單來說就是Visual basic(簡稱VB)的延伸語言，在最大差異是VB的程式語言的產出，通常有經過"編譯"所以可以單獨變成安裝程式，並可安裝於WINDOWS平台中，但VBA僅能依附在OFFICE的檔案中(如*.xlsm、*.doc)。但!寫程式的架構與邏輯可一點也沒特別被簡化歐!!!

二、為何要學VBA？

首先我必須說，這問題跟，"為何要學錄製巨集是完全不同層次的問題"，要先區分開來；要透過VB或C#畫出一張超美且有標題、例圖的折線圖或直方圖或任何圖形，簡直是要累死工程師生命，不不不，做更正，應該說如果有更簡便的資料整理工具(先不管你OFFICE是教育版、刷卡買的、還是點BT得到的)，OFFICE工具應該算是最普羅大眾且算接受度最高的整理資料工具吧(當然微軟後面還有出像是POWER BI的工具) ，且也開始有一部分主流ERP廠商，甚至將VBS語法內建，透過匯出的資料集中，透過命名為VBS檔案方式，讓使用者透過單機去自行生成資料，來降低erp系統的負載，也更有一部分廠家如XXXCAD，也有內建VBA搂；有點離題了，小小編在這將VBA學習，區為幾個淺在問題面向來剖析：

1.在資料作業整理中，具備高度作業重複的作業的需要

2.無法從0開始學習也無這方面基礎，但又有大量資料運算與分析的需要。

備註：很多人都是透過EXCEL來分析製程(Ca、Cpk、Spc)、整理股票或對帳等的應用，小小編是認為都算大量資料。

3.老闆不願意花錢找人寫客製軟體或雇用相關專業人員(這是出社會的感觸)

以上3點具備任何一點，小小編建議，在不讓自己雙手累死，以及眼睛度數逐年增加的考量下，若學習意志足夠，不妨花點時間來學習，既可以幫助自己工作效率提升學到某程度真有興趣，其實整理資料也可以是打在另外一扇門經營自己的方向歐。

p.s具備以上3點任2點很多做不下去的人，更多人選寫履歷 xd

三、最後：學寫程式的差異在那?

小小編以幾個切入點分享：

1.嚴謹度：對初學的小白來說，不需要像是C這類語言，變數都要宣告才能用或是要做那些運算要掛載啥元件的!免；VBA這部分嚴謹度不高，但反過來想，你要先宣告變數先掛載元件，也是可以的歐；所以相對來說比較算比較寬鬆拉，但這也是缺點，就不知不覺變數越用越多了，消耗記憶體跟拖慢運算速度搂(但絕對比手快)。

2.輔助：OFFICE內建相當多的功能，這些功能，全都可以透過vba直接使用，例如算迴歸方程式、算加總....做篩選等，實在太多，但你要用程式語言做?花2~3萬元去學python搞不好你連迴圈都還搞不清楚就下課了(強調沒有看不起人歐，因為python是免費的開發軟件；更白話的是，沒人分享案例或功能，你就要都自己寫自己要的功能)。

3.資源：因為內建在OFFICE內，這表示你可以透過微軟線上(MSDN)，查到一堆資料，更甚至如果學習過程中對物件熟悉了，可以透過物件使直接查詢網路資源，找類似案例應用做學習。