Skip to main content

Excel 技巧:按顏色(背景、字體、條件格式)計數/求和單元格

Author: Xiaoyang Last Modified: 2025-05-12

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

count sum colored cells example

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

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

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


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

 


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

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


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

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

步驟 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 用於計數匹配顏色的單元格。
    count sum background color formula
  • 根據特定背景顏色求和單元格:
    將以下提供的公式複製或輸入到你想要結果的單元格中。然後,向下拖動填充柄以獲取其他結果。見截圖:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    注意:在此公式中,G2 是具有你想要匹配的特定背景顏色的參考單元格;$B$2:$E$12 是你想要計數 G2 顏色單元格數量的範圍;TRUE 用於求和匹配顏色的單元格。
    count sum background color result

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

對於不熟悉編程的人來說,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 將計算結果導出到新工作簿。

count sum background color kutools

結果:

現在你將獲得包含統計信息的新工作簿。見截圖:
count sum background color kutools result

提示:
  1. Count by Color 功能還支持根據標準字體顏色、背景或條件格式的字體顏色以及填充和條件格式顏色的組合來計數和求和單元格。
    count sum background color kutools option
  2. 對此功能感興趣,請點擊下載以獲得 30 天免費試用

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

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

步驟 1:應用 SUBTOTAL 函數

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

  • 要計數所有具有相同背景顏色的單元格,請輸入公式:
    =SUBTOTAL(102, F2:F16)
  • 要求和所有具有相同背景顏色的單元格,請輸入公式;
    =SUBTOTAL(109, F2:F16)
  • 注意:在上述公式中,102 表示在過濾列表中計數數值,同時排除隱藏單元格;109 表示在過濾列表中求和值,排除隱藏單元格;F2:F16 是將要計算計數或求和的範圍。
  • count sum background color subtotal formula

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

  1. 選擇表格的標題,然後點擊 數據 > 篩選。見截圖:
    count sum background color filter
  2. 點擊 篩選 圖標 count sum background color filter icon 在標題單元格中 Amount 列,並點擊 按顏色篩選 以及你將依次計數的指定顏色。見截圖:
    count sum background color filter by color

結果:

篩選後,SUBTOTAL 公式自動計數並求和 Amount 列中的彩色單元格。見截圖:
count sum background color filter subtotal result

注意:此方法要求你將計數或求和的彩色單元格在同一列中。

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

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


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

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

步驟 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 顏色單元格數量的範圍。
    count sum font color formula
  • 根據特定字體顏色求和單元格:
    將以下提供的公式複製或輸入到你想要結果的單元格中。然後,向下拖動填充柄以獲取其他結果。見截圖:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    注意:在此公式中,G2 是具有你想要匹配的特定字體顏色的參考單元格;$B$2:$E$12 是你想要計數 G2 顏色單元格數量的範圍。
    count sum font color result

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

希望在 Excel 中輕鬆根據字體顏色計數或求和單元格值嗎?深入瞭解 Kutools for ExcelCount 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 將計算結果導出到新工作簿。

count sum font color kutools

結果:

現在,你有一個新工作簿顯示基於字體顏色的詳細統計信息。見截圖:
count sum font color kutools result

提示:對此功能感興趣,請點擊下載以獲得 30 天免費試用

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

在 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 鍵運行此代碼,將出現一個提示框,請選擇你想要根據條件格式計數和求和單元格的數據範圍。然後,點擊 確定,見截圖:
    count sum conditional formatting vba dialog 1
  2. 在另一個提示框中,選擇你想要計數和求和的特定條件格式顏色,然後點擊 確定 按鈕,見截圖:
    count sum conditional formatting vba dialog 2

結果:

現在,結果將包括具有指定條件格式顏色的單元格的計數和求和,並將在彈出的框中顯示。見截圖:
count sum conditional formatting vba result


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

如果你正在尋找其他快速且簡單的方法來計數和求和條件格式化單元格,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 將計算結果導出到新工作簿。

count sum conditional formatting kutools

結果:

現在,你有一個新工作簿顯示基於條件格式顏色的詳細統計信息。見截圖:
count sum conditional formatting kutools result

提示:對此功能感興趣,請點擊下載以獲得 30 天免費試用

相關文章:

  • 按多種顏色篩選數據
  • 通常,在 Excel 中,你可以快速篩選只有一種顏色的行,但你是否考慮過同時篩選多種顏色的行?本文將談論快速解決這個問題的技巧。
  • 為下拉列表添加顏色
  • 在 Excel 中,創建下拉列表可以幫助你很多,有時候,你需要根據相應選擇的值對下拉列表進行顏色編碼。例如,我創建了一個水果名稱的下拉列表,當我選擇 Apple 時,我希望該單元格自動變為紅色,而當我選擇 Orange 時,該單元格可以變為橙色。
  • 為合併單元格交替著色
  • 在我們掃描數據時,為交替行設置不同顏色非常有幫助,但在大型數據中可能會有一些合併單元格。為了像下面截圖所示那樣交替高亮合併單元格的行,你如何在 Excel 中解決這個問題?