직원 연차·휴가 관리표 만들기 | 엑셀 SUMIF·IF 함수 실무 예제

직원연차휴가자동관리

 

직원이 휴가를 사용할 때마다 남은 연차를 직접 계산하고 계신가요?

직원 수가 적을 때는 어렵지 않지만, 인원이 늘어나면 누가 며칠을 사용했는지 확인하는 일도 번거로워집니다.

특히 하루 휴가와 반차가 섞이거나 같은 직원이 여러 번 휴가를 사용하면 계산을 잘못하기 쉽습니다.

이번에는 직원별 연차 사용 내역을 입력하면 잔여 연차가 자동으로 계산되는 엑셀 관리표를 만들어 보겠습니다.

완성된 실습 파일도 무료로 제공합니다.

1. 직원의 남은 연차를 어떻게 관리할까?

연차 관리표에서 가장 중요한 것은 부여일수, 사용일수, 잔여일수를 구분하는 것입니다.

기본 계산식은 다음과 같습니다.

잔여 연차 = 부여 연차 − 사용 연차

예를 들어 연차가 15일 부여된 직원이 2.5일을 사용했다면 잔여 연차는 12.5일입니다.

하지만 휴가를 여러 번 사용했다면 각각의 기록을 먼저 합산해야 합니다.

이번 실습에서는 이를 자동으로 처리하겠습니다.

2. 첫 번째 작업: 직원별 연차 현황표 작성

먼저 엑셀의 '연차현황' 시트에 다음 항목을 만듭니다.

항목	입력 내용
A열	사번
B열	직원명
C열	부서
D열	입사일
E열	부여일수
F열	사용일수
G열	잔여일수
H열	관리상태

8행부터 직원 정보를 입력합니다.

여기에서 E열의 부여일수는 직접 입력하는 값입니다.

연차 발생일수는 입사일, 근속기간, 출근율 등 여러 조건에 따라 달라질 수 있으므로 이번 실습에서는 자동 산정하지 않습니다.

3. 두 번째 작업: 휴가 사용 내역 기록하기

이번에는 별도의 '휴가사용내역' 시트를 만듭니다.

이 시트에는 사번, 직원명, 휴가 사용일자, 사용일수, 휴가구분을 입력합니다.

예를 들어 김철수 직원의 휴가 기록이 다음과 같다고 가정하겠습니다.

사용일자	사용일수
2026-02-10	1일
2026-05-04	1일
2026-08-18	0.5일

총 사용일수는 2.5일입니다.

반차는 0.5일로 입력하면 됩니다.

중요한 점은 직원 이름보다 사번을 기준으로 기록을 연결하는 것입니다. 동명이인이 있어도 다른 직원의 휴가 내역이 합산되는 일을 줄일 수 있습니다.

4. 세 번째 작업: SUMIF 함수로 사용일수 자동 합산

이제 직원별 휴가 사용일수를 계산하겠습니다.

'연차현황' 시트의 F8 셀에 다음 수식을 입력합니다.

=IF(A8="","",SUMIF('휴가사용내역'!$B$7:$B$106,A8,'휴가사용내역'!$E$7:$E$106))

이 수식은 휴가사용내역의 B열에서 해당 사번을 찾고, 일치하는 행의 E열 사용일수를 모두 더합니다.

김철수 직원의 사번이 E001이라면 사용일수 1일, 1일, 0.5일이 합산되어 2.5일로 표시됩니다.

휴가 기록이 추가되면 계산 결과도 자동으로 변경됩니다.

5. 네 번째 작업: 잔여 연차 계산하기

이번에는 부여일수에서 사용일수를 빼겠습니다.

G8 셀에 다음 수식을 입력합니다.

=IF(A8="","",E8-F8)

김철수 직원에게 15일이 부여되고 2.5일을 사용했다면 잔여 연차는 12.5일입니다.

IF 함수를 함께 사용했기 때문에 사번이 입력되지 않은 행에는 계산 결과가 표시되지 않습니다.

같은 방식으로 수식을 아래쪽 직원까지 복사하면 여러 직원의 잔여 연차를 관리할 수 있습니다.

6. 연차가 부족한 직원 자동 표시하기

잔여일수가 적거나 초과 사용한 직원을 빠르게 구분하면 관리가 편리합니다.

H8 셀에 다음 수식을 사용합니다.

=IF(A8="","",IF(G8<0,"초과 사용",IF(G8<=3,"잔여 적음","정상")))

이 수식은 잔여 연차를 기준으로 세 가지 결과를 표시합니다.

• 잔여일수가 음수라면 '초과 사용'
• 잔여일수가 0~3일이면 '잔여 적음'
• 그 외에는 '정상'

잔여 3일이라는 기준은 실습을 위해 임의로 설정한 관리 기준이며, 법적 기준을 의미하지 않습니다.

완성 파일에는 잔여일수가 부족한 경우 눈에 띄도록 색상도 적용했습니다.

7. 실제 업무에서 주의해야 할 부분

연차 관리표를 사용할 때는 다음 사항을 확인해야 합니다.

첫째, 사번은 중복되지 않도록 관리해야 합니다.

둘째, 반차를 사용할 경우 사용일수에 0.5를 입력해야 합니다.

셋째, 휴가 사용 내역이 해당 관리기간의 기록인지 확인해야 합니다.

넷째, 연차의 발생·소멸·이월 등은 단순한 엑셀 계산과 별개로 확인해야 합니다.

이번 파일은 연차 부여일수를 직접 입력하고 사용일수와 잔여일수를 계산하는 학습용 서식입니다. 실제 인사·노무 관리에 적용하려면 최신 법령과 사업장 규정을 확인해야 합니다.

8. 직원 연차·휴가 관리표 무료 다운로드

이번 글의 실습 내용을 직접 연습할 수 있도록 엑셀 파일을 준비했습니다.

파일은 다음 세 가지 시트로 구성되어 있습니다.

• 연차현황: 직원별 부여·사용·잔여일수 확인
• 휴가사용내역: 연차 및 반차 사용기록 입력
• 사용안내: 입력 방법과 함수 설명

직원별 부여일수를 변경하거나 새로운 휴가 기록을 추가하면서 계산 결과를 확인해 보세요.

첨부 파일: GS10_직원_연차휴가_자동관리표.xlsx

※ Microsoft 365 또는 Excel 2021 이상에서 실습할 수 있습니다.

 

 

GS10_직원_연차휴가_자동관리표.xlsx

0.01MB

마무리

이번에는 SUMIF와 IF 함수를 연결해 직원 연차·휴가 관리표를 만들어 보았습니다.

단순히 휴가 일수를 더하고 빼는 것이 아니라 직원 정보와 휴가 사용기록을 분리해 관리하는 방법을 익히는 것이 핵심입니다.

이처럼 업무에 맞는 데이터 구조를 먼저 만들고 필요한 함수를 적용하면 반복적인 계산과 확인 작업을 줄일 수 있습니다.

다음 글에서는 재고 수량이 일정 기준 이하로 내려가면 자동으로 경고가 표시되는 재고 관리표를 만들어 보겠습니다.