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

Question

你知道在Excel中通过公式计算出唯一值列表的方法吗？

例如：一个垂直范围包含值"red"、"blue"、"red"、"green"、"blue"、"black"，我希望结果是"red"、"blue"、"green"、"black"+最多2个空单元格。

我已经找到了使用SMALL或LARGE结合INDEX获取计算排序列表的方法，但我也想要这个计算排序列表而不使用VBA。

- iDevlop

---

你希望数据以什么形式返回？我认为大多数（全部？）公式都会返回值而不是列表... - Arkady

通过使用数组公式，你可以获得一个单元格数组。 - iDevlop

作为对之前帖子的快速修改，如果在B列中将位置$A11更改为$A12，则将拥有每个值的单个副本，包括重复值，但仅出现一次！！ :) - user3377676

多年之后，一个内置解决方案终于要被实施了。 - GSerg

@GSerg 8-) 微软可能决定添加一些在 GSheets 中长期可用的有用功能！ - iDevlop

Answer 1

好的，我有两个想法。希望其中一个能帮助到你。请注意，第一个想法忽略了将此作为公式解决的请求，因为该解决方案不太美观。我想确保简单的方法真的对你行不通；）。

使用高级筛选命令

1. 选择列表（或将您的选择放在列表中的任何位置，并单击“确定”如果出现对话框抱怨Excel不知道您的列表是否包含标题）
2. 选择 数据/高级筛选
3. 选择“筛选列表”，原地筛选”或“复制到其他位置”
4. 点击“仅唯一记录”
5. 点击确定
6. 完成。创建了一个唯一的列表，可以原地或在新位置。请注意，您可以记录此操作以创建一个一行VBA脚本来执行此操作，该脚本可能可以概括地适用于其他情况（例如，不需要上面列出的手动步骤）。

使用公式（请注意，我正在改进Locksfree的解决方案，以便得到没有空洞的列表）

以下解决方案会有以下注意事项：

列表必须排序（升序或降序无所谓）。实际上这是相当准确的，因为要求实际上是所有相似的项目必须是连续的，但排序是达到这种状态最简单的方法。

需要三列新列（两列用于计算，一列用于新列表）。第二列和第三列可以合并，但我将其留给读者作为练习。

以下是解决方案的概述：

1. 对于列表中的每个项目，计算它上面的重复项数。
2. 对于唯一列表中的每个位置，计算下一个唯一项的索引。
3. 最后，使用这些索引创建一个只包含唯一项的新列表。

以下是逐步示例：

1. 打开新电子表格
2. 在A1：A6中输入原始问题中给出的示例（“red”，“blue”，“red”，“green”，“blue”，“black”）
3. 排序列表：将选择放在列表中，然后选择排序命令。
4. 在B列中，计算重复项：
   1. 在B1中输入“=IF(COUNTIF($A$1:A1,A1) = 1,0,COUNTIF(A1:$A$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+INDEX($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<=ROW($A$6), INDEX($A$1:$A$6,C1), "")”。并且，“if”是为了防止索引超出列表末尾而发生#REF情况。
   2. 使用智能复制来填充D2:D6。
   3. 现在D1:D6的值应为“black”，“blue”，“green”，“red”，“”，“”。希望这对你有所帮助...

- Drew Sherman

---

嗯，看起来我的解决方案有一个缺陷。它不能处理像“黑色，黑色，蓝色，蓝色，粉色，黄色”这样的列表。应该能够修复它，但我必须先做一些工作; ^)。我很快会更新。 - Drew Sherman

好的，我修复了解决方案，现在我相信它适用于所有情况。已在上方进行编辑。 - Drew Sherman

那似乎是一个有效的解决方案。与此同时，我已经找到了一些使用LARGE或SMALL函数首先对项目进行排序的东西。但是我的解决方案只适用于数字。 - iDevlop

我在实现它时遇到了一些注意事项：在步骤5.1中，无论列表的起始位置如何，C1都应该等于1。这样你就可以始终拥有正确的相对位置来使用行范围。而在步骤6.1中使用的IF仅在选择紧密范围时有效。我用一个改进的算法做出了新的答案，它没有这些问题，也不需要连续或排序的输入列表。 - ReneSac

Answer 2

这是一个老问题，已经有一些解决方案了。但我想出了比我遇到的其他任何解决方案都要简短和简单的公式，对路过的任何人都可能有用。

我将颜色列表命名为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。

带有样本数据{"red"; "blue"; "red"; "green"; "blue"; "black"}的解释：

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}，在C3中返回{0; 0; 0; 0; 0; 1}，因为“黑色”是排序中的第一个并且在数据中排名最后。在C4中返回{0; 1; 0; 0; 1; 1}，它表示“黑色”和所有“蓝色”的出现次数均已存在。
3. SUM通过计算已经存在的所有较小值的出现次数之和（数组#2的总和），返回第k个排序值。
4. MATCH找到第k个排序值的第一个索引（数组#1中的索引）。
5. IFERROR仅用于隐藏底部单元格中的#N/A错误，当排序后的唯一列表完成时会出现此错误。

要知道您有多少个唯一项，可以使用此常规公式:

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

- dePatinkin

---

边缘情况中存在一个小“bug”：如果原始数据包含空格，则可以通过在#1数组和SUM()中添加IF(ISBLANK(Colors), 0,1)来解决。 - AviD

@AviD，你好。你能帮忙澄清一下是否有空白值吗？目前我的公式数组返回整个数组的第一个值..非常感谢。 - Freelancer

我不确定我理解这个解决方案。提供屏幕截图示例会很有帮助。 - Stevoisiak

@dePatinkin 这对我有效，但我需要在列表中硬编码第一项，否则它会忽略所有以A开头的项目。你有忽略列表中某些项目的解决方案吗？（例如，不返回Staff） - Dumitru Daniel

@DumitruDaniel 这可能是因为您在 C1 中将“A”作为列标题写入。标题不应与任何值匹配。 过滤列表是一个不同的问题，有不同的解决方案，所以也许可以提出一个问题来解决它。如果您不需要排序，可以使用这个公式：=IFERROR(INDEX(Colors,MATCH(0,COUNTIF(C$1:C1,Colors)+(Colors="blue"),0)),"")。 - dePatinkin

Answer 3

解决方案

我已经为您创建了一个VBA函数，因此现在您可以轻松完成此操作。
按照这个教程所示，创建一个VBA代码模块（宏）。

1. 按下 Alt+F11
2. 点击 Insert 中的 Module.
3. 粘贴代码.
4. 如果Excel提示您的文件格式不支持宏，请在 另存为 中选择 Excel Macro-Enabled 格式保存。

源代码

Function listUnique(rng As Range) As Variant
    Dim row As Range
    Dim elements() As String
    Dim elementSize As Integer
    Dim newElement As Boolean
    Dim i As Integer
    Dim distance As Integer
    Dim result As String

    elementSize = 0
    newElement = True

    For Each row In rng.Rows
        If row.Value <> "" Then
            newElement = True
            For i = 1 To elementSize Step 1
                If elements(i - 1) = row.Value Then
                    newElement = False
                End If
            Next i
            If newElement Then
                elementSize = elementSize + 1
                ReDim Preserve elements(elementSize - 1)
                elements(elementSize - 1) = row.Value
            End If
        End If
    Next

    distance = Range(Application.Caller.Address).row - rng.row

    If distance < elementSize Then
        result = elements(distance)
        listUnique = result
    Else
        listUnique = ""
    End If
End Function

使用方法

只需在单元格中输入=listUnique(range)即可。唯一的参数是range，它是常规的Excel范围。例如：A$1:A$28或H$8:H$30。

条件

• range必须是列。
• 调用函数的第一个单元格必须与range开始的行相同。

示例

常规情况

1. 输入数据并调用函数。
   
2. 增加数据。
   
3. 完成。
   

空单元格情况

它适用于包含空单元格的列。如果您将单元格（调用函数）超出应无输出的位置，函数也不会输出任何内容（不会出现错误），就像我在上一个示例的“2.增加数据”部分所做的那样。

