통계학 & R 프로그램 스터디 8일차
- 더치페이와 N빵(2) :: 엑셀, 평균, 분산, 표준편차 <이토록 쉬운 통계 & R>
- 엑셀, 스프레드시트, 구글 시트 활용법, 평균, 분산, 표준편차 함수
- var 와 varp의 차이는? stdev 와 stdevp의 차이는?
오늘 스터디도 <이토록 쉬운 통계 & R> 책에는 나오지 않는 내용이다. 엑셀(스프레드시트, 구글 시트) 프로그램을 활용하여 간단하게 평균과 분산, 표준편차를 구해보려고 한다. 이미 알고 있는 average, var, stdev 함수에 대한 간략한 소개를 포함하여 기타 몇 가지 함수를 더 자세히 스터디하려 한다.
평균 Average, Averagea, Averageif
엑셀에서 평균을 구하는 함수는 average 함수이다. 사용법도 간단하다.
사용법 : AVERAGE(value1, [value2, ...])
예 : AVERAGE(A2:A100, B2:B100)
이번 스터디에서는 지난번 스터디(통계학 & R 프로그램 스터디 6일차 - 순서대로 한줄서기(3) 엑셀, 스프레드시트를 활용하여 복습하기 <이토록 쉬운 통계 & R>)에서 활용한 101명의 가상 점수표를 다시 활용했다.
average 함수를 이용해서 101개의 데이터들의 평균을 구했다. 결과는 73.95544554 가 나왔다.
이번에는 averagea 라는 함수를 살펴보자. AVERAGE + A ... A가 붙은게 어떤 차이점을 보일까?
사용법 : AVERAGEA(value1, [value2, ...])
예 : AVERAGEA(A2:A100, B2:B100)
일단 사용법은 Average 함수와 동일하다. 그렇다면 결과는?
어랏? 차이가 없는데????
아래의 그림을 다시 살펴보자.
엥? 지금은 왜 결과가 다르지? 그 차이를 혹시 찾을 수 있을까?
평균을 구하기 위해 선택한 영역은 B3:K3 로 모두 동일하다. 그런데 이 영역에 비밀이 숨어 있다. 데이터의 갯수는 총 101개. 가로로 10개의 열, 세로로 10개의 행에 총 100개의 데이터가 있다. 그리고 11번째 행 첫번째 열 1개가 추가로 있어서 총 101개가 있다.
평균을 구하기 위해 선택한 영역은 세로 10, 가로 11개로 총 110개를 선택했다. (이 중에 9개는 빈칸) Average와 Averagea 모두 셀이 비었을 경우에는 차이가 없었다. 그런데 마지막 사진에 빈 칸을 a, b, c, d, e, f, g, h, i 등으로 숫자가 아닌 임의의 문자를 채워보았다. 그랬더니 결과에 차이가 생겼다.
결론적으로
Avearge는 데이터 영역 안의 데이터 중에 숫자 데이터만 골라서 평균을 계산한다.
Averagea는 데이터 영역 안의 데이터 중에 숫자 데이터를 골라서 합산을 하고 나눌 때는 데이터 갯수 전체로 나누어 평균을 계산한다.
Averagea가 자주 쓰이지는 않을 것 같지만 알아두면 어딘가 써 먹을 수 있을 것 같다. 데이터가 숫자인지 아닌지를 자동으로 판별을 할 수 있으니 조건문 같은게 필요가 없을 듯... 근데 보통 데이터는 같은 유형으로 모으니 이걸 판별할 이유가 있을까? 싶기도 하다.
이번엔 Averageif 에 대해서 알아보자
사용법 : AVERAGEIF(criteria_range, criterion, [average_range])
예 : AVERAGEIF(A1:A10, ">20", B1:B10)
averageif 를 설명하기 위해 표를 약간 수정했다. 이번 표에는 점수와 남여가 기록되어 있다.
=averageif(C3:C13, "여", B3:B13) 이라고 입력했다.
여기에서 C3:13은 조건을 검토할 영역, "여"는 조건, B3:B13은 평균을 구할 영역이다.
이런 방법으로 여자의 데이터들로 평균을 구할 수 있다.
이번에는 =averageif(B3:B13, ">70") 이라고 입력해보았다.
이 수식은 B3:B13 영역의 데이터들 중에 70보다 큰 점수들끼리만 평균을 구하라는 내용이다.
조건의 예를 몇 가지 소개하자면 다음과 같다.
조건의 예 : average 함수에서만 쓰는 조건은 아니다.
- "<>70" : 70과 같지 않다.
- "=70" : 70과 같다. (<- 70과 같다는 조건으로 평균을 구하면 70일텐데... 쓸 일은 거의 없을 듯..)
- ">70" : 70보다 크다.
- "<70" : 70보다 작다.
- "><" : 텍스트이다. (평균 구할 때는 쓸 일은 없을 듯)
- "*" : 텍스트이다. (평균 구할 때는 쓸 일은 없을 듯)
- "a*" : a로 시작하는 텍스트이다. (평균 구할 때는 쓸 일은 없을 듯)
- "*a" : a로 끝나는 텍스트이다. (평균 구할 때는 쓸 일은 없을 듯)
- "=" : 셀이 빈 경우이다. (<- 역시나 이 조건으로 평균을 구할 이유가...?)
- "<>" : 셀이 비어있지 않은 경우이다.
분산 var, varp
var 함수와 varp 함수는 모두 분산을 구하는 함수이다. 분산은 이미 이전 스터디(통계학 & R 프로그램 스터디 7일차 - 더치페이와 N빵(1) :: 평균, 분산, 표준편차 <이토록 쉬운 통계 & R>)에서 정리했던 것 처럼 평균에서 떨어져 있는 거리를 숫자로 계산하기 위한 여러 가지 시도들 중에 하나이다.
$$s_x^2 = \frac{1}{n-1}\sum_{i=1}^{n}(x_i - \overline{x})^2$$
로 계산할 수 있다.
함수 사용법은 두 함수 모두 동일하며 매우 간단하다.
사용법 : VAR(value1, [value2, ...]) , VARP(value1, [value2, ...])
예 : =var(B3:K13) , =var(1, 2, 3, 4, 5)
그렇다면 var와 varp의 차이는 무엇일까?
일단 결과를 비교해보니 var로 계산했을 때 119.093495, varp로 계산했을 때 117.9143515 가 나왔다. 다르네?
가만히 들여다보니 뭔가 간단한(?) 규칙이 보인다. 딱 보이죠? 설마~ ㅎㅎ
var로 계산한 값 119.093495에 100을 곱하고 101로 나누니 varp로 계산한 값 117.9143515가 나온다. 반대로 varp로 계산한 값 117.9143515에 101을 곱하고 100을 나누니 var로 계산한 값 119.093495가 나온다. 참 쉽죠? 정말?
앞에서 분산을 계산하는 식을
$$s_x^2 = \frac{1}{n-1}\sum_{i=1}^{n}(x_i - \overline{x})^2$$
라고 소개했다. 그런데 사실 이 값은 모집단 전체에 대한 분산이 아니다. 표본을 임의로 추출해서 계산한 표본분산이다. 모집단 전체에 대한 분산은 사실
$$\sigma^2 = \frac{1}{N}\sum_{i=1}^{N}(x_i - \overline{x})^2$$
로 계산을 한다. 왜 모집단 전체에 대한 분산을 계산할 때는 $n$으로 나누고 표본집단에 대한 분산을 계산할 때는 $n-1$로 나누는 지에 대해서는 언젠가 기회가 정리를 하고 싶고(지금은 곤란하다. 조금만 기다려달라...) 어쨌든 이 둘의 차이가 var 와 varp의 차이를 나타낸다.
VAR는 표본분산, VARP는 모분산을 뜻한다. (VARP가 VARiance of the entire Population 이라는 것 같은데...)
따라서 위의 101개의 데이터를 이용해서 분산을 계산했을 때, var는 $n-1$, 즉 100으로 나눈 결과, varp는 $n$, 즉 101로 나눈 결과이다. 그래서 var에 100을 곱하고 101로 나누면 varp가, varp에 101을 곱하고 100을 나누면 var를 구할 수 있는 것이다. (지금 이 경우에만 그렇다.... 표본이 101개니까...)
아, 그리고
VARA 와 VARPA 함수도 있는데 이것은 AVERAGEA와 사용 원리가 같다!!!
표준편차 stdev, stdevp
표준편차는 단순하게 생각하면 분산을 계산한 후에 제곱근을 씌워준 것이다. 왜? 단위를 맞춰주기 위해!
그렇기 때문에 앞에서 설명한 var와 varp를 이해한다면 stdev와 stdevp도 동일하게 이해할 수 있다. (STDEV는 STandard DEViation 이다.)
STDEV는 표본의 표준편차, STDEVP는 모집단의 표준편차를 의미한다.
STDEVA 와 STDEVPA 함수도 있는데 이것 역시 AVERAGEA와 사용 원리가 같다!!!
지나가는 말
나중에 통계 관련 수업을 할 때, 구글 시트를 이용해서 학생들과 다양한 공공데이터를 구해 평균, 분산, 표준편차들을 계산해보면 좋겠다. 거기에 모집단과 표본집단에 대하여 탐구해볼 수 있게 프로젝트형 수업을 디자인해봐야겠다.
사이트 도구로 만들어본 IT4EDU 사이트
블로그 소식을 한 눈에!
http://sites.google.com/view/it4edu
댓글