VBA：營收資料分析 (一)

筆者剛接觸股票時，頭一件是就是掌握營收狀態，營收狀態最重要得是"營收年增率"，但又不可能一間公司一間公司看，所以開始思考寫VBA自動分析。

分析的方式很簡單，就是最近一個月不得為負數、過去3個月不得為負數與過去6個月不得為負數，這樣得概念來做分析。

筆者直接利用EXCEL內建函數SUMPRODUCT來處理這部份分析。

先來測試一下函數。

以下資料為例：



在H1儲存格，寫入=IF(SUMPRODUCT(--(F9:F14>=0))=6,TRUE,FALSE) 函數，來判斷是否6個月得"年增率"都大於0，成立則顯示TRUE反之為 FALSE，以此類推。

H2：=IF(SUMPRODUCT(--(F9:F11>=0))=3,TRUE,FALSE)、判斷3個月

H3：=IF(SUMPRODUCT(--(F9>=0))=1,TRUE,FALSE)，判斷最近1個月

結果：皆為FALSE，應用SUMPRODUCT函數沒問題了。

image

接下來思考整體流程該怎做：下一篇

VBA：西元年與民國年的互動

 RANGE.NumberFormatLocal 這個屬性可以做時間資料格式設定，若沒有使用NumberFormatLocal 屬性，也可以直接透過FORMAT函數，直接針對資料做設定；EX:FORMAT(NOW(),"YYYY/MM/DD")

小編簡單作兩個按鈕一個是切換年月日，另外一個是切換年月日時分。

圖1.

圖2.

VBA：資料拆解少不了的SPLIT 函數

這算是滿常用到的函數，做簡單分享：

 SPLIT，資料分割教學：

一、兩個小例子：

日期：109/12/08，透過B2=SPLIT("109/12/08","/")，分割出來的陣列長這樣。

圖1.

關鍵字：DATA:2020/12/08，透過B3=SPLIT("DATA:2020/12/08","DATA:")，分割出來的陣列長這樣

圖2.

分割完出來的資料就可以重新"再加工"做任何編輯了，例如B2(0)為存放年的資料，加上1911就等於西元年了，諸如此類的資料整理。

二、延伸：

若要避免錯誤的用法可以參考組合INSTR函數

例如 ：B2="1091208"

IF INSTR(B2,"/")>0 THNE

        B2=SPLIT(B2,"/")

END IF

WHY?WHY?WHY?

因為分割資料時，當未含有指定分割字元時，以B2=SPLIT("1091208","/")與B3=SPLIT("2020/12/08","DATA:")為例則變成這樣：

圖3.

三、再延伸一下:VBA 營收資料分析(二)

當中有一行是處理WEB的回傳結果。

     webdata = Split(web.responseText, vbLf)

分割前，透過即時運算視窗大概是這樣內容:

    

圖4.

分割後，如圖5.在區域變數視窗中，以陣列方式呈現，就可以做很多有趣的處理。

(VBA 營收資料分析(二))

圖5.

以上簡單分享與參考。

VBA：SUMPRODUCT 的美與好

 不知道大家有無處理大量資料的痛，尤其是使用SUMPRODUCT的時候，當有很多儲存格都使用時，開始維護新資料就會有明顯卡頓，小編簡單舉例自己同事，維護的一份簡單進銷存資料從剛開始的1000筆成長到目前的8萬多筆，然後有80個SUMPRODUCT，等於每打一筆資料EXCEL要重新計算64萬次(我可愛的同事都等到要砸鍋了)，當然，這部分也是可以透過關閉EXCEL內部的自動計算功能，來避免這個問題發生，但關了經常忘了在重新打開~~這又是另外一個問題了。

先來簡單幫SUMPRODUCT做介紹，使用前若是可以，可以針對資料所在行別，透過"名稱管理員"或是"定義名稱"做定義，這樣就省去每次資料有異動拉儲存格的動作。

接著開始說明SUMPRODUCT；

主要語法結構大致上是這樣：

SUMPRODUCT(資料1=判斷1,資料2=判斷2,資料N=判斷N)，

