재택근무가 보편화되면서, 자율 선택형 재택근무가 가능한 곳에서는 보통 팀단위로 팀원들의 재택 여부를 체크할 필요가 많아졌다.
그래서 구글 스프레드시트로 만들어 본 자동 근무 계획표다. 이 표의 목적 다음과 같다.
- '본인 근무 계획 작성'만 하면, 전체 팀원의 근무 상태를 별도 작업 없이 한눈에 파악할 수 있도록 한다.
- '본인 근무 계획 작성' 외에는 이 근무표가 작동하기 위한 모든 작업을 최대한 자동으로 이루어지도록 한다.
완성된 시트 링크를 먼저 던지고 시작하겠다.
재택 근무표
월간 해당 월,근무 상태 기준표,최소 출근 인원 2023-01,출근,연차,1 (YYYY-MM 형식),재택,오전반차 외근,오후반차 여분1,여분2 날짜,요일,한민숙,전희우,손영옥,류승주,풍형식,황광현 0,2023. 1. 1, 일요
docs.google.com
1. 날짜 입력 자동화
일단 테이블의 기본 와꾸를 잡아준다.
(한민숙 씨가 이끄는 6명으로 이루어진 팀이 있다고 가정했다. 팀원 이름은 랜덤 이름 생성기를 사용했다.)
그럼 이제 여기에 날짜를 1일부터 31일까지 쓰고, 그 옆에 요일을 월요일부터 일요일까지 쓰면 되나? 사실 오래 걸리는 일은 아니지만 내가 만들 근무표는 월 단위로 구성한 뒤 다음 달이 되면 새 시트를 복사해서 사용하도록 할 예정이다. 그리고 오래오래 썼으면 좋겠다. 따라서 이런 식으로 매월 날짜랑 요일을 다 수동으로 다 쓰면 너무 귀찮을 것이다.
지금이 몇월인지만 입력하면 자동으로 날짜랑 요일이 작성 완료되도록 해야겠다.
몇 월인지 입력할 칸을 하나 만들어주고, IF와 MONTH 함수만으로 간단히 작성했다.
구글 스프레드시트는 날짜를 YYYY-MM 형태로 적으면 알아서 그 달의 1일의 값으로 치환된다.
그래서 이 칸에 YYYY-MM 형태로 입력하게끔 하고, 날짜의 첫 칸에 다음과 같이 수식을 걸었다.
그리고 A열에 0부터 30까지의 숫자를 입력했다. (이게 무슨 기능을 하는지는 아래에 나옴)
=if(month($B$3)=month($B$3+A9),$B$3+A9,"")
이게 뭔 뜻이냐면,
해당월에 쓴 날짜(=2023. 1. 1)의 '월'과, 그 날짜에 A9 셀에 있는 값을 더한 값의 '월'이 같으면, 해당 월 + A9 셀을 더한 값을 표시하고, 다르면 공백으로 표시해라.
는 말이다.
이렇게 하면 어떻게 되느냐? 30일까지 있는 달에는 30일까지의 날짜만 표시가 되고, 31일까지 있는 달에는 31일까지 다 표시된다. (예를 들어, 10월의 근무표를 만들기 위해 해당월을 2023-10이라고 입력했다고 치자. 그러면 31번째 날짜칸에서는 $B$3 + 30으로 계산된 2023. 11. 1은 MONTH값으로 11을 갖게 되고, 이 값은 해당월의 월 값인 10과 다르기 때문에 공백으로 표시되는 것이다)
이 함수를 그 아래로 B39까지 긁어서 내려주면 날짜 자동입력 완성.
날짜 입력이 완료되면, 요일 부분은 아래와 같이 해결하면 된다.
=if(B9<>"",weekday(B9),"")
바로 옆 날짜가 공백이 아닐 경우, WEEKDAY(B9)의 값을 표시하고, 공백이면 공백으로 표시해라
근데 WEEKDAY 함수의 값은 기본적으로 1~7의 정수값으로 표시된다. 구글 스프레드시트에서는 함수 없이 이걸 한글 요일 텍스트로 변환할 수 있다.
서식을 이렇게 바꾸면 된다.
이렇게 요일 자동입력까지 해결하고, A열은 수식만을 위한 값이니까 안 보이게 사이즈를 1로 바꾸면 (아니면 폰트색을 흰색으로 바꿔도 된다) 이렇게 날짜 자동입력은 완료가 된다.
해당 월에 YYYY-MM 형식의 월 정보만 입력하면, 그 월의 1일부터 마지막날까지의 날짜와 요일이 자동으로 작성된다.
한 가지가 더 남았다. 이 표는 근무 기록을 위한 표다. 따라서 주중과 주말은 그 쓰임새에 근본적인 차이가 있다. 구분되어야 한다. 우리 사회는 이미 주말과 주중을 표에서 구분하기 위해 색깔을 사용하고 있다. 주말에 색칠을 해보자
날짜와 요일이 입력될 때, 자동으로 토요일은 파란색, 일요일은 빨간색 글씨가 되도록 하자
조건부서식을 사용하면 간단한 일이다.
날짜와 요일 전체 영역을 선택하고 조건부서식을 눌러보자
그리고 조건부 서식 규칙을 아래와 같이 2개 설정하면 된다.
맞춤 수식이 범위에 어떻게 적용되는지 연구를 해봤는데, 범위 내의 첫 셀 (가장 왼쪽, 가장 위)을 기준으로 함수를 작성하면 범위 내 모든 셀에 그대로 적용되는 것으로 확인됐다. 위 범위에선 B9이 첫 셀이라서 그 셀 기준의 함수를 작성했다.
자 이제 '해당월'에 YYYY-MM 형식의 월 정보만 넣으면, 해당 월의 1일부터 마지막날까지의 날짜와 요일이 자동으로 써지고, 그중 주말에는 각각 맞는 색깔이 자동으로 색칠되는 기능까지 구현이 완료되었다.
2. 근무 상태 색칠 자동화
이렇게 자동으로 작성된 날짜에 맞추어 근무 계획을 작성해 보자.
한눈에 이 팀의 출근과 재택 비중 및 개개인의 출퇴근 여부가 파악이 되는가? 물론 몇 분 들여다보면 파악이 되겠지만, 인간의 눈은 근본적으로 흰 바탕에 똑같은 검정색 글씨로 쓰인 3개 이상의 다른 텍스트를 한 눈에 분별하지 못한다. 6명 중 누가 출근하고 누가 재택 하는지 따위의 사소한 정보를 파악하기 위해 매번 몇 분씩의 시간을 허비하게 할 순 없다. 여기도 색깔이 필요하다.
우선 어떤 근무 상태를 어떤 색으로 표현할지 기준표를 만들었다.
색깔 선정도 대충 한 것이 아니다.
대부분의 직장인들은 이 모든 선택지 중에 출근을 제일 싫어할 것이라고 가정하고, 가장 금지되어야 할 것 같은 느낌을 주는 빨간색을 배정했다. 그리고 이 테이블의 가장 주요한 쓰임새는 출근과 재택을 분별하는 것이 될 것이기 때문에 그 반대색인 초록색을 재택 배정했다.
그리고 휴가 때는 보통 쾌청한 마음이 들기 때문에 휴가와 관련된 선택지엔 파란 계열을 넣었으며, 외근에 대한 호불호는 케바케라서 중립적인 노란색을 넣었다.
그래서 전체적으로 표가 완성됐을 때, 뜨거운 색상일수록 팀원들의 마음이 좋지 않고, 차가운 색상일수록 팀원들의 마음이 좋다는 것이 직관적으로 보이도록 하였다.
(6개 이외의 추가적인 근무 상태 구분이 필요할 때를 대비해서 여분 색상도 2개 만들어놨다.)
근무 상태를 입력하면, 해당 셀이 해당 근무 상태에 배정된 색깔로 자동으로 색칠되도록 하자.
근무 상태를 입력할 전체 영역을 선택하고 조건부서식을 눌러보자
그리고 출근의 빨간색부터 지정해 보자. 요일 색칠과 마찬가지로 맞춤 서식을 쓰면 된다.
=D9=$H$3
선택 범위 안에서, H3셀과 같은 값이 있는 셀은 빨간색으로 색칠하는 서식을 적용하라
H3에 절대위치 지정($)을 해야 한다는 점을 주의하자. 이렇게 하지 않을 경우, 범위 내에서 D9셀이 아닌 다른 셀에서는 H3이 아닌 다른 셀을 참조하게 되므로 조건부서식이 제대로 작동하지 않는다.
이 작업을 기준표에서 색깔을 배정한 모든 상태에 대해 다 해주면 된다. 약간 노가다 같지만 이거 8번만 하면 매번 수동으로 수십 번 색칠해야 하는 공수가 사라진다.
이제 다 됐다.
이렇게 조건부서식을 '근무 상태 기준표'를 참조한 조건으로 만들어두면 한 가지 더 좋은 점은, 기준표에서 기준텍스트를 바꾸면 자동으로 그 텍스트 기준으로 서식이 적용된다는 점이다.
예를 들어, 우리 회사는 병가를 따로 기록해야 하고, 그래서 여분으로 만들어둔 보라색을 병가에 배정해 쓰고자 한다면
이렇게 간단히 기준표 내 보라색에 병가라고 쓰기만 하면 병가라고 쓰여있는 칸은 자동으로 색칠된다.
디자인을 좀 정리하면 이렇게 된다. 디자인은 입맛에 맞게 알아서 하세요
(OPTIONAL) 최소 출근 인원 경고 자동화
종종 완전 자율 재택까지는 아니고, 팀마다 꼭 사무실에 출근해야 하는 최소 인원을 정해두는 곳도 있다.
근무 계획표를 작성할 때, 출근을 선택한 인원이 최소인원에 미달되는지도 자동으로 체크해 주면 좋을 것 같다.
그러면 이렇게 하면 되겠다.
최소 출근 인원수를 적을 칸을 추가하고, 근무 계획표 옆 빈칸에 이 수식을 넣었다.
=ifs(counta(D9:I9)=0,"",mod(weekday(B9),7)<=1,"",countif(D9:I9,"출근")>=$K$3,"",countif(D9:I9,"출근")<$K$3,"출근자 부족")
이 날 입력된 근무 상태가 없으면 공백을 표시하고, 입력된 근무 상태가 있더라도 토요일이거나 일요일이면 공백을 표시하고, 토요일이거나 일요일이 아닐 경우에 "출근"의 개수가 최소 출근 인원 이상이면 공백으로 표시하고, "출근"의 개수가 최소 출근 인원 미만일 때 "출근자 부족"으로 표시해라.
그러면 만약 출근이라고 적은 사람이 한 명도 없는 날이 생기면 이렇게 경고가 표시된다.
질문 환영
