아뜰리에(Atelier) 빅데이터 분석

강의 교안 · Lecture Notes

아뜰리에(Atelier) 빅데이터 분석

입지 결정 이론 × 실거래가 분석 × 엑셀 실습

PropTech Big Data Analysis — Individual Customized Track

이번 교안

🎨 아뜰리에 분석

개인 맞춤형 · 심층 데이터 · 특정 입지·자산에 집중하는 정밀 분석

다음 교안

🖥 플랫폼 분석

대중 일반형 · 전체 시장 · 광범위 패턴을 자동화·시각화하는 분석

세션

3세션 구성

핵심 도구

Excel · 실거래가공개시스템 · R(예고)

목표

이론→데이터수집→엑셀분석→R연계 전 과정

01

빅데이터와 입지 결정
이론 학습

02

실거래가 빅데이터
거래량 분석

03

엑셀 기초 분석 실습
(R 연계 준비)

01

빅데이터와 입지 결정의 관계 — 이론 학습

Big Data & Location Theory · 플랫폼 분석 vs. 아뜰리에 분석의 차이

1-1 | 아뜰리에 분석이란 무엇인가

빅데이터 분석은 목적에 따라 두 가지 방향으로 나뉜다. 플랫폼 분석이 불특정 다수를 위해 전체 시장의 패턴을 자동화·시각화하는 데 집중한다면, 아뜰리에 분석은 특정 고객·특정 자산·특정 입지를 위해 맞춤형으로 설계된 심층 분석이다.

🖥 플랫폼 분석 (대중 일반)

전체 시장 현황 제공 / 자동화된 대시보드 / 광역 트렌드·거래량 패턴 / 불특정 다수 독자 / 부동산 포털, 빅데이터 서비스

🎨 아뜰리에 분석 (개인 맞춤)

특정 입지·자산에 집중 / 경과연수·신축여부 등 세분화 / 고객 의사결정 지원 / 리포트 형태로 납품 / 컨설팅·자문·투자 분석

아뜰리에 분석의 핵심 질문

① 이 지역의 거래량은 증가하고 있는가? (시장 활성도)
② 신축과 구축의 가격 차이는 얼마인가? (경과연수 효과)
③ 경과연수에 따른 가격 변화 패턴은? (헤도닉 분석의 기초)

1-2 | 입지의 주요 결정 요인 5가지

입지 결정은 단일 요인이 아닌 여러 요소의 복합적 상호작용으로 이루어진다. 빅데이터는 이 각각의 요인을 정량적으로 측정·비교할 수 있게 해준다.

구분	주요 요인	실제 예시	활용 데이터
자연적 요인	지형, 기후, 토지 이용 가능성	한강변, 평지, 일조량 풍부한 남향	GIS 지형 데이터, 국토정보플랫폼
경제적 요인	토지가격, 거래량, 교통비, 시장 접근성	지하철역 반경 500m, CBD 인접성	실거래가공개시스템, 카카오맵 API
사회적 요인	인구, 소비력, 문화, 교육수준	강남·성수동의 고소득 소비자층	통계청 인구총조사, SKT 유동인구
제도적 요인	용도지역, 개발규제, 세제혜택	준주거지역, 국가산업단지, 경제자유구역	토지이음, 건축물대장, 세움터
기술적 요인	통신·물류 인프라, 디지털 접근성	데이터센터, 로지스틱스 허브, 5G 커버리지	과기부 통신망 데이터, 물류 GIS

아뜰리에 분석의 초점

5가지 요인 중 경제적 요인(거래량·가격·경과연수)은 실거래가공개시스템의 빅데이터로 가장 직접적으로 측정할 수 있다. 이번 실습의 핵심 데이터다.

1-3 | 입지분석 레포트의 3가지 핵심 질문

핵심 질문 01

거래량 추이

관심 지역의 거래량이 증가·감소하는가? → 시장 활성도와 수요 방향성 파악

핵심 질문 02

신축 vs. 구축

경과연수 5년 이하 = 신축. 가격 프리미엄이 얼마인가? → 재건축·리모델링 가치 산정

