한빛출판네트워크

출처: 회사에서 바로 통하는 엑셀 2003

재무부에 근무하는 김대리는 1년 자금 계획과 사용일을 계산하고 있습니다. 월급일은 25일인데 토/일요일인 경우에는 하루 전일로 앞당겨서 지급해야 합니다. 그런데 달력을 보고 일일이 작업하려고 하니 여간 번거로운게 아닙니다. 어떻게 쉽게 해결할 수 없을까요? 그리고 월을 계산할 때도 1월 1주, 2주, 3주 하듯이 해당 달의 주를 계산하는 방법도 알고 싶습니다.

먼저 월급날이 25인 경우에 년 초에 모든 월급일을 알 수 있는 수식을 작성해보겠습니다.



위 표는 월급일이 25일인 경우 매월 월급일을 일목요연하게 보여줍니다. 사용된 수식은 화면에서도 확인해 보실 수 있듯이 다음과 같습니다.

=DATE($B$1, A4, $E$1) – IF(WEEKDAY(DATE($B$1, A4, $E$1), 2)>5, WEEKDAY(DATE($B$1, A4, $E$1), 2) – 5)

위 수식은 간단하게 설명하면 다음과 같습니다.

① DATE(년도 값, 월 값, 일 값) 함수로 매월 25일에 해당하는 날짜를 구합니다.
② – IF(로 요일이 토, 일요일인지를 판단하여(WEEKDAY(, 2)>5), 토, 일요일인 경우 각각 WEEKDAY(날짜, 2) 함수는 6, 7을 반환하기 때문에 이 값을 5로 빼면 각각, 1, 2가 반환됩니다.
③ 따라서 25일이 토요일인 경우 토요일에서(6에서 5을 뺀 결과) 1일을 뺀 24일을, 일요일인 경우엔 2일을 뺀 23일을 각각 월급일로 지정하도록 한 것입니다.

위 수식은 대부분의 날짜에서 정상 동작하지만 각 월의 마지막 날을 지날 경우 정상적인 계산을 하지 못하는 단점을 지니고 있습니다. 그래서 매월 마지막이 월급일인 경우를 별도로 계산해 보겠습니다.



매월 마지막 일을 계산하는 공식은 다음 두 가지가 있습니다.

=DATE(년도, 월 + 1, 0)
=DATE(년도, 월 + 1, 1) – 1

어떤 걸 이용해도 같은 결과를 반환합니다. 위 화면에서 사용된 수식은 다음과 같습니다.

=DATE($B$1, A4+1, 0) – IF(WEEKDAY(DATE($B$1, A4+1, 0), 2) > 5, WEEKDAY(DATE($B$1, A4+1, 0), 2)-5)

전체적으로 앞에서 사용한 수식과 날짜의 계산부분만 바뀌고 나머지는 같다라는 것을 확인할 수 있습니다.

마지막으로 달의 주(WEEK)를 계산하는 수식에 대해서 알아보겠습니다. 엑셀에서 주(WEEK)를 계산해주는 함수로 WEEKNUM 함수가 있습니다. WEEKNUM 함수는 월별 주(WEEK) 일련번호를 반환하지 않고 1년을 통틀어서 주(WEEK)의 일련번호를 반환합니다. 즉, 1, 2, 3, 4, 5, 6, 7, 8, …., 53까지의 번호를 반환합니다.

하지만 WEEKNUM 함수는 추가 기능의 ‘분석 도구’를 설치하지 않으면 사용할 수 없는 ‘추가 기능’ 함수입니다. WEEKNUM 함수를 이용해서 주를 계산할 수도 있지만, 워크시트 함수만으로 계산할 수 있는 방법을 알아보겠습니다.

=INT((DAY(날짜) – WEEKDAY(날짜) + 6)/7) + 1

위의 수식이 달의 주(WEEK)를 계산하여 주는 수식입니다. 날짜에서 요일(월, 화, …, 일에 해당하는 일련번호)을 뺀 후 6을 더해서 7로 나눈 정수값에 1을 더하면 됩니다.

INT 함수로 나눗셈을 계산하면 나눗셈의 몫만을 반환받게 됩니다. 즉 7로 계산할 경우 0, 1, 2, 3, 4 이런 값을 반환하여 주는데 날짜와 요일은 최대 6의 차이값을 가지므로(날짜가 1일 요일을 반환하는 WEEKDAY가 7을 반환하는 경우 날짜에서 요일을 빼면 -6이 나옵니다), 계산된 결과에 6을 더해 7로 나눈 몫과 1을 더하면 주의 값이 계산됩니다.

화면을 참고하시길 바랍니다.