엑셀 표 머리글 합치기, 해결방법 3분 총정리
엑셀 머리글 합치기 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 표 머리글 합치기 - 파워쿼리 자동화예제파일
올바른 데이터 관리 규칙 - 3개만 기억하세요!
엑셀을 잘 사용하려면 함수나 피벗테이블과 같은 여러 기능을 숙지하는 것 보다, '올바른 데이터' 구조를 이해하는 것이 무엇보다 중요한데요. 올바른 구조로 데이터를 관리하면 그렇지 않은 데이터를 다룰 때보다 업무효율을 비약적으로 향상시킬 수 있습니다.
엑셀에서는 대부분의 경우, 아래 3가지 규칙을 잘 지키면 올바른 구조로 데이터를 관리할 수 있습니다.
- 머리글은 반드시 한 줄로 작성되어야 합니다.
- 병합된 셀이 없어야 합니다.
- 집계된 데이터가 없어야 합니다. (소계, 합계 등..)
올바른 구조로 데이터를 관리하는 방법과 잘못된 구조의 데이터를 올바른 구조로 손쉽게 정규화하는 방법은 아래 관련강의에서 자세히 설명해드렸으니 궁금하신 분은 한번 참고해보시길 바랍니다.
머리글이 여러 줄일 경우 발생하는 문제
엑셀 데이터분석에서 필수적으로 사용하는 '피벗테이블'을 사용하다보면, 원본데이터의 머리글이 여러 줄로 작성되어 데이터 분석이 올바르게 되지 않는 경우가 종종 발생하는데요. 머리글이 여러 줄로 작성되면, 크게 아래 2가지 문제가 발생합니다.
- 머리글에서 셀 병합된 부분이 해제되면서 서식이 틀어집니다.
표로 변환되면 병합된 셀이 해제되면서 표 구조가 틀어집니다. - 머리글 범위를 제대로 인식하지 못해서 피벗테이블 생성이 제한됩니다.
머리글이 여러줄일 경우 피벗테이블 생성이 제한됩니다.
따라서 원본데이터의 머리글이 여러줄로 작성된 경우, 아래 소개해드린 방법을 사용하면 파워쿼리로 머리글 합치기 작업을 손쉽게 자동화 할 수 있습니다.
- 범위를 표로 변환 : 예제파일을 실행한 후, 데이터가 입력된 B4:F12 범위를 선택합니다. 이후 [삽입] 탭 - [표] 버튼을 클릭하거나 단축키 Ctrl + T 를 누르면 '표 만들기' 대화상자가 실행됩니다. 현재 선택된 범위는 머리글이 두 줄로 잘못 입력되어 있으므로, '머리글 포함'을 체크해제 후 [확인] 버튼을 눌러 범위를 표로 변환합니다.
범위를 표로 변환합니다. (표만들기 대화상자에서 '머리글 포함'은 체크 해제) - 범위가 표로 변환되면서 "열1, 열2, 열3.."으로 불필요한 머리글이 추가되어 머리글을 숨겨주겠습니다. 표를 선택 후 [테이블 디자인] 탭 - [머리글 행]을 체크 해제하면 머리글 행이 숨겨집니다.
테이블 디자인에서 머리글 행을 체크 해제해서 머리글을 숨겨줍니다. 오빠두Tip : 표 스타일에서 스타일을 '없음'으로 변경하면, 기존 범위의 서식이 그대로 유지됩니다. - 파워쿼리 실행하기 : 표를 선택한 뒤, [데이터] 탭 - [테이블/범위에서] 버튼을 클릭하면 파워쿼리 편집기가 실행됩니다.
표 선택 - 데이터 - 테이블/범위에서를 클릭하면 파워쿼리가 실행됩니다. 오빠두Tip : 사용 중인 엑셀 버전에 따라 [테이블/범위에서] 위치가 조금씩 다를 수 있으며, 파워쿼리는 엑셀 2016 이후 버전부터 제공됩니다. 파워쿼리 기초 기능에 대한 자세한 설명은 아래 영상강의를 참고해주세요. - 파워쿼리 가공하기 : 파워쿼리 편집기가 실행되면 [변환] 탭 - [행/열바꿈] 을 클릭하여 행/열을 전환합니다. 이후 Ctrl 키를 누른 상태로 머리글이 입력된 열을 선택한 후 [변환] - [채우기] - [아래로]를 클릭하면 병합된 셀이 해제되면서 생긴 빈칸이 자동으로 채워집니다.
머리글 범위 선택 후, [채우기] - [아래로]를 클릭하면 빈칸이 한 번에 채워집니다. - 머리글 열이 선택된 상태에서 [변환] 탭 - [열 병합] 버튼을 클릭하면 '열 병합' 대화상자가 나타납니다. 대화상자에서 구분기호는 '공백'을 선택 후 [확인]을 누르면 머리글 열이 하나로 합쳐집니다.
머리글 열을 선택 후, 열 병합 클릭 - 공백으로 열을 병합합니다. 오빠두Tip : 구분기호는 줄바꿈, 세미콜론 등 다양한 기호를 선택할 수 있습니다. - 다시 [변환] 탭 - [행/열 바꿈]을 클릭하면 데이터가 원래 상태로 돌아가며, 마지막으로 [홈] 탭 - [첫 행을 머리글로 사용] 버튼을 클릭하면 병합된 머리글이 위로 승격되면서 표가 깔끔하게 완성됩니다.
행/열전환을 클릭하여 데이터를 원상태로 돌린 후, 첫 행을 머리글로 승격합니다. - 쿼리 출력하기 : 마지막으로 [파일] 탭 - [닫기 및 로드] 버튼을 클릭하면, 쿼리가 시트에 출력됩니다.
파일 - 닫기 및 로드로 쿼리를 시트로 출력합니다. - 이제 원본 데이터를 변경한 후, 표를 우클릭 - [새로고침] 버튼을 클릭하면 머리글이 여러 줄로 입력된 표가 머리글이 한 줄인 표로 자동으로 변환됩니다.
원본데이터를 변경 후, 표를 우클릭 - 새로고침 하면 표가 자동으로 갱신됩니다.
