Excel - 使用多个条件计算唯一值

Question

我有一个大规模数据集，正在寻找一种方法来计算唯一值的数量：

Day -ID,    Location-ID,    Truck-ID

1,           18,              1

1,           19,              1

1,           18,              2

2,           18,              2

2,           18,              2

2,           18,              1

2,           20,              1

3,           21,              2

3,           21,              1

我想要知道每天有多少辆卡车用于服务每个地点？
例如，第 2 天有多少辆卡车用于服务地点#18？

答案是 2，因为虽然地点 18、第 2 天有 3 个条目，但只有 2 个唯一 ID。

Answer 1

这个 VBa 可以满足你的要求

Public Sub Truckers()

'YOU NEED TO UPDATE THIS FIRST BIT

Dim entryOfLocation As String
entryOfLocation = "G1"

Dim entryOfDay As String
entryOfDay = "G2"

Dim result As String
result = "G3"

Range(result).Value = 0

Dim startingRow As Integer
startingRow = 2

Dim dayColumn As String
dayColumn = "A"

Dim locationColumn As String
locationColumn = "B"

Dim truckColumn As String
truckColumn = "C"

'STOP MAKING    ANY     UPDATES  :)

Dim uniqueIds As String

Do While (Range(dayColumn & startingRow).Value <> "")

    If Range(dayColumn & startingRow).Value = Range(entryOfDay).Value And Range(locationColumn & startingRow).Value = Range(entryOfLocation).Value Then

        Dim truckId As Integer
        truckId = Range(truckColumn & startingRow).Value

    Dim doesAlreadyExist As Boolean
    doesAlreadyExist = False

    Dim i As Integer

    Dim splitMe() As String
    splitMe = split(uniqueIds, ",")

    For i = 0 To UBound(splitMe)

        If Not splitMe(i) = "" Then

            If Replace(splitMe(i), ",", "") = truckId Then
                doesAlreadyExist = True
                Exit For
            End If

        End If

    Next i

    If UBound(splitMe) = -1 Then
    uniqueIds = uniqueIds & truckId & ","

    End If


    If Not doesAlreadyExist Then
        Range(result).Value = Range(result).Value + 1
    End If


    End If

startingRow = startingRow + 1
Loop

End Sub