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

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

例如: 一个垂直范围包含值 "red"，"blue"，"red"，"green"，"blue"，< strong > "black"
因此，我想有 "red，"blue"，"green"，"black" + 最终2其他空白细胞。

我已经找到了一种方法来获得一个计算排序列表使用小或大结合索引，但我想有这个计算排序，没有使用 VBA。

409850 次浏览
小开

您可以使用 COUNTIF 来获取范围中值的出现次数。所以如果值在 A3中，范围是 A1: A6，那么在下一列中使用 IF (EXACT (COUNTIF (A3: $A $6，A3) ，1) ，A3，“”)。对于 A4，它将是 IF (确切的(COUNTIF (A4: $6，A3) ，1) ，A4，“”)

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

小开

好吧，我有两个主意给你。希望其中一个能带你去你想去的地方。请注意，第一个方法忽略了以公式形式执行此操作的请求，因为该解决方案并不完美。我想我确保这种简单的方法对你来说真的行不通; ^)。

使用“高级筛选器”命令

  1. 选择列表(或者将所选内容放在列表中的任何位置，如果出现抱怨 Excel 不知道列表是否包含标题的对话框，则单击 OK)
  2. 选择数据/高级过滤器
  3. 选择“筛选列表，就地”或“复制到另一个位置”
  4. 点击“唯一记录”
  5. 点击确定
  6. 你完蛋了。在适当位置或新位置创建唯一列表。注意，您可以记录这个操作来创建一行 VBA 脚本来执行这个操作，然后可以将其推广到其他情况(例如，不需要上面列出的手动步骤)。

使用公式(注意，我正在构建基于 Locksfree 的解决方案，最终得到一个没有漏洞的列表)

这一解决方案将与以下警告一起工作:

  • 必须对列表进行排序(升序或降序无关紧要)。实际上，这是相当准确的，因为要求所有的 like 项都必须是连续的，但是排序是达到这种状态的最简单的方法。
  • 需要三个新列(两个用于计算的新列和一个用于新列表的新列)。第二和第三列可以合并，但我将把它作为一个练习留给读者。

    以下是解决方案的摘要:

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

    下面是一个循序渐进的例子:

    1. 打开一个新的电子表格
    2. 在 a1: a6中输入原问题中给出的例子(“ red”，“ blue”，“ red”，“ green”，“ blue”，“ black”)
    3. 对列表进行排序: 将所选内容放在列表中并选择 Sort 命令。
    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的值现在应该是“黑色”、“蓝色”、“绿色”、“红色”、“”、“”。

    希望这能帮上忙。

    • 小开

    我最近也遇到了同样的问题，最后终于弄明白了。

    使用您的列表，这里是一个粘贴从我的 Excel 与公式。

    我建议将公式写在列表的中间，例如，在我的示例的 C6单元格中，然后复制并粘贴到您的列中，公式应该会自动调整，而不需要重新键入。

    唯一具有唯一不同公式的单元格位于第一行。

    使用您的列表(“红色”、“蓝色”、“红色”、“绿色”、“蓝色”、“黑色”) ; 以下是结果: (我没有一个足够高的水平发布一个图像，所以希望这个文本版本有意义)

    • [ A 栏: 原始列表]
    • [ B 栏: 唯一列表结果]

    • [ C 栏: 唯一列表公式]

      1. 红色，红色，=A3
      2. 蓝色，蓝色，=IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
      3. 红色，=IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
      4. 绿色，绿色，=IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
      5. 蓝色，=IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
      6. 黑色，黑色，=IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")
    小开

    假设列 A 包含要查找的单个唯一实例的值，并且有一个 Heding 行，我使用了以下公式。如果您希望它具有不可预测的行数，那么可以用 = 地址(COUNTA (A: A) ，1)替换 A772(我的数据结束的位置)。

    = IF (COUNTIF (A5: $772，A5) = 1，A5，“”)

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

    小开

    一个迂回的方法是将你的 Excel 电子表格加载到 Google 电子表格中，使用 Google 的 UNIQUE (range)函数——它可以完全满足你的需要——然后将 Google 电子表格保存回 Excel 格式。

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

    小开

    解决方案

    我在 VBA 中为您创建了一个函数，所以您现在可以用一种简单的方法来完成这项工作。
    创建一个 VBA 代码模块(宏) ，如您在 本教程中看到的。

    1. 按 Alt + F11
    2. 单击 Insert中的 Module。
    3. 粘贴代码。
    4. 如果 Excel 表示您的文件格式不是宏友好比保存为 Save As中的 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. 输入数据和调用函数。
      Enter data and call function
    2. 种出来。
      Grow it
    3. 好了。
      Voilà

    空牢房

    它在包含空单元格的列中工作。如果您将单元格(调用函数)重写到应该没有输出的位置，那么该函数也不会输出任何东西(非错误) ，正如我在前面的示例“2”中所做的那样。“成长”那部分。

    Empty cell case

    小开

    Drew Sherman 的解决方案非常好，但是列表必须是连续的(他建议手动排序，这对我来说是不可接受的)。Guitarthrower 的解决方案是有点慢，如果项目的数量很大，并不尊重原始列表的顺序: 它输出一个排序的列表，无论如何。

    我想要项目的原始顺序(按照另一列中的日期排序) ，此外，我想要从最终列表中排除一个项目，不仅因为它是重复的，而且因为各种其他原因。

    我的解决方案是对 Drew Sherman 的解决方案的一个改进，同样，这个解决方案使用两列来进行中间计算:

    第一栏:

    要过滤的带有重复或空白的列表。我将把它放在 A11: A1100间隔中作为示例，因为我在将 Drew Sherman 的解决方案移动到第一行中没有开始的情况时遇到了麻烦。

    B 栏:

    如果此行中的值有效(包含非重复值) ，则此公式将输出0。注意，可以在第一个 IF 中添加任何其他排除条件，也可以作为另一个外部 IF 添加。

    =IF(ISBLANK(A11);1;IF(COUNTIF($A$11:A11;A11)=1;0;COUNTIF($A11:A$1100;A11)))

    使用智能副本填充列。

    C 栏:

    在第一行，我们将找到第一个有效的行:

    =MATCH(0;B11:B1100;0)

    从这个位置，我们用下面的公式搜索下一个有效值:

    =C11+MATCH(0;OFFSET($B$11:$B$1100;C11;0);0)

    将其放在第二行中，并使用智能副本填充列的其余部分。这个公式将输出 # N/D 错误时，没有更多的唯一项目指向。我们将在下一篇专栏文章中利用这一点。

    D 栏:

    现在我们只需要得到 C 列所指向的值:

    =IFERROR(INDEX($A$11:$A$1100; C11); "")

    使用智能副本填充列。这是输出唯一列表。

    小开

    我已经粘贴了我在下面的 Excel 文件中使用的内容。这将从范围 L11:L300中提取独特的值，并从列 V、 V11开始填充它们。在这个例子中，我在 v11中使用了这个公式，并将它拖到下面以得到所有的唯一值。

    =INDEX(L$11:L$300,MATCH(0,COUNTIF(V$10:V10,L$11:L$300),0))

    或者

    =INDEX(L$11:L$300,MATCH(,COUNTIF(V$10:V10,L$11:L$300),))

    这是一个数组公式

    小开

    你也可以这样做。

    创建以下命名范围:

    nList = the list of original values
