철자가 틀린 단어, 완고한 후행 공백, 원치 않는 접두사, 부적절한 경우 및 인쇄되지 않은 문자는 나쁜 첫 인상을 줍니다. 데이터가 더러워질 수 있는 방법의 전체 목록도 아닙니다. 소매를 롤업합니다. 이제 Microsoft Excel을 사용하여 워크시트를 몇 가지 주요 봄 청소할 때입니다.
데이터베이스, 텍스트 파일 또는 웹 페이지와 같은 외부 데이터 원본에서 가져오는 데이터의 형식과 서식을 제어할 수 없는 경우도 있습니다. 데이터를 분석하려면 대체로 정리 작업이 필요합니다. 다행히, Excel에는 원하는 서식으로 데이터를 정확하게 가져오기 위한 다양한 기능이 있습니다. 때로는 작업이 간단하고 해당 작업을 수행하는 특정 기능이 있습니다. 예를 들어 맞춤법 검사를 사용하여 메모 또는 설명이 포함된 열에서 철자가 틀린 단어를 쉽게 정리할 수 있습니다. 또는 중복 행을 제거하려는 경우 중복 항목 제거 대화 상자를 사용하여 빠르게 제거할 수 있습니다.
수식을 통해 가져온 값을 새 값으로 변환하여 하나 이상의 열을 조작해야 하는 경우도 있습니다. 예를 들어 뒤에 오는 공백을 제거하려는 경우 새 열을 만들어 수식으로 데이터를 정리하고, 새 열을 채우고, 새 열의 수식을 값으로 변환한 다음 원래 열을 제거하여 데이터를 정리할 수 있습니다.
데이터를 정리하는 기본 단계는 다음과 같습니다.
-
외부 데이터 원본에서 데이터를 가져옵니다.
-
별도 통합 문서에서 원래 데이터의 백업 복사본을 만듭니다.
-
데이터가 각 열의 유사한 데이터, 모든 열 및 행이 표시되고 범위 내에 빈 행이 없는 행 및 열의 테이블 형식인지 확인합니다. 최상의 결과를 보려면 Excel 표를 사용합니다.
-
맞춤법 검사, 찾기 및 바꾸기 대화 상자 사용 등 열 조작이 필요 없는 작업을 먼저 실행합니다.
-
다음으로, 열 조작이 필요한 작업을 실행합니다. 열을 조작하는 일반 단계는 다음과 같습니다.
-
정리가 필요한 원래 열(A) 옆에 새 열(B)을 삽입합니다.
-
새 열(B)의 맨 위에 데이터를 변환할 수식을 추가합니다.
-
새 열(B)에 수식을 채웁니다. Excel 표에서 계산된 열은 값이 채워진 상태로 자동으로 생성됩니다.
-
새 열(B)을 선택하고 복사한 다음 새 열(B)에 값으로 붙여넣습니다.
-
새 열을 B에서 A로 변환하는 원래 열(A)을 제거합니다.
-
동일한 데이터 원본을 주기적으로 정리하려면 매크로를 기록하거나 코드를 작성하여 전체 프로세스를 자동화하는 것이 좋습니다. 또한 직접 프로세스를 자동화할 시간이나 리소스가 없는 경우 타사 공급업체 섹션에 나열된, 타사 공급업체가 작성한 다양한 외부 추가 기능을 고려할 수도 있습니다.
|
추가 정보 |
설명 |
|---|---|
|
채우기 명령을 사용하는 방법을 보여 줍니다. |
|
|
테이블 만들기 및 서식 지정행 및 열을 추가하거나 제거하여 테이블 크기 조정Excel 테이블에서 계산 열 사용 |
Excel 표를 만들고 열이나 계산된 열을 추가 또는 삭제하는 방법을 보여 줍니다. |
|
매크로를 사용하여 반복 작업을 자동화하는 여러 가지 방법을 보여 줍니다. |
맞춤법 검사를 사용하여 철자가 틀린 단어를 찾을 뿐 아니라 제품, 회사 이름 등 일관성 있게 사용되지 않은 값을 사용자 지정 사전에 추가하여 찾을 수도 있습니다.
|
추가 정보 |
설명 |
|---|---|
|
워크시트에서 철자가 틀린 단어를 수정하는 방법을 보여 줍니다. |
|
|
사용자 지정 사전을 사용하는 방법을 설명합니다. |
중복 행은 데이터를 가져올 때 일반적으로 발생하는 문제입니다. 중복 값을 제거하기 전에 먼저 고유 값을 필터링하여 원하는 결과가 맞는지 확인하는 것이 좋습니다.
|
추가 정보 |
설명 |
|---|---|
|
고유 행을 필터링하는 방법과 중복 행을 제거하는 방법 등 밀접한 관련이 있는 두 절차를 보여 줍니다. |
레이블과 콜론 및 공백 같은 일반적인 선행 문자열 또는 오래되었거나 불필요한 문자열 끝에 있는 삽입구 같은 접미사를 제거하는 것이 좋습니다. 이렇게 하려면 해당 텍스트 인스턴스를 찾은 다음 텍스트를 제거하거나 다른 텍스트로 대체합니다.
|
추가 정보 |
설명 |
|---|---|
|
찾기 명령과 여러 함수를 사용하여 텍스트를 찾는 방법을 보여 줍니다. |
|
|
바꾸기 명령과 여러 함수를 사용하여 텍스트를 제거하는 방법을 보여 줍니다. |
|
|
찾기 및 바꾸기 대화 상자를 사용하는 방법을 보여 줍니다. |
|
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
문자열 내에서 하위 문자열 찾기 및 바꾸기, 문자열의 일부 추출, 문자열의 길이 확인 등 다양한 문자열 조작 작업을 실행하는 데 사용할 수 있는 함수입니다. |
특히 텍스트의 대/소문자와 관련된 경우 텍스트의 대/소문자가 혼합된 경우가 있습니다. 세 가지 대/소문자 함수 중 하나 이상을 사용하여 텍스트를 소문자로 변환(예: 전자 메일 주소)하거나, 대문자로 변환(예: 제품 코드)하거나, 적절한 대/소문자로 변환(예: 이름, 책 제목)할 수 있습니다.
|
추가 정보 |
설명 |
|---|---|
|
세 가지 대/소문자 함수를 사용하는 방법을 보여 줍니다. |
|
|
텍스트 문자열의 모든 대문자를 소문자로 변환합니다. |
|
|
단어의 첫째 문자와 영문자가 아닌 문자 다음에 오는 영문자를 대문자로 변환합니다. 나머지 문자들은 소문자로 변환합니다. |
|
|
텍스트를 대문자로 변환합니다. |
텍스트 값에 선행, 후행 또는 여러 개의 포함된 공백 문자(유니코드 문자 집합 값 32와 160) 또는 인쇄되지 않는 문자(유니코드 문자 집합 값 0-31, 127, 129, 141, 143, 144, 157)가 있는 경우도 있습니다. 이러한 문자로 인해 정렬, 필터링 또는 검색 시 예기치 않은 결과가 나올 수 있습니다. 예를 들어 외부 데이터 원본에서 사용자가 실수로 공백 문자를 추가하여 입력 오류를 만들거나 외부 원본에서 가져온 텍스트 데이터가 텍스트에 포함된 인쇄되지 않는 문자를 포함할 수 있습니다. 이러한 문자는 쉽게 인식되지 않으므로 예기치 않은 결과를 이해하기 어려울 수 있습니다. 원치 않는 문자를 제거하려면 TRIM, CLEAN, SUBSTITUTE 함수 조합을 사용할 수 있습니다.
|
추가 정보 |
설명 |
|---|---|
|
텍스트 문자열의 첫째 문자를 나타내는 코드값을 반환합니다. |
|
|
텍스트에서 7비트 ASCII 코드(값 0-31)의 인쇄되지 않는 처음 32자를 제거합니다. |
|
|
텍스트에서 7비트 ASCII 공백 문자(값 32)를 제거합니다. |
|
|
SUBSTITUTE 함수를 사용하여 상위 값 유니코드 문자(값 127, 129, 141, 143, 144, 157, 160)를 TRIM 및 CLEAN 함수가 설계된 7비트 ASCII 문자로 바꿀 수 있습니다. |
데이터를 정리해야 할 수도 있는 두 가지 주요 숫자 관련 문제는 실수로 숫자를 텍스트로 가져온 경우와 음수 기호를 조직의 표준으로 변경해야 하는 경우입니다.
|
추가 정보 |
설명 |
|---|---|
|
계산할 때 문제가 발생하거나 혼동되는 정렬 순서를 생성할 수 있는, 텍스트로 서식이 지정되고 셀에 저장된 숫자를 숫자 서식으로 변환하는 방법을 보여 줍니다. |
|
|
숫자를 텍스트 서식으로 변환하고 통화 기호를 적용합니다. |
|
|
값을 특정 숫자 서식의 텍스트로 변환합니다. |
|
|
숫자를 지정된 자릿수에서 반올림하고, 마침표와 쉼표를 사용하여 10진수 서식으로 지정한 다음, 결과를 텍스트로 반환합니다. |
|
|
숫자를 나타내는 텍스트 문자열을 숫자로 변환합니다. |
다양한 날짜 서식이 있고 이러한 서식이 번호가 매겨진 부품 코드 또는 슬래시 기호나 하이픈을 포함하는 다른 문자열과 혼동될 수 있으므로, 날짜와 시간을 자주 변환하고 서식을 다시 지정해야 합니다.
|
추가 정보 |
설명 |
|---|---|
|
날짜 체계가 Office Excel에서 작동하는 방식을 설명합니다. |
|
|
다양한 시간 단위 간에 변환하는 방법을 보여 줍니다. |
|
|
계산할 때 문제가 발생하거나 혼동되는 정렬 순서를 생성할 수 있는, 텍스트로 서식이 지정되고 셀에 저장된 날짜를 날짜 서식으로 변환하는 방법을 보여 줍니다. |
|
|
특정 날짜를 나타내는 순차적 일련 번호를 반환합니다. 함수가 입력되기 전에 셀 서식이 일반인 경우 결과 서식이 날짜로 지정됩니다. |
|
|
텍스트로 표시된 날짜를 일련 번호로 변환합니다. |
|
|
특정 시간에 대한 실수를 반환합니다. 함수가 입력되기 전의 셀 서식이 일반이었으면 결과값은 날짜 서식으로 지정됩니다. |
|
|
텍스트 문자열이 나타내는 시간에 대한 소수를 반환합니다. 이 소수는 0에서 0.99999999 사이의 값이며 이 값은 0:00:00(오전 12:00:00)에서 23:59:59(오후 11:59:59)까지의 시간을 나타냅니다. |
외부 데이터 원본에서 데이터를 가져온 후 일반적인 작업은 둘 이상의 열을 하나로 병합하거나 하나의 열을 둘 이상의 열로 분할하는 것입니다. 예를 들어 전체 이름이 포함된 열을 이름과 성으로 분할할 수 있습니다. 또는 주소 필드가 포함된 열을 별도 주소, 시, 지역, 우편 번호 열로 분할할 수 있습니다. 그 반대도 마찬가지일 수 있습니다. 이름 및 성 열을 전체 이름 열로 병합하거나 개별 주소 열을 하나의 열로 결합할 수 있습니다. 하나의 열로 병합하거나 여러 열로 분할해야 할 수 있는 일반적인 추가 값으로 제품 코드, 파일 경로, IP(인터넷 프로토콜) 주소 등이 있습니다.
|
추가 정보 |
설명 |
|---|---|
|
둘 이상의 열 값을 결합하는 일반적인 예를 보여 줍니다. |
|
|
이 마법사를 사용하여 다양한 일반적인 구분 기호를 기준으로 열을 분할하는 방법을 보여 줍니다. |
|
|
LEFT, MID, RIGHT, SEARCH, LEN 함수를 사용하여 이름 열을 둘 이상의 열로 분할하는 방법을 보여 줍니다. |
|
|
CONCATENATE 함수, &(앰퍼샌드) 연산자 및 텍스트를 열로 변환 마법사를 사용하는 방법을 보여 줍니다. |
|
|
셀 병합, 전체 병합 및 병합하고 가운데 맞춤 명령을 사용하는 방법을 보여 줍니다. |
|
|
두 개 이상의 텍스트 문자열을 하나의 텍스트 문자열로 연결합니다. |
Office Excel에 있는 대부분의 분석 및 서식 기능은 단일 2차원 플랫 테이블에 데이터가 있다고 가정합니다. 때로는 행을 열로 전환하고 열을 행으로 전환할 수 있습니다. 데이터가 표 형식으로 구성되어 있지 않고, 표가 아닌 형식에서 표 형식으로 데이터를 변환하는 방법이 필요한 경우도 있습니다.
|
추가 정보 |
설명 |
|---|---|
|
셀의 세로 범위를 가로 범위로 반환하거나 그 반대로 실행합니다. |
|
때로는 둘 이상의 표를 조인할 때 데이터베이스 관리자가 Office Excel을 사용하여 일치 오류를 찾아서 수정합니다. 예를 들어 서로 다른 워크시트의 두 표를 조정하여 두 표의 모든 레코드를 확인하거나 표를 비교하고 일치하지 않는 행을 찾을 수 있습니다.
|
추가 정보 |
설명 |
|---|---|
|
조회 함수를 사용하여 데이터를 조회하는 일반적인 방법을 보여 줍니다. |
|
|
한 행 또는 한 열로 이루어진 범위나 배열의 값을 반환합니다. LOOKUP 함수에는 벡터형과 배열형의 두 가지 구문 양식이 있습니다. |
|
|
표의 첫 행 또는 값의 배열에서 값을 검색한 다음 표나 배열에 지정한 행에서 동일한 열의 값을 반환합니다. |
|
|
표 배열의 첫 번째 열에서 값을 검색하고 표 배열의 다른 열에서 동일한 행의 값을 반환합니다. |
|
|
표나 범위에서 값 또는 값에 대한 참조를 반환합니다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있습니다. |
|
|
배열에서 지정된 순서의 지정된 값과 일치하는 항목의 상대 위치를 반환합니다. 항목 자체가 아니라 범위의 항목 위치가 필요한 경우 LOOKUP 함수 중 하나가 아니라 MATCH를 사용합니다. |
|
|
셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환합니다. 반환되는 참조는 단일 셀 또는 셀 범위일 수 있습니다. 반환할 행 및 열 수를 지정할 수 있습니다. |
다음은 다양한 방식으로 데이터를 정리하는 데 사용되는 제품을 보유한 타사 공급자의 일부 목록입니다.
참고: Microsoft는 타사 제품을 지원하지 않습니다.
|
공급자 |
제품 |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
AddinTools |
|
|
WinPure |