핵심 질문 03

경과연수별 가격

연수가 길수록 가격이 낮아지는가? (도심은 오히려 상승할 수 있음 - 정은상·김준형 2020)

1-4 | 엑셀 분석 → R 고급 분석으로의 연계 구조

실거래가 공개시스템
EXCEL 다운로드

→

Excel 전처리
변수 생성

→

Excel 피벗테이블
기초 통계

→

CSV 저장
R 불러오기

→

R 회귀분석
상호작용분석

아뜰리에 분석에서 엑셀이 중요한 이유

R·Python은 강력하지만, 데이터 구조를 눈으로 확인하고 변수를 직접 만들어 보는 과정이 선행되어야 고급 분석에서 오류를 최소화할 수 있다. 엑셀 실습은 R 분석의 '설계도'를 그리는 단계다.

02

실거래가 빅데이터를 활용한 거래량 분석

국토교통부 실거래가공개시스템 — 데이터 수집부터 권역 분류까지

2-1 | 실거래가공개시스템 데이터 수집

1

접속 및 유형 선택

rt.molit.go.kr 접속 → 상단 메뉴에서 상업/업무용 또는 아파트 선택 → 하단 '자료제공 바로가기' 클릭

2

조건 설정

계약일자: 분석 기간 설정 (최대 1년, 예: 2024-10-01 ~ 2025-09-30) → 시도: 서울특별시 → 시군구·읍면동·단지명 선택 (관심 지역)

3

EXCEL 다운로드

'EXCEL 다운' 버튼 클릭 → xlsx 파일 저장 → '편집 사용(E)' 클릭하여 제한된 보기 해제

4

데이터 구조 확인

행 1~12: 안내문 (분석 대상 아님) / 행 13: 헤더(NO, 시군구, 번지, …) / 행 14~: 실제 거래 데이터. 데이터 탭 → 중복된 항목 제거로 중복 확인 필수

주요 컬럼 설명 (아뜰리에 분석 핵심 변수)

G열 전용면적(m²) — 평단가 계산의 분모
J열 거래금액(만원) — 종속변수 후보
O열 건축년도 — 경과연수 계산의 기준
W열 구 — 권역(GBD/YBD/CBD/기타) 분류 기준
X열 동 — 소지역 분류 기준

2-2 | 권역(GBD / YBD / CBD / 기타) 분류 — SWITCH 함수

서울 상업용 부동산 분석의 기본 단위는 GBD(강남업무지구) · YBD(여의도업무지구) · CBD(도심업무지구)다. W열(구)을 기준으로 권역 변수를 생성한다.

열: Y열 (권역) — W열에 '구' 정보 존재 가정 EXCEL

■ SWITCH 함수 (권장 — 조건이 많을 때 간결) =SWITCH(W2,"강남구","GBD","서초구","GBD","마포구","YBD","영등포구","YBD","중구","CBD","종로구","CBD","기타") ■ IFS 함수 (대안 — 조건별 논리식 명시) =IFS(W2="강남구","GBD", W2="서초구","GBD", W2="마포구","YBD", W2="영등포구","YBD", W2="중구","CBD", W2="종로구","CBD", TRUE,"기타")

SWITCH vs. IF 선택 기준

조건이 6개 이상이면 IF 중첩은 가독성이 크게 저하된다. SWITCH는 Excel 2019 이상에서만 지원. 구형 엑셀은 IFS 또는 VLOOKUP(별도 참조 시트 활용)을 사용.

2-3 | 평단가(만원/평) 변수 생성

부동산 가격은 절대 금액보다 단위면적당 가격(평단가)으로 비교해야 한다. 전용면적(m²)을 평(坪)으로 환산한 후 거래금액을 나눈다.

열: Z열 (평단가) — G열: 전용면적(m²), J열: 거래금액(만원) EXCEL

■ 평단가 = 거래금액 ÷ 전용면적(평) ■ m² → 평 환산: 1평 = 3.3058m² ≈ 0.3025 사용 =J2/(G2*0.3025) ■ 소수점 정리 버전 (만원/평 기준) =ROUND(J2/(G2*0.3025), 1)

