엑셀 지저분한 데이터를 깔끔한 표로 바꾸는 방법

정렬, 필터, 피벗 테이블, 그리고 대부분의 수식은 데이터가 단순한 모양이라는 걸 전제로 해요. 머리글 행은 하나, 중간에 빈칸이 없고, 각 열에는 위에서 아래까지 같은 종류의 값만 들어 있어야 하죠. 먼저 이 모양을 갖춘 다음 범위를 엑셀 표로 바꾸세요. 이 글에서는 지저분한 배치를 찾아 고치고, 표로 변환해서 이름을 붙이고, 저절로 계산되는 열과 필터에 맞춰 바뀌는 합계를 추가하는 방법을 배워요.

표로 쓸 준비가 된 데이터란?

범위를 표로 바꾸려면 다음 네 가지 규칙을 지켜야 해요.

• 머리글 행은 하나. 첫 행에 각 열의 이름이 딱 하나씩 있고, 이름이 서로 겹치지 않아요. 데이터 위나 중간에 제목 행이나 두 번째 머리글 행이 없어요.
• 데이터 안에 빈 행이나 빈 열이 없음. 빈 행이 있으면 엑셀은 데이터가 거기서 끝났다고 판단해요.
• 병합된 셀이 없음. 값 하나는 자기 셀 하나에 들어 있어요.
• 한 열에는 한 종류의 값. 매출 열에는 숫자만, 날짜 열에는 진짜 날짜만 있고, 중간에 텍스트로 입력된 값이 섞여 있지 않아요.

아래 판매 목록은 이 규칙을 거의 다 어기고 있어요.

fx

	A	B	C	D	E
1	1분기 영업 담당자
2
3	담당자	지역	매출	목표	수수료율
4	이아리	동부	18,400	17,500	5%
5	박미나	서부	14600	16,000	5%
6
7	배준호	중부	22,100	20,000	6%
8	오지은	동부	9,900	12,000	4%
9	신동현	서부	31,500	30,000	7%
10	합계		96,500

이런 문제는 나중에 하나씩 말썽을 일으켜요. 빈 행 때문에 엑셀이 범위를 자동으로 찾을 때 거기서 멈추기 때문에, 표나 정렬, 피벗 테이블이 담당자 일부만 가져가요. C5처럼 텍스트로 저장된 숫자는 SUM이 건너뛰어요. 10행에 직접 입력한 합계는 필터나 피벗 테이블 입장에서는 담당자가 한 명 더 있는 것처럼 보여서 모든 합계가 두 배가 돼요.

변환하기 전에 배치부터 고치기

위에서부터 차례대로 문제를 고쳐 보세요. 하나에 몇 초면 돼요.

1. 제목 행과 빈 행을 지우세요

   제목이 있는 행(1행)의 행 번호를 마우스 오른쪽 단추로 클릭하고 삭제를 고르세요. 머리글 위의 빈 행과 데이터 중간의 빈 행도 같은 방법으로 지우세요. 제목을 남기고 싶다면 제목과 머리글 사이에 빈 행을 하나 두세요. 그래야 엑셀이 제목을 데이터의 일부로 착각하지 않아요.

2. 병합된 셀을 해제하세요

   병합된 셀을 선택하고 홈› then 맞춤› then 병합하고 가운데 맞춤(Merge & Center)을 클릭해서 병합을 해제하세요. 병합 때문에 지역 이름처럼 여러 행에 해당하는 값이 한 칸에만 들어 있었다면, 그 값을 해당하는 모든 행에 입력하세요.

3. 텍스트로 저장된 숫자를 변환하세요

   텍스트로 저장된 숫자는 셀 왼쪽에 붙어 있고, 보통 작은 초록색 삼각형이 보여요. 해당 셀을 선택하고 나타나는 경고 아이콘을 클릭한 다음 숫자로 변환을 고르세요.

4. 직접 입력한 합계를 지우세요

   직접 입력한 합계 행은 지우세요. 표에 있는 요약 행(Total Row)이 그 역할을 대신하고, 요약 행은 데이터와 따로 관리돼요.

정리를 마치면 머리글 행 하나와 데이터 다섯 행만 빈틈없이 남아요.

fx

	A	B	C	D	E	F
1	담당자	지역	매출	목표	수수료율
2	이아리	동부	18,400	17,500	5%
3	박미나	서부	14,600	16,000	5%
4	배준호	중부	22,100	20,000	6%
5	오지은	동부	9,900	12,000	4%
6	신동현	서부	31,500	30,000	7%

범위를 표로 변환하기

1. 데이터 안의 아무 셀이나 클릭하세요

   A1이나 목록 안의 다른 아무 셀이나 클릭하세요. 범위 전체를 선택할 필요는 없어요. 엑셀이 데이터의 가장자리를 알아서 찾아요.

