如何在excel中计数不同字体颜色的文本 [英] How to count up text of a different font colour in excel

问题描述

我有一个从另一个数据库导出到excel的名称列表。感兴趣的列表中的名称以红色字体突出显示。我想要一个计数方法，即约翰·史密斯总共出现5次，但5次中有3次，他的名字以红色字体突出显示。所以我想看看他的名字有多少是红色的。

我知道如何搜索他的名字的所有实例= COUNTIF（A1：A100，John Smith）

我也有帮助创建一个VB函数，它计算所有红色值（= SumRed）（一旦颜色索引被指定）在工作表中使用这个：

 函数SumRed（MyRange As Range）
 SumRed = 0 
对于每个单元格在MyRange 
如果cell.Font.Color = 255然后
 SumRed = SumRed + cell.Value 
结束如果
下一个单元格
结束功能
  

我找不到一种组合两个计数条件的方法。任何帮助将不胜感激！

解决方案

你不需要VBA，但如果你想要VBA解决方案，那么你可以与其他两个答案一起去。 ：）

我们可以使用Excel公式来查找单元格的字体颜色。请看这个例子。

我们将使用XL4宏。

1. 打开姓名经理


2. 提供名称。说 FontColor


3. 在引用到 = GET.CELL（24，OFFSET（INDIRECT（ RC，FALSE），0，-1）），然后单击确定

公式说明

语法是

  GET.CELL（type_num，reference ）
 
 Type_num是一个数字，用于指定所需的单元格信息类型。 
引用是单元格引用
  

在上面的公式中，数字 24 给出单元格中第一个字符的字体颜色，范围为1到56.如果字体颜色是自动的，则返回0.因此，缺点。确保整个字体颜色为红色。我们可以使用64，但是这不能正常工作。

OFFSET（INDIRECT（RC，FALSE） -1）是指左侧的直接单元格。

现在在单元格中输入此公式 = IF AND（Fontcolor = 3，B1 =John Smith），1,0）并将其复制。

注意：公式必须在包含文本的单元格右侧输入。

Screentshot

编辑（10/12/2013）

要计数具有特定背景颜色的单元格，请参阅THIS 链接

I have a list of names that has been exported from another database into excel. The names in the list that are of interest are highlighted in red font. I would like a way to count it, i.e. John Smith appears 5 times in total in a column but 3 of the 5 times, his name comes up highlighted in red font. So I would like to see how many instances of his name comes up red.

I know How to search all instances of his name e.g. =COUNTIF(A1:A100,"John Smith ")

I've also had help in creating a VB function which counts all values that are red (=SumRed) (once the colour index is specified) in a worksheet by using this:

Function SumRed(MyRange As Range)
    SumRed = 0
For Each cell In MyRange
    If cell.Font.Color = 255 Then
        SumRed = SumRed + cell.Value
    End If
Next cell
End Function

I just can't find a way to combine the two counting conditions. Any help would be much appreciated!

解决方案

You don't need VBA for this but still if you want VBA Solution then you can go with any of the other two answers. :)

We can use Excel formula to find the Font Color of a cell. See this example.

We will be using XL4 macros.

1. Open the Name Manager
2. Give a name. Say FontColor
3. Type this formula in Refers To =GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1)) and click OK

Explanation of the formula

The Syntax is

GET.CELL(type_num, reference)

Type_num is a number that specifies what type of cell information you want.
reference is the cell reference

In the above formula the number 24 gives you the font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0. And Hence the drawback. Ensure that the entire font color is red. We could have used 64 but that is not working properly.

OFFSET(INDIRECT("RC",FALSE),0,-1) refers to the immediate cell on the left.

Now enter this formula in a cell =IF(AND(Fontcolor=3,B1="John Smith"),1,0) and copy it down.

Note: The formula has to be entered on the Right of the cell which contains the Text.

Screentshot

EDIT (10/12/2013)

To count cells with specific backcolor see THIS link

这篇关于如何在excel中计数不同字体颜色的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！