마지막 편입니다. 1편에서 날짜 자동화를, 2편에서 시각화를 마쳤으니, 이번에는 완료율 자동 계산을 구현하고 템플릿을 공유해보려 합니다.
← 1편 : 스프레드시트 태스크 트래커 만들기(1)- 만년 달력 자동화
← 2편 :스프레드시트 태스크 트래커 만들기(2) - 조건부 서식으로 시각화
1. 완료율 수식의 핵심
단순히 체크 개수만 세면 될 것 같지만, 분모를 어떻게 잡느냐가 중요합니다.
* 주말 포함이면 해당 월의 전체 일수가 분모 (4월 = 30)
* 주말 제외이면 평일 수만 분모 (4월 평일 = 22)
* 시작일 기준으로 앞에 끼어든 전월 날짜는 분모에서 빠져야 함
그리고 업무 유형에 따라 완료율 표시 여부도 구분합니다.
| 유형 | 완료율 | 이유 |
| Daily | 표시 | 매일 반복하므로 달성률 추적이 의미 있음 |
| Weekly / Monthly | 빈칸 | 주 1회, 월 1회 업무의 일별 완료율은 의미 없음 |
2. 체크 기호 규칙
어떤 값을 "완료"로 인정할지 기준을 먼저 정합니다.
| 기호 | 의미 |
| ✅ | 완료 |
| ⌛ | 진행 중 |
| ❌ | 미진행 |
오직 ✅만 완료로 카운트합니다.
이 기호들은 데이터 유효성 검사(드롭다운)로 설정해 두면 오타 없이 빠르게 입력할 수 있으며, 기호는 원하는대로 추가/변경/삭제 가능합니다.
3. 분모 — 해당 월의 실제 일수
주말 포함/제외에 따라 분모가 달라지므로 IF 함수를 씁니다.
주말 포함 — 전체 일수:
DAY(EOMONTH(DATE($B$2,$D$2,1), 0))
EOMONTH로 월말 날짜를 구하고 DAY로 일(day)만 추출합니다.
ex) 4월이면 30, 2월이면 28(윤년 29).
주말 제외 — 평일 수:
NETWORKDAYS(DATE($B$2,$D$2,1), EOMONTH(DATE($B$2,$D$2,1), 0))
NETWORKDAYS는 두 날짜 사이의 평일 수를 반환합니다.
합친 수식:
=IF(
$D$3 = "포함",
DAY(EOMONTH(DATE($B$2,$D$2,1), 0)),
NETWORKDAYS(DATE($B$2,$D$2,1), EOMONTH(DATE($B$2,$D$2,1), 0))
)
4. 분자 — SUMPRODUCT로 본월 ✅만 세기
단순 COUNTIF로 세면 전월 날짜 열에 찍힌 값까지 포함되는 문제가 있습니다.
SUMPRODUCT를 써서 "본월 날짜인 열의 ✅만" 필터링합니다.
=SUMPRODUCT(
(MONTH($E$3:$AL$3) = VALUE($D$2))
* ($E5:$AL5 = "✅")
)
동작 원리는 다음과 같습니다.
* MONTH($E$3:$AL$3) = VALUE($D$2) → 날짜 행 각 셀이 현재 설정 월과 같은지 TRUE/FALSE 배열 반환
* $E5:$AL5 = "✅" → 해당 업무 행에서 ✅인 칸만 TRUE
* 두 배열을 곱하면 둘 다 TRUE인 칸만 1 → 합산하면 본월의 ✅ 개수
참고: COUNTIF가 아니라 SUMPRODUCT를 쓰는 이유는, 본월 날짜 + ✅ 값이라는 두 조건을 동시에 만족하는 칸을 세야 하기 때문입니다.
SUMPRODUCT는 여러 조건 배열을 곱해서 다중 조건 카운트를 처리합니다.
5. 완성된 완료율 수식
분자와 분모를 합치고, Daily 업무일 때만 표시하는 IF 함수를 넣으면 최종 수식이 완성됩니다.
업무 유형은 C열에, 데이터는 5행부터 시작한다고 가정합니다.
=IF(
$C5 = "Daily",
SUMPRODUCT(
(MONTH($E$3:$AL$3) = VALUE($D$2))
* ($E5:$AL5 = "✅")
)
/
IF($D$3="포함",
DAY(EOMONTH(DATE($B$2,$D$2,1),0)),
NETWORKDAYS(DATE($B$2,$D$2,1), EOMONTH(DATE($B$2,$D$2,1),0))
),
""
)
이 셀의 표시 형식을 백분율(0%)로 설정하면 0.733... 대신 73%로 나옵니다.
6. 템플릿 나눔
Daily 루틴 트래커 템플릿 3편에 걸쳐 만든 시트입니다.
구글 스프레드시트 사본 만들기 링크 클릭 시 본인 드라이브에 사본이 생성됩니다.
⬇️ 아래 시트 클릭 > [사본 만들기] 해서 사용해주세요
https://docs.google.com/spreadsheets/d/1pb7lb_r3DfKC_o9z3mXbivFl41oaRuy-IVUgF4y9vJQ/edit?usp=sharing
❗️참고 사항
1. 월별 시트 복사: 한 시트에서 월을 바꾸면 이전 체크 데이터가 날짜와 맞지 않습니다.
시트 탭 우클릭 → 복사로 월별 사본을 만들어 쓰세요. 수식과 조건부 서식이 함께 복사됩니다.
2. 업무 유형 구분: C열에 "Daily"로 표시된 행만 완료율이 나옵니다. Weekly, Monthly 행이 빈 칸인 건 정상입니다.
3. 이모지 입력: Windows는 Win + ., Mac은 Ctrl + Cmd + Space로 이모지 입력창을 열 수 있습니다.
❗️사용 가이드
1. B2에 연도, D2에 월 입력
2. B3에서 시작일 기준 선택 ("1일" / "월요일" / "일요일")
3. D3에서 주말 포함 여부 선택 ("포함" / "제외")
4. 왼쪽에 업무명 입력, 매일 ✅ / ⌛ / ❌ 체크
완료율은 Daily 업무에 한해 자동으로 계산됩니다.
7. 마치며
날짜 자동 생성이라는 단순한 출발점에서 시작했는데, 만들다 보니 WEEKDAY, EOMONTH, SUMPRODUCT 같은 함수들을 자연스럽게 익히게 됐습니다.
필요한 걸 직접 만들어 보는 게 결국 가장 좋은 공부인 것 같습니다. 이 시리즈가 도움이 되셨다면 좋겠습니다.
스프레드시트 셀프 스터디 기록입니다. 틀린 부분이나 더 나은 방법이 있을 수 있으니 참고용으로만 봐주세요~
상업적 사용은 금지합니다.
댓글