2. Ctrl+T를 누르세요

   Ctrl+T를 누르세요. 삽입› then 표› then 표를 써도 되고, 색 스타일을 먼저 고르고 싶다면 홈› then 스타일› then 표 서식(Format as Table)을 써도 돼요. 그러면 표 만들기(Create Table) 대화 상자가 열리고 엑셀이 찾은 범위가 표시돼요. 여기서는 =$A$1:$E$6이에요.

3. 범위와 머리글 확인란을 확인하세요

   범위에 데이터의 모든 행과 열이 들어 있는지 확인하세요. 범위가 중간에 끊겨 있다면 아직 어딘가에 빈 행이나 빈 열이 남아 있는 거예요. 머리글 포함은 체크된 그대로 두고 확인을 클릭하세요.

이제 범위가 표가 됐어요. 머리글마다 필터 화살표가 생기고, 행에 줄무늬가 들어가고, 표 안을 클릭할 때마다 리본에 테이블 디자인(Table Design) 탭이 나타나요. 7행에 새 담당자를 입력하면 표가 저절로 늘어나서 그 행까지 포함하고, 서식과 수식도 함께 적용돼요.

표에 제대로 된 이름 붙이기

엑셀은 표 이름을 표1, 표2처럼 붙여요. 이름을 바꿔 두면 그 표를 쓰는 수식이 무엇을 계산하는지 한눈에 보여요.

1. 표 이름 상자를 찾으세요

   표 안의 아무 셀이나 클릭하고 테이블 디자인› then 속성› then 표 이름을 보세요. 상자에 기본 이름이 들어 있어요.

2. 새 이름을 입력하고 Enter를 누르세요

   상자를 클릭하고 SalesData를 입력한 다음 Enter를 누르세요.

표 이름은 문자나 밑줄(_)로 시작해야 하고, 공백을 넣을 수 없고, 셀 주소처럼 보이면 안 돼요(Q1, R2C3 같은 이름은 쓸 수 없어요). 단어를 구분하려면 대문자나 밑줄을 쓰세요. SalesData, Sales_Q1처럼요.

이제 통합 문서 어디에서나 이름으로 열 전체를 가리킬 수 있어요. 표 밖의 아무 셀에 아래 수식을 입력하면 96,500이 나와요.

=SUM(SalesData[매출])복사

SalesData[매출]은 "매출 열의 데이터 셀들"이라는 뜻이고, 행을 추가하면 저절로 늘어나요. 이제 C2:C6 같은 범위를 고칠 일이 없어요.

계산된 열 추가하기

계산된 열(calculated column)은 수식을 한 번만 입력하면 열 전체가 저절로 채워지는 열이에요. 담당자별 매출에 수수료율을 곱하는 수수료 열을 추가해 볼게요.

1. 표 바로 옆에 머리글을 입력하세요

   F1을 클릭하고 수수료를 입력한 다음 Enter를 누르세요. 표가 곧바로 F열까지 늘어나요.

2. 셀을 클릭해서 수식을 만드세요

   F2에 =를 입력하고 C2를 클릭한 다음, *를 입력하고 E2를 클릭하세요. 두 셀이 표 안에 있기 때문에 엑셀은 셀 주소 대신 구조적 참조(structured reference)를 써 줘요.

   =[@매출]*[@수수료율]복사

   [@매출]은 "이 행의 매출 값"이라는 뜻이에요. 열 이름에 공백이 있으면(예: 수수료 비율) 엑셀이 [@[수수료 비율]]처럼 대괄호를 한 번 더 씌워요.

3. Enter 키를 누르세요

   엑셀이 열의 모든 행에 수식을 채워 줘요. 따로 복사할 필요가 없어요.

   F2fx=[@매출]*[@수수료율]

   	A	B	C	D	E	F
   1	담당자	지역	매출	목표	수수료율	수수료
   2	이아리	동부	18,400	17,500	5%	920
   3	박미나	서부	14,600	16,000	5%	730
   4	배준호	중부	22,100	20,000	6%	1,326
   5	오지은	동부	9,900	12,000	4%	396
   6	신동현	서부	31,500	30,000	7%	2,205

열의 모든 셀에 똑같은 수식이 들어 있어서 어느 한 행이 엉뚱한 셀을 가리킬 위험이 없어요. 7행에 담당자를 추가하면 그 행의 수수료도 복사하지 않아도 바로 계산돼요.

요약 행 켜기

1. 요약 행을 켜세요

   표 안의 아무 셀이나 클릭하고 테이블 디자인› then 표 스타일 옵션› then 요약 행에 체크하세요. 데이터 아래에 새 행이 생기고, 첫 셀에는 요약이라고 표시되고, 마지막 열에는 합계가 자동으로 들어가요. 수수료 열 아래에 5,577이 표시돼요.

