엑셀 데이터 관리 핵심, UNPIVOT 함수 완벽 가이드

엑셀 UNPIVOT 함수 목차 바로가기

예제파일 다운로드
UNPIVOT 함수 마스터 공식
UNPIVOT 함수 동작 원리 이해하기
LET, LAMBDA 함수로 UNPIVOT 함수 만들기

영상 강의

동영상 재생이 안 되시나요? - 유튜브에서 시청 하기

- UNPIVOT 함수 동작 원리 -
UNPIVOT의 의미 : 피벗을 푼다!
00:58
1단계 : 값 영역 만들기
02:27
2단계 : 머리글 영역 만들기
03:04
3단계 : 레이블 영역 만들기
05:55
1씩 증가하는 순번 만들기
07:31
주기마다 1씩 증가하는 순번 만들기
08:37
4단계 : 레이블 영역 피벗해제 완성
09:47
- LET & LAMBDA 함수 활용하기 -
수식에 사용된 인수 구조화하기
10:27
HSTACK 함수로 범위 합치기
12:31
LET 함수 : 값 영역 출력하기
12:54
LET 함수 : 피벗해제 공식 완성
15:07
피벗 해제 LAMBDA 함수 만들기
18:36
- UNPIVOT 함수 등록 및 사용예제 -
UNPIVOT 함수 등록하고 사용하기
20:32
표로 실시간 데이터 가공하기
21:36

큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

[엑셀 고급] 엑셀 데이터 관리 핵심 기능! UNPIVOT 함수 완벽 정리 - 예제파일
예제파일
다운로드
[엑셀 고급] 엑셀 데이터 관리 핵심 기능! UNPIVOT 함수 완벽 정리 - 완성파일
완성파일
로그인하기

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

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

UNPIVOT 함수 마스터 공식 (M365)

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

= LAMBDA( 범위_, [레이블개수_], [머리글개수_], [그룹개수_], LET( 범위, 범위_, 레이블개수,IF(레이블개수_=0,1,레이블개수_), 머리글높이,IF(머리글개수_=0,1,머리글개수_), 그룹개수,IF(그룹개수_=0,1,그룹개수_), 레이블,CHOOSECOLS(DROP(범위,머리글높이),SEQUENCE(레이블개수)), 머리글,DROP(TAKE(범위,머리글높이),0,레이블개수), 값,DROP(범위,머리글높이,레이블개수), 값필드,WRAPROWS(TOCOL(값),그룹개수), 레이블필드,CHOOSEROWS(레이블,QUOTIENT(SEQUENCE(ROWS(값필드),,0),COLUMNS(머리글)/그룹개수)+1), 열필드,CHOOSEROWS(UNIQUE(TRANSPOSE(DROP(SCAN(0,머리글,LAMBDA(a,v,IF(IFERROR(v,"")="",a,v))),IF(머리글높이 > 1,-1,0),0))),MOD(SEQUENCE(ROWS(값필드),,0),COLUMNS(값)/그룹개수)+1), HSTACK(레이블필드,열필드,값필드)))
// 위 공식을 이름관리자에 등록한 후, 바로 사용할 수 있습니다.
// 공식 사용법은 UNPIVOT 함수 만들기 라이브 전체 영상을 참고해주세요!

UNPIVOT 함수 동작 원리 이해하기

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

피벗-형태의-잘못된-데이터 — 엑셀을 효율적으로 활용하려면, 올바른 데이터 구조를 꼭 이해해야 합니다.

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

오늘은 M365 버전에 새롭게 추가된 기본 함수만으로 UNPIVOT 함수를 직접 제작하고 활용하는 방법을 알아보겠습니다.

오빠두Tip : 실무자를 위한 파워쿼리 핵심 기능을 3시간 안에 살펴보는 '파워쿼리 원데이 무료 챌린지'를 완주하고 파워쿼리의 강력한 기능을 경험해보세요!

UNPIVOT 함수는 보고서 형식의 '피벗된' 데이터를 다시 '피벗 해제'할 때 사용할 수 있습니다. 엑셀을 효율적으로 사용하려면 데이터를 올바른 구조로 관리하는 것이 중요합니다. 실무에서 자주 발생하는 잘못된 데이터 구조와 이를 올바른 형식으로 관리해야 하는 이유에 대해서는 이전 강의에서 자세히 정리해두었으니, 꼭! 미리 참고하시고 실습해보시길 추천드립니다.
먼저 피벗테이블 형식의 보고서를 피벗 해제하려면, 피벗된 보고서를 다음과 같이 3가지 영역(행, 열, 값)으로 분류해야 합니다.
피벗 테이블 구조의 보고설르 3개 영역으로 구분합니다.
예제파일의 '기초' 시트로 이동한 후, J3셀에 다음과 같이 TOCOL 함수로 수식을 작성한 후 입력하면 값 영역의 데이터가 1열의 세로 배열로 출력됩니다.
=TOCOL(C4:F9)

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

