JANGUN


엑셀 데이터 분석




목차

엑셀 통계 함수
1. 통계학의 적용 과정
2. 엑셀 그래프의 종류
3. 기술통계량
4. 표본분포
5. 추정
6. 가설검정 (유의성 검증)
7. 범주형 데이터 분석
8. 상관분석
9. 회귀분석
10. 분산 분석


엑셀 통계 함수

1. 엑셀 기능
- 정렬 : 오름차순, 내림차순
- 필터 : 자동필터, 고급필터
- 부분합
- 목표값 찾기 : 데이터 - 데이터 도구 - 가상분석 - 목표값
- 해 찾기
- 피벗테이블 : 1차원 빈도표 작성 또는 2차원 분할표 작성
- 산점도 그리기
- 추가기능 : 통계분석, 분석도구

2. 엑셀 통계 함수
- SUM : 합계 계산
- ROUND : 지정한 자릿수 반올림
- AVERAGE : 산술 평균
- STDEV : 표준편차
- IF( 조건, value-if-true, value-if-false) : 조건에 따른 값을 표시
- TRIMMEAN : a% 절사평균
- MEDIAN : 중앙값
- MODE : 최빈값
- VAR : 분산
- QUARTILE(array, 0~4) : 사분위수범위
- PERCENTILE : 자료의 백분위수 계산
- PERCENTRANK : 자료에서 x의 백분율 상대 위치 계산
- STANDARDIZE : 평균과 표준편차를 이용하여 z-값 계산
- 데이터베이스 함수 : SUM, DAVERAGE, DCOUNT, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DVAR
- BINORMDIST : 이항분포
- POISSON : 포아송 분포
- NORMDIST : 정규 분포에서 P(X ≤ x) 계산
- NORMIVN : 정규분포에서 왼쪽 부분의 확률이 주어진 경우, 이에 해당 x값
- TDIST(x, df, tailes) : Prob(t > x), df ~ 분포의 자유도
- TINV : t분포에서 해당하는 x 값
- CONFIDENCE : 신뢰구간
- CHIINV : 카이제곱분포에서 해당하는 x 값
- CHIDIST : 카이제곱 분포의 우측 확률 값 = Prob ( 카이^2 > x )
- ZTEST : 단측검정의 유의확률 계산
- NORMSDIST : 표준 정규 누적 분포 함수의 확률 값
- CORREL, PEARSON : 상관계수
- 행렬 함수 : MMULT, MDETERM, MINVERSE, TRANSPOSE,
- INTERCEPT : 회귀모형의 절편
- SLOPE : 단순회귀모형의 기울기
- RSQ : 단순회귀모형의 결정계수
- LINEST : 중회귀모형을 적합시킨 결과들 (절편, 기울기, 결정계수, F-통계량)
- FTEST : 분산의 동일성 검정


1. 통계학의 적용 과정

- 문제 설정
- 조사, 관측, 실험을 통한 데이터의 수집
- 수집된 데이터의 정리?요약을 통한 새로운 정보추출
- 통계적 추론과정을 통해서 문제 해결


2. 엑셀 그래프의 종류

- 구성비 : 원형 차트
- 항목별 비교 : 가로막대형, 세로막대형
- 시간적 추이 : 세로막대형, 꺽은선형
- 도수 분포 : 세로막대형, 꺽은선형 (※히스토그램)
- 연관성 검토 : 분산형


3. 기술통계량

- 중심값 : 평균, 중앙값, 최빈값, 절사평균, 백분위수
- 산포 : 분산, 표준편차, 사분위수, 범위, 변동계수
(변동계수=표본표준편차/평균 : 산포평균이 크게 다른 집단의 정도 )
- 기술통계량 : 줄기-잎 그림, 상자그림 (다섯 숫자요약)


4. 표본분포

- 표본추출변동 : 동일한 모집단에서 구한 표본평균이 달라지는 현상
- 표본분포 : 표본 추출을 무한히 반복했을 때 나타나는 개념상의 분포
- 통계량(statistic) ; 랜덤 표본의 함수
- 표본분포 : 통계량의 확률분포
- 중심극한정리 : 표본크기가 충분히 크면, 임의의 모집단에서 추출된 랜덤표본에 대한 표본평균의 분포는 근사적으로 정규분포를 따른다.
- t-분포 : 모분산을 모르는 경우 모평균에 대한 분포는 t-분포를 따른다
- 카이제곱분포 : 정규모집단의 표본분산의 표본분포는 카이제곱분포를 따른다
- 합동표본 분산
- F-분포


5. 추정

- 통계적 추론 : 모수의 값을 추정하거나 가설 검정을 시행 - 추정 : 모집단에서 랜덤표본한 특성치에 대한 추측값과 오차한계를 제시
- 통계량 : 표본평균, 표본분산, 표본비율
- 점추정, 구간추정, 신뢰구간


6. 가설 검정

