如下图1所示，在单元格A1中有一段英文文本，其中可能包含标点符号或不包含标点符号，在单元格B1中输入一个公式，识别文本中包含五个元音字母的单词，统计出这些单词的个数 。
文章插图
图1
注意，统计的单词应满足：
1. 单词中包含全部五个元音字母
2. 这五个元音字母在单词中从左至右出现的顺序是a、e、i、o、u
3. 这五个元音字母在单词中只出现一次
在图1中，红色字体的单词满足条件，而黑色斜体的单词虽然包含全部的五个元音字母但由于顺序不符合要求，因此不满足条件 。
先不看答案，自已动手试一试 。
公式
在单元格B1中输入公式：
=SUMPRODUCT(0+(MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{“a”,”e”,”i”,”o”,”u”},””))=1),{1;1;1;1;1})=5),0+(ISNUMBER(SEARCH(“a*e*i*o*u”,Arry2))))
公式解析
公式中的Arry1和Arry2是定义的两个名称 。
名称：Arry1
引用位置：=ROW(INDIRECT(“1:”&1+LEN($A1)-LEN(SUBSTITUTE($A1,””,””))))-1
名称：Arry2
引用位置：=TRIM(MID(SUBSTITUTE(LOWER($A1),””,REPT(” “,LEN($A1))),LEN($A1)*Arry1+1,LEN($A1)))
注意，在定义名称时确保活动单元格位于工作表的第一行 。
首先，来看看名称Arry1：
=ROW(INDIRECT(“1:” & 1+LEN($A1)-LEN(SUBSTITUTE($A1,””,””))))-1
由于单元格A1中字符串的长度为461，去掉空格后的长度为392，因此公式转换为：
=ROW(INDIRECT(“1:” & 1+461-392))-1
转换为：
=ROW(INDIRECT(“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(SUBSTITUTE(LOWER($A1),””,REPT(” “,LEN($A1))),LEN($A1)*Arry1+1,LEN($A1)))
注意，这里使用LOWER函数将文本转换成小写，因为SUBSTITUTE函数区分大小写 。
Arry2将生成由A1中的单词组成的数组，其运行原理在本系列前面的文章中已作详细讲解，有兴趣的朋友可查阅参考 。Arry2生成的数组为：
{“abstemious”;”people”;”who”;”are”;”not”;”facetious”;”by”;”nature”;”should”;”not”;”be”;”lacking”;”the”;”education”;”imbibing”;”of”;”arsenious”;”substances”;”will”;”not”;”make”;”them”;”more”;”abstentious.”;”indeed,”;”facetiousness”;”aside,”;”such”;”practices”;”are”;”likely”;”to”;”be”;”harmful,”;”as”;”many”;”acheilous”;”casualties”;”can”;”testify.”;”a”;”more”;”reliable”;”herbal”;”remedy”;”is”;”a”;”concoction”;”of”;”the”;”caesious,”;”annelidous”;”plants”;”found”;”anemious”;”plains”;”of”;”outer”;”mongolia,”;”plants”;”which”;”are”;”thought”;”to”;”contributed”;”to”;”the”;”diet”;”of”;”raeticodactylus.”}
推荐阅读
- Excel公式技巧：从字符串中提取数字——数字位于字符串开头
- Excel公式： 获取非连续单元格区域中只出现一次的数字
- Excel公式技巧：十进制数转换成指定进制的数
- excel公式技巧：从单元格区域的字符串中提取唯一值
- VBA 怎么使用DATEADD函数
- Excel函数:AVERAGE函数
- pandigitals excel公式教程： 1/17和其他全数字
- excel公式教程：求字符串中的数字组成的数能够被指定数整除的数的个数
- excel公式教程：TRANSPOSE，非数组版本
- excel公式教程： 求一列中的数字剔除掉另一列中的数字后剩下的数字