자동화 연차 관리 시스템 만들기

우리 회사는 연차를 관리할 수 있는 시스템이 없다.

지금까지는 연차를 쓰려면 대표님께 메일을 보내고 연차를 쓰겠다고 말하였다.

그래서 내 연차가 얼마나 남았는지 한눈에 보기 어렵고 관리하기 어려웠다.

2~3시간정도 걸려서 직접 시스템을 간단하게 만들어 보았다.

 

<결과>

1. 구글 폼으로 연차신청서를 제출하면 구글 스프레드 시트에 기록이 쌓이고 자동으로 연차가 차감된다.

2. 연차신청을 하면 연차관리페이지에 자동으로 업데이트 되며, 본인페이지를 볼 수 있다.

3. 연차신청을 하면 대표자의 메일로 알려준다.

 

혹시라도 직접 만들어보고 싶은 사람이 있을 것 같아,

만드는 방법을 공유해보려고 한다.

 

아무것도 모르는 내가 직접 하나하나 다 만들었기 때문에 더 나은 방법이 반드시 있을 것이라

참고만 해주면 좋겠다.

 

<만들기>

https://docs.google.com/forms

Google Forms: 로그인

먼저 구글 폼즈에서 간단하게 양식을 만들어 준다.

+ 버튼을 누르고, 객관식 질문에서 날짜로 변경해준다.

그리고 연차시작일 이라고 제목을 정하고, 복사하여 하나 더 만들어준다.

복사한 질문박스는 연차종료일이라고 제목을 수정하여 준다.

 

우리 회사는 반차, 연차 뿐이라 선택지를 2개만 만들어주었는데,

반반차가 있는 경우에는,, 엑셀 수식을 엄청 많이 고쳐주면 된다.

 

대략 이런식으로 만들어 주었다.

그리고 설정으로 가서 응답칸을 이미지처럼 바꿔주면 된다.

구글 신청폼에서 직원들을 이메일로 관리하기 위해서 이메일을 수집하여 준다.

마지막으로 응답 버튼을 누르고 sheets에 연결을 누른다.

그러면 구글폼즈에서 할건 다 끝났다.

 

 

구글 시트의 이름을 바꿔주고 시작해보자.

나는 간단하게 '연차관리'라고 써주었다.

시트이름 왼쪽에 보라색박스가 생긴곳이 구글폼에서 제출한 내용이 적혀지는 공간이다.

구글폼즈의 기록이 적혀지는 공간을 위 이미지와 같이 바꿔주었다.

안바꿔도 되지만, 나는 보기 편하게 하기 위해 순서를 바꾸어 주었다.

수식도 따라 할거면 순서를 나와 같이 맞춰주어야 한다.

그리고 + 버튼을 누르고 '종합'이라는 시트를 생성했다.

여기서는 직원들의 잔여연차와 근속연수를 확인하는 공간으로 만들었다.

 

 

https://docs.google.com/spreadsheets/d/18QylI8mfAKuhzsqAXS6OHDKXdqlU1Ra_XGEowWU3IPk/edit?usp=sharing 

연차관리

 

종합 시트는 위 공유 스프레드 시트를 복사하면 된다.

 

 

인덱스 칸에 있는 함수는 근무자가 있을 경우만 인덱스 번호가 채워진다.

=IF(C5="", "", 2)

만약 같은 행의 근무자가 빈칸일 경우에는 빈칸으로 채우고, 근무자가 적혀있을 경우 2라는 글자를 표시한다는 뜻이다.

회색 공간은 함수로 채워놔서 가만히 두면 되고, 하얀칸들만 채우면 된다.

 

근무자의 이름을 적고, 입사일을 적는다.

그러면 근속연수와 기본 발생 연차가 나오게 된다.

 

=IF(D4="","",IF(DATEDIF(D4,TODAY(),"y")=0,DATEDIF(D4,TODAY(),"y"),DATEDIF(D4,TODAY(),"y")))

근속연수의 년에 해당하는 컬럼에는 위와 같은 엑셀함수를 적어주었는데

만약 입사일이  적혀있지 않다면 빈칸이 나오게 하고, 입사일이 적혀있다면 몇년간 회사를 다녔는지 구해주는 함수이다.

월도 마찬가지이다.

 

다음은 발생연차이다.

발생연차는 기본으로 지급받는 연차의 일수이고, +@ 열은 추가로 지급되거나, 이 프로그램을 쓰기 전에 쓰인 연차를 적으면 된다.

3일을 쓴 상태라면 -3을 입력하면 된다.

 

=IF(D4="","",IF(E4>0,VLOOKUP($E4,$N$3:$O$10,2,0),IF(E4=0,VLOOKUP($F4,$Q$3:$R$15,2,0))))

발생연차의 함수이다.

입사일이 적혀있지 않다면 빈칸을 출력하고 아니라면 다음 IF문을 실행한다. (1번째 IF문)

적혀있다면 E4 (근속연수)가 0보다 큰지 확인하고 크다면 VLOOKUP함수로 , N,O열의 1년이상 연차표를 참고하고

근속연수가 0이라면 Q,R열의 1년미만 연차표를 이용하여 연차일수를 계산한다.

 

다음은 잔여연차이다. 공유해준 엑셀시트는 신청서의 기록이 남는 시트가 없어서 오류가 나있다.

구글폼즈를 연동한 엑셀시트의 이름을 '연차신청기록'으로 바꾸면 오류가 해결될 것이다.

 

 

 

=IF(D4="","",SUM(G4:H4) - (SUMIFS('연차신청기록'!C:C, '연차신청기록'!A:A, J4, '연차신청기록'!C:C, ">="&DATE(YEAR(TODAY()),1,1)) - SUMIFS('연차신청기록'!B:B, '연차신청기록'!A:A, J4, '연차신청기록'!C:C, ">="&DATE(YEAR(TODAY()),1,1)) + COUNTIFS('연차신청기록'!A:A, J4, '연차신청기록'!C:C, ">="&DATE(YEAR(TODAY()),1,1)) - COUNTIFS('연차신청기록'!A:A, J4, '연차신청기록'!D:D, "반차")*0.5))

잔여 연차의 함수는 엄청 길다. 내가 엑셀함수를 몰라서 대충 만들다 보니 길어졌다.

일일히 다 해석해줄수는 없으니 간단하게 설명하겠다.

 

먼저 D4(입사일)이 적혀있으면 '연차신청기록'시트에서 근무자의 이메일을 찾는다.

그리고 신청한 연차종료일에서 연차시작일을 빼서 연차개수를 계산하는데 연차로 체크되어있으면 일수를 계산하고,

반차라고 써져 있으면 0.5일로 계산하여 모두 더한다음, 기본연차와 +@의 연차와 함께 계산한다.

 

여기까지 '종합'시트의 함수설명이 다끝났다.

 

그리고 간단하게 시트를 꾸며주기 위해 조건부 서식을 사용하여 회색칸을 칠해주었다.

이 조건부 서식은 안해도 상관없다. 귀찮으면 이부분은 넘어가도 된다.

 

 

서식을 정해줄 칸을 드래그하고, 서식버튼을 누르고 형식 규칙은 '비어 있지 않음' 선택하고, 색깔을 지정해주면 된다.

마찬가지로 근속연수(년/월), 발생연차(기본), 잔여연차도 마찬가지로 서식을 지정해준다.

이렇게 해준 이유는 회색칸은 건드리지말라는 의미로 만들었다.

 

여기까지만 만들고 사용해도 크게 상관은 없다.

하지만 직원 개개인이 본인이 연차내역을 보게 하기 위해 추가 작업을 진행해주었다.

 

다음은 개별 페이지를 만들어 주는 과정이다.

공유해준 스프레드 시트에서 홍길동시트를 열고 복사해서 가지고 가면 된다.

나는 최대한 간편하게 만드는걸 좋아하기 때문에 이름칸만 바꾸면 모든게 적용되게 만들었다.

홍길동 시트를 복사하여 근무자 이름만 적는다면 다른 직원의 페이지로 바뀌게 된다.

 

 

먼저 회색칸의 이름은 수기로 적어주고, 입사일은 함수로 적용되어있다.

=INDIRECT("'종합'!D"& MATCH($B$4,'종합'!$C$4:$C$21,0)+3)

입사일에 적혀있는 함수는 '종합'시트에서 홍길동이라는 근무자의 이름을 찾아서 입사일을 출력해준다.

다른 열에 있는 근속연수나, 이메일도 위 함수로 똑같이 만들었으니 설명은 생략한다.

순번도 '종합'시트에 있는 인덱스 열과 같은 방식으로 만들었으니 생략한다.

 

 

연차시작일 함수이다.

