Excel에서 모든 가능한 조합을 나열하거나 생성하는 방법은 무엇입니까?

Excel에서 데이터를 다룰 때, 여러 목록에서 모든 가능한 조합을 생성해야 하는 상황에 직면할 수 있습니다. 예를 들어, 두 개 이상의 열 값이 있는 경우, 각 조합을 수동으로 나열하는 것은 특히 값의 수가 증가함에 따라 지루하고 오류가 발생하기 쉽습니다. 이 가이드에서는 Excel에서 모든 가능한 조합을 빠르게 나열하거나 생성하는 다양한 기술을 살펴보겠습니다.

수식을 사용하여 두 목록에서 모든 가능한 조합 나열 또는 생성하기

VBA 코드를 사용하여 세 개 이상의 목록에서 모든 가능한 조합 나열 또는 생성하기

Kutools for Excel을 사용하여 여러 목록에서 모든 가능한 조합 나열 또는 생성하기

수식을 사용하여 두 목록에서 모든 가능한 조합 나열 또는 생성하기

다음 긴 수식은 두 목록의 값을 기반으로 모든 가능한 조합을 신속하게 나열하는 데 도움이 될 수 있습니다. 아래 단계를 따르세요:

1. 아래 수식을 빈 셀에 입력하거나 복사하세요. 이 경우 D2 셀에 입력한 다음 'Enter' 키를 눌러 결과를 확인합니다. 스크린샷 참조:

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$5)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$5,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

2. 그런 다음 D2 셀을 선택하고 빈 셀이 나타날 때까지 채우기 핸들을 아래로 드래그합니다. 두 목록의 값에 기반한 모든 가능한 조합이 나열됩니다. 스크린샷 참조:

VBA 코드를 사용하여 세 개 이상의 목록에서 모든 가능한 조합 나열 또는 생성하기

위 수식이 특히 여러 열의 데이터와 함께 사용할 때 적용하기 어렵다면 수정이 어려울 수 있습니다. 이러한 경우 VBA 코드는 더 빠르고 유연한 솔루션을 제공합니다.

1. "ALT + F11" 키를 눌러 "Microsoft Visual Basic for Applications" 창을 엽니다.

2. "삽입" > "모듈"을 클릭하고 "모듈" 창에 다음 코드를 붙여넣습니다.

VBA 코드: 3개 또는 여러 열의 모든 조합 생성하기

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A5")  'First column data
Set xDRg2 = Range("B2:B4")  'Second column data
Set xDRg3 = Range("C2:C4")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

3. 그런 다음 "F5" 키를 눌러 코드를 실행하면 3개 열의 모든 조합이 한 번에 생성됩니다. 스크린샷 참조:

Kutools for Excel을 사용하여 여러 목록에서 모든 가능한 조합 나열 또는 생성하기

여러 값 목록에 대해 모든 가능한 조합을 나열해야 하는 경우 코드를 수정하기 어려울 수 있습니다. 여기서 강력한 도구인 "Kutools for Excel"을 추천합니다. 이 도구에는 주어진 데이터 목록에 기반하여 모든 가능한 조합을 신속히 나열할 수 있는 "모든 조합 나열"이라는 유용한 기능이 포함되어 있습니다.

1. "Kutools" > "삽입" > "모든 조합 나열"을 클릭합니다. 스크린샷 참조:

2. "모든 조합 나열" 대화상자에서 아래 데모에 표시된 작업을 수행합니다.

3. 그런 다음 지정된 모든 값과 구분 기호가 대화상자에 나열됩니다. 스크린샷 참조:

4. 그리고 "확인" 버튼을 클릭하면 결과를 출력할 셀을 선택하라는 알림 상자가 나타납니다. 스크린샷 참조:

5. "확인"을 클릭하면 주어진 목록에 기반한 모든 가능한 조합이 아래 스크린샷에 표시된 것처럼 워크시트에 생성됩니다.

관련 기사 더 보기: