Excel에서 정규 분포 벨 곡선을 만드는 방법

이 자습서는 모든 버전의 Excel에서 정규 분포 벨 곡선을 만드는 방법을 보여줍니다 : 2007, 2010, 2013 , 2016 및 2019.

통계에서 종 곡선 (표준 정규 분포 또는 가우스 곡선이라고도 함)은 데이터가 클러스터되는 경향을 나타내는 대칭 그래프입니다. 주어진 데이터 세트에서 중심 값 또는 평균 주위.

Y 축은 데이터 세트에서 발생하는 주어진 값의 상대 확률을 나타내는 반면 x 축은 차트에 값 자체를 그려 종 모양을 만듭니다. 곡선, 따라서 이름입니다.

그래프는 특정 값이 예상 변동의 일부인지 또는 통계적으로 유의한지 여부를 분석하는 데 도움이되므로 더 면밀히 조사해야합니다.

Excel에는 제공 할 기본 제공 솔루션이 없으므로 직접 플로팅해야합니다. 그렇기 때문에 몇 번의 클릭만으로 고급 Excel 차트를 작성할 수있는 도구 인 Chart Creator Add-in을 개발했습니다.

이 단계에서는- 단계별 자습서에서는 Excel에서 정규 분포 종형 곡선을 처음부터 만드는 방법을 배웁니다.

To 가우스 곡선을 그리려면 다음 두 가지를 알아야합니다.

• 평균 (표준 측정이라고도 함). 이것은 곡선의 중심을 결정하며, 이는 곡선의 위치를 나타냅니다.
• 측정의 표준 편차 (SD). 이것은 정규 분포에서 데이터의 산포를 정의합니다. 또는 일반 영어로 곡선의 너비를 정의합니다. 예를 들어, 위에 표시된 종형 곡선에서 평균의 표준 편차 1 개는 시험 점수 53 점과 85 점 사이의 범위를 나타냅니다.

SD가 낮을수록 곡선이 커지고 작습니다. 데이터가 분산되고 그 반대도 마찬가지입니다.

모든 정규 분포 곡선에 적용 할 수있는 68-95-99.7 규칙을 언급 할 가치가 있습니다. 즉, 데이터의 약 68 %가 배치 될 것입니다. 평균에서 1 SD 이내, 2 SD 이내 95 %, 3 SD 이내 99.7 %.

이제 필수 사항을 알고 이론에서 실습으로 이동해 보겠습니다.

시작하기

설명을 위해 200 명의 학생이 시험 점수를 받았으며 이들을 “곡선으로 채점”한다고 가정 해 보겠습니다. , 는 학생의 성적이 나머지 수업에 대한 상대적 성과를 기반으로한다는 것을 의미합니다.

1 단계 : 평균을 찾습니다.

일반적으로 평균이 제공됩니다. 처음부터 SD 값이 있지만 그렇지 않은 경우 몇 가지 간단한 단계만으로 이러한 값을 쉽게 계산할 수 있습니다. 먼저 평균을 살펴 보겠습니다.

평균은 표본 또는 데이터 모집단의 평균 값을 나타내므로 AVERAGE 함수를 사용하여 표준 측정 값을 찾을 수 있습니다.

다음 공식을 입력합니다. 데이터 세트의 시험 점수 평균을 계산하려면 실제 데이터 (열 A 및 B) 옆에있는 빈 셀 (이 예에서는 F1)에 삽입합니다.

빠른 메모 : 종종 수식 출력을 반올림해야 할 수도 있습니다. 이렇게하려면 다음과 같이 ROUND 함수로 래핑하면됩니다.

2 단계 : 표준 편차를 찾습니다.

1 아래로, 갈 것입니다. 다행히 Excel에는 표준 편차를 찾는 모든 더러운 작업을 수행하는 특수 기능이 있습니다.

다시 공식은 지정된 셀 범위 (B2 : B201)에서 모든 값을 선택하고 표준 편차를 계산합니다. 반올림하는 것을 잊지 마세요. 출력.

3 단계 : 곡선의 x 축 값을 설정합니다.

기본적으로 차트는 막대한 수의 간격 ( 부드러운 곡선을 만들기 위해 선과 함께 연결되는 단계로 생각하십시오.

이 경우 x 축 값은 특정 시험 점수를 나타내는 데 사용되며 y 축 값은 학생이 시험에서 해당 점수를받을 확률을 알려줍니다.

기술적으로 원하는만큼 간격을 포함 할 수 있습니다. 나중에 수평 축 눈금을 수정하여 중복 데이터를 쉽게 지울 수 있습니다. 세 가지 표준 편차를 통합 할 범위를 선택하세요.

1부터 카운트를 시작하고 (학생이 음수 시험 점수를받을 수있는 방법이 없기 때문에) 150-다른 도우미 테이블을 설정하는 것이 150인지 1500인지는 중요하지 않습니다.

1. 차트 데이터 (예 : E4) 아래에있는 빈 셀을 선택하고 “1”을 입력합니다. 첫 번째 간격을 정의하는 값입니다.
2. 홈 탭으로 이동합니다.
3. 편집 그룹에서 “채우기”를 선택합니다.
4. “시리즈 인”에서 “”열 “을 선택합니다.
5. “단계 값 “에”1 “을 입력합니다. 이 값은 Excel이 마지막 간격에 도달 할 때까지 자동으로 추가되는 증분을 결정합니다.
6. “중지 값”에 마지막 간격을 나타내는 값인 “150”을 입력하고 “확인”을 클릭합니다. ”

이상하게도 E 열에 149 개의 셀 (E5 : E153)은 2에서 150까지의 값으로 채워졌습니다.

참고 : 스크린 샷에 표시된대로 원래 데이터 셀을 숨기지 마십시오. 그렇지 않으면 기술이 작동하지 않습니다.

4 단계 : 모든 x 축 값에 대한 정규 분포 값을 계산합니다.

이제 정규 분포 값 (특정 x 축 값으로 표시된 특정 시험 점수를 학생이받을 확률)을 찾습니다. — 각 구간마다. 다행히도 Excel에는 NORM.DIST 함수라는 이러한 모든 계산을 수행 할 수있는 도구가 있습니다.

다음 수식을 오른쪽에있는 셀 (F4)에 입력합니다. 첫 인터벌 (E4) :

다음은 그에 따라 조정하는 데 도움이되는 디코딩 된 버전입니다.

나머지 간격 (E5 : E153)에 대한 공식을 쉽게 실행할 수 있도록 평균 및 SD 값을 잠급니다.

이제 채우기 핸들을 두 번 클릭하여 수식을 나머지 셀 (F5 : F153)에 복사합니다.

5 단계 : 부드러운 선으로 산점도를 만듭니다.

마지막으로 종형 곡선을 만들 시간입니다.

1. x 및 y 축 값 (E4 : F153)이 포함 된 도우미 테이블에서 값을 선택합니다.
   1. li>
   2. 삽입 탭으로 이동합니다.
   3. “분산 형 차트 (X, Y) 또는 거품 형 차트 삽입”버튼을 클릭합니다.
   4. “부드러운 선으로 분산 형”을 선택합니다.

   

   6 단계 : 라벨 테이블을 설정합니다.

   기술적으로 종형 곡선이 있습니다. 그러나 그것을 설명하는 데이터가 없기 때문에 읽기가 어렵습니다.

   평균 위와 아래의 모든 표준 편차 값을 나타내는 레이블을 추가하여 정규 분포를보다 유익하게 만들어 보겠습니다 ( 대신 z 점수를 표시하는 데 사용할 수도 있습니다.

   이를 위해 다음과 같이 또 다른 도우미 테이블을 설정합니다.

   

   먼저 X- 값 (I5) 열의 해당 셀 옆에 평균값 (F1)을 복사합니다.

   

   다음으로 다음 간단한 공식을 셀 I4에 입력하여 평균 이하의 표준 편차 값을 계산합니다.

   1	= I5- $ F $ 2

   간단히 말하면 공식은 평균에서 이전 표준 편차 값의 합계를 뺍니다. 이제 채우기 핸들을 위로 드래그하여 나머지 두 셀 (I2 : I3)에 수식을 복사합니다.

   

   미러 공식을 사용하여 평균 이상의 표준 편차에 대해 동일한 프로세스를 반복합니다.

   1	= I5 + $ F $ 2

   같은 방식으로 다른 두 셀 (I7 : I8).

   

   마지막으로 원하는대로 y 축 레이블 값 (J2 : J8)을 0으로 채 웁니다. 가로 축에 데이터 마커를 배치합니다.

   

   7 단계 : 레이블 데이터를 차트에 삽입합니다.

   이제 준비한 모든 데이터를 추가하십시오. 차트 그림을 마우스 오른쪽 버튼으로 클릭하고 “데이터 선택”을 선택합니다.

   

   대화 상자가 나타나면 ” 추가.”

   

   도우미 테이블에서 각 셀 범위 ( “시리즈 X 값”및 J2의 경우 I2 : I8)를 강조 표시합니다. “시리즈 Y 값”에 대해 : J8을 입력하고 “확인”을 클릭합니다.

   

   8 단계 : 차트 유형 변경 레이블 시리즈.

   다음 단계는 새로 추가 된 시리즈의 차트 유형을 변경하여 데이터 마커가 점으로 표시되도록하는 것입니다. 이렇게하려면 차트 그림을 마우스 오른쪽 버튼으로 클릭하고 “변경 차트 유형.”

   

   다음으로 콤보 차트를 디자인합니다.

   1. 콤보 탭.
   2. 시리즈 “Series2″의 경우 “Chart Type”을 “Scatter”로 변경합니다.
      • 참고 : “Series1″이 “Scatter with Smooth Lines”로 유지되는지 확인합니다. 때로는 콤보를 만들 때 Excel에서이를 변경합니다. 또한 “Series1″이 보조 축에 푸시되지 않았는지 확인합니다. 차트 유형 옆의 확인란은 표시하지 않아야합니다.
   3. 확인을 클릭합니다.

   

   9 단계 : 가로 축 눈금을 수정합니다.

   가로 축 눈금을 조정하여 종형 곡선의 중앙에 차트를 배치합니다. 가로 축을 마우스 오른쪽 버튼으로 클릭하고 선택 메뉴에서 “축 서식 지정”.

   

   작업 창이 나타나면 다음을 수행하십시오.

   • 축 옵션 탭으로 이동합니다.
   • 최소 경계 값을 “15”로 설정합니다.
   • 최대 경계 값을 “125”로 설정합니다.

   적합한 것으로 보이지만 축 스케일 범위를 조정할 수 있지만 표준 편차 범위를 알고 있으므로 곡선의 “꼬리”를 표시하기 위해 각 세 번째 표준 편차에서 약간 떨어진 경계 값을 설정합니다. .

   

   10 단계 : 맞춤 데이터 라벨을 삽입하고 배치합니다.

   차트를 다듬을 때 다음 사항을 확인하세요. 사용자 지정 데이터 레이블을 추가합니다. 먼저 시리즈 “Series2″를 나타내는 점을 마우스 오른쪽 버튼으로 클릭하고 “데이터 레이블 추가”를 선택합니다.

   

   다음으로 이전에 설정 한 레이블이있는 기본 레이블을 데이터 마커 위에 배치합니다.

   1. 시리즈 “Series2″데이터 레이블을 마우스 오른쪽 버튼으로 클릭합니다.
   2. “포맷”을 선택합니다. 데이터 레이블.”
   3. 작업 창에서 레이블 옵션 탭으로 전환합니다.
   4. “X 값”상자를 선택합니다.
   5. “Y 값”을 선택 취소합니다. ”상자.
   6. 라벨 위치에서 위를 선택합니다.

   

   또한 이제 격자 선을 제거 할 수 있습니다 (> 삭제를 마우스 오른쪽 버튼으로 클릭).

   11 단계 : 데이터 마커 다시 칠하기 (선택 사항).

   마지막으로 차트 스타일에 맞게 점의 색상을 다시 지정합니다.

   1. 시리즈 “Series2″데이터 레이블을 마우스 오른쪽 버튼으로 클릭합니다.
   2. 클릭 “채우기”버튼.
   3. 표시되는 팔레트에서 색상을 선택합니다.

   

   또한, r 점 주위의 테두리 제거 :

   1. 같은 데이터 마커를 다시 마우스 오른쪽 버튼으로 클릭하고 “윤곽선”을 선택합니다.
   2. “윤곽선 없음”을 선택합니다.

   

   12 단계 : 수직선을 추가합니다 (선택 사항).

   최종 조정으로 다음을 수행 할 수 있습니다. SD 값을 강조하는 데 도움이되도록 차트에 수직선을 추가합니다.

   • 차트 플롯을 선택합니다 (그러면 선이 차트에 직접 삽입됩니다).
   • 이동 삽입 탭으로 이동합니다.
   • “모양”버튼을 클릭합니다.
   • “선”을 선택합니다.

   “SHIFT”키를 누른 상태에서 마우스를 드래그하여 각 점에서 각 선이 종형 곡선과 만나는 위치까지 완벽하게 수직선을 그립니다.

   

   차트 제목 변경 , 개선 된 종형 곡선이 준비되어 귀중한 분포 데이터를 보여줍니다.

   

   그렇게하면됩니다. 이제 데이터 세트를 선택하고 다음 간단한 단계에 따라 정규 분포 벨 곡선을 만들 수 있습니다.

   정규 분포 벨 곡선 템플릿 다운로드

   Excel 용 무료 정규 분포 종 곡선 템플릿을 다운로드하십시오.

   지금 다운로드