2. 다른 열의 요약 방식을 고르세요

   매출 열 아래의 요약 행 셀(C7)을 클릭하세요. 셀 오른쪽에 화살표가 나타나요. 화살표를 클릭하고 합계를 고르면 96,500이 나와요. 목록에는 평균, 개수, 최대값, 최소값 등도 있어요. 평균을 고르면 19,300이 표시돼요.

   C7fx=SUBTOTAL(109,[매출])

   	A	B	C	D	E	F
   1	담당자	지역	매출	목표	수수료율	수수료
   2	이아리	동부	18,400	17,500	5%	920
   3	박미나	서부	14,600	16,000	5%	730
   4	배준호	중부	22,100	20,000	6%	1,326
   5	오지은	동부	9,900	12,000	4%	396
   6	신동현	서부	31,500	30,000	7%	2,205
   7	요약		96,500			5,577

어떤 요약 방식을 고르든 엑셀은 그 뒤에 SUBTOTAL 수식을 써 넣어요. 합계는 =SUBTOTAL(109,[매출]), 평균은 =SUBTOTAL(101,[매출])이에요. SUBTOTAL은 필터로 숨겨진 행을 빼고 계산하기 때문에 합계가 항상 화면에 보이는 데이터와 맞아요. 지역 열을 동부로 필터링하면 매출 합계가 이아리와 오지은의 매출을 더한 28,300으로 바뀌어요. 일반 SUM이었다면 계속 96,500을 보여 줬을 거예요.

나중에 표를 다시 일반 범위로 되돌리려면(서식은 그대로 남아요) 테이블 디자인› then 도구› then 범위로 변환을 쓰세요.

자주 하는 실수

• 빈 행이 남은 채로 변환하기. 엑셀이 찾는 범위가 빈 행에서 끊기기 때문에 그 위쪽 행만 표가 돼요. 빈 행부터 지우고, 확인을 누르기 전에 표 만들기 대화 상자의 범위를 꼭 확인하세요.
• 데이터 안에 합계 행을 직접 입력하기. 직접 입력한 합계는 데이터 행이 하나 더 있는 것과 같아요. 필터에도 나오고, 피벗 테이블 합계에도 더해지고, 계산된 열도 그 행에서 계산돼요. 요약 행을 쓰세요.
• 텍스트로 저장된 숫자를 그대로 두기. 요약 행의 합계와 SUM(SalesData[매출]) 모두 이런 값을 아무 경고 없이 건너뛰어서 합계가 실제보다 작게 나와요.
• 머리글 이름이 겹치기. 두 열의 이름이 모두 매출이면 엑셀이 두 번째 열 이름을 매출2로 바꿔요. 변환하기 전에 열마다 겹치지 않는 분명한 이름을 붙이세요.
• 기본 이름을 그대로 쓰기. 표3[금액]처럼 쓰인 수식은 한 달만 지나도 알아보기 어려워요. 표를 만들자마자 이름을 바꾸세요.

연습

연습

지저분한 내보내기 데이터를 표로 바꾸기

1. 실제로 가지고 있는 목록을 하나 여세요. 판매 내보내기 데이터, 신청자 명단, 지출 목록 등 무엇이든 좋아요. 마땅한 게 없다면 이 글의 판매 데이터를 빈 행과 직접 입력한 합계까지 그대로 입력하세요.
2. 제목 행, 빈 행, 직접 입력한 합계를 지우고, 병합된 셀을 해제하고, 텍스트로 저장된 숫자를 변환하세요.
3. 데이터 안의 셀을 클릭하고 Ctrl+T를 누른 다음, 범위를 확인하고 확인을 클릭하세요.
4. 테이블 디자인› then 속성› then 표 이름에서 어떤 데이터인지 알 수 있는 이름으로 바꾸세요.
5. 수수료, 세금 포함 가격, 목표와의 차이처럼 계산된 열을 하나 추가하세요.
6. 요약 행을 켜고, 그 데이터에 대해 실제로 궁금한 질문에 답해 주는 요약 방식(합계, 평균, 개수)을 고르세요. 그런 다음 한 열에 필터를 걸고 요약 값이 어떻게 바뀌는지 보세요.

실력 점검

• 어떤 범위가 표로 쓸 준비가 됐는지 설명하고, 그렇지 않은 범위를 고칠 수 있나요?
• 범위를 표로 변환하고 알아보기 쉬운 이름을 붙일 수 있나요?
• 아래로 저절로 채워지는 계산된 열을 추가할 수 있나요?
• 요약 행을 켜고 요약 방식을 바꿀 수 있나요?