엑셀 데이터 관리 핵심, UNPIVOT 함수 완벽 가이드 | M365 필수 기능

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

UNPIVOT 함수 마스터 공식 (M365)

완성된 마스터 공식을 이름관리자에 등록한 후, 실무에 바로 적용해보세요!😎 (LAMBDA 함수는 M365 버전에서만 제공됩니다.)

UNPIVOT 함수 동작 원리 이해하기

실무에서 다양한 데이터를 엑셀로 분석하다 보면, 잘못된 데이터 구조 때문에 분석과 관리에 어려움을 겪는 경우가 많습니다. 특히 '피벗테이블' 구조로 작성된 데이터를 올바른 데이터 구조로 변환하고 정리하는 작업은 많은 직장인이 마주하는 큰 과제 중 하나입니다.

엑셀 2016 이후 버전에서 제공되는 파워쿼리를 활용하면, 복잡한 구조의 데이터를 올바른 구조로 편리하게 가공하고 작업 과정까지 자동화할 수 있습니다. 다만 M365 버전 사용자라면 엑셀의 기본 함수만으로도 피벗 해제 기능을 손쉽게 구현할 수 있습니다.

이번 강의에서는 M365 버전에 새롭게 추가된 기본 함수만으로 UNPIVOT 함수를 직접 제작하고 활용하는 방법을 단계별로 알아보겠습니다.

1. UNPIVOT 함수는 보고서 형식으로 '피벗된' 데이터를 다시 '피벗 해제'할 때 사용합니다. 엑셀을 효율적으로 다루려면 데이터를 올바른 구조로 관리하는 것이 무엇보다 중요합니다. 실무에서 자주 발생하는 잘못된 데이터 구조와 이를 올바른 형식으로 정리해야 하는 이유는 이전 강의에서 자세히 정리해두었으니, 실습 전에 꼭 한 번 참고해보시길 권장드립니다.
   
2. 먼저 피벗테이블 형식의 보고서를 피벗 해제하려면, 피벗된 보고서를 다음과 같이 3가지 영역(행, 열, 값)으로 분류해야 합니다.

   

3. 예제파일의 [기초] 시트로 이동한 후, J3셀에 다음과 같이 TOCOL 함수를 사용한 수식을 입력하면 값 영역의 데이터가 1열의 세로 배열로 출력됩니다.
   =TOCOL(C4:F9)

   

4. EXPAND 함수를 활용하면 머리글 영역의 데이터를 피벗 해제할 수 있습니다. I3셀에 아래 수식을 입력하면 피벗 해제된 머리글 영역이 출력됩니다.
   =TOCOL(IFERROR(EXPAND(C3:F3,ROWS(C4:F9)),C3:F3))

   

   오빠두Tip : 위 공식의 단계별 동작 원리는 영상 강의에서 알기 쉽게 정리했습니다. 자세한 설명은 영상 강의를 참고해주세요!😊
