跳到主要內容

Excel 提示:按顏色(背景、字體、條件格式)對儲存格進行計數/求和

作者:小陽 最後修改時間:2023-11-03

在日常任務中,顏色標記是快速區分和突出顯示關鍵數據的流行方法。 但是,我們如何根據特定顏色(填滿顏色、字體顏色、條件格式)對儲存格資料進行計數或總和? 預設情況下,Excel 不提供按顏色進行計數或求和的直接功能。 儘管如此,透過一些技巧和間接方法,我們仍然可以實現這一目標。 本文將探討如何以顏色對資料進行計數或求和。

根據背景色對單元格進行計數和求和

根據字體顏色對儲存格進行計數和求和

根據條件格式顏色對儲存格進行計數和求和


影片:根據顏色對儲存格進行計數和求和


根據背景色對單元格進行計數和求和

例如,如果您有一系列數據,其中的值填入有不同的背景顏色,如下面的螢幕截圖所示。 若要根據特定顏色對儲存格進行計數或求和,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 顏色的儲存格數量的範圍; 用於對具有匹配顏色的細胞進行計數。
  • 根據特定背景顏色對單元格求和:
    將下面提供的公式複製或鍵入到所需的儲存格中以獲得結果。 然後,向下拖曳填充手柄以獲得其他結果。 看截圖:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    備註:在此公式中, G2 是具有您想要匹配的特定背景顏色的參考單元格; $B$2:$E$12 是要計算 G2 顏色的儲存格數量的範圍; TRUE 用於對具有匹配顏色的單元格求和。

透過強大的功能按背景顏色對單元格進行計數和求和

對於那些不熟悉程式設計的人來說,VBA 可能顯得相當複雜。 在這裡,我們將介紹一個強大的工具— Excel的Kutool,其 按顏色計數 此功能可讓您只需點擊幾下即可根據背景顏色輕鬆計算(計數、總和、平均值等)。 令人印象深刻的是, 按顏色計數 功能不僅僅是背景顏色——它還可以根據字體顏色和條件格式進行區分和計算。

下載並安裝 Kutools for Excel,首先,選擇要根據特定背景顏色對儲存格進行計數或求和的資料範圍。 接下來,導航至 Kutools 加 並選擇 按顏色計數.

按顏色計數 對話方塊中,請指定操作:

  1. 選擇 標準格式 來自 上色方式 下拉列表;
  2. 指定 背景 來自 計數類型 下拉列表,可以在對話框中預覽每種背景顏色的統計結果;
  3. 最後點擊 生成報告 將計算結果匯出到新工作簿。

結果:

現在,您將獲得一個包含統計信息的新工作簿。 看截圖:

保養竅門:
  1. 按顏色計數 此功能還支援基於標準字體顏色、背景或條件格式的字體顏色以及填充和條件格式顏色的組合對單元格進行計數和求和。
  2. 對此功能感興趣,請 點擊下載即可免費試用 30 天.

使用濾鏡和小計功能按背景顏色對儲存格進行計數和求和

假設我們有一個水果銷售表,如下圖所示,我們將對表中的彩色單元格進行計數或求和 金額 列。

步驟 1:應用 SUBTOTAL 函數

選擇空白儲存格進入小計功能。

  • 若要對具有相同背景顏色的所有儲存格進行計數,請輸入公式:
    =SUBTOTAL(102, F2:F16)
  • 若要將所有具有相同背景顏色的儲存格相加,請輸入公式;
    =SUBTOTAL(109, F2:F16)
  • 備註: 在上面的公式中, 102 表示對過濾清單中的數值進行計數,同時排除隱藏單元格; 109 表示對篩選清單中不包括隱藏儲存格的值進行求和; F2:F16 是計算計數或總和的範圍。

第 2 步:根據特定顏色過濾單元格

  1. 選擇表格標題,然後按一下 數據 > 篩選。 看截圖:
  2. 點擊 篩選 圖標  在標題儲存格中 金額 列,然後單擊 通過彩色濾光片 以及您將依次計算的指定顏色。 看截圖:

結果:

過濾後,SUBTOTAL 公式會自動對表格中的彩色儲存格進行計數和求和 金額 柱。 看截圖:

