엑셀 분산 계산하기

분산(Variance)이란?

분산은 서로 다른 값이 얼마나 멀리 퍼져 있는지를 나타내는 데이터 집합의 변동성 측도입니다. 수학적으로 평균과의 제곱 차이의 평균으로 정의됩니다.

 

분산으로 실제로 계산하는 것을 더 잘 이해하려면 이 간단한 예를 고려하십시오.

지역 동물원에 5세, 14세, 10세, 8세, 6세의 호랑이 2마리가 있다고 가정합니다.

분산을 찾으려면 다음과 같은 간단한 단계를 따르십시오.

1. 다섯 숫자의 평균(단순 평균)을 계산합니다.
   

   

2. 각 숫자에서 평균을 빼서 차이를 찾습니다. 이를 시각화하기 위해 차트에 차이점을 표시해 보겠습니다.
   

   

3. 각 차이를 제곱합니다.
4. 차이 제곱의 평균을 계산합니다.

 

따라서 분산은 16입니다.

분산은 값이 0이면 변동성이 없음을 의미하며, 즉 데이터 세트의 모든 숫자가 동일하다는 것을 의미합니다. 숫자가 클수록 데이터가 더 많이 분산됩니다.

이 예는 모집단 분산에 대한 것입니다(즉, 5마리의 호랑이가 관심 있는 전체 그룹임). 데이터가 더 큰 모집단에서 선택된 데이터인 경우 약간 다른 공식을 사용하여 표본 분산을 계산해야 합니다.

 

Excel에서 분산을 계산하는 방법

Excel에서 분산을 수행하는 6가지 기본 제공 함수(VAR, VAR)가 있습니다. S, VARP, VAR. P, VARA 및 VARPA입니다.

분산 공식의 선택은 다음 요인에 의해 결정됩니다.

• 사용 중인 Excel 버전.
• 표본 분산을 계산하는지 아니면 모집단 분산을 계산하는지 여부
• 텍스트와 논리값을 평가할지 아니면 무시할지 여부.

 

Excel 분산 함수

 

VAR.S vs. VARA and VAR.P vs. VARPA

VARA 및 VARPA는 참조에서 논리 값과 텍스트 값을 처리하는 방식만 다른 분산 함수와 다릅니다. 다음 표는 숫자와 논리값의 텍스트 표현이 평가되는 방법에 대한 요약을 제공합니다.

인수 유형	VAR, VAR. S, VARP, VAR. P	VARA & VARPA
배열과 참조 내의 논리값	무시	평가됨 (TRUE=1, FALSE=0)
배열과 참조 안에 있는 숫자의 텍스트 표현	무시	0으로 평가됨
			인수에 직접 입력된 숫자의 논리 값 및 텍스트 표현	평가됨 (TRUE=1, FALSE=0)
빈 셀	무시

 

Excel에서 표본 분산을 계산하는 방법

표본은 전체 모집단에서 추출된 데이터 집합입니다. 그리고 표본에서 계산된 분산을 표본 분산이라고 합니다.

예를 들어, 사람들의 키가 어떻게 변하는지 알고 싶다면 지구상의 모든 사람을 측정하는 것은 기술적으로 불가능합니다. 해결책은 모집단의 표본(예: 1,000명)을 채취하여 해당 표본을 기반으로 전체 모집단의 키를 추정하는 것입니다.

표본 분산은 다음 공식으로 계산됩니다.

 

• x̄는 표본 값의 평균(단순 평균)입니다.
• n은 표본 크기, 즉 표본의 값 수입니다.

Excel에서 표본 분산을 찾는 3가지 함수(VAR, VAR.S VARA)가 있습니다.

 

Excel의 VAR 기능

표본을 기반으로 분산을 추정하는 가장 오래된 Excel 함수입니다. VAR 기능은 Excel 2000부터 2019까지 모든 버전에서 사용할 수 있습니다.

VAR(number1, [number2], …)

Excel 2010에서는 VAR 기능이 VAR로 대체되었습니다. 향상된 정확도를 제공하는 S입니다. 이전 버전과의 호환성을 위해 VAR을 계속 사용할 수 있지만 VAR을 사용하는 것이 좋습니다. S는 현재 버전의 Excel에서 사용할 수 있습니다.

 

Excel의 VAR.S 함수

이것은 Excel VAR 기능의 최신 버전입니다. VAR.S 함수를 사용하여 Excel 2010 이상에서 표본 분산을 찾습니다.

VAR.S(number1, [number2], …)

 

Excel의 VARA 함수

Excel VARA 함수는 다음 표에 표시된 대로 숫자, 텍스트 및 논리값 집합을 기반으로 표본 분산을 반환합니다.

VARA(value1, [value2], …)

 

Excel의 표본 분산 수식

숫자 데이터 집합으로 작업 할 때 위의 함수 중 하나를 사용하여 Excel에서 표본 분산을 계산할 수 있습니다.

예를 들어 6개 항목(B2:B7)으로 구성된 표본의 분산을 구해 보겠습니다.

 

이를 위해 아래 공식 중 하나를 사용할 수 있습니다.

=VAR(B2:B7)

=VAR.S(B2:B7)

=VARA(B2:B7)

스크린샷에서 볼 수 있듯이 모든 수식은 동일한 결과를 반환합니다(소수점 이하 2자리로 반올림).

결과를 확인하기 위해 수동으로 var 계산을 수행해 보겠습니다.

