엑셀에서 데이터를 정리하는 것은 데이터 분석의 기초가 됩니다. 정리되지 않은 데이터는 원하는 정보를 찾기 어렵게 만들고, 오류가 발생할 가능성을 높입니다. 이번 가이드에서는 엑셀의 데이터 정렬, 중복 제거, 빈 셀 정리, 필터링, 텍스트 정리, 그리고 자동화하는 방법을 다룹니다.
목차
- 1. 엑셀 데이터 정렬 (Sort)
- 2. 중복 데이터 제거
- 3. 빈 셀 정리
- 4. 데이터 필터링
- 5. 텍스트 데이터 정리 (TRIM, CLEAN, SUBSTITUTE)
- 6. 자동화 - VBA 매크로 활용
- 7. 마무리
1. 엑셀 데이터 정렬 (Sort)
엑셀에서 데이터를 특정 기준에 따라 정렬하는 기능은 데이터 분석을 쉽게 만들어 줍니다.
A. 기본 정렬
- 정렬할 데이터가 포함된 셀을 클릭합니다.
- 상단 메뉴에서 [데이터] → [정렬]을 선택합니다.
- 정렬할 열을 선택하고, 오름차순(▲) 또는 내림차순(▼)을 선택합니다.
- 확인을 클릭하여 정렬을 완료합니다.
B. 여러 개의 기준으로 정렬
- [데이터] → [정렬]을 선택합니다.
- 첫 번째 정렬 기준을 선택합니다 (예: '부서' 기준 오름차순).
- [기준 추가] 버튼을 눌러 두 번째 기준을 설정합니다 (예: '사원명' 기준 오름차순).
- 확인 버튼을 누르면 복합 정렬이 적용됩니다.
2. 중복 데이터 제거
데이터가 중복될 경우 분석 결과가 왜곡될 수 있으므로, 불필요한 중복값을 제거하는 것이 중요합니다.
A. 중복값 삭제
- 중복이 포함된 데이터를 선택합니다.
- [데이터] → [중복된 항목 제거]를 클릭합니다.
- 중복 확인 기준이 될 열(예: 상품, 카테고리, 가격 등)을 선택합니다.
- 확인을 클릭하면 중복 항목이 자동 삭제됩니다.
B. 중복값 하이라이트 표시
- 데이터를 선택합니다.
- [홈] → [조건부 서식] → [중복된 값]을 선택합니다.
- 원하는 색상을 지정하여 중복된 값을 강조 표시할 수 있습니다. 중복 여부를 확인하고 싶다면 조건부 서식을 활용할 수 있습니다.
- 중복된 값은 진한 빨강 텍스트 시현된다.
3. 빈 셀 정리
엑셀에서 빈 셀이 많으면 데이터를 효율적으로 관리하기 어렵습니다.
A. 빈 행/열 삭제
- 데이터를 선택한 후 [홈] → [찾기 및 선택] → [이동 옵션]을 클릭합니다.
- 빈 셀을 선택한 후 [삭제] 버튼을 눌러 빈 셀을 정리합니다.
- 빈 셀이 삭제된 데이터 형태를 확인이 가능합니다.
B. 빈 셀을 특정 값으로 채우기
- 데이터를 선택한 후 Ctrl + G (F5) → 이동 옵션을 클릭합니다.
- 빈 셀 선택 후 확인을 누릅니다.
- 빈 셀에 채울 값 '0'을 입력한 후 Ctrl + Enter를 누르면 모든 빈 셀에 동일한 값이 입력됩니다.
4. 데이터 필터링
필요한 데이터만 선별하여 볼 수 있도록 필터 기능을 활용할 수 있습니다.
- 데이터를 선택한 후 [데이터] → [필터]를 클릭합니다.
- 열 제목 옆에 ▼ 버튼이 생성됩니다.
- 원하는 조건을 설정(연봉이 5500만원 이상인 직원만 표시)하여 데이터를 필터링합니다.
- 필터를 해제하려면 [데이터] → [필터 지우기]를 클릭합니다.
5. 텍스트 데이터 정리
텍스트 데이터에는 불필요한 공백이나 특수 문자가 포함될 수 있습니다. 이를 정리하는 함수들을 활용할 수 있습니다.
=TRIM(A1): 앞뒤 및 중복된 공백 제거=CLEAN(A1): 보이지 않는 문자 제거=SUBSTITUTE(A1, " ", ""): 특정 문자 제거
6. 자동화 - VBA 매크로 활용
반복적인 데이터 정리 작업을 자동화하면 시간과 노력을 절약할 수 있습니다.
A. 빈 행 삭제하는 VBA 코드
엑셀에서 빈 행이 포함된 데이터가 있을 경우, 수동으로 삭제하는 것은 비효율적입니다. VBA 코드를 활용하면 빈 행을 자동으로 찾아서 삭제할 수 있습니다.
1. 코드 설명
다음 VBA 코드는 활성 시트에서 빈 행을 찾아 자동으로 삭제합니다.
Sub DeleteEmptyRows()
Dim rng As Range
Dim i As Integer
' 현재 사용 중인 범위를 설정합니다.
Set rng = ActiveSheet.UsedRange
' 마지막 행부터 첫 번째 행까지 역순으로 순회합니다.
For i = rng.Rows.Count To 1 Step -1
' 각 행의 데이터 개수를 확인하여 모든 셀이 비어있다면 삭제
If Application.WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
rng.Rows(i).Delete
End If
Next i
End Sub2. 코드 실행 방법
- 엑셀에서 VBA 편집기 열기: Alt + F11을 눌러 VBA 편집기를 엽니다.
- 새 모듈 추가: 삽입 → 모듈(Module)을 선택합니다.
- 코드 복사 및 붙여넣기: 위의 VBA 코드를 복사하여 붙여넣습니다.
- 매크로 실행: F5 키를 눌러 코드를 실행하면 빈 행이 자동으로 삭제됩니다.
3. 코드 동작 방식
- ActiveSheet.UsedRange를 사용하여 현재 사용 중인 범위를 가져옵니다.
- For i = rng.Rows.Count To 1 Step -1 : 데이터를 마지막 행부터 거꾸로 확인하면서 삭제합니다.(이를 통해 삭제 후 인덱스가 변경되는 문제를 방지합니다.)
- Application.WorksheetFunction.CountA(rng.Rows(i)) = 0 : 해당 행의 셀이 비어 있는지 확인하고 삭제합니다.
4. 개선 가능 사항
- 특정 열을 기준으로 빈 행을 삭제하도록 수정 가능
- 빈 셀을 포함한 행이 아니라, 완전히 빈 행만 삭제하도록 조건 변경 가능
이 코드를 사용하면 엑셀에서 불필요한 빈 행을 손쉽게 제거할 수 있습니다.
Sub DeleteEmptyRows()
Dim rng As Range
Set rng = ActiveSheet.UsedRange
Dim i As Integer
For i = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
rng.Rows(i).Delete
End If
Next i
End Sub7. 마무리
엑셀 데이터를 정리하는 것은 데이터의 정확성과 활용성을 높이는 데 필수적인 과정입니다. 이번 가이드에서는 엑셀의 기본적인 정렬(Sort), 중복 제거, 빈 셀 정리, 필터링, 텍스트 정리 및 VBA 자동화 방법을 살펴보았습니다. 실무에서 데이터를 효율적으로 관리하려면 이러한 기능들을 적절히 활용하는 것이 중요합니다.
댓글