4-75. 스프레드시트 데이터 모델링
안녕하세요! 오늘은 스프레드시트로 데이터 모델링을 배워보겠습니다. 데이터 모델링이라고 하면 어렵게 느껴지시나요? 걱정하지 마세요! 쉽게 말해서 여러 개의 표를 연결해서 더 똑똑하게 데이터를 관리하는 방법이에요. 마치 퍼즐 조각을 맞추듯이 말이죠!
🔗 지금 바로 사용해보세요!
클릭 한 번으로 바로 접속해서 복사하여 사용하실 수 있습니다
💡 데이터 모델링이란 무엇인가요?
개념 설명
데이터 모델링은 현실 세계의 복잡한 정보를 여러 개의 연결된 표(테이블)로 체계적으로 정리하는 방법입니다. 예를 들어, 온라인 쇼핑몰을 생각해보세요:
· 고객 정보: 이름, 주소, 전화번호
· 상품 정보: 상품명, 가격, 재고량
· 주문 정보: 누가, 언제, 무엇을, 얼마나 주문했는지
이 세 가지 정보를 각각 별도의 표로 만들고, 서로 연결해서 관리하는 것이 데이터 모델링이에요!
왜 데이터 모델링이 필요한가요?
1. 중복 데이터 제거
❌ 나쁜 예: 주문할 때마다 고객 정보를 반복 입력
김민수, 010-1234-5678, 서울시... (매번 반복)
✅ 좋은 예: 고객 코드로 연결
고객코드: C001 → 고객 정보는 한 번만 저장
2. 데이터 일관성 유지
· 고객 정보가 바뀌면 한 곳만 수정하면 모든 곳에 반영
· 오타나 실수로 인한 데이터 불일치 방지
3. 유연한 분석
· 고객별 구매 패턴 분석
· 상품별 매출 현황 파악
· 지역별 판매 동향 분석
4. 확장성
· 새로운 정보를 쉽게 추가 가능
· 기존 데이터에 영향 없이 구조 변경
스프레드시트에서의 데이터 모델링 핵심 개념
1. 테이블 (Table)
· 하나의 주제에 대한 정보를 담는 표
· 예: 고객테이블, 제품테이블, 주문테이블
2. 기본키 (Primary Key)
· 각 행을 유일하게 구분하는 값
· 예: 고객ID(C001, C002...), 제품코드(P001, P002...)
3. 외래키 (Foreign Key)
· 다른 테이블의 기본키를 참조하는 값
· 예: 주문테이블의 고객ID는 고객테이블의 고객ID를 참조
4. 관계 (Relationship)
· 테이블 간의 연결 관계
· 일대다 관계: 한 고객이 여러 주문 가능
📚 단계별 실습 방법
1단계: 데이터 모델 설계하기
먼저 우리가 관리할 데이터를 논리적으로 분류해보겠습니다.
쇼핑몰 예시로 테이블 설계:
고객 테이블 (Customer)
고객ID (기본키) | 고객명 | 지역코드 | 등급 | 가입일 | 연락처
C001 | 김민수 | R001 | VIP | 2024-01-15 | 010-1234-5678
제품 테이블 (Product)
제품코드 (기본키) | 제품명 | 카테고리 | 단가 | 재고수량 | 공급업체
P001 | 노트북 A | 전자제품 | 1200000 | 25 | 삼성전자
지역 테이블 (Region)
지역코드 (기본키) | 지역명 | 지역관리자 | 인구수 | 평균소득
R001 | 서울 | 김서울 | 9700000 | 4500000
주문 테이블 (Order)
주문ID (기본키) | 고객ID (외래키) | 제품코드 (외래키) | 수량 | 주문일 | 배송상태 | 총금액
O001 | C001 | P001 | 2 | 2025-01-15 | 배송완료 | 2400000
2단계: 관계형 조회 함수 마스터하기
VLOOKUP 함수
=VLOOKUP(찾을값, 테이블범위, 열번호, 정확일치)
실제 사용:
=VLOOKUP(B2, 고객테이블!A:F, 2, 0) // 고객ID로 고객명 찾기
INDEX + MATCH 조합 (더 강력함!)
=INDEX(가져올범위, MATCH(찾을값, 찾을범위, 0))
실제 사용:
=INDEX(고객테이블!B:B, MATCH(B2, 고객테이블!A:A, 0)) // 고객ID로 고객명 찾기
XLOOKUP 함수 (최신 버전)
=XLOOKUP(찾을값, 찾을범위, 가져올범위)
실제 사용:
=XLOOKUP(B2, 고객테이블!A:A, 고객테이블!B:B) // 가장 간단!
3단계: 복잡한 관계형 분석하기
다중 테이블 조인 분석
예시 1: 고객별 총 주문금액
=SUMIFS(주문테이블!G:G, 주문테이블!B:B, 고객ID)
// 특정 고객의 모든 주문금액 합계
예시 2: 지역별 매출 분석
=SUMPRODUCT(
(VLOOKUP(주문테이블!B:B, 고객테이블!A:C, 3, 0) = 지역코드) *
주문테이블!G:G
)
// 고객테이블과 주문테이블을 지역코드로 연결하여 지역별 매출 계산
예시 3: 제품 카테고리별 재고 가치
=SUMPRODUCT(
(제품테이블!C:C = 카테고리명) *
제품테이블!D:D *
제품테이블!E:E
)
// 단가 × 재고수량으로 카테고리별 재고 가치 계산
4단계: 동적 분석 대시보드 구축
실시간 KPI 계산
총 고객수: =COUNTA(고객테이블!A:A)-1
VIP 고객 비율: =COUNTIF(고객테이블!D:D,"VIP")/COUNTA(고객테이블!A:A)*100
평균 주문금액: =AVERAGE(주문테이블!G:G)
최고 매출 제품: =INDEX(제품테이블!B:B, MATCH(MAX(매출범위), 매출범위, 0))
조건부 서식으로 시각화
· VIP 고객은 금색 배경
· 재고 부족 제품은 빨간색 경고
· 목표 달성 지역은 파란색 강조
5단계: 고급 데이터 모델링 기법
정규화 (Normalization)
· 중복 데이터를 최소화하여 효율성 높이기
· 각 테이블은 하나의 주제만 다루기
역정규화 (Denormalization)
· 성능 향상을 위해 의도적으로 중복 허용
· 자주 사용하는 조회는 미리 계산해두기
데이터 무결성 검증
고객ID 존재 확인: =IF(ISERROR(VLOOKUP(B2,고객테이블!A:A,1,0)),"⚠️ 존재하지 않는 고객","✅")
제품코드 유효성: =IF(COUNTIF(제품테이블!A:A,C2)=0,"⚠️ 잘못된 제품코드","✅")
📁 CSV 예시 데이터
실습에 사용할 관계형 데이터베이스 구조를 CSV로 제공합니다. 4개의 테이블이 서로 연결되어 있어요!
=== 고객 테이블 ===
고객ID,고객명,지역코드,등급,가입일,연락처
C001,김민수,R001,VIP,2024-01-15,010-1234-5678
C002,이영희,R002,GOLD,2024-02-20,010-2345-6789
C003,박철수,R001,SILVER,2024-03-10,010-3456-7890
C004,최미영,R003,VIP,2024-01-25,010-4567-8901
C005,정다운,R002,BRONZE,2024-04-05,010-5678-9012
C006,강호동,R001,GOLD,2024-02-14,010-6789-0123
C007,유재석,R004,VIP,2024-01-30,010-7890-1234
C008,박나래,R003,SILVER,2024-03-22,010-8901-2345
C009,송강호,R002,GOLD,2024-02-28,010-9012-3456
C010,전지현,R005,VIP,2024-01-18,010-0123-4567
C011,손석구,R001,BRONZE,2024-04-12,010-1234-5679
C012,김혜수,R004,SILVER,2024-03-15,010-2345-6780
C013,조정석,R002,GOLD,2024-02-08,010-3456-7891
C014,박소담,R003,VIP,2024-01-22,010-4567-8902
C015,이정재,R005,BRONZE,2024-04-18,010-5678-9013
=== 제품 테이블 ===
제품코드,제품명,카테고리,단가,재고수량,공급업체
P001,노트북 A,전자제품,1200000,25,삼성전자
P002,마우스 B,전자제품,35000,150,로지텍
P003,키보드 C,전자제품,85000,80,체리
P004,모니터 D,전자제품,450000,40,LG전자
P005,책상 E,가구,180000,20,한샘
P006,의자 F,가구,120000,35,시디즈
P007,책장 G,가구,220000,15,이케아
P008,샴푸 H,생활용품,15000,200,P&G
P009,세제 I,생활용품,8000,300,애경
P010,화장지 J,생활용품,12000,500,유한킴벌리
P011,운동화 K,의류,95000,60,나이키
P012,셔츠 L,의류,45000,100,유니클로
P013,바지 M,의류,75000,80,리바이스
P014,사과 N,식품,8000,150,농협
P015,우유 O,식품,3500,200,매일유업
=== 지역 테이블 ===
지역코드,지역명,지역관리자,인구수,평균소득
R001,서울,김서울,9700000,4500000
R002,부산,박부산,3400000,3200000
R003,대구,이대구,2400000,3000000
R004,인천,최인천,2950000,3300000
R005,광주,정광주,1500000,2900000
=== 주문 테이블 ===
주문ID,고객ID,제품코드,수량,주문일,배송상태,총금액
O001,C001,P001,2,2025-01-15,배송완료,=VLOOKUP(C2,제품테이블,4,0)*D2
O002,C002,P005,1,2025-01-16,배송중,=VLOOKUP(C3,제품테이블,4,0)*D3
O003,C003,P008,5,2025-01-17,주문확인,=VLOOKUP(C4,제품테이블,4,0)*D4
O004,C004,P002,3,2025-01-18,배송완료,=VLOOKUP(C5,제품테이블,4,0)*D5
O005,C005,P011,1,2025-01-19,배송중,=VLOOKUP(C6,제품테이블,4,0)*D6
(... 총 20개 주문 데이터)
📊 XLSX 템플릿 갤러리
완성된 템플릿에는 실제 쇼핑몰 수준의 데이터 모델링이 구현되어 있습니다:
시트 구성:
1. 고객테이블: 15명의 고객 정보 (기본키: 고객ID)
2. 제품테이블: 15개 제품 정보 (기본키: 제품코드)
3. 지역테이블: 5개 지역 정보 (기본키: 지역코드)
4. 주문테이블: 20건의 주문 정보 (외래키로 다른 테이블 연결)
5. 데이터모델링분석: 종합 분석 대시보드
6. 관계형조회: 테이블 간 조인 실습
핵심 기능:
· 자동 계산: 주문 총금액이 제품 단가 × 수량으로 자동 계산
· 관계형 조회: VLOOKUP, INDEX+MATCH로 테이블 간 데이터 연결
· 다차원 분석: 고객등급×지역×제품카테고리 복합 분석
· 실시간 대시보드: 핵심 KPI 자동 업데이트
🎯 실무 및 교육 현장 활용 사례
사례 1: 학급 도서관 관리 시스템 (중학교 2학년)
상황: 민지네 반에서 반 도서관을 운영하게 되었습니다. 30권의 책과 25명의 학생, 그리고 대출 기록을 체계적으로 관리해야 해요.
기존 문제점:
· 누가 어떤 책을 언제 빌렸는지 파악 어려움
· 책 반납일 관리 복잡
· 인기 도서와 잘 안 읽히는 책 구분 어려움
· 학생별 독서 현황 파악 힘듦
데이터 모델링 해결법:
1단계: 테이블 설계
📚 도서테이블
도서ID | 제목 | 저자 | 장르 | 출판사 | 구입일
👨🎓 학생테이블
학번 | 이름 | 반 | 독서등급 | 연락처
📋 대출테이블
대출ID | 학번 | 도서ID | 대출일 | 반납예정일 | 반납일 | 상태
2단계: 관계형 조회 구현
현재 대출자 찾기:
=INDEX(학생테이블!B:B, MATCH(대출테이블!B2, 학생테이블!A:A, 0))
도서 제목 표시:
=INDEX(도서테이블!B:B, MATCH(대출테이블!C2, 도서테이블!A:A, 0))
연체 확인:
=IF(AND(대출테이블!F2="", TODAY()>대출테이블!E2), "⚠️ 연체", "정상")
3단계: 자동 분석 시스템
학생별 대출 현황:
=COUNTIFS(대출테이블!B:B, 학번, 대출테이블!G:G, "대출중")
인기 도서 TOP5:
=INDEX(도서테이블!B:B, MATCH(LARGE(대출횟수범위,1), 대출횟수범위, 0))
장르별 선호도:
=COUNTIFS(도서테이블!D:D, "소설", 대출테이블!C:C, "<>")
4단계: 대시보드 구성
📊 주요 지표
- 총 장서: =COUNTA(도서테이블!A:A)-1
- 현재 대출중: =COUNTIF(대출테이블!G:G, "대출중")
- 연체 도서: =COUNTIFS(대출테이블!G:G, "대출중", 대출테이블!E:E, "<"&TODAY())
- 이번 달 대출: =COUNTIFS(대출테이블!D:D, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))
결과: 민지는 이제 반 도서관 운영을 완벽하게 관리할 수 있게 되었고, 선생님께도 매월 독서 현황 보고서를 자동으로 생성해서 제출할 수 있게 되었어요!
사례 2: 동아리 장비 관리 시스템 (학교 밴드부)
상황: 밴드부 부장 수민이가 50여 개의 악기와 장비를 체계적으로 관리하고, 부원들의 대여 현황을 추적해야 합니다.
기존 문제점:
· 장비 분실이나 파손 시 책임자 파악 어려움
· 장비별 상태 관리 복잡 (정상/수리중/폐기)
· 연습실별 장비 배치 현황 파악 어려움
· 신입 부원 교육용 장비 가이드 필요
데이터 모델링 해결법:
1단계: 체계적 테이블 설계
🎸 장비테이블
장비ID | 장비명 | 카테고리 | 브랜드 | 구입가격 | 구입일 | 상태 | 위치
👥 부원테이블
부원ID | 이름 | 학년 | 파트 | 실력등급 | 가입일
📝 대여테이블
대여ID | 부원ID | 장비ID | 대여일 | 반납예정일 | 반납일 | 상태 | 특이사항
🏠 연습실테이블
연습실ID | 연습실명 | 수용인원 | 고정장비목록
2단계: 스마트 관리 시스템
장비 현재 위치:
=IF(COUNTIFS(대여테이블!C:C,장비ID,대여테이블!G:G,"대여중")>0,
INDEX(부원테이블!B:B,MATCH(INDEX(대여테이블!B:B,대여테이블!C:C=장비ID),부원테이블!A:A,0)),
INDEX(장비테이블!H:H,MATCH(장비ID,장비테이블!A:A,0)))
부원별 대여 가능 여부:
=IF(COUNTIFS(대여테이블!B:B,부원ID,대여테이블!G:G,"대여중")>=3,"❌ 대여 제한","✅ 대여 가능")
장비 상태 알림:
=IF(DATEDIF(구입일,TODAY(),"Y")>5,"⚠️ 교체 검토",
IF(상태="수리중","🔧 수리중","✅ 정상"))
3단계: 예방 관리 시스템
정기 점검 알림:
=IF(DATEDIF(마지막점검일,TODAY(),"M")>=3,"🔍 점검 필요","정상")
부원별 책임도:
=COUNTIFS(대여테이블!B:B,부원ID,대여테이블!H:H,"<>") // 특이사항 있는 대여 건수
카테고리별 가동률:
=COUNTIFS(대여테이블!C:C,장비카테고리,대여테이블!G:G,"대여중")/
COUNTIF(장비테이블!C:C,장비카테고리)*100
결과: 수민이는 장비 분실 사고를 100% 방지했고, 부원들의 만족도도 크게 향상되었습니다. 이제 다른 동아리에서도 벤치마킹하러 올 정도예요!
사례 3: 가족 식당 운영 시스템 (부모님 사업 도움)
상황: 현우의 부모님이 운영하는 작은 식당의 메뉴, 재료, 주문, 고객 정보를 디지털로 체계화해서 효율성을 높이고 싶어합니다.
기존 문제점:
· 메뉴별 원가 계산 복잡
· 재료 발주 계획 수립 어려움
· 단골 고객 관리 체계 부족
· 매출 분석과 트렌드 파악 어려움
데이터 모델링 해결법:
1단계: 식당 운영 테이블 설계
🍜 메뉴테이블
메뉴코드 | 메뉴명 | 카테고리 | 판매가격 | 원가 | 조리시간 | 인기도
🥬 재료테이블
재료코드 | 재료명 | 단위 | 단가 | 현재고 | 최소재고 | 공급업체
👨👩👧👦 고객테이블
고객코드 | 고객명 | 연락처 | 주소 | 등급 | 선호메뉴 | 방문횟수
📋 주문테이블
주문번호 | 고객코드 | 메뉴코드 | 수량 | 주문일시 | 총금액 | 결제방법
🔗 메뉴재료테이블 (다대다 관계 해결)
메뉴코드 | 재료코드 | 필요량
2단계: 원가 관리 시스템
메뉴별 실제 원가:
=SUMPRODUCT(
(메뉴재료테이블!A:A=메뉴코드) *
VLOOKUP(메뉴재료테이블!B:B,재료테이블!A:D,4,0) *
메뉴재료테이블!C:C
)
수익률 계산:
=(판매가격-실제원가)/판매가격*100
재료 소모량 예측:
=SUMPRODUCT(
(메뉴재료테이블!A:A=메뉴코드) *
메뉴재료테이블!C:C *
COUNTIFS(주문테이블!C:C,메뉴코드,주문테이블!E:E,">="&TODAY()-7)
)
3단계: 고객 관리 시스템
고객별 누적 주문금액:
=SUMIFS(주문테이블!F:F,주문테이블!B:B,고객코드)
선호 메뉴 분석:
=INDEX(메뉴테이블!B:B,
MATCH(
MODE(IF(주문테이블!B:B=고객코드,주문테이블!C:C)),
메뉴테이블!A:A,0
)
)
VIP 고객 선정:
=IF(AND(방문횟수>=20,누적주문금액>=500000),"👑 VIP",
IF(AND(방문횟수>=10,누적주문금액>=200000),"⭐ 골드","일반"))
4단계: 운영 최적화 대시보드
📊 일일 운영 지표
- 오늘 매출: =SUMIFS(주문테이블!F:F,주문테이블!E:E,">="&TODAY())
- 인기 메뉴: =INDEX(메뉴명,MATCH(MAX(일일판매량),일일판매량,0))
- 부족 재료: =COUNTIFS(재료테이블!E:E,"<"&재료테이블!F:F)
- 신규 고객: =COUNTIFS(고객테이블!F:F,TODAY())
📈 주간 트렌드
- 요일별 매출: =SUMIFS(주문테이블!F:F,TEXT(주문테이블!E:E,"dddd"),"월요일")
- 시간대별 주문: =COUNTIFS(HOUR(주문테이블!E:E),">=12",HOUR(주문테이블!E:E),"<13")
결과: 현우네 식당은 원가율을 5% 절감하고 매출을 20% 증가시켰습니다. 무엇보다 부모님의 업무 스트레스가 크게 줄어들었어요!
사례 4: 개인 건강 관리 데이터베이스 (중학생 자기관리)
상황: 건강 관리에 관심이 많은 중2 학생 지은이가 운동, 식단, 수면, 건강검진 결과를 종합적으로 분석해서 최적의 건강 관리 패턴을 찾고 싶어합니다.
데이터 모델링 해결법:
1단계: 건강 데이터 테이블 설계
🏃♀️ 운동테이블
운동ID | 날짜 | 운동종류 | 시간(분) | 강도 | 소모칼로리 | 컨디션점수
🍎 식단테이블
식단ID | 날짜 | 식사시간 | 음식명 | 칼로리 | 탄수화물 | 단백질 | 지방
😴 수면테이블
수면ID | 날짜 | 취침시간 | 기상시간 | 수면시간 | 수면품질 | 꿈기록
📊 건강지표테이블
측정ID | 날짜 | 체중 | 체지방률 | 근육량 | 혈압 | 스트레스지수
2단계: 건강 패턴 분석
일일 칼로리 수지:
=SUMIFS(식단테이블!E:E,식단테이블!B:B,날짜) -
SUMIFS(운동테이블!F:F,운동테이블!B:B,날짜)
수면-컨디션 상관관계:
=CORREL(수면테이블!E:E,운동테이블!G:G)
최적 운동시간:
=INDEX(운동시간범위,MATCH(MAX(컨디션점수범위),컨디션점수범위,0))
식단-체중변화 분석:
=IF(일일칼로리수지<0,"💪 체중감소 예상","⚠️ 체중증가 주의")
결과: 지은이는 자신만의 건강 관리 패턴을 과학적으로 발견했고, 3개월 만에 목표했던 건강 지표를 모두 달성했습니다!
🔧 데이터 모델링 구현 시 주의사항
1. 데이터 무결성 보장
참조 무결성 검증
외래키 존재 확인:
=IF(ISERROR(VLOOKUP(외래키값,참조테이블!A:A,1,0)),"❌ 유효하지 않은 참조","✅ 정상")
중복 기본키 방지:
=IF(COUNTIF(기본키범위,기본키값)>1,"⚠️ 중복된 키","✅ 고유")
2. 성능 최적화
효율적인 범위 설정
❌ 비효율: =VLOOKUP(A2,전체시트!A:Z,2,0)
✅ 효율: =VLOOKUP(A2,전체시트!$A$1:$Z$100,2,0)
인덱스 활용
자주 조회하는 데이터는 별도 조회 테이블 생성
=IF(ROW()<=COUNTA(기본테이블!A:A),INDEX(기본테이블!A:A,ROW()),"")
3. 확장성 고려
유연한 구조 설계
· 새로운 열 추가 시에도 기존 수식이 작동하도록 설계
· 테이블 크기가 늘어나도 성능 유지되는 구조
버전 관리
· 데이터 구조 변경 시 이전 버전과의 호환성 고려
· 마이그레이션 계획 수립
📈 다음 단계 학습 가이드
중급 → 고급
1. 정규화 이론: 1정규형부터 3정규형까지 체계적 학습
2. 복합 키 활용: 여러 열을 조합한 기본키 설계
3. 트랜잭션 개념: 데이터 일관성 보장 방법
고급 → 전문가
1. Google Apps Script 연동: 자동화된 데이터 처리
2. 외부 데이터 소스: API 연결, 실시간 데이터 동기화
3. Big Data 처리: 대용량 데이터 모델링 기법
💡 핵심 요약
데이터 모델링의 핵심 원칙:
1. 정규화: 중복을 최소화하고 일관성을 보장
2. 관계 설정: 기본키와 외래키로 테이블 간 연결
3. 무결성: 데이터의 정확성과 일관성 유지
4. 효율성: 성능을 고려한 구조 설계
5. 확장성: 미래 변화에 대응할 수 있는 유연한 구조
성공하는 학습법:
· 실제 데이터로 연습: 가상 데이터가 아닌 실생활 데이터 활용
· 단계적 접근: 간단한 관계부터 복잡한 관계로 점진적 학습
· 무결성 검증: 항상 데이터의 정확성을 확인하는 습관
· 성능 고려: 사용자 경험을 생각한 효율적 설계
이제 여러분도 전문가 수준의 데이터 모델링을 스프레드시트에서 구현할 수 있습니다! 🚀