Excel公式练习34： 识别是否存在相同字母的单词

导语：这个案例来自于excelxor.com，真是太佩服了！这样复杂的要求都能够用公式解决，这样的解决方法都能够想到！

本次的练习是：判断单元格区域B1:B10的各单元格中的单词是否在单元格区域E1:E10中出现，如果该单词出现或者存在有与该单词相同字母组成的单词，则返回TRUE，否则返回FALSE。要求在列A中输入公式进行判断，如下图1所示。

图1

先不看答案，自已动手试一试。

公式

在单元格A1中输入数组公式：

=OR((LEN($E$1:$E$10)=LEN(B1))*(MMULT(0+(LEN(SUBSTITUTE(LOWER($E$1:$E$10),MID(LOWER(B1),TRANSPOSE(ROW(INDIRECT("1:"&LEN(B1)))),1),""))=LEN(SUBSTITUTE(LOWER(B1),MID(LOWER(B1),TRANSPOSE(ROW(INDIRECT("1:"& LEN(B1)))),1),""))),ROW(INDIRECT("1:" &LEN(B1)))^0))=LEN(B1))

下拉至单元格A10，如下图2所示。

图2

公式解析

原文使用单元格A3中的公式来分析：

=OR((LEN($E$1:$E$10)=LEN(B3))*(MMULT(0+(LEN(SUBSTITUTE(LOWER($E$1:$E$10),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"&LEN(B3)))),1),""))=LEN(SUBSTITUTE(LOWER(B3),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"& LEN(B3)))),1),""))),ROW(INDIRECT("1:" &LEN(B3)))^0))=LEN(B3))

公式的第一部分：

LEN($E$1:$E$10)=LEN(B3)

检查单元格区域E1:E10中有哪些单词与单元格B3中单词的字符数相同，得到数组：

{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE}

公式的主要部分，即传递给MMULT函数的第一个数组：

0+(LEN(SUBSTITUTE(LOWER($E$1:$E$10),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"&LEN(B3)))),1),""))=LEN(SUBSTITUTE(LOWER(B3),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"& LEN(B3)))),1),"")))

先看看：

