엑셀 솔루션 설계하기: 요구 사항 정리와 구조 잡기

"월간 매출 대시보드 좀 만들어 줄 수 있어요?"는 요청처럼 들리지만 사양서는 아니에요. 누가 쓸지, 그걸로 무엇을 결정할지, 얼마나 최신이어야 하는지, 결과가 맞는지 어떻게 알 수 있는지 아무것도 말해 주지 않거든요. 숙련된 개발자는 수식을 하나 쓰기 전에 이 질문부터 정리해요. 그 답이 이후에 만들 모든 것의 구조를 정하기 때문이에요. 이 튜토리얼에서는 그 한 문장을, 바로 만들 수 있고 결과를 검증할 수도 있는 설계서로 바꾸는 과정을 따라가 봐요.

대시보드가 아니라 의사 결정에서 시작하기

쓸모 있는 통합 문서는 모두 이름이 있는 한 사람의 질문 하나에 답해요. 엑셀을 열기 전에 요청한 사람에게 이렇게 물어보세요.

• 누가 쓰나요? "팀 전체"가 아니라 사람이나 직책으로 답을 받으세요.
• 그걸로 무엇을 결정하나요? 숫자를 보고 달라지는 게 없다면 그건 솔루션이 아니라 데이터 더미예요.
• 언제 결정하나요? 주간 회의, 월말 검토, 이사회 보고 자료 같은 시점이에요.
• 숫자가 이상해 보이면 어떻게 할 건가요? 이 답을 들으면 어떤 숫자를 가장 꼼꼼히 검사해야 하는지 알 수 있어요.

매출 요청이라면 대화가 이렇게 정리될 수 있어요. 지역 담당 부사장이 매달 각 지역이 월 목표를 달성할 속도로 가고 있는지 판단하고, 뒤처진 지역에 지원을 돌린다. 이 한 문장으로 많은 것이 걸러져요. 제품 단위의 상세 데이터도, 실시간 데이터도, 색 구성을 두고 벌이는 토론도 필요 없어요. 대신 목표치, "목표 속도"의 명확한 정의, 그리고 장부와 일치하는 지역별 합계는 꼭 필요해요.

입력, 출력, 새로 고침 주기 정하기

다음으로 무엇이 들어가고, 무엇이 나오고, 얼마나 자주 최신 상태여야 하는지 적어 두세요.

• 입력: 모든 원본과 그걸 누가 어떤 형태로 주는지 적어요. "재무팀이 ERP에서 CSV로 내보내는 월간 매출 파일", "영업 지원팀이 1년에 한 번 입력하는 지역별 목표"처럼요.
• 출력: 읽는 사람이 실제로 보는 것이에요. "한 페이지: 지역별 누적 매출, 목표, 목표 대비 진도율, 목표 속도 표시, 그리고 추세 차트."
• 새로 고침 주기: 숫자를 얼마나 자주 업데이트해야 하고, 무엇이 업데이트를 시작하게 하는지예요.

데이터보다 주기가 구조를 더 크게 좌우해요. 같은 매출 데이터라도 새로 고침 주기에 따라 전혀 다른 통합 문서가 필요해요.

지역 담당 부사장에게는 매달, 장부 마감 후가 맞아요. 그 전에는 매출 숫자가 확정되지 않으니, 매일 새로 고치면 나중에 바뀔 숫자를 보여 주게 돼요.

통합 문서 지도 그리기

통합 문서 지도는 데이터가 거쳐 가는 층을 순서대로 적고, 층마다 무엇을 담는지 정리한 목록이에요. 만들기 전에 종이에 먼저 그려 보세요.

데이터는 Source에서 Report까지 한 방향으로만 흐르고, QA는 양쪽 끝을 모두 읽어요. 작은 통합 문서라면 Staging과 Model을 파워 쿼리 단계 목록 하나에 합치는 식으로 층을 묶을 수 있지만, 순서는 절대 바뀌지 않아요. Report의 숫자가 틀렸을 때 다음에 어디를 봐야 하는지 이 지도가 알려 줘요.

모든 숫자를 추적할 수 있게 만들기

지도가 있으면 계보(lineage)도 생겨요. Report의 어떤 숫자든 가리키면 그 숫자를 만든 원본 행까지 거슬러 올라갈 수 있는 능력이에요. 계보가 없으면 숫자가 맞기를 바랄 수밖에 없지만, 계보가 있으면 숫자를 설명하고 지킬 수 있어요. 몇 가지 습관이 계보를 가능하게 해요.

• 모든 행에 주문 번호나 송장 ID 같은 식별자를 끝까지 남겨 두세요. 그래야 합계를 다시 행 단위로 쪼개 볼 수 있어요.
• 파일을 결합할 때는 파일 이름을 열로 남겨 두세요. 그러면 행마다 어느 내보내기 파일에서 왔는지 알 수 있어요.
• 워크시트 수식이 어떤 입력에서 왔는지는 수식› then 수식 분석› then 참조되는 셀 추적으로 따라가고, 어떤 쿼리가 어떤 쿼리에 데이터를 공급하는지는 파워 쿼리 편집기의 쿼리 종속성(Query Dependencies) 보기(보기› then 종속성› then 쿼리 종속성)에서 확인하세요.

가정 목록 관리하기

모델이 기대는 비율, 기준값, 판단은 모두 가정 목록(assumptions register)에 한 줄씩 올려요. 눈에 보이는 셀 하나, 이름, 한 줄짜리 이유, 담당자가 한 세트예요. 다음에 수식을 고칠 사람이 찾지 못할 곳, 즉 수식 안에 값을 직접 입력하는 일은 없게 하세요.

fx

	A	B	C	D	E
1	가정	값	이름	이유	담당
2	목표 속도 기준	95%	PaceThreshold	일할 목표의 95% 이상이면 목표 속도로 봐요	영업 지원팀
3	마감일	5	CloseDay	장부는 다섯째 영업일에 마감해요	재무팀
4	대사 허용 오차	1	TieTolerance	반올림으로 합계가 최대 1까지 달라질 수 있어요	보고서 담당자

값이 들어 있는 셀마다 이름을 붙이고(셀을 클릭하고 이름 상자(Name Box)에 이름을 입력한 뒤 Enter를 누르세요) 수식에서는 그 이름을 쓰세요. 어떤 지역의 누적 매출이 C2에, 일할 계산한 목표가 D2에 있다면 목표 속도 표시는 이렇게 써요.

=IF(C2/D2>=PaceThreshold,"순항","미달")복사

일할 목표 50,000에 대해 매출이 47,500이면 정확히 95%이므로 결과는 순항이에요. 나중에 영업 지원팀이 90%면 충분하다고 정하면 셀 하나만 바꾸면 되고, 모든 표시가 함께 업데이트돼요. 그 결정을 누가 내릴 수 있는지는 담당 열에 적혀 있어요.

유지 관리 담당자 정하기

담당자가 없는 통합 문서는 여섯 달만 지나도 아무도 믿지 않아요. 유지 관리 담당자는 이름이 정해진 한 사람이고, 이런 일을 맡아요.

• 정해진 주기에 새로 고침을 실행하고, 공유하기 전에 QA 시트를 확인해요.
• 원본의 구조가 바뀌면 통합 문서를 고쳐요.
• 가정 목록과 변경 기록을 최신 상태로 유지해요.
• 자리를 비울 때 누가 대신하는지 알고 있고, 떠날 때는 제대로 인수인계해요.

Notes 시트 맨 위에 담당자와 대리 담당자를 적어 두세요. 아무도 맡으려 하지 않는다면, 이 형태로는 통합 문서를 만들지 말아야 한다는 신호로 받아들이세요.

테스트할 수 있는 인수 기준 쓰기

인수 기준(acceptance criteria)은 "완성"이 무슨 뜻인지 정해 줘요. 만들기 전에 쓰고, 요청한 사람과 합의하고, 완성된 통합 문서를 그 기준으로 테스트하세요. 기준마다 다른 사람이 확인해서 예 또는 아니요로 답할 수 있는 문장이어야 해요.

왼쪽 기준은 그대로 QA 검사가 돼요. QA 시트는 원본의 수치와 보고서의 같은 수치를 비교하고, 정해 둔 허용 오차를 적용해 통과 또는 실패를 돌려줘요.

fx

	A	B	C	D	E
1	검사 항목	원본	보고서	허용 오차	결과
2	매출 합계	486,250	486,250.4	1	통과
3	주문 행 수	1,248	1,247	0	실패
4	지역 수	4	4	0	통과
5
6	배포해도 되나요?	FALSE

결과마다 ABS를 써서 어느 쪽이 더 큰지와 상관없이 비교해요.

=IF(ABS(B2-C2)<=D2,"통과","실패")복사

매출 수치는 0.4 차이로 허용 오차 1 안에 있으니 E2에는 통과가 나와요. 행 수는 1 차이인데 허용 오차가 0이니 E3에는 실패가 나와요. 그런 다음 요약 셀 하나가 통합 문서를 내보내도 되는지 담당자에게 알려 줘요.

=COUNTIF(E2:E4,"실패")=0복사

한데 모으기: 한 페이지 설계서

설계서(design brief)는 지금까지 정리한 내용을 한 페이지에 모은 거예요. 매출 요청이라면 이렇게 될 수 있어요.

무엇이든 만들기 전에 이 설계서를 요청한 사람에게 보여 주세요. 의견 차이는 한 페이지에서 고치면 싸지만, 완성된 통합 문서에서 고치면 비싸요.

자주 하는 실수

• 묻기 전에 만들기. 첫 버전이 엉뚱한 질문에 답하게 되고, 다시 만드는 비용이 대화 한 번보다 훨씬 커요.
• 결정권자가 없음. "모두를 위한" 보고서는 아무도 확인하지 않아요.
• 데이터가 따라올 수 없는 주기 고르기. 매달 확정되는 숫자로 매일 보고하면 나중에 바뀔 숫자를 보여 주게 돼요.
• 수식 안에 숨은 가정. =IF(C2/D2>=0.95,…)는 업무 규칙을 숨겨요. 95%는 가정 목록에 넣으세요.
• 만든 뒤에 쓴 인수 기준. 필요했던 것이 아니라 만들어진 것을 설명하게 돼요.
• 테스트할 수 없는 기준. "보기 좋다"는 실패할 수 없으니, 통과할 수도 없어요.

연습

연습

실제 설계서 써 보기

1. 실제로 받아 본 막연한 요청을 하나 고르세요. "이것 좀 정리해 줄래요?"나 "이걸 추적할 수 있을까요?" 같은 요청이요.
2. 그 요청이 돕는 의사 결정과, 그 결정을 내리는 사람을 적으세요.
3. 입력과 출력을 나열하고 새로 고침 주기를 정하세요. 그 주기가 구조에 어떤 영향을 주는지도 적으세요.
4. Source부터 Notes까지 통합 문서 지도를 그려 보세요.
5. 가정 목록에 가정 하나를 이름, 이유, 담당자와 함께 추가하세요.
6. 유지 관리 담당자와 대리 담당자를 정하세요.
7. 다른 사람이 테스트할 수 있는 인수 기준을 세 개 쓰고, 그중 하나를 QA 검사로 바꾸세요.
8. 만들기 전에 요청한 사람에게 설계서를 보여 주세요.

아래에서 QA 검사를 직접 해 보세요. 원본과 보고서의 수치를 허용 오차로 비교한 다음, 통합 문서를 배포해도 되는지 판단해요.

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

1. 1.E2에 원본과 보고서의 차이가 D2의 허용 오차 이하이면 통과, 아니면 실패를 표시해 보세요.힌트 보기
2. 2.E3에 행 수로 같은 검사를 해 보세요. 보고서에서 주문 한 건이 빠졌어요.힌트 보기
3. 3.E4에 보고서의 지역 수가 원본과 같은지 검사해 보세요.힌트 보기
4. 4.B6에 E2:E4의 검사가 하나도 실패하지 않았을 때만 TRUE가 나오게 해 보세요.힌트 보기

E2fx수식 입력 셀 E2Enter ↵

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

	A	B	C	D	E
1	검사 항목	원본	보고서	허용 오차	결과
2	매출 합계	486,250	486,250.4	1
3	주문 행 수	1,248	1,247	0
4	지역 수	4	4	0
5
6	배포해도 되나요?

실력 점검

• 엑셀을 열기 전에 통합 문서가 돕는 의사 결정과 그 결정을 내리는 사람을 말할 수 있나요?
• Source, Staging, Model, Report, QA, Notes 층으로 통합 문서 지도를 그릴 수 있나요?
• 값, 이름, 이유, 담당자를 갖춘 가정 목록 항목을 쓸 수 있나요?
• 유지 관리 담당자가 맡는 일을 설명할 수 있나요?
• "전문적으로 보인다"를 다른 사람이 테스트할 수 있는 인수 기준으로 바꿔 쓸 수 있나요?