1.  
2. 표본의 각 숫자에서 평균을 뺍니다.
   =B2-$B$8차이점은 C2부터 시작하여 C 열로 이동합니다.
3.  
4. 각 차이를 제곱하고 결과를 D2부터 시작하여 D 열에 넣습니다.
   =C2^2
5.  
6. 제곱 차이를 더하고 결과를 표본의 항목 수에서 1을 뺀 값으로 나눕니다.
   =SUM(D2:D7)/(6-1)

보시다시피 수동 var 계산의 결과는 Excel의 기본 제공 함수에서 반환된 숫자와 정확히 동일합니다.

 

데이터 세트에 부울 값 및/또는 텍스트 값이 포함된 경우 VARA 함수는 다른 결과를 반환합니다. 그 이유는 VAR 및 VAR.S는 참조의 숫자 이외의 값을 무시하는 반면, VAR는 텍스트 값을 0, TRUE는 1, FALSE는 0으로 평가하기 때문입니다. 따라서 텍스트와 논리를 처리할지 또는 무시할지에 따라 계산에 사용할 분산 함수를 신중하게 선택하십시오.

 

Excel에서 모집단 분산을 계산하는 방법

모집단은 주어진 그룹의 모든 구성원, 즉 연구 분야의 모든 관찰입니다. 모집단 분산은 전체 모집단의 데이터 포인트가 분산되는 방식을 설명합니다.

모집단 분산은 다음 공식으로 찾을 수 있습니다.

 

• x̄는 모집단의 평균입니다.
• n은 모집단 크기, 즉 모집단의 총 값 수입니다.

Excel에서 모집단 분산을 계산하는 함수는 VARP, VAR.P, VARPA 의 3가지가 있습니다. 

 

Excel의 VARP 기능

Excel VARP 함수는 전체 숫자 집합을 기반으로 모집단의 분산을 반환합니다. Excel 2000부터 2019까지의 모든 버전에서 사용할 수 있습니다.

VARP(number1, [number2], …)

 

Excel 2010에서는 VARP가 VAR로 대체되었습니다. P이지만 이전 버전과의 호환성을 위해 계속 유지됩니다. VAR을 사용하는 것이 좋습니다. 이후 버전의 Excel에서 VARP 함수를 사용할 수 있다는 보장이 없기 때문에 현재 버전의 Excel에서는 P입니다.

 

Excel의 VAR.P 함수

Excel 2010 이상에서 사용할 수 있는 VARP 기능의 향상된 버전입니다.

VAR.P(number1, [number2], …)

 

Excel의 VARPA 함수

VARPA 함수는 숫자, 텍스트 및 논리값의 전체 집합을 기반으로 모집단의 분산을 계산합니다. 2000년까지 모든 버전의 Excel 2019에서 사용할 수 있습니다.

VARA(value1, [value2], …)

 

Excel의 모집단 분산 수식

샘플 var 계산 예에서 우리는 해당 점수가 더 큰 학생 그룹에서 선택되었다고 가정할 때 5개의 시험 점수의 분산을 발견했습니다. 그룹의 모든 학생에 대한 데이터를 수집하는 경우 해당 데이터는 전체 모집단을 나타내며 위의 함수를 사용하여 모집단 분산을 계산합니다.

10명의 학생 그룹(B2:B11)의 시험 점수가 있다고 가정해 보겠습니다. 점수는 전체 모집단을 구성하므로 다음 공식으로 분산을 수행합니다.

=VARP(B2:B11)

=VAR.P(B2:B11)

=VARPA(B2:B11)

그리고 모든 수식은 동일한 결과를 반환합니다.

Excel이 분산을 올바르게 수행했는지 확인하려면 아래 스크린샷에 표시된 수동 var 계산 공식을 사용하여 확인할 수 있습니다.

 

일부 학생이 시험에 응시하지 않고 점수 번호 대신 N/A가 있는 경우 VARPA 함수는 다른 결과를 반환합니다. 그 이유는 VARPA가 텍스트 값을 0으로 평가하는 반면 VARP 및 VAR은 0으로 평가하기 때문입니다. P는 참조의 텍스트와 논리값을 무시합니다. 

 

Excel의 분산 수식 - 사용법 참고 사항

Excel에서 분산 분석을 올바르게 수행하려면 다음과 같은 간단한 규칙을 따르십시오.

• 변수를 값, 배열 또는 셀 참조로 제공합니다.
  
• Excel 2007 이상에서는 표본 또는 모집단에 해당하는 최대 255개의 인수를 제공할 수 있으며 Excel 2003 이상에서는 최대 30개의 인수를 제공할 수 있습니다.
  
• 빈 셀, 텍스트 및 논리적 값을 무시하고 참조의 숫자만 평가하려면 VAR 또는 VAR.S 함수를 사용하여 표본 분산을 계산하고 VARP 또는 VAR.P를 사용하여 모집단 분산을 찾습니다.
  
• 참조의 논리 및 텍스트 값을 평가하려면 VARA 또는 VARPA 함수를 사용합니다.
  
• Excel에서 표본 분산 공식에 두 개 이상의 숫자 값을 제공하고 모집단 분산 공식에 하나 이상의 숫자 값을 제공하지 않으면 #DIV/0! 오류가 발생합니다.
  
• 숫자로 해석할 수 없는 텍스트를 포함하는 인수는 #VALUE! 오류를 발생시킵니다.