☰

Excel で複数の列の一意の組み合わせの数を数える

tag-icon • tag-icon microsoft-excel
Excel で複数の列の一意の組み合わせの数を数える

スプレッドシートに 2 つの列があり、それぞれの組み合わせが何回表示されるかを判断する数式を記述する必要があります。例:

A  1
A  1
A  2
A  2
A  2
B  1
B  1
B  2

結果は次のようになります:

A  1  2
A  2  3
B  1  2
B  2  1

昨日は SQL テーブルにインポートして簡単に実行できましたが、スプレッドシートに 2 つの列を配置するだけで、誰でも実行できるようにする必要があります。そうすると、2 番目のシートに数量テーブルが入力されます。

答え1

編集： シンプルな解決策（SaintWacko による）

2 つの列を連結します。

=$A2&$B2

連結された列を「行ラベル」と「値」として使用して、ピボット テーブルを作成します。次に、「値」がカウントを計算していることを確認します (デフォルトである必要があります)。

複雑な解決策

列 C の 2 つの列を連結できます。

=$A2&$B2

列 D では、項目が別の項目の重複であるかどうかを判別して、各項目の 1 つに対してのみ値が true になるようにする関数が必要になります (これは、項目がデータ列で並べ替えられている場合にのみ機能しますが、マクロは後でそれを行います)。

=$C1<>$C2

次に、列 E で一致する場合の数を計算します。

=COUNTIF($C:$C,$C2)

マクロはすべての組み合わせを検索し、シート 2 に配置してカウントをコピーします。以下のコードにはいくつかの仮定が入っていますが、必要に応じて変更できる可能性があります。

Option Explicit

Sub GetCombinations()

    Dim sheet1, sheet2 As Worksheet
    Set sheet1 = Worksheets(1)
    Set sheet2 = Worksheets(2)

    Dim sStartColumn As String
    Dim iTopRow As Long
    Dim sEndColumn As String
    Dim iBottomRow As Long

    sStartColumn = "A"
    iTopRow = 1
    sEndColumn = "E"
    iBottomRow = sheet1.UsedRange.Rows.Count

    Dim Rng As Range
    Dim sRange1 As String
    sRange1 = sStartColumn & CStr(iTopRow) & ":" & sEndColumn & CStr(iBottomRow)

    Set Rng = sheet1.Range(sRange1)

    Rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
             Key2:=Range("B2"), Order2:=xlAscending, _
             Orientation:=xlSortColumns, Header:=xlYes

    Dim i, j As Integer

    j = 2

    For i = 2 To iBottomRow

        If sheet1.Cells(i, 4) Then

            sheet2.Cells(j, 1) = sheet1.Cells(i, 1)
            sheet2.Cells(j, 2) = sheet1.Cells(i, 2)
            sheet2.Cells(j, 3) = sheet1.Cells(i, 5)
            j = j + 1

        End If

    Next i

End Sub

それで大丈夫でしょう。

関連情報