当前位置： 文江博客话题详情

在 Excel 中计算唯一值

发布于 2024-08-10 05:37:14 字数 718 浏览 6 评论 0原文

我需要在 Excel 中计算范围 (C2:C2080) 中的唯一值。谷歌搜索公式：

=SUM(IF(FREQUENCY(MATCH(C2:C2080;C2:C2080;0);MATCH(C2:C280;C2:C2080;0))>0;1))

返回错误值。

UPD：蹩脚的解决方案：

Sub CountUnique()

Dim i, count, j As Integer

count = 1
For i = 1 To 470
    flag = False
    If count > 1 Then
        For j = 1 To count
            If Sheet1.Cells(i, 3).Value = Sheet1.Cells(j, 11).Value Then
                flag = True
            End If
        Next j
    Else
        flag = False
    End If

    If flag = False Then
        Sheet1.Cells(count, 11).Value = Sheet1.Cells(i, 3).Value
        count = count + 1
    End If

Next i

Sheet1.Cells(1, 15).Value = count

End Sub
原文

I need to count unique values in range (C2:C2080) in excel. Googled formula:

=SUM(IF(FREQUENCY(MATCH(C2:C2080;C2:C2080;0);MATCH(C2:C280;C2:C2080;0))>0;1))

return incorrect value.

UPD: Lame solution:

Sub CountUnique()

Dim i, count, j As Integer

count = 1
For i = 1 To 470
    flag = False
    If count > 1 Then
        For j = 1 To count
            If Sheet1.Cells(i, 3).Value = Sheet1.Cells(j, 11).Value Then
                flag = True
            End If
        Next j
    Else
        flag = False
    End If

    If flag = False Then
        Sheet1.Cells(count, 11).Value = Sheet1.Cells(i, 3).Value
        count = count + 1
    End If

Next i

Sheet1.Cells(1, 15).Value = count

End Sub

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（11）

此刻的回忆 2024-08-17 05:37:14

这是一个适合我的 VBA 函数。

您可以将其用作工作表函数< /a>，引用任何范围，例如“=CountUnique(N8:O9)”

它处理文本和数值，并将空白单元格视为一个值

它不需要处理数组函数。

对于字典对象，它需要引用 Microsoft 脚本库。

    Public Function CountUnique(rng As Range) As Integer
        Dim dict As Dictionary
        Dim cell As Range
        Set dict = New Dictionary
        For Each cell In rng.Cells
             If Not dict.Exists(cell.Value) Then
                dict.Add cell.Value, 0
            End If
        Next
        CountUnique = dict.Count
    End Function

Here is a VBA function that works for me.

You can use it as a worksheet function, referencing any range, eg “=CountUnique(N8:O9)”

It handles text and numeric values, and treats blank cells as one value

It does not require dealing with array functions.

It requires a reference to the Microsoft Scripting Library, for the dictionary object.

    Public Function CountUnique(rng As Range) As Integer
        Dim dict As Dictionary
        Dim cell As Range
        Set dict = New Dictionary
        For Each cell In rng.Cells
             If Not dict.Exists(cell.Value) Then
                dict.Add cell.Value, 0
            End If
        Next
        CountUnique = dict.Count
    End Function
回复 原文
游魂 2024-08-17 05:37:14

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

http://office.microsoft.com/en-us/excel/HP030561181033.aspx

您还可以编写一个 VBA 宏（不过不确定这是否是您想要的。）

其效果如下（给定一个 A1-A11 已填充且 B1-B11 为空的电子表格）：

Sub CountUnique()

Dim count As Integer
Dim i, c, j As Integer

c = 0
count = 0
For i = 1 To 11
    Sheet1.Cells(i, 2).Value = Sheet1.Cells(i, 1).Value
    c = c + 1
    For j = 1 To c
        If CDbl(Sheet1.Cells(i, 1).Value) = CDbl(Sheet1.Cells(j, 2).Value) Then
            c = c - 1
            Exit For
        End If
    Next j
Next i

' c now equals the unique item count put in the 12'th row
Sheet1.Cells(12, 1).Value = c

End Sub


=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

http://office.microsoft.com/en-us/excel/HP030561181033.aspx

You may also write a VBA macro (not sure if that's what you're after though.)

Something to the effect of (given a spreadsheet with A1-A11 filled and B1-B11 empty):

Sub CountUnique()

Dim count As Integer
Dim i, c, j As Integer

c = 0
count = 0
For i = 1 To 11
    Sheet1.Cells(i, 2).Value = Sheet1.Cells(i, 1).Value
    c = c + 1
    For j = 1 To c
        If CDbl(Sheet1.Cells(i, 1).Value) = CDbl(Sheet1.Cells(j, 2).Value) Then
            c = c - 1
            Exit For
        End If
    Next j
Next i

' c now equals the unique item count put in the 12'th row
Sheet1.Cells(12, 1).Value = c

End Sub
回复 原文
终遇你 2024-08-17 05:37:14

尝试：

=SUM(IF(FREQUENCY(C2:C2080,C2:C2080)>0,1))

编辑：以上将会处理列中的空白条目

Try:

=SUM(IF(FREQUENCY(C2:C2080,C2:C2080)>0,1))

EDIT: The above will handle blank entries in the column

回复 原文
触ぅ动初心 2024-08-17 05:37:14

JustinG 的函数运行得非常好（而且速度很快），直到由于 Excel 中的某种类型的限制而导致唯一项目的数量超过 32,767。

我发现如果你修改他的代码

Public Function CountUnique(rng As Range) As Integer

并将其设置为...

Public Function CountUnique(rng As Range) As Long

然后它将处理更多独特的项目。

JustinG's function works very well (and fast) until the number of unique items exceeds 32,767 due to some type of limit in Excel.

I found if you modify his code

Public Function CountUnique(rng As Range) As Integer

and make it as...

Public Function CountUnique(rng As Range) As Long

It will then handle more unique items.

回复 原文
风启觞 2024-08-17 05:37:14

对于仍然尝试使用 @JustinG 的字典方法的任何人，如果您使用的是较新版本的 VBA，则需要稍微更改代码。

您需要引用“Microsoft Scripting Runtime”并在 Dictionary 术语前添加 Scripting，如下所示：

Public Function CountUnique(rng As Range) As Long
    Dim dict As Scripting.Dictionary
    Dim cell As Range
    Set dict = New Scripting.Dictionary
    For Each cell In rng.Cells
         If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, 0
        End If
    Next
    CountUnique = dict.Count
End Function

For anyone still trying to use @JustinG's dictionary method, you will need to alter the code slightly if you're using a newer version of VBA.

You'll need to reference 'Microsoft Scripting Runtime' and prefix the Dictionary terms with Scripting, as follows:

Public Function CountUnique(rng As Range) As Long
    Dim dict As Scripting.Dictionary
    Dim cell As Range
    Set dict = New Scripting.Dictionary
    For Each cell In rng.Cells
         If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, 0
        End If
    Next
    CountUnique = dict.Count
End Function
回复 原文
傲鸠 2024-08-17 05:37:14

阅读本文并进一步调查后，我得到了一个比我在这里看到的任何东西都更适合我的方法：

Array-enter:
（Ctrl+Shift+Enter，并且不包括大括号）

{=SUM(IFERROR(1/COUNTIF(C2:C2080,C2:C2080),0))}

或者在 VBA 中：

MyResult = MyWorksheetObj.Evaluate("=SUM(IFERROR(1/COUNTIF(C2:C2080,C2:C2080),0))")

它适用于数字和文本，它处理空白单元格，它处理引用单元格中的错误，并且它在 VBA 中工作。它也是我见过的最紧凑的解决方案之一。在 VBA 中使用它，它显然会自动处理数组公式的需要。

请注意，它处理错误的方式是简单地将错误包含在唯一值计数中。例如，如果有两个单元格返回#DIV/0！以及三个返回#VALUE! 的单元格，这 5 个单元格会将 2 添加到唯一值的最终计数中。如果您希望完全排除错误，则需要对此进行修改。

在我的测试中，上面 Jacob 的这个仅适用于数字，不适用于文本，并且不处理引用单元格中的错误（如果任何引用单元格返回错误，则返回错误）：

=SUM(IF(FREQUENCY(G4:G29,G4:G29)>0,1))

After reading through this and then investigating further, I've got one that works better for me than anything I see here:

Array-enter:
(Ctrl+Shift+Enter, and don't include the curly brackets)

{=SUM(IFERROR(1/COUNTIF(C2:C2080,C2:C2080),0))}

Or in VBA:

MyResult = MyWorksheetObj.Evaluate("=SUM(IFERROR(1/COUNTIF(C2:C2080,C2:C2080),0))")

It works for both numbers and text, it handles blank cells, it handles errors in referenced cells, and it works in VBA. It's also one of the most compact solutions I've seen. Using it in VBA, it apparently automatically handles the need to be an array formula.

Note, the way it handles errors is by simply including them in the count of uniques. For example, if you have two cells returning #DIV/0! and three cells returning #VALUE!, those 5 cells would add 2 to the final count of unique values. If you want errors completely excluded, it would need to be modified for that.

In my tests, this one from Jacob above only works for numbers, not text, and does not handle errors in referenced cells (returns an error if any of the referenced cells returns an error):

=SUM(IF(FREQUENCY(G4:G29,G4:G29)>0,1))
回复 原文
装迷糊 2024-08-17 05:37:14

看看 https://excelchamps.com/blog/count-unique-values-excel /。
这就是你的答案。

您需要输入的公式是：

=SUMPRODUCT(1/COUNTIF(C2:C2080,C2:C2080))

当您以数组形式输入此公式时，它将如下所示：

{=SUMPRODUCT(1/COUNTIF(C2:C2080,C2:C2080))}

Look at https://excelchamps.com/blog/count-unique-values-excel/.
There is your answer.

The formula you need to enter is:

=SUMPRODUCT(1/COUNTIF(C2:C2080,C2:C2080))

When you enter this formula as an array it will look something like this:

{=SUMPRODUCT(1/COUNTIF(C2:C2080,C2:C2080))}
回复 原文
前事休说 2024-08-17 05:37:14

自 2018 年秋季以来，使用新的动态数组函数可以更轻松地解决此问题（目前仅适用于 Office 365 客户端 - 该解决方案确实不适用于 Excel 2016 / 2019）：

=COUNTA(UNIQUE(C2:C2080))

UNIQUE 函数将生成范围内唯一值的数组，可以使用 COUNTA 进行计数。

如果您的范围内有空白，您可以使用FILTER函数将其过滤掉：

=COUNTA(UNIQUE(FILTER(C2:C2080,C2:C2080<>"")))

Since fall 2018, this problem can be solved much easier using the new dynamic array functions (currently only available for Office 365 clients - this solution does not work on Excel 2016 / 2019):

=COUNTA(UNIQUE(C2:C2080))

The UNIQUE function will result in an array of unique values in the range, which can be counted using COUNTA.

If you have blanks in your range, you may filter them out using the FILTER function:

=COUNTA(UNIQUE(FILTER(C2:C2080,C2:C2080<>"")))
回复 原文
醉生梦死 2024-08-17 05:37:14

这个公式对我有用。有一些事情可能会导致此功能不起作用。首先，所有目标单元格中​​都必须有一个值。另一个可能不起作用的示例是，如果您有一个单元格的值为 31，而另一个单元格的文本值为“31”。它将把这些识别为不同的值。

你可以试试这个：

=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""), IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))

这是一个数组公式。您必须按 ctrl+shift+enter 键，而不是直接按 Enter 键进行确认。

来自：

http://www.cpearson.com/excel/Duplicates.aspx

The formula works for me. There are a few things that could cause this to not work. First, all target cells must have a value in them. Another example of where this might not work is if you have one cell with the value 31 and another cell with a text value of "31". It will recognize these as different values.

You could try this:

=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""), IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))

This is an array formula. Instead of hitting just enter to confirm it you must hit ctrl+shift+enter.

Which is from:

http://www.cpearson.com/excel/Duplicates.aspx

回复 原文
红玫瑰 2024-08-17 05:37:14

这可能是处理大量行的更有效方法。这使用内置的 AdvancedFilter 命令，而不是一次循环遍历每个单元格。

Public Function UniqueValues(oRange As Range) As Variant

' Uses the built-in AdvancedFilter Excel command to return the unique values onto the Worksheet
' and then populate and retuns an array of unique values

' Note:  The index:0 element in the returned array will be the header row.
'        You can ignore this element unless the first row in your oRange is a unique value
'        in which case the header will be that value.

Dim oTarget As Range
Dim r As Long, numrows As Long
Dim vs1, vs2 As Variant

' Get the first unused cell on the first row where the unique vaues will be temporarily populated
   Set oTarget = oRange.SpecialCells(xlLastCell) ' the last cell on the worksheet
   Set oTarget = oTarget.Parent.Cells(1, oTarget.Column + 1) ' the first unused cell on the first row

