2-29. 통계분석 스프레드시트 교육 가이드

통계분석은 데이터에서 의미 있는 패턴과 인사이트를 발견하는 핵심 도구입니다. Google 스프레드시트의 통계함수(AVERAGE, STDEV, CORREL)와 조건부 집계함수(AVERAGEIF, COUNTIF)를 활용해 원시 데이터부터 시각적 대시보드까지 구현하는 전 과정을 단계별로 안내합니다.

 

🔗 지금 바로 사용해보세요!

📥 통계분석 스프레드시트 바로가기

클릭 한 번으로 바로 접속해서 복사하여 사용하실 수 있습니다

 

 

1. 주제 설명: 개념과 필요성

통계분석은 수집된 데이터에서 중심경향성, 산포도, 상관관계 등을 계산하여 데이터의 특성을 파악하고 예측 모델의 근거를 제공하는 분석 방법입니다.

 

스프레드시트 통계분석의 장점:

·        접근성: 별도 통계 소프트웨어 없이 무료로 고급 분석 가능

·        시각화: 차트와 함수를 연동해 실시간 업데이트되는 대시보드 구현

·        협업: 클라우드 기반으로 팀 단위 데이터 분석 및 공유

·        확장성: QUERY 함수, 피벗테이블 등과 연계한 고급 분석 가능

 

활용 분야:

·        학습 성과 분석 (학습시간 vs 성적 상관관계)

·        지역별 매출 비교 (평균, 표준편차 기반 지역 특성 파악)

·        품질관리 (불량률 통계적 모니터링)

·        설문조사 결과 분석 (응답 분포 및 그룹별 차이 검정)

 

 

2. 실습 방법: 단계별 적용법

 

1단계: 데이터 구조 설계

·        RawData 시트: 원시 데이터 입력 영역 (번호, 학습시간, 지역, 성적, 등급)

·        Statistics 시트: 통계 함수 기반 집계 테이블

·        Dashboard 시트: KPI 지표 및 차트 시각화

 

2단계: 원시 데이터 입력 및 등급 자동 계산

·        E2 셀에 =IF(D2>=90,"A",IF(D2>=80,"B","C")) 수식 입력

·        2행부터 51행까지 드래그하여 50개 데이터 전체에 적용

 

3단계: Statistics 시트 통계 함수 구현

·        전체 평균: =AVERAGE(RawData!D:D)

·        표준편차: =STDEV(RawData!D:D)

·        지역별 조건부 평균: =AVERAGEIF(RawData!C:C,"서울",RawData!D:D)

·        최댓값/최솟값: =MAX(RawData!D:D), =MIN(RawData!D:D)

 

4단계: Dashboard 상관분석 및 분포 계산

·        상관계수: =CORREL(RawData!B:B,RawData!D:D) (학습시간 vs 성적)

·        등급별 개수: =COUNTIF(RawData!E:E,"A")

·        KPI 셀에서 Statistics 시트 직접 참조

 

5단계: 차트 연동 및 시각화

·        지역별 평균 막대차트: Statistics 시트 B2:G2 범위 활용

·        등급 분포 파이차트: Dashboard 등급별 개수 데이터 활용

 

 

3. CSV 예시 데이터

RawData에 적용된 50개 데이터 중 앞 5개 샘플(제목행 포함)입니다.

번호,학습시간,지역,성적,등급
1,8.0,서울,82.7,=IF(D2>=90,"A",IF(D2>=80,"B","C"))
2,1.3,부산,61.0,=IF(D3>=90,"A",IF(D3>=80,"B","C"))
3,4.0,대구,69.7,=IF(D4>=90,"A",IF(D4>=80,"B","C"))
4,3.5,인천,74.9,=IF(D5>=90,"A",IF(D5>=80,"B","C"))
5,9.1,광주,88.8,=IF(D6>=90,"A",IF(D6>=80,"B","C"))

·        B열(학습시간): 1.0~12.0시간 범위의 다양한 시나리오

·        C열(지역): 통계 분석 그룹화 기준 (서울/부산/대구/인천/광주)

·        D열(성적): 상관분석 대상이 되는 계산 범위 (60~100점)

·        E열(등급): IF 중첩 함수로 성적 구간별 자동 등급 부여

 

 

4. XLSX 예시 템플릿 갤러리

갤러리 스타일 완성 템플릿으로 다음 기능을 구현했습니다:

 

🔵 RawData 시트

·        50개 학습 데이터와 E열 등급 자동 계산 수식 삽입

·        헤더 파란색 배경, 데이터 영역과 수식 영역 구분

 

🟢 Statistics 시트

·        전체/지역별 6가지 통계 지표 (평균, 최댓값, 최솟값, 표준편차, 분산, 중앙값)

·        AVERAGE, STDEV, AVERAGEIF, MAXIFS 등 통계함수 종합 활용

·        조건부 집계로 지역별 비교 분석 가능

 

🟠 Dashboard 시트

·        📊 주요지표: 전체 평균, 최고 성적, 표준편차

·        📈 상관분석: CORREL 함수로 학습시간과 성적 간 상관계수 계산

·        🏆 등급분포: COUNTIF로 A/B/C 등급별 인원 집계

·        시각화: 지역별 평균 막대차트 + 등급 분포 파이차트

 

스타일링:

·        시트별 탭 컬러 (파랑/초록/주황)

·        섹션별 배경색 구분 및 이모지 활용

·        KPI 수치 반올림 및 가독성 최적화

 

 

5. 실무 및 교육 현장 활용 사례

 

📚 교육 현장

·        학급 성적 관리: 과목별/학기별 성적 분포 분석으로 교육과정 개선

·        통계학 실습: 대학 통계학과에서 실제 데이터 기반 상관분석 실습 도구

 

🏢 기업 실무

·        직원 교육 성과 측정: 교육시간 투입 대비 성과 지표 ROI 계산

·        지역별 매출 분석: 브랜치별 성과 비교 및 표준편차 기반 이상치 탐지

 

🔬 연구 분야

·        설문조사 분석: 응답자 그룹별 평균 차이 및 분산 검정

·        품질관리: 제품별 불량률 통계 모니터링 및 관리도 구현

 

💼 컨설팅

·        고객사 데이터 진단: Excel/CSV로 받은 데이터를 즉시 통계 분석 후 인사이트 제공