엑셀로 데이터 정제하기 Tip

도너스 엑셀 양식을 작성할 때에는 양식에 명시된 규칙을 반드시 준수해야합니다. 데이터가 규칙에 맞지 않는 형식으로 작성되어 있는 경우 엑셀 기능 및 함수를 사용하여 규칙에 맞게 수정할 수 있습니다.

목차

• 1. 필수값, 오류값 검사하고 수정하기
• 2. 중복값 검사하고 통합하기
• 3. 두 개의 셀 하나로 합치기
• 4. 하나의 셀 두 개로 나누기
• 5. 형식 변경하기
• 6. 문자 일괄적으로 바꾸기

   * 참고: Microsoft 데이터를 정리하는 10가지 주요 방법

1. 필수값, 오류값 검사하고 수정하기

이관 양식 내 필수값은 *(빨간색 별표) 표시되어 있습니다. 엑셀 필터 기능을 활용하면 필수값이 미입력된 경우나 형식에 맞지 않는 오류 값을 검사할 수 있습니다.

필드명이 있는 행을 전체 선택한 후 [데이터탭]의 필터 버튼을 클릭합니다.

필수 필드의 필터를 클릭한 후 '(필드값 없음)'을 선택하여 미입력 데이터를 검사합니다.

또한 입력 형식에 맞지 않는 오류값을 찾는데도 활용할 수 있습니다.

• 날짜 형식이 YYYY-MM-DD가 아닌 경우
• 주민번호에 하이픈(-)이 포함되지 않은 경우
• 금액이 0이거나 음수인 경우
• 월 출금일에 1일~31일 외 다른 일자가 입력된 경우
  

2. 중복 값 검사하고 통합하기

이관 양식 작성 과정에서 동일한 데이터를 실수로 두번 복사해서 중복으로 데이터를 작성할 수 있습니다. 또 기존 시스템에 동일 후원자가 서로 다른 회원으로 등록되어 있었던 경우도 있습니다. 조건부 서식 및 필터를 활용해서 중복값을 통합할 수 있습니다.

1) 조건부서식으로 중복값 찾기

휴대전화번호 또는 이메일 열을 전체 선택한 후 [홈 탭 > 조건부서식 > 셀강조규칙 > 중복값]을 클릭합니다.

중복값에 빨강 채우기를 적용하면 중복값들에 색이 표시됩니다.

2) 중복값 필터링하기

중복값을 검사한 필드를 오름차순으로 정렬하거나 색 기준 필터를 적용하면 중복값만 쉽게 모아서 조회하고 편집할 수 있습니다.

3) 중복값 통합하기

중복 유형에 따라 알맞는 편집방법으로 중복값을 통합합니다.

• 동일한 후원자지만 식별코드가 다른 경우, 식별코드를 하나로 통일합니다. (약정이 2개 이상인 경우)
• 동일 약정인데 실수로 중복 작성된 경우, 약정 하나만 남기고 나머지 행은 모두 삭제합니다.

3. 두 개의 셀 하나로 합치기

기존 시스템에서는 필드가 두 개로 나누어진 정보였으나, 도너스에서는 하나로 통합되어야하는 경우 CONCATENATE 함수를 활용할 수 있습니다.

• 합칠 두 개의 셀을 지정하고 그 사이에 콤마 및 띄어쓰기를 삽입했습니다.
• =CONCATENATE(셀1,", ",셀2)

4. 하나의 셀 두 개로 나누기

기존 시스템에서는 필드 한 개로 구성된 정보였으나, 도너스에서는 두 개로 나누어 입력하고자 하는 경우 텍스트 나누기 기능을 사용하여 분할할 수 있습니다.

나눌 필드 열을 전체 선택하고 [데이터 탭>텍스트 나누기] 버튼을 클릭합니다.

구분기회로 분리됨을 클릭하고, 위 사례의 경우 기타: 슬래시(/)로 구분하여 분할합니다.

5. 형식 변경하기

기존 시스템의 주민번호는 하이픈(-)이 포함되지 않았으나 도너스에는 하이픈을 포함해야하는 경우 사용자 지정 형식을 활용하여 형식을 변경할 수 있습니다.

형식을 변경하고자하는 열을 선택한 후 [홈 탭]의 표시형식 확장 버튼을 클릭합니다.

사용자지정 형식에 원하는 형식을 입력합니다.

• 주민번호: ######-#######
• 휴대전화번호: 0##-####-####
• 날짜: YYYY-MM-DD
  

6. 문자 일괄적으로 바꾸기

특정 문자열이나 기호를 제거하거나 다른 문자로 대체해야할 때 찾기/바꾸기와 SUBSTITUTE 함수를 활용할 수 있습니다.

1) 찾기/바꾸기

바꾸고싶은 셀 영역을 선택한 후 [홈 탭] 찾기 및 선택 옵션에서 바꾸기 버튼을 클릭합니다. (셀을 지정하지 않으면 전체 엑셀 파일 내 데이터가 모두 바뀌니 주의하세요!)

찾을 내용에 기존 문자열을, 바꿀 내용에 대체할 문자열을 입력하고 모두 바꾸기 버튼을 클릭합니다.

2) SUBSTITUTE 함수

SUBSTITUTE 함수를 사용하여 기존 문자열을 새 문자열로 대체할 수 있습니다.

• =SUBSTITUTE(셀, 기존문자열, 새문자열)