如果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
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),"")
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
20 回答
一种迂回的方式是将Excel电子表格加载到Google电子表格中,使用Google的UNIQUE(范围)功能 - 这完全符合您的要求 - 然后将Google电子表格保存回Excel格式 .
我承认这对Excel用户来说不是一个可行的解决方案,但这种方法对于想要这个功能并且能够使用Google电子表格的人来说非常有用 .
这是一个古老的,有一些解决方案,但我提出了 a shorter and simpler formula 比我遇到的任何其他解决方案,它可能对任何路过的人都有用 .
我已将颜色列表命名为
Colors
(A2:A7),并且 array formula 放入单元格 C2 为此( fixed ):使用
Ctrl+Shift+Enter
在 C2 中输入公式,然后将C2复制到 C3:C7 .样本数据说明{“红色”; “蓝色”; “红色”; “绿色”; “蓝色”; “黑色”}:
COUNTIF(Colors,"<"&Colors)
返回一个数组(#1),其数值小于数据{4; 1; 4; 3; 1; 0}中的每个项目(黑色= 0项目较小,蓝色= 1项目,红色= 4项) . 对于每个项目,这可以转换为 sort value .对于已在排序结果中的每个数据项,
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"的所有出现都已存在 .SUM
通过计算已存在的所有较小值的出现次数(数组#2的总和)来返回 k-th 排序值 .MATCH
找到第k个排序值的第一个索引(数组#1中的索引) .IFERROR
仅用于在已排序的唯一列表完成时隐藏底部单元格中的#N/A
错误 .要知道你有多少独特的物品,你可以使用 regular formula :
好的,我有两个想法 . 希望他们中的一个会让你到达你需要去的地方 . 请注意,第一个忽略了作为公式执行此操作的请求,因为该解决方案并不漂亮 . 我想我确保简单的方法对你不起作用; ^) .
使用“高级过滤器”命令
选择列表(或将您的选择放在列表中的任何位置,如果对话框出现抱怨Excel不知道您的列表是否包含 Headers ,请单击“确定”)
选择数据/高级过滤器
选择"Filter the list, in-place"或"Copy to another location"
点击"Unique records only"
点击确定
你完成了 . 将在原地或新位置创建唯一列表 . 请注意,您可以记录此操作以创建一行VBA脚本来执行此操作,然后可以将其推广到其他情况下(例如,没有上面列出的手动步骤) .
使用公式(请注意,我正在使用Locksfree解决方案,最终得到一个没有洞的列表)
此解决方案将使用以下警告:
必须对列表进行排序(升序或降序无关紧要) . 实际上,这是非常准确的,因为要求实际上所有类似的项目必须是连续的,但排序是达到该状态的最简单方法 .
需要三个新列(两个用于计算的新列和一个用于新列表的新列) . 第二和第三列可以组合,但我会将其作为练习留给读者 .
以下是解决方案的摘要:
对于列表中的每个项目,计算其上方的重复项数 .
对于唯一列表中的每个位置,计算下一个唯一项目的索引 .
最后,使用索引创建仅包含唯一项的新列表 .
这是一个循序渐进的例子:
打开一个新的电子表格
在a1:a6中输入原始问题中给出的示例("red","blue","red","green","blue","black")
对列表排序:将选择放在列表中,然后选择排序命令 .
在B列中,计算重复项:
在B1中,输入"=IF(COUNTIF($A$1:A1,A1) = 1,0,COUNTIF(A1:$A$6,A1))" . 请注意,单元格引用中的"$"非常重要,因为它将使下一步(填充列的其余部分)变得更加容易 . "$"表示绝对引用,以便在复制/粘贴单元格内容时,引用不会更新(与将更新的相对引用相对) .
使用智能副本填充B列的其余部分:选择B1 . 将鼠标移到选区右下角的黑色方块上 . 单击并向下拖动到列表底部(B6) . 当您发布时,公式将被复制到B2:B6并更新相对引用 .
B1:B6的值现在应为"0,0,1,0,0,1" . 请注意"1"条目表示重复 .
在C列中,创建唯一项目的索引:
在C1中,输入"=Row()" . 你真的只想要C1 = 1但是使用Row()意味着即使列表不在第1行开始,此解决方案也能正常工作 .
在C2中,输入"=IF(C1+1<=ROW($B$6), C1+1+INDEX($B$1:$B$6,C1+1),C1+1)" . "if"用于在索引到达列表末尾时停止生成#REF .
使用智能副本填充C3:C6 .
C1:C6的值应为"1,2,4,5,7,8"
在D列中,创建新的唯一列表:
在D1中,输入"=IF(C1<=ROW($A$6), INDEX($A$1:$A$6,C1), " ")" . 并且,"if"用于在索引超出列表末尾时停止#REF情况 .
使用智能副本填充D2:D6 .
D1:D6的值现在应该是"black","blue","green","red",“","” .
希望这可以帮助....
解决方案
我为您在VBA中创建了一个函数,因此您现在可以轻松地完成此操作 .
如this tutorial所示,创建一个VBA代码模块(宏) .
按Alt F11
单击
Insert
中的Module
.粘贴代码 .
如果Excel表示您的文件格式不是宏友好而不是在
Save As
中将其保存为Excel Macro-Enabled
.源代码
用法
只需在单元格中输入
=listUnique(range)
即可 . 唯一的参数是range
,这是一个普通的Excel范围 . 例如:A$1:A$28
或H$8:H$30
.条件
range
必须是一列 .调用函数的第一个单元格必须位于
range
启动的同一行 .示例
例
空单元格
它适用于包含空单元格的列 . 如果你将单元格(调用函数)覆盖到应该没有输出的地方,那么函数也不会输出任何内容(不是错误),正如我在上一个例子中的“2. Grow it”部分所做的那样 .
注意到它是一个非常古老的问题,但人们似乎仍然无法使用公式来提取独特的项目 . 这是一个返回值为selfs的解决方案 .
假设你在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)));"")
那应该是这样的;
在
B2
单元格中尝试此公式单击
F2
并按Ctrl
Shift
Enter
您可以使用COUNTIF来获取范围中值的出现次数 . 因此,如果值在A3中,范围是A1:A6,则在下一列中使用IF(EXACT(COUNTIF(A3:$ A $ 6,A3),1),A3,“”) . 对于A4,它将是IF(EXACT(COUNTIF(A4:$ A $ 6,A3),1),A4,“”)
这将为您提供一个列,其中所有唯一值都没有任何重复
假设列A包含您要查找单个唯一实例的值,并且具有 Headers 行,则使用以下公式 . 如果您希望它以不可预测的行数进行扩展,则可以使用 =ADDRESS(COUNTA(A:A),1) 替换A772(我的数据已结束) .
= IF(COUNTIF(A5:$ A $ 772 A5)= 1,A5, “”)
这将在列中的每个值的LAST实例上显示唯一值,并且不承担任何排序 . 它利用缺乏绝对性来基本上减少数据的“滑动窗口” . 当缩小窗口中的countif等于1时,该行是该列中该值的最后一个实例 .
即使要获得排序的唯一值,也可以使用公式来完成 . 这是您可以使用的选项:
范围数据:
A2:A18
单元格中的公式
C2
这是ARRAY FORMULA
Drew Sherman的解决方案非常好,但列表必须是连续的(他建议手动排序,这对我来说是不可接受的) . 如果项目数量很大并且不遵守原始列表的顺序,Guitarthrower的解决方案有点慢:它无论如何输出排序列表 .
我想要项目的原始顺序(按照另一列中的日期排序),另外我想从最终列表中排除一个项目,不仅是因为它是重复的,而且还有其他各种原因 .
我的解决方案是对Drew Sherman解决方案的改进 . 同样,此解决方案使用2列进行中间计算:
Column A:
列表中包含要复制的重复项和空格 . 我将把它放在A11:A1100区间作为一个例子,因为我无法将Drew Sherman的解决方案移到第一行没有开始的情况 .
Column B:
如果此行中的值有效(包含非重复值),则此公式将输出0 . 请注意,您可以添加所需的任何其他排除条件第一个IF,或另一个外部IF .
使用智能副本填充列 .
Column C:
在第一行,我们将找到第一个有效行:
从该位置,我们使用以下公式搜索下一个有效值:
将它放在第二行,并使用智能副本填充列的其余部分 . 当没有更多唯一的指向时,此公式将输出#N / D错误 . 我们将在下一栏中利用这一点 .
Column D:
现在我们只需要获取C列指出的值:
使用智能副本填充列 . 这是输出唯一列表 .
你也可以这样做 .
创建以下命名范围:
使用这3个命名范围,您可以使用下面的公式生成唯一值的有序列表 . 它将按升序排序 .
您需要将唯一有序列表的第一个元素上方的单元格行号替换为“?”字符 .
例如 . 如果您的唯一有序列表在单元格B5中开始,则公式将为:
我很惊讶这个解决方案尚未出现 . 我认为这是最简单的之一
为数据提供 Headers 并将其放入动态命名范围(即,如果您的数据位于col
A
中)然后创建一个数据透视表,使源成为您的命名范围 .
只需将 Headers 放入行部分,您将拥有唯一值,您可以使用内置功能对任何方式进行排序 .
我在下面的excel文件中粘贴了我使用的内容 . 这将从范围
L11:L300
中获取唯一值,并从V,V11列开始填充它们 . 在这种情况下,我在v11中有这个公式并将其向下拖动以获得所有唯一值 .要么
这是一个数组公式
使用数据透视表可能不算只使用公式,但到目前为止大多数其他建议似乎更实用:
我最近遇到了同样的问题,终于弄明白了 .
使用您的列表,这里是我的Excel中带有公式的粘贴 .
我建议在列表中间的某个位置编写公式,例如,在我的示例的单元格_745196中,然后将其复制并将其粘贴到列中,公式应自动调整,而无需重新键入 .
具有唯一不同公式的唯一单元格位于第一行 .
使用您的清单("red","blue","red","green","blue","black");结果如下:(我没有足够高的级别来发布图像,所以希望这个txt版本有意义)
[A栏:原始清单]
[B栏:唯一清单结果]
[C栏:唯一列表公式]
红色,红色,
=A3
蓝色,蓝色,
=IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
red ,,
=IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
绿色,绿色,
=IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
蓝色,,
=IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
黑色,黑色,
=IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")
这只适用于值是有序的,即所有“红色”在一起而所有“蓝色”在一起等等 . 假设您的数据在A2中开始的A列 - (不要从第1行开始) B2类型1在b3中类型= 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是最后一个单元格向下拖动,只会显示唯一值 . - 进行一些错误检查
对于适用于多行和多列值的解决方案,我发现以下公式非常有用,来自http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/在get-digital.help.com上的Oscar甚至可以逐步完成并使用可视化示例 .
1)给出标签tbl_text的值范围
2)在这种情况下,将以下数组公式与CTRL SHIFT ENTER一起应用于单元格B13 . 更改$ B $ 12:B12以引用您输入此公式的单元格上方的单元格 .
3)复制/向下拖动直到你得到N / A.
如果将所有数据放在相同的列中并使用以下公式示例公式:
=IF(C105=C104,"Duplicate","Not a Duplicate")
脚步
对数据进行排序
为公式添加列
检查单元格是否等于其上方的单元格
然后过滤
Not a Duplicate
可选:复制公式列计算的数据并仅粘贴为值(如果开始删除数据,则不会出现错误
注意/警告:这仅在您首先对数据进行排序时才有效
示例公式:
=IF(C105=C104,"Duplicate","Not a Duplicate")
优化的VBScript解决方案
我使用了totymedli的代码,但发现它在使用大范围时会出现问题(正如其他人指出的那样),所以我对其代码进行了优化 . 如果有人有兴趣使用VBScript获取唯一值但是在更新时发现totymedli的代码很慢,请尝试以下方法:
选择具有重复值的列然后转到数据选项卡,然后选择数据工具选择删除重复选择1)“继续当前选择”2)单击删除重复....按钮3)单击“全选”按钮4)单击“确定”
现在你得到了唯一的 Value 表 .