5-82. 스프레드시트 드릴다운 기능

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

📥 드릴다운 기능 스프레드시트 바로가기

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

 

 

🎯 주제 설명: 드릴다운의 개념과 필요성

 

드릴다운(Drill Down)이란 큰 범위의 정보에서 시작해서 점점 더 세부적인 정보로 파고들어 가는 분석 방법입니다. 마치 망원경으로 먼 곳을 보다가 점점 확대해서 세부 사항을 관찰하는 것과 같아요!

 

왜 드릴다운이 중요할까요?

중학생 여러분이 학급 성적을 분석한다고 생각해보세요. 처음에는 "우리 학교 전체 평균은 80점이야"라는 큰 그림을 보지만, 더 자세히 알고 싶으면:

·        1단계: 학교 전체 평균 → 학년별 평균

·        2단계: 학년별 평균 → 반별 평균

·        3단계: 반별 평균 → 개별 학생 점수

이처럼 단계별로 깊이 파고들어 가는 것이 바로 드릴다운입니다!

 

스프레드시트 드릴다운의 핵심 특징

1. 계층적 데이터 구조

·        상위 레벨: 전체적인 요약 정보

·        하위 레벨: 세부적인 상세 정보

·        각 단계가 자연스럽게 연결됨

2. 동적 분석 능력

·        클릭 한 번으로 세부 정보 확인

·        실시간으로 데이터 업데이트

·        여러 조건을 동시에 적용 가능

3. 효율적인 의사결정

·        문제가 있는 부분을 빠르게 찾아냄

·        원인 분석이 쉬워짐

·        개선 방안을 구체적으로 도출

 

스프레드시트에서 드릴다운이 필요한 이유

데이터가 너무 많을 때: 전교생 1000명의 성적을 한번에 보기는 어렵죠. 하지만 학년→반→개인 순으로 나누면 쉽게 파악할 수 있어요.

패턴을 찾고 싶을 때: "왜 이번 시험에서 수학 점수가 낮았을까?"라는 질문에 대한 답을 찾으려면, 전체→학년별→반별→개인별로 차근차근 분석해야 해요.

효과적인 보고를 위해: 선생님께 보고할 때도 "전체적으로는 이렇고, 세부적으로는 이런 문제가 있어요"라고 단계별로 설명하면 더 이해하기 쉬워요.

 

 

🛠️ 실습 방법: 단계별 적용법

 

1단계: 기본 드릴다운 구조 이해하기

드릴다운을 만들기 위해서는 먼저 계층구조를 이해해야 해요.

예시: 학교 성적 관리 시스템

레벨 1: 전국 → 시도별
레벨 2: 시도별 → 시군구별 
레벨 3: 시군구별 → 학교별
레벨 4: 학교별 → 학급별
레벨 5: 학급별 → 개별 학생

이렇게 5단계로 나누면 전국의 모든 학생 데이터를 체계적으로 분석할 수 있어요!

 

2단계: 핵심 함수들 마스터하기

드릴다운에서 가장 중요한 함수들을 배워봅시다.

SUMIFS 함수 (조건부 합계)

=SUMIFS(합계범위, 조건범위1, 조건1, 조건범위2, 조건2)

실제 사용 예:

=SUMIFS(점수열, 지역열, "서울", 과목열, "수학")

"서울 지역의 수학 점수 총합을 구해줘"라는 뜻이에요.

AVERAGEIFS 함수 (조건부 평균)

=AVERAGEIFS(평균범위, 조건범위1, 조건1, 조건범위2, 조건2)

COUNTIFS 함수 (조건부 개수)

=COUNTIFS(조건범위1, 조건1, 조건범위2, 조건2)

 

3단계: 데이터 구조 설계하기

효과적인 드릴다운을 위한 데이터 구조를 만들어봅시다.

상세 데이터 시트 (원본 데이터)

·        A열: 고유번호

·        B열: 시도 (서울, 부산, 대구...)

·        C열: 시군구 (강남구, 서초구...)

·        D열: 점수

·        E열: 학생수

·        F열: 과목 (수학, 국어, 영어...)

·        G열: 학년 (1, 2, 3학년)

요약 데이터 시트 (드릴다운 시트)

·        A열: 번호

·        B열: 시도

·        C열: 시군구

·        D열: 평균점수 (수식으로 계산)

 

4단계: 드릴다운 수식 작성하기

이제 실제로 드릴다운 수식을 만들어보겠습니다.

1차 드릴다운: 지역별 평균 계산

=AVERAGEIFS(상세데이터!D:D, 상세데이터!B:B, B2, 상세데이터!C:C, C2)

이 수식의 의미:

·        상세데이터!D:D: 점수 열에서 평균을 구해라

·        상세데이터!B:B, B2: 시도가 B2와 같은 것만

