엑셀에서 목록을 사용하여 가능한 모든 조합을 만들어야 하는 경우 어떻게 하면 쉽고 빠르게 조합을 만들 수 있을까요?
목록이 세 개 이상이면 Power Query를 사용하여 모든 조합을 생성하는 방법이 가장 효과적입니다.
하지만 수식이 조금 길어지는 단점은 있지만 수식을 사용하여 간단하게 조합을 생성할 수도 있습니다.
이번 글에서는 Excel 엑셀 두 개 이상의 목록에서 가능한 모든 조합을 만드는 방법(수식 사용)에 대해 설명해 보겠습니다.
엑셀에서 두 개 목록에 대한 모든 조합 생성하기
1. 아래 엑셀 화면은 품목이 나열 된 A열과 등급이 나열 된 B열이 존재합니다.
2. “품목”과 “등급”으로 조합할 수 있는 경우는 수는 3×3으로 계산되어 총 9개의 조합이 만들어 질 수 있습니다.
3. 수식은 다음과 같습니다.
=TEXTSPLIT(TEXTJOIN(“@”, TRUE, INDEX(A2:A4, INT((SEQUENCE(COUNTA(A2:A4)*COUNTA(B2:B4), 1, 0))/COUNTA(B2:B4))+1) & “|” & INDEX(B2:B4, MOD(SEQUENCE(COUNTA(A2:A4)*COUNTA(B2:B4), 1, 0), COUNTA(B2:B4))+1)),”|”,”@”) |
4. TEXTJOIN 수식만 사용했을 때 결과는 다음과 같습니다.
- 수식 : =TEXTJOIN(“@”, TRUE, INDEX(A2:A4, INT((SEQUENCE(COUNTA(A2:A4)*COUNTA(B2:B4), 1, 0))/COUNTA(B2:B4))+1) & “|” & INDEX(B2:B4, MOD(SEQUENCE(COUNTA(A2:A4)*COUNTA(B2:B4), 1, 0), COUNTA(B2:B4))+1))
- 결과 : 사과|상@사과|중@사과|하@바나나|상@바나나|중@바나나|하@수박|상@수박|중@수박|하
5. TEXTSPLIT 수식으로 구분자 “|”과 “@”를 각 행과 열의 셀로 나누어지도록 처리했습니다.
엑셀에서 세 개 이상 목록에 대한 모든 조합 생성하기
1. 앞 단락에서 2개 목록에 대한 조합 수식을 알아봤는데 3개 이상의 목록에 대한 조합은 아래 설명하는 수식을 참고하시면 됩니다.
2. “품목”과 “등급” 그리고 “재고” 이렇게 세 개의 목록으로 조합할 수 있는 경우는 수는 3x3x2으로 계산되어 총 18개의 조합이 만들어 질 수 있습니다.
3. 수식은 다음과 같습니다.
=TEXTSPLIT(TEXTJOIN(“@”,TRUE,TOCOL(TOCOL(A2:A4&”|”&TRANSPOSE(B2:B4))&”|”&TRANSPOSE(C2:C3))),”|”,”@”) |
마무리
미리 정리된 여러 목록을 사용하여 모든 경우의 수 만큼의 조합을 만드는 작업은 쉽고 단순한 작업이지만 조합해야 할 목록이나 대상이 많아지면 무척 힘들어지게 됩니다.
조합으로 만들어 지는 경우의 수는 각 목록 수를 모두 곱하기 한 결과 만큼의 경우의 수가 나오게 됩니다.
두 가지 수식을 설명하기 위해 두 개 목록, 세 개 이상 목록으로 단락을 나누어 설명을 하였지만 사실 목록 개수에 상관 없이 두 가지 수식을 모두 활용해서 사용할 수 있습니다.
※ 함께 읽으면 도움이 될 만한 다른 포스팅 글입니다.
♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.