엑셀 업무용 템플릿 만들기: 입력·계산·보고서를 분리해 재사용하기
업무용 엑셀 템플릿은 서식이 예쁜 빈 파일이 아니라, 어디에 입력하고 어떤 값으로 완료를 판단하는지 정해 둔 파일입니다. 이 실습은 INPUT의 표와 CALC의 SUMIFS, OUT의 월별 보고서를 연결합니다. 가상 입력 네 행의 전체 금액은 3,400이고, 선택 월 2026-09의 금액은 2,500입니다. 다른 월이나 행을 추가했을 때 무엇이 바뀌어야 하는지도 함께 확인합니다.
파일에 들어 있는 것과 없는 것
첨부 XLSX에는 INPUT, CALC, OUT, README 네 시트와 이름이 tblInput인 Excel 표가 들어 있습니다. INPUT의 A:D는 입력, E는 입력 판정 수식입니다. CALC는 구조화 참조를 사용해 집계하고 OUT은 선택 월의 결과를 보여 줍니다.
Windows용 Excel 데스크톱 앱을 기준으로 설명합니다. 시트 보호·암호·매크로·피벗 테이블·외부 연결은 넣지 않았습니다. 특히 '파일을 열면 이미 보호되어 있다'거나 '피벗이 입력 즉시 새로 고쳐진다'고 가정하지 마세요. 이 파일은 수식 집계 방식의 템플릿 시작본입니다. 실제 Excel에서 실행한 화면이나 운영 성능을 측정한 증빙도 아닙니다.
검토용 원본은 보존하고 사본에서 연습합니다. 실제 업무 데이터를 넣기 전에 README의 파일 버전과 기본 입력을 확인하세요. 개인정보나 회사 매출이 아닌 설명용 가상 값으로 구성했습니다.
입력: INPUT!A1:E5
| 행 | ID | Month | Category | Amount |
|---|---|---|---|---|
| 2 | R01 | 2026-09 | A | 1200 |
| 3 | R02 | 2026-09 | B | 800 |
| 4 | R03 | 2026-09 | A | 500 |
| 5 | R04 | 2026-10 | A | 900 |
1행의 머리글은 ID / Month / Category / Amount / Check입니다. 머리글은 비우지 않습니다. Month는 실제 날짜 일련번호가 아니라 YYYY-MM 모양의 텍스트 키입니다. Amount는 숫자이고 Category는 이 예제에서 A 또는 B만 허용합니다. 음수의 업무상 허용 여부 등 별도 규칙은 추가하지 않았습니다.
E2:E5는 사용자가 덮어쓰는 입력 칸이 아닙니다. 각 행의 필수 항목, 숫자형 금액, 중복 ID, 허용 구분을 확인합니다. 기본 입력에서는 네 행이 모두 정상입니다.
=IF(OR([@ID]="",[@Month]="",[@Category]=""),"입력 확인",IF(NOT(ISNUMBER([@Amount])),"금액 확인",IF(COUNTIF(tblInput[ID],[@ID])<>1,"ID 확인",IF(AND([@Category]<>"A",[@Category]<>"B"),"구분 확인","정상"))))이 수식은 Month가 비었는지는 보지만 2026-13 같은 달력상 잘못된 월을 모두 판정하지는 않습니다. 표기 규칙과 실제 보고 기간은 사람이 확인합니다. 값이 잘못됐는데도 우연히 합계가 맞는 상황을 검산 통과로 승인하지 마세요.
실행 순서와 시트별 역할
-
첨부 파일의 사본을 열고 INPUT의 네 행과 E열 수식을 확인합니다.
-
OUT!B2가 텍스트
2026-09인지 확인합니다. -
CALC!B4가 0인지, B12가
검산 통과인지 확인합니다. -
OUT!B6은 3, B7은 2,500, B9는 1,700, B10은 800인지 비교합니다.
-
CALC!D1:G10의 기본 입력 검산표에서 G2:G10이 모두
일치인지 확인합니다. -
아래의 월 변경과 행 추가를 한 번에 하나씩 연습하고, 기본 입력으로 복원합니다.
| 위치 | 역할 | 기본 입력에서 볼 값 |
|---|---|---|
| INPUT!E2:E5 | 행별 입력 판정 | 정상 네 개 |
| CALC!B2:B4 | 전체·정상·확인 행 수 | 4, 4, 0 |
| CALC!B6 | 모든 월의 금액 합계 | 3400 |
| CALC!B7:B10 | 선택 월 행 수·합계·A·B | 3, 2500, 1700, 800 |
| CALC!B11 | 월 합계와 A+B 차이 | 0 |
| OUT!B4 | 보고 사용 전 상태 | 검산 통과 |
수식이 새 행을 따라가게 하는 구조
구조화 참조는 표에 행을 추가하거나 제거하면 참조 범위를 조정합니다. Microsoft 구조화 참조 안내를 기준으로, D2:D5 같은 고정 주소 대신 tblInput[Amount]를 사용했습니다.
CALC!B8의 월별 합계와 B9의 A 구분 합계는 다음과 같습니다.
=SUMIFS(tblInput[Amount],tblInput[Month],OUT!$B$2)
=SUMIFS(tblInput[Amount],tblInput[Month],OUT!$B$2,tblInput[Category],"A")합계 수식만 자동으로 늘어난다고 입력 판정까지 항상 정상인 것은 아닙니다. 새 행이 표 내부에 포함됐는지, E열의 Check 수식이 채워졌는지 따로 확인합니다. 일반 셀에만 붙여 넣은 행은 구조화 참조에 포함되지 않을 수 있습니다.
보고 상태는 모든 입력 행이 정상이고 선택 월에 자료가 있으며 월 합계와 구분 합계가 맞을 때 검산 통과가 됩니다. 상태가 다르면 OUT의 금액 칸은 보류로 표시합니다. CALC의 원시 합계는 문제를 조사할 수 있도록 남겨 둡니다. 이것은 정해 둔 입력 규칙의 통과이지 실제 장부와의 대사를 대신하는 승인 표시는 아닙니다.
월 변경과 행 추가로 검산하기
월 변경: OUT!B2만 2026-10으로 바꿉니다. 선택 월 행 수는 1, 합계는 900, A는 900, B는 0이어야 합니다. INPUT은 바꾸지 않습니다. 확인 후 B2를 2026-09로 복원합니다.
CALC의 E열 고정 기대값은 기본 입력용이므로 월을 바꾸는 동안 일부 비교가 확인으로 바뀌는 것은 정상입니다. 다른 월의 숫자를 맞추려고 고정 기대값부터 덮어쓰지 마세요.
행 추가: 기본 월로 복원한 뒤 INPUT의 표 마지막 셀 E5에서 Tab을 눌러 표에 새 행을 만들고 A6:D6에 R05 / 2026-09 / B / 300을 입력합니다. E6에도 기존 Check 수식이 이어지는지 확인합니다. 자동 확장이 되지 않으면 표 디자인의 표 크기 조정에서 A1:E6을 지정하고 E열 수식을 채웁니다.
이때 전체 금액은 3,700, 선택 월 합계는 2,800, A는 1,700, B는 1,100이어야 합니다. 시험 후에는 추가한 R05의 표 행만 삭제해 네 입력 행으로 돌아갑니다. 워크시트 전체 행이나 머리글을 지우지 않습니다.
다음 달에도 같은 파일 구조를 쓰는 방법
기본 파일을 월별 사본으로 복제하고 INPUT의 A:D만 바꿉니다. 새 자료가 더 적다면 남은 이전 달 행을 표에서 삭제하고, 더 많다면 표 범위를 확장합니다. Check와 CALC·OUT의 수식은 값 붙여넣기로 덮어쓰지 않습니다. 빈 데이터 상태를 완성 보고서처럼 사용하지 말고, 적어도 입력 행을 유지한 채 새 자료를 넣고 판정을 확인합니다.
정식 템플릿으로 관리하려면 검토한 파일을 Excel의 파일 내보내기에서 '템플릿' 형식으로 저장하고, 새로 만들기의 개인 템플릿에서 새 통합 문서를 만듭니다. 메뉴와 개인 템플릿 경로는 환경에 따라 다릅니다. Microsoft 템플릿 저장 안내에 따른 별도 단계이며, 다운로드 파일 자체는 XLSX입니다.
보호가 필요하면 먼저 사본에서 CALC와 OUT의 수정 가능 범위를 정합니다. OUT에서는 월 선택 셀 B2를 편집 가능하게 두어야 합니다. 잠금 속성과 시트 보호를 함께 설정한 뒤 월 변경을 시험합니다. 워크시트 보호는 파일 암호화나 보안 기능을 대신하지 않습니다. Microsoft 워크시트 보호 안내
실패했을 때 복구하는 순서
| 증상 | 확인할 대상 | 복구 |
|---|---|---|
| 입력 확인 | INPUT!E열의 해당 행 | 공란·중복 ID·금액 유형·A/B 구분을 수정 |
| 자료 없음 | OUT!B2와 Month의 문자 | 앞뒤 공백과 월 표기를 통일 |
| 새 행이 합계에서 빠짐 | tblInput 범위와 E열 수식 | 표 범위를 다시 지정하고 수식 복원 |
| 값이 갱신되지 않음 | Excel 계산 설정과 수식 존재 | 계산을 실행하고 손상된 수식을 원본에서 복구 |
| #REF!·수식 오류 | 삭제된 열·바뀐 머리글 | 임의로 0 처리하지 말고 원본 구조와 비교 |
| 시트 보호 뒤 월 선택 불가 | OUT!B2 잠금 설정 | 권한 있는 사본에서 보호 설정 수정 |
예를 들어 Amount에 숫자 800 대신 문자 '800을 입력하면 금액 확인이 나와야 합니다. 해당 입력만 숫자 800으로 다시 입력하고 E열과 OUT 상태를 재검산합니다. Category에 C를 넣었다면 이를 무작정 A로 바꾸지 말고, 원래 A/B 예제인지 아니면 새 구분을 추가하는 설계 변경인지 먼저 판단합니다.
한계와 배포 전 확인
이 시작본은 A/B 두 구분과 한 월의 보고서에 맞춰져 있습니다. 구분을 늘릴 때는 입력 판정, CALC의 구분별 합계, OUT을 함께 수정해야 합니다. Month의 달력 유효성, 실제 금액의 정확성, 통화 혼합, 수식 삭제·변조까지 자동 확인하는 파일은 아닙니다.
공유 전에 샘플과 실제 자료를 섞지 않았는지 확인하고, 원본·월별 사본·보고용 배포본을 구분합니다. 성능이나 오류 감소를 측정한 파일이 아니므로 처리 가능한 행 수와 실행 시간을 보장하지 않습니다. 시트가 네 개 있다는 사실만으로 업무용 템플릿 검증이 끝나는 것도 아닙니다.
이어서 읽기
보고서를 피벗으로 다시 구성할 때는 원본을 보존하는 VBA 피벗 생성을, 여러 파일의 입력을 모을 때는 pandas로 Excel 파일 합치기를 이어서 봅니다. 입력을 합치는 작업과 합계가 맞는지 확인하는 작업은 분리해 두세요.
공식 문서 확인일: 2026-09-12. 이미지 세 장은 설명용 일러스트이며 실제 Excel 실행 화면이 아닙니다.
작성자 소개