실거래가 샘플 (2025.09)

중구 신당동 래미안하이베르

84.85m² / 14만원 / 202509

평 환산

84.85 × 0.3025 = 25.67평

전용 84.85m² ÷ 3.305 = 약 25.7평

평단가 계산

14만 ÷ 25.67 = 5,454만원/평

CBD 권역 중위 수준

2-4 | 거래량 추이 분석 — 피벗테이블 기초

수집된 71,933건의 거래 데이터를 계약년월 × 신축여부로 피벗테이블을 구성하면, 월별 거래량 추이와 신축·구축 거래 비중을 동시에 파악할 수 있다.

계약년월	신축 평균 평단가	신축 거래수	구축 평균 평단가	구축 거래수	전체 평균 평단가	전체 거래수
202509	6,482	1,353	4,638	3,848	5,118	5,201
202508	5,817	1,229	4,378	3,222	4,776	4,451
202507	6,430	1,065	5,110	3,412	5,424	4,477
202506	6,793	3,350	5,091	8,947	5,554	12,297
202505	5,090	2,500	4,807	5,907	5,188	8,407
202503	7,084	2,705	5,497	7,807	5,906	10,512
총합계	6,507	18,959	5,018	52,974	5,411	71,933

피벗테이블에서 읽어야 할 것

① 거래량 패턴: 특정 월에 거래가 집중되는가? (2025.06 = 12,297건으로 급증 → 계절성·정책 효과 확인 필요)
② 신축 프리미엄: 신축 평단가(6,507) − 구축 평단가(5,018) = 약 1,489만원/평 차이
③ 구축 거래 비중: 52,974 / 71,933 = 73.6% → 서울 아파트 시장은 구축이 주도

03

R 빅데이터 고급 분석을 위한 엑셀 기초 실습

경과연수 · 신축여부 · 기초통계 — R 연계 전 데이터 정제 완성

3-1 | 경과연수 변수 생성

건축물대장·실거래가 데이터의 O열(건축년도)을 기준으로 경과연수를 계산한다. 경과연수는 후속 R 분석에서 가격 결정요인의 핵심 독립변수가 된다.

열: AA열 (경과연수) — O열: 건축년도(4자리 연도) EXCEL

■ 기본 계산 (현재 연도 - 건축년도) =2025-O2 ■ 동적 버전 (매년 자동 갱신) =YEAR(TODAY())-O2 ■ 주의: O열 값이 '2025' 숫자형인지 확인 텍스트형이면: =YEAR(TODAY())-VALUE(O2)

데이터 품질 주의사항