nRow = ROW(nList)-ROW(OFFSET(nList,0,0,1,1))+1
nUnique = IF(COUNTIF(OFFSET(nList,nRow,0),nList)=0,COUNTIF(nList, "<"&nList),"")

    使用这3个命名范围，您可以使用下面的公式生成唯一值的有序列表。它将按升序排序。

    IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-?),nUnique,0)),"")

    您需要将单元格的行号替换为’?’，该行号刚好位于您的唯一有序列表的第一个元素之上性格。

    例如，如果您的唯一有序列表从单元格 B5开始，那么公式将是:

    IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-4),nUnique,0)),"")
    小开

    只有当值是有序的时候，例如所有的“红色”在一起，所有的“蓝色”在一起等等，这种方法才有效。 假设您的数据在从 A2开始的列 A 中-(不要从第1行开始) 在 B2类型1中 在 b3中，type = if (A2 = A3，B2，B2 + 1) 向下拖动公式直到数据结束 所有的“红色”将是1，所有的“蓝色”将是2，所有的“绿色”将是3等。

    在 C2类型1,2,3等下行列 在 D2 = OffSET ($A $1，MATCH (c2，$B $2: $B $x，0) ，0)中，x 是最后一个单元格 向下拖动，只会显示唯一的值。——输入一些错误检查

    小开

    选择具有重复值的列，然后转到“数据”选项卡，然后“数据工具”选择“删除重复值”选择 1)“继续当前选择” 2)点击“删除重复... ...”按钮 3)按「选择全部」按钮 4)按「确定」

    现在你得到了唯一值列表。

    小开

    我很惊讶这个解决方案还没有出现。我认为这是最简单的方法之一

    给您的数据一个标题，并将其放入一个动态命名范围(即，如果您的数据在 colA中)

    =OFFSET($A$2,0,0,COUNTA($A:$A),1)

    然后创建一个数据透视表，使源成为命名范围。

    只需将标题放入行部分，就可以得到唯一的值，可以使用内置特性按照您喜欢的任何方式进行排序。

    小开

    注意到这是一个非常古老的问题，但人们似乎仍然有困难使用公式提取独特的项目。 这里有一个解决方案，可以自动返回它们的值。

    假设在 A2: A7列中有“红色”、“蓝色”、“红色”、“绿色”、“蓝色”、“黑色”

    然后把它作为一个数组公式放在 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)));"")

    那么它应该是这样子的, enter image description here

    小开
    最佳答案

    这是一个古老的，有一些解决方案在那里，但我想出了 一个简短的公式比任何其他我遇到的，它可能会有用的任何人经过。

    我已经命名了颜色列表 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} ，在 C3中返回{0; 0; 0; 0; 0; 1} ，因为“ black”在排序中是第一个，在数据中是最后一个。在 C4{0; 1; 0; 0; 1; 1}中，它表示“ black”，并且所有出现“ blue”的地方都已经存在。
    3. SUM通过计算所有已经存在的较小值(数组 # 2的和)返回 KTH排序值。
    4. MATCH查找 k-th 排序值的第一个索引(数组 # 1中的索引)。
    5. 当排序的唯一列表完成时，IFERROR仅用于隐藏底部单元格中的 #N/A错误。

    要知道你有多少独特的项目，你可以使用这个 普通配方奶粉:

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

    即使要得到一个排序的唯一值，也可以使用公式:

    =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中的公式

    这是一个数组公式

    小开

    对于一个适用于多行和多列的值的解决方案，我发现以下公式非常有用，来自 < a href = “ http://www.get-digital-help. com/2009/03/16/only-values-from-multi-column-using-array-”rel = “ nofollow”> http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/ 奥斯卡 get-digital.help.com 甚至用一个可视化的例子一步一步地讲解它。

    1)给出值的范围: tbl _ text

    2)在这种情况下，对单元格 B13应用以下带 CTRL + SHIFT + ENTER 的数组公式。更改 $B $12: B12以引用输入此公式的单元格上面的单元格。

        =INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)

    3)复制/拖放，直到得到 N/A。

    小开

    如果将所有数据放在同一列中并使用以下公式 示例公式: =IF(C105=C104,"Duplicate","Not a Duplicate")

    步骤

    1. 把数据分类
    2. 为公式添加列
    3. 检查单元格是否等于上面的单元格
    4. 然后过滤 Not a Duplicate
    5. 可选项: 复制由公式列计算的数据，并仅将其粘贴为值(这样，如果开始删除数据，就不会出现错误)
    6. 注意/警告: 这只有在您首先对数据排序时才有效

    示例公式: =IF(C105=C104,"Duplicate","Not a Duplicate")

    小开

    优化的 VBScript 解决方案

    我使用 totymedli 的代码，但是发现在使用大范围时会出现问题(正如其他人指出的) ，所以我对他的代码进行了一些优化。如果有人对使用 VBScript 获取惟一值感兴趣，但发现 totymedli 的代码在更新时速度较慢，请尝试以下方法:

        Function listUnique(rng As Range) As Variant
