4-74. 스프레드시트 DAX 함수 기초

JS's Spreadsheets 2025. 9. 23. 23:57

안녕하세요! 오늘은 스프레드시트에서 DAX 함수 개념을 활용한 고급 데이터 분석을 배워보겠습니다. DAX가 뭔지 어렵게 느껴지시나요? 걱정하지 마세요! 중학생도 쉽게 이해할 수 있도록 차근차근 설명해드릴게요.

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

📥 DAX 함수 스프레드시트 바로가기

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

💡 DAX 함수란 무엇인가요?

개념 설명

**DAX(Data Analysis Expressions)**는 마이크로소프트의 파워 BI, 엑셀 파워피벗에서 사용하는 고급 데이터 분석 언어예요. 하지만 구글 시트나 일반 스프레드시트에서도 DAX의 핵심 개념들을 기존 함수들의 조합으로 구현할 수 있답니다!

DAX의 핵심 개념들:

· 집계 함수: SUM, AVERAGE, COUNT 등으로 데이터 요약

· 조건부 계산: 특정 조건에 맞는 데이터만 계산

· 시간 인텔리전스: 날짜와 시간 기반 분석

· 관계형 데이터: 여러 테이블 간 연결과 조회

· 동적 필터링: 조건에 따라 자동으로 데이터 선별

스프레드시트에서 DAX 구현하기

DAX 함수 → 스프레드시트 대응표:

DAX 함수	스프레드시트 함수	용도
SUM	SUM	합계 계산
AVERAGE	AVERAGE	평균 계산
CALCULATE	SUMIF, AVERAGEIF	조건부 계산
FILTER	FILTER, QUERY	데이터 필터링
RELATED	VLOOKUP, INDEX+MATCH	관계 테이블 조회
DISTINCTCOUNT	SUMPRODUCT+COUNTIF	고유값 개수
RANKX	RANK	순위 매기기

왜 DAX 개념이 필요한가요?

1. 복잡한 비즈니스 로직 구현

· 단순한 합계가 아닌 조건부 집계 가능

· 예: "서울 지역 전자제품의 평균 매출액"

2. 시간 기반 분석

· 전년 대비, 전월 대비 분석 자동화

· 예: "작년 같은 달 대비 매출 증감률"

3. 다차원 데이터 분석

· 여러 조건을 동시에 고려한 분석

· 예: "1분기 서울 지역 목표 달성 제품 수"

4. 동적 보고서 생성

· 데이터가 바뀌면 자동으로 결과 업데이트

· 매일 새로운 데이터 입력해도 보고서는 자동 완성!

📚 단계별 실습 방법

1단계: 기본 집계 함수 마스터하기

가장 기본이 되는 집계 함수들부터 시작해요!

SUM (합계)

기본: =SUM(D:D) // D열 전체 합계
조건: =SUMIF(C:C,"전자제품",D:D) // 전자제품만 합계
복수조건: =SUMIFS(D:D,C:C,"전자제품",G:G,"서울") // 서울 전자제품만

AVERAGE (평균)

기본: =AVERAGE(F:F) // F열 평균
조건: =AVERAGEIF(C:C,"가구",F:F) // 가구 카테고리만 평균
복수조건: =AVERAGEIFS(F:F,C:C,"가구",G:G,"부산") // 부산 가구만

COUNT (개수)

데이터 개수: =COUNT(D:D) // 숫자 데이터 개수
조건부 개수: =COUNTIF(C:C,"의류") // 의류 카테고리 개수
범위 조건: =COUNTIFS(D:D,">=100000",G:G,"서울") // 서울 10만원 이상

2단계: 조건부 계산 (CALCULATE 개념)

DAX의 CALCULATE 함수는 조건을 걸어서 계산하는 핵심 기능이에요.

단일 조건 계산

=SUMIF(카테고리범위, "전자제품", 매출범위)
=AVERAGEIF(지역범위, "서울", 단가범위)
=COUNTIF(달성률범위, ">=1")

복수 조건 계산 (AND 조건)

=SUMIFS(매출범위, 카테고리범위, "전자제품", 지역범위, "서울")
// 전자제품이면서 서울인 것만

복합 조건 계산 (OR 조건)

=SUMIF(카테고리범위,"전자제품",매출범위) + SUMIF(카테고리범위,"가구",매출범위)
// 전자제품 또는 가구

3단계: 시간 인텔리전스 구현

날짜 데이터를 활용한 분석이 DAX의 강력한 기능 중 하나예요.

월별 집계

1월 매출: =SUMIFS(매출범위, 날짜범위, ">=2025-01-01", 날짜범위, "<2025-02-01")
2월 매출: =SUMIFS(매출범위, 날짜범위, ">=2025-02-01", 날짜범위, "<2025-03-01")

누적 계산

현재까지 누적: =SUM(매출범위)
특정일까지 누적: =SUMIFS(매출범위, 날짜범위, "<="&DATE(2025,2,15))

전월 대비 증감률

=(이번달매출-전월매출)/전월매출*100

4단계: 관계형 데이터 조회 (RELATED 개념)

DAX의 RELATED 함수처럼 다른 테이블의 데이터를 가져오는 기능이에요.

VLOOKUP 방식

=VLOOKUP(찾을값, 테이블범위, 열번호, FALSE)

INDEX+MATCH 방식 (더 유연함)

=INDEX(가져올범위, MATCH(찾을값, 찾을범위, 0))

실제 사용 예시

제품명으로 카테고리 찾기:
=INDEX(카테고리범위, MATCH(B2, 제품명범위, 0))
최고 매출 제품 찾기:
=INDEX(제품명범위, MATCH(MAX(매출범위), 매출범위, 0))

5단계: 고급 분석 기법

순위 매기기 (RANKX 개념)

=RANK(대상값, 전체범위, 0) // 0은 내림차순(큰 값이 1등)
=RANK(D2, D:D, 0) // D2의 순위

고유값 개수 (DISTINCTCOUNT 개념)

=SUMPRODUCT(1/COUNTIF(범위,범위))
// 중복 제거한 고유값 개수

백분위 계산

=PERCENTILE(범위, 0.9) // 90퍼센타일 값
=PERCENTRANK(범위, 값) // 특정 값의 백분위 순위

📁 CSV 예시 데이터

실습에 사용할 50개의 상세한 데이터입니다. 제품명, 카테고리, 매출액, 수량, 단가, 지역, 판매일, 목표액, 달성률 등이 포함되어 있어요!

