提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何計算和統計基於Excel中的背景顏色的單元格?

假設您有一系列具有不同背景顏色的單元格,例如紅色,綠色,藍色等,但現在您需要計算該範圍內有多少單元格具有特定背景顏色,並將具有相同特定顏色的彩色單元格相加。 在Excel中,沒有直接的公式來計算顏色單元的總和和計數,這裡我將向您介紹一些解決此問題的方法。

通過Filter和SUBTOTAL基於特定填充顏色計算和求和單元格

通過GET.CELL函數根據特定填充顏色計數和求和單元格

使用用戶定義函數根據特定填充顏色計算和求和單元格

使用Kutools函數根據特定填充顏色計算和求和單元格

使用Kutools for Excel基於特定填充顏色(或條件格式顏色)計算和求和單元格

單擊以在Excel中計算,求和和平均彩色單元格

隨著優秀 按顏色計數 的特點 Kutools for Excel,只需在Excel中單擊一下,即可按指定的填充顏色或字體顏色快速計算,求和和平均單元格。 此外,此功能還將通過填充顏色或字體顏色找出單元格的最大值和最小值。 全功能免費試用60天!
廣告按顏色計算2


通過Filter和SUBTOTAL計算和求和單元格

假設我們有一個水果銷售表,如下面的屏幕截圖所示,我們將對Amount列中的彩色單元格進行計數或求和。 在這種情況下,我們可以按顏色過濾Amount列,然後在Excel中通過SUBTOTAL函數對過濾的彩色單元格進行計數或求和。

1。 選擇空白單元格以進入SUBTOTAL功能。

  1. 要計算具有相同背景顏色的所有單元格,請輸入公式 = SUBTOTAL(102,E2:E20);
  2. 要對具有相同背景顏色的所有單元格求和,請輸入公式 = SUBTOTAL(109,E2:E20);


注意:在兩個公式中,E2:E20是包含彩色單元格的Amount列,您可以根據需要更改它們。

2。 選擇表格的標題,然後單擊 數據 > 過濾。 看截圖:

3。 單擊“過濾器”圖標 在Amount列的標題單元格中,單擊 通過彩色濾光片 和您將連續計算的指定顏色。 看截圖:

過濾後,兩個SUBTOTAL公式都會自動計算和計算Amount列中所有已過濾的顏色單元格。 看截圖:

注意:此方法要求您將計算或總和的彩色單元格位於同一列中。


通過GET.CELL函數對彩色單元格進行計數或求和

在這個方法中,我們將使用GET.CELL函數創建一個命名範圍,獲取單元格的顏色代碼,然後在Excel中輕鬆地按顏色代碼計算或求和。 請做如下:

1。 點擊 公式 > 定義名稱。 看截圖:

2。 在New Name對話框中,請按以下屏幕截圖顯示:
(1)在“名稱”框中鍵入名稱;
(2)輸入公式 = GET.CELL(38,Sheet4!$ E2) 在參考框中(注意: 在公式, 38 表示返回單元格代碼,和 Sheet4!$ E2 是Amount列中的第一個單元格,除了您需要根據表數據更改的列標題。)
(3)點擊 OK 按鈕。

3。 現在將新的Color列添加到原始表的右側。 接下來輸入公式 = NUM​​COLOR ,並拖動自動填充處理以將公式應用於“顏色”列中的其他單元格。 看截圖:
注意: 在公式, NUMCOLOR 是我們在第一個2步驟中指定的命名範圍。 您需要將其更改為您設置的指定名稱。

現在,“數量”列中每個單元格的顏色代碼將在“顏色”列中返回。 看截圖:

4。 複製並列出活動工作表中空白範圍內的填充顏色,並在其旁邊鍵入公式,如下面的屏幕截圖所示:
A.要按顏色計算單元格,請輸入公式 = COUNTIF($ F $ 2:$ F $ 20,NUMCOLOR);
B.要按顏色對單元格求和,請輸入公式 = SUMIF($ F $ 2:$ F $ 20,NUMCOLOR,$ E $ 2:$ E $ 20).

注意:在兩個公式中, $ F $ 2:$ F $ 20 是Color列, NUMCOLOR 是指定的命名範圍, $ E $ 2:$ E $ 20 是金額列,您可以根據需要更改它們。

現在,您將看到Amount列中的單元格已計數,並按填充顏色求和。


使用用戶定義函數根據特定填充顏色計算和求和單元格


假設有色單元在如下面的屏幕截圖所示的範圍內散佈,上述兩種方法都不能對彩色單元格進行計數或求和。 這裡,這個方法將引入一個VBA來解決這個問題。

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,並將以下代碼粘貼到模塊窗口中。

VBA:根據背景顏色計算和求和單元格:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3。 然後保存代碼,並應用以下公式:
A.計算有色單元格: ColorFunction的=(A,B:C,FALSE)
B.總和有色細胞: ColorFunction的=(A,B:C,TRUE)

注意:在上面的公式中, A 是您想要計算計數和總和的特定背景顏色的單元格 公元前 是要計算計數和總和的單元格範圍。

4。 以下面的截圖為例,輸入公式ColorFunction的=(A1,A1:D11,FALSE) 計算黃色細胞。 並使用公式 ColorFunction的=(A1,A1:D11,TRUE) 總結黃色細胞。 看截圖:

5。 如果您想對其他顏色的單元格進行計數和求和,請重複步驟4。 然後你會得到以下結果:


使用Kutools函數根據特定填充顏色計算和求和單元格

Kutools for Excel還支持一些有用的功能,以幫助Excel用戶進行特殊計算,按單元格背景顏色計算,按字體顏色求和等。

Kutools for Excel - 包括300以上的便捷Excel工具。 全功能免費試用60天,無需信用卡! 現在得到它!

1。 選擇放置計數結果的空白單元格,然後單擊 Kutools > Kutools函數 > 統計與數學 > COUNTBYCELLCOLOR。 看截圖:

2。 在“函數參數”對話框中,請指定計算顏色單元格的範圍 參考 框,選擇由指定的背景顏色填充的單元格 Color_index_nr 框中,然後單擊 OK 按鈕。 看截圖:

筆記:
(1)您也可以鍵入指定的Kutools函數 = COUNTBYCELLCOLOR($ A $ 1:$ E $ 20,G2) 在空白單元格或公式欄中直接得到計數結果;
(2)點擊 Kutools > Kutools函數 > 統計與數學 > SUMBYCELLCOLOR 或鍵入 = SUMBYCELLCOLOR($ A $ 1:$ E $ 20,G2) 在空白單元格中直接根據指定的背景顏色對單元格求和。
應用 COUNTBYCELLCOLOR - SUMBYCELLCOLOR 分別為每種背景顏色設置功能,您將得到如下截圖所示的結果:

Kutools函數 包含許多內置函數,以幫助Excel用戶輕鬆計算,包括 計數/總和/平均可見細胞, 按單元格顏色計算/求和, 按字體顏色計算/總和, 計算字符, 按字體粗體計數等等。 免費試用!


使用Kutools for Excel根據特定填充顏色計算和求和單元格

使用上述用戶自定義功能,需要逐個輸入公式,如果有很多不同的顏色,這種方法會很繁瑣和耗時。 但如果你有 Kutools for Excel按顏色計數 實用程序,您可以快速生成彩色單元格的報告。 您不僅可以對有色單元進行計數和求和,還可以獲取有色域的平均值,最大值和最小值。

Kutools for Excel - 包括300以上的便捷Excel工具。 全功能免費試用60天,無需信用卡! 現在得到它!

1。 選擇要使用的範圍,然後單擊 Kutools Plus > 按顏色計數,看截圖:

2。 而在中 按顏色計數 對話框,請按以下屏幕截圖顯示:
(1)選擇 標準格式 來自 顏色方法 下拉列表;
(2)選擇 背景 來自 計數類型 下拉列表。
(3)單擊“生成報告”按鈕。

注意:要按特定的條件格式顏色對彩色單元格進行計數和求和,請選擇 條件格式 來自 顏色方法 在上面的對話框中下拉列表,或選擇 標準和條件格式 從下拉列表中計算由指定顏色填充的所有單元格。

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

按顏色計數 feature按背景顏色或字體顏色計算(Count,Sum,Average,Max等)單元格。 免費試用!


相關文章:

如何根據Excel中的字體顏色計算/求和單元格?


演示:基於背景計算和求和單元格,條件格式化顏色:

在這個視頻中, Kutools 標籤和 Kutools Plus 標籤是通過添加 Kutools for Excel。 如果需要,請點擊 點擊瀏覽,獲取更多資訊 有一個60天的免費試用沒有限制!



推薦的生產力工具

Office Tab

金星1 帶上方便的選項卡到Excel和其他Office軟件,就像Chrome,Firefox和新的Internet Explorer一樣。

Kutools for Excel

金星1 驚人! 提高您在5分鐘的生產力。 不需要任何特殊技能,每天保存兩個小時!

金星1 300 Excel的新功能,讓Excel變得簡單而強大:

  • 合併單元格/行/列而不丟失數據。
  • 合併和合併多個工作表和工作簿。
  • 比較範圍,複製多個範圍,將文本轉換為日期,單位和貨幣轉換。
  • 按顏色計算,分頁小計,高級分類和超級篩選,
  • 更多選擇/插入/刪除/文本/格式/鏈接/評論/工作簿/工作表工具...

Excel的Kutools屏幕截圖

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ahsan · 5 years ago
    Hi,
    If I change the cell color using another formula and then ask to calculate sum up with the above formula then it doesn't work. Can you please help me resolving the problem?
  • To post as a guest, your comment is unpublished.
    Josh · 5 years ago
    I am not sure if this question has already been asked, but how do you use the colorfunction when looking to find the sum of colored cells across many worksheets within the same Excel file? Thanks.
  • To post as a guest, your comment is unpublished.
    Brenda · 5 years ago
    Very Helpful. Thank you for sharing!!! :-)
  • To post as a guest, your comment is unpublished.
    Madhuka · 5 years ago
    Thanks and very useful !
  • To post as a guest, your comment is unpublished.
    Modeste · 5 years ago
    I am trying to add this macro to an exisiting excel macro enabled worksheet (.xlsm). However "colorfunction" is not recognized (pop up "Ambiguous Function" comes up)when I try to add it to a separate worksheet in the exisiting .xlsm file. It does work when I add it to a new file from scratch but not when I try to incorporate it in an exisiting .xlsm file. Is there something I'm missing? Please assist.