엑셀에서 목록 데이터로 작업을 할 때 여러 목록 각각에 대한 가능한 모든 조합(경우의 수)을 생성해야 하는 경우가 발생한다면 어떻게 하면 쉽게 가능한 모든 조합을 나열할 수 있을까요?
조합하려는 목록의 갯수가 적은 경우에는 수동으로 조합을 나열할 수 있습니다.
‘가’,’나’라는 목록과 ‘A’,’B’라는 두 목록으로 만들 수 있는 조합은 ‘가-A’, ‘가-B’, ‘나-A’, ‘나-B’ 이렇게 네 개의 조합이 만들어지게 됩니다.
목록 수가 2개 이하면 이렇게 수동으로 조합을 나열할 수 있지만 3개 이상만 되면 수동으로 조합을 만들어 나열하기는 정말 귀찮은 작업이 되고, 중간에 빠뜨리는 오류를 발생하기도 쉽습니다.
일반적으로 A,B 두 목록이 있을 경우 만들어 지는 조합의 수는 A 곱하기 B (A*B)만큼 만들어지게 됩니다.
이번 글에서는 Excel 엑셀에서 두 목록 값의 가능한 모든 조합을 생성하는 방법(가능한 모든 경우의 수 나열)에 대해 알아보겠습니다.
엑셀에서 수식을 사용하여 두 목록에서 가능한 모든 조합(경우의 수) 만들기
1. 만약 조합하는 목록이 2개인 경우 수식을 이용하여 간단하게 두 목록 값의 가능한 모든 조합을 만들 수 있습니다.
2. 수식이 조금 긴 편이지만 셀 범위만 수정해서 바로 가능한 조합의 목록을 나열할 수 있습니다.
- 수식 : =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))
3. 이해를 돕기 위해 여기에서 사용된 수식의 셀 범위에 대해서 간략히 설명해 보겠습니다.
- “$A$2:$A$5“는 첫 번째 목록의 열 값의 범위입니다.
- “$B$2:$B$4“는 두 번째 목록의 열 값의 범위입니다.
- “$D$2“는 수식을 입력하는 셀 입니다.
- 목록의 위치나 개수가 달라지면 셀 참조만 변경해서 사용하면 됩니다.
VBA 코드를 사용하여 세 개 이상의 목록에서 가능한 모든 조합(경우의 수) 만들기
1. 만약 조합하려는 목록이 3개 이상인 경우라면 VBA 코드를 사용하여 쉽게 목록을 조합해서 결과를 나열할 수 있습니다.
VBA 코드는 아래와 같습니다.
Sub ListAllCombinations() Dim xRange1, xRange2, xRange3 As Range Dim xTRange As Range Dim xSep As String Dim xCnt1, xCnt2, xCnt3 As Integer Dim xStr1, xStr2, xStr3 As String Set xRange1 = Range(“A2:A5”) ‘첫번째 목록 열 Set xRange2 = Range(“B2:B4”) ‘두번째 목록 열 Set xRange3 = Range(“C2:C5”) ‘세번째 목록 열 xSep = “-“ ‘조합구문자 Set xTRange = Range(“D2”) ‘조합 결과 For xCnt1 = 1 To xRange1.Count xStr1 = xRange1.Item(xCnt1).text For xCnt2 = 1 To xRange2.Count xStr2 = xRange2.Item(xCnt2).text For xCnt3 = 1 To xRange3.Count xStr3 = xRange3.Item(xCnt3).text xTRange.Value = xStr1 & xSep & xStr2 & xSep & xStr3 Set xTRange = xTRange.Offset(1, 0) Next Next Next End Sub |
2. 아래 화면은 실제 VBA 화면입니다.
3. 위의 VBA 코드를 실행하면 A열, B열, C열 이렇게 3개의 열에 대한 모든 가능한 조합을 D열에 나열하게 됩니다.
마무리
이상으로 Exce에서 가능한 모든 조합 또는 경우의 수를 만드는 방법에 대해 수식과 VBA 코드를 활용하는 방법에 대해 알아보았습니다.
조합을 하고자 하는 목록이 2개라면 수식으로 간단히 처리하면 되고, 3개 이상인 경우 수식으로 처리하기가 너무 복잡해 지는 경우에는 VBA 코드를 사용해서 간단하게 가능한 조합을 간편하게 만들 수 있습니다.
VBA 사용에 익숙지 않은 경우 아래 다른 포스팅 글을 참고하시면 도움이 됩니다.
※ 함께 읽으면 도움이 될 만한 다른 포스팅 글입니다.
♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.