번호,제품명,카테고리,매출액,수량,단가,지역,판매일,목표액,달성률
1,노트북A,전자제품,1500000,3,500000,서울,2025-01-15,1200000,=D2/I2
2,마우스B,전자제품,45000,15,3000,부산,2025-01-16,50000,=D3/I3
3,키보드C,전자제품,120000,8,15000,대구,2025-01-17,100000,=D4/I4
4,모니터D,전자제품,800000,2,400000,인천,2025-01-18,900000,=D5/I5
5,스피커E,전자제품,180000,6,30000,광주,2025-01-19,200000,=D6/I6
6,책상F,가구,350000,5,70000,서울,2025-01-20,300000,=D7/I7
7,의자G,가구,240000,8,30000,부산,2025-01-21,250000,=D8/I8
8,책장H,가구,420000,3,140000,대구,2025-01-22,400000,=D9/I9
9,서랍I,가구,180000,6,30000,인천,2025-01-23,160000,=D10/I10
10,소파J,가구,950000,1,950000,광주,2025-01-24,1000000,=D11/I11
11,샴푸K,생활용품,25000,10,2500,서울,2025-01-25,30000,=D12/I12
12,세제L,생활용품,35000,7,5000,부산,2025-01-26,40000,=D13/I13
13,화장지M,생활용품,18000,12,1500,대구,2025-01-27,20000,=D14/I14
14,세정제N,생활용품,42000,6,7000,인천,2025-01-28,45000,=D15/I15
15,타월O,생활용품,28000,8,3500,광주,2025-01-29,25000,=D16/I16
16,운동화P,의류,85000,4,21250,서울,2025-01-30,80000,=D17/I17
17,셔츠Q,의류,65000,5,13000,부산,2025-01-31,70000,=D18/I18
18,바지R,의류,95000,3,31667,대구,2025-02-01,90000,=D19/I19
19,모자S,의류,35000,7,5000,인천,2025-02-02,40000,=D20/I20
20,가방T,의류,120000,2,60000,광주,2025-02-03,110000,=D21/I21
21,사과U,식품,15000,20,750,서울,2025-02-04,18000,=D22/I22
22,바나나V,식품,12000,15,800,부산,2025-02-05,15000,=D23/I23
23,오렌지W,식품,24000,12,2000,대구,2025-02-06,25000,=D24/I24
24,포도X,식품,35000,7,5000,인천,2025-02-07,30000,=D25/I25
25,딸기Y,식품,45000,5,9000,광주,2025-02-08,40000,=D26/I26
26,노트북Z,전자제품,1800000,2,900000,서울,2025-02-09,1600000,=D27/I27
27,태블릿AA,전자제품,650000,4,162500,부산,2025-02-10,700000,=D28/I28
28,핸드폰BB,전자제품,1200000,3,400000,대구,2025-02-11,1100000,=D29/I29
29,이어폰CC,전자제품,95000,8,11875,인천,2025-02-12,100000,=D30/I30
30,충전기DD,전자제품,25000,12,2083,광주,2025-02-13,30000,=D31/I31
31,침대EE,가구,850000,1,850000,서울,2025-02-14,800000,=D32/I32
32,매트리스FF,가구,420000,2,210000,부산,2025-02-15,450000,=D33/I33
33,베개GG,가구,85000,6,14167,대구,2025-02-16,90000,=D34/I34
34,이불HH,가구,150000,4,37500,인천,2025-02-17,140000,=D35/I35
35,커튼II,가구,75000,8,9375,광주,2025-02-18,80000,=D36/I36
36,비누JJ,생활용품,8000,20,400,서울,2025-02-19,10000,=D37/I37
37,칫솔KK,생활용품,15000,15,1000,부산,2025-02-20,18000,=D38/I38
38,치약LL,생활용품,12000,12,1000,대구,2025-02-21,15000,=D39/I39
39,수건MM,생활용품,35000,7,5000,인천,2025-02-22,30000,=D40/I40
40,슬리퍼NN,생활용품,25000,10,2500,광주,2025-02-23,28000,=D41/I41
41,점퍼OO,의류,180000,2,90000,서울,2025-02-24,200000,=D42/I42
42,스웨터PP,의류,95000,4,23750,부산,2025-02-25,100000,=D43/I43
43,청바지QQ,의류,85000,5,17000,대구,2025-02-26,90000,=D44/I44
44,양말RR,의류,15000,12,1250,인천,2025-02-27,18000,=D45/I45
45,속옷SS,의류,65000,8,8125,광주,2025-02-28,60000,=D46/I46
46,우유TT,식품,18000,18,1000,서울,2025-03-01,20000,=D47/I47
47,빵UU,식품,24000,12,2000,부산,2025-03-02,25000,=D48/I48
48,계란VV,식품,15000,15,1000,대구,2025-03-03,18000,=D49/I49
49,쌀WW,식품,45000,3,15000,인천,2025-03-04,50000,=D50/I50
50,라면XX,식품,28000,20,1400,광주,2025-03-05,30000,=D51/I51

📊 XLSX 템플릿 갤러리

완성된 템플릿에는 DAX 개념을 스프레드시트로 구현한 5개의 시트가 포함되어 있습니다:

시트 구성:

1. RawData: 기본 데이터 (50개 샘플)

2. DAX기초함수: DAX와 스프레드시트 함수 대응표