備註:此方法需要將要計數或求和的彩色單元格放在同一列中。

根據字體顏色對儲存格進行計數和求和

想要根據 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 中輕鬆計算或求和儲存格值? 投身於 Excel的Kutools's 按顏色計數 特徵! 有了這個智慧工具,按特定字體顏色對單元格進行計數和求和變得輕而易舉。 了解如何 庫工具 可以改變您的 Excel 體驗。

下載並安裝 Kutools for Excel,首先,根據特定字體顏色選擇要計數或求和儲存格的資料範圍。 然後,點擊 Kutools 加 > 按顏色計數 打開 按顏色計數 對話框。

按顏色計數 對話方塊中,請指定操作:

  1. 選擇 標準格式 來自 上色方式 下拉列表;
  2. 指定 字體 來自 計數類型 下拉列表,可以在對話框中預覽每種字體顏色的統計結果;
  3. 最後點擊 生成報告 將計算結果匯出到新工作簿。

結果:

現在,您有一個新工作簿,顯示基於字體顏色的詳細統計資料。 看截圖:

保養竅門: 對這個功能有興趣,請 點擊下載即可免費試用 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 鍵執行此程式碼,會出現提示框,請根據條件格式選擇要對儲存格進行計數和求和的資料範圍。 然後,點擊 OK,請參見屏幕截圖:
  2. 在另一個提示方塊中,選擇要計數和求和的特定條件格式顏色,然後按一下 OK 按鈕,請參見屏幕截圖:

結果:

現在,結果(包括具有指定條件格式顏色的儲存格的計數和總和)將顯示在彈出框中。 看截圖:


使用智慧型功能對條件格式的儲存格進行計數和求和

如果您正在尋找其他快速簡便的方法來對條件格式的儲存格進行計數和求和, Excel的Kutools 是您的首選解決方案。 它是 按顏色計數 只需點擊幾下即可解決此任務。 深入了解 Kutools 可以為您的工作流程帶來的效率和精確度。

下載並安裝 Kutools for Excel,首先,根據特定的條件格式顏色選擇要對儲存格進行計數或求和的資料範圍。 然後,點擊 Kutools 加 > 按顏色計數 打開 按顏色計數 對話框。

按顏色計數 對話方塊中,請指定操作:

  1. 選擇 條件格式 來自 上色方式 下拉列表;
  2. 指定 背景 來自 計數類型 下拉列表,可以在對話框中預覽每種條件格式顏色的統計結果;
  3. 最後點擊 生成報告 將計算結果匯出到新工作簿。

結果:

現在,您有一個新工作簿,顯示基於條件格式顏色的詳細統計資料。 看截圖:

保養竅門: 對這個功能有興趣,請 點擊下載即可免費試用 30 天.

相關文章:

  • 如果字體顏色為紅色則傳回特定文本
  • 如果字體顏色在另一個單元格中為紅色,如下圖所示,您如何返回特定文本? 在本文中,我將介紹一些基於 Excel 中的紅色字體文本進行一些操作的技巧。
  • 按多種顏色過濾數據
  • 通常,在Excel中,您可以快速僅過濾一種顏色的行,但是,您是否曾經考慮過同時過濾多種顏色的行? 本文,我將為您介紹解決這個問題的快速技巧。
  • 添加顏色到下拉列表
  • 在 Excel 中,建立下拉清單可以為您提供很多幫助,有時,您需要根據相應的選擇對下拉清單值進行顏色編碼。 例如,我創建了一個水果名稱的下拉列表,當我選擇蘋果時,我需要將單元格自動著色為紅色,當我選擇橙色時,單元格可以自動著色為橙色。
  • 為合併儲存格的交替行著色
  • 在大數據中用不同顏色格式化交替行對我們掃描數據很有幫助,但有時,您的數據中可能會有一些合併的單元格。 要使用不同顏色交替突出顯示合併單元格的行,如下圖所示,您如何在 Excel 中解決此問題?
Comments (239)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
HI, i want sum all data with green color, and when I add 1 cell with green color the total will be increase qty, pls help me. Thank you.
This comment was minimized by the moderator on the site
Saya sudah copy VBA, dan pakai petunjuk sesuai di atas, untuk sum font color, tapi hasilnya #NAME. Knpa ya?
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations