4-78. 스프레드시트 관계 설정

JS's Spreadsheets 2025. 9. 25. 17:33

여러 테이블을 연결해 강력한 분석 만들기

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

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

엑셀/스프레드시트의 관계 설정은 서로 다른 테이블을 공통 필드(키)로 연결해 VLOOKUP 없이도 피벗 테이블에서 여러 테이블의 데이터를 함께 분석할 수 있게 해주는 강력한 기능입니다. 마치 데이터베이스처럼 테이블 간 연결을 만들어 효율적이고 정확한 데이터 분석이 가능합니다.

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

스프레드시트 관계 설정은 데이터 모델(Data Model) 기능을 활용해 여러 테이블을 논리적으로 연결하는 작업입니다.

관계 설정이란?

· 두 개 이상의 테이블을 **공통 필드(Primary Key - Foreign Key)**로 연결

· 예: 고객 테이블의 "고객ID"와 주문 테이블의 "고객ID"를 연결

· 일대일(1:1) 또는 일대다(1:多) 관계만 지원

왜 필요한가?

기존 방식의 문제점:

· 데이터 중복 저장으로 용량 낭비

· 수정 시 여러 곳을 동시에 변경해야 함

· VLOOKUP 지옥: 수만 개의 수식 작성 필요

관계 설정의 장점:

· 데이터 중복 제거로 저장 공간 절약

· 한 곳 수정으로 전체 데이터 자동 업데이트

· 피벗 테이블에서 여러 테이블 필드를 자유롭게 조합 분석

· 데이터 무결성 및 일관성 보장

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

1단계: 테이블 준비

· 각 데이터 범위를 테이블로 변환 (Ctrl+T)

· 테이블마다 의미 있는 이름 부여 (예: "고객", "주문")

· 연결할 공통 필드가 있는지 확인 (예: 고객ID)

2단계: 관계 생성

1. 데이터 탭 → 관계 클릭

2. 새로 만들기 버튼 선택

3. 테이블 선택: 다(多) 쪽 테이블 먼저 (예: 주문 테이블)

4. 열(외래키): 연결할 필드 선택 (예: 고객ID)

5. 관련 테이블: 일(1) 쪽 테이블 (예: 고객 테이블)

6. 관련 열(기본키): 고유값이 있는 필드

3단계: 피벗 테이블 생성

1. 아무 테이블 선택 → 삽입 → 피벗 테이블

2. "데이터 모델에 이 데이터 추가" 체크 필수

3. 피벗 테이블 필드에서 "전체" 탭 선택

4. 여러 테이블의 필드를 자유롭게 조합해서 분석

4단계: 자동 감지 기능

· 필드명이 같으면 Excel이 자동으로 관계 제안

· "자동 감지" 버튼으로 관계 자동 생성 가능

3) CSV 예시 데이터

고객 테이블 (customers.csv) - 마스터 테이블

번호,고객ID,고객명,지역
1,C1001,김철수,서울
2,C1002,이영희,부산
3,C1003,박민수,대구
4,C1004,최지은,인천
5,C1005,정우진,광주

주문 테이블 (orders.csv) - 상세 테이블

번호,주문ID,고객ID,상품명,수량,단가,총액
1,O2001,C1003,모니터,8,200000,=E2*F2
2,O2002,C1002,마우스,9,80000,=E3*F3
3,O2003,C1015,노트북,5,200000,=E4*F4
4,O2004,C1001,노트북,2,15000,=E5*F5
5,O2005,C1005,웹캠,1,15000,=E6*F6

4) XLSX 예시 템플릿 갤러리

템플릿은 3개 시트로 구성되어 있습니다:

고객 시트

· 고객 마스터 정보 (고객ID, 고객명, 지역)

· 각 고객ID는 고유값으로 관리

주문 시트

· 주문 상세 정보 (주문ID, 고객ID, 상품명, 수량, 단가)

· 총액 = 수량 × 단가 자동 계산

· 고객ID로 고객 시트와 연결

대시보드 시트

· 관계를 활용한 통합 분석:

o =VLOOKUP(A2,고객!B:D,2,FALSE): 고객명 자동 조회

o =COUNTIF(주문!C:C,A2): 고객별 총 주문 수

o =SUMIF(주문!C:C,A2,주문!G:G): 고객별 총 매출액

· 시각적으로 보기 좋은 레이아웃과 색상 적용

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

사례 1: 중학교 도서관 관리 시스템

상황: 중학교 2학년 정보 시간에 도서 대여 관리 시스템을 만들어보자.

테이블 구성:

1. 학생 테이블: 학번, 이름, 반, 연락처

2. 도서 테이블: 도서ID, 제목, 저자, 분류

3. 대여 테이블: 대여번호, 학번, 도서ID, 대여일, 반납일

관계 설정:

· 대여 테이블의 "학번" ↔ 학생 테이블의 "학번"

· 대여 테이블의 "도서ID" ↔ 도서 테이블의 "도서ID"

분석 결과:

· 학생별 대여 권수 TOP 10

· 인기 도서 순위 (분류별)

· 반별 평균 독서량 비교

· 연체 현황 자동 추적

교육 효과:

· 중복 데이터 입력 없이 효율적인 관리 경험

· 실제 도서관 시스템 작동 원리 이해

· 데이터 분석을 통한 독서 패턴 발견

사례 2: 학급 매점 운영 체험

상황: 학급에서 간단한 매점을 운영하며 판매 데이터를 분석해보자.

테이블 구성:

1. 상품 테이블: 상품코드, 상품명, 가격, 재고수량

2. 고객 테이블: 학번, 이름, 잔액

3. 판매 테이블: 판매번호, 학번, 상품코드, 수량, 판매일

실습 과정:

1. 각 테이블을 엑셀 테이블로 변환

2. 데이터 → 관계에서 연결 설정

3. 피벗 테이블로 매출 분석

분석 내용:

· 일별 매출액 변화 추이

· 인기 상품 베스트 5

· 학생별 구매 패턴 분석

· 재고 부족 상품 알림