데이터 전처리

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

• 데이터 및 변수 형태 변환
• 변수 선정
• 결측치 및 이상치 처리
• 데이터 분류(카테고리 化)
• 데이터 분리 및 결합
• 기타 데이터 가공 및 처리

데이터 전처리는 데이터 분석 과정 중 가장 중요한 과정이다. 엑셀에서는 함수와 엑셀 자체의 기능을 통해 데이터 전처리를 실시할 수 있는데 이번 포스트에선 데이터 전처리 관련 함수를 알아보고자 한다.

1. IF 함수

if 함수는 부등호/등호(>, <, =)를 활용해 가정을 설정하고 데이터를 가정에 만족하는 값과 만족하지 않는 값으로 분류하는 함수이다.
기본 형태는 다음과 같다.

=IF(logical_test, [value_if_true], [value_if_false])

if 함수는 2번 중첩 사용하게 되면 데이터가 3가지로 구분되고 3번 중첩 사용하게 되면 4가지로 구분되며 n번 중첩 사용하게 되면 n+1가지로 구분된다.

2. VLOOKUP, HLOOKUP 함수

VLOOKUP 함수는 공통 열을 기준으로 n번째 있는 데이터를 불러 오는 함수이고, HLOOKUP 함수는 공통 행을 기준으로 n번째 있는 데이터를 불러 오는 함수이며, 일반적으로 HLOOKUP 함수보다 VLOOKUP 함수를 더 많이 사용한다.
VLOOKUP 함수는 방대하고 다양한 데이터에서 내가 원하는 데이터를 불러오는 함수로서 데이터 전처리 뿐만 아니라 일반적으로 엑셀을 다룰때도 많이 사용하는 함수이다.
기본 형태는 다음과 같다.

=VLOOKUP(Lookup_Value, Table_array, Col_index_num, Range_lookup)
(조회하려는 항목, 찾고자 하는 위치, 반환할 값이 포함된 범위의 열 번호, 대략적 또는 정확히 일치 반환 - 1/TRUE 또는 0/FALSE로 표시).

첫 번째 인수는 조회하려는 항목 즉, 기준 데이터를 말하며, 두 번째 인수인 찾고자 하는 위치는 데이터의 범위를 뜻한다. 세 번째 인수는 데이터의 범위에서 찾아올 데이터가 몇 번째 열에 있는지를 말하며, 마지막 Range_lookup은 0일 때 정확히 일치할 때만 데이터를 불러오고, 1일 때는 근사치여도 데이터를 불러오게 된다. 보통은 0일 때를 많이 사용한다.

여기서 세 번째 인수인 열 번호를 불러올 때 데이터 양이나 속성이 많으면 일일이 열 번호를 알아내기 힘들기 때문에 열 번호를 참조하여 사용하거나 MATCH 함수 대입하는 방법을 사용한다.

2-1. MATCH 함수

MATCH 함수는 내가 찾고 싶은 값이 한 행/열에서 몇 번째에 있는지를 숫자로 알려주는 함수로 기본 형태는 다음과 같다.

=MATCH(lookup_value, lookup_array, [match_type])
(match_type은 [1: 보다 작음 / 0 : 정확히 일치 / -1 : 보다 큼]로 표시한다.) 

2-2. INDEX 함수

INDEX 함수는 특정 범위에서 행 번호와 열 번호로 원하는 데이터를 불러오는 함수로 MATCH 함수와 함께 VLOOKUP 함수에서 자주 사용하는 함수이다.

=INDEX(array, row_num, [column_num])
(데이터의 전체 범위, 찾고 싶은 데이터의 범위 내 행 번호, 찾고 싶은 데이터의 범위 내 열 번호)

3. COUNT 함수

COUNT 함수는 특정 범위에서 “숫자 데이터”가 들어가 있는 셀의 개수 파악하는 함수로 COUNT 함수 이외에 다양한 COUNT 함수들이 있다.

3-1. COUNTA 함수

특정 범위에서 데이터가 들어가 있는 셀(비어 있지 않은 셀)의 개수 파악

3-2. COUNTBLANK 함수

특정 범위에서 비어 있는 셀의 개수 파악

3-3. COUNTIF 함수

특정 범위에서 하나의 조건을 만족하는 셀의 개수 파악

★3-4. COUNTIFS 함수

특정 범위에서 2개 이상의 조건을 동시에 만족하는 셀의 개수 파악
COUNTIFS 함수는 여러 개의 조건일 때도 사용하지만 조건이 1개 일때도 사용 가능하기 때문에 COUNTIFS 함수를 생활화 하는 것이 편리하다.
COUNTIFS 함수의 기본 형태는 다음과 같다.

=COUNTIFS([데이터의 개수를 파악할 셀 범위1], [개수를 셀 데이터의 조건1], [데이터의 개수를
파악할 셀 범위2], [개수를 셀 데이터의 조건2], ... ,)

4. SUMIF 함수 & SUMIFS 함수

SUMIF 함수는 특정 조건을 만족하는 데이터의 합계를 계산하는 함수로 SUMIFS 함수를 사용하면 2개 이상의 조건을 사용할 수 있다. SUMIF 함수 & SUMIFS 함수 또한 COUNTIF 함수 & COUNTIFS 함수와 같이 SUMIFS 함수가 조건이 1개 일때도 사용 가능하기 때문에 SUMIFS 함수를 생활화 하는 것이 편리하다.
SUMIFS 함수의 기본 형태는 다음과 같다.

=SUMIFS([더할 값들의 범위], [더할 조건 범위 1], [조건 1], [더할 조건 범위 2], [조건 2], ...)

5. FIND 함수

FIND 함수는 긴 텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력하는 함수로 텍스트에 찾고자 하는 단어가 텍스트에 포함하고 있는지를 확인할 때 사용한다. FIND 함수의 결괏값은 숫자로, 찾고자 하는 단어가 텍스트에 포함되어 있지 않으면 #VALUE 에러가 출력되기 때문에 IFERROR 함수와 같이 사용하기도 한다.
FIND 함수의 기본 형태는 다음과 같다.

=FIND([찾을 텍스트], [긴 텍스트], [문자열을 찾기 시작할 위치])

6. LEFT & RIGHT 함수

LEFT & RIGHT 함수는 텍스트의 가장 왼쪽/오른쪽부터 원하는 문자열까지 추출하는 함수이다.
두 번째 인수에 불러올 문자열의 수를 입력하면 텍스트에서 왼쪽/오른쪽부터 그 숫자만큼 텍스트를 추출한다.
LEFT & RIGHT 함수의 기본 형태는 다음과 같다.

=LEFT / RIGHT([전체 텍스트], [불러올 문자열 수])

6-1. MID 함수

MID 함수는 텍스트의 중간 시작 위치부터 원하는 문자열까지 추출하는 함수로 추출하고자 하는 문자열이 처음 또는 마지막이여야 하는 LEFT & RIGHT 함수에 단점을 보완하는 함수이다.
MID 함수의 기본 형태는 다음과 같다.

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