3. 조건부집계: CALCULATE 개념 구현

4. 시간인텔리전스: 날짜 기반 분석

5. DAX대시보드: 종합 분석 대시보드

주요 기능:

· 자동 계산: 원본 데이터 변경 시 모든 분석 결과 자동 업데이트

· 다차원 분석: 카테고리×지역×시간 복합 분석

· 시각적 대시보드: 핵심 KPI와 트렌드 한눈에 확인

· 실무 적용: 실제 비즈니스에서 바로 사용 가능한 구조

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

사례 1: 학급 성적 관리 시스템 (중학교 2학년)

상황: 현민이네 반 선생님이 30명 학생의 중간고사 성적을 체계적으로 분석하고 싶어합니다.

기존 문제점:

· 과목별, 학생별 분석이 복잡함

· 성적 향상/하락 학생 파악 어려움

· 학부모 상담용 자료 준비 시간 소모

· 반 평균과 개인 비교 분석 복잡

DAX 개념 적용 해결법:

1단계: 기본 데이터 구조

학번 | 이름 | 국어 | 영어 | 수학 | 과학 | 사회 | 총점 | 평균 | 반평균대비

2단계: 집계 함수 활용

개인 총점: =SUM(C2:G2)
개인 평균: =AVERAGE(C2:G2)
과목별 반평균: =AVERAGE(C:C) // 국어 반평균

3단계: 조건부 분석 (CALCULATE 개념)

상위권 학생수 (평균 90점 이상): =COUNTIF(H:H,">=90")
과목별 우수학생: =COUNTIF(C:C,">=95") // 국어 95점 이상
학습부진학생: =COUNTIFS(H:H,"<70",A:A,"<>") // 평균 70점 미만

4단계: 순위와 등급 (RANKX 개념)

반등수: =RANK(H2,H:H,0)
등급산정: =IF(H2>=90,"A",IF(H2>=80,"B",IF(H2>=70,"C","D")))
상대평가: =IF(RANK(H2,H:H,0)<=3,"최우수",IF(RANK(H2,H:H,0)<=10,"우수","보통"))

5단계: 자동 분석 보고서

반 1등: =INDEX(B:B,MATCH(MAX(H:H),H:H,0))
최고득점과목: =INDEX(과목명,MATCH(MAX(반평균범위),반평균범위,0))
학습지도 필요학생: =COUNTIFS(H:H,"<반평균")

결과: 선생님은 이제 새로운 시험 점수만 입력하면 30초 만에 완벽한 성적 분석표가 완성됩니다! 학부모 상담 시간도 절반으로 줄었어요.

사례 2: 동아리 예산 관리 (학생회 임원)

상황: 학생회 총무 지수가 한 학기 동아리 예산 300만원을 효율적으로 관리해야 합니다.

기존 문제점:

· 동아리별 예산 사용 현황 파악 어려움

· 분기별 예산 계획 대비 실적 분석 복잡

· 예산 초과 위험 동아리 조기 발견 어려움

· 결산 보고서 작성 시간 소모

DAX 개념 적용 해결법:

1단계: 데이터 구조 설계

날짜 | 동아리명 | 분류 | 항목 | 금액 | 승인자 | 잔액 | 예산한도

2단계: 기본 집계 분석

동아리별 총 사용액: =SUMIF(동아리열,"축구부",금액열)
분류별 사용현황: =SUMIF(분류열,"행사비",금액열)
월별 지출현황: =SUMIFS(금액열,날짜열,">=2025-03-01",날짜열,"<2025-04-01")

3단계: 예산 통제 (조건부 분석)

예산초과 동아리: =COUNTIFS(사용액범위,">"&예산한도범위)
예산 사용률: =SUMIF(동아리열,"축구부",금액열)/예산한도*100
위험 동아리 (80% 이상): =COUNTIFS(사용률범위,">=80%")

4단계: 시간 인텔리전스

