텍스트 함수로 데이터 정리

여러 소스에서 얻은 데이터는 분석 할 준비가 된 형태가 아닙니다. 이 장에서는 분석을 위해 텍스트 형식의 데이터를 준비하는 방법을 이해합니다.

처음에는 데이터를 정리해야합니다. 데이터 정리에는 텍스트에서 원하지 않는 문자 제거가 포함됩니다. 다음으로 추가 분석에 필요한 형식으로 데이터를 구조화해야합니다. 다음과 같이 똑같이 할 수 있습니다.

• 텍스트 기능으로 필요한 텍스트 패턴 찾기.
• 텍스트에서 데이터 값 추출.
• 텍스트 함수로 데이터 서식 지정.
• 텍스트 함수로 데이터 작업을 실행합니다.

텍스트에서 원하지 않는 문자 제거

다른 응용 프로그램에서 데이터를 가져올 때 인쇄 할 수없는 문자 및 / 또는 초과 공백이있을 수 있습니다. 초과 공간은-

• 선행 공백 및 / 또는
• 단어 사이에 추가 공백.

이러한 데이터를 정렬하거나 분석하면 잘못된 결과가 나타납니다.

다음 예를 고려하십시오-

이것은 제품 ID, 제품 설명 및 가격을 포함하는 제품 정보에서 얻은 원시 데이터입니다. 문자 "|" 각 행의 필드를 구분합니다.

이 데이터를 Excel 워크 시트로 가져 오면 다음과 같이 보입니다.

보시다시피 전체 데이터는 단일 열에 있습니다. 데이터 분석을 수행하려면이 데이터를 구조화해야합니다. 그러나 처음에는 데이터를 정리해야합니다.

데이터에있을 수있는 인쇄 할 수없는 문자와 초과 공백을 제거해야합니다. 이를 위해 CLEAN 기능과 TRIM 기능을 사용할 수 있습니다.

S. 아니.	기능 및 설명
1.	CLEAN 텍스트에서 인쇄 할 수없는 모든 문자를 제거합니다.
2.	TRIM 텍스트에서 공백을 제거합니다.

• 셀 C3 – C11을 선택합니다.
• = TRIM (CLEAN (B3))을 입력 한 다음 CTRL + Enter를 누릅니다.

수식은 C3 – C11 셀에 채워집니다.

결과는 다음과 같습니다.

텍스트 기능으로 필요한 텍스트 패턴 찾기

데이터를 구조화하려면 데이터 값을 추출 할 수있는 특정 텍스트 패턴 일치를 수행해야 할 수 있습니다. 이 목적에 유용한 텍스트 함수 중 일부는-

S. 아니.	기능 및 설명
1.	EXACT 두 텍스트 값이 동일한 지 확인합니다.
2.	FIND 다른 텍스트 값 (대소 문자 구분)을 찾습니다.
삼.	SEARCH 다른 텍스트 값을 찾습니다 (대소 문자 구분 안 함).

텍스트에서 데이터 값 추출

동일하게 구조화하려면 텍스트에서 필요한 데이터를 추출해야합니다. 위의 예에서 데이터를 ProductID, Product_Description 및 Price의 세 열에 배치해야합니다.

다음 방법 중 하나로 데이터를 추출 할 수 있습니다.

• 텍스트를 열로 변환 마법사를 사용하여 데이터 값 추출
• 텍스트 함수로 데이터 값 추출
• 빠른 채우기로 데이터 값 추출

텍스트를 열로 변환 마법사를 사용하여 데이터 값 추출

당신은 사용할 수 있습니다 Convert Text to Columns Wizard 필드가있는 경우 데이터 값을 Excel 열로 추출하려면-

• 문자로 구분 또는
• 각 필드 사이에 공백이있는 열로 정렬됩니다.

위의 예에서 필드는 문자 "|"로 구분됩니다. 따라서 다음을 사용할 수 있습니다.Convert Text to Columns 마법사.

• 데이터를 선택하십시오.

• 같은 위치에 값을 복사하여 붙여 넣습니다. 그렇지 않으면,Convert Text to Columns 데이터 자체가 아닌 함수를 입력으로 사용합니다.

• 데이터를 선택하십시오.

• 클릭 Text to Columns 에 Data Tools 아래 그룹 Data 리본의 탭.

Step 1 − 텍스트를 열로 변환 마법사-1/3 단계가 나타납니다.

• 구분을 선택합니다.
• 다음을 클릭하십시오.

Step 2 − 텍스트를 열로 변환 마법사-2/3 단계가 나타납니다.

• 아래에 Delimiters, 고르다 Other.

• 옆에있는 상자에서 Other, 문자 입력 |

• 딸깍 하는 소리 Next.

Step 3 − 텍스트를 열로 변환 마법사-3 단계 중 3 단계가 나타납니다.

이 화면에서 마법사에서 데이터의 각 열을 선택하고 해당 열의 형식을 설정할 수 있습니다.

• 에 대한 Destination에서 D3 셀을 선택합니다.

• 클릭 할 수 있습니다. Advanced, 설정 Decimal Separator 과 Thousands Separator 에 Advanced Text Import Settings 나타나는 대화 상자.

• 딸깍 하는 소리 Finish.

열로 변환 된 데이터는 D, E 및 F의 세 열에 나타납니다.

• 열 헤더의 이름을 ProductID, Product_Description 및 Price로 지정합니다.

텍스트 함수로 데이터 값 추출

데이터의 필드가 문자로 구분되지 않고 각 필드 사이에 공백이있는 열에 정렬되어 있지 않다고 가정하면 텍스트 함수를 사용하여 데이터 값을 추출 할 수 있습니다. 필드가 구분 된 경우에도 텍스트 함수를 사용하여 데이터를 추출 할 수 있습니다.

