엑셀 종합 프로젝트: 완성된 솔루션 처음부터 끝까지 만들기

이 프로젝트는 전문가 과정에서 배운 내용을 하나의 작업으로 묶어요. 원본 다섯 개로 새로 고칠 수 있는 운영 모델을 만들고, 내가 손을 뗀 뒤 다른 팀이 맡게 될 것처럼 완성해서 넘기는 거예요. 정답이 하나로 정해진 과제가 아니라서, 이 안내서는 요청서, 순서대로 진행할 단계, 단계마다 만들어 내야 할 결과물, 완성한 통합 문서를 검토할 기준을 알려 줘요. 한자리에서 끝내기보다 여러 번에 나눠 작업할 계획을 세우세요.

준비됐는지 확인하기

단계마다 앞에서 배운 기술을 써요. 아래 중 자신 없는 항목이 있다면 먼저 그 글로 돌아가세요. 이 과제는 기술을 처음 배우는 자리가 아니라 중간 점검을 위한 자리예요.

요청서

영업 운영팀이 매주 쌓이는 스프레드시트 더미를 통합 문서 하나로 바꾸고 싶어 해요. 운영 관리자는 매주 월요일, 어느 지역이 월간 목표에 뒤처져 있는지와 어떤 주문을 살펴봐야 하는지 알아야 해요. 영업 이사는 한 달에 한 번, 지역별 목표 대비 매출과 추세를 봐야 해요.

모델에 들어가는 원본은 다섯 개예요.

실제 내보내기 파일이 없다면 실제와 비슷하게 만들어 보세요. 몇백 행짜리 주간 CSV 파일 세 개와 나머지 작은 표 네 개면 돼요. 문제도 일부러 심어 두세요. Product ID가 빈 판매 한 건, 지역 표에 없는 Region ID를 가진 판매 한 건, 매출이 텍스트로 입력된 값 하나 정도요. 모델은 잘못된 데이터를 만나 봐야 제대로 테스트된 거예요.

1단계: 설계 요청서와 통합 문서 구성도 쓰기

파워 쿼리를 열기 전에 한 페이지짜리 설계 요청서를 쓰세요. 두 가지 의사 결정(이번 주에 뒤처진 지역은 어디인가, 이번 달 지역별 목표 대비 실적은 어떤가), 의사 결정 담당자, 입력, 출력, 새로 고침 주기(매주), 그리고 다른 사람이 테스트할 수 있는 승인 기준을 세 개 이상 적으세요.

그런 다음 통합 문서의 계층을 이 프로젝트에 맞춰 정리하세요.

완료 기준: 무엇을 만들기 전에 요청한 사람이 요청서를 읽고 동의했어요.

2단계: 파워 쿼리로 데이터 가져오고 검증하기

• 주간 파일은 폴더에서 불러오세요. 데이터› then 데이터 가져오기 및 변환› then 데이터 가져오기› then 파일에서› then 폴더에서를 고른 다음 데이터 결합 및 변환(Combine & Transform Data)을 선택하세요. 폴더에 새 파일을 넣으면 다음 새로 고침 때 자동으로 들어와요. 결합 단계에서 추가되는 Source.Name 열은 지우지 말고 두세요. 그래야 모든 행이 어느 파일에서 왔는지 알 수 있어요.
• 폴더 경로는 매개 변수에 넣으세요. 파워 쿼리 편집기의 매개 변수 관리(Manage Parameters)를 쓰면 폴더를 옮겨도 단계를 고칠 필요 없이 값 하나만 바꾸면 돼요.
• 데이터 형식 지정은 별도 단계로 두고, 그 단계에서 실패한 값을 처리하세요. 텍스트로 입력된 매출 값은 열을 숫자 형식으로 바꿀 때 오류가 돼요.
• 잘못된 행은 지우지 말고 따로 보내세요. 거부된 행을 이유와 함께 나열하는 Exceptions 쿼리를 만드세요. 형식 변환에서 생긴 오류, 빈 Product ID, 키가 맞는 짝이 없는 판매가 여기에 들어가요. Sales와 Products를 왼쪽 앤티(Left Anti) 방식으로 병합하면 Product ID가 제품 표에 없는 판매만 정확히 나와요. Exceptions는 시트로 로드하세요. 운영팀이 읽을 목록이에요.
• 스테이징 쿼리는 연결만 만드세요. 파워 쿼리 편집기의 홈 탭에서 닫기 및 로드의 화살표를 클릭하고 닫기 및 다음으로 로드(Close & Load To)를 고르세요. 연결만 만들기를 선택하고, 모델에 필요한 표라면 이 데이터를 데이터 모델에 추가에 체크하세요.

