Note: The other languages of the website are Google-translated. Back to English
English English

如何在Excel中根據背景色對單元格進行計數和求和?

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


通過過濾器和小計對有色單元進行計數和求和

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

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

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


備註:在兩個公式中,E2:E20是包含彩色單元格的Amount列,您可以根據需要進行更改。

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

3。 點擊過濾器圖標  在“金額”列的標題單元格中,然後單擊 通過彩色濾光片 以及您將依次計算的指定顏色。 看截圖:

過濾後,兩個SUBTOTAL公式都會自動對“金額”列中所有過濾的顏色單元進行計數和求和。 看截圖:

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

一鍵計算,匯總和平均Excel中的彩色單元格

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

Excel的Kutools -包括300多個用於Excel的便捷工具。 全功能30天免費試用,無需信用卡! 立即行動吧!

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

在此方法中,我們將使用GET.CELL函數創建命名範圍,獲取單元格的顏色代碼,然後在Excel中輕鬆按顏色代碼進行計數或求和。 請執行以下操作:

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

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

3。 現在,在原始表的右側添加一個新的“顏色”列。 接下來輸入公式 = NumColor ,然後拖動自動填充手柄將公式應用於“顏色”列中的其他單元格。 看截圖:
備註: 在公式, 數字顏色 是我們在前兩個步驟中指定的命名範圍。 您需要將其更改為您設置的指定名稱。

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

4。 複製並列出活動工作表中空白區域中的填充顏色,然後在其旁邊鍵入公式,如下所示:
A.要按顏色對單元格進行計數,請輸入公式 = COUNTIF($ F $ 2:$ F $ 20,NumColor);
B.要按顏色對單元格求和,請輸入公式 = SUMIF($ F $ 2:$ F $ 20,NumColor,$ E $ 2:$ E $ 20).

備註:在兩個公式中 $ F $ 2:$ F $ 20 是“顏色”列, 數字顏色 是指定的命名範圍, $ E $ 2:$ E $ 20 是“金額”列,您可以根據需要進行更改。

現在,您將看到“數量”列中的單元格已被計數並通過其填充顏色求和。


使用用戶定義的功能根據特定的填充顏色對單元格進行計數和求和

假設有色單元散佈在如下所示的屏幕快照範圍內,則上述兩種方法都無法對有色單元進行計數或求和。 在此,此方法將介紹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用戶進行特殊計算,例如按單元格背景顏色計數,按字體顏色求和等等。

Excel的Kutools -包括300多個用於Excel的便捷工具。 全功能30天免費試用,無需信用卡! 立即免費試用!

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

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

筆記:
(1)您也可以輸入指定的Kutools函數 = COUNTBYCELLCOLOR($ A $ 1:$ E $ 20,G2)  直接在空白單元格或公式欄中獲取計數結果;
(2)點擊 庫工具 > Kutools函數 > 統計與數學 > 超級手機顏色 或輸入 = SUMBYCELLCOLOR($ A $ 1:$ E $ 20,G2) 直接在空白單元格中根據指定的背景色求和單元格。
應用 單色 超級手機顏色 分別為每種背景色提供功能,您將獲得如下屏幕截圖所示的結果:

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


使用Kutools for Excel根據特定的填充顏色對單元格進行計數和求和

使用上面的用戶定義函數,您需要一個一個地輸入公式,如果有很多不同的顏色,此方法將很繁瑣且耗時。 但是如果你有 Excel的Kutools按顏色計數 實用程序,您可以快速生成彩色單元格的報告。 您不僅可以對有色單元格進行計數和求和,還可以獲取有色範圍的平均值,最大值和最小值。

Excel的Kutools -包括300多個用於Excel的便捷工具。 全功能30天免費試用,無需信用卡! 立即免費試用!

1。 選擇您要使用的範圍,然後單擊 Kutools 加 > 按顏色計數,請參見屏幕截圖:

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

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

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

按顏色計數 功能通過背景顏色或字體顏色計算(計數,總和,平均值,最大值等)單元格。 免費試用!


相關文章:


演示:根據背景和條件格式顏色對單元格進行計數和求和:


Excel的Kutools 包括300多種用於Excel的便捷工具,可以在30天之內免費試用,不受限制。 立即下載並免費試用!

