로그인

Select the product you need help with

Excel의 향상된 통계 함수가 분석 도구에 미치는 영향에 대한 설명

이 문서에서는 Microsoft Office Excel 2003과 그 이상 버전의 Excel에서 통계 함수의 향상된 계산 기능이 ATP(분석 도구) 도구에 미치는 영향에 대해 설명합니다. 대부분의 ATP 도구는 결과를 계산하는 과정에서 Excel 통계 함수를 호출합니다. 많은 사례에서 이 문서는 개별 Excel 통계 함수에 대해 설명하는 문서에 대한 포인터 역할을 하며 일부 ATP 도구에 도움이 되는 향후 개선 사항에 정보를 제공합니다.

Mac용 Microsoft Excel 2004 정보

Mac용 Excel 2004의 통계 함수는 Microsoft Office Excel 2003과 그 이상 버전의 Excel에서 통계 함수를 업데이트할 때 사용한 알고리즘과 동일한 알고리즘을 사용하여 업데이트되었습니다. Excel 2003과 그 이상 버전의 Excel에서 함수를 작동하고 수정하는 방법을 설명하는 이 문서의 모든 정보는 Mac용 Excel 2004에도 적용됩니다.

위로 가기 | 피드백 보내기

추가 정보

ATP 코드는 세 가지 ATP ANOVA 도구를 향상시키기 위한 경우를 제외하고 직접 수정되지 않았습니다.

몇몇 ATP 도구는 Excel 2003과 그 이상 버전 Excel의 향상된 통계 함수를 호출하므로 Excel 2003과 그 이상 버전 Excel에 대한 계산 기능이 향상되었습니다. 이전 버전 Excel의 결과와 이후 버전 Excel의 결과가 다를 경우에는 Excel 2003과 그 이상 버전 Excel의 값이 더 정확합니다.

대부분의 사용자는 Excel 버전 간의 결과 차이를 알아차리지 못합니다. 이는 극단적인 경우에만 문제가 되는 반올림 오류로 인해 차이가 생기기 때문입니다. 그러나 이 문서에서는 Microsoft Excel 2002와 그 이전 버전 Excel의 잘못된 수식 때문에 차이가 발생하는 경우를 첫 번째 예로 보여 줍니다. 이러한 버전에서는 이 도구를 사용하면 안 됩니다.

그런 다음 두 번째 예에서는 Excel 2003과 그 이상 버전의 Excel에서도 계속 사용되는 Excel 2002와 그 이전 버전 Excel의 잘못된 수식을 보여 줍니다. 이 경우에는 Excel 버전에 관계없이 ATP 도구를 사용하면 안 됩니다.

첫째, Constant is Zero 확인란을 선택해야 하는 경우에는 회귀 도구를 사용하면 안 됩니다. 이 문제는 Excel 2003과 그 이상 버전의 Excel에서 해결되었습니다. Constant is Zero 확인란의 선택이 취소되어 있는 경우(실제로 더 일반적인 경우)에는 회귀 도구를 사용해도 좋습니다.

둘째, Excel 버전에 관계없이 데이터 관측값이 누락되지 않는다는 것을 보장할 수 없는 경우에는 ATP t-검정: 2표본 평균 쌍체 도구를 사용하면 안 됩니다. 관측값이 하나 이상 누락될 경우 이 도구는 잘못된 응답을 제공하거나 전혀 응답하지 않습니다.

ATP 일치 쌍 2표본 t-검정 도구에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

829252

(http://support.microsoft.com/kb/829252/ )

Excel에서 분석 도구 t-검정을 사용하면 잘못된 결과 및 레이블이 생성될 수 있다

이 도구를 사용하려는 경우 데이터가 누락되거나 누락될 가능성이 있으면 Excel의 TTEST 함수가 이를 올바로 처리합니다.

이 문서의 뒷부분에는 개별 ATP 도구에 대해 설명하는 절이 나오는데, 여기에 없는 도구는 Excel 2003과 그 이상 버전의 Excel에 포함된 향상된 기능의 영향을 받지 않습니다.

ANOVA: 단일 요소, 복제가 있는 두 요소 및 복제가 없는 두 요소

이러한 세 가지 각 ANOVA 도구는 계산 기능이 보다 강력해진 두 단계 통과 알고리즘으로 계산 절차를 업그레이드하기 위해 다시 작성되었습니다. 이러한 개선 사항은 평균에 대한 제곱 편차의 합을 계산하는 통계 함수(예: VAR, STDEV, SLOPE, PEARSON)의 개선 사항과 비슷합니다.

ATP ANOVA에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

829215

(http://support.microsoft.com/kb/829215/ )

Excel 분석 도구 ANOVA 도구의 향상된 계산 기능에 대한 설명

상관 관계

이 도구는 변경되지 않았습니다. 그러나 모든 버전의 Excel에 있는 상관 관계 도구와 공변성(Covariance) 도구 사이에는 약간의 차이가 있습니다. 상관 관계 도구는 대각 위치에 1이 표시되고 비대각 위치에 상관 관계가 표시되는 하위 삼각 상관 관계 테이블을 반환합니다. 이 도구는 CORREL을 사용하여 비대각 항목을 계산하고 이러한 항목을 CORREL이 반환하는 값으로 채웁니다. 따라서 데이터 항목이 변경되어도 테이블의 항목은 변경되지 않습니다. 이 동작을 공변성(Covariance) 도구의 동작과 비교해 보십시오.

공변성(Covariance)

이 도구는 대각 위치에 분산이 표시되고 비대각 위치에 공변성(Covariance)이 표시되는 하위 삼각 공변성(Covariance) 테이블을 반환합니다. 데이터 항목이 변경되는 경우 테이블의 결과도 변경되도록 대각 위치에 있는 셀에는 "=VARP(...)" 수식이 포함됩니다. VARP는 Office Excel 2003과 그 이상 버전의 Excel에서 향상되었습니다.

VARP에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

826393

(http://support.microsoft.com/kb/826393/ )

Excel 통계 함수: VARP

공변성(Covariance) 도구는 COVAR을 사용하여 비대각 항목을 계산하고 이러한 항목을 COVAR이 반환하는 값으로 채웁니다. 따라서 데이터 항목이 변경되어도 비대각 항목은 변경되지 않습니다.

설명 통계

이 도구는 자신이 계산하는 모든 항목에 대해 Excel 통계 함수를 호출합니다. Excel 2003 및 그 이상 버전의 Excel에서는 VAR 및 STDEV가 향상되었기 때문에 극단적인 경우의 반올림 오류로 인해 다양한 값을 생성할 수 있습니다.

VAR에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

826112

(http://support.microsoft.com/kb/826112/ )

Excel 통계 함수: VAR

F-검정 분산 2표본

설명 통계 도구와 마찬가지로 이 도구도 VAR을 호출하며 극단적인 경우의 반올림 오류로 인해 다양한 값을 생성할 수 있습니다.

난수 생성

이 도구는 범위를 임의의 관측값으로 채웁니다. 이러한 관측값은 시트가 다시 계산될 때 셀 값이 다시 계산되어 새 관측값으로 대체되지 않도록 셀에 직접 입력됩니다. 반면에 Excel의 기본 제공 RAND 함수는 시트가 다시 계산될 때마다 기존 난수를 새 난수로 대체합니다. RAND를 사용하면 값을 유지할 수 있습니다. 이렇게 하려면 특정 범위에 있는 결과를 복사한 다음 선택하여 붙여넣기 명령을 사용하여 동일한 범위에 값을 붙여 넣으십시오.

RNG(난수 생성) 도구도 다양한 확률 분포값으로부터 난수 관측값을 생성하는 반면 RAND는 RNG 도구에 포함된 하나의 옵션(Uniform[0,1])에 해당합니다. 이 문서에서는 RAND와 Excel의 통계 함수를 결합하여 이러한 관측값을 생성하는 방법에 대해 설명합니다.

따라서 기능적인 측면에서 약간의 창의력을 발휘하여 RAND를 사용하면 ATP 난수 도구를 열거할 수 있습니다. 이렇게 하면 많은 난수가 필요한 경우 특히 유용할 수 있습니다.

Excel 2002와 그 이전 버전의 Excel의 경우 ATP 난수 생성기와 RAND는 둘 다 무작위성 표준 테스트의 결과가 좋지 않은 것으로 알려져 있습니다. 테스트 결과가 좋지 않은 것은 의사 난수의 시퀀스가 반복을 시작하기 전의 주기가 지나치게 짧기 때문입니다. 이는 많은 난수가 필요할 때만 문제가 됩니다.

Excel 2003과 그 이상 버전의 Excel에서는 RAND가 향상되어 이러한 표준 테스트를 모두 통과합니다. 여기서는 RAND의 난수 시퀀스가 1조 개 이상의 숫자가 생성된 후에 반복을 시작합니다.

RAND에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

828795

(http://support.microsoft.com/kb/828795/ )

Excel 2007 및 Excel 2003의 RAND 함수에 대한 설명

그러나 ATP의 개별 난수 생성기는 업그레이드되지 않았습니다. Excel 2002와 그 이전 버전의 Excel에 포함된 RAND 버전에서처럼 ATP의 개별 난수 생성기는 무작위성 표준 테스트의 결과도 좋지 않고 반복 주기도 짧은 것으로 알려져 있습니다. 이는 매우 긴 시퀀스의 난수(예: 1백만 개)가 필요한 경우에만 부정적인 영향을 줍니다.

RNG 도구는 다양한 확률 분포값과 RAND를 통해 난수를 출력하는 데 사용된 분포값인 Uniform[0,1]으로부터 임의의 관측값을 제공합니다. ATP 도구는 먼저 Uniform[0,1] 난수를 하나 또는 둘 이상 추출한 후 다음과 같은 특정 분포값 중 하나에서 관측값으로 응답을 변환합니다. RAND를 사용하여 다양한 관측값을 생성하는 사용자를 위해 이 문서에서는 다음 표에 RAND를 사용하는 수식을 보여 줍니다. 여기에는 분석 도구의 정규 분포 사례에 대한 경고도 나와 있습니다.

표 축소표 확대

Distribution	RAND()를 사용하는 Excel 수식
Bernoulli(p)	=IF(RAND() <= p, 1, 0)
Binomial(n,p)	=CRITBINOM(n, p, RAND())
Discrete	아래 참조
Normal(mu, sigma)	=NORMINV(RAND(), mu, sigma)
Patterned	실제로는 난수가 아님
Poisson(mean)	아래 참조
Uniform(low, high)	= low + (high ? low) * RAND()

Normal(mu, sigma) 사례에 ATP 난수 도구를 사용하는 대신 이 표에 나오는 RAND와 수식을 사용하는 이유는 두 가지입니다. 첫째는 RAND가 ATP 도구보다 향상된 Uniform[0,1] 난수 생성기이고 둘째는 ATP 도구가 Excel의 NORMINV 함수를 호출하는 대신 자체 버전의 역 정규 분포를 사용하기 때문입니다. 이로 인해 Excel 2003과 그 이상 버전의 Excel에 포함된 NORMINV 버전보다 정확성이 떨어집니다. ATP 도구는 정규 분포 근사치의 정확도와 이진 검색의 구체화 능력이 Excel보다 떨어집니다. Excel에서는 훨씬 더 향상된 NORMSDIST 함수가 사용되며 NORMINV의 확률 인수에 보다 가까운 값을 얻기 위해 훨씬 더 세부적인 검색이 수행됩니다. 즉, 이 사례에 ATP를 사용하면 Excel 2003과 그 이상 버전의 Excel에 포함된 향상된 NORMINV, NORMSDIST 및 RAND 함수를 사용할 수 없습니다.

이산 분포에서 관측값을 추출하려면 값이 B 열에 있고 확률이 C 열에 있다고 가정하십시오. 그런 다음 A 열의 각 행을 해당 행의 B 열에 있는 값보다 작은 값을 관측할 확률로 채웁니다. 여기서 10개의 값이 있다고 가정하면 이 데이터는 A1:C10 셀에 있습니다. 그러면 A1에는 첫 번째 값보다 작은 값을 관측할 확률이 포함되므로 A1을 0으로 설정해야 합니다. 따라서 VLOOKUP(RAND(), A1:C10, 2)을 사용할 수 있습니다. 네 번째 VLOOKUP 인수는 선택적이며 생략하거나 TRUE로 설정해야 합니다. "2"는 두 번째 열(이 예에서는 B 열)의 값을 반환한다는 것을 의미합니다.

ATP는 1992년 캠브리지 대학에서 출판한 Numerical Recipes in C, The Art of Scientific Computing(W.H., S.A. Teukolsky, W. T. Vetterling, B.P. Flannery 공저) 2판의 293-295 페이지에 나오는 포아송 관측 생성 방법을 사용합니다. 기존 Excel 함수를 쉽게 사용할 수 있는 두 가지 방법이 있습니다.

첫 번째 방법은 평균 값이 m인 POISSON 확률 변수에 큰 n에 대해 BINOMIAL(n, m/n)에 가까운 분포값이 있는 관측을 사용하는 것입니다. 그런 다음 CRITBINOM(n, m/n, RAND())을 호출할 수 있습니다. n의 선택 여부는 m에 의해 결정됩니다. m보다 1,000배 이상 큰 n은 충분히 커야 합니다.

두 번째 방법은 지수에 대한 POISSON 분포와 관련이 있습니다. POISSON 프로세스에 따라 단위 시간 당 m 비율로 이벤트가 발생할 경우 이벤트 발생 간격은 평균 값이 1/m인 지수 분포값에 의해 결정됩니다. POISSON 관측의 경우 이 지수 분포값에서 관측값 시퀀스를 취해 합계가 1을 초과하기 전까지 이벤트의 발생 횟수를 카운트할 수 있습니다. 이 지수 분포값에서 관측값을 구하려면 GAMMAINV(RAND(), 1, 1/m)를 사용하십시오. 이 방법은 m이 상대적으로 0에 가까울 때 사용하는 것이 좋습니다.

회귀

회귀 도구는 Excel의 LINEST를 호출합니다. LINEST에 대한 문서에서는 Excel 2003과 그 이상 버전의 Excel에서 향상된 다양한 LINEST 기능에 대해 설명합니다.

LINEST에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

828533

(http://support.microsoft.com/kb/828533/ )

Excel 2003과 그 이상 버전 Excel의 LINEST 함수에 대한 설명

Excel 2002 또는 그 이전 버전 Excel의 ATP 회귀 도구에는 LINEST와 마찬가지로 다음과 같은 두 가지 단점이 있습니다.

원점을 통해 회귀가 강요되는 경우 제곱근, r 제곱 및 f 통계 값의 회귀 합이 항상 정확하지 않습니다.

이것은 LINEST에서는 "세 번째 인수가 TRUE로 설정되거나 생략되는 대신 FALSE로 설정되었음"을 의미하고, ATP에서는 "Constant is Zero 확인란이 선택되어 있음"을 의미합니다.
LINEST 및 ATP 도구는 공선성(Collinearity) 문제의 영향을 받지 않습니다. LINEST에 대한 문서에서는 Excel 2003과 그 이상 버전의 Excel에서 공선성(Collinearity)과 근사 공선성(Near-collinearity)이 있을 경우 이를 찾아서 적절히 처리하도록 설계된 LINEST에 대한 계산 접근법에 대해 설명합니다.

Excel 2003과 그 이상 버전의 Excel에서는 이러한 두 가지 단점이 모두 해결되었으며 ATP 회귀 도구의 성능도 유사한 방식으로 향상되었습니다. 도구 코드는 변경되지 않았으므로 향상된 Excel 함수를 호출하는 방식으로 향상되었습니다. 이 문서에서는 LINEST의 개선 사항이 통계 함수 개선 사항 중 가장 중요한 것으로 간주합니다.

다음 표에서는 이전 버전의 Excel과 Excel 2003 이상 버전에서 Constant is Zero 확인란을 선택한 상태에서 회귀 도구를 실행할 경우 출력되는 내용을 보여 줍니다. 여기에는 앞에서 언급한 첫 번째 단점이 나와 있습니다. 이전 버전의 Excel에서는 제곱 근의 회귀 합이 R 제곱근 값에서처럼 음수입니다.

표 축소표 확대

X	Y
1	11
2	12
3	13

Excel 2002 및 그 이전 버전
출력 요약

회귀 통계
R 배수	65535
R 제곱근	-20.4285714
조정된 R 제곱근	-20.9285714
표준 오류	4.629100499
관측값	3

ANOVA
	df	SS	MS	F	Significance F
회귀	1	-40.85714286	-40.85714286	-1.90666667	#NUM!
나머지	2	42.85714286	21.42857143
합계	3	2

Excel 2003 및 그 이상 버전의 Excel
출력 요약

회귀 통계
R 배수	0.949342311
R 제곱근	0.901250823
조정된 R 제곱근	0.401250823
표준 오류	4.629100499
관측값	3

ANOVA
	df	SS	MS	F	Significance F
회귀	1	391.1428571	391.1428571	18.25333333	0.14637279
나머지	2	42.85714286	21.42857143
합계	3	434

t-검정: 2표본 평균 쌍체

앞에서 언급한 대로 데이터 값이 하나 이상 누락될 가능성이 있을 경우 이 도구를 사용하면 안 됩니다. 일반적으로 이 검정은 특정 사건을 기준으로 전과 후를 비교하는 데 사용됩니다. 예를 들어, 60일 동안 다이어트를 한 후 다이어트 이전 체중과 다이어트 이후 체중을 비교하는 데 이 검정을 사용할 수 있습니다. 누락된 관측값이 없으면 이 도구는 제대로 작동합니다. 이전 관측값과 이후 관측값에서 누락된 관측값 개수가 다르면 오류 메시지가 나타나고 이 도구는 계산 작업을 수행하지 않습니다. 누락된 관측값이 있고 이전 관측값과 이후 관측값에서 누락된 관측값의 개수가 같으면 이 도구는 여러 개의 오류가 포함된 응답을 반환합니다.

표준 절차는 이전 관측값이나 이후 관측값이 누락될 경우 데이터에서 해당 주제를 제거하고 이전 관측값과 이후 관측값이 둘 다 있는 주제가 포함된 데이터를 분석하기 위한 것입니다. Excel의 TTEST 함수는 이 표준 절차에 따라 누락된 데이터를 처리합니다.

다른 두 t-검정 도구인 2표본 등분산성 가정 및 2표본 비등분산성 가정은 이러한 결함을 공유하지 않습니다.

z-검정: 평균 2표본

이 문서에서는 난수 생성 도구의 정규 분포 사례에서 NORMSINV(정확하게 표현하면 NORMSINV를 호출하는 NORMSINV) 함수를 호출하지 않지만 정규 역(normal inverse) 값을 검색하는 하위 절차를 사용한다는 내용에 대해 설명했습니다.

z-검정 도구는 NORMSINV 함수를 호출하지 않고 Excel 2003과 그 이상 버전의 Excel에 포함된 향상된 기능을 사용합니다.

이전 Excel 버전에서의 실행한 결과

ATP 도구는 Excel 2003 및 그 이상 버전 Excel의 향상된 통계 함수를 호출하므로 Excel 2003과 그 이상 버전 Excel에 대한 성능이 향상되었습니다. 이러한 향상된 기능에는 Excel 2002와 그 이전 버전의 Excel에서 Constant is Zero 확인란이 선택되어 있을 때 세 번째 인수가 FALSE로 설정되면 ATP 회귀 도구가 잘못된 결과를 반환하는 LINEST도 포함됩니다. 이외에도 이전 버전에서는 대부분 극단적인 경우 반올림 오류 때문에 생기는 차이를 알아차리지 못하는 문제를 해결하도록 Excel 함수가 향상되었습니다.

세 가지 ATP ANOVA 도구는 Excel의 VAR과 마찬가지로 보다 강력해진 계산 알고리즘으로 바꾸기 위해 ATP 코드를 수정하여 향상되었습니다. 이전 버전의 Excel에서 이러한 도구를 사용한 경우에는 극단적인 경우에만 차이를 발견할 가능성이 높습니다.

모든 버전의 사용자에 대한 경고: 데이터가 누락될 가능성이 조금이라도 있을 경우 t-검정: 2표본 평균 쌍체를 사용하면 안 됩니다.

Excel 2003 및 그 이상 버전 Excel에서의 실행 결과

Excel의 통계 함수가 크게 향상되었습니다. 따라서 이러한 함수를 호출하는 많은 ATP 도구도 향상되었습니다. 그러나 ATP 도구 중 난수 생성기는 자체 포함 방식으로 구현되고 RAND를 호출하지 않기 때문에 향상된 RAND 함수를 사용하지 않습니다. 이는 바람직하지 않으며 정규 분포 난수 관측값을 사용하는 특수한 경우에 특히 더 바람직하지 않습니다. 역 정규 분포도 자체 포함 방식으로 구현되고 훨씬 더 향상된 NORMSINV 함수를 호출하지 않습니다.

다음 표에서는 Excel 2003과 그 이상 버전의 Excel에서 향상된 ATP 도구와 이 ATP 도구가 호출하는 Excel 함수를 보여 줍니다. 호출되는 각 Excel 함수에 대해서는 해당 문서를 참조하십시오.

표 축소표 확대

ATP 도구	호출되는 Excel 함수

ANOVA: 단일 요소	VAR, FINV
ANOVA: 복제가 있는 두 요소	VAR, FINV
ANOVA: 복제가 없는 두 요소	VAR, FINV
상관 관계
공변성(Covariance)
설명 통계	STDEV, TINV, VAR
지수 평활법(Exponential Smoothing)
F-검정 분산 2표본	VAR, FINV
푸리에 분석
히스토그램
이동 평균
난수 생성
순위 및 백분위수
회귀	LINEST
샘플링	RAND
t-검정: 2표본 평균 쌍체	VAR, PEARSON, TINV
t-검정: 2표본 등분산성 가정	VAR, TINV
t-검정: 2표본 비등분산성 가정	VAR, TINV
z-검정: 평균 2표본	NORMSDIST, NORMSINV

LINEST와 RAND를 제외하고 이 표에 나오는 모든 함수는 극단적인 경우 반올림 오류가 발생하면 이전 버전의 Excel과 이후 버전의 Excel 간에 차이가 있을 가능성이 높습니다. LINEST과 RAND는 앞에서 설명한 대로 훨씬 더 향상되었습니다. 예외적으로 샘플링 도구는 RAND를 호출하지만 RNG 도구는 긴 시퀀스의 임의 관측값이 필요할 때 성능이 저하되는 자체 포함 생성기에 의존합니다.

결론

세 가지 각 ANOVA 도구에 대한 ATP 코드의 변경 내용을 제외하고 ATP 코드는 다시 작성되지 않았지만 ATP 도구 표에 설명된 대로 향상된 Excel 함수를 호출하여 이점을 얻습니다. Excel 2003과 그 이상 버전의 Excel에서는 t-검정: 2표본 평균 쌍체 테스트의 결함이 해결되지 않았습니다. 가장 두드러진 개선 사항은 Constant is Zero 확인란이 선택되어 있을 때 LINEST가 더 이상 잘못된 결과를 반환하지 않고 LINEST가 공선성(Collinearity)을 적절하게 처리할 수 있는 회귀 도구에서 찾을 수 있습니다.

Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹
(http://support.microsoft.com/newsgroups/default.aspx)
에 참여하시기 바랍니다.

위로 가기 | 피드백 보내기