- totymedli

---

感谢@totymedli。我的问题是关于在没有使用VBA的情况下完成它。 - iDevlop

@iDevlop 抱歉，我没有看到。实际上我是为一个朋友做的，只是想它可能对你有用。 - totymedli

当它正常工作时非常好，但在处理大数据集时似乎会导致Excel崩溃。 - Jamie Bull

非常有帮助，但是每次向列表中添加新数据时，Excel都需要太多时间。我希望如果进行一些优化，那就太棒了。 - Adarsh Madrecha

很好，但是如果范围包含末尾的空单元格，则无法工作。=(此外，此函数将无法用于在单元格中创建ComboBox。=( - Andrew_STOP_RU_WAR_IN_UA

Answer 4

一种迂回的方法是将您的Excel电子表格加载到Google电子表格中，使用Google的UNIQUE(range)函数 - 此函数正好可以实现您想要的功能 - 然后将Google电子表格保存为Excel格式。

我承认这不是Excel用户可行的解决方案，但对于任何想要此功能且能够使用Google电子表格的人来说，这种方法都是有用的。

- yoyo

---

不算是回复，但还是很有趣的。谢谢。 - iDevlop

是的，正如我所说，这对于Excel用户来说不可行，但对于有同样问题的Google电子表格用户很有帮助。 - yoyo

最重要的是，公式会自动更新，但这个不会。 - sancho.s ReinstateMonicaCellio

UNIQUE是一种公式，和其他公式一样会自动更新。 - yoyo

Answer 5

在单元格B2中尝试此公式

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF(B$1:$B1,$A$2:$A$7),0),1),"")

点击 F2 并按下 Ctrl + Shift + Enter

- Hariharan G R

Answer 6

注意这是一个非常老的问题，但似乎人们仍然难以使用提取唯一项的公式。

以下是一种返回值本身的解决方案。

假设您在A2:A7列中有"red"、"blue"、"red"、"green"、"blue"、"black"，那么将以下内容作为数组公式放入B2中并向下复制：

=IFERROR(INDEX(A$2:A$7;SMALL(IF(FREQUENCY(MATCH(A$2:A$7;A$2:A$7;0);ROW(INDIRECT("1:"&COUNTA(A$2:A$7))));ROW(INDIRECT("1:"&COUNTA(A$2:A$7)));"");ROW(A1)));"")

然后它应该看起来像这样:

- Jimmy

Answer 7

即使要获取排序唯一的值，也可以使用公式完成。这是您可以使用的一个选项：

=INDEX($A$2:$A$18,MATCH(SUM(COUNTIF($A$2:$A$18,C$1:C1)),COUNTIF($A$2:$A$18,"<" &$A$2:$A$18),0))

数据范围：A2:A18

单元格C2中的公式

这是一个数组公式

- new bie

Answer 8

假设A列包含您想要查找单个唯一实例的值，并且有一个标题行，我使用了以下公式。如果您希望它能够扩展到不可预测的行数，您可以将A772（我的数据结束的地方）替换为=ADDRESS(COUNTA(A:A),1)。

=IF(COUNTIF(A5:$A$772,A5)=1,A5,"")

这将显示该列中每个值的最后一个实例的唯一值，并且不假定任何排序。它利用缺少绝对值来基本上具有递减的“滑动窗口”数据进行计数。当在缩小的窗口中的countif等于1时，那一行就是该值在该列中的最后一个实例。

- Geoff Still

Answer 9

您可以使用COUNTIF函数来获取范围内值的出现次数。例如，如果值在A3中，范围是A1：A6，则在下一列中使用IF（EXACT（COUNTIF（A3：$ A $ 6，A3），1），A3，“”）。对于A4，它将是IF（EXACT（COUNTIF（A4：$ A $ 6，A3），1），A4，“”）。

这将为您提供一个列，其中所有唯一值都没有重复。

- Locksfree

Answer 10

使用数据透视表可能不算是仅使用公式，但似乎比迄今为止的大多数建议更实用:

- pnuts