메뉴
실무 위키›응용 공식›상품과 지점의 모든 조합을 만드는 공식

상품과 지점의 모든 조합을 만드는 공식

2024
지원 버전 자세히 보기
엑셀 2024M365웹 엑셀

상품과 지점을 빠짐없이 짝지어 모든 조합을 만듭니다.

예제파일 다운로드
상품과 지점의 모든 조합을 만드는 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=LET ( p, FILTER ( 상품 범위, 상품 범위 <> "" ), b, FILTER ( 지점 범위, 지점 범위 <> "" ), n, SEQUENCE ( ROWS ( p ) * ROWS ( b ) ), HSTACK ( INDEX ( p, ROUNDUP ( n / ROWS ( b ), 0 ) ), INDEX ( b, MOD ( n - 1, ROWS ( b ) ) + 1 ) ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
상품 범위필수조합에 사용할 상품명과 빈 셀이 함께 있는 범위입니다. 비교식 <>""에서 <>는 같지 않음, ""는 빈 문자열이므로 상품명이 있는 셀만 남기는 조건 범위입니다.
지점 범위필수조합에 사용할 지점명과 빈 셀이 함께 있는 범위입니다. 비교식 <>""에서 빈 문자열과 같지 않은 지점명만 남기는 조건 범위입니다.
E2=LET(p,FILTER(A2:A5,A2:A5<>""),b,FILTER(C2:C6,C2:C6<>""),n,SEQUENCE(ROWS(p)*ROWS(b)),HSTACK(INDEX(p,ROUNDUP(n/ROWS(b),0)),INDEX(b,MOD(n-1,ROWS(b))+1)))
A
B
C
D
E
F
G
H
I
J
1
상품
지점
조합 상품
조합 지점
검산 항목
값
2
무선마우스
서울
무선마우스
서울
상품 수
3
3
키보드
부산
무선마우스
부산
지점 수
4
4
웹캠
대전
무선마우스
대전
예상 조합 수
12
5
광주
무선마우스
광주
결과 행 수
12
6
키보드
서울
7
키보드
부산
8
키보드
대전
9
키보드
광주
10
웹캠
서울
11
웹캠
부산
12
웹캠
대전
13
웹캠
광주
14
상품 3개와 지점 4개를 곱한 12개 조합이 상품 순서대로 만들어집니다. 각 상품 안에서는 서울, 부산, 대전, 광주 순서가 반복되며 E2:F13 범위에 자동으로 펼쳐집니다.
이 공식이 사용하는 함수
LET 함수 FILTER 함수 SEQUENCE 함수 ROWS 함수 HSTACK 함수 INDEX 함수 ROUNDUP 함수 MOD 함수

동작 원리

FILTER 함수가 빈 셀을 제외하고 SEQUENCE 함수가 조합 순번을 만들면, ROUNDUP 함수와 MOD 함수가 상품 및 지점 번호를 계산하고 INDEX 함수와 HSTACK 함수가 두 목록을 짝지어 놓습니다.

예제 시트에서 모든 조합이 시작되는 E2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

빈 상품을 제외합니다

상품 범위에서 빈 셀을 제외하면 무선마우스, 키보드, 웹캠 세 개가 원래 입력 순서대로 남습니다.

=FILTER ( A2:A5, A2:A5 <> "" )
= {무선마우스; 키보드; 웹캠}
2

빈 지점을 제외합니다

지점 범위에서 빈 셀을 제외하면 서울, 부산, 대전, 광주 네 개가 원래 입력 순서대로 남습니다.

=FILTER ( C2:C6, C2:C6 <> "" )
= {서울; 부산; 대전; 광주}
3

순번을 상품과 지점 번호로 바꿉니다

상품 수 3과 지점 수 4를 곱한 1~12번 순번을 만듭니다. 상품 번호는 네 번씩 유지되고 지점 번호는 1부터 4까지 반복됩니다.

=LET ( n, SEQUENCE ( ROWS ( p ) * ROWS ( b ) ), HSTACK ( ROUNDUP ( n / ROWS ( b ), 0 ), MOD ( n - 1, ROWS ( b ) ) + 1 ) )
= {1,1; 1,2; 1,3; 1,4; 2,1; 2,2; 2,3; 2,4; 3,1; 3,2; 3,3; 3,4}
4

번호에 맞는 상품과 지점을 가져옵니다

각 번호에 해당하는 상품명과 지점명을 가져와 가로로 붙이면 모든 상품과 지점의 12개 조합이 완성됩니다.

=LET ( p, FILTER ( A2:A5, A2:A5 <> "" ), b, FILTER ( C2:C6, C2:C6 <> "" ), n, SEQUENCE ( ROWS ( p ) * ROWS ( b ) ), HSTACK ( INDEX ( p, ROUNDUP ( n / ROWS ( b ), 0 ) ), INDEX ( b, MOD ( n - 1, ROWS ( b ) ) + 1 ) ) )
= {무선마우스, 서울; 무선마우스, 부산; 무선마우스, 대전; 무선마우스, 광주; 키보드, 서울; 키보드, 부산; 키보드, 대전; 키보드, 광주; 웹캠, 서울; 웹캠, 부산; 웹캠, 대전; 웹캠, 광주} E2:F13 범위에 열두 행과 두 열로 표시됩니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료