아트 에셋 수량 행렬을 항목당 워크시트 한 행으로 확장하려면 먼저 각 에셋–형식 조합과 누적 수량을 나열합니다. 그런 다음 SEQUENCE 및 XLOOKUP을 사용해 필요한 횟수만큼 레이블을 반복합니다. 이 안내에서는 합성 아트워크 레이블을 사용합니다.
작업은 간단합니다. 간결한 계획표를 항목 목록으로 바꾸면 됩니다. 수량이 2이면 같은 에셋과 형식의 행이 두 개 생성되고, 0이면 생성되지 않습니다. 아래 예제에서는 중간 계산을 표시해 결과를 확인한 다음 자신의 데이터에 적용할 수 있습니다.
이 방법을 지원하는 Mac용 Excel 버전은 무엇인가요?
Mac용 Microsoft 365 Excel, Mac용 Excel 2024 또는 Mac용 Excel 2021을 사용하세요. Microsoft 문서에는 이 버전들이 SEQUENCE 및 XLOOKUP 모두에 해당하는 버전으로 나와 있으며, Mac용 Excel 2021 기능 가이드에는 동적 배열이 명시적으로 설명되어 있습니다. SEQUENCE 함수, XLOOKUP 함수, Mac용 Excel 2021의 새로운 기능.
다른 안내서에서 수식을 복사할 때는 함수 지원 여부가 중요합니다. 예를 들어 Microsoft는 Mac용 Microsoft 365 및 Excel 2024에서 VSTACK를 사용할 수 있다고 명시하지만, Excel 2021은 목록에 포함하지 않습니다. 여기의 방법은 해당 함수에 의존하지 않습니다. 또한 Microsoft는 XLOOKUP가 Excel 2016 및 Excel 2019에서는 사용할 수 없다고 명시하므로, 이 버전에서는 아래 수식을 그대로 실행할 수 없습니다. VSTACK 함수, XLOOKUP 함수.
아트 에셋 수량 행렬 설정하기
빈 워크시트의 A1:C4에 다음 합성 데이터를 입력합니다. 첫 번째 열에는 에셋 레이블을, 머리글 행에는 형식을, 나머지 셀에는 수량을 입력합니다.
| 에셋 | 소형 | 대형 |
|---|---|---|
| 아크 타일 | 2 | 0 |
| 도트 타일 | 1 | 0 |
| 웨이브 타일 | 2 | 1 |
이 예제에서 모든 수량은 0 이상의 정수여야 합니다. 항목이 필요하지 않으면 0을 명시적으로 입력하세요. 진행하기 전에 빈 셀, 텍스트로 입력된 수량, 음수, 소수 및 셀 오류를 해결하세요. 이 수식은 유효한 수량을 전제로 하며, 값을 검증하거나 반올림하지 않습니다.
예상 출력에는 항목 행이 여섯 개 있습니다. 소형 아크 타일 두 개, 소형 도트 타일 하나, 소형 웨이브 타일 두 개, 대형 웨이브 타일 하나입니다. 레이블이 반복되는 것은 의도된 결과이며, 각 행이 하나의 항목을 나타냅니다. 수식은 워크시트 레코드를 만들며 아트워크 파일을 만들거나 복제하지 않습니다.
행렬을 확인하기 쉬운 보조 목록으로 펼치기
D 열은 비워 둡니다. E1:I1에 Position, Asset, Format, Quantity, End item 머리글을 입력합니다. E2:E7에는 1부터 6까지의 숫자를 순서대로 입력합니다. 에셋 3개에 형식 2개를 곱하면 조합이 6개이므로 보조 행은 6개입니다.
보조 목록은 각 원본 행을 왼쪽에서 오른쪽으로 읽은 다음 아래 행으로 이동합니다. 아크 타일 소형, 아크 타일 대형, 도트 타일 조합 2개, 웨이브 타일 조합 2개 순입니다. 이 순서를 일관되게 유지하는 것이 중요합니다.
각 조합의 레이블과 수량 가져오기
F2에 다음 수식을 입력합니다.
=INDEX($A$2:$A$4,INT((E2-1)/2)+1)
G2에 다음 수식을 입력합니다.
=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)
H2에 다음 수식을 입력합니다.
=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)
F2:H2를 7행까지 복사합니다. 이것들은 셀마다 하나의 결과를 반환하는 일반 셀 수식입니다. INDEX는 지정된 행과 열의 값을 가져옵니다. 여기서 INT은 두 위치마다 원본 행을 다음 행으로 이동시키고, MOD은 원본 열을 두 형식 사이에서 번갈아 선택합니다. INDEX 함수, INT 함수, MOD 함수.
각 조합의 최종 항목 번호 계산하기
I2에 다음 수식을 입력하고 I7까지 아래로 복사합니다.
=SUM($H$2:H2)
시작 참조를 고정하고 끝 참조를 이동시키면 누계가 만들어집니다. SUM는 첫 번째 보조 행부터 현재 행까지의 수량을 더합니다. SUM 함수.
완성된 보조 목록은 다음과 같아야 합니다.
| 위치 | 에셋 | 형식 | 수량 | 최종 항목 |
|---|---|---|---|---|
| 1 | 아크 타일 | 소형 | 2 | 2 |
| 2 | 아크 타일 | 대형 | 0 | 2 |
| 3 | 도트 타일 | 소형 | 1 | 3 |
| 4 | 도트 타일 | 대형 | 0 | 3 |
| 5 | 웨이브 타일 | 소형 | 2 | 5 |
| 6 | 웨이브 타일 | 대형 | 1 | 6 |
최종 항목은 경계값으로 해석하세요. 첫 번째 조합은 항목 번호 1과 2를 차지합니다. 세 번째 조합은 항목 번호 3을 차지합니다. 다섯 번째 조합은 4와 5를, 여섯 번째 조합은 6을 차지합니다. 수량이 0이면 경계값이 증가하지 않으므로 해당 조합에는 항목 번호가 없습니다.
항목당 출력 행 하나 생성하기
K1에 Asset을, L1에 Format을 입력합니다. 두 머리글 아래 셀은 비워 둡니다. 출력은 Excel 표 바깥의 일반 워크시트 셀에 배치하세요. Microsoft 문서에 따르면 표 안에서는 배열 수식의 스필이 지원되지 않습니다. 동적 배열 수식 및 스필 배열 동작.
K2에 다음 전체 수식을 입력합니다.
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))
L2에 다음 전체 수식을 입력합니다.
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))
각 수식을 Return으로 확정합니다. 이 두 수식은 아래로 복사하지 마세요. 배열 결과가 인접한 행을 자동으로 채웁니다. Microsoft는 이 자동 확장을 스필이라고 설명합니다. 동적 배열 수식 및 스필 배열 동작.
SEQUENCE는 총 수량에 따라 1부터 시작하는 항목 번호를 생성합니다. 각 번호에 대해 XLOOKUP은 누적 경계값을 검색합니다. 마지막 두 인수는 정확히 일치하거나 다음으로 큰 값을 검색하도록 지정하며, 처음부터 끝까지 검색합니다. 이 검색 방향은 0 때문에 합계가 반복되는 경우 첫 번째로 일치하는 경계값을 유지합니다. SEQUENCE 함수, XLOOKUP 함수.
바깥쪽 IF은 총합이 0일 때 빈 텍스트를 반환합니다. 이 경우 두 기준 셀은 비어 있는 것처럼 보이며 사용할 항목 레코드가 없습니다. 이는 두 결과 중 하나를 선택하는 IF을 이용한 의도된 0 합계 처리입니다. IF 함수.
제공된 행렬의 경우 K1:L7에는 다음 내용이 있어야 합니다.
| 에셋 | 형식 |
|---|---|
| 아크 타일 | 소형 |
| 아크 타일 | 소형 |
| 도트 타일 | 소형 |
| 웨이브 타일 | 소형 |
| 웨이브 타일 | 소형 |
| 웨이브 타일 | 대형 |
두 출력 열은 동일하게 생성된 항목 번호와 경계 범위를 사용합니다. 반환 범위만 다릅니다. F 열은 에셋 레이블을, G 열은 형식을 제공합니다. 수식을 편집할 때 조회 입력 범위를 서로 일치하게 유지하세요.
원본을 확장하기 전에 결과 확인하기
샘플 데이터로 다음 세 가지를 확인합니다.
- 합계 비교:
=SUM(B2:C4)및=SUM(H2:H7)는 모두 6을 반환해야 합니다. - 조합 비교: 출력에는 수량만큼 반복된, 0이 아닌 네 가지 조합이 포함되어야 합니다.
B2을 2에서 3으로 변경합니다. 예상 출력은 7개 행으로 늘어나며 아크 타일 소형 항목이 세 개가 됩니다. 그런 다음 값을 다시 2로 되돌립니다.
첫 번째 확인은 펼치는 과정에서 수량이 빠지거나 중복되는 문제를 찾습니다. 두 번째 확인은 합계만으로는 알 수 없는 레이블 불일치를 찾습니다. 세 번째 확인은 변경 사항이 보조 목록을 거쳐 출력에 반영되는지 점검합니다.
에셋 또는 형식 추가하기
B2:C4 안의 수량을 바꾸면 기존 설정을 그대로 사용할 수 있습니다. 행렬의 크기를 늘리려면 참조와 보조 목록 길이를 바꿔야 합니다.
전체 예제로 원본을 A1:D5의 에셋 4개와 형식 3개로 확장합니다. 보조 영역을 E:I에 유지하거나 그곳으로 옮기고, E2:E13에 1부터 12까지의 위치를 입력한 다음 F2:H2의 수식을 다음과 같이 바꿉니다.
=INDEX($A$2:$A$5,INT((E2-1)/3)+1)
=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)
=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)
이 수식들을 13행까지 복사하고, I 열의 누계 수식도 같은 행까지 확장합니다. 두 출력 수식에서 행 7로 끝나는 보조 범위를 모두 행 13으로 바꿉니다. 이제 각 에셋에 형식이 3개이므로 나누는 값과 나머지 계산의 기준은 3입니다. 이렇게 변경한 뒤 원본 합계와 개별 조합을 다시 확인하세요.
출력이 불완전하거나 막힌 경우 해결하기
Excel에 #SPILL!이 표시되면 출력 예정 영역에 기존 항목이나 병합된 셀이 있는지 확인하고, 수식이 Excel 표 바깥에 있는지 확인합니다. 워크시트 가장자리를 넘어가는 결과도 스필될 수 없습니다. Microsoft는 이러한 각 조건을 문서화하고 있습니다. #SPILL! 오류 수정 방법.
출력 행이 너무 적으면 조회 로직을 변경하기 전에 수량부터 확인합니다. Microsoft에 따르면 SUM는 참조된 범위의 텍스트를 무시합니다. 따라서 텍스트로 저장된 수량은 합계에서 제외될 수 있습니다. 원본 값을 수정하고 합계를 다시 확인하세요. SUM 함수.
합계는 맞는데 레이블이 잘못된 경우 보조 목록을 행렬과 셀 단위로 비교합니다. 각 보조 행에서 에셋, 형식, 수량 및 누적 경계값을 함께 유지하세요. 보조 열을 각각 따로 재정렬하지 마세요.
합계와 조합이 일치하면 두 출력 열을 항목 목록으로 사용하세요. 나중에 수량을 변경해도 반복 행의 근거를 추적할 수 있도록 행렬과 보조 계산을 유지하세요.
출처
- Mac용 Excel 2021의 새로운 기능 — Microsoft 지원.
- SEQUENCE 함수 — Microsoft 지원.
- XLOOKUP 함수 — Microsoft 지원.
- VSTACK 함수 — Microsoft 지원.
- INDEX 함수 — Microsoft 지원.
- INT 함수 — Microsoft 지원.
- MOD 함수 — Microsoft 지원.
- SUM 함수 — Microsoft 지원.
- IF 함수 — Microsoft 지원.
- 동적 배열 수식 및 스필 배열 동작 — Microsoft 지원.
- #SPILL! 오류 수정 방법 — Microsoft 지원.