이번달 지출: =SUMIFS(금액열,날짜열,">="&DATE(2025,3,1),날짜열,"<"&DATE(2025,4,1))
전월 대비: =(이번달지출-전월지출)/전월지출*100
1분기 누적: =SUMIFS(금액열,날짜열,"<="&DATE(2025,3,31))

5단계: 자동 알림 시스템

예산 경고: =IF(사용률>=80%,"⚠️ 예산 주의","✅ 정상")
추천 월예산: =남은예산/남은개월수
다음달 예상: =AVERAGE(최근3개월평균)*1.1

결과: 지수는 매주 5분만 투자해서 전체 동아리 예산 현황을 완벽하게 파악할 수 있게 되었고, 예산 초과 사고를 사전에 방지할 수 있게 되었습니다!

사례 3: 가족 편의점 매출 분석 (부모님 사업 도움)

상황: 수진이의 부모님이 운영하는 편의점의 일일 매출을 체계적으로 분석해서 매출 증대 방안을 찾고 싶어합니다.

기존 문제점:

· 상품별 매출 기여도 파악 어려움

· 시간대별 판매 패턴 분석 복잡

· 계절별 트렌드 예측 어려움

· 재고 발주 계획 수립 복잡

DAX 개념 적용 해결법:

1단계: 상세 매출 데이터 구조

날짜 | 시간 | 상품분류 | 상품명 | 수량 | 단가 | 매출액 | 요일 | 날씨

2단계: 상품 분석 (집계 함수)

분류별 매출: =SUMIF(상품분류열,"음료",매출액열)
인기상품 TOP5: =LARGE(상품별매출범위,1~5)
매출기여도: =상품매출/총매출*100
ABC분석: =IF(누적기여도<=70%,"A",IF(누적기여도<=90%,"B","C"))

3단계: 시간 패턴 분석 (조건부 계산)

시간대별 매출: =SUMIFS(매출액열,시간열,">=07:00",시간열,"<08:00")
요일별 평균: =AVERAGEIF(요일열,"월요일",매출액열)
날씨별 매출: =AVERAGEIF(날씨열,"맑음",매출액열)

4단계: 트렌드 분석 (시간 인텔리전스)

일주일 평균: =AVERAGE(최근7일매출)
전주 대비: =(이번주매출-전주매출)/전주매출*100
월 목표 달성률: =현재까지매출/월목표*일수비율
계절 지수: =해당월평균/연평균*100

5단계: 예측과 추천 (고급 분석)

내일 예상매출: =(같은요일평균+최근추세)/2
추천 발주량: =예상매출/평균단가*1.2
위험 상품: =IF(매출감소율>20%,"⚠️ 매출급감","정상")
기회 상품: =IF(매출증가율>30%,"🔥 매출급증","정상")

결과: 수진이네 편의점은 데이터 기반 운영으로 월매출이 15% 증가했고, 재고 부담은 20% 줄어들었습니다. 이제 수진이도 부모님 사업에 실질적인 도움을 주고 있어요!

사례 4: 개인 건강 관리 시스템 (중학생 자기관리)

상황: 건강에 관심이 많은 중2 학생 준호가 자신의 운동, 식단, 수면을 체계적으로 관리하고 싶어합니다.

기존 문제점:

· 운동 효과 측정 어려움

· 식단과 체중 변화 연관성 파악 복잡

· 생활 패턴과 컨디션 관계 분석 어려움

· 목표 달성 진도 관리 복잡

DAX 개념 적용 해결법:

1단계: 건강 데이터 구조

2단계: 건강 지표 분석

주간 평균체중: =AVERAGE(최근7일체중)
체중 변화량: =오늘체중-일주일전체중
운동 효과: =(체중감소량/운동시간)*-1 // 음수를 양수로
건강점수: =(운동점수+식단점수+수면점수)/3

3단계: 목표 달성 분석 (조건부 계산)

