MS Excel-根据有色细胞数量的百分比填充细胞? [英] MS Excel- Populate cell with a percentage based on number of cells coloured?
本文介绍了MS Excel-根据有色细胞数量的百分比填充细胞?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望根据有多少单元格的颜色填充一个单元格值。
I'm looking to populate a cell value with a number based on how many cells have the colour green.
示例1:
A1 B1 C1 D1 (E1- Answer '15%' will appear here)
G NG NG NG (G= Green, NG= Not Green)
另一个例子:
A1 B1 C1 D1 (E1- Answer '30%' will appear here)
G G NG NG (G= Green, NG= Not Green)
= IF(GetFillColor(A1)= 4,1和IF(GetFillColor(A1:B1)= 4 ,2),和IF(GetFillColor(A1:C1)= 4,3)和IF(GetFillColor(A1:C1)= 4,4,0))
GetFillColour:
GetFillColour:
Function GetFillColor(Rng As Range) As Long
GetFillColor = Rng.Interior.ColorIndex
End Function
任何帮助都会非常感谢,谢谢!
Any help would be much appreciated, Thanks!
推荐答案
如果您不介意使用 helper 单元格(并且没有VBA):
If you don't mind using helper cells (and no VBA):
- 创建一个命名公式:
= GET.CELL(38,OFFSET(INDIRECT(RC,FALSE), - 1 ,0))
我称之为 CellColour 。 - 将公式
= CellColour
放置在有色单元格下方1个单元格。
公式中的偏移量告诉它看看上面的那一行。 - 公式将显示绿色单元格的数字10(RGB = 0,255,0)。
- 现在使用一个简单的公式来计算百分比:
= COUNTIF(A2:D2,10)/ COUNTA(A2:D2)
- Create a named formula:
=GET.CELL(38,OFFSET(INDIRECT("RC",FALSE),-1,0))
I've called mine CellColour. - Place the formula
=CellColour
1 cell below your coloured cells.
The Offset in the formula tells it to look at the row above. - The formula will display the number 10 for cells coloured green (RGB = 0,255,0).
- Now use a simple formula to calculate the percentage:
=COUNTIF(A2:D2,10)/COUNTA(A2:D2)
您需要按F9更新公式结果。
You'll need to press F9 to update the formula result.
http://www.mrexcel。 com / forum / excel-questions / 20611-info-only-get-cell-arguments.html
这篇关于MS Excel-根据有色细胞数量的百分比填充细胞?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文