엑셀 데이터 관리 - 데이터 구조, 로우 데이터, 파워쿼리

솔직히 말하면, 저는 꽤 오랫동안 엑셀 함수를 많이 아는 것이 곧 엑셀 실력이라고 믿었습니다. VLOOKUP, SUMIFS, INDEX-MATCH까지 익히고 나서야 뭔가 된 것 같다는 느낌이 들었으니까요. 그런데 실무에서 데이터를 직접 다루면서 그 믿음이 조금씩 흔들리기 시작했습니다. 함수를 아무리 잘 써도 데이터 구조 자체가 잘못되어 있으면 결국 아무 의미가 없다는 걸, 실수를 겪고 나서야 제대로 깨달았습니다.

데이터 구조가 잘못되면 함수가 소용없다는 걸 알았습니다

제가 처음 실무에서 입고 현황표를 받았을 때, 날짜가 가로 방향으로 쭉 늘어서 있는 형태였습니다. 처음에는 보기 편하다고 생각했어요. 월요일부터 금요일까지 한눈에 들어오니까요. 그런데 피벗 테이블을 만들려는 순간 문제가 생겼습니다.

피벗 테이블(Pivot Table)이란 대량의 데이터를 원하는 기준으로 빠르게 집계하고 요약해 주는 엑셀의 핵심 분석 도구입니다. 그런데 날짜가 가로로 늘어서 있으니, 피벗 테이블 필드 목록에 날짜 하나하나가 각각 별도의 항목으로 잡혔습니다. 1월 1일, 1월 2일, 1월 3일이 모두 따로따로 필드가 되어버린 거죠. 특정 요일 데이터를 필터링하려면 날짜를 하나씩 찾아서 체크해야 했고, 새로운 날짜가 추가될 때마다 표는 가로로만 계속 넓어졌습니다.

이것이 바로 데이터 정규화(Data Normalization)가 지켜지지 않아서 생기는 문제입니다. 데이터 정규화란 동일한 속성을 가진 값들이 하나의 열(column)에 세로 방향으로 쌓이도록 구조를 맞추는 원칙을 말합니다. 날짜는 결국 '날짜'라는 하나의 속성입니다. 그러니 가로로 펼쳐질 것이 아니라 세로로 쌓여야 했던 거죠.

제가 직접 겪어보니, 이 구조 문제를 모르는 상태에서 함수만 열심히 입력하는 건 기울어진 책상 위에 정밀한 계산기를 올려놓는 것과 다르지 않았습니다. 

• 날짜가 가로로 늘어선 표는 피벗 테이블 분석에 적합하지 않습니다
• 동일한 속성(날짜, 제품명 등)은 반드시 하나의 열에 세로로 쌓여야 합니다
• 데이터가 가로로 누적될수록 관리 난이도는 지수적으로 올라갑니다
• 잘못된 구조는 함수나 기능으로 보완하는 데 한계가 있습니다

요약: 날짜처럼 동일한 속성을 가진 값은 가로가 아닌 세로로 쌓아야 피벗 테이블 분석이 제대로 작동합니다.

 

로우 데이터가 뭔지 제대로 이해한 건 훨씬 나중이었습니다

처음 실무에서 "로우 데이터(Raw Data) 뽑아오세요"라는 말을 들었을 때, 저는 그냥 정리 안 된 원본 파일을 뜻하는 줄 알았습니다. 로우 데이터란 집계나 가공이 이루어지기 전의 가장 세밀한 단위의 원본 기록을 의미합니다. 쉽게 말해 개별 거래 한 건, 클릭 한 번, 입고 한 건처럼 더 이상 쪼갤 수 없는 단위의 데이터입니다.

 

예를 들어 주간 매출 현황표가 있다고 할 때, 이것이 로우 데이터인지 물어보면 대답하기 애매합니다. 일자별로 보고 싶다면 그 표는 이미 집계된 데이터이고, 한 단계 아래인 일자별 데이터가 필요합니다. 그런데 또 "그날 어떤 고객이 어떤 제품을 샀는지"를 보려면 일자별 데이터조차 부족하고, 개별 거래 단위까지 내려가야 합니다.

제 경험상 이걸 모르면 IT팀이나 담당 부서에 엉뚱한 요청을 하게 됩니다. "매출 데이터 좀 주세요"라고 했다가 월별 합계표를 받아 들고 당황했던 적이 실제로 있습니다. 반면 "특정 기간의 고객별·제품별 개별 거래 단위 데이터가 필요합니다"라고 요청했을 때는 원하는 데이터를 정확하게 받을 수 있었습니다. 데이터 구조를 이해하는 것이 곧 커뮤니케이션 능력이 되는 셈이죠.

통계청 자료에 따르면 기업 내 데이터 활용 실패의 주요 원인 중 하나가 데이터 품질 및 구조에 대한 이해 부족이라고 합니다. 화려한 분석 도구보다 먼저 내가 다루는 데이터의 수준을 파악하는 것이 선행되어야 한다는 뜻입니다.

 

요약: 로우 데이터는 분석 목적에 따라 달라지며, 내가 필요한 데이터 단위를 정확히 알아야 올바른 요청과 분석이 가능합니다.

 

파워쿼리로 가로 데이터를 세로로 바꾸는 건 생각보다 간단했습니다