EXPAND 함수를 활용해 머리글 영역 데이터를 피벗해제 합니다.

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

순번을 만드는 SEQUENCE 함수를 활용해 레이블 영역 데이터를 피벗해제 합니다.
H3, I3, J3셀에 수식을 모두 입력하면, 아래 그림과 같이 피벗 해제 데이터가 완성됩니다.
레이블, 머리글, 값 영역의 피벗해제 보고서가 완성되었습니다.
마지막으로 HSTACK 함수로 레이블, 머리글, 값 영역의 데이터를 하나의 배열로 병합합니다. L3셀에 아래 수식을 입력하면, 피벗해제 된 각 범위가 하나로 병합되어 출력됩니다.
=HSTACK(H3#,I3#,J3#)

레이블, 머리글, 값 영역의 배열을 HSTACK 함수를 사용해 하나로 병합합니다.

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

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

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

오빠두Tip : LET 함수와 LAMBDA 함수의 기초 예제와 사용법은 아래 영상 강의에서 알기 쉽게 정리했습니다. 함수를 처음 사용하시는 분은 미리 예습 후 실습해 보시길 추천드립니다!👇

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

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

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

LAMBDA 함수 공식을 작성합니다.
LET 함수 동작 테스트 : 함수를 입력하면 아래 그림과 같이 #CALC! 오류가 출력됩니다. LAMBDA 함수는 엑셀의 일반 함수와 다르게, 함수 뒤로 괄호 안의 인수를 추가로 작성하여 동작을 테스트할 수 있습니다.
LAMBDA 함수를 그냥 입력하면, #CALC! 오류가 출력됩니다.
다음과 같이 LAMBDA 함수의 뒤로 인수를 추가해서, 함수의 결과 값을 확인할 수 있습니다.
=LAMBDA(값범위,레이블범위,머리글범위,LET(값,값범위,
레이블,레이블범위,
머리글,머리글범위,
값필드,TOCOL(값),
머리글필드,TOCOL(IFNA(EXPAND(머리글,ROWS(값)),머리글)),
레이블필드,CHOOSEROWS(레이블,QUOTIENT(SEQUENCE(ROWS(값필드),,0),COLUMNS(머리글))+1),
HSTACK(레이블필드,머리글필드,값필드)))(C4:F9,B4:B9,C3:F3)

LAMBDA 함수 뒤로, 괄호 안에 인수를 추가하여 동작을 테스트합니다.
UNPIVOT 함수 등록하기 : LAMBDA 함수가 잘 동작하는 것을 확인하였으면, 이름 관리자에 UNPIVOT 함수를 등록합니다. [수식] - [이름 관리자]를 클릭해서 이름관리자를 실행한 후, [새로만들기] 버튼을 클릭합니다.
수식 - 이름관리자 - 새로 만들기를 클릭합니다.
다음과 같이 이름, 설명, 참조대상을 입력한 후, [확인] 버튼을 클릭해 UNPIVOT 함수를 등록합니다.
· 이름 : UNPIVOT
· 설명 : 피벗 보고서로 작성된 값, 레이블, 머리글 범위의 데이터를 피벗 해제 합니다.
· 참조 대상 : =LAMBDA(값범위,레이블범위,머리글범위,LET(값,값범위, 레이블,레이블범위, 머리글,머리글범위, 값필드,TOCOL(값), 머리글필드,TOCOL(IFNA(EXPAND(머리글,ROWS(값)),머리글)), 레이블필드,CHOOSEROWS(레이블,QUOTIENT(SEQUENCE(ROWS(값필드),,0),COLUMNS(머리글))+1), HSTACK(레이블필드,머리글필드,값필드)))

완성된 LAMBDA 함수를 참조 대상에 붙여넣은 후, UNPIVOT 함수를 등록합니다.
이제 통합문서에서 비어있는 셀을 선택한 후, 다음과 같이 UNPIVOT 함수를 사용해 피벗 보고서 형식의 데이터를 편리하게 피벗해제 할 수 있습니다.
=UNPIVOT(C4:F9,B4:B9,C3:F3)

이제 통합문서에서 UNPIVOT 함수를 사용할 수 있습니다.