3. 데이터분석과 엑셀 : 데이터 전처리 , IF, VLOOKUP

데이터 전(前)처리

• 데이터의 분석 목적과 방법에 맞게 데이터를 가공 또는 처리하는 과정
• 데이터 및 변수 형태 변환
• 변수선정
• 결측치 및 이상치 처리
• 데이터 분류 (카테고리)
• 데이터 분리 및 결합
• 기타 데이터 가공 및 처리

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

Garbage in , Garbage out

 

IF 함수와 중첩

부등호/등호 (>,<,=)를 활용해 조건을 가정하고 조건에 만족하는 / 만족하지않는 값을 다르게 표시

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

-> IF 함수를 N번 중첩 사용하면 데이터가 N+1가지로 구분

IF 사용 및 중첩

값을 직접 넣을수도 있으나, 엑셀의 셀을 참조하는 방식으로 사용할 수 도

=IF(E5>=N5, M5, IF(E5>=N6, M6, M7))

기준 연령에 맞춰서, 구분을 표시하도록

=IF(E5>=$N$5, $M$5, IF(E5>=$N$6, $M$6, $M$7))

데이터 참조할 건 절대참조 (구분 & 기준연령)

 

인원수를 구할 때, COUNTIF 등 사용. (지정된 범위내에서 조건에 맞는 셀의 개수를 구한다)

=COUNTIF(F:F, $M$5)

 

VLOOKUP 함수

공통 기준 열을 기준으로 n번째 있는 데이터를 찾아 오는 함수. Vertical + Look up (~을 찾다)

• 방대하고 다양한 데이터에서 내가 원하는 데이터를 불러오는게 업무의 시작
• HLOOKUP보다 VOLLUP이 많이 사용되는 이유는 인간이 데이터를 "열"별로 정리하기에

VLOOKUP 함수의 조건

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

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

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

 

MATCH 함수

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

=MATCH( 내가 찾고 싶은 값 , 찾고 싶은 값이 포함된 단일 열/행 범위 [, 정확히 일치하는 값 or 근사치 여부])