时间戳

首页 > 解决方案 > 使用数据透视表计算唯一值

excel - 使用数据透视表计算唯一值

问题描述

请帮助我创建一个 Excel 数据透视表，用于计算下表中唯一值的数量。我有下表，其中包含服务器机架连接电缆及其类型：

Source SDevice  Dest DDevice Cable_tape 
1.1    node1    2.1  switch1 Fiber
1.1    node2    2.2  switch2 Copper
2.1    server1  3.7  switch1 SAN
2.1    node12   1.1  switch5 Fiber

我想创建一个数据透视表，该表将根据唯一的端到端方向计算每种电缆类型的总数，因此输出将类似于以下内容：

From  To     Copper  SAN  Fiber
1.1   2.1                   2
1.1   2.2      1            
2.1   3.7             1

如果源和目标以相反的方向存在，则它应该在结果中只出现一次（如 1.1 > 2.1 然后 2.1 > 1.1）。是否可以使用数据透视表来实现这一点？提前感谢您的支持。

标签： excelexcel-formulapivot-table

解决方案

假设您的Source和Dest列始终可以解析为数字，您可以添加一个新列来计算您的路径...类似于=MIN([@Source],[@Dest]) & " -> " & MAX([@Source],[@Dest]).

然后，您的数据透视表将只将此字段作为行，将电缆类型作为列：

在此处输入图像描述

但是，这并没有考虑到不可解析为数字1.1.1的值（例如，当解析为数字时相同但语义不同的值（例如1.1和1.10）。

为了解决这个问题，您可以在 VBA 中创建 2 个用户定义的函数，并从 Excel 中调用它们。我不确定您是否完全熟悉 VBA（我知道您的问题肯定没有被标记VBA。可以在这里找到在 Excel 中编写 VBA 函数的一个很好的介绍。您需要如下函数：Option Explicit

    Public Function StringMax(rng As Excel.Range, Optional ignoreCase As Boolean = True) As Variant
        Dim cell As Excel.Range, maxValue As String, comp As VbCompareMethod

        If ignoreCase Then
            comp = vbTextCompare
        Else
            comp = vbBinaryCompare
        End If

        ' make sure the range you're passing in only has 1 column
        If rng.Columns.Count > 1 Then
            StringMax = CVErr(XlCVError.xlErrNA)
        Else
            ' loop through cells in the range, checking if each one is > the current max
            StringMax = CStr(rng.Cells(1, 1).Value)
            For Each cell In rng.Cells
                If VBA.StrComp(CStr(cell.Value), StringMax, comp) > 0 Then
                    StringMax = CStr(cell.Value)
                End If
            Next cell
        End If

    End Function

    Public Function StringMin(rng As Excel.Range, Optional ignoreCase As Boolean = True) As Variant
        Dim cell As Excel.Range, maxValue As String, comp As VbCompareMethod

        If ignoreCase Then
            comp = vbTextCompare
        Else
            comp = vbBinaryCompare
        End If

        ' make sure the range you're passing in only has 1 column
        If rng.Columns.Count > 1 Then
            StringMin = CVErr(XlCVError.xlErrNA)
        Else
            ' loop through each cell in the range, checking if each one is < the current max
            StringMin = CStr(rng.Cells(1, 1).Value)
            For Each cell In rng.Cells
                If VBA.StrComp(CStr(cell.Value), StringMin, comp) < 0 Then
                    StringMin = CStr(cell.Value)
                End If
            Next cell
        End If

    End Function

推荐阅读