Microsoft Excel에서 데이터를 정리하는 8가지 방법

실수는 발생할 수 있습니다, 특히 스프레드시트에서요. 오타, 여분의 공백, 중복, 수식 오류 및 빈 셀은 데이터를 분석할 때 문제를 일으킬 수 있습니다. Microsoft Excel 스프레드시트를 정리하려면 “더러운 데이터”를 처리하기 위해 다음 방법 중 하나 이상을 시도해 보세요.

참고: “C” 드라이브의 데이터도 정리해야 하나요? 방법을 알려드립니다.

목차

• 1. 여분의 공백 제거
• 2. 인쇄되지 않는 문자 제거
• 3. 빈 행 삭제
• 4. 대소문자 수정
• 5. 중복 찾기 및 제거
• 6. 서식 지우기
• 7. 텍스트를 열로 변환
• 8. 오류 강조
• 자주 묻는 질문

1. 여분의 공백 제거

데이터를 가져오거나 잘못 입력하면 여분의 공백이 생길 수 있습니다. 여기에는 셀의 시작 부분에 있는 선행 공백, 끝 부분에 있는 후행 공백 또는 단어 또는 값 사이의 잡다한 공백이 포함될 수 있습니다. 이들은 무해해 보일 수 있지만, 여분의 공백이 있는 데이터를 필터링, 정렬 또는 수식 계산할 때 문제를 일으킬 수 있습니다.

Excel 시트에서 여분의 공백을 제거하는 두 가지 간단한 방법이 있습니다: TRIM 함수와 찾기 및 바꾸기 도구입니다.

TRIM 함수 사용

Excel의 TRIM 함수는 데이터에서 공백을 제거하는 데 특별히 작동합니다. 수식의 구문은 간단히 TRIM(text)입니다. 인수에 대한 셀 참조 또는 텍스트를 입력하세요.

예를 들어, 셀 A1에 텍스트 사이에 선행, 후행 및 여분의 공백이 있습니다. 다른 셀로 이동하여 다음 수식을 입력하여 공백을 제거합니다:

=TRIM(A1)

이 함수는 공백을 제거하고 깨끗한 데이터를 제공합니다.

또 다른 예로, 셀 참조 대신 인수로 텍스트를 입력할 수 있습니다. 다음 수식을 사용하여 텍스트를 따옴표로 묶어 입력하세요:

=TRIM(“   John    Jacob    Smith   ”)

공백이 제거된 것을 볼 수 있습니다.

TRIM 함수를 사용할 때 주의할 점은 데이터가 포함된 셀과 다른 셀에 수식을 입력해야 한다는 것입니다 – 해당 데이터를 대체하고 싶지 않다면요.

팁: 이러한 고급 Excel 수식이 매우 유용할 수 있습니다.

찾기 및 바꾸기 도구 사용

시트에 여분의 공백이 흩어져 있다면 TRIM 함수 대신 Excel의 찾기 및 바꾸기 도구를 사용하는 것이 좋습니다. 이 방법을 사용하면 모든 공백 또는 제거할 특정 수의 공백을 제거할 수 있습니다.

1. “홈” 탭으로 이동하여 편집 섹션에서 “찾기 및 선택” 드롭다운 메뉴를 열고 “바꾸기”를 선택합니다.

1. 나타나는 찾기 및 바꾸기 상자에서 “찾을 내용” 상자에 모든 공백을 제거하려면 단일 공백을 입력하거나 제거할 정확한 수의 공백을 입력합니다.

1. “바꿀 내용” 상자에 모든 공백을 제거하려면 아무것도 입력하지 않거나 유지할 정확한 수의 공백을 입력합니다.

1. “모두 바꾸기”를 선택하여 여분의 공백을 제거하면 시트가 즉시 업데이트됩니다.

2. 인쇄되지 않는 문자 제거

