Excel 技巧：按顏色（背景、字體、條件格式）計數/求和單元格

在日常任務中，顏色標記是一種快速區分和突出顯示關鍵數據的流行方法。但是，我們如何根據特定顏色（填充顏色、字體顏色、條件格式）來計算或求和單元格數據呢？默認情況下，Excel 並未提供直接按顏色計數或求和的功能。然而，通過一些技巧和間接方法，我們仍然可以實現這一目標。本文將探討如何按顏色計數或求和數據。

根據背景顏色計數和求和單元格

• 使用用戶定義函數
• 使用強大的功能 – Kutools for Excel
• 使用篩選和SUBTOTAL函數

根據字體顏色計數和求和單元格

• 使用用戶定義函數
• 使用簡單的功能 – Kutools for Excel

根據條件格式顏色計數和求和單元格

• 使用VBA代碼
• 使用智能功能 – Kutools for Excel

視頻：根據顏色計數和求和單元格

根據背景顏色計數和求和單元格

例如，如果你有一組數據，其中值填充了不同的背景顏色，如下圖所示。要根據特定顏色計數或求和單元格，Excel 並未提供直接按背景顏色計數或求和單元格的功能。然而，通過一些創意和實用技巧，你可以完成此任務。讓我們在本節中探索一些有用的方法。

使用用戶定義函數按背景顏色計數和求和單元格

這裡，我們將向你展示如何創建和使用這樣的用戶定義函數來解決這個問題。請按照以下步驟操作：

步驟 1：打開 VBA 模塊編輯器並複製代碼

1. 按下 Alt + F11 鍵以打開 Microsoft Visual Basic for Applications 窗口。
2. 在打開的窗口中，點擊 插入 > 模塊 以創建一個新的空白模塊。
3. 然後，將以下代碼複製並粘貼到空白模塊中。
   VBA 代碼：根據背景顏色計數和求和單元格

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

步驟 2：創建公式以按背景顏色計數和求和單元格

粘貼上述代碼後，關閉模塊窗口，然後應用以下公式：

• 根據特定背景顏色計數單元格：
  將以下提供的公式複製或輸入到你想要結果的單元格中。然後，向下拖動填充柄以獲取其他結果。見截圖：
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  注意：在此公式中，G2 是具有你想要匹配的特定背景顏色的參考單元格；$B$2:$E$12 是你想要計數 G2 顏色單元格數量的範圍；FALSE 用於計數匹配顏色的單元格。
  
• 根據特定背景顏色求和單元格：
  將以下提供的公式複製或輸入到你想要結果的單元格中。然後，向下拖動填充柄以獲取其他結果。見截圖：
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  注意：在此公式中，G2 是具有你想要匹配的特定背景顏色的參考單元格；$B$2:$E$12 是你想要計數 G2 顏色單元格數量的範圍；TRUE 用於求和匹配顏色的單元格。
  

使用強大功能按背景顏色計數和求和單元格

對於不熟悉編程的人來說，VBA 可能顯得相當複雜。這裡，我們將介紹一款強大的工具 - Kutools for Excel，其 Count by Color 功能允許你僅需幾次點擊即可根據背景顏色輕鬆計算（計數、求和、平均值等）。令人印象深刻的是，Count by Color 功能不僅限於背景顏色 – 它還可以根據字體顏色和條件格式進行區分和計算。

下載並安裝 Kutools for Excel 後，首先選擇你想要根據特定背景顏色計數或求和單元格的數據範圍。接著，導航到 Kutools Plus 並選擇 Count by Color。

在 Count by Color 對話框中，請指定操作：

1. 從 Color method 下拉列表中選擇 Standard formatting；
2. 從 Count type 下拉列表中指定 Background，並且你可以在對話框中預覽每種背景顏色的統計結果；
3. 最後，點擊 Generate report 將計算結果導出到新工作簿。

結果：

現在你將獲得包含統計信息的新工作簿。見截圖：

使用篩選和 SUBTOTAL 函數按背景顏色計數和求和單元格

假設我們有如下截圖所示的水果銷售表，我們將計數或求和 Amount 列中的彩色單元格。

步驟 1：應用 SUBTOTAL 函數

選擇空白單元格以輸入 SUBTOTAL 函數。

• 要計數所有具有相同背景顏色的單元格，請輸入公式：

  =SUBTOTAL(102, F2:F16)

• 要求和所有具有相同背景顏色的單元格，請輸入公式；

  =SUBTOTAL(109, F2:F16)

• 注意：在上述公式中，102 表示在過濾列表中計數數值，同時排除隱藏單元格；109 表示在過濾列表中求和值，排除隱藏單元格；F2:F16 是將要計算計數或求和的範圍。
• 

步驟 2：根據特定顏色篩選單元格

1. 選擇表格的標題，然後點擊 數據 > 篩選。見截圖：
   
2. 點擊 篩選 圖標  在標題單元格中 Amount 列，並點擊 按顏色篩選 以及你將依次計數的指定顏色。見截圖：
   

結果：

篩選後，SUBTOTAL 公式自動計數並求和 Amount 列中的彩色單元格。見截圖：

根據字體顏色計數和求和單元格