이 목적에 유용한 일부 텍스트 기능은 다음과 같습니다.

S. 아니.	기능 및 설명
1.	LEFT 텍스트 값에서 가장 왼쪽 문자를 반환합니다.
2.	RIGHT 텍스트 값에서 맨 오른쪽 문자를 반환합니다.
삼.	MID 지정한 위치에서 시작하는 텍스트 문자열에서 특정 수의 문자를 반환합니다.
4.	LEN 텍스트 문자열의 문자 수를 반환합니다.

또한 보유한 데이터에 따라 이러한 텍스트 함수 중 둘 이상을 결합하여 필요한 데이터 값을 추출 할 수 있습니다. 예를 들어, LEFT, RIGHT 및 VALUE 함수의 조합을 사용하거나 FIND, LEFT, LEN 및 MID 함수의 조합을 사용합니다.

위의 예에서

• 처음에 남은 모든 문자 | ProductID라는 이름을 지정합니다.

• 두 번째까지 모든 문자 | Price라는 이름을 지정하십시오.

• 첫 번째 사이에있는 모든 문자 | 두 번째 | Product_Description이라는 이름을 지정합니다.

• 각각 | 앞뒤에 공백이 있습니다.

이 정보를 관찰하면 다음 단계에 따라 데이터 값을 추출 할 수 있습니다.

• 첫 번째 위치 찾기 | -First | Position

  • FIND 기능을 사용할 수 있습니다.

• 두 번째 위치 찾기 | -Second | Position

  • FIND 기능을 다시 사용할 수 있습니다.

• 시작 (First | Position – 2) 텍스트의 문자는 ProductID를 제공합니다.

  • LEFT 기능을 사용할 수 있습니다

• (First | Position + 2) ~ (Second | Position -2) 텍스트의 문자는 Product_Description을 제공합니다.

  • MID 기능을 사용할 수 있습니다.

• (Second | Position + 2) 텍스트의 끝 문자에 가격 제공

  • RIGHT 기능을 사용할 수 있습니다.

결과는 다음과 같습니다.

가격 열의 값이 텍스트 값임을 알 수 있습니다. 이러한 값에 대해 계산을 수행하려면 해당 셀의 서식을 지정해야합니다. 텍스트 서식 지정을 이해하려면 아래 제공된 섹션을 참조하십시오.

빠른 채우기로 데이터 값 추출

Excel 사용 Flash Fill텍스트에서 데이터 값을 추출하는 또 다른 방법입니다. 그러나 이것은 Excel이 데이터에서 패턴을 찾을 수있는 경우에만 작동합니다.

Step 1 − 데이터 옆에 ProductID, Product_Description 및 Price에 대한 세 개의 열을 생성합니다.

Step 2 − B3에서 C3, D3 및 E3 값을 복사하여 붙여 넣습니다.

Step 3 − C3 셀을 선택하고 클릭 Flash Fill 에 Data Tools 에 그룹 Data탭. ProductID의 모든 값이 채워집니다.

Step 4− Product_Description 및 Price에 대해 위의 주어진 단계를 반복합니다. 데이터가 채워집니다.

텍스트 함수로 데이터 서식 지정

Excel에는 텍스트가 포함 된 데이터의 서식을 지정하는 데 사용할 수있는 몇 가지 기본 제공 텍스트 함수가 있습니다. 여기에는-

Functions that format the Text as per your need −

S. 아니.	기능 및 설명
1.	LOWER 텍스트를 소문자로 변환

S. 아니.	기능 및 설명
1.	UPPER 텍스트를 대문자로 변환
2.	PROPER 텍스트 값의 각 단어에서 첫 글자를 대문자로 표시

Functions that convert and/or format the Numbers as Text −

S. 아니.	기능 및 설명
1.	DOLLAR $ (달러) 통화 형식을 사용하여 숫자를 텍스트로 변환합니다.
2.	FIXED 숫자를 고정 된 소수 자릿수가있는 텍스트로 포맷합니다.
삼.	TEXT 숫자 형식을 지정하고 텍스트로 변환합니다.

Functions that convert the Text to Numbers −

S. 아니.	기능 및 설명
1.	VALUE 텍스트 인수를 숫자로 변환

Executing Data Operations with the Text Functions

데이터에 대해 특정 텍스트 작업을 수행해야 할 수도 있습니다. 예를 들어 직원의 로그인 ID가 형식 변경에 따라 조직에서 새 형식으로 변경되면 텍스트 교체를 수행해야 할 수 있습니다.

다음 텍스트 함수는 텍스트를 포함하는 데이터에 텍스트 작업을 수행하는 데 도움이-

S. 아니.	기능 및 설명
1.	REPLACE 텍스트 내의 문자를 대체합니다.
2.	SUBSTITUTE 텍스트 문자열에서 이전 텍스트를 새 텍스트로 대체합니다.
삼.	CONCATENATE 여러 텍스트 항목을 하나의 텍스트 항목으로 결합
4.	CONCAT 여러 범위 및 / 또는 문자열의 텍스트를 결합하지만 구분 기호 또는 IgnoreEmpty 인수를 제공하지 않습니다.
5.	TEXTJOIN 여러 범위 및 / 또는 문자열의 텍스트를 결합하고 결합 될 각 텍스트 값 사이에 지정하는 구분 기호를 포함합니다. 구분 기호가 빈 텍스트 문자열 인 경우이 함수는 범위를 효과적으로 연결합니다.
6.	REPT 주어진 횟수만큼 텍스트를 반복합니다.