데이터 추출하기 2

데이터를 추출할 때, 구분자가 명확한 경우에는 앞서 사용한 텍스트나누기를 사용하면 되지만,
구분자가 명확하지 않은 경우는 Left, Right, Mid의 추출함수들과 find함수, len함수를 중첩해서 쓴다.
***find함수는 대상이 되는 셀이 2번째 인자로 온다.그리고 반환결과는 왼쪽에서부터 몇번째 자리수인지를 알려준다.
find에다가 구분자를 찾을 문자열로 넣으면, 구분자까지를 포함해서 추출하니, -1 +1 등을 해주면 된다.
***만약 구분자 뒷부분을 가져오고 싶다면 Right함수에다가 Len함수를 이용해 전체길이-(구분자까지길이)로 구하면된다.

정리)
구분자 직전까지 추출 : Left( 대상 셀,  Find("구분자", 대상 셀) -1)
구분자 뒷부분을 추출 : Right(대상 셀,  Len(대상셀) - FInd("구분자", 대상 셀))

image


실습 - 데이터 추출하기

image

위 데이터에서 출생년도를 구분해보자.
=LEFT(A2, 2)

image

출생월 을 가져오고 싶다면, 가운데이므로, mid함수를 써야한다.
=MID(A2, 3, 2)
image

생년월일을 추출해보자. 구분자 앞까지만 가져오면 되므로
left를 이용해서 왼쪽부터 6개를 뽑아와도 된다.(=LEFT(A2, 6)). 하지만 구분자를 이용하기 위해서
 left함수find함수를 충첩해서 사용해보자.
=LEFT(A2, FIND("-",A2)-1)

image

주민번호 뒷자리를 추출해보자. 뒤에서부터 7자리를 가져오면 되지만(=RIGHT(A2, 7))
뒤에서 부터 잘라와야하기 때문에, right 함수에다가  전체길이 -구분자까지의 길이를 가져오면 된다.
=RIGHT(A2, LEN(A2) - FIND("-",A2))

image
image


[텍스트 나누기]를 통해 데이터 변환하기

image

문자열 1을 ['1으로 입력]---> 숫자1로 바꾸는 것은
데이터-데이터나누기-[텍스트 나누기]를 통해 2단계에서 [공백]으로  설정, 3단계에서 [일반]을 누르면 문자열을 숫자로 바꿀 수 있다.
image
image
image
image


데이터변환 함수 정리

image

*숫자 형식은 문자열로 바꿀 때 꼭 들어가는 서식이다.
*datavalue는 활용할 수 없는 날짜 정보를 주므로, date나 month, day함수를 중첩해서 사용한다.


cf)셀서식은 데이터자체가 아니라 보이는 모양만 바꿔주는 것이다.
cf) 날짜를 계산할 때는, datevalue()를 쓰지만,
    텍스트로 날짜들을 구분(yyyy-mm-dd)하고 싶을 땐, 날자->문자열 + 숫자형식으로 바뀐 뒤->문자추출함수를 사용하는 것이 일반적이다.
cf) 실제 데이터의 양식을 보고 싶다면 [Ctrl + `]를 이용해서 확인한다.


실습


문자열로 적힌 숫자들을, 연산하기 위해서는 숫자데이터로 바꾸어야한다.

먼저 문자열로 적힌 숫자를 ---> 숫자로 바꾸는데, [텍스트나누기-공백-일반 ]을 활용해보자.

image
image


이제 똑같은 데이터를 변환함수를 이용해서 문자<-->숫자 데이터를 변환해보자.
문자열 숫자--> 숫자로 바꾸는 것은 value()함수.
image


이번에는 숫자데이터를 -> 문자열로 바꾸어보자.
주민번호나 생년월일, 전화번호로 같은 숫자정보들에 [문자 추출함수 len,left,mid,right,find]함수들을 사용하기 위해서 문자열로 바꾼다.
이 때 사용하는 함수는 text함수이다. 2번째 인자 숫자형식에다가는 눈에보이는대로의 형식을 #을 이용해 넣어준다. "###" 등을 넣을 수 있다.image
image


2000이 2,000으로 표시해야한다면, text()함수의 숫자형식에 "#,###" 으로 넣어준다.

image


3000.1 같은 경우 "####.#" 으로 입력해줘야 소수점이 표시된다.
image


날짜처럼 보이는 문자열을 ---> 날짜데이터로 변환하는 것이 datevalue()함수다.
하지만 날짜가 아닌 값(1990.1.1부터 계산한 날 수)을 반환하기 때문에, 셀서식[ctrl+1]로 날짜 서식을 줘야한다.
image
image
image


[텍스트나누기]를 이용해서 문자열 --> 날짜로 바꿔보자.
[텍스트나누기] - [공백] - [날짜]를 선택해서 바꿔주자.
image
image


이번에는 날짜 ---> 문자열로 바꿔보자.
숫자 ---> 문자열로 바꿔 추출함수를 사용할 때도, text()함수를 썼으나, 날짜도 동일하다.
숫자형식만 달라진다.( 숫자는 "#", 날짜 "yyyy-mm-dd")
=TEXT(E17, "yyyy-mm-dd")
image


중간 셀에 있는  [ 셀이름형식의 문자열 ] --->  [ 참조셀로서 값 반환]해주는 INDIRECT함수( 중간셀 )를 사용해보자
예를 들어,  중간 셀인, 연초록 J3셀에 있는 "문자열(H2)"를 실제로 찾아가서 참조하고 싶을 땐,
INDIRECT( J3)를 사용하면 실제 H2값이 참조되어 반환된다.
INDIRECT(  중간셀  )   --- 중간셀에 있는 문자열을--> 참조 셀로 읽어온다. --- 참조셀 값을 반환

=INDIRECT(J3) + 200

image
image
image

참조할 셀이름을 H5로 바꾸면, 그 값을 읽어온다.
image

+ Recent posts