想根據字體顏色在 Excel 中計數或求和單元格嗎？假設你有數據，如給定截圖所示，單元格包含紅色、藍色、橙色和黑色的文字。默認情況下，Excel 並不方便這樣做。但別擔心！在本節中，我們將向你展示一些簡單的技巧來做到這一點。

使用用戶定義函數根據字體顏色計數和求和單元格

要計數和求和具有特定字體顏色的單元格，以下用戶定義函數可能有助於解決這個任務。請按照以下步驟操作：

步驟 1：打開 VBA 模塊編輯器並複製代碼

1. 按下 Alt + F11 鍵以打開 Microsoft Visual Basic for Applications 窗口。
2. 在打開的窗口中，點擊 插入 > 模塊 以創建一個新的空白模塊。
3. 然後，將以下代碼複製並粘貼到空白模塊中。
   VBA 代碼：根據字體顏色計數和求和單元格

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

步驟 2：創建公式以按字體顏色計數和求和單元格

粘貼上述代碼後，關閉模塊窗口，然後應用以下公式：

• 根據特定字體顏色計數單元格：
  將以下提供的公式複製或輸入到你想要結果的單元格中。然後，向下拖動填充柄以獲取其他結果。見截圖：
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  注意：在此公式中，G2 是具有你想要匹配的特定字體顏色的參考單元格；$B$2:$E$12 是你想要計數 G2 顏色單元格數量的範圍。
  
• 根據特定字體顏色求和單元格：
  將以下提供的公式複製或輸入到你想要結果的單元格中。然後，向下拖動填充柄以獲取其他結果。見截圖：
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  注意：在此公式中，G2 是具有你想要匹配的特定字體顏色的參考單元格；$B$2:$E$12 是你想要計數 G2 顏色單元格數量的範圍。
  

使用簡單功能根據字體顏色計數和求和單元格

希望在 Excel 中輕鬆根據字體顏色計數或求和單元格值嗎？深入瞭解 Kutools for Excel 的 Count by Color 功能！借助這個智能工具，按特定字體顏色計數和求和單元格變得輕而易舉。發現 Kutools 如何改變你的 Excel 體驗。

下載並安裝 Kutools for Excel 後，首先選擇你想要根據特定字體顏色計數或求和單元格的數據範圍。然後，點擊 Kutools Plus > Count by Color 打開 Count by Color 對話框。

在 Count by Color 對話框中，請指定操作：

1. 從 Color method 下拉列表中選擇 Standard formatting；
2. 從 Count type 下拉列表中指定 Font，並且你可以在對話框中預覽每種字體顏色的統計結果；
3. 最後，點擊 Generate report 將計算結果導出到新工作簿。

結果：

現在，你有一個新工作簿顯示基於字體顏色的詳細統計信息。見截圖：

根據條件格式顏色計數和求和單元格

在 Excel 中，你通常會使用條件格式來為符合某些條件的單元格應用特定顏色，使數據可視化直觀。但如果需要計數或求和那些特殊格式化的單元格呢？雖然 Excel 未提供直接方法，以下是繞過此限制的方法。

使用 VBA 代碼計數和求和條件格式化單元格

在 Excel 中計數和求和條件格式化單元格使用內置函數並不直觀。然而，你可以使用 VBA 代碼完成此任務。讓我們看看如何使用 VBA 來實現：

步驟 1：打開 VBA 模塊編輯器並複製代碼

1. 按下 Alt + F11 鍵以打開 Microsoft Visual Basic for Applications 窗口。
2. 在打開的窗口中，點擊 插入 > 模塊 以創建一個新的空白模塊。
3. 然後，將以下代碼複製並粘貼到空白模塊中。
   VBA 代碼：根據條件格式顏色計數和求和單元格

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

步驟 2：執行此 VBA 代碼

1. 粘貼代碼後，按下 F5 鍵運行此代碼，將出現一個提示框，請選擇你想要根據條件格式計數和求和單元格的數據範圍。然後，點擊 確定，見截圖：
   
2. 在另一個提示框中，選擇你想要計數和求和的特定條件格式顏色，然後點擊 確定 按鈕，見截圖：
   

結果：

現在，結果將包括具有指定條件格式顏色的單元格的計數和求和，並將在彈出的框中顯示。見截圖：

使用智能功能計數和求和條件格式化單元格

如果你正在尋找其他快速且簡單的方法來計數和求和條件格式化單元格，Kutools for Excel 是你的首選解決方案。其 Count by Color 功能只需幾次點擊即可解決這個任務。深入了解 Kutools 如何提升你的工作效率和精確度。

下載並安裝 Kutools for Excel 後，首先選擇你想要根據特定條件格式顏色計數或求和單元格的數據範圍。然後，點擊 Kutools Plus > Count by Color 打開 Count by Color 對話框。

在 Count by Color 對話框中，請指定操作：

1. 從 Color method 下拉列表中選擇 Conditional formatting ；
2. 從 Count type 下拉列表中指定 Background，並且你可以在對話框中預覽每種條件格式顏色的統計結果；
3. 最後，點擊 Generate report 將計算結果導出到新工作簿。

結果：

現在，你有一個新工作簿顯示基於條件格式顏色的詳細統計信息。見截圖：

相關文章：