☰

计算长度为 N 的字符串在单元格中出现的次数

tag-icon • tag-icon microsoft-excel worksheet-function
计算长度为 N 的字符串在单元格中出现的次数

我有一个包含订单号的单元格，以逗号分隔。（即1689519145, 2944241, 1689611938）。

我正在尝试获取一个公式来计算 7 位订单号在单元格中出现的次数（如果存在）。

我的研究让我找到了这个公式，它计算了分隔符之间出现的次数，但我不知道如何解析仅 7 位数字的订单号：=LEN(P9)-LEN(SUBSTITUTE(P9,",",""))+1

答案1

如果你要计算所有七位数字，你可以使用类似这样的方法

{=SUM(IF((1*TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))=1),99)))>999999,IF((1*TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))=1),99)))<10000000,1)))}

它计算 999,999 至 10,000,000 之间的所有数字。

或者，如果你想计算某个特定数字出现的次数，你可以将公式调整为如下形式

=SUM(IF((1*TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)))=1),99)))=2944241,1))

您还可以用参考替换特定的 7 位数字（在本例中为 2944241）。

使公式起作用的关键：

  1. 确保实例ROW(OFFSET($A$1停留ROW(OFFSET($A$1在第一行或至少在第一行
  2. 以数组形式输入公式（整个ctl+shift+enter）

公式如何运作：

  1. SUBSTITUTE(A1,",",REPT(" ",99)遍历并将每个数字拆分成 99 个空格
  2. LEN(A3)-LEN(SUBSTITUTE(A3,",",""))计算有多少个项目
  3. ROW(OFFSET($A$1,,创建具有尽可能多的项目的数组
  4. 第一个数组乘以 99
  5. 将一个添加到数组的第一个元素
  6. MID然后将数组切割成与元素数量相同的次数，每次切割不同的元素
  7. TRIM删除多余的空格
  8. 将数组乘以 1，将其从字符串转换为数字
  9. 计算SUM数组中有多少符合条件IF

功劳归于罗恩·罗森菲尔德对于大多数繁重的工作

答案2

如果你想使用 Len()/Substitute() 来做到这一点，那么

=(LEN(A2)+2-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A2&",","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|")," |||||||,","")))/LEN(" |||||||,")

除了 | 之外，您还可以放置字符串中不存在的任何其他符号。

答案3

在我看来，您似乎想要的是目标单元格中​​出现的精确、特定的七位数字的次数，而不是单元格中出现的任何长度和位数的数字的数量。 (这也可以描述为目标单元格中​​有多少个“元素”，就像人们所说的“数组元素”一样。)

并且从您研究得出的公式来看，我相信您看到了两者的答案，但没有意识到它们是不同的，因此正确的公式也必须不同，然后在这里使用错误的公式来满足您的需要。

首先，有多少个元素？每个元素都用逗号分隔，因此逗号的数量是加上一个（因为最后一个元素后面没有逗号，所以您必须再添加一个）。因此，如果您对每个逗号都使用 SUBSTITUTE()“无任何内容”（在 Excel 世界中为“”），则目标字符串的长度将比原始字符串中的逗号数量短。您的公式就是这样。但是您在其中错误地执行了“+1”部分。考虑到 SUBSTITUTE() 函数周围原本不需要的 () 对，我认为您知道将“+1”放在它们里面，但在这里输入错误。如果您将其留在外面，则必须减去它（“-1”）。从原始长度中减去较短的长度和 1，您就得到了多少个元素。

但是……如果您确实需要知道一个精确的七位数字字符串出现了多少次，仅此而已，那么您需要使用相同的概念，但略有不同。首先使用 SUBSTITUTE() 将“无”替换为要计数的字符串。从原始长度中减去该值。最后，将该差值除以您想要知道的字符串的长度：这里是七位数字，也许其他长度在其他时间和地点使用。所以：

= ( LEN(P9) - SUBSTITUTE(P9,"1234567","") ) / 7

当然，如果您在其他单元格中有该字符串，则可以将其放入公式中而不是我的“1234567”，从而使您的公式更有用！

如果您想知道 P9 中有多少个长度恰好为七位数（不多不少）的数字，您可以使用数组函数查看每个元素的长度并计算只有七位数的数字。这有点复杂，如果您需要添加查找字母并忽略这些元素，那就更复杂了。而且……您明白了。

答案4

当您以一种非常通用的方式引用字符串中的七位数字时，您似乎并没有想到要测试的数字，因此以下内容可以很好地完成：

=COUNT(  IF(  LEN(  FILTERXML("<Group><Members>"&SUBSTITUTE(A1,",","</Members><Members>")&"</Members></Group>","/Group/Members"))  =  7,  FILTERXML("<Group><Members>"&SUBSTITUTE(A1,",","</Members><Members>")&"</Members></Group>","/Group/Members"),  "")  )

该FILTERXML()部分使用标准 JOINing 创建一个带有两级标记的 HTML 字符串： Group上级或组内所有成员的容器（即字符串中的各个数字），Members下级，可以这么说。在起始字符串和结束字符串之间是一个，它将SUBSTITUTE() “ , ”字符更改为成员级标记……但先是结束标记，然后是开始标记，这似乎不自然，但被 JOINed 的开始字符串有一个开始标记，因此“中间”的第一个必须是结束标记，然后是新的开始标记，依此类推，直到结束 JOINed 字符串具有所有结束标记。第二个参数是它XPATH告诉函数如何检查和分解事物。

所有这些都进入一个内部数组，Excel 会将该数组传递给LEN()位于IF()测试中的函数，该函数用于检查长度为 7 位的数字。如果数字符合要求，它会保留该数字，如果数字不符合要求，则会变成""'s。

此时，您有零个或多个七位数字和零个或多个""项目。Excel""将这些项目视为文本，因此COUNT()将忽略它​​们，仅计算字符串中的七位数字。

从您编写它们的方式来看，似乎只有一个七位数字会出现，尽管它可能出现 10 次。在这种情况下，这里的答案就是您需要的答案。如果字符串中可能有几个不同的七位数字，并且需要检查其中一个特定的数字，那么您将需要对公式进行更多的工作。这将在测试中进行IF()：只需添加一个AND()以保存现有的长度测试，并进行测试以确定找到的数字是否等于硬编码值，或者更好的是，一个单元格地址，您可以在其中输入不同的值（如果您愿意）。

（该FILTERXML()方法是目前将细胞分割成类似单词的片段的最佳方法。）

相关内容