如何在 Excel 中計算符合多個條件的中位數？

在 Excel 中計算數據集的中位數是數據分析和報告中經常需要的操作。雖然可以使用標準的 Excel 函數快速找到簡單範圍的中位數，但通常會遇到這樣的情況：您只需要來自符合多個特定條件的數據中的中位數值——例如，在大型數據集中查找特定日期的特定產品的銷售額中位數。僅靠傳統函數處理這種複雜、有條件的操作可能會很有挑戰性。在本教程中，我們將介紹各種實用解決方案，用於在 Excel 中計算符合多個條件的中位數，並探索基於公式的解決方法以及使用 VBA 實現高級需求的自動化。

• 計算符合多個條件的中位數
• VBA 程式碼 - 計算符合多個條件的中位數

計算符合多個條件的中位數

假設您有一個如下所示的數據範圍，您的任務是確定符合兩個條件的中位數值：例如，確定 A 列中值為「a」且 C 列中日期為「1 月 2 日」時 B 列的中位數值。這種情況在銷售報告、班級測試成績以及其他業務或學術數據分析中尤為常見，因為需要按多個類別進行過濾。

為了清楚起見，讓我們將工作表準備如下：在您的 Excel 工作表中，輸入您的條件並創建與下圖相似的佈局。在此處，E 列列出了 A 列的條件，而 F 列及以後的第 1 行代表了從 C 列中提取的日期條件。

要計算符合多個條件的中位數，您可以使用一個陣列公式，該公式利用 MEDIAN 和 IF 函數根據條件構建篩選後的值列表。具體操作如下：

1. 點擊 F2 單元格（希望顯示中位數結果的位置），並輸入以下公式：

=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))

此公式的工作原理是檢查每一行，如果 A 列中的值與 E2 中的條件匹配，並且 C 列中的值與 F1 中的標題匹配，則收集 B 列中的值以進行中位數計算。

2. 輸入公式後，按下 Ctrl + Shift + Enter（不只是 Enter），因為這是一個陣列公式。Excel 將自動用大括號 { } 包圍該公式以標識其為陣列公式。

3. 從 F2 的右下角拖動填充手柄，將公式複製到其他相關單元格中，以在不同條件下獲得所需的中位數，如下所示：

參數解釋和使用技巧：在公式中，$A$2:$A$12 是包含第一個條件的範圍（如產品名稱），$C$2:$C$12 是第二個條件的範圍（如日期），而 $B$2:$B$12 是包含要計算中位數的數值的範圍。根據自己的工作表調整這些範圍。始終使用絕對引用（$ 符號）以確保在複製公式時範圍不會移動。

注意事項：如果沒有值符合兩個條件，該公式將返回 #NUM! 錯誤。為避免混淆，您可以將該公式嵌套在 IFERROR 內部，以返回空白或自定義消息：

=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")

確保中位數列中不包含空單元格或非數字值，因為這可能會影響結果。

當條件相對簡單時（通常最多兩到三個條件），此基於公式的解決方案非常適用。它設置迅速且不需要任何編程技能。然而，對於具有動態條件或較大數據集的複雜過濾，維護或編輯陣列公式可能會變得繁瑣。

VBA 程式碼 - 計算符合多個條件的中位數

對於需要自動化條件中位數計算的場景——例如，條件眾多、數據集龐大或條件本身頻繁變化時——VBA 解決方案可提供實際的替代方案。使用 VBA，您可以建立一個可重複使用的宏，根據任意數量的條件計算中位數。VBA 解決方案特別有用於希望簡化重複分析或開發用於報告和儀表板的自定義 Excel 流程的情況。

按照以下步驟使用 VBA 進行條件中位數計算：

1. 點擊 開發工具 > Visual Basic。將打開新的 Microsoft Visual Basic for Applications 窗口。點擊 插入 > 模組，然後將以下代碼粘貼到模組中：

Sub ConditionalMedian()
    Dim DataRange As Range
    Dim CriteriaRange1 As Range
    Dim CriteriaRange2 As Range
    Dim OutputRange As Range
    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim TempArr() As Double
    Dim i As Long
    Dim j As Long
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
    Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
    Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
    Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
    Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
    Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
    
    count = 0
    For i = 1 To DataRange.Rows.count
        If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
           CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
            ReDim Preserve TempArr(count)
            TempArr(count) = DataRange.Cells(i, 1).Value
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        OutputRange.Value = "No match"
    Else
        Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
        If count Mod 2 = 1 Then
            OutputRange.Value = TempArr(count \ 2)
        Else
            OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
        End If
    End If
End Sub

Sub QuickSort(arr() As Double, first As Long, last As Long)
    Dim i As Long
    Dim j As Long
    Dim pivot As Double
    Dim temp As Double
    
    i = first
    j = last
    pivot = arr((first + last) \ 2)
    
    Do While i <= j
        Do While arr(i) < pivot
            i = i + 1
        Loop
        
        Do While arr(j) > pivot
            j = j - 1
        Loop
        
        If i <= j Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            i = i + 1
            j = j - 1
        End If
    Loop
    
    If first < j Then
        QuickSort arr, first, j
    End If
    
    If i < last Then
        QuickSort arr, i, last
    End If
End Sub

2. 點擊 按鈕（或按 F5）運行程式碼。系統將提示您選擇每個所需的範圍並輸入條件。完成提示後，結果（符合所有條件的中位數）將輸出到您指定的目標單元格中。

每次運行該宏時，此宏都允許您靈活選擇值範圍、條件範圍、條件值以及輸出結果的位置。如果需要，您還可以輕鬆調整代碼以包含更多條件。

提示和故障排除：使用 VBA 解決方案時，請確保所有選擇的範圍長度相等，並且條件與正確的數據類型和格式匹配（例如，文本與日期）。如果沒有值符合條件，輸出將顯示「No match」。為確保穩定性，運行宏之前保存您的工作簿，並在出現提示時始終啟用宏。此 VBA 解決方案適合熟悉宏安全設置的用戶，並可用於自動化的 Excel 工作流程中。

總之，VBA 方法自動執行了單靠公式難以實現或操作繁瑣的複雜中位數計算。在處理變量條件、頻繁重新計算和大型數據集時尤其適用。

相關文章：

• 在範圍內計算中位數