多條件時，透過*號將條件串在一起(資料1=判斷1)*(資料2=判斷2)；白話一點意思就是當資料1滿足判斷1並且(AND的意思)資料2也等於判斷2時，才算成立，等等歐這樣還不夠!!((資料1=判斷1)*(資料2=判斷2),資料1)，小編在這補上資料1表示當條件成立時加總資料1的資料；延伸一下，((資料1=判斷1)*1*(資料2=判斷2))這樣勒!!差在那，會變成計算資料1的個數歐，大概簡單說到這裡。

為了改進SUMPRODUCT的運算缺點，小編配合VBA語法，以儲存格物件的Formula屬性結合SUMPRODUCT語法做一點小改善，所以小編做了一個按鈕，把SUMPRODUCT用公式方式寫入Formula屬性中。

範例：

圖1.原始資料

圖2.SUMPRODUCT彙整結果頁面

VBA CODE：

簡單說明：雙引號間的資料透過&符號串在一起，然後透過等號寫入Formula ；紅色字體的d為變數，透過d = "C" & I，I為迴圈的步進值，用賴配合"C"，做變數的設定，例如"C" & 1則變數d為"C1",以此類推"C" & 10，則為"C10"。在變數d=C10情況下，"= SUMPRODUCT((Item = " & d & ") * (日期 >= A2) * (日期 <= B2),入庫)" 則在Formula 會成為SUMPRODUCT((Item = C10) * (日期 >= A2) * (日期 <= B2),入庫)這樣!

Sheet1.Range("D" & I).Formula = "= SUMPRODUCT((Item = " & d & ") * (日期 >= A2) * (日期 <= B2),入庫)" 

步驟：

1.先定義"名稱"

2.寫一個函數版的SUMPRODUCT，測試OK後，在參考小編的CODE，做一個按鈕，貼上CODE後，依樣畫葫蘆參考自己寫的SUMPRODUCT函數做修改。

   依樣畫葫蘆(1)：

CODE中的d = "C" & I；這邊是你要拿ITEM跟誰比較的對象設定，比較對象"C"行組合數字來匹配儲存格，所以每一列比對的對象，都是不同的ITEM號碼歐。小編的I是從數字2開始編歐( I = 2)，然後透過While迴圈檢查C行有無資料歐，所以有改到"C"則有2處要修改歐(分別是： d = "C" & I、    While Sheet1.Range("C" & I) <> "") 

圖3.SUMPRODUCT彙整比對對象(C行；紅框)

   依樣畫葫蘆(2)：

參考小編的CODE，修改 * (AND)條件，或是>、<、=、<=、>=等邏輯與判斷條件。

   依樣畫葫蘆(3)：

參考小編的CODE，把名稱改掉；例如ITEM、日期、入庫。

    依樣畫葫蘆(4)：

Sheet1.Range("D" & I).Formula ："D" 用來控制寫在哪一行，這邊有D、E、F行，可以單獨一個或多個自行視需要做修改即可，但這邊的修改請一併修改 Sheet1.Range("D2:F" & I).Copy顏色字體標示的部分，例如，單行"D"，則改為"D2:D"，多行C、D、E則"C2:E"，以此類推。

    依樣畫葫蘆(5)：

Sheet1.Range("D2").PasteSpecial xlPasteValuesAndNumberFormats；顏色字體標示的部分，類似前步驟，但不管單一或多儲存格，僅要寫第一個位置的儲存格即可。

解說：有人可能會想，不就是自動寫入SUMPRODUCT函數，還不是一樣每次輸入資料會卡頓!!! 小編最後程式碼倒數第二行，這行功能為貼上資料，所以寫的函數公式會被自動蓋掉，自然沒卡頓問題。

Sheet1.Range("D2").PasteSpecial xlPasteValuesAndNumberFormats '這一行會僅留下結果，去除公式

VBA 營收資料分析(二)

參閱本篇分享文，也請尊重網路資源，請勿濫用網路爬蟲相關軟體技術歐。



圖1.程式碼流程

續前篇，維護好股票代碼後，做網路爬蟲與分析。

