[Excel] 엑셀 데이터 분석 기본

1. 엑셀의 붙여넣기

Ctrl + C(복사) / Ctrl + V(붙여넣기)를 하면 값, 서식, 수식 모두를 붙여넣기 때문에 주의하자.

따라서 Ctrl + V 보다 값만, 서식만, 수식만 붙여넣는 기능을 활용하도록 하자.

빠른실행도구모음에 등록해두면 편하게 사용할 수 있다.

 

2. 엑셀의 참조

상대참조: 함수를 복사/붙여넣기 할 때 참조된 셀이 함께 이동

절대참조: 함수를 복사/붙여넣기 해도 참조된 셀이 이동하지 않는 참조

혼합참조: 한 셀의 열 또는 행에만 절대 참조

 

3. 기초통계량 계산

합계	=SUM(범위)
중앙값	=MEDIAN(범위)
최대값	=MAX(범위)
최소값	=MIN(범위)
평균	=AVERAGE(범위)
최빈값	=MODE(범위)
분산	=VAR(범위)
표준편차	=STDEV(범위)

 

4. 피벗테이블 활용

피벗테이블 만드는건 어렵지 않다. 어떤 것을 만들고 싶은지를 명확히 하면 쉽게 만든다.

원하는 데이터 셋의 셀을 선택하고 [삽입]탭 - [피벗 테이블] - 필터/열/행/값 중 원하는 자료를 드래그

 

5. 피벗테이블을 활용한 대시보드 작성

 

직접 엑셀로 만들어본 대시보드 예시

 

6. IF 함수

=IF( 조건, 참일 때의 값, 거짓일 때의 값 )

예) =IF(B2 >= 90, "PASS", "FAIL")

예) =IF(C4 >= 90, "A", IF(C4 >= 80, "B", "C"))

위 예시처럼 IF 함수를 중첩하여 사용할 수도 있다.

 

7. VLOOKUP 함수

=VLOOKUP( 찾을 기준 데이터, 데이터 범위, 불러올 데이터 열 번호, FALSE(정확히 일치)/TRUE(근사치) )

 

8. MATCH 함수

=MATCH( 찾고 싶은 값, 값이 포함된 범위, [정확히 일치/근사치] )

VLOOKUP함수의 불어올 데이터 열 번호 인수를 넣는 곳에 MATCH함수의 결과값을 넣는 방식으로 활용할 수도 있다.

 

9. INDEX함수

특정 범위에서 행 번호, 열 번호로 원하는 데이터를 불러올 수 있다.

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

 

10. 날짜 데이터

계산: 종료일 - 시작일 = 기간 (단, 기간에서 종료일은 카운트되지 않으므로 주의)

DAYS/DATEDIF 함수 (함수도 기간에서 종료일은 카운트하지 않으므로 주의)

=DAYS(종료일, 시작일)

=DATEDIF(시작일, 종료일, ["D"/"M"/"Y"/"YM"/"MD"] )

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

 

11. 데이터 리본 메뉴에서 [데이터 분석] 활용

상관 분석: 함수를 활용해서 할 수도 있지만 데이터 범위를 잡아서 한 번에 손쉽게 계산할 수 있다.

분산 분석: F-검정으로 등분산성을 검정(P-value가 0.05보다 크면 등분산, 0.05보다 작으면 이분산)

    등분산 → Student's t-test  /  이분산 → Welch's t-test

회귀 분석: 단순 회귀 분석 / 다중 회귀 분석

 

12. 시계열 분석

FORECAST.ETS로 특정 시점의 미래 값을 예측

=FORECAST.ETS( 예측할 날짜, 실제 값들, 과거 날짜들 )