티스토리 뷰
군대에서 매월 이맘때 즈음이 되면 행정병이었던 동기 하나는 늘 밤을 새야 했습니다. 다음달에 장병들이 휴가 나갈 날짜를 취합해야 했기 때문입니다. 병사와 간부를 합쳐 400명쯤 되는 인원의 휴가를 한 데 모아 정리하는 것은 결코 쉬운 일이 아니었습니다. 한컴 한글(hwp) 파일에 휴가일을 예쁘게 표시해야할 뿐 아니라 각 날짜별로 몇 명의 인원이 휴가 상태인지 비율도 계산해야 했기 때문입니다.
▼ 당시 행정병이 사용하던 것은 아래와 같은 한컴 한글 양식이었습니다. 400 여 명의 정보를 이렇게 정리하는 것은 시간도 시간이지만 유지·보수에 어려움이 정말 엄청 났습니다. 애써 날짜별로 출타자 비율 계산을 다 해놓았는데 한 두 명 씩 휴가를 바꿀 때마다 세로 줄로 다 다시 더하고 계산하는 작업을 해야했기 때문입니다. 그러다가 실수라도 하여 출타자 비율을 잘못 계산했을 때에 행정병이 겪었어야 하는 고초는 말로 다 할 수 없습니다.
그런데 사실 위와 같은 작업은 엑셀로 하면 정말 순식간에 끝나는 일입니다. 처음에 만들 줄을 몰라서 그렇지 양식만 만들어 놓으면 날짜만 입력하면 자동으로 색칠되고 출타자 비율이 계산되게 할 수 있습니다. 그래서 제가 상병이 됐을 무렵에 컴퓨터를 빌려 엑셀 양식을 행정병에게 만들어 주어 고맙다는 인사를 연거푸 받았던 적이 있습니다. 그 기억을 더듬어 휴가기간, 출타기간, 출입국기록 등을 자동으로 표시해 줄 수 있는 엑셀 양식을 만들어 보겠습니다.
달력 형태 꾸미기
먼저, 전체적인 양식의 모습을 만들어 봅시다.
▼ 취합할 당시 입력 받을 데이터는 다음과 같이 [이름], [출발일], [복귀일], [휴가종류] 4가지로 가정합니다.
▼ 날짜가 시작될 부분에 엑셀 날짜 형태로 [2020-10-01] 데이터를 입력합니다. 그러면 엑셀에서 자동으로 표시 형식을 [mm월 dd일] 형태로 바꾸어 [10월 01일] 이라고 표시되는 것을 볼 수 있습니다.
▼ 칸도 좁은데 [10월 01일]이라고 표시할 수는 없습니다. 단순히 [1]이라고만 표시되게 하기 위해서 셀 서식[Ctrl+1]으로 들어가 [사용자 지정] 범주에서 표시 형식을 [d] 로 바꿉니다.
- (참고) 형식을 d로 하면 날짜가 [1, 2, 3, 4...] 형태로, 형식을 dd로 하면 날짜가 [01, 02, 03, 04...] 형태로 표시됩니다.
▼ 날짜 서식을 변경한 결과 [E2] 셀에 들어 있는 데이터는 [2020-10-01]이지만 표시형식은 [1]로 바뀌었음을 확인할 수 있습니다.
▼ 이제 [E2] 셀의 날짜 데이터를 우측으로 채우기(초록색 점을 끌어서 드래그) 하고 열 너비를 조정하면 아래와 같이 월력 형태가 만들어집니다. 다른 날짜들 역시 표시형식은 [31] 이어도 날짜값은 [2020-10-31] 과 같은 형태로 나와야 합니다.
▼ [휴가자]와 [비율] 행을 만들어주고 배경색 등 서식을 약간 만져 기본적인 문서 형태를 만들어 줍니다.
- [출발일]과 [복귀일] 역시 표시 형식을 [mm/dd] 형태로 고쳤습니다.
휴가자 자동 계산하기
이제 자동으로 휴가자 수를 계산하는 기능을 만들어보겠습니다. 휴가자 수를 자동 계산은 다음과 같은 원리를 기본으로 합니다.
- 휴가 도중이면 [출발일 <= 해당날짜 <= 복귀일 ] 이다.
- 즉, [출발일 <= 해당날짜] 이고 [해당날짜 <= 복귀일] 일 때가 바로 출타일이다.
▼ E1셀에 [ =IF ( AND ( $B3 <= E$2, E$2 <= $C3 ), 1 , 0 ) ] 이라 입력합니다. $는 절대참조를 의미하는데 채우기를 통해 나머지 셀들도 손쉽게 계산하기 위해 추가하였습니다.
▼ [E1] 셀에 적힌 내용을 드래그하여 [AI11] 셀까지 채워보았습니다. 절대참조($)를 해놓았기 때문에 각 셀에 맞게 함수가 수정되어 입력되었습니다. [출발일]과 [복귀일]에 맞추어 휴가 날에는 1, 휴가가 아닌 날에는 0이 입력되었습니다.
▼ [=SUM(E3:E11)] 을 통해 날짜별 휴가자 수의 합계를 구합니다. 이렇게 더하기를 편하게 하기 위해서 [ =IF ( AND ( $B3 <= E$2, E$2 <= $C3 ), 1 , 0 ) ] 을 통해 휴가일에는 1, 아닌 날에는 0이 입력되도록 해놓은 것입니다.
▼ 마찬가지로 [=E12 / COUNTA(E3:#11) * 100]을 계산하여 출타자 비율도 구해봅니다. 저는 사람 수가 바뀔 때마다 자동 반영되도록 [COUNTA : 비어있지 않은 셀의 개수를 구하는 함수] 를 사용해 보았습니다. 그냥 사람 수인 [9] 로 나누셔도 무방합니다.
- 이때 열 너비가 부족하면 계산한 값이 [##]으로 표시되는데 당황하지 마시고 글자 크기를 줄이시거나 칸을 늘리시면 됩니다.
조건부 서식으로 자동 색칠하기
아직 다 만든 것이 아닙니다. 예쁘게 색칠해주는 기능이 빠졌습니다. 군대에서는 문서를 예쁘게 만드는 것을 엄청 중요시하기 때문에 색칠이 빠졌다가는 큰일납니다. [조건부 서식] 기능을 사용하여 자동으로 색칠 되도록 기능을 넣어 주도록 합시다.
▼ 먼저, 숫자 0과 1로 지저분하게 나와 있는 부분을 예쁘게 정리해보겠습니다. 달력 부분인 [E3:AI11] 영역을 선택한 채로 [조건부서식]-[새 규칙]으로 진입합니다.
▼ [수식을 사용하여 서식을 지정할 셀을 결정] 을 선택한 다음에 [=E3=0] 이라고 입력해 줍니다. 이렇게 하면 [자신의 값이 0인 셀]을 선택한 것이고 이제 [서식]을 눌러 글자를 지워버릴 겁니다.
▼ 바로 이렇게 말이죠. 글자색을 배경색과 똑같이 흰색으로 지정해줍니다.
▼ 이렇게 [조건부 서식]을 지원하면 다음과 같이 숫자가 0이었던 칸들의 글씨가 흰색으로 바뀌면서 자동으로 달력이 정리되게 됩니다. 마찬가지 방법으로 숫자가 [1]이 써져 있는 부분들도 색칠해보겠습니다.
▼ 숫자 0을 지울 때와는 규칙과 서식이 조금 다릅니다.
- 규칙은 [=E3=0] 대신 [=E3=1] 을 사용했습니다.
- 서식은 글자와 배경을 똑같이 [노란색]으로 만들었고 [좌우 테두리]를 없앴습니다.
▼ 여기까지만 해줘도 되지만, 노란색 색칠이 된 부분의 제일 바깥쪽 테두리가 다 사라져버려 아쉽습니다. 제일 바깥쪽 테두리는 다시 추가해주는 서식을 걸어주도록 합시다.
- 박스의 왼쪽테두리 추가 규칙 : [ = AND(E3=1, D3=0) ] 일 때 [왼쪽] 테두리 실선 서식 적용
- 박스의 오른테두리 추가 규칙 : [ = AND(E3=1, F3=0) ] 일 때 [오른쪽] 테두리 실선 서식 적용
▼ 마지막으로 출타율에 따라 빨간계통의 경고 표시가 칠해지도록 [조건부 서식]을 걸어주겠습니다. 출타율 기준이 몇 이었는지 기억이 정확히 나지 않는데 25% 를 기준으로 표시하도록 작성하였습니다.
- [$E$12:$AI$13] 범위에 대하여 규칙이 [=E$13 >= 25] 일때 빨간색 색칠 서식 적용
완성된 모습(파일공유)
여기까지 완성한 파일을 공유해드립니다.
▼ 여기까지 완성한 모습은 다음과 같습니다. [출발일], [복귀일]을 변경하여도 자동으로 달력에 표시부터 휴가자수 비율계산까지 수행해주기 때문에 무척 편리합니다.
▼ 인원을 추가하는 경우에도 마지막 행을 남겨두고 중간 부분에 행을 추가해주면 엑셀 양식을 고칠 필요 없이 자동으로 계산됩니다.
- 마지막 행인 [최원사] 뒤쪽에 행을 추가하게 되면 다른 부분은 괜찮은데 [비율] 부분 계산이 틀려지게 됩니다.
기타 사항
여기까지 구현한 바로, 한글 서식과 달리 [휴가종류]를 노란색 바 안에 집어넣지 못했습니다. 이 부분은 궁리를 좀 해봤는데 매크로를 짜지 않고서는 달리 구현할 방법을 떠오르지가 않더군요. 그래서 최대한 노가다를 덜 할 수 있게 수정한 파일을 아래와 같이 첨부합니다.
▼ 겉보기엔 똑같이 생겼지만 더 이상 달력 부분에 숫자 [0]과 [1]을 채우지 않습니다. [조건부 서식]이나 [휴가자 비율 계산] 등의 내부에서 조건을 다 처리하도록 수식을 변경하였습니다.
- 대신에 기존에 숫자 [0]과 [1]이 채워지던 부분에는 휴가 첫날에 [휴가종류]가 채워지게 됩니다.
- 사용자가 해야 할 일은 [휴가기간] 노란 막대 부분을 CTRL을 누른 채로 잘 선택해서 [병합하기 버튼]을 누르는 것 뿐입니다.
▼ 최종적으로 이제는 필요 없어진 [D열]의 [휴가 종류] 를 [숨기기] 해버리면, 아래와 같이 깔끔한 월력 양식이 탄생하게 됩니다.
- 다만, 이렇게 해버리면 다른 기능들은 사관 없으나 [병합]은 사용자가 수동으로 해줘야하니 그 부분은 감안하셔야합니다.
