☰

別の列の同一値の各セットに対して、列に存在する一意の値を取得するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
別の列の同一値の各セットに対して、列に存在する一意の値を取得するにはどうすればよいですか?

次の 2 つの列があります。

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

Con.By列または列 のいずれかで一意の値を簡単に取得できます。ただし、私の要件は、列 の一意の値ごとに、Prod列 の一意の値を取得することです。したがって、上記のデータ列の意図した出力は次のようになります。ProdCon.By

Con.By  Prod
   A     1
         2
   B     1
         2

どこから始めればいいのかさえわかりません。Con.By各値を個別にフィルター処理して、列内の一意の値を見つけるVBA コードを記述しようとしました。ただし、列に含まれるエントリが多すぎて、一部は時間の経過とともに変化する可能性があるProdため、この方法は機能しません。Con.By

必要な出力を得るための最良の方法は何ですか? Excel の数式はありますか、それとも VBA コーディングが必要ですか?

答え1

この方法を試すことができます。ユーザー定義クラスを使用して、2 番目の列の一意の項目を収集するのに役立ちます。

通常のモジュールとクラス モジュールの両方のコードでは、既存のメンバーと同じキーを持つメンバーをコレクションに追加しようとするとエラーが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 の重複エントリを削除できます。例: =$A2=$A1 という数式を使用し、テキストの色を背景と同じ色に書式設定します。Con.By 値はそのまま残りますが、表示されなくなります。

ここに画像の説明を入力してください

答え2

次のレシピを試してみてください。これは理解しやすいと思いますが、Ron の回答ほど自動化には適していないかもしれません。

  1. Con.Byが列 A にあり、が列 B にあると仮定しProd、別の列 (たとえば C) で、区切り文字 (例: "_") を使用して 2 つの列を連結します。

    =A2&"_"&B2これは以下と同じです=CONCATENATE(A2,"_",B2)

  2. あなたの例を使用すると、出力はA_1次のようになります。列 CPaste Valuesのみを列 D にコピーします。

  3. 列 D を強調表示し、リボン メニューを使用して選択しますData -> Remove Duplicates。列 D は次のようになります。 A_1 A_2

  4. データを2つの列に分割するには、リボンメニューを使用してを選択しますData -> Text to Columns。「区切り」を選択します。

答え3

次のレシピを試してみてください。これは理解しやすいと思いますが、Ron の回答ほど自動化には適していないかもしれません。

  1. Con.Byが列 A にあり、が列 B にあると仮定しProd、別の列 (たとえば C) で、区切り文字 (例: "_") を使用して 2 つの列を連結します。

    =A2&"_"&B2これは以下と同じです=CONCATENATE(A2,"_",B2)

  2. あなたの例を使用すると、出力はA_1次のようになります。列 CPaste Valuesのみを列 D にコピーします。

  3. 列 D を強調表示し、リボン メニューを使用して選択しますData -> Remove Duplicates。列 D は次のようになります。 A_1 A_2

  4. データを 2 つの別々の列に分割するには、リボン メニューを使用して を選択しますData -> Text to Columns。Delimited最初のオプションとして を選択し、 2 番目のオプションとして を選択します。この場合、Other区切り文字は になります。_

これにより、希望に近い結果が得られます。

関連情報