最佳辦公效率工具

Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50%,並減少數百次鼠標單擊!
officetab底部
按評論排序
留言 (235)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
非常有用的工具,非常感謝
網站主持人對此評論進行了最小化
插入函數後,當我們更改值時,總和不會自動更新,任何建議
網站主持人對此評論進行了最小化
Alt-Ctrl-F9 將重新計算
網站主持人對此評論進行了最小化
如果其他人正在使用該文件並且他們不知道 Alt-Ctrl-F9,您可以添加和更新按鈕。 只需將這行代碼添加到宏“Application.CalculateFull”中。 它與剛剛編程到按鈕中的 Alt-Ctrl-F9 相同。
網站主持人對此評論進行了最小化
En que parte agrego la linea para crear el boton
網站主持人對此評論進行了最小化
在第一行正下方添加第二行,表示 Application.volitile 它使其在更新某些內容後重新計算
網站主持人對此評論進行了最小化
我試著把 Application.Volatile 在第一行沒有發生任何事情:(
網站主持人對此評論進行了最小化
[quote]在第一行正下方添加第二行,表示 Application.volitile 它使它在更新某些內容後重新計算通過12345678998765432154[/quote] Application.Volatile 是正確的函數不是 Application.volitile
網站主持人對此評論進行了最小化
我沒有看到任何說 application.volatile 的行。 我在哪裡可以找到它?
網站主持人對此評論進行了最小化
我用 ColorFunction 公式雙擊單元格,然後按 Enter。 它更新了。
網站主持人對此評論進行了最小化
很有用。 謝謝
網站主持人對此評論進行了最小化
我也試過這個公式。 適用於第一排,不適用於第二排和第三排。 ???
網站主持人對此評論進行了最小化
我同意賈羅德的觀點。 我的函數的前兩行說: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Application.Volatile 問題是只有 Alt-Ctrl-F9 會重新計算。 我們正在尋找更自動化的解決方案。 想法?
網站主持人對此評論進行了最小化
像這樣嘗試: 1. 確保您已按照描述保存函數 2. 請允許我使用示例 * 我有數百行數據 * 我使用 A 到 AB 列的更多數據 * 每當我遇到問題時數據,我用黃色突出顯示 * 我使用這個很酷的公式來“計算”每行的突出顯示數量 3. 如何計算每行突出顯示的數量 a。 數字 單元格範圍可能有您想要計算(或求和)的亮點 * 對我來說,我想計算每行(我的範圍)上標記的亮點的數量 b。 選擇一個單元格,您將為我報告計數(或總和)*,我將其放在數據的最右側...在 AE c 列中。 在您在 b 項(上圖)中選擇的單元格中插入以下公式 =colorfunction(AE3,A3:AB3,FALSE) * 對我來說,我將此公式放在單元格 AE3 中(行尾)並且我突出顯示了相同公式單元格 黃色 d. 然後,我將這個公式複製到我的所有數據行 4。我觀察到 a。 沒有計數。 (壞的)這裡有 5 個關鍵問題: 我按下 CTL+ ALT + F9 。 普雷斯托!!! 有用。! 希望這個玩笑能有所幫助。 乾杯:ABCD 1 2 3 4
網站主持人對此評論進行了最小化
非常好,非常感謝!!!
網站主持人對此評論進行了最小化
很有用的文章,非常感謝
網站主持人對此評論進行了最小化
精彩的!!! 非常感謝!!!
網站主持人對此評論進行了最小化
太棒了,非常感謝
網站主持人對此評論進行了最小化
這個工具不錯,謝謝分享! 我確實有一個問題:我注意到當您將單元格顏色從一種顏色更改為另一種顏色時,宏公式不會自行更新。 有沒有辦法讓它在單元格顏色發生變化時自動更新? 如果我雙擊帶有加法公式的單元格並按 Enter 鍵,它會更新,但我有一張包含許多這些單元格的工作表,並且不想每次都手動更新它。
網站主持人對此評論進行了最小化
謝謝您的回复,很抱歉,在目前的情況下,VBA代碼無法解決您指出的問題,需要手動更新。
網站主持人對此評論進行了最小化
我還發現使用格式刷更新單元格顏色會導致公式按預期工作。 仍然不完美。
網站主持人對此評論進行了最小化
你好,我試過了。 並且工作表只需要一個“Enter”無論哪個單元格。 因此,在為您的單元格著色後,只需在彩色單元格上鍵入一個備忘錄並“輸入”
網站主持人對此評論進行了最小化
嘗試 CTL+ALT+f9 它將立即使用添加公式更新所有單元格。 不是很自動,但至少比必須單獨單擊每個帶有添加公式的單元格要好。
網站主持人對此評論進行了最小化
這對我沒有任何作用:(
網站主持人對此評論進行了最小化
你好,報錯。 編譯錯誤,模棱兩可的錯誤來了。
網站主持人對此評論進行了最小化
我應用了公式,但單元格顯示名稱? 我需要解決的問題
網站主持人對此評論進行了最小化
您好 RAMON,可能您沒有將上述代碼複製到模塊中。 您必須先將代碼複製到模塊中並保存,然後再應用公式。 你可以試試看。 如果對您沒有幫助,請告訴我。
網站主持人對此評論進行了最小化
你好,它第一次工作,但是現在,當我必須在總和中添加一個單元格時......我嘗試再次引入代碼,保存,再次編寫公式......它說“名字?”
網站主持人對此評論進行了最小化
我遇到了同樣的問題——起初工作得很好,但在添加另一個單元格後出現了 NAME 錯誤。 已嘗試刪除模塊並重新添加,重新輸入功能...不高興。
網站主持人對此評論進行了最小化
您可能需要再次啟用 Marcos。 應根據 excel 版本在屏幕頂部閃爍。 :)
網站主持人對此評論進行了最小化
Skyyang,如果我給你發一份文件,你能幫我嗎? 麥克風
網站主持人對此評論進行了最小化
非常感謝! 這是天才,感謝您的幫助!
網站主持人對此評論進行了最小化
非常感謝,我整天都在尋找一個成功的結果,現在我們有了一個:)
網站主持人對此評論進行了最小化
謝謝。 如前所述,它對我有用。 但是,在我下次重新打開文件後,使用此公式的所有單元格都顯示錯誤。 我不得不再次重新復制編碼。 有什麼我想念的嗎? 我需要與其他人共享文件,他們將無法“解決”問題。 謝謝,克爾。
網站主持人對此評論進行了最小化
您需要將其保存為 excel 宏工作簿
網站主持人對此評論進行了最小化
做得好..但是..它如何在條件格式單元格顏色中工作???
網站主持人對此評論進行了最小化
這太棒了,謝謝!
網站主持人對此評論進行了最小化
它做得很好!
網站主持人對此評論進行了最小化
親愛的先生,事實上這是很棒的代碼,但我遇到了一個問題,即使我們改變了單元格顏色,它也會繼續計算單元格
網站主持人對此評論進行了最小化
謝謝先生,這是一項了不起的工作,對我幫助很大。
網站主持人對此評論進行了最小化
我有一個包含許多彩色單元格的 Excel 文件。 有沒有辦法將這些顏色轉換為數據? 因此,紅色單元格中將包含“紅色”作為數據,藍色單元格中將包含“藍色”等等? 詹姆士
網站主持人對此評論進行了最小化
及時而貼切的文章,例如擊中正在尋找的目標:)謝謝
網站主持人對此評論進行了最小化
我似乎無法讓它工作。 它僅適用於手動著色的單元格嗎? 我需要計算通過條件格式著色的單元格,但目前它沒有發揮作用。
網站主持人對此評論進行了最小化
我和 Ian 有同樣的問題,我試圖根據條件格式設置的顏色規則對數字求和,但這似乎沒有抓住。 有什麼建議麼?
網站主持人對此評論進行了最小化
我們將嘗試在即將發布的版本中對其進行增強。 :-)
網站主持人對此評論進行了最小化
我拋出了一個 Powershell 腳本,它作為一種解決方法:“ #setup Excel $excelApp = New-Object -comobject Excel.Application $excelApp.Quit() $excelApp.Visible = $True $workbook = $excelApp.Workbooks .Open("H:\Desktop\test.xlsx")#將其更改為您的 EXCEL 文件地址。$worksheet = $workbook.Worksheets.Item("Sheet1")#如果您的工作表未調用“Sheet1”,則更改此#static variables $row = 1 $column = 1#將此變量更改為您要搜索的列 $totalRow = $worksheet.UsedRange.Rows.Count do{ $currentCell = $worksheet.cells.item($row, $column ) if($currentCell.text -eq "SEARCH_FOR_THIS") { $worksheet.cells.item($row,$column).Interior.ColorIndex = 44#改變這個數字來改變新單元格的顏色 "$row, $column =空白。 著色" } $row++ $row } while($row -lt $totalRow) "腳本完成。" "正在保存..." $excelApp.Save $excelApp.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject ($excelApp) " 此腳本將搜索您的電子表格,如果找到包含“SEARCH_FOR_THIS”的單元格(您可能應該更改腳本中的該部分),那麼它將將該單元格轉換為腳本中的任何顏色編號(當前設置為 44,一種看起來很奇怪的橙色/棕褐色)。 腳本完成後,它會嘗試告訴 Excel 它要保存,但 Excel 不是一個非常值得信賴的程序,因此會要求您單擊保存。 一旦你這樣做了,它就會關閉。 然後,打開文件備份並執行此頁面上列出的所有業務,確保我的腳本查找它的顏色與此頁面上列出的腳本查找的顏色相同。 希望這可以幫助!
網站主持人對此評論進行了最小化
傳奇! 謝謝工作的魅力
這裡還沒有評論
載入更多
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點