多列中的唯一值

多列中的唯一值

我有一个包含以下内容的电子表格:-

Prod No     Store 1 $    Store 1 Qty    Store 2 Sale    Store 2 Qty  etc
A               4.00         1             7.50            2
B               0            0             15.00           1
C               4.00         2              -8             -1
D               5.00         1              5.00           1

我需要获取每个零件编号的所有唯一价格,然后计算每个价格的总数量,例如,

Prod No A has 4.00 1 unit and 7.50 2 units
Prod No B has 0.00 0 units and 15.00 1 unit
Prod No C has 4.00 2 units and -8 -1 unit
Prod No D has 5.00 2 units

我还需要一份仅包含特定价格以及每种价格的总数量清单

例如,

4.00 3 units
7.50 2 units
0.00 0 units
-8 -1 unit
15.00 1 unit
5.00 2 units

答案1

首先,我认为从长远来看,如果你以更水平的形式存储数据,你会更幸运,例如

Store      Product     Qty    Sales
Store 1    A           1      4.00
Store 1    B           0      0.00
Store 1    C           2      4.00
Store 1    D           0      0.00

在单个列上进行查找比在一对列上进行查找要容易得多。

(根据大小和规模,具有单独的商店、产品和销售表的 Access 数据库甚至可能比这更好)

也就是说,如果您坚持现有的状态,并且您可以在工作表中接受 VBA 宏,那么您可以尝试以下操作:

  1. 向您的 VBA 项目添加一个名为的类模块Tuple,其中包含:

    Private szKey As String
    Private nValue As Double
    
    Public Property Get Key() As String
        Key = szKey
    End Property
    Public Property Let Key(newKey As String)
        szKey = newKey
    End Property
    
    Public Property Get Value() As Double
        Value = nValue
    End Property
    Public Property Let Value(newValue As Double)
        nValue = newValue
    End Property
    
  2. 添加一个普通模块,例如Module 1添加到您的项目,其中包含:

    Public Function Summarize(ByRef rng As Range) As String
        If rng.Cells.Count Mod 2 = 1 Then Err.Raise 100, "", "Expected range of even cells"
    
        Dim coll As New Collection
    
        On Error Resume Next
        Dim flag As Boolean: flag = False
        Dim prevCel As Range, cel As Range: For Each cel In rng.Cells
            If flag Then
                Dim Key As String: Key = "" & prevCel.Value2
                coll(Key).Value = coll(Key).Value + cel.Value2
    
                If Err.Number <> 0 Then
                    Err.Clear
                    Dim t1 As New Tuple
                        t1.Key = "" & prevCel.Value2
                        t1.Value = cel.Value2
                        coll.Add t1, Key
                    Set t1 = Nothing
                End If
            End If
            Set prevCel = cel
            flag = Not flag
        Next cel
        On Error GoTo 0
    
        Dim t2 As Variant: For Each t2 In coll
            If Len(Summarize) Then Summarize = Summarize & ", "
            Summarize = Summarize & Format(t2.Key, "#0.00") & " @ " & t2.Value
        Next t2
    End Function
    
  3. 然后,您可以在工作表中输入一个公式,例如:

    ="Product " & $A2 & " has " & Summarize($B2:$I2)
    

    确保将范围 $B2:$I2 替换为足够宽的范围,以涵盖所有可能的商店数量。另外,请确保使用偶数大小的范围(因为 Sale/Qty 值是成对的),否则您将收到错误#VALUE

相关内容