excelperfect
导语：这个案例来自于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
太不容易了！
联系客服
