엑셀 중첩 배열로 셀 하나에 표 하나 담는 방법

지금까지의 배열 동작과 중첩 배열의 차이

실무에서는 주문 번호 하나로 상품명, 수량, 단가, 금액처럼 여러 값을 검색할 때가 많습니다. 그럴 때 보통 함수를 복사해 오른쪽에 붙여 넣고 열 번호를 하나씩 바꿔 입력하는데, 이는 엑셀 수식이 처음 만들어질 때 수식 하나가 값 하나만 반환하도록 설계되었기 때문입니다.

1. 예제 파일을 실행한 후, 오른쪽 검색 표의 상품명 셀을 클릭하고 아래와 같이 VLOOKUP 함수를 입력합니다. 왼쪽 주문 내역 전체 범위에서 주문 번호를 찾되, 상품명은 세 번째 열에 있으므로 열 번호는 3, 마지막 인수는 정확히 일치(0)로 지정합니다.

   =VLOOKUP(찾을주문번호,주문내역범위,3,0)

   

2. 이번에는 열 번호를 중괄호로 묶고 그 안에 3, 4, 5, 6을 넣어 실행합니다. 그러면 상품명, 수량, 단가, 금액을 수식 하나로 한 번에 검색할 수 있습니다.

   =VLOOKUP(찾을주문번호,주문내역범위,{3,4,5,6},0)

   
   

   오빠두Tip : 이처럼 수식 하나로 여러 값을 반환하는 동적 배열 기능은 엑셀 2021 이후 버전에서 제공됩니다.
3. 데이터가 두 건 이상인 주문 번호는 VLOOKUP이나 XLOOKUP 함수만으로는 모두 검색할 수 없습니다. 이럴 때에는 아래와 같이 FILTER 함수를 입력해 주문 내역 전체 범위에서 주문 번호가 일치하는 데이터를 여러 건 한 번에 검색합니다.

   =FILTER(주문내역범위,주문번호범위=찾을주문번호)

   
   

   오빠두Tip : FILTER 함수는 엑셀 2021 이후 버전에서 제공됩니다.
4. 이제 FILTER 함수를 중괄호로 묶어 실행합니다. 그러면 FILTER 결과 범위가 셀 안에 한 번에 들어갑니다. 지금까지는 FILTER 수식을 아래로 자동 채우기하면 결과가 펼쳐질 범위가 다음 수식과 겹쳐 #분산! 오류가 발생해 매번 범위를 아래로 옮겨 가며 작업해야 했는데, 중첩 배열을 활용하면 이 문제를 해결할 수 있습니다.

   ={FILTER(주문내역범위,주문번호범위=찾을주문번호)}

   
   

   오빠두Tip : 중첩 배열은 현재 M365 베타 채널 사용자에게 먼저 순차적으로 배포되고 있으며, 아직 정식 채널에는 공개되지 않았습니다.
5. 완성된 수식을 아래로 자동 채우기합니다. 그러면 검색할 주문 번호를 새로 추가해도 FILTER 결과가 범위로 펼쳐지지 않고 각 셀 안에 한 번에 들어가는 보고서를 만들 수 있습니다.
   
6. 오른쪽 셀에 새롭게 추가된 FLATTEN 함수를 입력하면 중첩 배열을 다시 한 번에 펼칠 수 있습니다.

   =FLATTEN(중첩배열범위)

   

Ctrl + J 로 쉼표 데이터를 목록으로 바꾸기

쉼표로 나누어진 데이터를 받을 때가 가끔 있는데, 이런 데이터는 문자로 처리되어 특정 점수만 필터를 걸거나 합계를 계산하려면 데이터를 나누는 작업이 필요했습니다. 중첩 배열을 쉽게 쓸 수 있도록 이번에 함께 업데이트된 목록 기능을 사용하면 단축키 하나로 해결할 수 있습니다.

1. 쉼표로 나누어진 데이터 범위를 선택한 후 단축키 Ctrl + J 를 누릅니다. 그러면 범위가 목록으로 바뀌면서 문자로 처리되던 값이 숫자 데이터로 바뀌고, 필터를 열었을 때 점수별로 필터를 걸 수 있습니다.
   
   
   오빠두Tip : 목록은 삽입 탭에 있는 목록 버튼을 클릭해 풀거나 다시 걸 수도 있습니다.
2. 오른쪽 셀에 SUM 함수를 사용하면 목록에 담긴 숫자의 합계를 구할 수 있고, AVERAGE 함수를 사용하면 각 숫자의 평균을 구할 수 있습니다.

   =SUM(점수목록)

   =AVERAGE(점수목록)

   

3. 이후, 같은 방법으로 사무용품 요청 내역의 품목 범위를 선택하고 Ctrl + J 를 눌러 목록으로 바꿉니다. 목록으로 바꾸는 순간 데이터가 중첩 배열로 바뀌므로, 오른쪽 셀에 FLATTEN 함수를 입력해 전체 범위를 하나의 배열로 합칩니다.

   =FLATTEN(품목범위)

   

