在Excel中计算唯一值(Count unique values in Excel)

在Excel中计算唯一值(Count unique values in Excel)

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

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

返回不正确的值。

UPD：Lame解决方案：

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

最满意答案

=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