5. 이어서 아래 수식을 H3셀에 입력해 피벗 해제된 레이블 영역을 출력합니다.
   =CHOOSEROWS(B4:B9,QUOTIENT(SEQUENCE(ROWS(J3#),,0,1),4)+1)

   

6. H3, I3, J3셀에 수식을 모두 입력하면, 아래 그림과 같이 피벗 해제 데이터가 완성됩니다.

   

7. 마지막으로 HSTACK 함수를 사용해 레이블, 머리글, 값 영역의 데이터를 하나의 배열로 병합합니다. L3셀에 아래 수식을 입력하면 피벗 해제된 각 범위가 하나로 병합되어 출력됩니다.
   =HSTACK(H3#,I3#,J3#)

   

   오빠두Tip : 엑셀 2021 이후 버전에서는 배열을 반환하는 수식의 시작셀 뒤에 # 기호를 추가해, 배열을 동적으로 선택할 수 있습니다. 분산 범위에 대한 자세한 설명은 아래 기초 입문 강의를 참고해보세요!👇
   

LET, LAMBDA 함수로 UNPIVOT 함수 만들기

LET 함수와 LAMBDA 함수를 활용하면 복잡한 공식의 처리 속도를 개선할 뿐만 아니라, 수식을 간결하게 정리하고 나만의 사용자 함수로 등록해 편리하게 재사용할 수 있습니다.

1. LET 함수 작성하기 : LET 함수를 사용하면 복잡한 공식의 처리 속도를 개선하고, 수식을 간결하게 정리할 수 있습니다.
   = LET ( 이름1, 값1, [이름2], [값2], ... , 수식 )
   · 수식에 이름을 할당해 수식의 성능과 가독성을 함께 개선합니다.
2. 예제파일 [기초] 시트에서 L3셀에 작성된 수식을 지운 후, 다음과 같이 LET 함수를 작성합니다.
   =LET(값,C4:F9,레이블,B4:B9,머리글,C3:F3,TOCOL(값))
   // 값, 레이블, 머리글 이름으로 범위를 할당한 후, 피벗해제 된 값 범위(=TOCOL(값))을 출력합니다.

   

3. 이전 단계에서 H3, I3, J3셀에 작성했던 각 공식을 참고해 LET 함수를 완성합니다.
   =LET(값,C4:F10,
   레이블,B4:B10,
   머리글,C3:F3,
   값필드,TOCOL(값),
   머리글필드,TOCOL(IFNA(EXPAND(머리글,ROWS(값)),머리글)),
   레이블필드,CHOOSEROWS(레이블,QUOTIENT(SEQUENCE(ROWS(값필드),,0),COLUMNS(머리글))+1),
   HSTACK(레이블필드,머리글필드,값필드))

   

4. LAMBDA 함수 작성하기 : LAMBDA 함수는 특정 공식을 편리하게 재사용할 수 있도록 사용자 함수를 생성하는 함수입니다.
   =LAMBDA ( 인수1, [인수2], ..., 수식 )
   · 엑셀의 기본 함수를 조합해 통합문서에서 재사용 가능한 사용자 정의 함수를 생성합니다.
5. 이제 기존의 LET 함수를 다음과 같이 LAMBDA 함수로 묶어 UNPIVOT 공식을 완성합니다.
   =LAMBDA(값범위,레이블범위,머리글범위,LET(값,값범위,
   레이블,레이블범위,
   머리글,머리글범위,
   값필드,TOCOL(값),
   머리글필드,TOCOL(IFNA(EXPAND(머리글,ROWS(값)),머리글)),
   레이블필드,CHOOSEROWS(레이블,QUOTIENT(SEQUENCE(ROWS(값필드),,0),COLUMNS(머리글))+1),
   HSTACK(레이블필드,머리글필드,값필드)))

   

6. LET 함수 동작 테스트 : 함수를 입력하면 아래 그림과 같이 #CALC! 오류가 출력됩니다. LAMBDA 함수는 엑셀의 일반 함수와 달리, 함수 뒤로 괄호 안에 인수를 추가로 작성해 동작을 직접 테스트할 수 있습니다.

   

7. 다음과 같이 LAMBDA 함수 뒤에 인수를 추가해, 함수의 결과 값을 직접 확인할 수 있습니다.
   =LAMBDA(값범위,레이블범위,머리글범위,LET(값,값범위,
   레이블,레이블범위,
   머리글,머리글범위,
   값필드,TOCOL(값),
   머리글필드,TOCOL(IFNA(EXPAND(머리글,ROWS(값)),머리글)),
   레이블필드,CHOOSEROWS(레이블,QUOTIENT(SEQUENCE(ROWS(값필드),,0),COLUMNS(머리글))+1),
   HSTACK(레이블필드,머리글필드,값필드)))(C4:F9,B4:B9,C3:F3)

   

8. UNPIVOT 함수 등록하기 : LAMBDA 함수가 정상적으로 동작하는 것을 확인했다면, 이름 관리자에 UNPIVOT 함수를 등록합니다. [수식] - [이름 관리자]를 클릭해 이름관리자를 실행한 후, [새로 만들기] 버튼을 클릭합니다.

   

9. 다음과 같이 이름, 설명, 참조 대상을 입력한 후, [확인] 버튼을 클릭해 UNPIVOT 함수를 등록합니다.
   · 이름 : UNPIVOT
   · 설명 : 피벗 보고서로 작성된 값, 레이블, 머리글 범위의 데이터를 피벗 해제 합니다.
   · 참조 대상 : =LAMBDA(값범위,레이블범위,머리글범위,LET(값,값범위, 레이블,레이블범위, 머리글,머리글범위, 값필드,TOCOL(값), 머리글필드,TOCOL(IFNA(EXPAND(머리글,ROWS(값)),머리글)), 레이블필드,CHOOSEROWS(레이블,QUOTIENT(SEQUENCE(ROWS(값필드),,0),COLUMNS(머리글))+1), HSTACK(레이블필드,머리글필드,값필드)))

   

10. 이제 통합문서에서 빈 셀을 선택한 후, 다음과 같이 UNPIVOT 함수로 피벗 보고서 형식의 데이터를 손쉽게 피벗 해제할 수 있습니다.
    =UNPIVOT(C4:F9,B4:B9,C3:F3)