4. 오른쪽 셀에 UNIQUE 함수를 입력하면 FLATTEN 함수로 반환된 동적 범위에서 고유값만 출력할 수 있습니다.

   =UNIQUE(합친품목범위)

   

5. 이제 오른쪽 셀에 GROUPBY 함수를 입력해 합친 품목 범위를 집계합니다. 집계 조건에는 품목의 개수를 세는 함수를 지정하고, 정렬 방식에 -2를 넣어 두 번째 열의 개수가 큰 값부터 정렬되도록 합니다.

   =GROUPBY(합친품목범위,합친품목범위,개수함수,,,-2)

   

6. 마지막으로 집계 결과에 홈 탭의 조건부 서식으로 데이터 막대를 넣습니다. 그러면 쉼표로 작성되어 관리하기 쉽지 않던 데이터로 요약 보고서까지 만들 수 있습니다.
   

요청 품목별 상세 규격을 표 하나로 관리하기

요청 품목이 쉼표로 나뉘어 입력된 데이터에서 품목마다 제품 규격의 상세 내역을 하나씩 검색해 관리해야 할 때가 있습니다. 기존 함수만으로는 쉼표를 나누고, 품목을 각각 검색하고, 다시 합친 다음 표를 만들어야 해서 번거롭지만, 중첩 배열을 활용하면 함수 몇 개로 간단하게 해결할 수 있습니다.

1. 요청 품목 범위를 선택하고 Ctrl + J 를 눌러 목록으로 바꾼 후, 오른쪽 셀에 FLATTEN 함수를 입력해 요청 품목 목록을 범위로 바꿉니다.

   =FLATTEN(요청품목)

   

2. 이제 XLOOKUP 함수로 펼친 값을 하나씩 검색합니다. 검색 범위는 제품 규격 시트의 제품명으로, 반환할 범위는 제품 규격 전체 범위로 입력하면 각각의 요청 품목에 대한 검색 결과가 범위로 반환됩니다.

   =XLOOKUP(FLATTEN(요청품목),제품명범위,제품규격범위)

   

3. 반환된 범위를 다시 FLATTEN 함수로 펼칩니다. 그러면 각 항목에 대한 제품 규격 상세 정보가 검색됩니다.

   =FLATTEN(XLOOKUP(FLATTEN(요청품목),제품명범위,제품규격범위))

   

4. 완성된 수식을 중괄호로 묶어 중첩 배열로 바꾼 후 아래로 자동 채우기합니다. 그러면 각각의 제품 정보가 한 번에 정리됩니다.

   ={FLATTEN(XLOOKUP(FLATTEN(요청품목),제품명범위,제품규격범위))}

   

5. 이후, 오른쪽 셀에 아래와 같이 SUM 함수와 CHOOSECOLS 함수를 입력해 앞에서 만든 중첩 배열에서 금액이 있는 네 번째 열의 합계를 구합니다. 수식을 자동 채우기하면 요청 품목의 세부 정보와 합계를 함께 볼 수 있습니다.

   =SUM(CHOOSECOLS(상세규격표,4))

   

폴더 속 월별 CSV 파일을 함수 한 줄로 취합하기

M365 엑셀에 IMPORTCSV 함수가 업데이트되면서 여러 개의 CSV 파일도 엑셀로 바로 불러올 수 있게 되었습니다. 여기에 중첩 배열을 함께 활용하면 폴더 안에 월별로 나뉜 파일을 함수 한 줄로 취합할 수 있습니다.

1. 예제 파일의 구매 내역 폴더를 열면 월별로 데이터가 정리된 CSV 파일이 있습니다. 합칠 파일을 선택하고 단축키 Ctrl + Shift + C 를 동시에 눌러 파일 경로를 복사합니다.
   
   
   오빠두Tip : 파일을 우클릭한 후 중간에 있는 경로로 복사를 클릭해도 파일 경로를 복사할 수 있습니다.
2. 빈 셀을 클릭하고 IMPORTCSV 함수에 복사한 파일 경로를 붙여 넣어 실행하면 CSV 파일 데이터를 한 번에 불러올 수 있습니다. 이때 머리글은 제외하고 실제 데이터만 불러오도록 두 번째 인수인 건너뛸 행에 1을 입력합니다.

   =IMPORTCSV("파일경로",1)

   