인쇄되지 않는 문자는 여분의 공백과 유사하게 나타날 수 있습니다. 이들은 7비트 ASCII 코드에서 0을 포함한 처음 32개의 문자로, 널 문자, 제목 시작, 페이지 나누기, 캐리지 리턴 및 단위 구분자와 같은 것들입니다. 공백처럼 이러한 문자는 Excel 시트에서 수식을 계산하거나 정렬하거나 필터링할 때 문제를 일으킬 수 있습니다.

편리하게도, Excel에서 인쇄되지 않는 문자를 제거하는 간단한 함수를 사용할 수 있습니다. 이 함수는 CLEAN이라고 하며, 수식의 구문은 CLEAN(text)입니다. 여기서 인수로 텍스트 또는 셀 참조를 입력할 수 있습니다.

이 예에서는 셀 A1의 수식 시작과 끝에 인쇄되지 않는 문자 CHAR(10)가 있습니다.

다음 수식을 사용하여 제거합니다:

=CLEAN(A1)

기존 텍스트를 깨끗한 텍스트로 대체하려면 CLEAN 수식의 일부로 텍스트를 입력할 수도 있습니다. 다음 수식을 사용하세요:

=CLEAN(CHAR(10)&"John Jacob Smith"&CHAR(10))

인쇄되지 않는 문자가 제거되고 텍스트만 남습니다.

3. 빈 행 삭제

시트에 빈 행이 생기면 실제 데이터에 필요한 공간을 차지할 수 있습니다. Excel에서 빈 행을 몇 단계 만에 제거하여 시트의 공간을 회복할 수 있습니다.

1. “홈” 탭으로 이동하여 편집 섹션에서 “찾기 및 선택” 드롭다운 메뉴를 열고 “특별 이동”을 선택합니다.

1. 나타나는 창에서 “빈 셀“을 선택하고 “확인”을 클릭합니다.

1. 모든 빈 셀이 일시적으로 강조 표시됩니다. 행을 삭제하기 전에 다른 데이터가 없는지 검토하세요. 이는 오른쪽으로 스크롤하지 않고 추가 열을 볼 수 없는 경우 특히 중요합니다. 빈 행을 제거하려면 “홈” 탭으로 돌아가서 “삭제” 드롭다운 메뉴를 열고 “시트 행 삭제”를 선택합니다.

1. 빈 행이 사라지고 시트의 나머지 행이 위로 이동하는 것을 볼 수 있습니다.

알아두면 좋은 점: Microsoft Excel에서 텍스트를 결합하는 방법을 배우세요.

4. 대소문자 수정

데이터를 가져오는 경우 데이터에 혼합된 대소문자가 있을 수 있습니다. Excel에서 대소문자를 일관되게 만들기 위해 몇 가지 간단한 대소문자 함수를 사용할 수 있습니다.

LOWER는 모든 문자를 소문자로 변환하고, UPPER는 모든 문자를 대문자로 변환하며, PROPER는 텍스트 문자열의 각 단어의 첫 글자를 대문자로 만들고 나머지 문자를 소문자로 변경합니다. 또한, PROPER는 문자 이외의 문자 뒤의 첫 글자를 대문자로 만듭니다. 예를 들어 1a는 1A로 변환됩니다.

각 함수의 구문은 동일하며, LOWER(text), UPPER(text), PROPER(text)입니다. 여기서 인수로 텍스트 또는 셀 참조를 사용할 수 있습니다. 예제를 살펴보겠습니다.

셀 A1의 모든 문자를 소문자로 변경하려면 다음 수식을 사용하세요:

=LOWER(A1)

같은 셀에서 모든 문자를 대문자로 변경하려면 다음 수식을 사용하세요:

=UPPER(A1)

셀 A1의 텍스트 문자열에서 각 단어의 첫 글자를 대문자로 만들려면 다음 수식을 사용하세요:

=PROPER(A1)

또한 수식에서 인수로 셀 범위를 사용할 수 있습니다. PROPER 함수를 사용하여 A1에서 A5까지의 셀에서 대소문자를 변경할 수 있습니다:

=PROPER(A1:A5)

5. 중복 찾기 및 제거

중복 데이터는 Excel 시트에서 정리해야 할 더 많은 데이터입니다. 동일한 고객 이름, 이메일 주소, 전화번호 또는 유사한 항목이 있을 수 있으며, 중복은 필요 없는 추가 데이터입니다.

Microsoft Excel에서 중복을 찾고 제거하는 방법은 여러 가지가 있으며, 이 주제에 대한 다양한 방법을 다룬 튜토리얼을 확인할 수 있습니다. 여기서는 Excel의 내장된 중복 제거 기능을 사용하는 가장 간단한 옵션만 살펴보겠습니다.

Excel은 첫 번째 인스턴스를 유지하고 다음 인스턴스를 제거합니다.

1. 중복이 포함된 셀 범위를 선택한 후 “데이터” 탭으로 이동하여 데이터 도구 그룹에서 “중복 제거”를 클릭합니다.

1. 팝업 창에서 검토하고 제거할 데이터를 좁힐 수 있습니다. 먼저 중복을 검토할 열에 대해 체크박스를 선택하거나 “모두 선택”을 사용하세요. 다음으로 데이터에 헤더가 포함되어 있으면 체크박스를 선택합니다. “확인”을 클릭하여 계속합니다.

1. Excel이 중복 데이터를 성공적으로 제거하면 삭제된 항목과 유지된 항목의 수를 알려주는 팝업 메시지가 표시됩니다.

1. 중복이 제거된 시트가 정리됩니다.

참고: PDF의 데이터를 Excel 스프레드시트로 변환하는 방법을 배우세요.

6. 서식 지우기

여러 사람이 시트에서 작업하거나 다른 위치에서 데이터를 복사하여 붙여넣은 경우 필요하지 않은 여분의 서식이 있을 수 있습니다. 여기에는 굵은 글꼴, 채우기 색상 또는 테두리가 포함될 수 있습니다. 다행히도 각 셀, 행 또는 열을 하나씩 변경할 필요는 없습니다. 클릭 한 번으로 시트의 서식을 지울 수 있습니다.

조건부 서식을 설정한 경우 여기에서 설명한 대로 서식을 지우면 해당 서식도 제거됩니다.

1. 제거할 서식이 포함된 셀 범위를 선택합니다. 우리는 텍스트에 굵게, 색상 및 기울임꼴이 적용되어 있으며 채우기 색상과 테두리가 있습니다.

1. “홈” 탭으로 이동하여 “지우기” 드롭다운 메뉴를 열고 “서식 지우기”를 선택합니다.

1. 해당 셀의 모든 서식이 사라지고 깨끗한 상태가 됩니다.

7. 텍스트를 열로 변환

다른 소스에서 데이터를 가져올 때 원하는 방식으로 배치되지 않을 수 있습니다. 데이터가 열의 개별 셀에 배치되어야 하는 행이 있을 수 있습니다. 몇 단계 만에 이 텍스트를 열로 변환하여 조작하기 쉽게 만들 수 있습니다.

1. 변환할 셀을 선택하고 “데이터” 탭으로 이동하여 데이터 도구 그룹에서 “텍스트를 열로”를 선택합니다.

1. 텍스트를 열로 변환 마법사가 열리면 현재 상태와 표시하려는 방식에 따라 데이터를 변환하기 위해 몇 단계를 진행합니다. “구분 기호” 또는 “고정 너비”를 선택하여 시작합니다. Excel은 데이터에 따라 추천을 제공하지만 가장 적합한 옵션을 선택할 수 있습니다. “다음”을 클릭합니다.

1. 첫 번째 단계에서 선택한 옵션에 따라 해당하는 두 번째 단계를 보게 됩니다. 예를 들어, 구분 기호 데이터를 선택하면 구분 기호를 선택할 수 있고, 고정 데이터를 선택하면 열 너비를 조정할 수 있습니다. “다음”을 클릭합니다.

