☰

多列中的唯一值

tag-icon • tag-icon microsoft-excel worksheet-function
多列中的唯一值

我有一個電子表格，其中包含以下內容：-

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 範圍替換為足夠寬以覆蓋所有可能數量的 Stores 的範圍。還要確保您使用均勻大小的範圍（因為銷售/數量值是成對的），否則您會收到錯誤#VALUE。

相關內容