excel公式技巧：从单元格区域的字符串中提取唯一值( 二 ) _知识经验

1+LEN(A1)-LEN(SUBSTITUTE(A1,””,””))
的结果为：1+22-20 ，为3 。
注意这种公式构造，该构造可以有效地计算字符串中（以空格分隔的）子字符串的数量。
这样， MID函数的参数start_num部分转换成：
99*(ROW(INDIRECT(“1:” & 3))-1)+1
即为：
99*({0;1;2})+1
结果为：
{1;100;199}
这样，可以确保本例中所拆分的单词都在空格分隔的区域内。其实，只要单词不长且我们选择的类似99这样的数字够大，都可以保证能够有效拆分单词。
实际上，可以保证有效获取分隔区域的数值的长度应该始终大于字符串中任何单个单词的长度。这样，我们可以选择该值为字符串的长度，因为单个子字符串的长度都不能大于整个字符串本身的长度。所以，建议在这种类型的公式结构中使用LEN(A1)而不是99 ，甚至999 。本例中由于要将公式应用于一系列单元格，故没有使用该方法，而是直接取值999 。
这里取MID函数的第三个参数为99 ，以保证能够将单词包含到我们获取到子字符串中。
这样，上面的SUMPRODUCT公式变为：
=SUMPRODUCT(TRIM(MID(“AmaranthBronzeSilver”,{1;100;199},99)))
转换为：
=SUMPRODUCT(TRIM({“Amaranth“;”Bronze“;”Silver”}))
TRIM函数去掉字符串前后的空格：
=SUMPRODUCT({“Amaranth“;”Bronze“;”Silver”})
好了！原理讲清楚了，现在回到名称Arry3：
=INDEX(TRIM(MID(SUBSTITUTE(Data,””,REPT(” “,999)),TRANSPOSE(999*(ROW(INDIRECT(“1:”&MAX(Arry1)))-1)+1),999)),N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))
记住，我们没有传递单个单元格到TRIM(MID(SUBSTITUTE(中，而是单元格区域。先看看公式中MID函数的指定起始位置的参数部分：
TRANSPOSE(999*(ROW(INDIRECT(“1:”& MAX(Arry1)))-1)+1)
首先看看定义的名称Arry1：
1+LEN(Data)-LEN(SUBSTITUTE(Data,””,””))
转换为：
1+LEN({“Amaranth BronzeSilver”;”Bronze”;””;”Violet BronzeAmaranth”;”Red”;”Puce Bronze”;”Taupe Ochre BronzeCerise”;”Silver Red CeriseOrange”;””;”Cerise”})-LEN(SUBSTITUTE({“AmaranthBronze Silver”;”Bronze”;””;”Violet BronzeAmaranth”;”Red”;”Puce Bronze”;”Taupe Ochre BronzeCerise”;”Silver Red CeriseOrange”;””;”Cerise”},” “,””))
转换为：
1+{22;6;0;22;3;11;25;24;0;6}-{20;6;0;20;3;10;22;21;0;6}
结果为：
{3;1;1;3;1;2;4;4;1;1}
即单元格区域Data中每个单元格内单个单词的数量，除了其中第3行和第9行为空但仍返回不正确的数字1外。但由于我们只是想获取所构造的数组的最大值，因此这些不正确的结果不会对我们有影响。
这样， MID函数的指定起始位置的参数部分转换为：
TRANSPOSE(999*(ROW(INDIRECT(“1:” &MAX({3;1;1;3;1;2;4;4;1;1})))-1)+1)
转换为：
TRANSPOSE({1;1000;1999;2998})
结果为：
{1,1000,1999,2998}
此时，公式中的一部分转换为：
TRIM(MID(SUBSTITUTE(Data,””,REPT(” “,999)),{1,1000,1999,2998},999))
转换为：
TRIM({“Amaranth“,”Bronze“,”Silver”,””;”Bronze”,””,””,””;””,””,””,””;”Violet“,”Bronze“,”Amaranth”,””;”Red”,””,””,””;”Puce“,”Bronze”,””,””;”Taupe“,”Ochre“,”Bronze“,”Cerise”;”Silver“,”Red“,”Cerise“,”Orange”;””,””,””,””;”Cerise”,””,””,””})

excel公式技巧：从单元格区域的字符串中提取唯一值( 二 )

猜你喜欢