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

问题描述

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

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.

我知道如何搜索他名字的所有实例，例如=COUNTIF(A1:A100,约翰史密斯")

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

我还帮助创建了一个 VB 函数，该函数使用以下方法计算工作表中所有红色 (=SumRed) 值(一旦指定了颜色索引):

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!

推荐答案

为此您不需要 VBA，但如果您仍然需要 VBA 解决方案，那么您可以选择其他两个答案中的任何一个.:)

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. :)

我们可以使用 Excel 公式来查找单元格的字体颜色.请参阅此示例.

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

我们将使用 XL4 宏.

We will be using XL4 macros.

1. 打开名称管理器
2. 给一个名字.说 FontColor
3. 在引用 =GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1)) 中键入此公式，然后单击确定

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

公式说明

语法是

GET.CELL(type_num, reference)

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

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

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) 指的是左边的直接单元格.

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

现在在单元格 =IF(AND(Fontcolor=3,B1="John Smith"),1,0) 中输入此公式并将其复制下来.

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.

截图

编辑(10/12/2013)

要计算具有特定背景色的单元格，请参阅 这个链接

To count cells with specific backcolor see THIS link

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