=ArrayFormula(IF(COUNTIFS('연차신청기록'!A:A, $I$4, '연차신청기록'!B:B, ">="&DATE(YEAR(TODAY()),1,1))<ROW()-7,"",INDIRECT("'연차신청기록'!B"&SMALL(IF(('연차신청기록'!A:A=$I$4)*('연차신청기록'!B:B>=DATE(YEAR(TODAY()),1,1)), ROW('연차신청기록'!A:A),""),ROW()-7))))

 

이 부분은 나에게 꽤 어려운 부분이라 상당히 어렵게 만들었다.

이 페이지에서 고려할 부분이 꽤 많았기 때문에 먼저 고려한 부분이다.

 

1. 연차기록되는 곳에서 올해의 기록만 가지고 와야한다.

2. 올해의 기록중 해당하는 근무자의 기록만 가지고 와야한다.

3. 전체 중에서 차례대로 나오게 해야한다.

 

IF(COUNTIFS('연차신청기록'!A:A, $I$4, '연차신청기록'!B:B, ">="&DATE(YEAR(TODAY()),1,1))<ROW()-7

위 함수에서 IF문을 먼저 가지고 왔다.

이 뜻은 연차신청기록시트에서 I4의 값인 근무자의 이메일이 있는지 확인하면서, 해당 기록이 올해의 기록인지 확인한다.

 

해당하는 기록이 맞다면, IF문의 Ture절로 가서 다음 함수를 실행한다.

INDIRECT("'연차신청기록'!B"&SMALL(IF(('연차신청기록'!A:A=$I$4)*('연차신청기록'!B:B>=DATE(YEAR(TODAY()),1,1)), ROW('연차신청기록'!A:A),""),ROW()-7))))

연차신청기록시트에서 근무자의 이메일이며, 올해의 기록이라면 연차시작일 정보를 가지고 온다.

한번에 모든행을 다 가지고 올 수 있지만, 개인 직원 페이지는 사용자에게 보기 편하게끔 만들어서,

연차신청기록시트와 배열이 다르기 때문에 연차 시작일에 해당하는 한 열만 가지고 왔다.

 

다 설명하려면 내가 이해한 것을 말로 풀기엔 공부가 더 필요해서 필요하다면 다른곳에서 검색해서 보면 좋겠다.

함수 설명은 이정도만 하고, 공유해준 스프레드 시트를 복사해서 사용하면 된다.

 

나는 직원들이 잘못 입력한 경우가 무조건 있을거라고 생각하고 조건부 서식으로 뭔가 잘못입력했을 경우,

빨간색으로 칠해주어 수정하라고 알려주는 방법을 선택했다.

 

위 이미지는 내가 노션에 사용방법을 정리해둔건데, 연차 시작일보다 종료일이 더 나중이거나,

연차인데 반차로 잘못 쓰는 경우에는 그 행이 빨간색으로 바뀌게 된다.

 

=IF($C8>$D8, 1,  IF(($C8<>$D8)*($E8="반차"),1,0))

조건부 서식을 적용할 곳을 드래그하고, 서식버튼을 누르고 조건부 서식을 클릭한다.

그리고 형식 규칙을 맞춤 수식으로 바꾸고 위의 함수를 넣어주고 색깔을 적용하면 된다.

함수는 바꿔도 된다. 내가 만든 함수는 시작일보다 종료일이 과거이거나, 시작과 종료일이 다른데 반차인 경우에 해당한다.

 

그다음은 구글 폼즈로 직원들이 연차를 신청한다면 메일이 오게 할 것이다.

도구를 누르고, 알림설정, 알림 수정을 누르고 규칙설정을 위 이미지와 같이 해주면 된다.

 

이제 마지막으로 직원들이 개별페이지로 확인하는 방법이다.

파일을 누르고 공유, 웹에 게시를 누르고 직원 시트로 변경하고, 웹에 게시창에서 빨간박스와 같이 설정한다.

그리고 2번째 박스링크를 개별 직원에게 전달하면 된다.

 

그러면 이제 자동화된 연차관리 시스템이 만들어졌다.

노션같은곳에 구글폼을 넣어두면 편하게 사용 가능하다.

 

이게 좋은점은 개별 페이지가 동적으로 업데이트가 자동으로 된다.

신청서를 제출하면 직원페이지를 새로고침하면 바로 업데이트가 되어 확인이 가능하다.