如何分析請參考前篇內容，至於資料怎整理的???

筆者是先想好呈現方式後再開始撰寫程式碼。

筆者是這樣呈現的，單純參考：



圖2.整理呈現

做一個Activex命令按鈕，並使工作頁命名為"營收盈餘"與"營收彙整"等兩頁，然後在按鈕內撰寫以下內容：



測試：

筆者以1101、1102、1301、2002做測試下：

image

圖3.測試結果

完成!!!

如果要在更豐富一點，也可以透過儲存格設定方式，讓年增率部分以百分比與顏色方式呈現。

在CommandButton1_Click增加：

Sheets("營收彙整").Range("f" & i & ":g" & i).NumberFormatLocal = "0.00%;[紅色](0.00%)"

就會長類似這樣：

image

圖4.美工後結果

大致上，先到這；整理美觀部分也來單獨整理一篇vba好了。

缺點整理：

時間性：如果要一次性抓上千筆資料，要花一點時間，畢竟這是單執行緒的程式，雖然比手動快多了，筆者自己的電腦，進行測試大約要13~15分鐘吧。

資料保存：筆者沒有寫自動存檔功能，資料都爬回來了，不保存下來總覺得有點可惜，應該弄個資料庫加強版才對厚。

昨晚想到沒寫怎應用：下

VBA：矩陣計算(相乘)

一、前言：

在算迴歸時，有使用到矩陣計算，所以小編分享一下矩陣乘法計算；

矩陣乘法一開始我在寫的時候是用雙迴圈概念，後來發現不對，有些組合永遠跑不到，所以改成用陣列方式來完成；目前還沒想到如何多矩陣相乘，還停在2個矩陣相乘。

二、發想：

透過Application.InputBox來取的資料儲存格位置。

副程式：

矩陣相乘有一部分是要與前回計算結果相加，這部分單獨靠迴圈明顯不足，這邊先寫一個副程式，名稱叫MATRIX_CAL，透過丟入2個矩陣當參數方式，用來計算矩陣相乘積與加總和。

主程式：

A矩陣：用I迴圈堆壘第一個矩陣成A矩陣，

B矩陣：用J迴圈堆壘第二個矩陣成B矩陣，並用J迴圈控制呼叫MATRIX_CAL的次數，然後每次呼叫時把A與 B矩陣當引數丟給MATRIX_CAL。

輸出：使用之前小編寫的sheet_name_check_delete這個副程式，下文字資料的引數"矩陣相乘結果"做執行；新增表單後，透過新的I與J迴圈將計算結果寫入工作表中

三、來做做：

先做一個VBA的AxtiveX命令按鈕

然後貼上code：

測試資料：

圖1.A與B矩陣

操作：貼上以上CODE後，點選按鈕後，會有對話框做如下操作。

 

圖2.A矩陣輸入(位置任選)

圖3.B矩陣輸入(位置任選)

圖4.結果

以前老長官名言"問題僅有一個，方法有好幾個"

似乎在程式語言當中，也是這樣，小編簡單整理與紀錄自己的小作品

VBA：自動寫存檔

 小編，經常處理很多案子要存檔，感覺滿實用的，寫一篇作整理。

副程式作動原理很簡單，就是直接複製一份要存檔的工作表後，直接存檔，不影響主要作業的工作表。

副程式：

主要有3個參數，

SOLDTONO：設定輸出檔案名稱；EX:"Report_"

SHEET_TAG：要另存新黨的工作表；EX:"SHEET1"

FILE_TYPE：檔案存檔類型；EX:52；此部分可參考MSDN資料

Save_Name = FILEPATH & "\" & A；這一行根據FileFormat:=FILE_TYPE的設定，自己加上& ".xlsm" 這部分歐

應用1：例如現在想要針對"工作表1"作單獨輸出，可以這樣設定(當然要弄一個按鈕先)：

那這時候去檢查目前檔案所在的資料夾就會多一個存檔結果!!

圖1.

應用2：例如現在想要針對"工作表1、工作表2、工作表3"作多輸出，可以這樣設定：