Excel：计算包含特定文本的单元格（精确和部分匹配）。 |
您所在的位置：网站首页 › exlc表格公式对,计算显示错误 › Excel：计算包含特定文本的单元格（精确和部分匹配）。 |
|
本教程展示了如何计算Excel中含有特定文本的单元格的数量。 你会发现精确匹配、部分匹配和过滤单元格的公式例子。 上周我们研究了如何在Excel中计算带有文本的单元格，即所有带有任何文本的单元格。 在分析大块信息时，你可能还想知道有多少单元格含有特定的文本。 本教程以简单的方式解释了如何做到这一点。 如何计算Excel中具有特定文本的单元格Microsoft Excel有一个特殊的函数来有条件地计算单元格，即COUNTIF函数。 你所要做的就是在下面提供目标文本字符串 标准 争论。 下面是一个通用的Excel公式，用于计算包含特定文本的单元格的数量。 COUNTIF(range, " 文本 ")下面的例子显示了它的作用。 假设你有一个A2:A10的项目ID列表，你想计算具有特定ID的单元格的数量，例如 "AA-01"。 在第二个参数中输入这个字符串，你会得到这个简单的公式。 =countif(a2:a10, "aa-01") 为了使你的用户能够计算带有任何给定文本的单元格，而不需要修改公式，在一个预定义的单元格中输入文本，例如D1，并提供单元格参考。 =countif(a2:a10, d1) 注意：Excel的COUNTIF函数是 不区分大小写 要区别对待大写字母和小写字母，请使用这个区分大小写的公式。 如何计算有特定文本的单元格（部分匹配）？前面例子中讨论的公式与标准完全匹配。 如果一个单元格中至少有一个不同的字符，例如最后多了一个空格，那就不是完全匹配，这样的单元格就不会被计算在内。 要查找内容中包含某些文本的单元格的数量，在你的标准中使用通配符，即代表任何序列或字符的星号（*）。 根据你的目标，公式可以是以下的一种。 计算包含特定文本的单元格，在 非常开始 : COUNTIF(range, " 文本 *")计算包含特定文本的单元格，在 任何位置 : COUNTIF(range, "* 文本 *")例如，要找到A2:A10区域中多少个以 "AA "开头的单元格，使用这个公式。 =countif(a2:a10, "aa*") 要获得在任何位置含有 "AA "的单元格的数量，请使用这个。 =countif(a2:a10, "*aa*") 为了使公式更加动态，用单元格引用取代硬编码的字符串。 计算以某些文字开始的单元格。 =COUNTIF(A2:A10, D1; "*") 计算单元格中任何地方有特定文字的单元格。 =COUNTIF(A2:A10, "*";D1;"*") 下面的屏幕截图显示了结果。 计算包含特定文本的单元格（区分大小写）。当你需要区分大写和小写字符时，COUNTIF函数将不起作用。 根据你寻找的是精确匹配还是部分匹配，你将不得不建立一个不同的公式。 对大小写敏感的公式来计算具有特定文本的单元格（精确匹配）。为了计算有某些文字识别的单元格的数量，我们将使用SUMPRODUCT和EXACT函数的组合。 总产品(--exact(" 文本 ", 范围 ))这个公式如何运作。 EXACT将范围内的每个单元格与样本文本进行比较，并返回一个TRUE和FALSE值的数组，TRUE代表完全匹配，FALSE代表所有其他单元格。 一个双连字符（称为a 双单数 )将TRUE和FALSE胁迫为1和0。 SUMPRODUCT对数组中的所有元素进行求和，这个和就是1的数量，也就是匹配的数量。例如，要获得A2:A10中包含D1中文本的单元格数量，并将大写和小写作为不同的字符处理，请使用此公式。 =sumproduct(--exact(d1, a2:a10)) See_also: 如何在Excel中改变和自动调整列宽 对大小写敏感的公式来计算具有特定文本的单元格（部分匹配）。为了建立一个对大小写敏感的公式，能够在单元格的任何地方找到感兴趣的文本字符串，我们使用了3个不同的函数。 sumproduct(--(isnumber(find(" 案文 ", 范围 ))))这个公式如何运作。 对大小写敏感的FIND函数在范围的每个单元格中搜索目标文本。 如果成功，该函数返回第一个字符的位置，否则返回#VALUE！错误。 为了清楚起见，我们不需要知道确切的位置，任何数字（相对于错误）意味着该单元格包含目标文本。 ISNUMBER函数处理由FIND返回的数字和错误数组，并将数字转换为TRUE，其他都转换为FALSE。 一个双单数（--）将逻辑值胁迫为1和0。 SUMPRODUCT对1和0的数组进行求和，并返回包含指定文本作为其内容一部分的单元格的数量。为了在真实的数据上测试该公式，让我们找出A2:A10中多少个单元格包含D1中输入的子串。 =sumproduct(--(isnumber(find(d1, a2:a10))))) 而这将返回3个计数（单元格A2、A3和A6）。 如何统计有特定文字的过滤单元格要计算 可见的项目 在一个过滤的列表中，你需要使用4个或更多的函数组合，这取决于你是想要一个精确的还是部分的匹配。 为了使例子更容易理解，让我们先快速看一下源数据。 假设，你有一个表，上面有 订单ID 在B栏和 数量 目前，你只对大于1的数量感兴趣，并相应地过滤了你的表格。 问题是--你如何计算具有特定id的过滤单元格？ 计算具有特定文本（完全匹配）的过滤单元格的公式要计算内容与样本文本字符串完全匹配的过滤单元格，请使用以下公式之一。 See_also: 如何解决 "无法启动Microsoft Office Outlook "的问题=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A";ROW(A2:A10)), --(B2:B10=F1) =sumproduct(subtotal(103, offset(a2:a10, row(a2:a10) - min(row(a2:a10)), ,1)), --(b2:b10=f1) 其中F1为样本文本，B2:B10为要计算的单元格。 这些公式是如何工作的。 在这两个公式的核心，你要进行2次检查。 识别可见行和隐藏行。 为此，你可以使用SUBTOTAL函数与 功能_num 要向SUBTOTAL提供所有单个单元格的引用，可以使用INDIRECT（在第一个公式中）或OFFSET、ROW和MIN的组合（在第二个公式中）。 由于我们的目的是定位可见和隐藏的行，所以引用哪一列（在我们的例子中是A）并不重要。 这个操作的结果是一个1和0的数组，其中1代表可见行和零--隐藏的行。 查找包含给定文本的单元格。 为此，将样本文本（F1）与单元格范围（B2:B10）进行比较。 该操作的结果是一个TRUE和FALSE值的数组，在双单数运算符的帮助下，它们被强制为1和0。最后，SUMPRODUCT函数将两个数组中相同位置的元素相乘，然后将得到的数组相加。 因为乘以0会得到0，所以只有两个数组中都有1的单元格在最后的数组中才有1。 1的总和是包含指定文本的过滤单元格的数量。 计算具有特定文本的过滤单元格的公式（部分匹配）。要计算包含某些文本的过滤单元格作为单元格内容的一部分，请按以下方式修改上述公式。 不要将样本文本与单元格范围进行比较，而是通过使用ISNUMBER和FIND搜索目标文本，如前面一个例子中所解释的那样。 =SUMPRODUCT(SUBTOTAL(103, INDIRECT("A";ROW(A2:A10)), --(ISNUMBER(FIND(F1, B2:B10))))) =sumproduct(subtotal(103, offset(a2:a10, row(a2:a10) - min(row(a2:a10)), ,1)), --(isnumber(find(f1, b2:b10))))) 因此，公式将在单元格中的任何位置找到一个给定的文本字符串。 注意：SUBTOTAL函数与103中的 功能_num 参数，确定所有隐藏的单元格，过滤掉的和手动隐藏的。 结果，上述公式只计算了 可见的细胞 而不管隐藏的单元格是怎样的。 要想只排除被过滤掉的单元格，而包括手动隐藏的单元格，可以用3来表示 功能_num . 这就是如何在Excel中计算有特定文字的单元格的数量。 感谢你的阅读，希望下周在我们的博客上看到你的身影! 可用的下载用Excel公式计算有特定文字的单元格 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |