엑셀 업무 효율 계산 완전 가이드 — 생산성·가동률·처리량·정시율 템플릿
환경: Excel 365/2019. 글 구조화에는 AI를 보조로 활용했지만 모든 수식은 직접 재현해 검증했습니다.
연계 가이드(내부 링크 7+)
1) 지표 설계 — 원리는 단순, 계산은 투명
- 측정가능 숫자·시간·건수로 환산
- 비교가능 목표/표준과의 차이
- 재현가능 누구나 수식만 보면 계산 근거를 이해
권장 단위: 시간은 소수(시간)와 [h]:mm 표시를 병행. 합산/평균은 숫자열로, 보고용은 표시열로 분리.
2) 템플릿 열 & 기본 수식(구조적 참조)
표 이름: LogTbl
| 열 | 의미 | 수식(예시) | 서식 |
|---|---|---|---|
| WorkHrs | 실제 작업시간(시간) | =((LogTbl[End]-LogTbl[Start])-LogTbl[Break])*24 | 0.00 |
| OnTime | 정시 완료 여부 | =LogTbl[CompletedAt]<=LogTbl[Due] | TRUE/FALSE |
| OK | 품질 합격 | =LogTbl[Quality]="OK" | TRUE/FALSE |
| UPH_Target | 목표 처리량(건/시간) | =LogTbl[TargetUPH] | 숫자 |
| UPH_Actual | 실제 처리량 | =IFERROR(LogTbl[Units]/LogTbl[WorkHrs],0) | 0.00 |
3) 업무 효율 핵심 지표 10가지(복붙용)
/* ① 효율% (표준시간 기반) */
=IFERROR( (LogTbl[Units] * 60 / LogTbl[TargetUPH]) / (LogTbl[WorkHrs]*60) * 100 , 0)
/* ② 효율% (목표 처리량 대비) */
=IFERROR( LogTbl[UPH_Actual] / LogTbl[UPH_Target] * 100 , 0)
/* ③ 가동률(Availability) = 실제 작업 / 계획 근무 */
=IFERROR( SUM(LogTbl[WorkHrs]) / SUM(LogTbl[PlannedHrs]) * 100 , 0)
/* ④ 활용률(Utilization) = 작업 중 핵심/빌러블 비중 */
=IFERROR( SUMIFS(LogTbl[WorkHrs], LogTbl[Billable], TRUE) / SUM(LogTbl[WorkHrs]) * 100 , 0)
/* ⑤ 정시율(On-time) */
=IFERROR( COUNTIFS(LogTbl[OnTime], TRUE) / COUNT(LogTbl[OnTime]) * 100 , 0)
/* ⑥ 품질율(Quality rate) */
=IFERROR( COUNTIFS(LogTbl[OK], TRUE) / COUNT(LogTbl[OK]) * 100 , 0)
/* ⑦ 종합효율(OPE) = 효율 × 품질 × 정시 */
=ROUND( 효율% * 품질율/100 * 정시율/100 , 1)
/* ⑧ 인당 생산성(일/주) */
=LET(u, UNIQUE(LogTbl[Person]),
HSTACK(u, MAP(u, LAMBDA(p, SUMIFS(LogTbl[Units], LogTbl[Person], p)))) )
/* ⑨ 카테고리별 시간/건수 */
=LET(cat, UNIQUE(LogTbl[Category]),
HSTACK(cat,
MAP(cat, LAMBDA(c, SUMIFS(LogTbl[WorkHrs], LogTbl[Category], c))),
MAP(cat, LAMBDA(c, SUMIFS(LogTbl[Units], LogTbl[Category], c)))
))
/* ⑩ 상위 병목 업무(시간 TOP5) */
=LET(t, SORT(HSTACK(LogTbl[Task], LogTbl[WorkHrs]), 2, -1),
TAKE(t, 5))표준시간이 없을 때는 초기 1~2주의 평균 UPH_Actual을 기준선으로 삼고 목표를 단계적으로 상향하세요.
4) 피벗 없이 만드는 미니 대시보드(스파크라인)
/* 날짜별 처리량 추이(스파크라인) */
=SPARKLINE( SUMIFS(LogTbl[Units], LogTbl[Date], UNIQUE(LogTbl[Date])) )
/* 인당 주간 생산성 랭킹 */
=LET(p, UNIQUE(LogTbl[Person]),
s, MAP(p, LAMBDA(x, SUMIFS(LogTbl[Units], LogTbl[Person], x))),
SORT(HSTACK(p,s), 2, -1))5) 병목 파레토 & 개선 체크리스트
- 상위 20% 업무가 80% 시간을 쓴다면 표준작업·매크로·단축키 교육 투입
- 정시율↓이면
Due산정 로직/대기시간(핸드오프) 점검 - 품질율↓이면 원인별 불량 코드화 →
COUNTIFS로 재발 탑5 - 가동률↓이면 회의/비가동 로그 분리 기록
다음으로 LET·LAMBDA로 중복 계산을 줄이고, VSTACK/HSTACK으로 월별 로그를 한 번에 합치세요.