완료 기준: 폴더에 새 주간 파일을 넣고 데이터› then 쿼리 및 연결› then 모두 새로 고침을 클릭하면 쿼리를 하나도 고치지 않아도 그 행들이 모델에 들어오고, 심어 둔 문제가 모두 Exceptions에 나타나요.

3단계: 데이터 모델링과 재사용 로직 만들기

스타 스키마를 만드세요. Sales를 가운데에 두고 Products, Regions, Calendar와 연결해요. Targets는 단위가 다른 두 번째 팩트 테이블이에요(지역별, 월별로 한 행). 목표마다 그 달의 첫날 날짜를 붙여 저장하고, Regions와는 Region ID로, Calendar와는 날짜로 연결하세요. 그러면 두 팩트 테이블이 같은 차원으로 필터링되어서, 행에 Region을 넣은 피벗 테이블 하나에 매출과 목표를 나란히 표시할 수 있어요. 목표는 월 단위 이상으로 보고하세요. 일 단위로 보면 목표가 모두 1일에만 붙어 있어요.

측정값은 세 개 이상, 앞의 측정값을 바탕으로 만드세요.

Total Revenue := SUM(Sales[Revenue])
Total Target := SUM(Targets[Goal])
% of Target := DIVIDE([Total Revenue], [Total Target])

DIVIDE는 아직 목표가 없는 지역에 오류 대신 빈 값을 돌려줘요. 그런 지역은 QA 점검에서 걸러 내면 돼요.

상태 규칙(On target, Watch, Behind)은 두 보고서 페이지에서 모두 쓰니 한 번만 정의하세요. Watch 기준값(예: 90%)을 가정 목록의 WatchLevel이라는 이름 붙은 셀에 넣고, 아래 LAMBDA를 수식› then 정의된 이름› then 이름 관리자에 TargetStatus라는 이름으로 저장하세요.

=LAMBDA(pct, IF(pct>=1,"On target",IF(pct>=WatchLevel,"Watch","Behind")))복사

C5에 있는 % of Target 값이 95%라면 =TargetStatus(C5)는 Watch를 돌려줘요. 회사가 Watch 기준을 바꾸면 셀 하나만 고치면 되고, 두 페이지가 모두 따라 바뀌어요.

완료 기준: 지역별, 월별 피벗 테이블에 매출, 목표, 목표 대비 비율이 올바르게 표시되고, WatchLevel을 바꾸면 상태도 바뀌어요.

4단계: 두 보고서 페이지 설계하기

두 대상에게는 같은 모델 위에 만든 두 페이지가 필요해요. 시각적 개체마다 어떤 의사 결정을 돕는지 적으세요. 적을 수 없다면 그 개체는 필요 없는 거예요.

페이지마다 월 슬라이서와 지역 슬라이서를 하나씩 두고, 그 페이지의 모든 피벗 테이블에 연결하세요(슬라이서를 마우스 오른쪽 단추로 클릭하고 보고서 연결 선택). 두 페이지 모두 맨 위에 새로 고침 날짜와 전체 QA 상태를 표시하세요. 그래야 숫자가 얼마나 최신이고 믿을 만한지 독자가 알 수 있어요.

완료 기준: 각 대상이 도움 없이 자기 답을 찾을 수 있고, 모든 시각적 개체 옆에 돕는 의사 결정이 적혀 있어요.

5단계: QA 점검으로 숫자 증명하기

Checks 시트에 원본 합계와의 대사를 포함해 점검을 세 개 이상 추가하세요. 내보내기 파일과 비교한 행 수, 내보내기 파일의 통제 합계와 비교한 매출 합계, 제품이 연결되지 않은 판매 0건, 지역이 연결되지 않은 판매 0건, 활성 지역마다 목표 있음, 가장 이른 판매와 가장 늦은 판매를 모두 포함하는 달력 같은 점검이에요.

아래 예시를 보면 점검이 한 종류로는 부족한 이유를 알 수 있어요. 주문 O-105의 Region ID는 R9인데, 지역 표에 없는 ID예요.

fx

	A	B	C	D	E	F	G	H
1	Order ID	Region ID	Revenue	Region		Region ID	Region	Target
2	O-101	R1	1,200	북부		R1	북부	3,000
3	O-102	R2	800	남부		R2	남부	2,000
4	O-103	R1	1,500	북부		R3	동부	2,000
5	O-104	R3	950	동부
6	O-105	R9	400	미지정		선택한 지역	R1
7	O-106	R2	1,100	남부		매출	3,350
8	O-107	R3	700	동부		미지정 행	1
9	O-108	R1	650	북부		합계 점검	통과
10
11	내보내기 합계		7,300

합계 점검은 통과해요. 판매 여덟 건의 합계가 7,300으로 내보내기 파일과 같거든요. 하지만 북부, 남부, 동부를 합치면 6,900뿐이에요. O-105의 400이 어느 지역에도 속하지 않기 때문이에요. 대사 점검만 했다면 이 보고서를 맞다고 판단했을 거예요.

완료 기준: 깨끗한 데이터에서는 모든 점검이 통과로 표시되고, 데이터를 일부러 망가뜨리면 점검마다 실패로 바뀌어요.

6단계: 한 단계 자동화하기

사람들이 반복하는 단계를 하나 골라, 그 일을 안정적으로 해내는 가장 단순한 도구를 고르세요.

고르기 전에 그 단계가 무엇에 기대는지 확인하세요. 클라우드 흐름은 내 컴퓨터에 있는 폴더를 읽는 쿼리를 새로 고칠 수 없고, VBA 매크로는 웹용 Excel로 파일을 연 사람에게는 실행되지 않아요. 자동화가 무엇을 하는지, 무엇이 실행을 시작하는지, 실패하면 어떻게 되는지, 누가 관리하는지 적어 두세요.

7단계: 보호하고 문서화하고 인수인계하기

• 보호하세요. 수식이 있는 시트는 모두 보호하고, 가정 목록의 입력 셀은 잠금을 풀어 두고, 통합 문서 구조도 보호하세요.
• 다른 통합 문서로 연결된 링크를 없애고, 버전 번호와 날짜를 적으세요.
• 메모 시트에 인수인계 메모를 쓰세요. 목적과 의사 결정 담당자, 파일 위치, 번호를 매긴 새로 고침 절차, 공유 전에 확인할 것, 알려진 위험(예: 내보내기 파일의 열 이름이 바뀌면 Sales 쿼리가 깨짐, 새 지역은 Regions와 Targets 둘 다에 추가해야 함), 유지 관리 담당자와 대리인, 롤백 계획을 적어요.

그런 다음 진짜 테스트를 하세요. 이 통합 문서를 처음 보는 사람에게 파일과 새 주간 파일을 주고, 메모 시트만 보고 새로 고치고, 점검하고, 뒤처진 지역을 알려 달라고 하세요. 돕지 말고 지켜보세요. 그 사람이 머뭇거리는 곳이 모두 내 마무리 작업 목록이에요.

완료 기준과 비교해 검토하기

자주 하는 실수

• 요청서에 동의받기 전에 만들기. 아무도 묻지 않은 질문에 답하는 모델이 돼요.
• 잘못된 행 지우기. 합계에서 사라지고, 그런 행이 있었다는 사실조차 아무도 몰라요. Exceptions로 보내세요.
• 비율을 행마다 계산하기. 행별 목표 대비 비율을 평균 내면 지역의 값이 틀려요. 측정값은 합계끼리 나눠요.
• 아직 바뀌고 있는 단계 자동화하기. 수동으로 몇 번 똑같이 실행해 본 다음에 자동화하세요.
• 내 컴퓨터에서만 테스트하기. 경로, 추가 기능, 버전은 사람마다 달라요. 인수인계 테스트가 그걸 찾아 줘요.

연습

연습

새로 고칠 수 있는 완전한 모델 만들기

1. 요청서, 통합 문서 구성도, 승인 기준 세 개를 쓰고 동의를 받으세요.
2. 폴더 쿼리, 원본별 스테이징 쿼리, Exceptions 쿼리를 만드세요.
3. 표를 스타 스키마로 연결하고 측정값 세 개와 TargetStatus 함수를 만드세요.
4. 슬라이서가 있는 Executive 페이지와 Operations 페이지를 만들고, 시각적 개체마다 옆에 돕는 의사 결정을 적으세요.
5. 대사를 포함한 QA 점검을 세 개 이상 추가하고 전체 상태를 표시하세요.
6. 한 단계를 자동화하고 문서로 남기세요.
7. 통합 문서를 보호하고 버전을 매기고 문서화한 다음, 다른 사람에게 넘겨 막히는 곳을 모두 고치세요.

예시 데이터로 먼저 몸을 풀어 보세요. 판매를 지역에 연결하고, 한 지역의 합계를 구하고, 지역 없는 행을 세고, 합계를 내보내기 파일과 맞춰 봐요.

직접 해 보기4개 중 0개 완료

1. 1.D6에 B6의 지역 ID에 해당하는 지역 이름을 찾고, F2:F4에 없으면 미지정을 표시해 보세요.힌트 보기
2. 2.G7에 G6에서 선택한 지역 ID의 매출을 C2:C9에서 모두 더해 보세요.힌트 보기
3. 3.G8에 D2:D9에서 미지정으로 표시된 판매 행의 수를 세어 보세요.힌트 보기
4. 4.G9에 C2:C9의 합계가 C11의 내보내기 합계와 같으면 통과, 아니면 실패를 표시해 보세요.힌트 보기

D6fx수식 입력 셀 D6Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E	F	G	H
1	Order ID	Region ID	Revenue	Region		Region ID	Region	Target
2	O-101	R1	1,200	북부		R1	북부	3,000
3	O-102	R2	800	남부		R2	남부	2,000
4	O-103	R1	1,500	북부		R3	동부	2,000
5	O-104	R3	950	동부
6	O-105	R9	400			선택한 지역	R1
7	O-106	R2	1,100	남부		매출
8	O-107	R3	700	동부		미지정 행
9	O-108	R1	650	북부		합계 점검
10
11	내보내기 합계		7,300

실력 점검

• 모호한 요청을 의사 결정, 담당자, 테스트할 수 있는 승인 기준이 담긴 요청서로 바꿀 수 있나요?
• 새 파일을 자동으로 가져오고, 잘못된 행을 지우는 대신 예외 목록으로 보내는 쿼리를 만들 수 있나요?
• 내 모델의 팩트 테이블과 차원 테이블을 설명하고, 어떤 필터에서도 올바른 답을 내는 측정값을 만들 수 있나요?
• 자동화 방법을 고르고, 유지 관리 위험을 근거로 그 선택을 설명할 수 있나요?
• 다른 사람이 나 없이 새로 고치고, 점검하고, 필요하면 롤백할 수 있도록 통합 문서를 넘길 수 있나요?