LEN(SUBSTITUTE(LOWER($E$1:$E$10),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"& LEN(B3)))),1),""))

其中：

1. LOWER($E$1:$E$10)

将单元格E1:E10中的数据转换成小写，以避免一些函数区分大小写问题。结果为：

{"andrew";"adel";"shelia";"anelie";"blake";"andria";"shelia";"eleina";"kassia";"daren"}

这个数组被传递给SUBSTITUTE函数，作为其第1个参数。

2. MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:" &LEN(B3)))),1)

其中：

（1）LOWER(B3)

结果为：

eliane

（2）TRANSPOSE(ROW(INDIRECT("1:" &LEN(B3))))

转换为：

TRANSPOSE(ROW(INDIRECT("1:" &6)))

转换为：

TRANSPOSE({1;2;3;4;5;6})

结果为：

{1,2,3,4,5,6}

因此，MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:" &LEN(B3)))),1)被转换为：

MID(“eliane”,{1,2,3,4,5,6},1)

最终创建组成单元格B3中字符串的字母构成的数组：

{“e”,”l”,”i”,”a”,”n”,”e”}

3. 因此，LEN(SUBSTITUTE(LOWER($E$1:$E$10),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"& LEN(B3)))),1),""))转换为：

LEN(SUBSTITUTE({"andrew";"adel";"shelia";"anelie";"blake";"andria";"shelia";"eleina";"kassia";"daren"},{“e”,”l”,”i”,”a”,”n”,”e”},””))

SUBSTITUTE函数参数中有两个数组，一个是10行1列的数组，另一个是1行6列的数组，得到一个10行6列的数组：

{"andrw","andrew","andrew","ndrew","adrew","andrw";"adl","ade","adel","del","adel","adl";"shlia","sheia","shela","sheli","shelia","shlia";"anli","aneie","anele","nelie","aelie","anli";"blak","bake","blake","blke","blake","blak";"andria","andria","andra","ndri","adria","andria";"shlia","sheia","shela","sheli","shelia","shlia";"lina","eeina","elena","elein","eleia","lina";"kassia","kassia","kassa","kssi","kassia","kassia";"darn","daren","daren","dren","dare","darn"}

作为LEN函数的参数，得到：

{5,6,6,5,5,5;3,3,4,3,4,3;5,5,5,5,6,5;4,5,5,5,5,4;4,4,5,4,5,4;6,6,5,4,5,6;5,5,5,5,6,5;4,5,5,5,5,4;6,6,5,4,6,6;4,5,5,4,4,4}

再看看等号的另一侧：

LEN(SUBSTITUTE(LOWER(B3),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"& LEN(B3)))),1),""))

转换为：

LEN(SUBSTITUTE("eliane",{"e","l","i","a","n","e"},""))

转换为：

LEN({"lian","eiane","elane","eline","eliae","lian"})

结果为：

{4,5,5,5,5,4}

与前面一样，将字符串拆分成独立的字符，并对字符串进行替换，将替换后的字符串的长度作为数组元素。

这样做，就是要告诉我们字符串中每个字母有多少个，因此，数组{4,5,5,5,5,4}可以解释为：

在“eliane”中，字母“e”有2个，字母“l”有1个，字母“i”有1个，字母“a”有1个，字母“n”有1个，字母“e”有2个。

之所以这样做的原因是，如果对于60个元素的矩阵中10个1×6数组中的任何一个（这些1×6数组中的每个数组都由E1:E10中每个单元格中的字符串依次删除“eliane”中的6个字母后组成），其所有6个元素都与此处的这些值（{4,5,5,5,5,5,4}）精确对应，并且在它们各自的数组中占据相同的位置，那么可以得出结论，与该特定的1×6数组相对应的字符串必定与单元格B3中字符串的组成字母相同。

为了更清楚地解释，以大矩阵中的第一行{5,6,6,5,5,5}——对应于“andrew”的结果来说明。

第一个元素（5）是“andrew”在删除了“eliane”中的第一个字母后即“e”后，结果字符串“andrw”的长度为5。换句话说，“andrew”中恰好有一个字母“e”。

同样，该数组中的第二个元素（6）是“andrew”在删除了“eliane”中的第二个字母（即从中删除“l”）之后，结果字符串“andrew”的长度仍为6（该字符串中没有这个字母）。换句话说，“andrew”中没有字母“l”。

据此可以轻松地得到数组中的第三、第四、第五和第六个元素6、5、5和5分别对应于字符串“andrew”、“ndrew”、“adrew”和“andrw”的长度。

因此，我们可以说：字母“e”在“andrew”有1个，字母“l”在“andrew”有0个，字母“i”在“andrew”有0个，字母“a”在“andrew”有1个，字母“n”在“andrew”有1个，字母“e”在“andrew”有1个。

因此，因此，“andrew”与“eliane”的组成字母不相同。当然，我们可以立即看到这一点，但是对于Excel来说，却并不简单。

下一个要考虑的字符串，是单元格E2中的字符串（“adel”），只有四个字符的长度，不会超过我们公式的初始部分（该部分检查该字符串是否与B3中的长度相同）。因此，让我们从E1:E10中获取下一个字符串，即单元格E3中的“Shelia”。

从前面的计算结果可知，其生成的数组为{5,5,5,5,6,5}，即从中“Shelia”中依次删除“eliane”中的字母后的字符长度组成的数组。可以得出：字母“e”在“Shelia”有1个，字母“l”在“Shelia”有1个，字母“i”在“Shelia”有1个，字母“a”在“Shelia”有1个，字母“n”在“Shelia”有0个，字母“e”在“Shelia”有1个。两个单词组成的字母不同。

接着看E1:E10中的下一个字符串“anelie”实际与“eliane”组成的字母相同。其对应的数组为：{4,5,5,5,5,4}。这与我们在字符串“eliane”自身中用其组成的字母替换后获得的字符串长度数组完全相同。这表明：在“anelie”中，字母“e”有2个，字母“l”有1个，字母“i”有1个，字母“a”有1个，字母“n”有1个，字母“e”有2个。这也是“eliane”中每个字母出现的次数，因此，“anelie”与“eliane”组成字母一定相同。

至此，我们已经在E1:E10中找到了与组成单元格B3中字符串字母相同的字符串，但如何让Excel知道呢？现在要做的是，构造必要的规则以指示Excel将{4,5,5,5,5,4}与10行6列的大矩阵相匹配，该矩阵上文已给出：

{5,6,6,5,5,5;3,3,4,3,4,3;5,5,5,5,6,5;4,5,5,5,5,4;4,4,5,4,5,4;6,6,5,4,5,6;5,5,5,5,6,5;4,5,5,5,5,4;6,6,5,4,6,6;4,5,5,4,4,4}

我们希望此矩阵中的行至少有一个为{4,5,5,5,5,4}的结果，能够返回TRUE。（实际上，矩阵中的第8行也相匹配，并且其字符串就是“eliane”）

单行单列的匹配可以使用MATCH函数，但对于数组来说就无能为力了。此时，可以使用MMULT函数。首先，将上面计算得到的两个数组设置为相等：

0+(LEN(SUBSTITUTE(LOWER($E$1:$E$10),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"&LEN(B3)))),1),""))=LEN(SUBSTITUTE(LOWER(B3),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"& LEN(B3)))),1),"")))

根据上文已经计算出的结果，该公式可转换为：

0+({5,6,6,5,5,5;3,3,4,3,4,3;5,5,5,5,6,5;4,5,5,5,5,4;4,4,5,4,5,4;6,6,5,4,5,6;5,5,5,5,6,5;4,5,5,5,5,4;6,6,5,4,6,6;4,5,5,4,4,4}={4,5,5,5,5,4})

此时，将生成一个新的由TRUE/FALSE组成的10行6列的矩阵：

0+({FALSE,FALSE,FALSE,TRUE,TRUE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,FALSE,TRUE,FALSE,TRUE,TRUE;FALSE,FALSE,TRUE,FALSE,TRUE,FALSE;FALSE,TRUE,TRUE,TRUE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,FALSE,FALSE,TRUE})

加上0，将TRUE/FALSE转换成数字：

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

可以看到，矩阵中的第4行和第8行都是由6个1组成的行。

此时，就要用到矩阵乘法了。要检测该矩阵数组中的任何行是否包含6个1，只需要将此10行6列矩阵乘以6行1列的单位矩阵（即{1; 1; 1; 1; 1; 1}）。

但是，我们这里是查找单元格B3中的单词，刚好有6个字母，但不能保证所有单词都是6个字母，这里生成的是10行6列数组，而对于其他单词可能生成10行4列数组、10行5列数组，等等。因此，可以构造一个动态的单位矩阵，作为MMULT函数第二个参数：

ROW(INDIRECT("1:"& LEN(B3)))^0

转换为：

ROW(INDIRECT("1:" & 6))^0

转换为：

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

结果为：

{1;1;1;1;1;1}

此时MMULT函数为：

MMULT({0,0,0,1,1,0;0,0,0,0,0,0;0,1,1,1,0,0;1,1,1,1,1,1;1,0,1,0,1,1;0,0,1,0,1,0;0,1,1,1,0,0;1,1,1,1,1,1;0,0,1,0,0,0;1,1,1,0,0,1},{1;1;1;1;1;1})

结果为：

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

注意到，在第4行和第8行都是6，对应着与组成单元格B3中字符串字母相同的字符串“anelie”和“eliane”。

至此，公式的其余部分就非常简单了。将上面得到的数组设置为等于单元格B3中字符串的长度（6），然后将得到的TRUE/FALSE组成的数组与检查字符串长度得到的数组相乘：

=OR((LEN($E$1:$E$10)=LEN(B3))*(MMULT(0+(LEN(SUBSTITUTE(LOWER($E$1:$E$10),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"&LEN(B3)))),1),""))=LEN(SUBSTITUTE(LOWER(B3),MID(LOWER(B3),TRANSPOSE(ROW(INDIRECT("1:"&LEN(B3)))),1),""))),ROW(INDIRECT("1:"&LEN(B3)))^0))=LEN(B3))

转换为：

=OR(({6;4;6;6;5;6;6;6;6;5}=6)*({2;0;3;6;4;2;3;6;1;4})=6)

转换为：

=OR(({TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE})*({2;0;3;6;4;2;3;6;1;4})=6)

转换为：

=OR({2;0;3;6;0;2;3;6;1;0}=6)

转换为：

=OR({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE})

结果为：

TRUE

太不容易了！