1. 열의 데이터 형식을 선택하고, 변환된 데이터의 대상을 입력합니다. “마침”을 클릭합니다.

1. 텍스트가 열로 변환되어 작업할 준비가 됩니다.

알아두면 좋은 점: Grammarly와 Microsoft Editor 중 어떤 것을 사용할지 배우세요.

8. 오류 강조

Excel은 수식에서 발생하는 오류와 같은 셀의 오류를 잘 지적하지만, 긴 스프레드시트를 사용할 경우 이러한 오류를 놓칠 수 있습니다. 조건부 서식을 사용하면 오류를 강조 표시하여 더 쉽게 확인하고 수정할 수 있습니다.

1. 시트의 왼쪽 상단에 있는 “모두 선택”(삼각형) 버튼을 클릭하여 전체 시트를 선택합니다. 특정 셀만 확인하려면 해당 셀을 선택합니다.

1. “홈” 탭으로 이동하여 스타일 그룹에서 “조건부 서식” 드롭다운 메뉴를 열고 “새 규칙”을 선택합니다.

1. 새 서식 규칙 창 옵션에서 상단의 “포함된 셀만 서식 지정”을 선택하고 하단의 드롭다운 목록에서 “오류“를 선택합니다.

1. “서식” 버튼을 클릭하여 적용할 서식을 선택합니다. 셀에 색상을 채우거나 텍스트를 굵게 만들거나 어두운 테두리를 추가하는 등의 작업을 할 수 있습니다. 서식을 설정한 후 “확인”을 선택합니다.

1. 새 서식 규칙 창 하단에서 선택한 항목의 미리보기를 볼 수 있습니다. 규칙을 저장하고 적용하려면 “확인”을 클릭합니다.

1. 시트나 선택한 셀을 보면 오류가 강조 표시됩니다. 오류를 수정하면 서식이 사라지고 다음 오류로 넘어갈 수 있습니다.

자주 묻는 질문

Excel에서 맞춤법 오류를 확인하려면 어떻게 하나요?

다른 Microsoft Office 응용 프로그램과 마찬가지로 Excel에서도 맞춤법 검사를 사용할 수 있습니다. 이는 일반적인 오타나 맞춤법 오류를 찾는 데 유용합니다.

“검토” 탭으로 이동하여 리본의 교정 섹션에서 “맞춤법”을 선택합니다. 맞춤법 검사 상자가 나타나면 사전에 없는 스프레드시트의 단어를 볼 수 있습니다. 맞춤법을 무시하거나, 단어를 사전에 추가하거나, 수동으로 변경하거나, 제안 중 하나를 사용할 수 있습니다.

Excel 오류를 수정하는 방법을 어떻게 알 수 있나요?

시트에서 특정 오류가 보이면 셀을 선택하고 “수식” 탭으로 이동합니다. 수식 감사 그룹에서 “오류 검사”를 클릭합니다. 오류 검사 창이 나타나면 오류에 대한 간략한 세부정보가 표시됩니다. Microsoft 웹에서 오류에 대한 도움을 받거나, 계산 단계를 표시하거나, 오류를 무시하거나, 수식을 편집할 수 있습니다.

또한 Microsoft 지원 사이트의 손상된 수식을 피하는 방법 페이지를 방문하여 설명이 포함된 일반적인 수식 오류 목록을 확인할 수 있습니다.

Excel에서 행을 열로 또는 그 반대로 변환하려면 어떻게 하나요?

여기에서 논의된 텍스트를 열로 변환하는 기능과 유사하게, Excel에서 행을 열로 또는 열을 행으로 변환할 수 있습니다.

가장 간단한 방법은 전치 기능을 사용하여 복사 및 붙여넣기입니다. 또는 TRANSPOSE 함수를 사용할 수 있습니다. Excel에서 데이터를 전치하는 방법에 대한 전체 튜토리얼을 확인하여 두 가지 방법을 설명합니다.