강력한 통계 데이터 분석을 위한 5가지 입증된 Excel 기술

방법 1 – 중심 경향성 및 변동성 계산

위의 7학년 학생들의 데이터 세트 점수는 수학을 기반으로 주어졌습니다. , 역사 , 과학 , 및 문학 주제입니다.

1.1 AVERAGE 함수 사용

평균 점수 가 표시됩니다. Ethan 을 위해 C20 셀에 .

여기서는 AVERAGE 함수를 사용했습니다. , 데이터세트의 산술 평균을 반환합니다.

• C20 셀에 다음 수식을 입력하세요.

여기서는 셀 범위 C9:F9 Ethan에 대한 다양한 과목의 점수를 나타냅니다. .

1.2 AVERAGEIF 및 AVERAGEIFS 함수 사용

위 데이터 세트는 AVERAGEIF 의 다양한 용도를 보여줍니다. 및 AVERAGEIFS 기능을 사용하세요.

6학년 학생들이 얻은 점수의 평균을 구하려고 합니다. 이를 위해서는

• B22: 셀에 다음 수식을 입력하세요.

=AVERAGEIF(B5:B16,B19,D5:D16)

여기서 B19 셀 평균 점수를 찾는 기준을 나타냅니다. .

평균 점수를 찾고 싶은 또 다른 상황을 가정해 보겠습니다. 두 가지 기준:자신의에 따라 학생을 선정합니다.  등급 및 섹션 .

• C24 셀에 다음 수식을 입력하세요. :

=AVERAGEIFS($D$5:$D$16,$B$5:$B$16,B19,$C$5:$C$16,C19)

여기서는 셀 범위 $D$5:$D$16 마크 의 셀을 나타냅니다. 열, 셀 범위 $B$5:$B$16 등급 의 셀을 나타냅니다. 열 및 셀 범위 $C$5:$C$16 섹션의 셀을 나타냅니다. 칼럼. 셀 B19 및 C19 두 가지 기준을 나타냅니다. .

1.3 HARMEAN 및 GEOMEAN 함수 활용

데이터로 6개의 숫자가 있다고 가정해 보겠습니다. 숫자는 1,2,3,4,5, 6 입니다. 그러면 조화 평균값은 다음과 같습니다.

Harmonic Mean = 11+1/2+1/3+1/4+1/5+1/66 = 2.4489

GEOMEAN 함수는 기하학적 평균을 계산합니다. 선택한 데이터세트의 기하 평균은 n번째 근을 찾아 계산됩니다. n 을 곱한 후 데이터 세트의 값. 여기, n 데이터 세트에 있는 값의 총 개수입니다. 예를 들어 5 숫자를 데이터세트로 사용합니다. 이는 1, 2, 3, 4, 5입니다. 따라서 기하평균은 그럴 겁니다,

Geometric Mean = 51*2*3*4*5 = 2.6051.

위 이미지는 HARMEAN 을 모두 사용하는 실제 예를 보여줍니다. 및 GEOMEAN 기능을 사용하세요.

• B21 의 조화 평균 계산 공식 셀은:

여기서는 셀 범위 C5:C17 평균 표시의 셀을 나타냅니다. 칼럼.

한 가지는 분명합니다. 이 경우 조화 평균 값은 평균 마크의 평균 값보다 낮습니다. 산술 평균은 82.58입니다. , 조화평균은 82.37 . 이는 평균 점수의 큰 값의 값을 제한한다는 의미입니다. .

• 기하 평균을 구하는 경우 C22: 셀에 다음 수식을 사용했습니다.

조화 평균과 마찬가지로 기하 평균(82.47 )는 산술 평균(82.58)과 다릅니다. . 투자자들은 여러 기간에 걸쳐 행 값이 제공될 때마다 더 정확한 평균 값을 제공하기 때문에 기하 평균을 사용합니다.

1.4 STANDARDIZE 기능 적용

단계 :

• C평균 및 표준편차 계산 데이터 세트의.

• D5: 셀에 다음 수식을 입력하세요.

=STANDARDIZE(C5,$C$20,$C$21)

여기서 C5 셀 평균 점수를 나타냅니다. 매튜 , 셀 $C$20 평균값을 나타냅니다. 및 셀 $C$21 STDEV.P(표준편차)를 의미합니다. 가치.

MODE.SNGL을 사용할 수 있습니다. , 중간 , VAR.S , VAR.P , STDEV.S , 및 STDEV.P Excel에서 데이터를 추가로 통계적으로 분석하는 기능입니다.

방법 2 – 상대적 지위 계산

5번째 를 둘 다 가정해 보겠습니다. 그리고 6번째 - 순위 값은 동일합니다. 이 경우 RANK.EQ 함수는 5 순위를 반환합니다. 두 값 모두에 대해 다음 순위 값은 7 순위가 됩니다. . 여기에는 총점이 있습니다. 7번째 학년 학생 데이터세트로 사용하세요.

여기서는 10일 이라는 것을 알 수 있습니다. 그리고 11일 가치는 묶여 있었다. 따라서 RANK.EQ 함수가 10 순위를 반환했습니다. 두 값 모두에 대해.

• D5 셀에 다음 수식을 적용했습니다. .

=RANK.EQ(C5,$C$5:$C$17,0)

여기서 C5 셀 총점의 첫 번째 셀을 나타냅니다. 열 및 셀 범위 $C$5:$C$17 총점의 셀을 나타냅니다. 칼럼.

여기는 10일 11번째 값이 동률이므로 RANK.AVG 함수는 10.5의 평균을 반환했습니다. 두 값 모두에 대해.

RANK.AVG 함수는 데이터세트의 상대적 순위도 반환합니다. 하지만 동점인 경우에는 평균 순위가 반환됩니다. 묶인 값의 경우. 예를 들어 4번째 라고 가정해 보겠습니다. 5위 값은 동률입니다. 따라서 RANK.AVG 함수는 4.5 순위를 반환합니다. 두 값 모두에 대해. 다음 값의 순위는 6입니다. . 이제 아래 설명된 지침에 따라 RANK.AVG를 활용해 보겠습니다. 데이터를 통계적으로 분석하는 Excel 기능입니다.

• E5: 셀에 다음 수식을 사용했습니다.

=RANK.AVG(C5,$C$5:$C$17,0)

또한 PERCENTRANK.INC를 사용할 수도 있습니다. , PERCENTRANK.EXC , PERCENTILE.INC , PERCENTILE.EXC , QUARTILE.INC 및 QUARTILE.EXC 함수 Excel에서 데이터의 상대적 위치를 계산합니다.

방법 3 – 상관관계 및 회귀 분석

3.1 SLOPE, INTERCEPT, STYEX 함수 사용

STYEX 기능 Y 값 의 표준 오류를 제공합니다. 주어진 X 값에 대해 . 이를 사용하여 Y 값을 예측할 수 있습니다. X 값에서 .

단계 :

• C21: 셀에 아래 수식을 입력하세요.

여기에서는 셀 범위 C5:C17 Y 값의 셀을 나타냅니다. 열 및 셀 범위 B5:B17 X 값의 셀을 나타냅니다. 칼럼.

• ENTER를 누르세요 .

표준 오류가 발생합니다. Y 값 주어진 X 값에 대해 C21. 셀에

3.2 CORREL 함수 적용

CORREL 함수 두 데이터 세트가 얼마나 밀접하게 관련되어 있는지 알아내는 데 도움이 됩니다.

• C19 에서는 다음 공식을 사용했습니다. 셀:

여기서는 C5:C17 범위입니다. 총점의 셀을 나타냅니다. 열 및 범위 D5:D17 평균 표시의 셀을 나타냅니다. 칼럼.

4. 통계 분석을 위한 배열 함수 적용

여기에는 각 Bin Range에 대한 빈도가 있습니다. 다음 그림에서 볼 수 있듯이. FREQUENCY 함수를 사용했습니다. , Excel에서 데이터를 통계적으로 분석하기 위해 가장 일반적으로 사용되는 배열 함수 중 하나입니다.

• C17 셀에 다음 수식을 입력하세요. :

=FREQUENCY(C5:C14,B17:B23)

여기서 B17:B23 범위는 처음 7을 나타냅니다. Bin Ranges의 셀 칼럼.

MODE.MULT 함수를 사용할 수 있습니다. , LINEST 함수 , TREND 기능 및 GROWTH 함수 Excel에서 데이터를 통계적으로 분석합니다.

참고: 이전 버전의 Excel을 사용하는 경우 CTRL + SHIFT + ENTER를 눌러야 할 수도 있습니다. 배열 수식을 사용합니다. 우리는 Excel 365를 사용하므로 ENTER 를 누르면 됩니다. 우리에게 도움이 될 것입니다.

방법 5 – 데이터 분석 도구를 활용하여 이동 평균 계산

위 이미지는 이동평균을 나타냅니다. 우리 데이터세트의 데이터 분석 도구 옵션이 Excel 리본에 없습니다. 기본적으로. 이 기능을 수동으로 활성화해야 합니다. 이 문서를 따라 데이터 분석 도구를 활성화하고 다양한 용도에 대해 알아볼 수 있습니다 .

• 데이터 로 이동 리본의 탭>>데이터 분석을 선택합니다. 분석 의 옵션 그룹.

데이터 분석 위 이미지와 같이 대화 상자가 워크시트에 나타납니다.

• 입력 범위로 이동 필드를 사용하여 평균 표시 셀을 선택합니다. 열>> 출력 범위 를 클릭합니다. 입력란을 클릭하고 G5 셀을 선택합니다.>> 확인을 클릭하세요. .

Excel의 몇 가지 일반적인 데이터 분석 도구

• Anova:단일 요소 → 두 개 이상의 관찰에 대한 분산을 분석합니다.
• Anova:복제의 2가지 요소 → 각 변수 수준의 조합에 대해 두 개의 독립 변수와 다양한 관측값을 사용하여 분산 분석을 생성합니다.
• Anova:복제 없는 2요소 → 변수 수준의 각 조합에 대해 두 개의 독립 변수와 단일 관측값을 사용하여 분산 분석을 생성합니다.
• 상관관계 → 사람 표본에 대해 두 개 이상의 측정값이 있는 경우 가능한 각 측정값 쌍에 대한 상관 계수 행렬이 계산됩니다.
• 공분산 → 사람 표본에 대해 두 개 이상의 측정값이 있는 경우 가능한 각 측정값 쌍에 대해 공분산 계수 행렬이 계산됩니다.
• 기술통계 → 정의된 셀 범위 내 값의 중심 경향, 변동성 및 기타 속성을 요약한 보고서를 생성합니다.
• 지수평활 → 이전 값의 시퀀스와 이전 예측을 사용하여 시퀀스의 다음 값을 예측합니다.
• 2-표본 분산 F-검정 → F-검정을 수행하여 두 분산을 비교합니다.
• 히스토그램 → 선택한 셀 범위 내 값의 빈도 분포를 표 형식으로 표현합니다.
• 난수 생성 → 7가지 잠재적 분포 중 하나를 기반으로 특정 수량의 난수를 생성합니다.
• 순위 및 백분위수 → 순서 및 백분위수 순위와 함께 값 집합의 각 값을 표시하는 테이블을 생성합니다.
• 회귀 → 이렇게 하면 하나의 종속 변수와 하나 이상의 독립 변수를 포함하는 데이터 집합에 적용된 선형 회귀 통계 보고서가 생성됩니다.
• 샘플링 → 지정된 범위의 셀에서 값 샘플을 생성합니다.

데이터 분석 도구에서 다음 분석 도구를 사용할 수 있습니다. .

기억할 사항

• Excel에서 데이터 분석을 수행하기 전에 데이터 유형(예:연속형 또는 범주형)을 명확히 해야 합니다.
• 다음으로 t-test, ANOVA, 회귀, 상관관계 등 풍부한 통계 분석 도구 목록에서 선택해야 합니다.
• 분석을 수행한 후에는 결과를 의미있게 해석하는 것이 중요합니다. 이는 숫자의 의미와 연구 질문과의 연관성을 이해하는 것을 의미합니다.
• 마지막으로 오류를 확인하여 결과를 검증하고 분석이 견고한지 확인하는 것이 중요합니다. 여기에는 이상값 확인, 가정 테스트, 민감도 분석 수행이 포함됩니다.

연습 섹션

Excel 통합문서 , 연습 섹션을 제공했습니다. 워크시트 오른쪽에 있습니다.

연습 문제집의 각 연습문제에는 샘플 연습 섹션이 제공됩니다.

연습 학습서 다운로드

다음 워크북을 다운로드하여 연습해 보세요.