如何在 Excel 中快速計算忽略零值的百分位數或四分位數？

作者Sun修改日期2025-10-24

當您在 Excel 中使用 PERCENTILE 或 QUARTILE 函數時，資料範圍常會包含零值。預設情況下，這些函數會將零值納入計算；然而，若零值在您的資料情境中不具實際意義，便可能大幅拉低百分位數或四分位數的結果。為獲得更精準的統計分析，您或許希望在計算時完全忽略零值。本教學將為您介紹幾種在 Excel 中高效解決此問題的實用技巧，包括原生公式方法、VBA 解決方案，以及各方法的適用情境，協助您選出最符合自身需求的方案。
計算百分位數並忽略零值

PERCENTILE 或 QUARTILE 忽略零值

VBA 巨集篩選並計算

PERCENTILE 或 QUARTILE 忽略零值

PERCENTILE 忽略零值（陣列公式）

太神奇了！在 Excel 中使用高效 Tabs，就像 Chrome、Edge、Firefox 和 Safari 一樣！
每天為您節省 50% 的時間，並減少數千次滑鼠點擊！

若要在計算百分位數時忽略零值，可使用陣列公式，僅針對大於零的數值進行運算。

選取一個空白儲存格以顯示結果，並輸入下列公式：

=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)

在輸入公式後，必須按 Ctrl + Shift + Enter（而非僅按 Enter），因為這是陣列公式。Excel 會自動在公式前後加上大括號{ }，表示已正確輸入。在此公式中：

A1:A13 是您的數據區域——請根據工作表需求調整此範圍。
0.3 代表第 30 ^個百分位數。您可以將此數值調整為任意想要計算的百分位數（例如，0.75 表示第 75）^個百分位數）。

此方法特別適用於您希望避免零值（例如遺漏值或無效測量）干擾統計結果的情境。

請注意，僅按 Enter 無法正確執行；您必須使用 Ctrl + Shift + Enter。此外，含有 IF(...)的聚合函數在大型資料集上可能效率較低。

套用公式以取得忽略零值的百分位數

QUARTILE 忽略零值（陣列公式）

四分位數的處理方式也類似。請選取一個儲存格作為結果輸出位置，並輸入：

=QUARTILE(IF(A1:A18>0,A1:A18),1)

在輸入公式後，請按 Ctrl + Shift + Enter 以確認為陣列公式。

A1:A18 為樣本數據範圍（請依需求調整）。
1 表示您要計算第一四分位數（即第 25）^個百分位數）。您也可以輸入 2 來計算中位數，或輸入 3 來計算第三四分位數（即第 75 ^個百分位數）。

請確保您的數據範圍不含文字或錯誤儲存格，因為此公式僅適用於數值資料。此解決方案最適合中等規模的資料集，無需 VBA 或增益集，即可快速完成計算。

套用公式以取得忽略零值的四分位數

VBA 巨集篩選並計算排除零值的百分位數／四分位數

您還可運用 VBA（Visual Basic for Applications）自動篩除零值，並對剩餘資料計算百分位數或四分位數。此方法尤其適合處理大型資料集，或需要頻繁重複執行此程序卻不想手動輸入公式的使用者。

適用情境：適合進階使用者、重複性任務或複雜區域。透過自訂程式碼，您可靈活處理任意百分位數或四分位數索引，以及任意資料範圍。

1. 前往 Excel 中的開發人員工具選項卡。若未顯示，請在功能區上按右鍵，選擇自訂功能區，並勾選開發人員。接著點選開發人員工具 > Visual Basic。
2. 在 Microsoft Visual Basic for Applications 視窗中，點選插入 > 模組。
3. 將下列 VBA 程式碼複製並貼上至模組中：

Sub FilterZeroAndPercentile()
    Dim rng As Range
    Dim ws As Worksheet
    Dim arr As Variant
    Dim filteredArr As Variant
    Dim i As Long, count As Long
    Dim percentileVal As Double
    Dim quartileVal As Double
    Dim pctl As Double
    Dim quartIdx As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
    
    If rng Is Nothing Then Exit Sub
    
    ' Prompt for percentile value (e.g., 0.75 for 75th percentile)
    pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
    
    ' Prompt for quartile index (1, 2, 3, 4)
    quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
    
    arr = rng.Value
    count = 0
    
    ' Count non-zero numbers
    For i = 1 To UBound(arr, 1)
        If arr(i, 1) > 0 Then
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        MsgBox "No non-zero data found!", vbExclamation, xTitleId
        Exit Sub
    End If
    
    ReDim filteredArr(1 To count)
    count = 0
    
    For i = 1 To UBound(arr, 1)
        If arr(i, 1) > 0 Then
            count = count + 1
            filteredArr(count) = arr(i, 1)
        End If
    Next i
    
    ' Calculate percentile / quartile
    percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
    quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
    
    MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
           "Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub

4. 點選按鈕，或在 VBA 視窗中按下 F5 執行巨集。系統將提示您選取僅包含數值的資料範圍，並指定所需的百分位數（例如 0.3 表示第 30）^個百分位數）及四分位數索引（如 1 表示第一四分位數）。巨集會自動過濾零值，並在訊息方塊中立即顯示計算結果！

優點：可快速處理大型或不規則資料，徹底排除零值，且無需手動輸入公式，還支援重複使用與自訂調整。
缺點：需啟用巨集並具備基本 VBA 知識；若未轉換為使用者自訂函數（UDF），則無法直接用於工作表公式。

常見問題與疑難排解：若您選取了非數值或錯誤的儲存格，巨集可能會跳過該項目或顯示錯誤訊息。請確保所選數據區域僅包含零值與正數；若系統未偵測到任何非零資料，將自動顯示相應提示。

提示：您可以進一步自訂 VBA 程式碼，將結果輸出至指定工作表的儲存格、更換計算函數，或自動處理多個範圍。執行或編輯巨集前，務必先儲存活頁簿，以避免資料意外遺失。

若您希望將此解決方案擴展至多欄位的百分位數或四分位數計算，可考慮修改巨集，加入針對跨欄位或範圍的迴圈處理機制。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多