Excel 技巧:按顏色(背景、字體、條件格式)計數/求和單元格
在日常任務中,顏色標記是一種快速區分和突出顯示關鍵數據的流行方法。但是,我們如何根據特定顏色(填充顏色、字體顏色、條件格式)來計算或求和單元格數據呢?默認情況下,Excel 並未提供直接按顏色計數或求和的功能。然而,通過一些技巧和間接方法,我們仍然可以實現這一目標。本文將探討如何按顏色計數或求和數據。
根據背景顏色計數和求和單元格
例如,如果你有一組數據,其中值填充了不同的背景顏色,如下圖所示。要根據特定顏色計數或求和單元格,Excel 並未提供直接按背景顏色計數或求和單元格的功能。然而,通過一些創意和實用技巧,你可以完成此任務。讓我們在本節中探索一些有用的方法。
使用用戶定義函數按背景顏色計數和求和單元格
這裡,我們將向你展示如何創建和使用這樣的用戶定義函數來解決這個問題。請按照以下步驟操作:
步驟 1:打開 VBA 模塊編輯器並複製代碼
- 按下 Alt + F11 鍵以打開 Microsoft Visual Basic for Applications 窗口。
- 在打開的窗口中,點擊 插入 > 模塊 以創建一個新的空白模塊。
- 然後,將以下代碼複製並粘貼到空白模塊中。
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 對話框中,請指定操作:
- 從 Color method 下拉列表中選擇 Standard formatting;
- 從 Count type 下拉列表中指定 Background,並且你可以在對話框中預覽每種背景顏色的統計結果;
- 最後,點擊 Generate report 將計算結果導出到新工作簿。
結果:
現在你將獲得包含統計信息的新工作簿。見截圖:
- Count by Color 功能還支持根據標準字體顏色、背景或條件格式的字體顏色以及填充和條件格式顏色的組合來計數和求和單元格。
- 對此功能感興趣,請點擊下載以獲得 30 天免費試用。
使用篩選和 SUBTOTAL 函數按背景顏色計數和求和單元格
假設我們有如下截圖所示的水果銷售表,我們將計數或求和 Amount 列中的彩色單元格。
步驟 1:應用 SUBTOTAL 函數
選擇空白單元格以輸入 SUBTOTAL 函數。
- 要計數所有具有相同背景顏色的單元格,請輸入公式:
=SUBTOTAL(102, F2:F16)
- 要求和所有具有相同背景顏色的單元格,請輸入公式;
=SUBTOTAL(109, F2:F16)
- 注意:在上述公式中,102 表示在過濾列表中計數數值,同時排除隱藏單元格;109 表示在過濾列表中求和值,排除隱藏單元格;F2:F16 是將要計算計數或求和的範圍。
步驟 2:根據特定顏色篩選單元格
- 選擇表格的標題,然後點擊 數據 > 篩選。見截圖:
- 點擊 篩選 圖標
在標題單元格中 Amount 列,並點擊 按顏色篩選 以及你將依次計數的指定顏色。見截圖:
結果:
篩選後,SUBTOTAL 公式自動計數並求和 Amount 列中的彩色單元格。見截圖:
根據字體顏色計數和求和單元格
想根據字體顏色在 Excel 中計數或求和單元格嗎?假設你有數據,如給定截圖所示,單元格包含紅色、藍色、橙色和黑色的文字。默認情況下,Excel 並不方便這樣做。但別擔心!在本節中,我們將向你展示一些簡單的技巧來做到這一點。
使用用戶定義函數根據字體顏色計數和求和單元格
要計數和求和具有特定字體顏色的單元格,以下用戶定義函數可能有助於解決這個任務。請按照以下步驟操作:
步驟 1:打開 VBA 模塊編輯器並複製代碼
- 按下 Alt + F11 鍵以打開 Microsoft Visual Basic for Applications 窗口。
- 在打開的窗口中,點擊 插入 > 模塊 以創建一個新的空白模塊。
- 然後,將以下代碼複製並粘貼到空白模塊中。
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 對話框中,請指定操作:
- 從 Color method 下拉列表中選擇 Standard formatting;
- 從 Count type 下拉列表中指定 Font,並且你可以在對話框中預覽每種字體顏色的統計結果;
- 最後,點擊 Generate report 將計算結果導出到新工作簿。
結果:
現在,你有一個新工作簿顯示基於字體顏色的詳細統計信息。見截圖:
根據條件格式顏色計數和求和單元格
在 Excel 中,你通常會使用條件格式來為符合某些條件的單元格應用特定顏色,使數據可視化直觀。但如果需要計數或求和那些特殊格式化的單元格呢?雖然 Excel 未提供直接方法,以下是繞過此限制的方法。
使用 VBA 代碼計數和求和條件格式化單元格
在 Excel 中計數和求和條件格式化單元格使用內置函數並不直觀。然而,你可以使用 VBA 代碼完成此任務。讓我們看看如何使用 VBA 來實現:
步驟 1:打開 VBA 模塊編輯器並複製代碼
- 按下 Alt + F11 鍵以打開 Microsoft Visual Basic for Applications 窗口。
- 在打開的窗口中,點擊 插入 > 模塊 以創建一個新的空白模塊。
- 然後,將以下代碼複製並粘貼到空白模塊中。
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 代碼
- 粘貼代碼後,按下 F5 鍵運行此代碼,將出現一個提示框,請選擇你想要根據條件格式計數和求和單元格的數據範圍。然後,點擊 確定,見截圖:
- 在另一個提示框中,選擇你想要計數和求和的特定條件格式顏色,然後點擊 確定 按鈕,見截圖:
結果:
現在,結果將包括具有指定條件格式顏色的單元格的計數和求和,並將在彈出的框中顯示。見截圖:
使用智能功能計數和求和條件格式化單元格
如果你正在尋找其他快速且簡單的方法來計數和求和條件格式化單元格,Kutools for Excel 是你的首選解決方案。其 Count by Color 功能只需幾次點擊即可解決這個任務。深入了解 Kutools 如何提升你的工作效率和精確度。
下載並安裝 Kutools for Excel 後,首先選擇你想要根據特定條件格式顏色計數或求和單元格的數據範圍。然後,點擊 Kutools Plus > Count by Color 打開 Count by Color 對話框。
在 Count by Color 對話框中,請指定操作:
- 從 Color method 下拉列表中選擇 Conditional formatting ;
- 從 Count type 下拉列表中指定 Background,並且你可以在對話框中預覽每種條件格式顏色的統計結果;
- 最後,點擊 Generate report 將計算結果導出到新工作簿。
結果:
現在,你有一個新工作簿顯示基於條件格式顏色的詳細統計信息。見截圖:
相關文章:
- 如果字體顏色是紅色則返回特定文本
- 如何在另一個單元格的字體顏色是紅色時返回特定文本,如下截圖所示?在本文中,我將介紹一些基於 Excel 中紅色字體文本進行操作的技巧。
- 按多種顏色篩選數據
- 通常,在 Excel 中,你可以快速篩選只有一種顏色的行,但你是否考慮過同時篩選多種顏色的行?本文將談論快速解決這個問題的技巧。
- 為下拉列表添加顏色
- 在 Excel 中,創建下拉列表可以幫助你很多,有時候,你需要根據相應選擇的值對下拉列表進行顏色編碼。例如,我創建了一個水果名稱的下拉列表,當我選擇 Apple 時,我希望該單元格自動變為紅色,而當我選擇 Orange 時,該單元格可以變為橙色。
- 為合併單元格交替著色
- 在我們掃描數據時,為交替行設置不同顏色非常有幫助,但在大型數據中可能會有一些合併單元格。為了像下面截圖所示那樣交替高亮合併單元格的行,你如何在 Excel 中解決這個問題?
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!