Dim val As String
Dim elements() As String
Dim elementSize As Integer
Dim newElement As Boolean
Dim i As Integer
Dim distance As Integer
Dim allocationChunk As Integer
Dim uniqueSize As Integer
Dim r As Long
Dim lLastRow  As Long


lLastRow = rng.End(xlDown).row


elementSize = 1
unqueSize = 0


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


If distance <> 0 Then
If Cells(Range(Application.Caller.Address).row - 1, Range(Application.Caller.Address).Column).Value = "" Then
listUnique = ""
Exit Function
End If
End If


For r = 1 To lLastRow
val = rng.Cells(r)
If val <> "" Then
newElement = True
For i = 1 To elementSize - 1 Step 1
If elements(i - 1) = val Then
newElement = False
Exit For
End If
Next i
If newElement Then
uniqueSize = uniqueSize + 1
If uniqueSize >= elementSize Then
elementSize = elementSize * 2
ReDim Preserve elements(elementSize - 1)
End If
elements(uniqueSize - 1) = val
End If
End If
Next




If distance < uniqueSize Then
listUnique = elements(distance)
Else
listUnique = ""
End If
End Function
    小开

    使用数据透视表可能不算作 只用公式，但似乎比迄今为止大多数其他建议更实际:

    SO1429899 example

    小开

    在 B2单元格中尝试此公式

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

    单击 F2后按 Ctrl + Shift + Enter

    enter image description here

    小开

    简单公式解决方案: 使用动态数组函数(UNIQUE 函数)

    自2018年秋季以来，MicrosoftExcel 的订阅版本(Office 365/Microsoft 365应用程序)包含所谓的 动态数组函数动态数组函数(Office 2016/2019非订阅版本尚未提供)。

    UNIQUE 功能

    其中一个函数是 UNIQUE函数，它将为所选范围提供一个唯一值数组。

    例子

    在下面的示例中，输入值在 A1:A6范围内。 UNIQUE函数被键入到单元格 C1中。

    =UNIQUE(A1:A6)

    Simple solution to show unique values in Excel using dynamic array functions

    如您所见，为了显示所有唯一值，UNIQUE函数将自动溢出所需的单元格范围。这是由 C1:C4周围的蓝色细框表示的。

    谢谢你告诉我

    由于 UNIQUE函数会自动溢出所需的行数，所以应该在 C1下留出足够的空间。如果没有足够的空间，就会出现 #SPILL错误。

    Dynamic array functions: Spill not possible because a value in C3 is blocking the spill range

    如果想要 UNIQUE函数的 参考结果，只需引用包含 UNIQUE函数的单元格并添加一个散列 #符号。

    =C1#

    也可以检查 多列中的唯一值。在这种情况下，UNIQUE 函数将交付行内单元格组合唯一的所有行:

    Applying the UNIQUE function over several columns

    如果希望使用 显示唯一的列而不是唯一的行，则必须将 [by_col]参数设置为 TRUE(默认值为 FALSE，这意味着您将收到唯一的行)。

    还可以通过将 [exactly_once]参数设置为 TRUE来显示 只出现一次的值:

    =UNIQUE(A1:A6;;TRUE)

    Show unique values that appear exactly once


    提交答案