VBA 資料分析教學：DAY 12

 VBA一鍵統計摘要

儲備：Excel工作表有銷售金額欄（B欄，從B2開始），需自動計算筆數、總和、平均，並輸出至資料下方總區。這常用於每日銷售或良率資料快速彙總。

VBA：

文字

Sub GenerateSummary()

    Dim ws As Worksheet, DataCol As Range, LastRow As Long, SummaryStartRow As Long

    Dim CountVal As Long, SumVal As Double, AvgVal As Double

    Set ws = ActiveSheet

    LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    Set DataCol = ws.Range("B2:B" & LastRow)

    CountVal = Application.WorksheetFunction.Count(DataCol)

    SumVal = Application.WorksheetFunction.Sum(DataCol)

    If CountVal > 0 Then AvgVal = SumVal / CountVal Else AvgVal = 0

    SummaryStartRow = LastRow + 2

    ws.Cells(SummaryStartRow, "A").Value = "統計摘要"

    ws.Cells(SummaryStartRow + 1, "A").Value = "筆數": ws.Cells(SummaryStartRow + 1, "B").Value = CountVal

    ws.Cells(SummaryStartRow + 2, "A").Value = "總和": ws.Cells(SummaryStartRow + 2, "B").Value = SumVal

    ws.Cells(SummaryStartRow + 3, "A").Value = "平均": ws.Cells(SummaryStartRow + 3, "B").Value = AvgVal

    MsgBox "統計摘要已產出"

End Sub

解說：利用End(xlUp)動態偵測最後一行，固定範圍錯誤；WorksheetFunction呼叫Excel內建函數計算統計值，安全且。執行後自動產生摘要，避免延伸可修正多欄或加條件篩選（如金額>0）。

驗算：6筆數據，總共和 25000+300+800+25000+12000+300=62400，平均 10400。此範例驗證動態監控與 WorksheetFunction 正確，適合增量資料統計。