如何在 Excel 中計算動態範圍的平均值？

當您在處理每月銷售或累計總計等情境時，若資料範圍的起點或終點經常變動，公式便成為一種靈活的解決方案。透過讓變數儲存格動態決定範圍邊界，您無需重寫公式，即可迅速適應更新後的資料。

設定時，請選取一個空白儲存格（例如 C4 儲存格），並輸入下列公式：

=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2)))

接著按下 Enter 鍵，即可立即看到計算出的平均值！

此公式會自動調整範圍，涵蓋從 A2 到 C2 所指定列的所有儲存格；當 C2 的值變更時，用於計算平均值的範圍也會隨之動態調整，讓您能靈活因應新增資料或針對特定子集進行分析的需求，輕鬆擴充或縮減範圍。

注意事項：

（1）在此公式中 =IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2)))：A2 代表要計算平均值範圍的第一個儲存格，而 C2 則指向包含該範圍最後一列列號的儲存格。請根據您的資料結構調整這些參照，並務必確保 C2 儲存格填入有效的列號，否則將產生非預期結果或「#N/A」錯誤！

（2）另一種替代方式如下：

=AVERAGE(INDIRECT("A2:A"&C2))

這種方法同樣有效，因為它會建立範圍的文字參照，再由 INDIRECT 函數動態解讀。但請注意：若對已關閉的工作簿或大型資料集使用 INDIRECT，可能影響計算速度；且在處理易變動資料時，其效率不如 INDEX 函數。

實用提示：當您在資料持續增長（例如每日新增列）時，可搭配使用 COUNTA 或 COUNT 函數自動設定上限儲存格參照，確保動態範圍始終涵蓋最新資料。

適用情境：每日資料記錄、時間序列資料，或任何分析範圍由使用者輸入或摘要儲存格決定的情況。 優點：操作直接，無需額外工具。 限制：若資料列位置大幅變動，仍需手動調整公式。

當您需要根據特定條件（例如地區、類別或使用者自訂標籤）而非儲存格位置來定義動態範圍時，可結合動態名稱範圍與 INDIRECT 等函數，靈活調整計算方式。此方法尤其適合用於儀表板——只要使用者從下拉式清單中選取項目，系統便會立即顯示對應的平均值。

首先，依據標題列或欄位對資料集進行分組。操作步驟如下：

1. 選取整個區域（例如 A1:D11），然後點擊根據所選內容創建按鈕（位於）名稱管理器窗格中）。在彈出的對話方塊中，同時勾選首行與最左列選項，再按一下 OK。此步驟將自動為行列資料指派名稱範圍，讓公式參照更簡潔高效！

2. 在您選定的空白儲存格中輸入下列公式：

=AVERAGE(INDIRECT(G2))

其中，G2 是使用者輸入或選擇列／欄標題名稱的條件儲存格。當您在 G2 中變更內容（例如從「Region 1」改為「Region 2」）時，公式會立即動態計算對應範圍的平均值！請務必確保 G2 的輸入內容與已定義的名稱完全一致（包括大小寫），否則將導致 #REF！ 錯誤。

最適用於：報表儀表板與條件驅動的分析。 優點：支援極具彈性的動態報表及單一儲存格分析，並可透過使用者互動即時觸發。 限制：需仰賴正確的名稱管理與一致的輸入值。

在 Excel 中依填充顏色自動計數／加總／平均儲存格

有時您會以填充顏色標記儲存格，日後再對這些儲存格進行計數、加總或平均計算。按顏色統計功能可讓 Kutools for Excel 輕鬆協助您完成此作業！

針對進階動態行為（例如計算最後 N 列的平均值、根據多項動態條件求平均，甚至跨多張工作表整合資料），您可以建立自訂 VBA 巨集。當您發現內建公式過於複雜，或需要能靈活應對頻繁變動結構的自動化機制時，此方法尤其實用。

舉例來說，您可能希望計算 A 欄最後 N 列的平均值（N 由使用者指定），或對使用者所選定的非連續儲存格範圍進行平均。

1. 前往 開發人員工具 > Visual Basic，開啟 Microsoft Visual Basic for Applications 編輯器；接著選擇插入 > 模組，並貼上以下 VBA 程式碼：

Sub DynamicAverage_LastNRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim N As Long
    Dim result As Double
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    N = Application.InputBox("How many last rows to average?", xTitleId, 5, Type:=1)
    
    If N <= 0 Or N > lastRow - 1 Then
        MsgBox "Invalid input for N!", vbExclamation
        Exit Sub
    End If
    
    Set rng = ws.Range("A" & lastRow - N + 1, "A" & lastRow)
    result = Application.WorksheetFunction.Average(rng)
    
    MsgBox "Average of the last " & N & " rows in column A: " & result, vbInformation
End Sub

2. 點擊按鈕執行巨集。在彈出的對話方塊中，輸入您想計算平均值的最後幾列數量（例如 5、10 等），再按「確定」。結果將立即顯示在訊息方塊中！

若需根據更複雜的條件（例如依特定條件篩選，或跨多張工作表）計算平均值，可相應調整 VBA 程式碼——例如加入 InputBox 讓使用者輸入條件值，或透過循環遍歷多張工作表，合併指定區域後再計算平均值。

此方法提供極致的彈性，能自動處理複雜或重複性的動態平均計算。但務必啟用巨集，並僅在可信賴的工作簿中使用，以避免安全風險。執行新巨集前，請先儲存檔案；進行自動化變更時，也建議建立備份。

優點：支援自動化，能輕鬆應對複雜或大型資料情境，並可根據特定商業邏輯量身打造。 缺點：需具備基本 VBA 知識，且在結構變更時須同步維護相關程式碼。