River's Footmar And Note: VBA：透過SUMPRODUCT，來分析產業別營收年增率走勢圖

2021年6月24日星期四

VBA：透過SUMPRODUCT，來分析產業別營收年增率走勢圖

我必須要說，這不是最好，但卻是最方便的做法，對於不熟悉陣列用法的朋友來說，也是一個可以解決問題的做法。

用法：SUMPRODUCT、FORMULA

一、發想：

圖4.

大致上想法是這樣，滿足資料整理好，也好閱讀的想法。

二、原始資料：參圖1. 小小編自己抓下來已預先整理成這樣了

圖1.原始資料

三、期望結果：參圖2.；小編心想透過產業別跟月份掌握營收變化，先簡單模擬一下。

圖2.期望結果

四、實作：

4.1 SUMPRODUCT 公式驗證：

小編在C2儲存格中做如下公式編輯：

= SUMPRODUCT((產業別="水泥工業")*(月份="109/04"),當月營收)

P.S產業別、月份、當月營收等行別要預先做"定義名稱"的設定歐

圖3.

說明：紅框圍比對的字串

4.2 開始來動手做VBA自動化：

規劃：

圖4.

4.3 vba code：作一個按鈕放入以下內容。


	Application.ScreenUpdating = False
	Application.DisplayStatusBar = False
	Application.Calculation = xlCalculationManual
	Application.EnableEvents = False

	S1 = Sheets("OUT").Range("z2000").End(xlUp).Row

	S2 = Sheets("OUT").Range("Aa2000").End(xlUp).Row

	Tag = Sheets("OUT").Range("Z1:Z" & S1)

	Tag = WorksheetFunction.Transpose(Tag)

	DATAA = Sheets("OUT").Range("AA1:AA" & S2)

	DATAA = WorksheetFunction.Transpose(DATAA)

	TOPIC = Array("產業別", "月份", "當月營收", "去年當月營收", "年增減(%)")

	Sheets("OUT").Range("A1:E1") = TOPIC

	For I = LBound(Tag) To UBound(Tag) Step 1

	S3 = Sheets("OUT").Range("A10000").End(xlUp).Row

	For J = LBound(DATAA) To UBound(DATAA) Step 1


	Sheets("OUT").Range("A" & J + S3) = Tag(I)

	Sheets("OUT").Range("B" & J + S3) = DATAA(J)

	Sheets("OUT").Range("C" & J + S3).Formula = "= SUMPRODUCT((產業別=" & Chr(34) & Tag(I) & Chr(34) & ")*(月份=" & Chr(34) & DATAA(J) & Chr(34) & "),當月營收)"

	Sheets("OUT").Range("C" & J + S3).Calculate

	Sheets("OUT").Range("D" & J + S3).Formula = "= SUMPRODUCT((產業別=" & Chr(34) & Tag(I) & Chr(34) & ")*(月份=" & Chr(34) & DATAA(J) & Chr(34) & "),去年當月營收)"

	Sheets("OUT").Range("D" & J + S3).Calculate

	Sheets("OUT").Range("E" & J + S3).Formula = "=(C" & J + S3 & "-D" & J + S3 & ")/D" & J + S3

	Sheets("OUT").Range("E" & J + S3).Calculate

	Next J

	Next I

	S3 = Sheets("OUT").Range("A10000").End(xlUp).Row

	Sheets("OUT").Range("A2:E" & S3).Copy
	Sheets("OUT").Range("A2").PasteSpecial xlPasteValuesAndNumberFormats

	Application.ScreenUpdating = True
	Application.DisplayStatusBar = True
	Application.Calculation = xlCalculationAutomatic
	Application.EnableEvents = True