如何在 Excel 中根據條件僅對可見儲存格求和？

在 Excel 中，使用者通常可以使用 SUMIFS 函數根據特定條件對儲存格進行求和。然而，當處理篩選後的資料時，簡單地應用 SUMIFS 將會在計算中包含可見和隱藏的儲存格。如果你需要僅對符合特定條件的可見（即未被篩選）儲存格求和，這通常會導致不正確的結果，如下方截圖所示。

在日常報告和數據分析工作流程中，準確聚合篩選表格中的數據是一種常見需求，例如在應用某些篩選後計算特定產品或類別的銷售金額。如果操作不當，可能會導致總計包含你不想要的數據，因此使用僅對可見數據求和的技術非常重要，這樣才能確保屏幕上的數據準確無誤。

本文介紹了幾種適合不同場景和熟練程度的實用方法，每種方法都有其優點和可能的限制。你可以選擇最適合你的工作表大小、數據結構和操作習慣的解決方案。下面提供了每個解決方案的詳細步驟，並解釋了潛在錯誤以及優化計算過程的方式，以獲得更可靠結果的方法。

使用輔助列根據一個或多個條件僅對可見儲存格求和

一種最直觀且穩定的方法是使用輔助列，該列僅返回可見行的值，然後利用 SUMIFS 函數與你所需的條件配合來進行求和。這特別適用於數據集經常以各種方式進行篩選的情況，或者需要設置同事們能夠容易理解或修改的計算。

優勢：設置簡單；所有邏輯和計算都在工作表中可見；最適合中小型表格；當需要調整或審核公式時非常穩健。

局限性：創建額外的列；如果行布局改變，可能需要更新公式；在非常大的數據集中廣泛使用可能會變得繁瑣。

例如，要僅對篩選範圍內產品為“Hoodie”的訂單值求和：

1. 在數據集旁邊的空白列中輸入或複製以下公式（例如，輸入到 E2 單元格，假設 D 是你的值列）：

拖動填充柄向下填充此公式至數據範圍的所有行。如果該行是可見的，該公式將返回 D 列中的值；如果該行因篩選而被隱藏，則返回 0。

2. 在 E 列生成輔助值後，使用 SUMIFS 函數根據條件僅對可見值進行求和。例如，針對 A 列中的 “Hoodie” 求和：

提示：如果你想讓總計反映多個條件，例如求和 “Hoodie” 並且也是 “Red” 的值，擴展你的公式如下：

=SUMIFS(E2:E12,A2:A12,A17,C2:C12,B17)

你可以通過增加 =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], ...) 格式的參數來添加更多條件。始終檢查你的範圍，以確保正確對齊和預期結果。

請注意：如果你在設置公式後重新排列、插入或刪除了行，請仔細檢查以確保所有引用仍與數據結構匹配。有時錯誤可能是由於範圍錯位或忘記更新條件單元格造成的。

使用公式根據條件僅對可見儲存格求和

如果你偏好不需要添加輔助列的基於公式的解決方案，你可以使用 SUMPRODUCT、SUBTOTAL、OFFSET、ROW 和 MIN 函數的組合來根據特定條件對可見儲存格求和。這種方法最適合熟悉陣列公式經驗豐富的 Excel 使用者，尤其當你希望保持工作表整潔而不增加額外列時非常有用。

優勢：不需要額外的工作表列；靈活且動態；當你篩選或更改條件時，公式會立即更新。

局限性：公式可能難以閱讀或調試，特別是對於不熟悉陣列函數的人；在非常大的表格中性能可能會變慢。

在空白單元格中複製或輸入以下公式（例如，對 A2:A12 中的 “Hoodie” 可見儲存格求和，實際值位於 D2:D12，條件位於 A17）：

輸入公式後，按下 Enter 鍵即可得到所需結果，如下所示：

注意：此方法對指定的範圍非常敏感——不匹配或重疊的範圍可能會引發錯誤或意外結果。測試極端情況，特別是在篩選改變可見行的數量或位置時。

使用 VBA 程式碼根據條件僅對可見儲存格求和

對於高級用戶，使用 VBA 提供了一種靈活的方式，僅根據特定條件對可見儲存格求和，特別是在處理標準公式可能遇到性能瓶頸或條件計數包括難以用單一公式表達的多條件邏輯的複雜場景或大型數據集時。VBA 可以迭代每一行可見數據，測試條件，並高效地計算總和。這對於重複報告任務或自動化匯總統計非常適合。

