提示:其他語言是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.
    Laxmannarayanan · 5 years ago
    Wonderful!!! Thank you so much!!!
  • To post as a guest, your comment is unpublished.
    imamoglu · 5 years ago
    Very useful article, thanks a lot
  • To post as a guest, your comment is unpublished.
    Petr · 5 years ago
    Very good, thanks a lot !!!
  • To post as a guest, your comment is unpublished.
    Gowrisankar · 5 years ago
    Very useful tool , Thank you verymuch
    • To post as a guest, your comment is unpublished.
      HR · 4 years ago
      Very useful. Thank you
      • To post as a guest, your comment is unpublished.
        Aniko · 3 years ago
        I also tried the formula.
        Works in the first row and does not with second and third.

        ????
        • To post as a guest, your comment is unpublished.
          Mark · 2 years ago
          Try it like this:

          1. be certain you have the function saved as described

          2. permit me to use an example
          * I have hundreds of rows of data
          * I use columns A to AB with more data
          * whenever I have an issue with my data, I highlight it in yellow
          * I use this cool formula to 'count' the number of highlights on each row

          3. How to count the number of my highlights per row
          a. figure[u] the range of cells[/u]that could have highlights that you want to count (or sum)
          * for me, I want to COUNT the quantity of my flagged highlights on each row (my range)
          b. pick a cell where you will report the count (or sum)
          * for me, I placed it on the far right of my data...in column AE
          c. insert the following formula in the cell you chose in item b (above)
          =colorfunction(AE3,A3:AB3,FALSE)
          * for me, I placed this formula in cell AE3 (the end of my row) AND AND I highlighted the same formula cell Yellow
          d. I then copied this formula down for all my data rows

          4. I observed
          a. no counts were made. (bad)

          5 KEY ISSUE HERE:
          a. I pressed CTL+ ALT + F9
          . PRESTO !!! it works.!

          hope this banter is of some help.

          Cheers




          do this:

          A B C D
          1
          2
          3
          4
        • To post as a guest, your comment is unpublished.
          Mark · 2 years ago
          I agree with Jarod.

          The first two lines of my function says:

          Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
          Application.Volatile

          The problem is that only a Alt-Ctrl-F9 will re-calculate. We're looking for a more automated solution. Ideas?
    • To post as a guest, your comment is unpublished.
      Gowrisankar · 5 years ago
      After inserting the function , when we change the value the sum not automatically udpated , any suggestion
      • To post as a guest, your comment is unpublished.
        Jacqui · 2 years ago
        I double clicked the cell with the ColorFunction formula, and then pressed Enter. It updated.
      • To post as a guest, your comment is unpublished.
        12345678998765432154 · 3 years ago
        Add a second line directly below the first that says Application.volitile it makes it recalculate after something is updated
        • To post as a guest, your comment is unpublished.
          abrugg · 2 years ago
          [quote name="12345678998765432154"]Add a second line directly below the first that says Application.volitile it makes it recalculate after something is updated[/quote]

          Application.Volatile is the correct function not Application.volitile
          • To post as a guest, your comment is unpublished.
            Jo · 2 years ago
            I don't see any line that says application.volatile.
            Where do I find it?
        • To post as a guest, your comment is unpublished.
          Jarod · 3 years ago
          I tried to put [b]Application.Volatile[/b] unde the first line and nothing hapens :(
      • To post as a guest, your comment is unpublished.
        WestwoodMike · 3 years ago
        Alt-Ctrl-F9 will re-calculate
        • To post as a guest, your comment is unpublished.
          Mike Brannigan · 1 years ago
          You can add and update button if someone else is using the file and they don't know about the Alt-Ctrl-F9. just add this line of code to the macro "Application.CalculateFull". it's that same as Alt-Ctrl-F9 just programmed into a button.