들어가기 전에
Excel에서 데이터를 수동으로 재구성하는 데는 시간이 오래 걸리고 오류가 발생할 가능성이 높습니다. 다행스럽게 Excel에는 몇 초 만에 데이터를 변환할 수 있는 다양한 도구와 기능이 있으며, 꼭 알아야 할 7가지 데이터 변형 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 7 Ways to Reshape Data in Microsoft Excel
- URL: https://www.howtogeek.com/microsoft-excel-ways-to-reshape-data/
1. 파워 쿼리 사용
Excel의 파워 쿼리 편집기는 데이터 구조를 재구성할 때 편리한 도구입니다. 셀 병합 또는 분할, 열 추가 또는 제거, 워크시트 결합 등 어떤 작업을 하든 효율적인 방법입니다. 하지만 파워 쿼리의 가장 큰 장점 중 하나는 모든 비교 값이 단일 열에 포함되도록 데이터를 변환하는 기능입니다. 이를 데이터 평면화 라고도 합니다.
아래 스크린샷에서는 여러 열에 회계 데이터가 포함되어 있습니다. 모든 재무 수치가 한 열에 있는 것이 좋습니다. 그래야 필터링하고 비교하기가 더 쉽습니다. 즉, 각 행에는 회사, 연도, 그리고 값이 포함되어야 합니다. 즉, 이 세 가지 변수를 기준으로 데이터를 쉽게 정렬하고 필터링할 수 있습니다. 파워 쿼리에서 이러한 구조 조정 단계를 "데이터 피벗 해제"라고 합니다.
(1) 데이터의 아무 셀이나 선택하고 [데이터] 탭 - [테이블/범위에서]를 클릭합니다.
(2) Power Query 편집기에서 피벗 해제할 모든 열의 머리글을 선택합니다. 이 예에서는 "2020" 머리글을 선택하고 Shift 키를 누른 상태에서 "2024" 머리글을 선택합니다.
(3) [변형] 탭에서 "열 피벗 해제" 드롭다운 메뉴를 확장하고 "선택한 열만 피벗 해제"를 클릭합니다.
(4) 각 행에는 회사, 연도, 값이 포함되고, 각 열에는 일관된 유형의 데이터가 포함됩니다.
(5) 마지막으로, 열 머리글을 더블 클릭하여 열 이름을 바꾸고 숫자 형식 아이콘을 클릭하여 각 열에 올바른 데이터 유형이 포함되어 있는지 확인하여 데이터를 정리합니다.
참고 영상:
2. 피벗 테이블 사용
피벗 테이블은 대규모 데이터 세트를 분석, 비교, 시각화를 훨씬 쉽게 만드는 구조로 재구성하는 데 이상적인 Excel 도구입니다.
각 행에 판매된 제품에 대한 다양한 세부 정보가 포함된 다음 Excel 표를 예로 들어 보겠습니다. 구체적으로, 국가별 각 부서의 수익을 비교한다고 가정해 보겠습니다.
부서와 국가 열을 필터링하여 수익을 추출하는 작업을 수동으로 수행하려면 시간이 너무 많이 걸립니다. 대신, 데이터를 피벗 테이블로 변환하세요.
(1) 표에서 아무 셀이나 선택하고 리본의 삽입 탭에서 분할된 "피벗 테이블" 단추의 위쪽 절반을 클릭합니다.
(2) 피벗 테이블에서 테이블 또는 범위 대화 상자에서 올바른 범위가 선택되었는지 확인하고 피벗 테이블을 활성 워크시트에 표시할지 또는 새 워크시트에 표시할지 결정한 후 "확인"을 클릭합니다.
(3) 피벗 테이블을 구성할 수 있는 영역이 지정한 위치에 나타납니다. 피벗 테이블 필드 창에서 표시할 필드를 클릭하여 해당 영역으로 끌어다 놓습니다. 국가별 각 부서의 수익을 비교하는 것이 목표이므로 부서는 개별 행으로, 국가는 열로, 수익은 값으로 표시해야 합니다.
(4) 피벗 테이블 필드 창의 다른 영역으로 필드를 이동하면 피벗 테이블이 형성되는 것을 볼 수 있습니다. 이 간단한 프로세스의 결과로, 이제 국가 및 부서별로 수익을 빠르게 비교할 수 있는 방식으로 데이터가 재구성되었으며, 원래 데이터 세트는 변경되지 않은 상태로 유지됩니다.
피벗 테이블의 장점은 피벗 테이블 필드 창에서 다른 필드나 추가 필드를 쉽게 선택할 수 있다는 것입니다. 예를 들어, 여기에서는 더 자세한 분석을 위해 행 영역에 제품 필드를 추가했습니다.
참고 영상:
3. 데이터 방향 변환
데이터 세트에서 행과 열을 전환하려는 데에는 여러 가지 이유가 있습니다. 각 열이 필드이고 각 행이 레코드인지 확인하거나, XLOOKUP 또는 SUMIFS를 사용하여 추가 분석을 위해 데이터를 준비하거나, Excel 차트 에 데이터가 표시되는 방식을 변경하는 것이 그 예입니다.
예를 들어, 이 배열을 뒤집어서 맨 위 행에 국가를, 첫 번째 열에 자동차 브랜드를 포함시키려고 한다고 가정해 보겠습니다.
데이터를 수동으로 전치하는 데 걸리는 시간을 감안할 때, Excel에서는 몇 초 안에 해당 작업을 수행할 수 있는 세 가지 방법을 제공합니다.
방법 1: TRANSPOSE 함수 사용
TRANSPOSE 함수는 Excel 2016 이상, Microsoft 365용 Excel, 웹용 Excel 또는 Excel 모바일 앱을 사용하는 경우에만 사용할 수 있습니다.
TRANSPOSE 함수를 사용하여 이 데이터를 뒤집으려면 전치된 결과의 왼쪽 위 셀을 넣을 셀을 선택하고 다음 수식을 입력합니다.
=TRANSPOSE(A1:H8)
TRANSPOSE 함수는 일반 범위와 마찬가지로 Excel 표에서도 동일하게 작동합니다. 유일한 차이점은 수식에 셀 직접 참조 대신 표 이름(구조적 참조)이 포함된다는 것입니다. 배열을 식별할 때는 열 머리글을 선택하는 것을 잊지 마세요.
수식을 사용했기 때문에 결과는 동적입니다. 즉, 원본 데이터세트의 모든 변경 사항이 전치된 배열에 반영됩니다. 결과를 정적으로 만들려면 모든 셀을 선택하고 Ctrl + C를 눌러 복사한 다음, Ctrl + Shift + V를 눌러 값으로 붙여넣으세요. 그런 다음 원하는 경우 원본 데이터세트를 삭제할 수 있습니다.
방법 2: 선택하여 붙여넣기 사용
Excel에서 데이터 세트를 전치하는 또 다른 방법은 붙여넣기 특수 기능을 사용하는 것입니다. 이 방법은 일반 범위에만 적용되며 Excel 표에는 적용되지 않습니다. Excel 표를 일반 범위로 변환하려면 표에서 아무 셀이나 선택하고 표 디자인 탭에서 "범위로 변환"을 클릭하세요.
(1) 열 머리글을 포함한 모든 데이터를 선택하고 Ctrl + C를 눌러 복사합니다. 또는 선택한 배열을 마우스 오른쪽 버튼으로 클릭하고 "복사"를 클릭해도 됩니다.
(2) 결과를 표시할 셀로 이동하여 Ctrl + Alt + V를 누릅니다. 리본 메뉴의 홈 탭에서 "붙여넣기" 드롭다운 화살표를 클릭하고 "선택하여 붙여넣기"를 선택합니다.
(3) 어떤 방법을 선택하든 '선택하여 붙여넣기' 대화 상자가 나타납니다. 거기서 [행/열 바꿈]을 체크하고 [확인]을 클릭합니다.
(4) 데이터가 독립적인 배열로 전치되므로 새 데이터 세트에 어떤 영향을 미칠지 걱정하지 않고 원본 데이터 세트를 삭제할 수 있습니다.
방법 3: Power Query 사용
Excel의 파워 쿼리 편집기에는 데이터 전치 전용 도구가 있지만, 이 데이터 변형 작업을 수행하는 더 나은 방법이 있습니다. 실제로 파워 쿼리의 전치 도구를 사용하면 기존 열 머리글이 제거되어 문제를 해결하기보다는 오히려 더 많은 문제를 야기합니다. 대신 피벗 및 피벗 해제 도구를 사용하세요.
(1) 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 - [테이블/범위에서]를 클릭하여 Power Query 편집기에서 데이터 세트를 로드합니다.
(2) 행으로 바꾸려는 모든 열의 머리글을 선택하고 변형 탭에서 "열 피벗 해제"를 클릭합니다.
(3) 헤더 행으로 전환하려는 열을 선택하고 "열 피벗"을 클릭합니다.
(4) 피벗 열 대화 상자가 나타나면 "값"을 선택하고 "확인"을 클릭합니다.
원본 데이터 세트가 성공적으로 전치되었으므로 [홈] 탭에서 [닫기 및 로드]를 클릭하여 새로 구성된 데이터 세트를 기존 통합 문서의 새 워크시트로 보낼 수 있습니다.
Power Query를 통해 생성된 테이블은 원본 데이터에 연결되므로 언제든지 쿼리 탭에서 "새로 고침"을 클릭하여 최신 정보가 표시되는지 확인할 수 있습니다.
4. PIVOTBY 함수 사용
Excel의 PIVOTBY 함수를 사용하면 원본 데이터 집합을 변경하지 않고도 수치를 그룹화할 수 있습니다. 피벗 테이블과 유사한 결과를 생성하지만, 주요 차이점은 단일 함수 내에서 모든 매개변수를 결정할 수 있다는 것입니다.
[참고] PIVOTBY 함수는 Excel 2021 이상, Microsoft 365용 Excel, 웹용 Excel 또는 Excel 모바일 앱을 사용하는 경우에만 사용할 수 있습니다.
PIVOTBY 함수에는 네 개의 필수 필드가 있습니다. 이 필드들은 Excel에서 행에 표시할 변수, 열에 표시할 변수, 값을 찾을 위치, 그리고 데이터 집계 방법을 지정하는 데 사용됩니다.
=PIVOTBY(a, b, c, d)
- a: 결과 왼쪽에 행 머리글로 표시하려는 범주를 포함하는 셀
- b: 결과 상단에 열 머리글로 표시하려는 범주를 포함하는 셀
- c: 인수 a 및 b에 대해 선택한 행과 열에 따라 결과 중앙에 나타날 값
- d: 데이터가 어떻게 집계될지 정의하는 Excel 함수(또는 LAMBDA를 통해 직접 만든 함수)
PIVOTBY 함수에는 머리글과 합계를 포함할지 여부, 데이터 정렬 여부, 추가 매개변수 지정 등을 지정할 수 있는 여러 가지 선택적 인수가 있습니다. 이 예제에서는 이해하기 쉽도록 필수 인수만 사용하겠습니다.
스포츠별, 연도별 시청자 수를 빠르게 분석할 수 있도록 다음 표(Sports_Viewers)를 재구성한다고 가정해 보겠습니다.
이렇게 하려면 F1 셀에 다음 수식을 입력합니다.
=PIVOTBY(Sports_Viewers[Sport], Sports_Viewers[Year], Sports_Viewers[Viewers], SUM)
- Sports_Viewers[Sport]: 6가지 스포츠를 포함하는 명명된 테이블의 열. 이 열은 결과 화면 왼쪽에 행 머리글로 표시
- Sports_Viewers[Year]: 표의 연도 열을 나타내며, 이는 결과 상단에 표시되는 열 머리글
- Sports_Viewers[Viewers]: 해당 연도에 해당 스포츠를 시청한 사람의 수를 나타내는 열이므로 이 데이터가 결과의 초점
- SUM: Excel에 합계 열의 수치를 합산하라고 지시
Enter 키를 누르면 각 연도의 각 스포츠에 대한 총합이 결과로 합산됩니다.
수식에서 필수 인수만 사용했기 때문에 기본적으로 Excel에서는 재구성된 데이터의 행과 열에 모두 합계를 자동으로 추가하고, 결과는 인수 a를 기준으로 알파벳순으로 정렬됩니다.
한 걸음 더 나아가, 각 스포츠를 네 개의 지역으로 구분하는 열을 포함할 수 있습니다. 이를 위해서는 인수 a에 스포츠와 지역을 모두 포함해야 합니다.
=PIVOTBY(Sports_Viewers[[Sport]:[Region]], Sports_Viewers[Year], Sports_Viewers[Viewers], SUM)
참고로, 인접하지 않은 열을 인수로 그룹화하려면 CHOOSECOLS 함수를 사용합니다.
참고 영상:
5. WRAPCOLS 및 WRAPROWS 함수 사용
Excel의 WRAPCOLS 및 WRAPROWS 함수는 단일 열이나 행의 데이터를 2차원 배열로 변환하는 데 매우 유용합니다.
[참고] WRAPCOLS 및 WRAPROWS 함수는 Excel for Microsoft 365, 웹용 Excel 또는 Excel 모바일 앱을 사용하는 사용자에게만 제공됩니다.
이 두 함수의 구문은 동일합니다.
=WRAPCOLS(a, b, c)
=WRAPROWS(a, b, c)
- a (필수): 래핑하려는 1차원 범위
- b (필수): WRAPCOLS의 경우 각 열의 최대 값 수(WRAPROWS의 경우 각 행의 최대 값 수)
- c (선택 사항): 줄 바꿈 후 남은 셀을 채울 값(생략하면 남은 셀에 #N/A 오류 발생)
이 이름 목록을 여러 팀으로 나누고, 각 팀에는 최대 7명의 선수만 있을 수 있다고 가정해 보겠습니다. 즉, 각 열에는 최대 7명의 이름이 포함될 수 있습니다.
빈 셀로 이동하여 다음 수식을 입력합니다.
=WRAPCOLS(A1:A48, 7, "")
결과의 순서가 열 기반이라는 점에 유의하세요. 즉, 처음 7개 이름은 결과의 첫 번째 열에, 그 다음 7개 이름은 두 번째 열에 위치하는 방식입니다.
그런 다음, 직접 입력하거나 수식을 사용하여 결과 데이터 위에 팀 이름을 추가할 수 있습니다. 다음 예에서는 SEQUENCE와 COUNTA 함수를 사용했습니다.
이제 팀당 최대 선수 수를 정의하는 대신 전체 팀 수를 제한한다고 가정해 보겠습니다. 구체적으로, 선수들을 9개 팀으로 나누어 각 행에 최대 9명의 이름을 입력할 수 있도록 하려고 합니다. 이렇게 하려면 다음과 같이 입력합니다.
=WRAPROWS(A1:A48, 9, "")
A1:A48은 범위입니다. 각 행의 최대 값 수는 9 이고, "" 는 Excel에서 남은 셀에 공백을 반환하도록 지시합니다.
이번에는 결과 순서가 행 기반입니다. 즉, 처음 9개 이름은 첫 번째 행에, 두 번째 9개 이름은 두 번째 행에 이어지는 식입니다.
WRAPCOLS 함수와 WRAPROWS 함수를 사용하면 결과는 동적 배열이 됩니다. 즉, 수식을 입력한 셀에서 배열이 펼쳐집니다. 동적 배열은 Excel 표와 호환되지 않으므로 서식이 지정되지 않은 일반 셀에 수식을 입력해야 합니다.
6. TOCOL 및 TOROW 함수 사용
TOCOL 및 TOROW 함수는 WRAPCOLS 및 WRAPROWS 함수와 정반대의 작업을 수행합니다. 즉, 2차원 배열을 1차원 열이나 행으로 변환합니다.
[참고] TOCOL 및 TOROW 함수는 Microsoft 365용 Excel, 웹용 Excel 또는 Excel 모바일 앱을 사용하는 경우에만 사용할 수 있습니다.
구문은 동일합니다.
=TOCOL(a, b, c)
=TOROW(a, b, c)
- a (필수): 변환할 배열
- b (선택 사항): Excel에서 모든 값을 유지할지(0—기본값), 공백을 무시할지(1), 오류를 무시할지(2), 공백과 오류를 무시할지(3)를 알려주는 숫자
- c (선택 사항): Excel에서 결과를 열별로 정렬하려는 경우 TRUE(그렇지 않으면 기본적으로 결과를 행별로 정렬)
각 팀의 선수가 나열된 다음과 같은 데이터가 있다고 가정합니다.
이 선수들을 한 열에 나열하려면 빈 셀에 다음 수식을 입력합니다.
=TOCOL(A2:I7)
위 수식에 인수 a만 포함되어 있는 것을 주목하세요. 빈 셀이 배열 끝에 있으므로 어떻게 처리될지 정의할 필요가 없고, 이 데이터 세트에서는 행 정렬(인수 c의 기본 옵션)이 잘 작동하기 때문입니다.
반면, 동일한 배열을 단일 행으로 재배열하려면 다음을 입력합니다.
=TOROW(A2:I7)
불필요하고 공식을 너무 복잡하게 만들 필요가 없기 때문에 인수 b 와 c를 생략했습니다.
배열을 반환하는 모든 함수와 마찬가지로 TOCOL 및 TOROW 수식은 Excel 표로 서식이 지정되지 않은 셀에 입력해야 합니다.
7. VSTACK 함수 사용
Excel의 VSTACK은 두 개 이상의 배열을 쌓아 올립니다. 특히 서로 관련성이 있지만 별개의 배열을 하나의 데이터 집합으로 결합하여 분석과 비교를 용이하게 하는 데 유용합니다.
[참고] VSTACK 함수는 Microsoft 365용 Excel, 웹용 Excel 또는 Excel 모바일 앱을 사용하는 사용자에게만 제공됩니다.
VSTACK 함수의 구문은 매우 간단합니다.
=VSTACK(a, b,...)
여기서 a, b 등은 서로 쌓을 개별 배열입니다. 배열을 선택하는 순서가 배열이 쌓이는 순서가 됩니다. 즉, 첫 번째 배열은 두 번째 배열 위에, 두 번째 배열은 세 번째 배열 위에 쌓이는 식으로 진행됩니다.
다음과 같은 데이터가 있습니다. 세 리그에 걸친 각 팀의 점수가 포함되어 있습니다. 이제 시즌이 종료되었으므로 VSTACK을 사용하여 모든 팀의 점수를 하나의 연속된 배열로 병합해 보겠습니다.
빈 셀에 다음 수식을 입력합니다.
=VSTACK(A2:C6, A9:C13, A16:C20)
VSTACK 함수는 다른 데이터 구성 함수와 잘 작동합니다. 예를 들어, 위의 VSTACK 수식 전체는 SORT 수식 의 첫 번째 인수를 구성할 수 있습니다.
=SORT(VSTACK(A2:C6, A9:C13, A16:C20), 3, -1)
여기서 3은 데이터가 세 번째 열(점수)을 기준으로 정렬됨을 나타내고, -1은 해당 열을 내림차순으로 정렬함을 나타냅니다.
HSTACK 함수에도 같은 원리를 적용할 수 있습니다. 이 함수는 배열을 서로 위에 쌓는 대신, 배열을 서로 옆에 쌓습니다.
마치며
Excel에서 수치를 재구성할 때는 데이터가 가능한 한 최적의 방식으로 구성되었는지 확인하는 데 시간을 투자해야 합니다 . 예를 들어, 각 열은 일관된 필드여야 하고, 각 행은 개별 레코드여야 하며, 모든 셀에는 데이터 요소가 하나만 있어야 하고, 배열에 빈 행과 열이 없도록 해야 합니다.
'Excel' 카테고리의 다른 글
| 새로운 콘텐츠 오픈 안내 (0) | 2026.02.08 |
|---|---|
| 정말 대단한 Excel MAP 함수 (0) | 2026.02.04 |
| 오늘(2/1 일) 오후에 새로운 콘텐츠가 오픈됩니다 (0) | 2026.02.01 |
| Excel의 LAMBDA 함수에 대한 이야기 (0) | 2026.01.28 |
| 휴대폰에서 Excel을 올바르게 사용하는 방법 (0) | 2026.01.23 |
