엑셀 교대 근무표 양식: 주간 근무 시간, 초과 근무, 인원 배치 자동 계산

구성

통합 문서에는 시트가 5개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 자동으로 계산돼요.

시작하기 전에

파일을 열면 바로 쓸 수 있어요. 셀 색으로 각 칸의 용도를 알 수 있어요.

1. 파일을 열어요. 엑셀 위쪽에 노란색 제한된 보기 표시줄이 나오면 편집 사용을 눌러요. 인터넷에서 받은 파일은 항상 이렇게 열려요.
2. 파일› then 다른 이름으로 저장으로 내 사본을 저장해 두면 언제든 원본으로 다시 시작할 수 있어요.
3. Staff의 예시 직원을 내 직원으로 바꾸고, Roster의 근무 코드를 지워요(노란색 칸을 선택하고 Delete). Roster의 이름은 Staff에서 같은 행 순서로 가져와요.

단계별 사용법

처음에는 순서대로 따라 하세요. 그다음부터는 정기적으로 업데이트하는 단계만 반복하면 돼요.

1. 근무 코드 설정하기

   Lists의 근무 코드 표가 모든 코드를 정해요. 근무마다 Code(E, D, L 등), Shift Name, Start와 End 시각, 무급 Break (min)(휴게 시간, 분)를 입력해요. Paid Hours는 자동 계산돼요. 오전 6시~오후 2시 근무에 휴게 30분이면 7.5시간이에요. 자정을 넘기는 야간 근무도 계산돼요.

   PTO(유급 휴가)나 SICK(병가)처럼 시각이 없는 코드는 Leave Hours를 써요. 실제로 일하는 코드만 On Shift?를 Yes로 두세요. Yes인 코드만 근무 인원과 초과 근무에 들어가요.

2. 직원 추가하기

   Staff에 직원마다 Role(직무), Overtime After (hrs/week)(주당 초과 근무 기준 시간, 예: 40), Hourly Rate(시급)를 입력해요. Roster에 같은 순서로 나와요.

3. 주 고르기

   Roster의 B3에 그 주의 첫날을 입력해요. 요일 머리글 7개에 날짜가 표시되고, 주말 머리글은 더 진한 색이 돼요.

4. 근무표 채우기

   노란색 칸을 클릭해 드롭다운에서 코드를 고르거나 직접 입력해요. 근무 코드는 초록색, OFF는 회색으로 바뀌어요. 직원마다 다음이 계산돼요.

   • Worked Hours: On Shift가 Yes인 근무 시간
   • Paid Hours: 근무 시간 + PTO 같은 유급 휴가 시간
   • Overtime: 그 직원의 기준 시간을 넘는 근무 시간
   • Days On(근무 일수), Est. Pay(예상 급여), Flag: Overtime, No day off(휴무 없음), Check code(근무 코드 표에 없는 코드), OK

5. 인원 확인하기

   근무표 아래 Coverage by day에서 요일별로 근무 코드 수를 세요. Staff needed 행에 요일마다 필요한 인원을 입력하세요. 근무 인원이 모자라면 Status에 빨간색으로 Short 1처럼 표시되고, 충분하면 OK예요.

6. 대시보드 보기

   Start Here에서 Employees Scheduled, Total Worked Hours, Overtime Hours, Employees in Overtime, Days Short-Staffed, Est. Labor Cost를 볼 수 있고, 요일별 근무 인원과 필요 인원을 비교하는 차트도 있어요.

7. 다음 주 계획하기

   지난주 근무표를 남기려면 Roster 시트 탭을 마우스 오른쪽 단추로 클릭하고 이동/복사를 고른 뒤 복사본 만들기에 체크하세요. 복사본 이름은 Roster Nov 2처럼 바꿔요. 그런 다음 원래 Roster에서 날짜와 코드를 바꾸면 돼요. 대시보드와 Checks는 항상 이름이 Roster인 시트를 읽어요.

수식 이해하기

수식을 몰라도 템플릿은 쓸 수 있지만, 수식을 살펴보면 엑셀 공부에 도움이 돼요. 엑셀에서 셀을 클릭하면 수식 입력줄에 수식이 보여요.

=IF(A5="", 0, IF(AND(ISNUMBER(C5), ISNUMBER(D5)),
   ROUND(MOD(D5-C5, 1)*24-N(E5)/60, 2), N(F5)))

=IF(A5="", "", SUMPRODUCT(SUMIF(Lists!$A$5:$A$20, C5:I5, Lists!$I$5:$I$20)))

=SUMPRODUCT(COUNTIF(C$5:C$34, Lists!$A$5:$A$20)*(Lists!$G$5:$G$20="Yes"))

=IF(A5="", "", MAX(0, J5-IF(Staff!$C5="", 40, Staff!$C5)))

팁과 한계

• 여기서 초과 근무는 직원별 기준 시간을 넘는 주간 근무 시간이고, Est. Pay는 그 시간에 시급의 절반을 더해 계산해요. 초과 근무와 휴게 규정은 지역과 계약마다 다르니 적용되는 규정에 맞게 기준을 조정하세요.
• 직원 30명, 근무 코드 16개까지 들어가요. Roster 시트 하나가 한 주예요.
• 코드는 대소문자를 구분하지 않아서 d와 D 모두 Day 근무로 세요.
• Roster는 인원 현황 표까지 가로 방향 한 페이지에 인쇄되도록 설정돼 있어요.