仅使用公式在Excel中获取唯一值

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (23)

你知道在Excel中用公式“计算”唯一值列表的方法吗?

例如:范围包含值。"red",,,"blue",,,"red",,,"green",,,"blue",,,"black"

我也想有结果"red,,,"blue",,,"green",,,"black"+最后还有两个空白细胞。

我已经找到了一种方法,可以使用小或大的组合索引来获得计算的排序列表,但是我也希望使用这种计算排序,而不使用VBA。

提问于
用户回答回答于

这是个老掉牙的问题,有一些解决办法,但我想出了办法更短更简单的公式比我遇到的任何其他人都要好,这对路过的人来说可能是有用的。

我已经命名了颜色列表Colors(A2:A7)阵列公式放入细胞C2这是(固定)):

=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")

使用Ctrl+Shift+Enter中输入公式C2,然后复制C2到C3:C7...

用示例数据说明{“红色”;“蓝色”;“红色”;“绿色”;“蓝色”;“黑色”}:

  1. COUNTIF(Colors,"<"&Colors)返回一个数组(#1),其值的计数比数据{4;1;4;3;1;0}中的每个项都小(黑色=0项,蓝色=1项,红色=4项)。这可以转换为排序值每个项目。
  2. COUNTIF(C$1:C...,Colors)为已排序结果中的每个数据项返回一个数组(#2),其中包含1。在C2中,它返回{0;0;0;0;0;0;0}和C3{0;0;0;0;0;1},因为“Black”在排序中是第一位,在数据中是最后一位。在C4{0;1;0;0;1;1}中,它表示“黑色”,所有出现的“蓝色”都已经存在。
  3. SUM返回K-排序值,通过计数所有已出现的较小值(数组#2的和)。
  4. MATCH查找k-th排序值的第一个索引(数组#1中的索引)。
  5. IFERROR只是为了隐藏#N/A当排序的唯一列表完成时,底部单元格中出现错误。

要知道有多少独特的项目,可以使用这个正则公式

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))
用户回答回答于

好吧,我有两个想法给你。希望他们中的一个能帮你找到你该去的地方。请注意,第一个方法忽略了将其作为公式执行的请求,因为该解决方案并不漂亮。我想我要确保简单的方法不会对你有效。

使用高级筛选器命令

  1. 选择列表(或者将所选内容放置在列表中的任何地方,如果出现对话框抱怨Excel不知道列表中是否包含标头,则单击“确定”)。
  2. Choose Data/Advanced Filter
  3. 选择“筛选列表,就地”或“复制到另一个位置”。
  4. 单击“唯一记录”
  5. 单击“确定”
  6. 在适当的位置或在新的位置创建唯一的列表。请注意,可以记录此操作以创建一行VBA脚本来完成此操作,然后可以将其推广到其他情况下(例如,无需上面列出的手动步骤)。

使用公式(请注意,我是在无锁解决方案的基础上建立的,最终得到一个没有漏洞的列表)

此解决方案将与以下注意事项一起工作:

  • 必须对列表进行排序(升序或降序并不重要)。实际上,这是相当准确的,因为需求是,所有类似的项目必须是连续的,但是排序是达到这种状态的最简单的方法。
  • 需要三个新列(两个新列用于计算,一个新列用于新列表)。第二和第三列可以合并,但我将把它作为练习留给读者。 以下是解决方案的总结:
  1. 对于列表中的每一项,计算其上方的重复数。
  2. 对于唯一列表中的每个位置,计算下一个唯一项的索引。
  3. 最后,使用索引创建一个只有唯一项的新列表。

下面是一个逐步的例子:

  1. 打开一个新的电子表格
  2. 在A1:A6中,输入原问题中的示例(“红色”、“蓝色”、“红色”、“绿色”、“蓝色”、“黑色”)
  3. 对列表进行排序:将所选内容放入列表并选择排序命令。
  4. 在B栏中,计算重复数:
    1. 在B1中,输入“=if(COUNTIF($1:A1,A1)=1,0,COUNTIF(A1:$6,A1))”。请注意,单元格引用中的“$”非常重要,因为它将使下一步(填充列的其余部分)更加容易。“$”表示绝对引用,以便当单元格内容被复制/粘贴时,引用不会更新(相对于将更新的相对引用)。
    2. 使用智能副本填充B列的其余部分:选择B1。将鼠标移动到所选区域右下角的黑色方框上。单击并向下拖到列表的底部(B6)。发布时,公式将被复制到B2:B6中,并更新相关引用。
    3. B1:B6的值现在应为“0,0,1,0,0,1”。请注意,“1”项表示重复。
  5. 在C栏中,创建一个唯一项的索引:
    1. 在C1中输入“=Row()”。实际上只想要c1=1,但是使用Row()意味着即使列表没有在第1行中开始,这个解决方案也能工作。
    2. 在C2中,输入“=if(C1+1<=row($B$6),c1+1+索引($B$1:$B$6,c1+1),c1+1)”。“if”用于在索引到达列表末尾时停止生成#ref。
    3. 使用智能副本填充C3:C6。
    4. C1:C6的值应为“1,2,4,5,7,8”
  6. 在D栏中,创建新的唯一列表:
    1. 在D1中,输入“=if(C1<=行($6),索引($1:$6,$6),”。并且,当索引超出列表末尾时,“if”将用于停止#ref情况。
    2. 使用智能副本填充D2:D6。
    3. D1:D6的值现在应该是“黑色”、“蓝色”、“绿色”、“红色”、“”、“”。

希望这能帮上忙..。

扫码关注云+社区