엑셀 주간보고서 자동화: 완료율·지연·시간을 자동 집계하는 방법

주간보고서를 Excel로 만들고 있는데도 매주 완료 건수를 다시 세고 있다면, 아직 문서만 Excel일 뿐 계산은 수작업인 상태입니다.

주간보고 자동화의 출발점은 화려한 대시보드가 아닙니다. 업무를 한 줄에 하나씩 입력하고, 완료율·지연·시간·입력 오류가 같은 기준으로 계산되게 만드는 것입니다.

이 글에서는 매크로나 별도 프로그램 없이도 이해할 수 있도록 다음 구조를 설명합니다.

• 어떤 항목을 입력해야 하는가
• 완료율을 어떤 기준으로 계산해야 하는가
• 지연 업무를 잘못 판정하지 않으려면 무엇이 필요한가
• 계획시간과 실제시간을 어떻게 비교하는가
• 잘못된 입력을 보고 전에 어떻게 발견하는가

1. 주간보고 입력표는 10개 항목이면 시작할 수 있습니다

처음부터 모든 정보를 넣으려고 하면 입력표가 복잡해져 사용하지 않게 됩니다. 아래 10개 항목으로 시작해도 완료율·지연·시간 집계에 필요한 기본 구조를 만들 수 있습니다.

핵심은 업무명을 길게 잘 쓰는 것이 아니라, 계산에 쓰는 항목과 설명에 쓰는 항목을 구분하는 것입니다.

• 계산 항목: ID, 상태, 기한, 계획시간, 실제시간
• 설명 항목: 업무명, 결과·진척, 이슈·다음 행동

이렇게 나누면 숫자 집계는 Excel이 맡고, 사람은 결과와 판단에 집중할 수 있습니다.

2. 상태값은 자유롭게 쓰지 말고 세 가지로 고정합니다

같은 상태를 진행, 진행 중, 진행중, 수행 중으로 제각각 입력하면 Excel은 서로 다른 값으로 인식할 수 있습니다. 상태값은 다음 세 가지처럼 고정하는 것이 좋습니다.

• 완료
• 진행
• 미착수

Excel의 데이터 유효성 검사에서는 셀에 입력할 값의 종류를 제한하거나 드롭다운 목록을 만들 수 있습니다. Microsoft도 목록·날짜·숫자 등의 입력 제한과 오류 메시지 설정 방법을 안내합니다.

상태를 드롭다운으로 만들면 오타를 줄일 수 있지만, 그것만으로 충분하지는 않습니다. 다른 파일에서 붙여넣은 값이나 수식 변경으로 잘못된 상태가 들어올 수 있으므로 집계표에 잘못된 상태 건수도 별도로 표시해야 합니다.

3. 업무 건수는 ID가 있는 행만 계산합니다

주간보고 집계에서 자주 생기는 오류는 업무명이 있거나 상태가 입력됐다는 이유만으로 한 건으로 세는 것입니다.

예를 들어 ID 없이 상태만 완료로 입력된 행이 있다면 완료 건수에 포함하면 안 됩니다. 전체 건수와 상태별 건수의 기준이 달라지기 때문입니다.

집계 기준은 다음처럼 통일합니다.

전체 업무 = ID가 입력된 행
완료 업무 = ID가 있고 상태가 완료인 행
진행 업무 = ID가 있고 상태가 진행인 행
미착수 업무 = ID가 있고 상태가 미착수인 행

완료율도 같은 기준을 사용합니다.

완료율 = 완료 업무 수 ÷ 전체 업무 수

전체 업무가 0건이라면 0%라고 표시하기보다 해당 없음으로 표시하는 편이 정확합니다. 입력된 업무가 없다는 뜻과 업무가 있지만 완료된 것이 없다는 뜻은 다르기 때문입니다.

4. 지연 여부에는 반드시 기준일이 필요합니다

기한만 보고 지연을 계산하면 파일을 언제 열었는지에 따라 결과가 달라질 수 있습니다. 보고서가 어느 시점을 기준으로 작성됐는지 명확히 하려면 별도의 기준일이 필요합니다.

지연 조건은 다음 세 가지를 모두 만족할 때만 참입니다.

1. ID가 있다
2. 기한이 기준일보다 이전이다
3. 상태가 완료가 아니다

여기서 중요한 예외가 있습니다.

• 기준일이 비어 있음
• 기준일 칸에 문자가 들어감
• 허용 범위를 벗어난 날짜가 들어감
• 기한이 날짜가 아닌 텍스트임

