如何在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單元格指向有效的行，否則會得到意外的結果或 "NA"。

(2) 或者，您可以使用：

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

這種方法同樣有效，因為它為範圍創建了一個文本引用，INDIRECT 則動態解讀該引用。然而，在處理關閉的工作簿或大型數據集時請小心，因為它可能會影響計算速度，對於易變數據不如 INDEX 高效。

實用提示：當您的數據不斷增長（例如每天添加新行）時，您可以使用 COUNTA 或 COUNT 函數自動設置上限單元格引用——這確保您的動態範圍始終覆蓋最新條目。

適用場景：每日數據記錄、時間序列條目，或任何由用戶輸入或摘要單元格引導範圍開始或結束的分析。優勢：直接，不需要額外工具。限制：如果行位置大幅改變，則需要手動調整公式。

在某些情況下，您的動態範圍不是由位置定義，而是由特定條件（例如區域、類別或用戶自定義標籤）定義的，您可以結合動態命名範圍和 INDIRECT 等函數來調整計算。這對於用戶從下拉選單中選擇並立即看到相關平均值的儀表板尤其有用。

首先，按標題行或列對數據集進行分組。以下是操作步驟：

1. 選擇整個區域（例如 A1:D11），然後點擊 根據所選內容創建名稱 按鈕 在 名稱管理器 面板中。在彈出的對話框中，勾選 首行 和 最左列 選項，然後點擊 確定。此步驟將自動為行和列中的數據分配命名範圍，從而簡化公式的引用。

2. 在您選擇的空白單元格中，輸入以下公式：

=AVERAGE(INDIRECT(G2))

這裡，G2 是用戶輸入或選擇行或列標題名稱的條件單元格。當 G2 改變時（例如從 "Region1" 改為 "Region2"），公式將動態計算相應範圍的平均值。請始終確保 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 等），然後按下 OK。結果將會顯示在消息框中。

要根據更複雜的條件（例如基於條件或多個工作表）進行平均值計算，您可以相應地調整 VBA 代碼——例如，添加 InputBoxes 作為條件值，或循環遍歷多個工作表以在平均之前合併範圍。

此方法提供了最大的靈活性，能夠自動化複雜或重複的動態平均值計算。但請確保啟用宏並在受信任的工作簿中使用此方法以避免安全風險。運行新宏之前保存您的工作，並在自動化更改時考慮創建備份。

優點：允許自動化，處理複雜或大數據場景，可針對非常具體的業務邏輯進行定制。缺點：需要基本的 VBA 知識，並且當結構改變時需要維護過程。