3. 이제 파일 경로에서 파일명 부분을 지우고 그 자리에 큰따옴표 두 개를 넣은 후, 그 사이에 & 기호 두 개를, 다시 그 사이에 파일명이 입력된 셀을 선택해 넣습니다. 그러면 셀의 파일명에 따라 1월, 2월, 3월 데이터를 바꿔서 불러올 수 있습니다.

   =IMPORTCSV("폴더경로\"&파일명&".csv",1)

   

4. 파일명 셀 하나 대신 파일명 전체 범위를 선택해 한 번에 불러옵니다. 이때 빈칸까지 선택하면 빈칸 때문에 #VALUE! 오류가 반환되므로, 파일명 범위는 트리밍 참조로 지정해 값이 있는 실제 데이터만 참조하도록 합니다. 그러면 실제 데이터가 있는 범위만 확장해서 불러올 수 있습니다.

   =IMPORTCSV("폴더경로\"&파일명범위&".csv",1)

   

5. 합쳐진 범위를 FLATTEN 함수로 펼치면 파일명 범위에 입력된 달의 데이터가 하나로 취합됩니다. 일부 파일명을 지우면 3월, 4월, 5월처럼 원하는 달의 데이터만 불러올 수도 있습니다.

   =FLATTEN(IMPORTCSV("폴더경로\"&파일명범위&".csv",1))

   

6. 취합한 데이터를 SORT 함수로 묶고 금액이 있는 열을 내림차순으로 정렬하면 금액이 큰 것부터 집계하는 보고서도 만들 수 있습니다. 이번 예제에서는 금액이 일곱 번째 열에 있어 7을 입력했습니다.

   =SORT(FLATTEN(IMPORTCSV("폴더경로\"&파일명범위&".csv",1)),7,-1)

   

흩어진 교육 이수 내역을 직원별 표로 정리하기

직원마다 꼭 들어야 하는 필수 교육과정을 언제 이수했는지 과정별로 따로 정리한 데이터는 실무에서 가장 많이 겪게 되는 데이터 전처리 사례입니다. 이런 형태는 잘못된 구조라서 교육 과정들을 하나의 필드로 합쳐 주는 작업이 필요한데, 중첩 배열을 활용하면 함수 두 줄로 해결할 수 있습니다.

1. 교육 이수 내역 오른쪽 셀에 TOCOL 함수를 입력해 교육과정 범위를 한 열로 합칩니다. 데이터 중간중간에 빈칸이 있으므로 두 번째 인수에 1을 넣어 공백은 제외하고 실제 이수한 교육과정만 불러옵니다.

   =TOCOL(교육과정범위,1)

   

2. TOCOL 함수를 다시 WRAPROWS 함수로 묶고 두 번째 인수에 2를 넣어, 한 열로 합쳐진 데이터를 두 열로 바꿉니다. 그러면 각각의 교육과정이 깔끔하게 정리됩니다.

   =WRAPROWS(TOCOL(교육과정범위,1),2)

   

3. 이제 완성된 수식을 중괄호로 묶어 중첩 배열로 바꾼 후 아래로 자동 채우기합니다. 그러면 직원마다 어떤 과정을 이수했는지 하나의 표로 관리할 수 있습니다.

   ={WRAPROWS(TOCOL(교육과정범위,1),2)}

   

4. 오른쪽 셀에 HSTACK 함수를 입력해 왼쪽의 직원 목록 범위와 방금 만든 상세 교육과정 범위를 합칩니다. 그러면 직원별로 어떤 교육을 언제 이수했는지 하나의 표 안에서 볼 수 있습니다.

   =HSTACK(직원목록범위,상세교육과정범위)

   

5. 오른쪽 셀에 FLATTEN 함수를 입력해 합친 전체 범위를 펼칩니다. 이때 중간에 #N/A 오류가 반환되는데, 중첩 배열이 펼쳐지면서 왼쪽의 사번부터 직급까지는 값이 한 줄밖에 없기 때문에 발생하는 오류입니다.

   =FLATTEN(합친전체범위)

   

6. 이 #N/A 오류 칸을 채우기 위해, 보충 자료에 남겨드린 FILLNA 함수를 복사한 후 수식 탭 -> 이름 관리자로 이동해 새로 만들기를 클릭합니다.

   =LAMBDA(범위,열개수,DROP(REDUCE("",SEQUENCE(COLUMNS(범위)),LAMBDA(누적,번호,LET(열,CHOOSECOLS(범위,번호),HSTACK(누적,IF(번호<=열개수,SCAN("",열,LAMBDA(이전,값,IFNA(값,이전))),열))))),,1))

   

7. 복사한 함수를 참조 대상에 붙여 넣고 이름을 [FILLNA]로 지정한 후, 확인 버튼을 클릭해 함수를 등록합니다.
   
   
   오빠두Tip : 설명에는 함수의 동작을 적어 둘 수 있습니다. 강의에서는 '범위의 왼쪽부터 지정한 열 개수만큼 NA 오류를 위에 있는 값으로 채웁니다'라고 입력했습니다.
8. 이제 앞에서 작성한 FLATTEN 함수를 FILLNA 함수로 묶고, 오류를 채울 열 개수를 입력합니다. 이번 예제에서는 사번부터 직급까지 네 개 열이라 4를 입력했습니다. 함수를 실행하면 직원별로 이수한 교육과정이 정규화된 데이터로 깔끔하게 정리됩니다.

   =FILLNA(FLATTEN(합친전체범위),4)

   

9. 이렇게 작성한 함수는 왼쪽 데이터를 실시간으로 불러오기 때문에, 원본에 교육 이수 내역을 추가하면 정리된 결과도 실시간으로 업데이트됩니다.