每个值出现在列中多少次? [英] How many times does each value appear in a column?

问题描述

我有一列这样的时间数据:

I have a column with time data like this:

17:27:31
17:27:32
17:27:32
17:27:33
17:27:33
17:27:34
17:27:34
17:27:34
17:27:35
17:27:36

我想计算每个值重复多少次:

I want to count how many times each value repeats:

Value      Count
17:27:31   1
17:27:32   2
17:27:33   2
17:27:34   3
17:27:35   1
17:27:36   1

我该怎么做?

推荐答案

我赞同Dave的想法.我并不总是喜欢透视表，但是在这种情况下，它们非常易于使用.

I second Dave's idea. I'm not always fond of pivot tables, but in this case they are pretty straightforward to use.

这是我的结果:

创建它是如此简单，以至于我甚至都记录了一个宏，以防您需要使用 VBA :

It was so simple to create it that I have even recorded a macro in case you need to do this with VBA:

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("Table1[[#All],[DATA]]").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet3!R3C7", TableName:="PivotTable4", DefaultVersion:= _
        xlPivotTableVersion14
    Sheets("Sheet3").Select
    Cells(3, 7).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("DATA")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("DATA"), "Count of DATA", xlCount
End Sub

这篇关于每个值出现在列中多少次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！