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

2. 使用Arry3替换掉上文中使用FREQUENCY函数求唯一值的公式中的单元格区域，并进行适当的调整，得到单元格B2中的公式：
=IF(ROWS($1:1)>$C$1,””,INDEX(Arry3,SMALL(IF(FREQUENCY(IF(Arry3<>””,MATCH(Arry3,Arry3,0)),Arry2),Arry2),ROWS($1:1))))
3. 对于单元格C1中求唯一值个数的公式：
=SUM((Arry3<>””)/MMULT(0+(Arry3=TRANSPOSE(Arry3)),ROW(INDIRECT(“1:”& COUNTA(Arry3)))^0))
（1）Arry3中的元素是否为空进行比较，得到数组：
{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
（2）看看MMULT中的第二个数组：
ROW(INDIRECT(“1:” &COUNTA(Arry3)))^0
我们已经知道Arry3中元素个数为40 ，因此上述数组为：
{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}^0
结果为：
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}
（3）看看MMULT中的第一个数组：
0+(Arry3=TRANSPOSE(Arry3))
这将转换成40行40列的数组。由于数组太大，为了方便解释其原理，将数据区域Data缩减为A1:A2 ，这样Arry3为：
{“Amaranth”;”Bronze”;”Silver”;”Bronze”;””;””}
此时， MMULT中的第一个数组转换为：
0+({“Amaranth”;”Bronze”;”Silver”;”Bronze”;””;””}={“Amaranth”,”Bronze”,”Silver”,”Bronze”,””,””})
两个正交数组比较后的结果为：
0+{TRUE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE,TRUE}
加上0强制转换为1/0组成的数组：
{1,0,0,0,0,0;0,1,0,1,0,0;0,0,1,0,0,0;0,1,0,1,0,0;0,0,0,0,1,1;0,0,0,0,1,1}
（4）此时， MMULT公式为：
MMULT({1,0,0,0,0,0;0,1,0,1,0,0;0,0,1,0,0,0;0,1,0,1,0,0;0,0,0,0,1,1;0,0,0,0,1,1},{1;1;1;1;1;1})
得到：
{1;2;1;2;2;2}
（5）此时， SUM公式为：
=SUM({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}/{1;2;1;2;2;2})
转换为：
=SUM({1;0.5;1;0.5;0;0})
结果为3 。表明如果数据区域为A1:A2 ，有3个唯一值。
（6）回到示例中的数据区域A1:A10 ，此时的SUM公式为：
=SUM({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}/{2;5;2;21;5;21;21;21;21;21;21;21;1;5;2;21;2;21;21;21;1;5;21;21;1;1;5;3;2;2;3;1;21;21;21;21;3;21;21;21})
转换为：
=SUM({0.5;0.2;0.5;0;0.2;0;0;0;0;0;0;0;1;0.2;0.5;0;0.5;0;0;0;1;0.2;0;0;1;1;0.2;0.333333333333333;0.5;0.5;0.333333333333333;1;0;0;0;0;0.333333333333333;0;0;0})
结果为10 。表明数据区域A1:A10中有10个唯一值。
小结
解决本案例的过程是，首先从原来的以空格分隔的字符串中生成子字符串数组，重新构建该数组，以便能够对其进行处理。我们从本案例中至少可以学到：
1. 使用大量的空格替换来拆分由分隔符分隔的字符串。
2. 从列表中获取唯一值的标准公式。
【excel公式技巧：从单元格区域的字符串中提取唯一值】3. 将二维数组转换成一维数组的方法。

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

猜你喜欢