EXCEL資料表，分別有 財產設備大分類、廠牌型號、部門別、數量 等4欄位，
A B C D
1 財產設備大分類 廠牌型號 部門別 數量
2 個人電腦 HP Pro SFF 400 G9 人事 2
3 個人電腦 HP Pro SFF 400 G9 財務 2
4 個人電腦 HP Pro SFF 400 G9 行政 2
5 個人電腦 HP Pro SFF 400 G9 銷售 2
6 個人電腦 ASUS S500TE 研發 1
7 個人電腦 HP Pro SFF 400 G9 研發 1
8 個人電腦 HP Pro SFF 400 G9 資訊 2
公司部門，有人事、財務、行政、銷售、研發、資訊等6部門別
有1份報表，需臚列出 財產設備大分類，其各⌈廠牌型號⌋，分配到公司，各個部門之台數(如下方產出的結果)
,####################################################################################
以下是個人作法，先用「樞紐分析表」，將EXCEL資料 預做梳理，
在以下區域之間拖曳欄位:
💛篩選 拖曳 財產設備大分類、 (因為有數個「財產設備大分類」項目，如:個人電腦、筆電)
💜欄 拖曳 部門別、 (方便掌握 各部門別，可逐欄一一列出)
💜列 拖曳 廠牌型號、 (各廠牌型號，以此例「個人電腦」大分類，呈現會有2列，
分別 為HP Pro SFF 400 G9 及 ASUS S500TE 等2類桌機
💜值 拖曳 數量 (可幫忙預先加總，列出各部門別，各廠牌型號 電腦之台數)
'=====================================================================
💙'T1欄及O1欄，公式手動設定好
💛B1欄，為PivotTable樞鈕分析表之 大分類「財產設備大分類」，個人電腦、筆電等可供下拉(此欄位值，即為上面 篩選 拖曳，自動產生)，只要下拉變動不同之大分類，樞鈕分析表之欄及列 資料，也會隨之變動。
💙T1欄(=COUNTA(4:4))，為PivotTable樞鈕分析表之總欄數 ,因為第4列為PivoteTable部門別，逐欄一一列出，此T1欄的值，為手動填入COUNTA公式。
此公式之目的主要為讓後續VBA程式，清楚掌握「個人電腦」大分類中，共有多少 部門別?在PivotTable中，如該部門別沒有分配到大分類資源者,將不會列出部門別,故此欄會隨著大分類之不同,而做變動。
💙O1欄(=COUNTA(A:A)+1)，為PivotTable樞鈕分析表之總列數 ，因為A欄為PivoteTable廠牌型號，逐列一一呈現，此O1欄的值，為手動填入COUNTA公式。
此公式之目的主要為讓後續VBA程式，清楚掌握「個人電腦」大分類中，共有多少項?廠牌型號，此欄會隨著大分類之不同,而做變動(total_Rows)。
'####################################################################################
Sub ApplyFormula()
Dim ws As Worksheet
Dim lastRow As Long
Dim total_Rows As Long
Dim formulaRange As Range
Dim i As Long
'來源4個欄位，經過「樞紐分析表」，將資料預做梳理後，工作表名稱指定為Pivot_1
Set ws = ThisWorkbook.Sheets("Pivot_1")
'因「樞紐分析表」精靈產出後，上半段前3列已固定做標題列用途，故要扣除3後，每次下拉💛B1「財產設備大分類」後，才是實際⌈廠牌型號⌋之資料筆數。
total_Rows = ws.Range("O1").Value - 3
Sheets("Pivot_1").Select
Columns("P:P").Select '清空P欄，因為在下拉⌈財產設備大分類⌋，會貼不同的Formula
Selection.ClearContents
' 預訂放置公式的起始處，預訂每次均由P5欄開始放置
Set formulaRange = ws.Range("P5")
' 各列⌈廠牌型號⌋，詳列出 各個 "部門別"擁有之台數，逐一詳列出來
For i = 1 To total_Rows ' 第一列開始，直到最後一列
'搭配Range & Formula ，方便每次於💛B1下拉「財產設備大分類，每列最後貼上公式
formulaRange.Offset(i - 1, 0).Formula = "=IF($B" & i + 4 & ">0, $B$4&$B" & i + 4 & "&""台, "", """")" & _
"&IF($C" & i + 4 & ">0, $C$4&$C" & i + 4 & "&""台, "", """")" & _
"&IF($D" & i + 4 & ">0, $D$4&$D" & i + 4 & "&""台, "", """")" & _
"&IF($E" & i + 4 & ">0, $E$4&$E" & i + 4 & "&""台, "", """")" & _
"&IF($F" & i + 4 & ">0, $F$4&$F" & i + 4 & "&""台, "", """")" & _
"&IF($G" & i + 4 & ">0, $G$4&$G" & i + 4 & "&""台, "", """")" & _
"&IF($H" & i + 4 & ">0, $H$4&$H" & i + 4 & "&""台, "", """")"
'最後一列,為該大分類，各部門別彙整後之小計台數 (因為PivotTable樞鈕分析表已預先產製好) ;最前方IF判斷式>0,即 台數>0,則顯示 部門別及台數。
Next i
End Sub
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
產出的結果 ( 💛B1欄「財產設備大分類」，下拉選擇⌈個人電腦⌋)
列標籤 人事 財務 行政 銷售 研發 資訊
HP Pro SFF 400 G9 2 2 2 2 1 2 ,人事2台,財務2台....研發1台....(略)
ASUS S500TE 1 ,研發1台
總計 2 2 2 2 2 2 ,人事2台....................研發2台,資訊2台
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
PROMPT提示詞下法如下: (可將下面提示詞,貼至AI神器去詢問,取得VBA程式碼參考資訊)
處理前之樞鈕分析表,計有4欄位
篩選 拖曳 財產設備大分類、
欄 拖曳 部門別、
列 拖曳 廠牌型號、
值 拖曳 數量
經過「樞紐分析表」處理後，工作表名稱指定為Pivot_1
B1欄,為樞鈕分析表之篩選 財產設備大分類
excel表中,第4列為標題列,分別為 A4 廠牌型號、B4 人事、C4 財務、D4 行政、E4 銷售、F4 研發、G4 資訊H4 ,
T1欄,公式已設好 =COUNTA(4:4)，為樞鈕分析表之總欄數
O1欄,公式已設好 =COUNTA(A:A)+1，為樞鈕分析表之總列數
下面公式為全數設備型號,我已將每列總數 放於 P5開始放置公式, 因為台數為0 ,就無需放入,請參考下面公式,寫VBA程式
total_Rows = ws.Range("O1").Value - 3
Sheets("Pivot_1").Select
Columns("P:P").Select
Selection.ClearContents
Set formulaRange = ws.Range("P5")
For i = 1 To total_Rows ' 第一列開始，直到最後一列
'搭配Range & Formula ，方便每次於💛B1下拉「財產設備大分類，每列最後貼上公式
formulaRange.Offset(i - 1, 0).Formula = "=IF($B" & i + 4 & ">0, $B$4&$B" & i + 4 & "&""台, "", """")" & _
"&IF($C" & i + 4 & ">0, $C$4&$C" & i + 4 & "&""台, "", """")" & _
"&IF($D" & i + 4 & ">0, $D$4&$D" & i + 4 & "&""台, "", """")" & _
"&IF($E" & i + 4 & ">0, $E$4&$E" & i + 4 & "&""台, "", """")" & _
"&IF($F" & i + 4 & ">0, $F$4&$F" & i + 4 & "&""台, "", """")" & _
"&IF($G" & i + 4 & ">0, $G$4&$G" & i + 4 & "&""台, "", """")" & _
"&IF($H" & i + 4 & ">0, $H$4&$H" & i + 4 & "&""台, "", """")"
Next i
相關參考資訊:
