메뉴
실무 위키›응용 공식›직원별 주간 업무 배정시간과 초과시간 계산 공식

직원별 주간 업무 배정시간과 초과시간 계산 공식

2021
지원 버전 자세히 보기
엑셀 2021엑셀 2024M365웹 엑셀

주간 배정시간과 수용시간을 넘긴 초과시간을 계산합니다.

예제파일 다운로드
직원별 주간 업무 배정시간과 초과시간 계산 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01직원별 한 주의 배정시간 합산하기

=SUMIFS ( 계획시간 범위, 원장 직원 범위, 직원, 작업일 범위, ">=" & 주 시작일, 작업일 범위, "<" & 주 시작일 + 7 )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
계획시간 범위필수작업마다 배정한 시간이 숫자로 입력된 범위입니다. 원장 직원 범위와 작업일 범위의 행 수를 같게 맞춥니다.
원장 직원 범위필수작업을 맡은 직원명이 입력된 범위입니다.
직원필수주간 배정시간을 계산할 직원명이 입력된 셀입니다. 원장 직원 범위와 수용시간 기준표의 표기와 글자까지 같아야 합니다.
작업일 범위필수각 작업을 수행할 날짜가 입력된 범위입니다. 날짜와 시각이 함께 입력된 경우에도 다음 주 시작일은 제외됩니다.
주 시작일필수주간 집계를 시작할 날짜가 입력된 셀입니다. 비교 연산자 >=를 큰따옴표로 묶고 &로 연결해 이 날짜를 포함합니다.
C2=SUMIFS($H$2:$H$12,$F$2:$F$12,A2,$G$2:$G$12,">="&B2,$G$2:$G$12,"<"&B2+7)
A
B
C
D
E
F
G
H
I
J
K
L
1
직원
주 시작일
배정시간
초과시간
직원
작업일
계획시간
직원
주간 수용시간
2
김민수
2026-08-31
44
4
김민수
2026-08-28
6
김민수
40
3
박서연
2026-08-31
36
0
김민수
2026-08-31
8
박서연
40
4
이준호
2026-08-31
42
7
김민수
2026-09-01
12
이준호
35
5
김민수
2026-09-07
16
0
김민수
2026-09-03
16
6
김민수
2026-09-06
8
7
박서연
2026-09-01
20
8
박서연
2026-09-04
16
9
이준호
2026-09-02
18
10
이준호
2026-09-05
24
11
김민수
2026-09-07
10
12
김민수
2026-09-10
6
13
주 시작일은 포함하고 7일 뒤의 다음 주 시작일은 제외해 한 주만 집계합니다. 날짜와 직원 조건을 모두 만족하는 작업의 계획시간을 합산합니다.

02주간 수용시간을 넘긴 초과시간 계산하기 (엑셀 2021 이후)

=MAX ( 0, 배정시간 - XLOOKUP ( 직원, 수용시간 직원 범위, 주간 수용시간 범위 ) )
인수 설명 자세히 보기
인수구분설명
배정시간필수직원과 주간 조건을 모두 만족하는 작업의 계획시간 합계가 입력된 셀입니다.
직원필수주간 수용시간을 찾을 직원명이 입력된 셀입니다. 수용시간 직원 범위의 표기와 글자까지 같아야 합니다.
수용시간 직원 범위필수직원마다 주간 수용시간을 정한 기준표의 직원명 범위입니다. 중복 없이 한 번씩 입력합니다.
주간 수용시간 범위필수수용시간 직원 범위와 행 수를 같게 맞춘 직원별 주간 수용시간 범위입니다.
D2=MAX(0,C2-XLOOKUP(A2,$J$2:$J$4,$K$2:$K$4))
A
B
C
D
E
F
G
H
I
J
K
L
1
직원
주 시작일
배정시간
초과시간
직원
작업일
계획시간
직원
주간 수용시간
2
김민수
2026-08-31
44
4
김민수
2026-08-28
6
김민수
40
3
박서연
2026-08-31
36
0
김민수
2026-08-31
8
박서연
40
4
이준호
2026-08-31
42
7
김민수
2026-09-01
12
이준호
35
5
김민수
2026-09-07
16
0
김민수
2026-09-03
16
6
김민수
2026-09-06
8
7
박서연
2026-09-01
20
8
박서연
2026-09-04
16
9
이준호
2026-09-02
18
10
이준호
2026-09-05
24
11
김민수
2026-09-07
10
12
김민수
2026-09-10
6
13
직원별 주간 수용시간을 배정시간에서 빼고, 남는 값이 음수이면 0으로 표시합니다. 초과시간이 큰 직원부터 작업을 재배치할 수 있습니다.
이 공식이 사용하는 함수
SUMIFS 함수 MAX 함수 XLOOKUP 함수

동작 원리

01직원별 한 주의 배정시간 합산하기

SUMIFS 함수가 직원이 같고 주 시작일 이상이며 다음 주 시작일보다 앞선 작업의 계획시간을 합산합니다.

예제 시트에서 김민수의 2026-08-31 주간 배정시간을 구하는 C2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

김민수에게 배정된 시간을 모두 합산합니다

원장에서 김민수에게 배정된 시간은 이전 주 6시간, 집계 주 44시간, 다음 주 16시간입니다. 날짜를 구분하기 전 합계는 66시간입니다.

=SUMIFS ( $H$2:$H$12, $F$2:$F$12, A2 )
= 66
2

주 시작일 이전 작업을 제외합니다

>=는 기준 날짜 이상을 뜻합니다. 2026-08-31 이상 조건을 넣으면 이전 주에 배정된 6시간이 빠져 합계는 60시간입니다.

=SUMIFS ( $H$2:$H$12, $F$2:$F$12, A2, $G$2:$G$12, ">=" & B2 )
= 60
3

다음 주에 배정된 작업을 제외합니다

<는 기준 날짜보다 앞선 값만 남깁니다. 2026-09-07 미만 조건을 더하면 다음 주의 16시간이 빠져 이번 주 배정시간은 44시간입니다.

=SUMIFS ( $H$2:$H$12, $F$2:$F$12, A2, $G$2:$G$12, ">=" & B2, $G$2:$G$12, "<" & B2 + 7 )
= 44

02주간 수용시간을 넘긴 초과시간 계산하기 (엑셀 2021 이후)

XLOOKUP 함수로 직원의 주간 수용시간을 찾고, MAX 함수가 배정시간과의 차이를 0 이상으로 계산합니다.

예제 시트에서 김민수의 초과시간을 구하는 D2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

직원의 주간 수용시간을 찾습니다

김민수는 수용시간 기준표의 첫 번째 직원이므로 주간 수용시간 40시간을 가져옵니다.

=XLOOKUP ( A2, $J$2:$J$4, $K$2:$K$4 )
= 40
2

배정시간에서 수용시간을 뺍니다

김민수의 배정시간 44시간에서 주간 수용시간 40시간을 빼면 4시간이 남습니다.

=C2 - 40
= 4
3

양수로 남은 초과시간만 표시합니다

0과 수용시간을 넘긴 4시간 중 큰 값은 4입니다. 배정시간이 수용시간 이하이면 계산 결과가 음수여도 MAX 함수가 0을 반환합니다.

=MAX ( 0, 4 )
= 4
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료