·        상세데이터!C:C, C2: 시군구가 C2와 같은 것만

2차 드릴다운: 과목별 세부 분석

=AVERAGEIFS(상세데이터!D:D, 상세데이터!B:B, "서울", 상세데이터!F:F, "수학")

3차 드릴다운: 학년별 추가 분석

=AVERAGEIFS(상세데이터!D:D, 상세데이터!B:B, "서울", 상세데이터!F:F, "수학", 상세데이터!G:G, 1)

 

5단계: 동적 드릴다운 만들기

사용자가 선택한 조건에 따라 자동으로 결과가 바뀌는 동적 드릴다운을 만들어보세요.

드롭다운 리스트 만들기:

1.      데이터 → 데이터 유효성 검사

2.     기준: 목록

3.      범위: 시도 목록이 있는 셀 범위

동적 수식 작성:

=AVERAGEIFS(상세데이터!D:D, 상세데이터!B:B, 선택셀, 상세데이터!F:F, 과목선택셀)

 

6단계: 시각적 대시보드 구성하기

드릴다운 결과를 보기 쉽게 만들기 위해 대시보드를 구성해보세요.

상위 5개 지역 자동 추출:

=LARGE(점수범위, 1)  # 1등
=LARGE(점수범위, 2)  # 2등
=LARGE(점수범위, 3)  # 3등

해당 지역명 자동 표시:

=INDEX(지역범위, MATCH(LARGE(점수범위,1), 점수범위, 0))

 

 

📊 CSV 예시 데이터

번호,예시데이터1,예시데이터2,결과값
1,서울,강남구,=SUMIFS(상세데이터!D:D,상세데이터!B:B,B2,상세데이터!C:C,C2)
2,서울,서초구,=SUMIFS(상세데이터!D:D,상세데이터!B:B,B3,상세데이터!C:C,C3)
3,서울,송파구,=SUMIFS(상세데이터!D:D,상세데이터!B:B,B4,상세데이터!C:C,C4)
4,서울,영등포구,=SUMIFS(상세데이터!D:D,상세데이터!B:B,B5,상세데이터!C:C,C5)
5,서울,마포구,=SUMIFS(상세데이터!D:D,상세데이터!B:B,B6,상세데이터!C:C,C6)

 

 

🌟 실무 및 교육 현장 활용 사례

 

사례 1: 중학생 민수의 학급 성적 분석 프로젝트

상황: 중학교 2학년 민수는 학급 반장으로서 우리 반 성적을 분석해서 선생님께 보고하는 과제를 받았습니다. 전교 20개 반, 총 600명의 성적 데이터를 어떻게 분석해야 할지 막막했어요.

드릴다운 적용 과정:

1단계: 전체 구조 설계
민수는 먼저 분석 단계를 다음과 같이 나눴어요:

·        레벨 1: 전교 평균

·        레벨 2: 학년별 평균 (1, 2, 3학년)

·        레벨 3: 반별 평균 (각 학년 7개 반씩)

·        레벨 4: 과목별 평균 (국어, 수학, 영어, 과학, 사회)

·        레벨 5: 개별 학생 점수

2단계: 상세 데이터 정리
스프레드시트의 '원본데이터' 시트에 다음과 같이 정리했어요:

A열: 학번 (20240101, 20240102...)
B열: 학년 (1, 2, 3)
C열: 반 (1반, 2반, 3반...)
D열: 이름
E열: 국어점수
F열: 수학점수
G열: 영어점수
H열: 과학점수
I열: 사회점수

3단계: 드릴다운 수식 작성
'분석결과' 시트에서 다음 수식들을 사용했어요:

학년별 평균 계산:

=AVERAGEIFS(원본데이터!E:E, 원본데이터!B:B, 2)  # 2학년 국어 평균

반별 평균 계산:

=AVERAGEIFS(원본데이터!E:E, 원본데이터!B:B, 2, 원본데이터!C:C, "5반")  # 2학년 5반 국어 평균

과목별 최고점 찾기:

=MAXIFS(원본데이터!F:F, 원본데이터!B:B, 2, 원본데이터!C:C, "5반")  # 2학년 5반 수학 최고점

4단계: 문제점 발견
드릴다운 분석을 통해 민수는 다음과 같은 패턴을 발견했어요:

·        전교 평균: 78점

·        2학년 평균: 75점 (전교 평균보다 3점 낮음!)

·        2학년 5반 평균: 72점 (2학년 평균보다도 3점 낮음!)

·        특히 수학이 68점으로 가장 낮음

5단계: 해결방안 도출
민수는 드릴다운 결과를 바탕으로 구체적인 개선 방안을 제시했어요:

·        수학 스터디 그룹 결성

·        수학 우수 학생들의 멘토링 시스템 구축

·        주 2회 수학 문제풀이 시간 운영

결과: 선생님과 반 친구들이 민수의 체계적인 분석에 감탄했고, 실제로 제안한 방안들을 실행하여 다음 시험에서 수학 평균이 7점 향상되었어요!

 

사례 2: 학교 도서관의 독서 활동 분석

상황: 우리 학교 도서부 부장인 지은이는 도서관 이용 현황을 분석해서 독서 활성화 방안을 찾고 싶었습니다. 1년간 누적된 대출 기록이 무려 5,000건이나 되어서 어디서부터 분석해야 할지 고민이었어요.

드릴다운 설계:

레벨 1: 전체 현황

·        월별 총 대출 건수

·        전체 평균 대출 권수

레벨 2: 학년별 분석

·        학년별 대출 현황

·        학년별 선호 장르

레벨 3: 장르별 세부 분석

·        소설, 과학, 역사, 만화 등 장르별 대출 현황

·        월별 장르 트렌드

레벨 4: 개별 도서 분석

·        가장 인기 있는 책 TOP 20

·        가장 안 읽히는 책 찾기

핵심 수식 활용:

# 학년별 월간 대출 건수
=COUNTIFS(대출기록!학년열, 1, 대출기록!월열, 3)
# 장르별 평균 대출 기간
=AVERAGEIFS(대출기록!기간열, 대출기록!장르열, "소설")
# 가장 인기 있는 책 찾기
=INDEX(도서목록!제목열, MATCH(MAX(대출통계!횟수열), 대출통계!횟수열, 0))

발견한 패턴:

·        3월, 9월에 대출이 급증 (새 학기 효과)

·        1학년은 만화, 3학년은 소설 선호

·        과학 도서의 대출률이 현저히 낮음 (전체의 5%만)

개선 방안:
지은이는 분석 결과를 바탕으로 다음과 같은 제안을 했어요:

·        과학 도서 코너에 재미있는 실험 관련 책 비치

·        학년별 맞춤형 추천 도서 코너 신설

·        새 학기마다 특별 이벤트 개최

 

사례 3: 동아리 활동 예산 관리

상황: 학생회 총무인 현우는 여러 동아리의 예산 사용 내역을 관리하고 분석해야 했습니다. 20개 동아리, 월별 예산 사용 내역을 체계적으로 정리하고 싶었어요.

드릴다운 구조:

1차: 동아리별 전체 예산 사용률

=SUMIFS(예산사용!금액열, 예산사용!동아리열, "과학동아리")

2차: 월별 사용 패턴 분석

=SUMIFS(예산사용!금액열, 예산사용!동아리열, "과학동아리", 예산사용!월열, 3)

3차: 카테고리별 세부 분석

=SUMIFS(예산사용!금액열, 예산사용!동아리열, "과학동아리", 예산사용!카테고리열, "재료비")

현우가 발견한 문제점:

·        일부 동아리는 예산의 80%를 마지막 달에 몰아서 사용

·        재료비 비중이 너무 높은 동아리들

·        예산을 전혀 사용하지 않는 비활성 동아리들

해결책:

·        분기별 예산 사용 계획서 제출 의무화

·        예산 사용률이 낮은 동아리에 대한 컨설팅 제공

·        효율적 예산 사용 우수 동아리 시상

 

사례 4: 급식 만족도 조사 분석

상황: 급식 운영위원으로 활동하는 서연이는 전교생 대상 급식 만족도 조사 결과를 분석해야 했습니다. 총 800명의 응답 데이터를 어떻게 의미 있게 분석할지 고민했어요.

다차원 드릴다운 분석:

1차원: 전체적인 만족도

·        전체 평균 만족도: 3.2/5.0

2차원: 학년별 만족도

·        1학년: 3.5/5.0

·        2학년: 3.1/5.0

·        3학년: 2.9/5.0

3차원: 요일별 만족도

·        월요일: 3.8/5.0 (가장 높음)

·        금요일: 2.8/5.0 (가장 낮음)

4차원: 메뉴별 세부 분석

=AVERAGEIFS(설문결과!만족도열, 설문결과!요일열, "금요일", 설문결과!메뉴열, "카레라이스")

서연이의 분석 결과:

·        학년이 올라갈수록 만족도 하락

·        금요일 메뉴에 대한 불만이 집중됨

·        특히 "생선 메뉴"에 대한 만족도가 현저히 낮음

개선 제안:

·        금요일 메뉴 다양화

·        학년별 선호도를 고려한 메뉴 구성

·        월 1회 '학생 희망 메뉴' 반영

 

이처럼 드릴다운 기능을 활용하면 복잡한 데이터 속에서도 의미 있는 패턴을 찾아내고, 구체적인 문제 해결 방안을 도출할 수 있어요. 여러분도 학교생활의 다양한 상황에서 드릴다운 분석을 활용해보세요!