운동 달성일: =COUNTIF(운동시간열,">=60") // 1시간 이상
완벽한 날: =COUNTIFS(운동열,">=목표",식단열,">=목표",수면열,">=목표")
달성률: =완벽한날/총일수*100
연속달성: =현재까지연속일수

4단계: 패턴 분석 (시간 인텔리전스)

요일별 컨디션: =AVERAGEIF(요일열,"월요일",컨디션열)
수면-컨디션 상관관계: =CORREL(수면시간범위,컨디션범위)
월별 개선도: =(이번달평균-전월평균)/전월평균*100

5단계: 개인 맞춤 추천

최적 수면시간: =INDEX(수면시간범위,MATCH(MAX(컨디션범위),컨디션범위,0))
효과적 운동: =INDEX(운동종류범위,MATCH(MAX(체중감소범위),체중감소범위,0))
위험 신호: =IF(연속저조일>=3,"⚠️ 관리 필요","✅ 양호")

결과: 준호는 3개월 만에 목표 체중을 달성했고, 자신에게 가장 효과적인 운동과 생활 패턴을 발견했습니다. 이제 친구들에게도 건강 관리 방법을 조언해주고 있어요!

🔧 DAX 개념 구현 시 주의사항

1. 데이터 품질 관리

일관된 데이터 형식

날짜: YYYY-MM-DD 형식 통일
숫자: 천 단위 구분자 없이 순수 숫자
텍스트: 대소문자, 띄어쓰기 일관성 유지

결측값 처리

빈 셀 처리: =IF(ISBLANK(A2),0,A2)
오류 방지: =IFERROR(계산식,0)
조건부 계산시 빈값 제외: =SUMIF(범위,"<>",합계범위)

2. 성능 최적화

범위 설정 최적화

❌ 비효율: =SUMIF(A:A,조건,B:B)
✅ 효율: =SUMIF(A1:A1000,조건,B1:B1000)

휘발성 함수 최소화

❌ 피하기: =TODAY(), =NOW() 과다 사용
✅ 권장: 필요한 곳에만 사용, 셀 참조 활용

3. 오류 처리

일반적인 오류들

#DIV/0!: =IF(분모=0,0,분자/분모)
#N/A: =IFERROR(VLOOKUP(...),0)
#VALUE!: =IF(ISNUMBER(값),값,0)

📈 다음 단계 학습 가이드

초급에서 중급으로

1. 배열 함수 마스터: ARRAYFORMULA, FILTER 활용

2. 정규식 활용: REGEXMATCH, REGEXEXTRACT로 텍스트 분석

3. 피벗 테이블 고급: 계산 필드, 그룹화 활용

중급에서 고급으로

1. Google Apps Script 연동: 자동화 워크플로우 구축

2. 외부 데이터 연결: API, 웹 크롤링 활용

3. 실시간 대시보드: 자동 새로고침, 실시간 알림

실무 전문가 되기

1. 비즈니스 로직 설계: 복잡한 업무 프로세스 자동화

2. 팀 협업 시스템: 권한 관리, 워크플로우 설계

3. 데이터 거버넌스: 데이터 품질 관리, 보안 정책

💡 핵심 요약

DAX 개념의 스프레드시트 구현 핵심:

1. 집계 함수 (SUM, AVERAGE, COUNT)가 모든 분석의 기초

2. 조건부 함수 (SUMIF, AVERAGEIF, COUNTIF)로 CALCULATE 구현

3. 시간 함수와 조건 조합으로 시간 인텔리전스 구현

4. INDEX+MATCH로 관계형 데이터 조회 구현

5. 복합 수식으로 고급 분석 기능 구현

성공하는 학습자의 특징:

· 꾸준한 연습: 매일 10분씩이라도 실습

· 실무 적용: 실제 데이터로 프로젝트 진행

· 오류 학습: 실수를 통해 더 깊이 이해

· 창의적 활용: 기본 함수를 조합해서 새로운 분석 창조

이제 여러분도 DAX 수준의 고급 데이터 분석을 스프레드시트에서 자유자재로 할 수 있습니다! 🚀