湖南龙网

  1. 首页 > 生活百科 > >

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

小知识生活百科

如下图1所示 ， 在单元格区域A1:A10中有一些数据 。现在 ， 想从该区域中提取单词并创建唯一值列表 ， 如列B中的数据所示 。

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

文章插图
图1
可以在单元格B1中编写一个公式 ， 向下拖拉以创建该唯一值列表 。如何编写这个公式呢？
先不看答案 ， 自已动手试一试 。
公式
在单元格B1中输入数组公式：
=IF(ROWS($1:1)>$C$1,””,INDEX(Arry3,SMALL(IF(FREQUENCY(IF(Arry3<>””,MATCH(Arry3,Arry3,0)),Arry2),Arry2),ROWS($1:1))))
下拉直至出现空单元格为止 。
在单元格C1中 ， 下面的数组公式：
=SUM((Arry3<>””)/MMULT(0+(Arry3=TRANSPOSE(Arry3)),ROW(INDIRECT(“1:”& COUNTA(Arry3)))^0))
计算单元格区域A1:A10中不重复的单个单词的数量 。
公式解析
公式中的Arry1、Arry2、Arry3是定义的三个名称 。
名称：Arry1
引用位置：=1+LEN(Data)-LEN(SUBSTITUTE(Data,””,””))
名称：Arry2
引用位置：=ROW(INDIRECT(“1:” &(MAX(Arry1)*ROWS(Data))))
名称：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)))))
在上述名称中 ， 使用了另一个定义的名称：Data
引用位置：=Sheet1!$A$1:$A$10
1. 我们首先来看一个名称Arry3 ， 这是我们公式的关键部分 。名称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)))))
（1）使用TRIM、MID、SUBSTITUTE和REPT函数的构造是一种标准的（并且非常有用）组合 ， 给定一个以某字符（空格、逗号、分号等）分隔的字符串 ， 该构造能用于提取这些被分隔的子字符串中的任何一个 ， 或者（像本例一样）生成由这些被分隔的子字符串的组成的数组 ， 以便按我们希望的方式进行操作 。
现在 ， 仅将上述公式应用于Data中的第一个单元格 ， 以了解其工作原理 。我们还将用SUMPRODUCT封装该结构 ， 不是因为真想对“Amaranth”、“Bronze”和“Silver”求和 ， 而是使我们能够强制返回数组 。因此 ， 我们将使用的公式为：
=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1,” “,REPT(“”,99)),99*(ROW(INDIRECT(“1:” &1+LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))-1)+1,99)))
（为便于解释 ， 这里将上面的999缩小为99 。）
关于此公式构造的关键点是字符串中所有空格的初始替换 ， 这次替换基本上使用更多的空格 。如果所使用的字符串用逗号或分号分隔 ， 则情况将相同：我们将用更多的空格替换所有逗号或分号 。
这里 ， 生成那些更多空格字符串的部分就是REPT(” ”,99) ， 构成了一个包含99个空格的字符串 。
使用空格替换后的公式为：
=SUMPRODUCT(TRIM(MID(“AmaranthBronzeSilver,99*(ROW(INDIRECT(“1:”& 1+LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))-1)+1,99)))
此时 ， 在单词之间已放置了大量的空格 。
再看看MID函数中参数start_num部分：
99*(ROW(INDIRECT(“1:” &1+LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))-1)+1
在单元格A1中字符串的长度是22 ， 将单词之间的空格去掉后长度是20 。因此：

推荐阅读


上一篇：DATE 函数

下一篇：Excel公式技巧：十进制数转换成指定进制的数