☰

Excel에서 여러 열의 고유한 조합 수 계산

tag-icon • tag-icon microsoft-excel
Excel에서 여러 열의 고유한 조합 수 계산

스프레드시트에 두 개의 열이 있는데 각 조합이 표시되는 횟수를 결정하는 수식을 작성해야 합니다. 예:

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 테이블로 가져오는 방식으로 매우 쉽게 수행했지만 스프레드시트에 해당 두 열을 배치하는 것만으로 누구나 수행할 수 있도록 만들어야 합니다. 그런 다음 수량 테이블로 두 번째 시트를 채워야 합니다.

답변1

편집하다: 간단한 솔루션(신용은 SaintWacko에게 있음)

두 열을 연결합니다.

=$A2&$B2

연결된 열을 '행 레이블'과 '값'으로 사용하여 피벗 테이블을 만듭니다. 그런 다음 '값'이 개수를 계산하는지 확인하세요(기본값이어야 함).

복잡한 솔루션

C열의 두 열을 연결할 수 있습니다.

=$A2&$B2

D 열에는 각 항목 중 하나에 대해서만 값이 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

그렇게 해야 합니다.

관련 정보