Excel公式练习42:统计一个句子中符合条件的字数

如下图1所示，单元格A1中有一个英文文本，可能包含也可能不包含标点符号。在单元格B1中输入公式，识别文本中带有五个元音的单词，并计算这些单词的数量。

图1

请注意，统计单词应该满足：

1.这个词包含所有五个元音。

2.这五个元音在一个单词中从左到右出现的顺序是A、E、I、O和u。

3.这五个元音在一个单词中只出现一次。

在图1中，红色字体的单词符合条件，而黑色和斜体的单词包含所有五个元音，但顺序不符合要求，因此不符合条件。

先不看答案，自己试试。

公式

在单元格B1中输入公式：

=SUMPRODUCT(0(MMULT(0(LEN(arr y2)-LEN(replace(arr y2)，{“a”、“e”、“I”、“o”、“u”}、“)))=1)；{ 1；1;1;1;1})=5)，0(ISNUMBER(SEARK(" a * e * I * o * u "，Arry2)))

公式分析

公式中的Arry1和Arry2是定义的两个名称。

名称：Arry1。

参考位置：=row(间接(" 1:" 1 len ($ a1)-len(替代($ a1，""，" "))))-1。

名称：Arry2。

参考位置：=trim (mid(替换为(lower ($ a1)、""、replt("、len ($ a1))、len ($ a1) * arry11、len ($ a1))。

请注意，定义名称时，请确保活动单元格位于工作表的第一行。

首先，看看Arry1这个名字：

=ROW(INDIRECT(" 1: " 1 LEN($ A1)-LEN(replace($ A1，""，" "))))-1

由于单元格A1中的字符串长度为461，删除空格后的长度为392，因此公式转换为：

=ROW(INternet(" 1: " 1 461-392))-1

转换为：

=ROW(间接(" 1:" 70))-1

转换为：

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70}-1

结果是：

{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69}

中间数组中的70对应于包含70个子字符串(单词)的文本。

将结果数组传递给名称为Arry2的MID函数的start_num参数：

=TRIM(MID(replace(LOWER($ A1)，""，REPT("，LEN($A1)))，LEN($A1)*Arry1 1，LEN($A1)))

请注意，这里使用LOWER函数将文本转换为小写，因为replace函数区分大小写。

Arry2将生成一个由A1中的单词组成的数组，其工作原理在本系列的前几篇文章中已经详细解释过了。有兴趣的朋友可以参考一下。Arry2生成的数组为：

{“节制”；“人”；“谁”；“正在”；“不是”；“开玩笑的”；“by”；“自然”；“应该”；“不是”；“是”；“缺乏”；“the”；“教育”；“吸收”；“的”；“砷中毒”；“物质”；“意志”；“不是”；“制作”；“他们”；“更多”；"节制"；“的确如此，”；“滑稽”；“一旁，”；“这样”；“惯例”；“正在”；“可能”；“到”；“是”；“有害”；“as”；“很多”；“阿奇洛乌斯”；“伤亡人数”；“可以”；"作证"；“a”；“更多”；“可靠”；“草药”；“补救措施”；“是”；“a”；“炮制”；“的”；“the”；“青灰色”；“annelidous”；“植物”；“找到了”；“贫血”；“平原”；“的”；“外部”；“蒙古，”；“植物”；“哪个”；“正在”；“思想”；“到”；“贡献了”；“到”；“the”；“饮食”；“的”；"雷铁偶蹄类动物"}

数组中的一些单词包含标点符号，但它们不会影响最终结果。

接下来，我们需要对上面生成的数组中的每个元素进行两个测试：第一个测试是确定每个元素是否依次包含五个元素“a”、“e”、“I”、“o”和“u”，第二个测试是确定这五个元音在元素中只出现一次。

先看公式：

ISNUMBER(SEARCH("a*e*i*o*u "，Arry2))

SEARCH函数有一个很好的特性，它接受通配符。因此，在适当的地方插入通配符后，可以使用字符串“a*e*i*o*u”作为该函数的find_text参数。这样，如果搜索到的字符串按顺序包含五个元素“a”、“e”、“I”、“o”和“u”，则会返回表示找到的字符位置的数字。上述公式可转换为：

{真；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；真；假；真；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；真；真；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；假；真}

数组中的“真”是红色字体，对应于数组2中的相应元素：

{“节制”；“人”；“谁”；“正在”；“不是”；“开玩笑的”；“by”；“自然”；“应该”；“不是”；“是”；“缺乏”；“the”；“教育”；“吸收”；“的”；“砷中毒”；“物质”；“意志”；“不是”；“制作”；“他们”；“更多”；"节制"；“的确如此，”；“滑稽”；“一旁，”；“这样”；“惯例”；“正在”；“可能”；“到”；“是”；“有害”；“as”；“很多”；“阿奇洛乌斯”；“伤亡人数”；“可以”；"作证"；“a”；“更多”；“可靠”；“草药”；“补救措施”；“是”；“a”；“炮制”；“的”；“the”；“青灰色”；“annelidous”；“植物”；“找到了”；“贫血”；“平原”；“的”；“外部”；“蒙古，”；“植物”；“哪个”；“正在”；“思想”；“到”；“贡献了”；“到”；“the”；“饮食”；“的”；"雷铁偶蹄类动物"}

显然，这些红色字体的元素符合我们的要求，但不是全部。有两种“滑稽”和“滑稽偶蹄”其中一些元音不止一个。

这样，我们需要进行第二次测试：

MMULT(0(LEN(arr y2)-LEN(DELETE(arr y2，{“a”、“e”、“I”、“o”、“u”}、“))=1)；{ 1；1;1;1;1})=5

这是一种标准的公式技术，用于确定字符串中有多少个字符：使用原始字符串的长度减去删除指定字符后的字符串长度。

请注意，我们需要确定五个字符，而不是一个。

对于LEN(arr 2)，转换为原始字符串的长度：

{10;6;3;3;3;9;2;6;6;3;2;7;3;9;8;2;9;10;4;3;4;4;4;12;7;13;6;4;9;3;6;2;2;8;2;4;9;10;3;8;1;4;8;6;6;2;1;10;2;3;9;10;6;5;8;6;2;5;9;6;5;3;7;2;11;2;3;4;2;16}

在公式中：

LEN(DELETE(arr y2，{“a”、“e”、“I”、“o”、“u”}、“)

将五个值的数组传递给替换函数的参数old_text，并确保五个值的数组与Arry2正交。因为Arry2是单行数组向量，所以这五个数组应该是单行数组向量。这样就形成了一个69行5列的数组，对应于每个单词删除一个元音字母后的字符串。例如，生成的数组的第一行应该是：

{“有节制的”、“不节制的”、“节制的”、“节制的”}

最后的结果是：

{9,9,9,9,9;6,4,6,5,6;3,3,3,2,3;2,2,3,3,3;3,3,3,2,3;8,8,8,8,8;2,2,2,2,2;5,5,6,6,5;6,6,6,5,5;3,3,3,2,3;2,1,2,2,2;6,7,6,7,7;3,2,3,3,3;8,8,8,8,8;8,8,5,8,8;2,2,2,1,2;8,8,8,8,8;9,9,10,10,9;4,4,3,4,4;3,3,3,2,3;3,3,4,4,4;4,3,4,4,4;4,3,4,3,4;11,11,11,11,11;7,5,6,7,7;12,11,12,12,12;5,5,5,6,6;4,4,4,4,3;8,8,8,9,9;2,2,3,3,3;6,5,5,6,6;2,2,2,1,2;2,1,2,2,2;7,8,8,8,7;1,2,2,2,2;3,4,4,4,4;8,8,8,8,8;8,9,9,10,9;2,3,3,3,3;8,7,7,8,8;0,1,1,1,1;4,3,4,3,4;7,6,7,8,8;5,5,6,6,6;6,4,6,6,6;2,2,1,2,2;0,1,1,1,1;10,10,9,7,10;2,2,2,1,2;3,2,3,3,3;8,8,8,8,8;9,9,9,9,9;5,6,6,6,6;5,5,5,4,4;7,7,7,7,7;5,6,5,6,6;2,2,2,1,2;5,4,5,4,4;8,9,8,7,9;5,6,6,6,6;5,5,4,5,5;2,2,3,3,3;7,7,7,6,6;2,2,2,1,2;11,10,10,10,10;2,2,2,1,2;3,2,3,3,3;4,3,3,4,4;2,2,2,1,2;14,15,15,15,15}

从上面生成的第一个数组减去第二个数组，等于由每个元素中分别包含五个元音的数量组成的数组。例如第一个数组的第一个元素10(即单词"节制"的长度)减去第二个数组中的第一行{9,9,9,9,9}(即单词"节制"分别去掉五个元音后的长度):

10-{9,9,9,9,9}

得到：

{1,1,1,1,1}

也就是单词"节制"中元音“a”、“e”、“I”、“o”、“u”的个数组成的数组。

最终的结果为：

{1,1,1,1,1;0,2,0,1,0;0,0,0,1,0;1,1,0,0,0;0,0,0,1,0;1,1,1,1,1;0,0,0,0,0;1,1,0,0,1;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,0,1,0,0;0,1,0,0,0;1,1,1,1,1;0,0,3,0,0;0,0,0,1,0;1,1,1,1,1;1,1,0,0,1;0,0,1,0,0;0,0,0,1,0;1,1,0,0,0;0,1,0,0,0;0,1,0,1,0;1,1,1,1,1;0,2,1,0,0;1,2,1,1,1;1,1,1,0,0;0,0,0,0,1;1,1,1,0,0;1,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,0,0,0;1,0,0,0,1;1,0,0,0,0;1,0,0,0,0;1,1,1,1,1;2,1,1,0,1;1,0,0,0,0;0,1,1,0,0;1,0,0,0,0;0,1,0,1,0;1,2,1,0,0;1,1,0,0,0;0,2,0,0,0;0,0,1,0,0;1,0,0,0,0;0,0,1,3,0;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,1,1,1,1;1,0,0,0,0;0,0,0,1,1;1,1,1,1,1;1,0,1,0,0;0,0,0,1,0;0,1,0,1,1;1,0,1,2,0;1,0,0,0,0;0,0,1,0,0;1,1,0,0,0;0,0,0,1,1;0,0,0,1,0;0,1,1,1,1;0,0,0,1,0;0,1,0,0,0;0,1,1,0,0;0,0,0,1,0;2,1,1,1,1}

代表着每个单词中元音“a”、“e”、“I”、“o”、“u”分别出现的个数。

由于我们感兴趣的仅仅是这五个元音只出现一次的单词，将上面的数组与一相比较：

LEN(arr y2)-LEN(DELETE(arr y2、{“a”、“e”、“I”、“o”、“u”})=1

得到：

{真，真，真，真，真，真；假，假，假，真，假；假，假，假，真，假；真、真、假、假、假；假，假，假，真，假；真，真，真，真，真，真；假，假，假，假，假；真、真、假、假、真；假，假，假，真，真；假，假，假，真，假；假，真，假，假，假；真、假、真、假、假；假，真，假，假，假；真，真，真，真，真，真；假，假，假，假，假；假，假，假，真，假；真，真，真，真，真，真；真、真、假、假、真；假，假，真，假，假；假，假，假，真，假；真、真、假、假、假；假，真，假，假，假；假，真，假，真，假；真，真，真，真，真，真；假，假，真，假，假；真、假、真、真、真；真，真，真，假，假；假，假，假，假，真；真，真，真，假，假；真、真、假、假、假；假，真，真，假，假；假，假，假，真，假；假，真，假，假，假；真、假、假、假、真；真、假、假、假、假；真、假、假、假、假；真，真，真，真，真，真；假，真，真，假，真；真、假、假、假、假；假，真，真，假，假；真、假、假、假、假；假，真，假，真，假；真、假、真、假、假；真、真、假、假、假；假，假，假，假，假；假，假，真，假，假；真、假、假、假、假；假，假，真，假，假；假，假，假，真，假；假，真，假，假，假；真，真，真，真，真，真；真，真，真，真，真，真；真、假、假、假、假；假，假，假，真，真；真，真，真，真，真，真；真、假、真、假、假；假，假，假，真，假；假，真，假，真，真；真、假、真、假、假；真、假、假、假、假；假，假，真，假，假；真、真、假、假、假；假，假，假，真，真；假，假，假，真，假；假，真，真，真，真；假，假，假，真，假；假，真，假，假，假；假，真，真，假，假；假，假，假，真，假；假，真，真，真，真}

将布尔值转换成数字：

{1,1,1,1,1;0,0,0,1,0;0,0,0,1,0;1,1,0,0,0;0,0,0,1,0;1,1,1,1,1;0,0,0,0,0;1,1,0,0,1;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,0,1,0,0;0,1,0,0,0;1,1,1,1,1;0,0,0,0,0;0,0,0,1,0;1,1,1,1,1;1,1,0,0,1;0,0,1,0,0;0,0,0,1,0;1,1,0,0,0;0,1,0,0,0;0,1,0,1,0;1,1,1,1,1;0,0,1,0,0;1,0,1,1,1;1,1,1,0,0;0,0,0,0,1;1,1,1,0,0;1,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,0,0,0;1,0,0,0,1;1,0,0,0,0;1,0,0,0,0;1,1,1,1,1;0,1,1,0,1;1,0,0,0,0;0,1,1,0,0;1,0,0,0,0;0,1,0,1,0;1,0,1,0,0;1,1,0,0,0;0,0,0,0,0;0,0,1,0,0;1,0,0,0,0;0,0,1,0,0;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,1,1,1,1;1,0,0,0,0;0,0,0,1,1;1,1,1,1,1;1,0,1,0,0;0,0,0,1,0;0,1,0,1,1;1,0,1,0,0;1,0,0,0,0;0,0,1,0,0;1,1,0,0,0;0,0,0,1,1;0,0,0,1,0;0,1,1,1,1;0,0,0,1,0;0,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,1,1,1}

现在要找到数组中由5个一组成的行，使用求不同单价下的利润函数：

MMULT(0(LEN(arr y2)-LEN(DELETE(arr y2，{“a”、“e”、“I”、“o”、“u”}))=1)；{ 1;1;1;1;1})

得到：

{5;1;1;2;1;5;0;3;2;1;1;2;1;5;0;1;5;3;1;1;2;1;2;5;1;4;3;1;3;2;2;1;1;2;1;1;5;3;1;2;1;2;2;2;0;1;1;1;1;1;5;5;1;2;5;2;1;3;2;1;1;2;2;1;4;1;1;2;1;4}

我们将数组中为5的与Arry2中相应的元素对应：

{"节制";"人";"谁";"正在";"不是";"开玩笑的";“by”；"自然";"应该";"不是";"是";"缺乏";“the”；"教育";"吸收";"的";"砷中毒";"物质";"意志";"不是";"制作";"他们";"更多";"节制";"的确如此，";"滑稽";"一旁，";"这样";"惯例";"正在";"可能";"到";"是";"有害";“as”；"很多";"阿奇洛乌斯";"伤亡人数";"可以";"作证";“a”；"更多";"可靠";"草药";"补救措施";"是";“a”；"炮制";"的";“the”；"青灰色";“annelidous”；"植物";"找到了";"贫血";"平原";"的";"外部";"蒙古，";"植物";"哪个";"正在";"思想";"到";"贡献了";"到";“the”；"饮食";"的";"雷铁偶蹄类动物"}

有9个结果满足，但只满足单词中出现五个元音字母一次而不满足其按“a”、“e”、“I”、“o”、“u”的顺序出现。

将上面的数组与5比较，得到：

{真；假；假；假；假；真；假；假；假；假；假；假；假；真；假；假；真；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；真；真；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；假；FALSE}

将上面得到的两个条件的结果代入公式：

=SUMPRODUCT(0(MMULT(0(LEN(arr y2)-LEN(替换(arr y2)，{“a”、“e”、“I”、“o”、“u”}))=1)；{ 1;1;1;1;1})=5)，0(ISNUMBER(SEARK(" a * e * I * o * u "，Arry2)))

得到：

=SUMPRODUCT(0 { TRUE；假；假；假；假；真；假；假；假；假；假；假；假；真；假；假；真；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；真；真；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假})，0 {真假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；真；假；真；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；真；真；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；假；真})

得到：

=SUMPRODUCT({ 1；0;0;0;0;1;0;0;0;0;0;0;0;1;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0 0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0 0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;1})

结果为：

8