데이터 전처리

※ 데이터 전처리

: 데이터의 분석 목적과 방법에 맞게 데이터를 가공 or 처리하는 과정

: 데이터 분석 과정 중 가장 많은 시간과 비용이 필요한 과정(60~80%)

"Garbage in, Garbage out"

 

- 데이터 및 변수 형태 변환

- 변수 선정

- 결측치 및 이상치 처리

(상황에 따라 결측치가 있는 데이터를 뺄지, 다른 값으로 대체할 것인지 판단하고 처리)

- 데이터 분류

- 데이터 분리 및 결합

- 기타 데이터 가공 및 처리

 

 

■ 데이터 분류하기 ( IF 함수와 IF 중첩 )

● IF함수의 개념

부등호 / 등호 (<,>,=)를 이용하여 조건을 가정

조건에 만족하는 값과 만족하지 않는 값을 다르게 표시한다.

 

 

● IF함수의 목적

if 함수를 1번 사용할 때마다 데이터가 2가지로 구분되는데 이는 "분류"할 때 사용

ex) 매출에 따른 백화점 고객 등급 분류

ex) 영업 실적에 따른 우수 사원 분류

 

if함수를 2번 중첩 사용하면 데이터가 3가지로 구분
if함수를 3번 중첩 사용하면 데이터가 4가지로 구분
→ if함수를 N번 중첩 사용하면 데이터가 n+1가지로 구분된다

 

ex) 국어점수가 90점 이상이면 "합격", 그렇지 않으면 "불합격"으로 분류
= if(A1>=90, "합격", "불합격") (A1셀에 국어점수가 들어있다는 가정)

 

ex) 백화점의 고객 매출이 $5,000이상이면 "Platinum", $2,000이상이면, "Gold", 나머지는 "Silver"로 분류

= if(A2>=5000, "Platinum", if(A2>=2000, "Gold", "silver"))
→ if함수를 두 번 사용했으므로 데이터가 3개로 분류된다

 

 

■ 데이터 불러오기(1) - VLOOKUP 함수의 활용

→ 공통 기준 열을 기준으로 N번째에 있는 데이터를 찾아오는 함수

Vertical : 수직의 = "열"을 중심으로
Look up : ~을 찾다 = 데이터를 찾아오다

 

● VLOOKUP 함수를 많이 사용하는 이유?

- 방대하고 다양한 데이터에서 내가 원하는 데이터를 불러오는게 업무의 시작이기 때문!!

- HLOOKUP보다 VLOOKUP이 많이 사용되는 이유는 사람은 데이터를 "열"별로 정리하기 때문!!

(컬럼명 별로 데이터를 정리)

 

● VLOOKUP 함수의 조건

- 현재 작성 중인 표와 원래 데이터 간의 공통 기준 열이 있어야 한다 (ex. 사번 열)
- 내가 불러오고자 하는 데이터가 원래 데이터의 공통 기준 열 오른쪽에 있어야 함
- 공통 기준 열에 중복된 데이터가 없어야 함

 

● VLOOKUP 함수의 원리

: 원래 데이터의 공통 기준열을 1열이라고 가정, N번째 있는 열의 데이터를 불러온다

 

 

VLOOKUP(찾을 기준 데이터, 원래 데이터의 범위(공통 기준열부터 ~), 불러올 데이터의 열 번호(공통 기준열을 1열로 했을 때), 0 or 1)

0=FALSE(정확히 일치할 때만)

1=TRUE(근사치)

- 맨뒤의 인자는 생략 가능하지만, 디폴트 값이 1(근사치)이다. 주의!!

 

 

★ 중복 값이 있거나 조건이 2개인 경우?

 

VLOOKUP 함수를 사용할 때,

기준열에 중복 값이 있을 경우

가장 위에 있는 데이터의 정보만 불러온다

 

→ 중복 값이 없는 새로운 기준 열을 생성 → VLOOKUP 함수 사용

 

 

 

 

 

 

 

 

 

■ 데이터 불러오기(2) - MATCH & INDEX 함수

● MATCH 함수

내가 찾고 싶은 값이 한 행/열에서 몇 번째에 있는지를 숫자로 알려준다

→ 결과가 반드시 숫자(몇 번째인지)

 

MATCH(찾고싶은 값, 찾고싶은 값이 포함된 단일 열/또는 행 범위, 0 or 1)

0=FALSE(정확히 일치할 때만)

1=TRUE(근사치)

 

 

● INDEX 함수

특정 범위에서 행 번호와 열 번호로 원하는 데이터를 불러온다

 

INDEX(범위, 찾고싶은 데이터의 범위 내 행 번호, 찾고싶은 데이터의 범위 내 열 번호)

 

 

■ 날짜 데이터 처리하기 

→ 하이픈(-)을 사용해 날짜를 입력하면 서식이 자동으로 날짜로 변경된다  

(홈 탭 -> 표시형식)

 

● 날짜 데이터의 원리

하이픈(-)을 사용하여 날짜를 입력했는데도 4~5자리의 숫자로 나오는 이유는 서식이 일반으로 인식되었기 때문

- 엑셀은 1900-01-01을 1일이라고 인식하고 일수를 센다

→ 서식을 날짜로 바꿔주자! 

 

● 날짜 데이터의 계산

기간 = (종료일 - 시작일) + 1

(종료 당일은 기간에서 제외됨) 

ex) 2020-10-05 ~ 2020-10-15 → 뺄셈을 하면 10이지만, 15일까지 포함하면 기간은 11일이다.

 

● 날짜 데이터 함수

• DAYS(종료일, 시작일)
• DATEDIF(시작일, 종료일, "요약어")  

요약어 → D:일 수 / M:월 수 / Y:연 수 / YM:올해의 월 수 / MD: 이번달의 일 수  

*종료일을 기간에 포함시켜야 한다면?

→ 기간을 구하는 수식에 +1

• YEAR(날짜) : 연도 추출
• MONTH(날짜) : 월 추출
• DAY(날짜) : 일 추출

 

 

■ 텍스트 데이터 처리하기 

● FIND 함수

- 긴 텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력

-  띄어쓰기까지 포함하여 문자를 세며 대소문자를 구분 

 

FIND(찾을 텍스트, 긴 텍스트, 문자열을 찾기 시작할 위치(생략가능))

→ 숫자가 나오면 특정 단어가 포함, 오류가 나면 포함되어 있지 않은 것으로 해석 

 

● SEARCH 함수

- 긴 텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력

- 띄어쓰기까지 포함하여 문자를 세며 대소문자를 구분하지 않음

 

☆ COUNTIFS 함수와 같이 활용!!

ex) =COUNTIFS(FIND("존잼", A1), ">0")

→ FIND함수로 인해 "존잼"이라는 단어가 하나라도 있으면 0보다 크기 때문에 이를 활용

 

● LEFT 함수

LEFT(전체 텍스트, 불러올 문자열 수)

 

● RIGHT 함수

RIGHT(전체 텍스트, 불러올 문자열 수)

 

● MID 함수

MID(전체 텍스트, 불러올 문자열의 시작위치, 불러올 문자열 수)

 

 

■ 텍스트 나누기와 합치기 / 중복 데이터 제거하기

● 텍스트 나누기

- 1개의 셀에 있는 데이터를 2개 이상의 셀에 나누는 기능

(데이터 탭 → 텍스트 나누기 → 구분기호로 분리 → 구분기호 설정)

 

→ 텍스트 나누기를 시행하면 이전에 있던 1개의 셀이 나눠지기 때문에 원본을 유지하고 싶다면, 나눠지기 전의 셀을 복사하여 옆에 붙여놓은 후 텍스트 나누기를 시행하자

 

→ 다시 분류한 텍스트들을 이어주려면 "&" 연산자로 이어주면 된다.

(CONCAT함수가 있지만 "&" 연산자가 효율적...)

 

 

● 중복된 항목 제거하기

- 특정 열에서 중복되어 있는 데이터를 1개씩만 남기고 제거

(데이터 범위 지정 → 데이터 탭 → 중복된 항목 제거 → 기준열 지정하기)

 

→ 예를들어 '물류센터'의 열만 중복된 항목 제거하기를 한다면,

다른 열에 따로 복사한 후 실행할 경우 물류센터가 몇가지가 있는지 확인할 수 있다. (고유값을 파악 가능) 

그리고 기존의 표를 보관 가능