☰

如何取得另一列中每組相同值的欄位中存在的唯一值？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
如何取得另一列中每組相同值的欄位中存在的唯一值？

我有以下兩列：

Con.By  Prod
   A     1
   A     1
   A     2
   A     2
   B     1
   B     1
   B     2
   B     2

Con.By我可以輕鬆獲得 column或 column中的唯一值Prod。但是，我的要求是Prod為 column 中的每個唯一值取得Column 中的唯一值Con.By。因此，我對上述資料列的預期輸出是：

Con.By  Prod
   A     1
         2
   B     1
         2

我甚至不知道從哪裡開始。我嘗試編寫 VBA 程式碼來Con.By單獨過濾列中的每個值，然後在Prod列中找到唯一值。但是，此方法不起作用，因為我的Con.By專欄包含太多條目，其中一些可能會隨著時間而變化。

獲得我需要的輸出的最佳方法是什麼？是否有 Excel 公式或需要 VBA 編碼？

答案1

你可以嘗試這個方法。它利用使用者定義的類別來幫助收集第二列中的唯一項目。

常規模組和類別模組中的程式碼都利用了以下事實：當您嘗試將與現有成員具有相同 Key 的成員新增至集合時，457將會產生錯誤。

您可以在程式碼中查看在何處進行變更以解決工作表以及來源 (Src) 和結果 (Res) 範圍中的差異。

您必須重新命名類別模組cConBy。您先請Insert Class Module，F4打開屬性視窗。更改Name那裡的參數。

班級模組

Option Explicit
Private pConBy As String
Private pProd As String
Private pProds As Collection

Private Sub Class_Initialize()
    Set pProds = New Collection
End Sub

Public Property Get ConBy() As String
    ConBy = pConBy
End Property
Public Property Let ConBy(Value As String)
    pConBy = Value
End Property

Public Property Get Prod() As String
    Prod = pProd
End Property
Public Property Let Prod(Value As String)
    pProd = Value
End Property

Public Function AddProd(Value As String)
    On Error Resume Next
    pProds.Add Value, CStr(Value)
    On Error GoTo 0
End Function

Public Property Get Prods() As Collection
    Set Prods = pProds
End Property

常規模組

Option Explicit
Sub UniqueConBy()
    Dim cCB As cConBy, colCB As Collection
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes() As Variant
    Dim I As Long, J As Long, K As Long
    Dim lRowCount As Long

'Source and results location
Set wsSrc = Worksheets("Sheet1")
Set wsRes = Worksheets("Sheet1")
    Set rRes = wsRes.Cells(1, 5)
With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(columnsize:=2)
End With

'Collect and consolidate the data
Set colCB = New Collection
On Error Resume Next
For I = 2 To UBound(vSrc, 1)
    Set cCB = New cConBy
    With cCB
        .ConBy = vSrc(I, 1)
        .Prod = vSrc(I, 2)
        .AddProd .Prod
        lRowCount = lRowCount + 1
        colCB.Add cCB, CStr(.ConBy)
        Select Case Err.Number
            Case 457
                With colCB(CStr(.ConBy))
                    lRowCount = lRowCount - .Prods.Count - 1
                    .AddProd cCB.Prod
                    lRowCount = lRowCount + .Prods.Count
                End With
                Err.Clear
            Case Is <> 0
                MsgBox "Error: " & Err.Number & vbTab & Err.Description
                Stop
        End Select
    End With
Next I
On Error GoTo 0

'Create results array
ReDim vRes(0 To lRowCount, 1 To 2)

'column labels
For I = 1 To UBound(vRes, 2)
    vRes(0, I) = vSrc(1, I)
Next I

'populate the array
For I = 1 To colCB.Count
    With colCB(I)
        K = K + 1
        vRes(K, 1) = .ConBy
        vRes(K, 2) = .Prods(1)
        For J = 2 To .Prods.Count
            K = K + 1
            vRes(K, 2) = .Prods(J)
        Next J
    End With
Next I

Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With

End Sub

編輯：

另一種方法很接近您想要的，但輸出略有不同，即簡單地使用「資料功能區/資料工具」標籤上的「刪除重複項」選項。您將選擇 A 列和 B 列。

在應用此方法之前，請確保資料已排序（使用 VBA 方法不需要排序）。

根據您發布的數據，結果將如下所示：

在此輸入影像描述

您可以使用條件格式來消除 A 列中的重複條目。 Con.By 值仍然存在，但不可見。

在此輸入影像描述

答案2

嘗試以下配方，我認為它更容易理解，但可能不如羅恩的答案那麼自動化友好。

  1. 假設Con.By在 A 列和ProdB 列中，在另一列（例如 C）中，用分隔符號連接兩列，例如「_」：

    =A2&"_"&B2這與以下相同=CONCATENATE(A2,"_",B2)

  2. 使用您的範例，輸出將類似於等A_1。Paste Values

  3. 反白 D 列並使用功能區選單選擇Data -> Remove Duplicates。 D 列將如下所示： A_1 A_2

  4. 若要將資料拆分回兩個單獨的列，請使用功能區選單並選擇Data -> Text to Columns。選擇“分隔”

答案3

嘗試以下配方，我認為它更容易理解，但可能不如羅恩的答案那麼自動化友好。

  1. 假設Con.By在 A 列和ProdB 列中，在另一列（例如 C）中，用分隔符號連接兩列，例如「_」：

    =A2&"_"&B2這與以下相同=CONCATENATE(A2,"_",B2)

  2. 使用您的範例，輸出將類似於等A_1。Paste Values

  3. 反白 D 列並使用功能區選單選擇Data -> Remove Duplicates。 D 列將如下所示： A_1 A_2

  4. 若要將資料拆分回兩個單獨的列，請使用功能區選單並選擇Data -> Text to Columns。選擇Delimited第一個選項和Other第二個選項。您的分隔符號就是_在這種情況下。

這將為您提供接近您想要的結果。

相關內容