3-53. 스프레드시트 고급 함수 조합

스프레드시트의 고급함수(ARRAYFORMULA, INDEX/MATCH, QUERY 등)를 조합해 반복 작업을 자동화하고, 데이터 분석·시각화를 한 번에 처리할 수 있는 템플릿 갤러리를 제공합니다. 중학교 2학년도 이해할 수 있는 쉬운 설명과 단계별 실습, CSV 및 XLSX 예시 파일을 함께 활용하세요.

 

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

📥 고급 함수 조합 스프레드시트 바로가기

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

 

 

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

스프레드시트 고급함수 조합이란

·        ARRAYFORMULA로 여러 행에 함수를 한 번에 적용

·        INDEX/MATCH로 VLOOKUP 한계를 보완

·        QUERY로 SQL처럼 데이터 필터·집계

·        이들을 함께 조합해 복잡한 분석을 자동화하는 기법

필요성

·        방대한 데이터를 수작업으로 처리할 때 발생하는 오류 방지

·        반복적인 계산·필터링 작업을 클릭 한 번으로 처리

·        대시보드 형태로 실시간 업데이트 가능한 시각화 구현

 

 

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

1.      RAW 데이터 준비

    o   번호, 예시데이터1(입력값), 예시데이터2(참조값) 컬럼 구성

2.     ARRAYFORMULA 적용

     o   D열 전체에 =ARRAYFORMULA(IF(B2:B="", "", B2:B + C2:C)) 입력

3.      INDEX/MATCH 활용

     o   별도 참조 시트에서 키값 기준으로 정보 가져오기

     o   예: =INDEX(참조시트!D:D, MATCH(B2, 참조시트!A:A, 0))

4.     QUERY 함수로 요약 테이블 생성

     o   =QUERY(A1:D, "select B, sum(D) where B is not null group by B", 1)

5.      대시보드 시트에 임베드

     o   차트 삽입, 조건부 서식, 슬라이서 추가

 

 

3) CSV 예시 데이터

번호,예시데이터1,예시데이터2,결과값
1,10,5,=B2+C2→15
2,20,7,=B3+C3→27
3,15,9,=B4+C4→24
4,30,12,=B5+C5→42
5,25,8,=B6+C6→33

·        RawData 50개 이상 준비된 CSV 파일을 첨부합니다.

 

 

4) XLSX 예시 템플릿 갤러리

·        Sheet1 (RawData): 위 CSV 데이터를 붙여넣기

·        Sheet2 (결과 자동 계산)

     o   D2셀: =ARRAYFORMULA(IF(B2:B="", "", B2:B + C2:C))

·        Sheet3 (참조Lookup)

     o   A2셀: =INDEX(RawData!D:D, MATCH(Sheet3!B2, RawData!B:B, 0))

·        Dashboard

     o   B열 합계 차트, C열 평균 차트, 스파크라인 등 시각화

템플릿 갤러리 스타일:

·        각 시트별 구분 색상

·        헤더 행에 굵은 테두리와 배경색

·        대시보드에 카드형 레이아웃

 

 

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

사례 1: 학교 과제 점수 집계

·        학생별 과제 점수(B열)와 출석 점수(C열)을 합산해 총점 자동 계산

·        데이터가 추가되면 ARRAYFORMULA로 실시간 업데이트

사례 2: 동아리 활동비 관리

·        항목별 지출 내역을 INDEX/MATCH로 예산표에서 자동 조회

·        QUERY로 월별 지출 합계 리포트 생성

사례 3: 중학생 과학 실험 데이터 분석

·        실험 기기별 측정값을 QUERY로 필터링해 그래프로 즉시 시각화

·        스프레드시트 대시보드에 삽입해 발표 자료로 활용

 

 

모든 사례는 함수 조합으로 반복 작업을 “클릭 한 번”에 끝내고, 오류 없이 즉시 시각화할 수 있다는 점을 강조합니다.