엑셀 89강] 엑셀에서 사용할 수 있는 데이터 형태로 정리하기

 

엑셀의 다양한 기능과 함수를 사용하여 데이터를 관리하고 수식을 적용하여 결과를 구하기 위해서는 엑셀에서 요구하는 방식에 맞추어 데이터가 입력 되어야 합니다. 

좀 더 보충설명을 하면 데이터 범위에 병합된 셀이 있으면 안 되고 범위 중간중간에 빈 셀이 있어서도 안됩니다.

 

아래 예 중에서 왼쪽 이미지는 학교 업무 시스템에서 학생별 수업료, 수련비등의 미납금액을 엑셀 파일로 다운로드 받은 겁니다.

양식이 정해져 있는 페이지가 수백건 입니다.

이런 경우 엑셀의 함수 또는 다양한 기능을 적용하기가 쉽지 않습니다.

그래서 왼쪽 데이터를 오른쪽과 같이 엑셀에서 사용할 수 있는 데이터 형태로 정리하는 방법을 소개하겠습니다.

수작업으로는 데이터 양이 많아 쉽지 않습니다.

 

 

빨강 박스에 있는 빈 행 또는 불필요한 행을 제거해 보겠습니다.

A열 번호를 선택한 후 빈 셀을 모두 찾아 선택하고 행 전체를 삭제하는 방법이 있습니다.

하지만 빈 셀 외에도 "소계", "2017년  최상고등학교   1학년  1반" 텍스트가 있는 행도 제거해야 합니다.

엑셀 기능 중에는 빈 셀을 모두 선택하는 방법은 있지만 특정 글자가 있는 셀을 한 번에 선택하는 방법은 없습니다.

그래서 "소계"와 "2017년  최상고등학교   1학년  1반" 이 있는 셀을 찾아 빈 셀로 변경하는 작업도 해줘야 합니다.

 

 먼저 찾아 바꾸기 기능을 사용하여 "소계" 텍스트 셀을 빈 셀로 변경합니다.

[홈]-[찾기 및 선택]-‘찾을 내용’ 입력창에 “소계”라고 입력하고 ‘바꿀 내용’ 입력창을 비워둔 채 [모두 바꾸기]를  누릅니다.

 

 

전체 페이지에 “소계” 텍스트가 빈 공백으로 변경되었습니다.

 

 같은 방법으로 '2017년 ~ ' 이후 년도와 학교명은 동일하지만 학년과 반이 다른 데이터를 모두 찾아 빈 공백으로 바꾸려고 합니다.

이런 경우 찾을 내용에는 “2017년*” 이라고 입력합니다.

“*” 만능문자라고 합니다.

그럼 '2017년'로 시작하는 모든 문자 즉, 1학년 1반이든지 2반이든지 모두 찾아라는 명령이 됩니다.

 

 

A열 번호를 선택하고 [홈]-[이동 옵션]-‘빈 셀’ 옵션을 선택합니다.

빈 셀이 모두 선택되면 선택된 영역에서 마우스 오른쪽 버튼을 눌러 [삭제] 메뉴를 선택합니다.

 

 

 

삭제 대화상자에서 ‘행 전체’를 선택합니다.

선택된 행이 모두 제거됩니다.

 

위와 같은 방법으로 시트 전체에 빈 행을 모두 제거했습니다.

이제 첫 번째 제목 행을 남기고 나머지 제목 행을 모두 제거해야 합니다.

두 번째 제목행을 블록지정하고 단축키 <Ctrl + Shift + ↓ (방향키)>를 누르면 나머지 아래쪽 데이터를 쉽게 블록 지정할 수 있습니다.

[홈]-[찾기 및 선택]-‘찾을 내용’ 입력창에 “납부자정보”라고 입력하고 ‘바꿀 내용’ 입력창을 비워둔 채 [모두 바꾸기]를  누릅니다.

A열 번호를 선택하고 [홈]-[이동 옵션]-‘빈 셀’ 옵션을 선택합니다.

빈 셀이 모두 선택되면 선택된 영역에서 마우스 오른쪽 버튼을 눌러 [삭제]를 선택합니다.  

 

첫 번째 제목행을 제외하고 나머지 제목행이 제거되어 엑셀에서 사용 가능한 데이터로 정리되었습니다.

 

 

참고로 여기에 사용된 데이터는 실습용으로 만들어진 데이터입니다.

다음 강좌에서는 정리된 데이터를 다양한 결과를 구하는 방법에 대해서 소개하겠습니다. 

첨부파일을 다운로드 해서 연습해보세요~

 

실습 파일 다운로드

엑셀에서사용할수있는데이터형태로정리.xlsx

다운로드