' Copy the unique values from your oRange to the first unused cell on row 1
   oRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=oTarget, Unique:=True

' Get the number of rows including the first row which is the header
   numrows = WorksheetFunction.CountA(oTarget.EntireColumn)

' create an 2-dim array of the rows
   vs1 = oTarget.Resize(numrows)

' Prepare a second 1-dim array for the result
   ReDim vs2(numrows)

' Transfer the 2-dim array into the 1-dim array
   For r = 1 To UBound(vs1, 1)
      vs2(r - 1) = vs1(r, 1)
   Next

' Return the 1-dim array as the function result
   UniqueValues = vs2

' Clean up the extra column on the worksheet
   oTarget.EntireColumn.Delete

End Function

This might be a more efficient way of dealing with a large number of rows. This uses the inbuilt AdvancedFilter command instead of cycling through each cell at a time.

Public Function UniqueValues(oRange As Range) As Variant

' Uses the built-in AdvancedFilter Excel command to return the unique values onto the Worksheet
' and then populate and retuns an array of unique values

' Note:  The index:0 element in the returned array will be the header row.
'        You can ignore this element unless the first row in your oRange is a unique value
'        in which case the header will be that value.

Dim oTarget As Range
Dim r As Long, numrows As Long
Dim vs1, vs2 As Variant

' Get the first unused cell on the first row where the unique vaues will be temporarily populated
   Set oTarget = oRange.SpecialCells(xlLastCell) ' the last cell on the worksheet
   Set oTarget = oTarget.Parent.Cells(1, oTarget.Column + 1) ' the first unused cell on the first row

' Copy the unique values from your oRange to the first unused cell on row 1
   oRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=oTarget, Unique:=True

' Get the number of rows including the first row which is the header
   numrows = WorksheetFunction.CountA(oTarget.EntireColumn)

' create an 2-dim array of the rows
   vs1 = oTarget.Resize(numrows)

' Prepare a second 1-dim array for the result
   ReDim vs2(numrows)

' Transfer the 2-dim array into the 1-dim array
   For r = 1 To UBound(vs1, 1)
      vs2(r - 1) = vs1(r, 1)
   Next

' Return the 1-dim array as the function result
   UniqueValues = vs2

' Clean up the extra column on the worksheet
   oTarget.EntireColumn.Delete

End Function
回复 原文
望她远 2024-08-17 05:37:14

另一种方法是：

Sub CountUnique()
    Dim Count, x, a, lastRow, Values(), StringValues
    a = ActiveCell.Column
    a = GetLetterFromNumber(a)
    lastRow = Range(a & Rows.Count).End(xlUp).row
    Count = 0
    For Each c In Range(Range(a & "1"), Range(a & Rows.Count).End(xlUp))
        If c.row = 1 Then
            ReDim Values(lastRow)
            Values(Count) = c.Value
            Count = Count + 1
        End If
        StringValues = Join(Values, "#")
        StringValues = "#" + StringValues
        If InStr(1, StringValues, c.Value) = 0 Then
            Values(Count) = c.Value
            Count = Count + 1
        End If
    Next c
    MsgBox "There are " & Count & " unique values in column " & a
End Sub

您只需让活动单元格位于您正在计数的列的第 1 行即可。

Another way to do this is this:

Sub CountUnique()
    Dim Count, x, a, lastRow, Values(), StringValues
    a = ActiveCell.Column
    a = GetLetterFromNumber(a)
    lastRow = Range(a & Rows.Count).End(xlUp).row
    Count = 0
    For Each c In Range(Range(a & "1"), Range(a & Rows.Count).End(xlUp))
        If c.row = 1 Then
            ReDim Values(lastRow)
            Values(Count) = c.Value
            Count = Count + 1
        End If
        StringValues = Join(Values, "#")
        StringValues = "#" + StringValues
        If InStr(1, StringValues, c.Value) = 0 Then
            Values(Count) = c.Value
            Count = Count + 1
        End If
    Next c
    MsgBox "There are " & Count & " unique values in column " & a
End Sub

You just have to have the active cell be on row 1 of the column that you are counting.

回复 原文
~没有更多了~

关于作者

怎言笑

暂无简介

0 文章
0 评论
664 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

留蓝

文章 0 评论 0

18790681156

文章 0 评论 0

zach7772

文章 0 评论 0

Wini

文章 0 评论 0

ayeshaaroy

文章 0 评论 0

初雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文