공공데이터포털에서 받은 CSV 두 개로 지역별 비교표를 만들 때 계산은 나눗셈 한 번입니다. 구마다 공영주차면을 인구로 나누고 1,000을 곱하면 ‘인구 1천 명당 주차면’이 나옵니다. 손이 가는 곳은 나눗셈 앞쪽에 있습니다. 두 파일은 올린 기관도, 기준일도, 글자를 저장한 방식도 다르고, 인구 파일에는 합계 행이 섞여 있습니다.
예로 드는 곳은 가상 도시 ‘누리광역시’입니다. 교통정책과 담당자는 다음 주 업무보고에 넣을 구별 공영주차장 비교표를 맡았습니다. 공공데이터포털에서 시가 올린 공영주차장 정보 CSV와 행정동별 주민등록인구 CSV를 내려받았고, 두 파일을 클로드에게 그대로 첨부해 수식이 든 엑셀 비교표를 받습니다.
이 장에서 다루는 것
| 이런 분께 | 공공데이터포털이나 통계 사이트에서 받은 CSV로 지역별·기관별 비교표를 만들어 보고하는 분 |
| 이번 편 방식 | ① 대화창에 CSV 두 개를 첨부 → 수식이 든 엑셀 비교표 파일 받기 |
| 준비 파일 | 가상 도시의 공영주차장 정보 CSV(189행)와 행정동별 주민등록인구 CSV(45행) (예제 파일) |
| 완성 파일 | 비교표·주차장정리·인구원본·동대응표·검산·기준과출처 시트를 갖춘 엑셀 파일 |
| 엑셀 기능 | COUNTIFS·SUMIFS·VLOOKUP·RANK, 주소에서 낱말을 꺼내는 TRIM·MID·SUBSTITUTE, 막대 차트 |
| 쓰는 도구 | 클로드 앱 대화창(파일 첨부)과 엑셀. 챗GPT·제미나이에서도 같은 방식으로 요청할 수 있습니다 |
| 핵심 키워드 | 기준일 · 행정구역 변경 · 합계 행 · 인구 대비 비교 |
| 난이도 | ●●○ 중급 입문 — 수식을 직접 쓰지는 않지만, 비교 기준을 정하고 대응표를 확인합니다 |
먼저 답부터
만들 수 있습니다. CSV 두 개를 열지 않고 그대로 대화창에 첨부해 구조와 비교 기준부터 정리받은 뒤, 무엇을 셀지(공영만), 어느 기준의 구로 나눌지(현재 행정구역), 어떤 행을 더할지(행정동 행만)를 정해 주면 비교표·정리·대응표·검산 시트가 든 엑셀 파일을 수식째 받습니다. 기준일이 다른 두 파일을 합칠 때는 그사이 행정구역이 바뀌지 않았는지 먼저 확인합니다.
SECTION 13-1
13-1 북구가 1위, 하늘구는 0곳
이 장의 도시와 구, 동, 주차장, 인구는 모두 가상입니다. 실제 공공데이터포털 파일의 열 구성과 값은 기관과 데이터마다 다릅니다.
담당자는 먼저 손으로 초안을 만들었습니다. 주차장 CSV의 도로명주소에서 둘째 낱말(○○구)을 떼어 구별로 주차면을 더하고, 인구 CSV는 시군구명으로 더했습니다. 결과는 북구가 인구 1천 명당 41.98면으로 1위, 하늘구는 주차장 0곳이었습니다. 하늘구청 옆에도 공영주차장이 있으니 0곳일 리가 없습니다.
하늘구가 0곳으로 나온 까닭
주차장 CSV의 데이터기준일자는 2025년 12월 31일이고, 인구 CSV의 통계년월은 2026년 9월입니다. 하늘구는 2026년 1월 1일 북구의 별빛·새솔·한빛·은하동이 떨어져 나와 생겼습니다. 주차장 파일이 만들어질 때는 하늘구가 없었으니 주소에 모두 ‘북구’로 적혀 있습니다. 그래서 하늘구 공영주차장 15곳이 북구로 들어갔고, 인구는 하늘구가 따로 있으니 북구의 주차면은 늘고 인구는 줄어 1위가 되었습니다.
그 밖에 섞여 있던 것
| 파일에 있던 것 | 초안에서 생긴 일 | 규모 |
|---|---|---|
| 파일마다 기준일이 다름 | 하늘구 주차장이 북구로 들어감 | 하늘구 공영 15곳 · 1,538면 |
| 인구 파일에 합계 행 | 시 합계 1행, 구 합계 6행까지 더해져 구 인구가 두 배 | 초안 인구 합계 1,479,070명 |
| 민영주차장이 섞임 | 공영 비교에 민영 주차면이 들어감 | 민영 48곳 |
| 노상주차장 도로명주소가 빈칸 | 도로명주소로 구를 나누면 빠짐 | 공영 51곳 (중구가 16곳 빠져 3위로) |
| 주차구획수가 빈 공영주차장 | 면수 0으로 더해지는데 아무도 모름 | 2곳 |
| 두 CSV의 글자 저장 방식이 다름 | 엑셀에서 바로 연 인구 파일의 한글이 깨져 보임 | 주차장 CP949 · 인구 UTF-8 |
엑셀왕 TIP 두 파일의 기준일부터 적어 두기
공공데이터는 파일마다 기준일이 다릅니다. 주차장처럼 1년에 한 번 바뀌는 자료와 인구처럼 달마다 바뀌는 자료를 합치면 몇 달씩 어긋나는 일이 흔합니다. 받은 파일 이름 옆에 기준일을 적어 두고, 그사이 행정구역이 바뀌었는지 떠올려 보는 것이 첫 단계입니다.
SECTION 13-2
13-2 구조 확인부터 검산까지 다섯 단계
클로드에서는 CSV는 열지 말고 그대로 첨부하기
공공데이터포털에서 받은 CSV를 엑셀에서 열어 고치지 말고 받은 그대로 대화창에 첨부합니다. 엑셀에서 바로 열었을 때 한글이 깨져 보이던 인구 파일도 파일 자체는 멀쩡합니다. 첨부하면 클로드가 파일마다 글자 저장 방식과 행 수, 열 이름부터 확인하고 읽습니다. 엑셀에서 직접 열어 보고 싶다면 파일을 두 번 눌러 열지 말고 [데이터] → [텍스트/CSV에서]로 가져오면, 불러오기 전 미리 보기에서 글자가 제대로 보이는지 확인할 수 있습니다.
공개된 공공데이터라 가명화할 것은 없습니다. 다만 내부 자료(부서별 실적, 민원 대장 등)를 함께 붙여 비교할 때는 그 파일부터 공개해도 되는 자료인지 확인합니다.
STEP 1 두 파일 구조와 비교 기준 듣기
이렇게 말하기
공공데이터포털에서 받은 CSV 두 개를 첨부할게. 공영주차장 정보와 행정동별 주민등록인구야. 구별로 인구 1천 명당 공영주차면을 비교하는 표를 엑셀로 만들고 싶어. 표를 만들기 전에 두 파일 구조와 비교 기준부터 정리해 줘.
그러면 처리되는 일
· 주차장 CSV(CP949, 189행)와 인구 CSV(UTF-8, 45행)를 각각 읽고 열 이름과 기준일을 확인합니다.
· 주차장 주소에는 하늘구가 없고 인구 파일에는 있다는 차이, 민영주차장과 도로명주소가 빈 노상주차장, 인구 파일의 시 합계·구 합계 행을 짚습니다.
· 그대로 합치면 생기는 일을 표로 보여 주고, 사람이 정해야 할 것 세 가지를 묻습니다.
STEP 2 비교 기준 정하기
이렇게 답하기
공영만 세 줘. 구는 인구 파일과 같은 현재 행정구역으로 맞춰 줘. 하늘구는 2026년 1월 1일에 북구의 별빛·새솔·한빛·은하동이 나뉘어 생겼어. 주소의 법정동으로 구를 찾는 대응표를 먼저 보여 줘. 인구는 행정동 행만 더하고, 주차구획수가 빈 2곳은 주차장 수에는 넣고 면수는 0으로 두되 따로 목록에 남겨 줘.그러면 처리되는 일
· 정한 기준 다섯 줄을 ‘기준과출처’ 시트에 적고, 비교표 맨 위에 두 파일 이름과 기준일을 한 줄로 넣기로 합니다.
· 도로명주소는 빈 곳이 많으니 소재지지번주소에서 구와 법정동을 꺼내 쓰자고 제안합니다. 지번주소는 189행 모두 차 있습니다.
· 인구 파일의 행정동 이름(누리1동·누리2동)과 주소의 법정동 이름(누리동)이 다른 곳을 묶은 동대응표 초안을 보여 줍니다.
엑셀왕 노트 주소의 동과 통계의 동
주소에 적는 동(법정동)과 인구 통계의 동(행정동)은 이름이 다를 수 있습니다. 행정안전부 데이터 설명에 따르면 행정동은 행정 능률과 주민 편의를 위해 나눈 읍면동 단위입니다. 이 장의 누리동처럼 법정동 하나가 행정동 둘로 나뉜 곳은 대응표에서 하나로 묶습니다. 어느 동이 언제 어느 구로 옮겼는지는 파일만 봐서는 알 수 없으니, 시의 행정구역 공고나 업무 자료로 담당자가 확인해 알려 줍니다.
STEP 3 주소 열과 동대응표 확인하기
받은 파일의 주차장정리 시트에는 원본 189행 오른쪽에 노란 머리글의 수식 열 다섯 개가 붙어 있습니다. 주소상 구와 법정동은 지번주소에서 꺼내고, 현재 구는 동대응표에서 찾습니다. 비교 포함 열은 공영이면 ‘포함’, 민영이면 ‘제외(민영)’이고, 비교 면수는 포함인 행의 주차구획수입니다.
수식 한 줄 읽기 U32
=TRIM(MID(SUBSTITUTE(E32," ",REPT(" ",100)),201,100))· SUBSTITUTE(E32,” “,REPT(” “,100)) → 지번주소의 띄어쓰기 한 칸을 빈칸 100칸으로 늘립니다. 낱말 사이가 100칸씩 벌어집니다.
· MID(…,201,100) → 201번째 글자부터 100글자를 떼면 셋째 낱말이 든 구간만 잡힙니다.
· TRIM(…) → 앞뒤 빈칸을 지워 ‘은하동’만 남깁니다.
한 줄로 읽으면 “지번주소의 셋째 낱말을 꺼낸다”입니다. 101로 바꾸면 둘째 낱말인 구가 나옵니다. 주소가 ‘누리광역시’로 시작하든 ‘누리시’로 시작하든 띄어쓰기 순서로 세기 때문에 결과가 같습니다.
동대응표 시트에는 법정동 32개가 현재 구와 함께 한 줄씩 있고, 하늘구 4개 동에는 ‘2026-01-01 북구에서 분리’라는 비고가 붙어 있습니다. 이 표는 사람이 확인합니다. 내가 아는 행정구역과 다른 줄이 있으면 그 줄만 고쳐 달라고 하면 됩니다. 주소에서 꺼낸 법정동이 대응표에 없으면 현재 구가 ‘미배정’으로 나오도록 해 두었으니, 미배정이 0인지도 봅니다.
STEP 4 비교표 받기
이렇게 말하기
정한 기준대로 엑셀 파일로 만들어 줘. 비교표에는 구별 공영주차장 수, 주차면 합계, 인구, 인구 1천 명당 주차면, 순위를 수식으로 넣고 맨 위에 두 파일의 기준일을 적어 줘. 1천 명당 주차면으로 막대 차트도 하나 그려 줘.그러면 처리되는 일
· 비교표의 주차장 수와 주차면은 주차장정리 시트를 현재 구와 비교 포함으로 COUNTIFS·SUMIFS 합니다.
· 인구는 인구원본 시트에서 행 종류가 ‘행정동’인 행만 SUMIFS로 더합니다. 행 종류 열이 시 합계·구 합계·행정동을 나눕니다.
· 1천 명당 주차면은 소수 둘째 자리까지, 순위는 RANK로 넣고, 시 전체 줄과 막대 차트를 붙입니다.
| 구 | 처음 만든 초안 | 기준을 맞춘 비교표 |
|---|---|---|
| 중구 | 16.42면 · 3위 | 26.85면 · 1위 |
| 동구 | 7.25면 · 5위 | 10.17면 · 6위 |
| 서구 | 8.61면 · 4위 | 10.97면 · 5위 |
| 남구 | 18.17면 · 2위 | 18.19면 · 2위 |
| 북구 | 41.98면 · 1위 | 15.13면 · 3위 |
| 하늘구 | 0.00면 · 6위 | 13.37면 · 4위 |
초안에서 1위였던 북구는 하늘구 주차장 15곳이 빠지면서 3위가 되었고, 노상주차장이 많아 도로명주소로는 16곳이 빠졌던 중구가 1위로 올라왔습니다. 시 전체는 인구 1천 명당 14.38면입니다.
엑셀왕 노트 1천 명당 숫자를 보고할 때
주민등록인구는 그 구에 주소를 둔 사람 수입니다. 사는 사람은 적고 낮에 오가는 사람이 많은 구도심은 1천 명당 숫자가 높게 나오기 쉽습니다. 이 장의 중구가 그런 경우입니다. 숫자 옆에 “주민등록인구 기준, 구도심은 유동 인구가 많아 높게 나옴”처럼 한 줄 설명을 붙이면 표를 읽는 사람이 순위만 보고 판단하지 않습니다.
STEP 5 검산 시트로 확인하기
검산 시트는 원본에서 바로 센 값과 비교표·정리 시트의 값을 나란히 놓고 차이를 봅니다. 주차장 전체 189행이 공영과 민영으로 남김없이 나뉘었는지, 공영 141곳과 주차면 10,638면이 비교표 합계와 같은지, 구를 못 정한 행이 0인지 봅니다. 인구는 인구 파일에 들어 있는 시 합계 행과 구 합계 행을 검산 기준으로 씁니다. 행정동 행만 더한 값이 합계 행과 같으면 빠진 동도 겹친 동도 없다는 뜻입니다.
차이가 모두 0이고 결과가 ‘일치’면 표는 정한 기준대로 만들어진 것입니다. 남은 일은 확인 목록의 2곳을 주차장 담당 부서에 물어 면수를 채우는 것, 그리고 보고서에 두 파일의 기준일을 함께 적는 것입니다.
SECTION 13-3
13-3 누가 무엇을 하나
| 맡는 쪽 | 하는 일 |
|---|---|
| 사람 | 무엇을 셀지(공영만)와 어느 기준의 구로 나눌지 정하고, 하늘구처럼 파일에 없는 행정구역 변경을 알려 줍니다. 동대응표를 확인하고, 1위가 왜 높은지 설명을 붙여 보고합니다. |
| 클로드 | 두 파일의 글자 저장 방식·기준일·합계 행·빈칸을 짚고, 주소에서 동을 꺼내는 수식과 동대응표, 비교표와 검산 시트를 설계합니다. |
| 엑셀 | COUNTIFS·SUMIFS·VLOOKUP·RANK로 구별 숫자와 순위를 계산하고, 원본이 바뀌면 다시 계산합니다. |
손으로 할 때는 CSV를 하나씩 열어 깨진 글자를 바로잡고, 주소에서 구를 잘라 내고, 피벗 테이블 두 개를 만들어 옆에 놓고 나눴습니다. 기준일 차이는 표가 이상하게 나온 뒤에야 알아챘습니다.
한 번에 말하기
한 번에 말하는 요청
공공데이터포털에서 받은 CSV 두 개로 지역별 비교표를 엑셀로 만들려고 해. 파일을 첨부할게.
표를 만들기 전에 두 파일의 기준일, 글자 저장 방식, 합계 행, 빈칸, 지역 이름이 서로 맞는지부터 정리해 줘.
내가 무엇을 셀지와 어느 기준의 지역으로 나눌지 정하면, 비교표·정리·대응표·검산 시트를 수식으로 만들어 주고, 비교표 맨 위에 두 파일의 기준일을 적어 줘.챗GPT·제미나이에 같은 요청을 보내도 됩니다. 받은 표는 AI마다 다를 수 있으니, 검산 시트의 차이와 대응표를 직접 보고 판단하세요.
내 업무에 바꿔 쓰기
첫 문장의 두 파일과 나누는 단위만 바꾸면 됩니다.
| 업무 | 첫 문장 바꿔 쓰기 |
|---|---|
| 시도별 공공도서관 | “도서관 현황 CSV와 시도별 인구 CSV로 인구 10만 명당 도서관 수를 …” (시도 이름 바뀐 곳 확인) |
| 구별 어린이집 정원 | “어린이집 현황과 연령별 인구로 영유아 1천 명당 정원을 …” (연령 구간 행만 더하기) |
| 읍면동별 전기차 충전기 | “충전기 설치 현황과 행정동별 인구로 읍면동별 비교표를 …” (법정동·행정동 대응표) |
| 기관별 민원 처리 | “부서별 민원 대장과 부서 정원표로 직원 1인당 처리 건수를 …” (조직 개편 전후 부서 이름 맞추기) |
이 장의 핵심
두 파일을 합치기 전에 기준일을 나란히 적고, 그사이 행정구역이 바뀌지 않았는지 본다.
무엇을 셀지, 어느 기준의 지역으로 나눌지, 어떤 행을 더할지는 사람이 정하고, 클로드는 그 기준을 정리 수식과 대응표로 옮긴다.
파일 안의 합계 행은 더하지 말고 검산 기준으로 쓰고, 비교표 맨 위에 두 파일의 기준일을 적어 보고한다.
직접 해 보기 새로 올라온 2026년 6월 기준 파일
예제 파일의 ‘직접해보기/누리광역시_공영주차장정보_20260630.csv’는 반년 뒤 시가 새로 올린 공영주차장 파일입니다. 주소가 하늘구로 바뀌어 있고, 공영주차장이 새로 생기거나 없어졌고, 비어 있던 주차구획수도 채워졌습니다. 같은 비교 기준으로 비교표를 새로 만들려면 클로드에게 어떻게 말하면 될까요? 이 파일에는 처음 보는 함정이 하나 더 있습니다.
답 예시
누리광역시 공영주차장 CSV가 2026-06-30 기준으로 새로 올라왔어. 지난번 비교표 파일과 새 CSV를 첨부할게. 같은 기준(공영만, 현재 구, 행정동 행만)으로 주차장정리 시트를 새 파일로 바꾸고, 지난번 파일과 비교해 새로 생긴 곳·없어진 곳·면수가 바뀐 곳을 따로 정리해 줘. 같은 주차장이 두 번 들어 있는지도 확인해 줘.새 파일의 공영 행은 145개인데, 그중 2개는 같은 주차장(은하 공영, 새벽 공영)을 시설관리공단이 한 번 더 제공한 행입니다. 주차장명과 지번주소가 같고 관리기관명과 제공기관명만 다릅니다. 그대로 더하면 11,488면, 중복을 빼면 공영 143곳 11,245면입니다. 하늘구는 17곳 1,818면으로 인구 1천 명당 15.80면, 주차구획수가 채워진 북구는 17.50면이 되어야 합니다. 순위는 그대로입니다. 주소가 이미 하늘구로 적혀 있어도 동대응표로 찾은 현재 구와 같으니 대응표는 그대로 둡니다. 어떤 행을 중복으로 볼지(주차장명+지번주소)는 사람이 정하고, 검산 시트에 ‘중복으로 뺀 행’ 줄을 하나 더해 달라고 하면 다음 파일부터는 그 줄까지 확인할 수 있습니다.
처음 질문으로 돌아가면, 공공데이터포털 CSV를 정리해 지역별 비교표를 만들 수 있습니다. 누리광역시 파일 두 개로 만든 초안은 북구 1위, 하늘구 0곳이었고, 기준을 맞춘 비교표는 중구 1위, 하늘구 15곳 1,538면이었습니다. 계산식은 같은 나눗셈입니다. 달라진 것은 나눗셈 앞의 세 가지, 무엇을 셀지와 어느 날 기준의 구로 나눌지와 어떤 행을 더할지였습니다.
APPENDIX
예제 파일
공영주차장 정보 CSV와 행정동별 주민등록인구 CSV(원본 폴더), 비교표·주차장정리·인구원본·동대응표·검산·기준과출처 시트가 든 ‘완성예시/누리광역시_구별_공영주차장_비교표.xlsx’, ‘직접 해 보기’용 2026년 6월 기준 CSV, 파일 설명을 적은 ‘읽어보기.txt’를 한 번에 받을 수 있습니다. 두 CSV는 일부러 글자 저장 방식을 다르게 해 두었습니다(주차장 CP949, 인구 UTF-8). 모든 도시·주차장·인구 숫자는 가상 데이터입니다.
이 글에 대하여
예제 파일의 수식과 본문 숫자는 2026년 10월 28일 LibreOffice로 다시 계산해 확인했습니다. ‘처음 만든 초안’과 ‘직접 해 보기’의 숫자는 같은 기준을 파이썬으로 계산해 맞췄습니다. 이 글의 화면은 모두 예제 파일 값으로 다시 그린 ‘재현 화면’입니다.
공공데이터포털 파일의 항목 구성은 같은 날 공공데이터포털의 공영주차장 현황 파일 페이지(주차장명·주차장구분·주차장유형·소재지도로명주소·소재지지번주소·주차구획수 등)와 행정안전부 행정동별 통계 파일 페이지(행정기관코드·통계년월·시도명·시군구명·읍면동명, 행정동 설명)를 참고했습니다. 엑셀의 [데이터] → [텍스트/CSV에서] 가져오기는 Microsoft 지원 문서 ‘텍스트(.txt 또는 .csv) 파일 가져오기 또는 내보내기’를 기준으로 적었습니다.
이 연재는 저자가 기획하고 AI 도구의 도움을 받아 집필·정리했습니다. 구성, 사실 확인, 표현, 최종 판단과 출판 책임은 저자와 출판사에 있습니다. 클로드와 엑셀의 화면과 기능은 바뀔 수 있으며, 이 글은 2026년 10월 기준입니다. 초판 선공개 원고 v1.0.
『클로드 엑셀왕』 연재 목록 · 이전 편: 버튼 하나로 갱신·점검하기, 매주 하는 정리 작업을 버튼 하나로 끝낼 수 있나요? · 다음 편: 성적 분석표 만들기, 반별·과목별 성적 분석표를 만들 수 있나요?