들어가며
Excel에는 여러 가지 강력한 분석 도구들이 있습니다. 필터, 정렬, 피벗 테이블 등이 대표적입니다. 이런 분석 도구들을 활용하려면 소스 데이터가 제대로 정리되어 있어야 합니다. 이번 시간에는 Excel에서 데이터를 정리하는 방법에 대해 살펴봅니다. 제대로 구성된 테이블은 정렬이나 필터, 피벗 테이블을 제대로 활용하기 위한 근간이 되므로 매우 중요합니다. 기대되시죠? 출발합니다.
중요한 용어 몇 가지
워크시트에 어떤 자료를 작성할 때 흔히 아래와 같은 형식으로 정리합니다. 이러한 형태를 테이블, 표, 데이터베이스 등 여러 가지로 부릅니다. 엑셀의 강력한 분석 툴을 활용하려면 테이블이 온전한 데이터베이스 구조를 하고 있어야 합니다.
(1) 표 제목: 각 필드(열)의 제목 입력
(2) 필드(field):데이터베이스의 열
(3) 레코드(record): 데이터베이스의 행
테이블의 종류 3가지
데이터가 이런 형태로 정리되어 있다면 엑셀의 분석 도구를 사용하는 데 무리가 없습니다. 우리가 엑셀에서 흔히 사용하는 표(테이블)는 크게 세 가지 종류로 구분할 수 있습니다. [테이블 1]과 같은 형태를 '로데이터(raw=data)', '데이터베이스', '표준 테이블' 등으로 부릅니다. [테이블 2]는 '변형된 테이블', [테이블 3]은 '크로스탭(Cross-tab) 테이블'이라고 합니다.
어떻게 부르느냐는 중요하지 않습니다. 테이블 2나 3은 보고를 받는 사람 관점에서 보기 편하게 만든 형태입니다. [테이블 1]과 같은 원시 데이터를 반드시 백업해 두는 있는 것이 바람직합니다.
테이블 작성 시 유의할 점
엑셀 워크시트에 테이블을 작성할 때 주의해야 할 점을 4가지로 정리했습니다. 이론적인 느낌이 살짝 있지만 매우 매우 중요한 사항입니다. 이 기준이 충족되지 않으면 엑셀의 강력한 분석 기능을 제대로 활용할 수 없게 됩니다.
1. 필드 제목은 반드시 입력하고 셀 병합하지 않는다
모든 필드의 제목은 빠짐 없이 입력하고 테이블 제목이나 테이블 내에는 병합된 셀이 없도록 합니다. 테이블 내부나 제목에 병합된 셀이 있으면 어떤 일이 생기는 지, 그런 오류를 방지하려면 어떻게 해야 하는 지는 다음 영상에 잘 나와 있습니다. 단, 엑셀 입문용은 아니므로 이해되지 않는 부분이 나오더라도 스트레스 받지 말고 넘어 가세요. 쓸데 없이(?) 셀 병합을 하면 어떤 사태가 생기는지만 알면 됩니다.
✏️ 셀 병합에 신중해야 하는 이유와 대처법 ㅡ 그럼에도 엑셀 셀 병합이 꼭 필요하다면 이렇게!
2. 테이블 중간에 빈 행이나 열이 없게 한다
데이터 중간에 공백 데이터가 있으면 필터나 피벗 테이블을 만들 때 원치 않는 결과가 나타날 수 있고, 수식을 작성할 때에도 실수로 이어지기 쉽습니다. 테이블에는 구조적으로 같은 데이터를 반복해서 입력할 수 밖에 없습니다. [테이블 1]의 '영업팀'은 영업사원이 다르더라도 반복될 수 밖에 없습니다. 셀을 비워두지 말고 값을 제대로 입력하세요.
'빈 셀 채우기' 기능을 통해 빈 값을 쉽게 처리할 수 있습니다. 이와 관련해서는 나중에 소개할 기회가 있으리라 생각합니다. 도저히 그때까지 참을 수 없는 분들은 인터넷에서 '엑셀 빈 셀 채우기'로 검색해 보세요.
3. 한 열에는 같은 성격의 데이터를 입력한다
전문적인 데이터베이스는 필드(위의 그림에서 (2))마다 정해진 형식의 데이터만 입력할 수 있지만 엑셀은 자유롭게 입력할 수 있습니다. 예를 들어, 데이터베이스에서는 '수량'이나 '금액' 필드의 데이터 형식을 '숫자'로 지정하면 숫자값만 입력할 수 있는 반면, 엑셀은 그런 제약을 받지 않습니다. 이것은 장점인 동시에 단점이 되기도 합니다.
하나의 필드(열)에는 같은 성격의 정보를 입력하세요. 숫자면 숫자, 문자면 문자를 입력합니다. 같은 숫자라도 숫자의 형식을 지키세요. 위에는 원 단위, 아래에서는 퍼센트 이런 식으로 하면 곤란하다는 겁니다. 나중에 자료 활용을 염두에 둔다면 지극히 상식적인 내용일 뿐 어렵거나 까다로운 것은 개념은 아닐 겁니다.
4. 하나의 레코드는 행 단위로 작성한다
하나의 행에 여러 레코드를 입력하거나, 반대로 별개 레코드로 정리해아 할 것이 한 행에 정리되어 있다면 데이터를 효율적으로 분석하고 처리할 수 없습니다. 첫 번째 이미지의 (3)을 잘 들여다 보세요.
일반 테이블과 엑셀 표
팀별/품목별 실적이 집계된 표가 있습니다. 엑셀에서 숫자값을 더하고자 할 때 SUM 함수를 사용합니다. 함수나 수식에 대해서는 나중에 다시 나오므로 지금은 몰라도 됩니다. '=sum('를 입력하고(작은 따옴표 제외), 마우스로 범위를 지정한 다음, Enter를 누르면 지정한 범위에 있는 숫자값을 더한 결과를 구해줍니다.
다음 그림에서 G7 셀에는 '=SUM(G3:G6)'이 입력되어 있습니다. G3:G6 셀에 있는 숫자의 합계를 구하는 수식입니다. 이 상태에서 기존 데이터 맨 아래쪽에 새로운 데에터가 추가되었습니다(19행). 하지만 G7 셀에는 반영되지 않습니다.
일반 테이블을 엑셀 표로 변환하기
이럴 때에는 수식의 범위를 직접 수정해도 되지만 엑셀의 [표] 기능을 사용하면 편리합니다.
(1) 테이블 내부의 셀을 하나 선택하고 [삽입] 탭 - [표] 그룹 - [표]를 클릭합니다(Ctrl + T를 눌러도 됩니다). [표 만들기] 대화상자에서 데이터 범위와 [머리글 포함]에 체크가 되었는지 살펴보고 [확인] 버튼을 누릅니다.
'테이블 작성 시 유의할 점'에서 언급한 점이 지켜지지 않았다면 표로 변경할 범위를 수동으로 지정해야 할 수도 있습니다. 벌써부터 문제가 생기기 시작하죠?
(2) 범위가 표로 변환되면서 표 스타일이 자동으로 적용됩니다. 리본 메뉴에도 변화가 생겼습니다. 표 내부에 있는 임의의 셀이 선택되었다면 [테이블 디자인] 탭이 나타납니다.
(3) B19:D19 셀에 새로운 데이터를 추가하고 Enter를 눌러보세요. 표가 자동으로 확장하면서 G7 셀의 결과값도 자동으로 업데이트 됩니다.
데이터가 계속 쌓여나가는 형태라면 엑셀 표로 만들어 두면 일일이 범위를 변경하지 않아도 되므로 편리합니다.
표가 자동으로 확장되지 않을 때 대처 방법
표의 아래(또는 오른쪽)에 새로운 데이터를 추가해도 표가 자동으로 확장되지 않는다면 다음 사항을 점검해 보세요.
(1) [파일] - [옵션]을 클릭합니다.
(2) [Excel 옵션] 대화상자에서 [언어 교정] - [자동 고침 옵션] 버튼을 누릅니다.
(3) [자동 고침] 대화상자가 나타납니다. [입력할 때 자동 서식] 탭 - [표에 새 행 및 열 포함 항목에 체크를 합니다.
엑셀 표 해제하는 방법
엑셀 표는 편리하지만 경우에 따라서는 해제해야 하는 경우도 있습니다. 엑셀 표 내부의 셀을 하나 선택하고 [테이블 디자인] 탭 - [도구] 그룹 - [범위로 변환]을 클릭합니다. 그렇습니다! 표를 '해제'하는 것이 아니라 일반 범위 형태로 '변환'하는 개념입니다.
표를 정상 범위로 변환할 것인지를 묻는 메시지 상자가 나타나면 [예] 버튼을 누르면 됩니다. 범위로 변환되고 나면 엑셀 표의 특징([테이블 디자인] 탭, 표 확장 등)은 모두 제거되지만 엑셀 표를 만들 때 적용된 표 스타일은 그대로 남아 있습니다.
나가며
야구를 즐기기 위해서는 야구 규칙을 알아야 합니다. 축구나 다른 스포츠도 마찬가지죠. 스포츠만 그럴까요? 아니요, 엑셀도 마찬가지입니다. 우리가 엑셀을 사용하는 이유는 데이터를 정리하고 분석하기 위해서입니다. 그러기 위해서는 엑셀이 잘 처리할 수 있도록 자료를 제공해야 합니다. 이번 시간에는 엑셀에서 데이터를 관리하는 방법에 대해 전반적으로 살펴보았습니다. 어려운 한 고비를 또 넘으셨습니다. 축하합니다.
'Excel' 카테고리의 다른 글
| 피벗 테이블에서 데이터를 새로 고치는 3가지 방법 (0) | 2023.12.08 |
|---|---|
| Excel 문서가 저장되지 않는 오류 수정하는 방법 (2) | 2023.12.01 |
| 간트 차트에 관한 거의 모든 것 ㅡ 개념부터 활용까지 (2) | 2023.12.01 |
| Excel PV 함수를 사용하여 연금의 현재 가치를 계산하는 방법 (2) | 2023.11.26 |
| 돈 관리를 위한 16가지 Excel 템플릿 (4) | 2023.11.26 |