이 경우 지연 건수를 억지로 계산하면 안 됩니다. 집계표에 기준일 오류 또는 판단 불가를 표시하고 먼저 입력을 고치게 해야 합니다.

오늘 날짜를 자동으로 넣는 방식도 가능하지만, 과거 보고서를 다시 열었을 때 지연 건수가 달라질 수 있습니다. 확정 보고서는 작성 당시의 기준일을 값으로 남기는 편이 추적에 유리합니다.

5. 계획시간과 실제시간은 합계보다 입력 규칙이 먼저입니다

계획시간과 실제시간을 입력하면 다음 수치를 볼 수 있습니다.

• 계획시간 합계
• 실제시간 합계
• 계획 대비 실제시간 차이
• 실제시간 미입력 업무 수
• 실제시간 0시간으로 입력된 업무 수

여기서 빈칸과 0을 구분해야 합니다.

• 빈칸: 아직 실제시간을 기록하지 않음
• 0: 실제 투입시간이 없었다고 명시함

두 값을 같은 것으로 처리하면 미입력 업무를 찾을 수 없습니다. 반대로 진행 중인 업무의 실제시간이 비어 있다고 무조건 오류로 잡는 것도 현실과 맞지 않을 수 있습니다. 조직의 보고 규칙에 따라 완료 업무인데 실제시간이 빈칸인 경우만 확인 대상으로 삼는 방식이 실용적입니다.

6. 집계보다 먼저 보여야 할 것은 오류 배너입니다

완료율이 75%라고 크게 표시돼 있어도 입력 데이터가 틀렸다면 그 숫자는 의미가 없습니다. 집계표 맨 위에는 결과보다 먼저 데이터 상태를 보여주는 것이 좋습니다.

예를 들면 다음과 같습니다.

오류가 있으면 다음처럼 바꿉니다.

최소한 다음 오류는 확인해야 합니다.

Excel의 데이터 유효성 검사와 조건부 계산을 함께 사용하면 입력 단계의 오류와 집계 단계의 오류를 나누어 확인할 수 있습니다. ID 중복처럼 반복값을 찾는 데는 COUNTIF 계열의 조건 계산을 활용할 수 있습니다.

7. 실제 사용 흐름은 다섯 단계로 단순해야 합니다

양식을 잘 만들어도 사용 순서가 복잡하면 매주 유지되지 않습니다.

1단계 · 원본을 복사합니다

수식과 서식을 보존하기 위해 원본 파일은 따로 두고, 해당 주차의 복사본에서 작업합니다.

↓

2단계 · 기준일을 입력합니다

보고서를 작성하는 날짜 또는 조직에서 정한 마감 기준일을 입력합니다.

↓

3단계 · 업무를 한 행씩 입력합니다

한 셀에 여러 업무를 몰아 쓰지 않습니다. 업무가 세 개라면 세 행으로 나눕니다.

↓

4단계 · 오류 배너부터 확인합니다

완료율이나 지연 건수를 보기 전에 이상 없음인지 확인합니다. 오류가 있으면 해당 행부터 수정합니다.

↓

5단계 · 집계 결과를 보고서 문장으로 바꿉니다

확인된 숫자와 이슈만 Word 보고서에 옮깁니다. AI를 사용한다면 이 단계에서만 초안 작성을 맡깁니다.

8. AI에는 원본 전체보다 검증된 요약을 줍니다

AI에게 Excel 파일 전체를 바로 맡기면 입력 오류까지 그럴듯한 문장으로 포장할 수 있습니다. 먼저 Excel에서 계산과 오류 검사를 끝낸 뒤 아래처럼 검증된 요약만 전달하는 편이 안전합니다.

[기준 정보]
- 보고 기간:
- 기준일:
[확인된 집계]
- 전체 업무:
- 완료:
- 진행:
- 미착수:
- 지연:
- 계획시간:
- 실제시간:
[핵심 결과]
-
[이슈와 다음 행동]
-
[작성 규칙]
1. 입력에 없는 사실과 숫자는 만들지 마라.
2. 확인되지 않은 내용은 '확인 필요'로 표시하라.
3. 완료 업무, 지연 업무, 다음 행동 순으로 작성하라.
4. 개인정보와 회사 기밀은 포함하지 마라.

거래처명, 담당자명, 금액, 미공개 일정처럼 민감한 정보는 외부 AI에 입력하기 전에 일반화하거나 제거해야 합니다. 회사에서 허용한 계정과 도구인지 확인하는 것도 먼저입니다.

주간보고 전체 과정에서 AI·규칙 기반 자동화·사람이 맡을 구간을 나누는 방법은 매주 반복되는 주간보고, AI로 어디까지 줄일 수 있을까에서 확인할 수 있습니다.

9. 자주 실패하는 설계 다섯 가지

업무 수와 완료 수의 기준이 다릅니다

전체 업무는 업무명으로 세고 완료 업무는 상태로 세면 완료율의 분모와 분자가 서로 다른 기준이 됩니다. 모든 상태 집계는 ID가 있는 행을 기준으로 통일합니다.

지연 업무에 완료 건이 섞입니다

기한이 지났더라도 상태가 완료라면 현재 지연 목록에서는 제외해야 합니다. 완료일 지연을 별도로 관리하려면 계획 준수율이라는 다른 지표로 분리합니다.

날짜처럼 보이는 문자를 날짜로 계산합니다

화면에는 날짜처럼 보여도 실제로는 텍스트일 수 있습니다. 날짜 형식 오류를 별도 항목으로 확인합니다.

30건까지만 계산하면서 31번째 입력을 허용합니다

집계 범위보다 아래에 입력한 업무가 조용히 빠지면 사용자는 오류를 알아채기 어렵습니다. 입력 한도를 명시하고, 초과 행에 값이 있으면 경고해야 합니다.

완료율 숫자만 보고 원본을 확인하지 않습니다

자동 집계는 사람의 검토를 없애는 기능이 아니라, 검토할 곳을 좁혀주는 기능입니다. 오류 배너와 지연 목록을 함께 확인해야 합니다.

10. 직접 만들기 전 확인할 체크리스트

• [ ] 한 행에 업무 한 건만 입력하는가
• [ ] 모든 업무에 고유 ID가 있는가
• [ ] 상태값이 완료·진행·미착수로 고정됐는가
• [ ] 전체·상태별 건수가 모두 ID 기준인가
• [ ] 기준일이 없거나 잘못되면 지연을 계산하지 않는가
• [ ] 계획시간·실제시간에 문자 입력을 확인하는가
• [ ] ID 중복과 필수 항목 누락을 표시하는가
• [ ] 입력 한도를 넘으면 경고하는가
• [ ] 집계표 상단에 오류 상태가 먼저 보이는가
• [ ] AI 초안에는 검증된 수치만 전달하는가

이 열 가지가 맞으면 주간보고 Excel은 단순한 기록표에서 반복 사용 가능한 업무 도구로 바뀝니다.

11. 직접 만드는 대신 바로 사용하려면

자주 묻는 질문

매크로나 VBA가 꼭 필요한가요?

아닙니다. 상태별 건수, 완료율, 지연 조건, 시간 합계와 기본 오류 검사는 일반적인 Excel 기능과 수식으로도 구성할 수 있습니다. 데이터 양이 많거나 여러 파일을 자동 취합해야 할 때 매크로나 별도 자동화를 검토하면 됩니다.

Google Sheets에서도 같은 방식으로 만들 수 있나요?

기본적인 입력·조건 집계 구조는 만들 수 있지만, Excel 파일의 수식·드롭다운·서식이 그대로 유지된다고 보장할 수는 없습니다. 사용하는 프로그램에서 반드시 다시 검증해야 합니다.

실제시간이 비어 있으면 오류인가요?

항상 오류는 아닙니다. 아직 진행 중이어서 기록하지 않았을 수 있습니다. 다만 완료된 업무인데 실제시간이 비어 있다면 확인 대상으로 표시할 수 있습니다.

기준일은 오늘 날짜를 자동으로 쓰면 안 되나요?

실시간 현황판이라면 가능하지만 확정된 과거 보고서를 다시 열 때 결과가 달라질 수 있습니다. 주간보고 보관 목적이라면 작성 당시 기준일을 값으로 남기는 방법이 안전합니다.

업무가 30건을 넘으면 어떻게 하나요?

양식의 집계 범위를 확장하거나 팀·기간별 파일로 나누어야 합니다. 중요한 것은 한도를 넘은 업무가 경고 없이 집계에서 빠지지 않게 하는 것입니다.

참고 자료

• Microsoft Support, Apply data validation to cells
• Microsoft Support, Use the COUNTIF function in Microsoft Excel
• Microsoft Support, IF function
• 일잘랩, 매주 반복되는 주간보고, AI로 어디까지 줄일 수 있을까