실거래가 데이터의 건축년도는 간혹 2자리 연도('96 → 1996)로 표기되거나, 앞에 작은따옴표가 붙어 텍스트로 저장되는 경우가 있다. 수식 입력 전 O열 셀 서식을 확인하고, 필요 시 VALUE() 함수로 숫자 변환.

3-2 | 신축여부 변수 생성 — 기준과 논거

신축 기준 설정의 근거

법적으로 명확한 '신축' 기준은 없다. 실무에서는 준공 후 5년 이하를 신축으로 구분하는 것이 관행이다 (분양가 상한제 적용 시 2년, 세금 우대 시 3년, 입주·거래 분석 시 5년). 본 분석에서는 경과연수 5년 이하 = 신축으로 정의한다.

열: AB열 (신축여부) — AA열: 경과연수 EXCEL

■ 기본 IF 함수 =IF(AA2<=5, "신축", "구축") ■ 3단계 구분 버전 (신축/준신축/구축) =IFS(AA2<=5, "신축", AA2<=15, "준신축", TRUE, "구축") ■ 숫자 더미 버전 (R 회귀분석용: 신축=1, 구축=0) =IF(AA2<=5, 1, 0)

R 연계 준비 포인트 — 더미변수(0/1) 생성

엑셀에서 "신축"/"구축" 텍스트로 만들어도 되지만, R로 내보낼 때는 0/1 숫자 더미변수가 회귀분석에 바로 투입 가능하여 전처리 시간을 절약한다. 두 버전을 각각 열로 만들어두는 것을 권장.

3-3 | 기초 통계량 계산 — AVERAGE · MEDIAN · QUARTILE

R 분석 전, 엑셀에서 기초통계를 먼저 확인하면 데이터 이상치·분포 형태를 파악하여 분석 방향을 설계할 수 있다.

기초 통계량 — AA열: 경과연수 / Z열: 평단가 (71,933개 행 기준) EXCEL

■ 경과연수 요약 통계 (텍스트 결합 버전) ="평균: " & ROUND(AVERAGE(AA2:AA71934),1) & " / 중위값: " & MEDIAN(AA2:AA71934) → 결과 예: "평균: 21.9 / 중위값: 22" ■ 평단가 요약 통계 ="평균: " & ROUND(AVERAGE(Z2:Z71934),1) & " / 중위값: " & ROUND(MEDIAN(Z2:Z71934),1) → 결과 예: "평균: 5,410.8 / 중위값: 4,657.9" ■ 경과연수 1사분위수 (하위 25% 경계값) =QUARTILE.INC(AA2:AA71934, 1) → 결과: 13 (경과연수 13년 이하가 전체의 하위 25%) ■ 4분위수 전체 (Q1, Q2, Q3, Q4) =QUARTILE.INC(AA2:AA71934, 2) → 중위값(Q2) =QUARTILE.INC(AA2:AA71934, 3) → 상위 25% 경계값(Q3)

경과연수 통계 (서울 아파트 2025.09 기준)

평균 21.9년 / 중위값 22년

1사분위수: 13년 → 서울 아파트의 25%는 경과연수 13년 이하 (비교적 최신)

평단가 통계 (서울 아파트 매매)

평균 5,411만원 / 중위값 4,658만원

평균 > 중위값 → 오른쪽 치우침(right-skewed). 고가 아파트가 평균을 끌어올림. R 분석 시 로그변환 고려.

3-4 | 신축·구축 피벗테이블 종합 분석 — 실습 단계별 가이드

1

피벗테이블 삽입

전체 데이터 선택 (Ctrl+A) → 삽입 탭 → 피벗테이블 → 새 워크시트에 생성

2

행 / 열 / 값 설정

행 레이블: 계약년월(H열) / 열 레이블: 신축여부(AB열) / 값: 평단가(Z열) → 평균, 거래금액(J열) → 개수

3

값 필드 설정 변경

값 영역 클릭 → '값 필드 설정' → 평단가는 평균, 거래건수는 개수로 설정. 표시 형식: 숫자 → 천단위 구분기호, 소수점 0자리

4

차트 삽입

피벗테이블 선택 → 피벗차트 삽입 → 콤보형(막대: 거래수 / 꺾은선: 평단가) 선택. 신축·구축 계열 각각 색상 지정.

5

CSV 저장 (R 연계)

원본 데이터 시트 선택 → 다른 이름으로 저장 → 파일 형식: CSV UTF-8(쉼표로 분리) → R에서 read.csv("파일명.csv", fileEncoding="UTF-8-BOM")

3-5 | R 고급 분석 예고 — 엑셀 결과를 R에서 확장하는 법

엑셀에서 만든 변수(평단가, 경과연수, 신축여부, 권역)는 R 회귀분석의 입력 데이터가 된다. 아래는 다음 세션에서 사용할 R 코드 구조의 예고다.

R — 경과연수와 평단가의 관계 분석 (예고) R

# 1. 데이터 불러오기 df <- read.csv("apt_trade.csv", fileEncoding="UTF-8-BOM") # 2. 기초 통계 확인 (엑셀과 비교) summary(df$평단가) quantile(df$경과연수, probs=c(0.25, 0.5, 0.75)) # 3. 신축·구축별 평단가 차이 (t-검정) t.test(평단가 ~ 신축여부, data=df) # 4. 단순회귀: 경과연수 → 평단가 model1 <- lm(평단가 ~ 경과연수, data=df) summary(model1) # 5. 다중회귀: 권역·신축여부 추가 (헤도닉 모형) model2 <- lm(평단가 ~ 경과연수 + 신축여부 + 권역 + 전용면적, data=df) summary(model2) # 6. 상호작용분석: 신축여부 × 경과연수 model3 <- lm(평단가 ~ 경과연수 * 신축여부, data=df) summary(model3)

엑셀 → R 연계의 학습 효과

① 엑셀에서 직접 만든 평단가·경과연수 변수가 R 모형의 독립·종속변수로 투입된다 → 변수가 왜 이렇게 생겼는지 이해하면 R 코드 해석이 쉽다
② 엑셀 피벗의 결과(신축 vs. 구축 가격 차이)를 R t-검정이 통계적으로 검증한다
③ R의 summary() 회귀 결과를 엑셀 분석과 비교하면 두 도구의 강점·한계가 명확해진다

3-6 | 종합 실습 과제 — 나만의 아뜰리에 입지분석 레포트

실습 과제 A — 데이터 수집 및 변수 생성 (필수)

관심 지역(서울 내 1개 구 이상) 아파트 매매 거래 데이터를 수집하고 다음 변수를 생성하시오.

• 실거래가공개시스템에서 최근 1년 데이터 다운로드 (EXCEL)
• 권역 변수 생성: SWITCH 함수로 GBD/YBD/CBD/기타 분류
• 평단가 변수 생성: =J열/(G열×0.3025)
• 경과연수 변수 생성: =2025-O열
• 신축여부 변수 생성: =IF(경과연수<=5, "신축", "구축")
• 기초통계 셀 작성: 평균·중위값·1사분위수

실습 과제 B — 분석 및 해석 (심화)

생성한 데이터로 피벗테이블을 구성하고 다음 질문에 답하는 입지분석 레포트(A4 2페이지)를 작성하시오.

• ① 관심 지역의 월별 거래량 추이 — 시장이 활성화되고 있는가, 위축되고 있는가?
• ② 신축과 구축의 평단가 차이 — 신축 프리미엄은 얼마이며, 이것이 의미하는 바는?
• ③ 경과연수별 평단가 패턴 — 연수가 길수록 가격이 낮아지는가, 높아지는가? (관심 지역의 특성과 연결)
• ④ 이 데이터로 R 회귀분석을 한다면, 어떤 독립변수를 추가하고 싶은가? 이유는?

∑

아뜰리에 분석 전체 흐름 정리

이론 → 데이터 수집 → 엑셀 전처리 → R 고급 분석의 연결고리

세션별 핵심 학습 포인트

세션 01 — 이론: 입지 결정 5요인 중 경제적 요인(거래량·평단가·경과연수)은 실거래가 빅데이터로 정량화 가능. 아뜰리에 분석의 3가지 핵심 질문 (거래량 추이 / 신축·구축 비교 / 경과연수별 가격) 확립.

세션 02 — 데이터 수집: 실거래가공개시스템 조건 설정 → EXCEL 다운로드 → 권역(SWITCH), 평단가(J/G×0.3025) 변수 생성 → 피벗테이블로 신축·구축 거래량 추이 분석.

세션 03 — 엑셀 실습: 경과연수(=2025-O), 신축여부(=IF), 기초통계(AVERAGE·MEDIAN·QUARTILE) 완성 → CSV 저장 → R 회귀분석 투입 준비 완료. 평균과 중위값의 괴리에서 오른쪽 치우침 분포를 발견하고 로그변환 필요성 인식.

엑셀 함수

SWITCH

IF / IFS

AVERAGE

MEDIAN

QUARTILE.INC

ROUND

YEAR / TODAY

VALUE

핵심 변수

평단가 (만원/평)

경과연수 (년)

신축여부 (0/1)

권역 (GBD/YBD/CBD)

계약년월

전용면적

R 연계 함수

read.csv()

summary()

t.test()

lm()

quantile()

ggplot2

NAI Korea BDRI — 프롭테크를 활용한 빅데이터 분석 | 아뜰리에 트랙

실거래가공개시스템(rt.molit.go.kr) 기반 교안 | 교육 목적 활용