가설 : 모집단의 분포에 대한 예상, 추측, 또는 가정
- 대립가설 : 수집된 자료를 이용하여 주장하거나 보이고 싶은 가설
- 귀무가설 : 대립가설과 반대되는 가설

가설검정 (유의성검증) : 표본조사 결과를 이용한 가설검정의 통계적 절차
- 검정통계량 : 가설검정에 사용되는 통계량
- 기각역 : 귀무가설을 기각하게 되는 검정 통계량 값의 영역
- p값 (유의확률) : 귀무가설이 옳다는 가정 하에서 검정통계량 값이 나올 가능성

가설검정의 수행과정
- 통계적 가설(귀무가설과 대립가설)을 설정함
- 유의 수준을 정함
- 조사 결과로부터 검정통계량의 관측된 값을 계산함
- 조사 결과에 대한 유의확률을 계산함 (유의확률은 귀무가설 하에서 검정통계량의 표본분포를 이용)
- 지정된 유의수준과 계산한 유의확률을 비교하여 통계적 유의성을 판단!!


7. 범주형 데이터 분석

범주형 데이터 분석
- 명목척도, 순서척도, 구간척도, 비율척도
- 1차원 빈도표나 2차원 분할표를 작성
- 적합도 검정 : 범주형 자료분석에서 각 범주의 관측도수가 사전에 가정한 확률 모형에 적합한 지 여부를 검정
- 귀무가설 : 주사위의 각 숫자의 확률은 1/6, 동전의 앞/뒤면의 확률은 1/2 등
- 검정통계량 : 귀무가설 하에서 카이제곱분포를 따름
- 귀무가설이 옳다면 관측값과 기대도수의 차이는 적게될 것임. 표본에서 구한 검정통계량의 카이제곱 값이 크다는 것은 관측도수와 기대도수의 차이가 크다는 것을 의미함. 즉 검정통계량의 카이제곱 값이 클수록 귀무가설이 옳지 않다는 증거가 될 수 있을 것임.
- 독립성 검정 : 범주형 자료를 이차원 분할표로 정리하여 두 변수 간의 연관성이 있는가를 검정하는 방법 (카이제곱 분포를 따름)


8. 상관분석

- 산점도 : 이차원 평면상에 두 변수들이 취하는 관측값을 표시한 그래프
- 상관계수 : 연속형인 두 변수 간의 선형관계 정도 (-1 ~ +1)


9. 회귀분석

종속변수(반응변수), 설명변수(독립변수)
- 회귀모형 : 종속변수와 독립변수들 간의 함수관계를 나타내는 통계 모형
- 회귀분석 : 변수 간의 회귀모형을 밝히고, 적합된 회귀모형이 자료를 얼마나 잘 적합하고 있는가를 규명하는 통계적 분석방법

단순 선형회귀분석
- 회귀 직선의 적합도
: 회귀식의 타당성 검토
: 제곱합과 자유도의 분할
- 결정계수(R^2) : 총 변동 중에서 설명된 변동이 차지하는 비 (클수록 적합도가 높음) (0 ~ 1)
- 분산분석표


10. 분산 분석

분산분석이란 앞서 다른 두 개의 처리 또는 두 모집단의 모평균 차이에 대한 검정문제를 세 개 이상의 처리 또는 세 개 이상 모집단의 평균 차이에 대한 검정 문제로 확장할 때 사용되는 분석 방법임.
- 실험연구에서 계획된 실험에 따라 얻어진 반응값을 특성값이라고 하며, 특성값에 영향을 주는 실험의 조건들을 인자(factor), 각 인자의 실험조건을 인자의 수준(level) 또는 처리(treatment)라고 함.
- 실험계획법 : 어떤 관심 있는 특성값에 대하여 하나의 인자의 영향을 조사할 때 사용되는 실험계획법을 일원배치법, 인자가 둘인 경우를 이원배치법이라 함.
=> 일원배치법에서는 인자의 주효과, 이원배치법에서는 인자의 주효과 및 인자간의 교호작용이 있는지 고려함.
- 어떠한 실험이든 각 수준조합에서 1회의 실험을 하는 것보다는 반복실험을 하는 것이 실험결과의 신뢰성을 높일 수 있게 됨.
- 반복이 있는 이원배치법의 경우는 반복이 없는 이원배치법의 경우에 비하여 인자들의 수준조합에서 일어나는 효과를 오차항과 분리할 수 있게 되며, 따라서 두 인자간의 교호작용 효과를 검출할 수 있게 됨.
- 교호작용 : 2인자 이상의 특정한 인자수준의 조합에서 일어난 효과로 인자 A의 효과가 인자 B의 수준의 변화에 따라 변화하는 경우

- 일원배치법 : 인자가 하나 있고, 이 인자의 유의성에 대하여 F 검정을 수행함
- 반복이 없는 이원배치법 : 인자가 두개 있음
- 반복이 있는 이원배치법 : 특성값에 영향을 미치는 인자가 두 개이고, 각 인자의 수준조합에서 반복이 있는 실험