優勢：可以輕鬆處理大型數據集、多個或動態條件及複雜邏輯；即使有數千行也能快速執行；減少手動更改公式帶來的錯誤風險。

局限性：需要啟用宏；一些用戶可能不熟悉 VBA 或沒有足夠的權限；更改需要訪問宏編輯器。在重要數據集上運行 VBA 前務必備份。

1. 首先，透過點擊開發工具 > Visual Basic 打開 VBA 編輯器。在出現的窗口中，進入 插入 > 模塊，並將以下代碼粘貼到新模塊中：

Sub SumVisibleByCriteria()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim criteriaColumn As Range
    Dim sumColumn As Range
    Dim criteriaValue As Variant
    Dim total As Double
    Dim lastRow As Long
    Dim criteriaColNum As Integer
    Dim sumColNum As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    
    ' Prompt user for criteria column and sum column
    Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
    Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
    criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
    
    If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
        MsgBox "Operation cancelled.", vbInformation, xTitleId
        Exit Sub
    End If
    
    If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
        MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
        Exit Sub
    End If
    
    total = 0
    
    For Each cell In criteriaColumn
        If Not cell.EntireRow.Hidden Then
            If cell.Value = criteriaValue Then
                total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
            End If
        End If
    Next cell
    
    MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub

2點擊 “運行”按鈕（或按 F5）執行代碼。系統會彈出對話框，提示你選擇條件範圍（如你的產品名稱）、要加總的值範圍以及你希望作為篩選條件的值（如“Hoodie”）。該宏將僅對符合條件的可見行進行求和，並在彈出消息中顯示結果。
實用提示： 當你需要在更改數據或篩選後重新計算總和時，使用此 VBA 代碼。你可以進一步擴展 VBA 代碼，通過添加更多輸入提示或邏輯條件來支持多個條件。

故障排查：始終確保你選擇的條件和值範圍具有相同的行數，並且屬於與篩選數據相同的列。如果代碼報錯或未返回你期望的總和，請仔細檢查篩選設置和當前選擇。

總結建議：對於需要重複可視計算的數據分析，將此宏保存在你的個人宏工作簿中可以加快日常報告速度。如果對話框未出現，請檢查你的宏設置和安全權限。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析 ｜生成程式碼 ｜ 創建自訂公式 ｜ 分析數據並生成圖表 ｜ 調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值 ｜ 刪除空行 ｜ 合併列或單元格且不遺失數據 ｜ 四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表 ｜依賴型下拉列表 ｜ 多選下拉列表...
	列管理器：添加指定數量的列 ｜ 移動列 ｜ 切換隱藏列的顯示狀態 ｜ 比較區域及列...
	精選功能：網格聚焦 ｜ 設計檢視 ｜ 增強編輯欄 ｜ 工作簿及工作表管理器 ｜ 資源庫（快捷文本） ｜ 日期提取器 ｜ 合併資料 ｜ 加密/解密儲存格 ｜ 按列表發送電子郵件 ｜ 超級篩選 ｜ 特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表 類型（甘特圖等）｜40+ 實用 公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多

使用 Kutools，語言任你選 — 支援英語、西班牙語、德語、法語、中文及超過40 種語言！

運用 Kutools for Excel，全面提升您的 Excel 技能，體驗前所未有的高效。 Kutools for Excel 提供超過300 項進階功能，讓您提升工作效率、節省時間。 點此尋找您最需要的功能...

查看更多 Kutools for Excel 詳情... 免費下載...

---

Office Tab 為 Office 帶來分頁介面，讓您的工作更加輕鬆簡單

• 在 Word、Excel、PowerPoint 中啟用分頁編輯與閱讀。
• 在同一視窗的新分頁中打開與創建多份文件，而非開啟新視窗。
• 提升您的生產力50%，每日可幫您減少數百次鼠標點擊！

查看更多 Office Tab 詳情... 免費下載...

---

所有 Kutools 外掛，一次安裝

Kutools for Office 套裝整合了 Excel、Word、Outlook 和 PowerPoint 的外掛，外加 Office Tab Pro，非常適合需要跨 Office 應用程式協同作業的團隊。

• 全合一套裝 — Excel、Word、Outlook及 PowerPoint 外掛 + Office Tab Pro
• 一鍵安裝，一份授權 — 幾分鐘完成設置（支援 MSI）
• 協同運作更順暢 — Office 應用間無縫提升生產力
• 30 天全功能試用 — 無需註冊、無需信用卡
• 最超值 — 一次購買，節省單獨外掛費用

查看更多 Kutools for Office 詳情... 免費下載...