vba 计算具有相同背景颜色的单元格列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count a list of cells with the same background color
提问by FortunateDuke
Each cell contains some text and a background color. So I have some cells that are blue and some that are red. What function do I use to count the number of red cells?
每个单元格包含一些文本和背景颜色。所以我有一些蓝色的单元格和一些红色的单元格。我用什么函数来计算红细胞的数量？
I have tried =COUNTIF(D3:D9,CELL("color",D3))with no success (Where D3is red).
我试过=COUNTIF(D3:D9,CELL("color",D3))没有成功（D3红色在哪里）。
回答by Sean
Excel has no way of gathering that attribute with it's built-in functions. If you're willing to use some VB, all your color-related questions are answered here:
Excel 无法使用其内置函数收集该属性。如果您愿意使用一些 VB，您所有与颜色相关的问题都在这里得到解答：
http://www.cpearson.com/excel/colors.aspx
http://www.cpearson.com/excel/colors.aspx
Example form the site:
网站示例：
The SumColor function is a color-based analog of both the SUM and SUMIF function. It allows you to specify separate ranges for the range whose color indexes are to be examined and the range of cells whose values are to be summed. If these two ranges are the same, the function sums the cells whose color matches the specified value. For example, the following formula sums the values in B11:B17 whose fill color is red.
=SUMCOLOR(B11:B17,B11:B17,3,FALSE)
SumColor 函数是 SUM 和 SUMIF 函数的基于颜色的模拟。它允许您为要检查其颜色索引的范围和要对其值求和的单元格范围指定单独的范围。如果这两个范围相同，则该函数对颜色与指定值匹配的单元格求和。例如，以下公式对 B11:B17 中填充颜色为红色的值求和。
=SUMCOLOR(B11:B17,B11:B17,3,FALSE)
回答by Graham
The worksheet formula, =CELL("color",D3)returns 1if the cell is formatted with color for negative values (else returns 0).
如果单元格的格式为负值的颜色，则工作表公式=CELL("color",D3)返回1（否则返回0）。
You can solve this with a bit of VBA. Insert this into a VBA code module:
你可以用一点 VBA 来解决这个问题。将其插入到 VBA 代码模块中：
Function CellColor(xlRange As Excel.Range)
CellColor = xlRange.Cells(1, 1).Interior.ColorIndex
End Function
Then use the function =CellColor(D3)to display the .ColorIndexof D3
然后使用函数=CellColor(D3)来显示.ColorIndex的D3
回答by Stan Towianski
I just created this and it looks easier. You get these 2 functions:
我刚刚创建了这个，看起来更容易。你得到这两个函数：
=GetColorIndex(E5) <- returns color number for the cell
from (cell)
来自（单元格）
=CountColorIndexInRange(C7:C24,14) <- returns count of cells C7:C24 with color 14
from (range of cells, color number you want to count)
from（单元格范围，要计算的颜色数）
example shows percent of cells with color 14
示例显示颜色为 14 的单元格百分比
=ROUND(CountColorIndexInRange(C7:C24,14)/18, 4 )
Create these 2 VBA functions in a Module (hit Alt-F11)
在模块中创建这 2 个 VBA 函数（按 Alt-F11）
open + folders. double-click on Module1
打开+文件夹。双击模块 1
Just paste this text below in, then close the module window (it must save it then):
只需将此文本粘贴到下面，然后关闭模块窗口（然后必须保存它）：
Function GetColorIndex(Cell As Range)
GetColorIndex = Cell.Interior.ColorIndex
End Function
Function CountColorIndexInRange(Rng As Range, TestColor As Long)
Dim cnt
Dim cl As Range
cnt = 0
For Each cl In Rng
If GetColorIndex(cl) = TestColor Then
Rem Debug.Print ">" & TestColor & "<"
cnt = cnt + 1
End If
Next
CountColorIndexInRange = cnt
End Function
回答by jsxt
I was needed to solve absolutely the same task. I have divided visually the table using different background colors for different parts. Googling the Internet I've found this page https://support.microsoft.com/kb/2815384. Unfortunately it doesn't solve the issue because ColorIndex refers to some unpredictable value, so if some cells have nuances of one color (for example different values of brightness of the color), the suggested function counts them. The solution below is my fix:
我需要解决完全相同的任务。我对不同的部分使用不同的背景颜色在视觉上划分了表格。谷歌搜索互联网我发现这个页面https://support.microsoft.com/kb/2815384。不幸的是，它并没有解决问题，因为 ColorIndex 指的是一些不可预测的值，所以如果某些单元格具有一种颜色的细微差别（例如颜色的不同亮度值），建议的函数将对它们进行计数。下面的解决方案是我的修复：
Function CountBgColor(range As range, criteria As range) As Long
Dim cell As range
Dim color As Long
color = criteria.Interior.color
For Each cell In range
If cell.Interior.color = color Then
CountBgColor = CountBgColor + 1
End If
Next cell
End Function
回答by qoheleth
Yes VBA is the way to go.
是的 VBA 是要走的路。
But, if you don't need to have a cell with formula that auto-counts/updates the number of cells with a particular colour, an alternative is simply to use the 'Find and Replace' function and format the cell to have the appropriate colour fill.
但是，如果您不需要具有自动计数/更新具有特定颜色的单元格数量的公式的单元格，另一种方法是使用“查找和替换”功能并设置单元格格式以具有适当的颜色填充。
Hitting 'Find All' will give you the total number of cells found at the bottom left of the dialogue box.
点击“查找全部”将为您提供在对话框左下角找到的单元格总数。
This becomes especially useful if your search range is massive. The VBA script will be very slow but the 'Find and Replace' function will still be very quick.
如果您的搜索范围很大，这将变得特别有用。VBA 脚本会很慢，但“查找和替换”功能仍然会很快。