데이터 구조 문제를 이해하고 나서 가장 먼저 든 생각은 "그럼 이미 잘못 만들어진 데이터는 어떻게 하지?"였습니다. 수백 행이 넘는 표를 손으로 옮기는 건 사실상 불가능하니까요. 이때 써본 것이 파워쿼리(Power Query)입니다.

파워쿼리란 엑셀에 내장된 데이터 변환 도구로, 복잡한 수식 없이 클릭 몇 번만으로 데이터 구조를 바꾸고 정제할 수 있는 기능입니다. 특히 가로로 늘어선 열들을 세로 방향으로 전환하는 '열 피벗 해제(Unpivot Columns)' 기능이 핵심이었습니다.

실제로 써봤더니 과정은 생각보다 간단했습니다. 우선 원본 표를 엑셀의 표(Table) 형식으로 변환한 뒤 데이터 탭에서 파워쿼리를 실행합니다. 그다음 기준이 되는 열(예: 제품명)을 선택하고, 변환 탭에서 '다른 열 피벗 해제'를 클릭하면 가로로 늘어서 있던 날짜 열들이 한 번에 세로 방향으로 재구성됩니다. 이때 자동으로 생성된 '특성'과 '값' 열의 이름을 각각 '날짜'와 '수량'으로 바꾸면 끝입니다.

수작업으로 지루하게 몇시간을 해야할 것 같았던 작업이 5분도 안 걸립니다. 변환이 완료된 데이터를 시트로 내보낸 뒤 피벗 테이블을 다시 만들었더니, 날짜를 슬라이서(Slicer)로 필터링하면서 제품별 수량을 실시간으로 확인하는 분석 보고서가 금방 완성되었습니다. 슬라이서란 피벗 테이블이나 차트에 연결된 시각적 필터 버튼으로, 클릭 한 번으로 데이터를 즉시 필터링할 수 있는 기능입니다.

 

요약: 파워쿼리의 '열 피벗 해제' 기능을 쓰면 가로 구조 데이터를 세로 방향으로 빠르게 변환할 수 있고, 이후 피벗 테이블 분석도 훨씬 수월해집니다.

 

자주 묻는 질문

Q. 엑셀 데이터를 가로로 입력하면 안 되나요?

A. 사람 눈으로 읽기에는 편할 수 있지만, 피벗 테이블이나 함수를 이용한 분석에는 적합하지 않습니다. 날짜나 항목처럼 동일한 속성을 가진 값들은 세로 방향으로 쌓아야 나중에 필터링, 집계, 분석이 훨씬 수월해집니다. 데이터 정규화 원칙을 따르는 게 결국 시간을 절약하는 방법입니다.

 

Q. 파워쿼리는 엑셀 어떤 버전부터 쓸 수 있나요?

A. 파워쿼리는 엑셀 2016 이상에서는 기본 내장되어 있으며, 엑셀 2010·2013에서는 별도 추가 기능으로 설치해서 사용할 수 있습니다. Microsoft 365 구독 버전에서는 최신 기능을 포함한 파워쿼리가 기본 제공됩니다. 데이터 탭에서 '데이터 가져오기 및 변환' 항목이 보이면 사용 가능한 버전입니다.

 

Q. 로우 데이터랑 집계 데이터는 어떻게 구분하나요?

A. 로우 데이터는 개별 거래나 이벤트 하나하나가 한 행으로 기록된 데이터입니다. 집계 데이터는 그 로우 데이터를 특정 기준(날짜별, 제품별 등)으로 합산하거나 요약한 결과물입니다. 구분하는 가장 빠른 방법은 "이 데이터에서 더 세밀한 정보를 볼 수 있는가?"를 스스로 물어보는 것입니다. 더 내려갈 단계가 있다면 현재 데이터는 집계된 상태입니다.

 

Q. 열 피벗 해제와 피벗 테이블은 어떻게 다른가요?

A. 피벗 테이블은 세로로 쌓인 데이터를 원하는 기준으로 집계·요약하는 분석 도구입니다. 반면 열 피벗 해제는 가로로 넓게 퍼진 데이터를 분석에 적합한 세로 구조로 바꿔주는 전처리 작업입니다. 순서로 보면 열 피벗 해제로 데이터 구조를 먼저 바로잡고, 그다음에 피벗 테이블로 분석하는 흐름이 올바른 순서입니다.

 

결론

제가 실무를 거치면서 얻은 가장 큰 깨달음은 "어떤 함수를 쓸까?"보다 "이 데이터는 지금 분석에 적합한 구조인가?"를 먼저 물어야 한다는 것입니다. 데이터 구조가 잘못되어 있으면 파워쿼리로 바로잡을 수 있고, 내가 가진 데이터가 충분한지 판단할 수 있다면 올바른 요청도 할 수 있습니다. 도구를 먼저 배우기 전에 데이터를 읽는 눈을 먼저 키우는 것, 그게 엑셀 실력의 진짜 출발점이라고 생각합니다.

 

지금 엑셀 파일 하나가 눈앞에 있다면, 함수를 입력하기 전에 잠깐 멈춰보시길 권합니다. 머리글이 한 줄인지, 날짜가 가로로 늘어서 있지는 않은지, 중간에 합계 행이 끼어 있지는 않은지를 먼저 확인해 보는 것만으로도 작업 방향이 훨씬 명확해질 것입니다.