在Excel中计算唯一值 [英] Count unique values in Excel

问题描述

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

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)) 

返回不正确的值。

UPD：Lame解决方案：

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宏（不确定是否这就是你以后的事情。）

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

符合（给出一个A1-A11填充的电子表格和B1-B11空白）的效果：

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

这篇关于在Excel中计算唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！