엑셀 배우기, 일주일 이내 종료일을 확인할 수 있는 조건부 서식!

여러분들이 학원생들의 수강 관리를 담당하고 있다고 합시다. 학생들은 월단위로 1개월에서 3개월씩 수강을 계속 갱신한다고 했을 때, 여러분의 업무는 각 학생별로 수강 종료일이 되기 전에, 일주일 이내에 수강이 종료되는 학생들과 상담을 통해서 강좌를 계속해서 수강할지 말지를 결정하도록 해야 합니다.

그렇다면, 매일매일 학생별로 종료일일 확인해줘야야는데, 이 역시 함수를 사용하여 해당 학생들의 명단을 추출할 수도 있겠습니다만, 이보다는 더 간단한 엑셀의 조건부 서식을 활용면 필요한 데이터들을 쉽게 확인할 수가 있답니다. 아주 간단히 한번에 쉽게 확인할 수 있는 방법입니다. 수강 종료일이 이번주에 해당한다거나, 또는 오늘 날짜부터 일주일 이내에 종료하는 학생들의 셀값의 색깔을 자동으로 바꾸는 방법입니다.

일단 아래는 조건부 서식만을 보여드리기 위해 정말 간단히 만든 데이터입니다. 실제로는 학생 수강 관리를 담당한다면, 학생 이름부터 생년월일, 성별, 연락처, 기타 등등 아주 다양한 데이터들을 입력해 두었겠지요. 일단 수강 시작일 기준으로 EDATE 함수를 활용해서 수강 종료일을 계산합니다.

EDTE 함수는 아래의 포스팅에서 이미 다루었으니 참고로 확인하셔도 좋고, 간단히 함수의 구조만 말씀드리자면, '=EDATE(시작일, 개월수)'입니다. 시작일 기준 특정 개월수 뒤의 날짜를 반환하는 함수입니다. 아래에서는 시작일 기준으로 각각 D열의 예약 월수만큼 지난 날짜를 반환했고, 정확히 만 1달, 2달, 3달을 맞추기 위해 EDATE 함수 값에서 1을 빼줬습니다.

엑셀배우기, EDATE 함수와 WEEKDAY 함수로 수강 종료일 구하기!

다음으로 바로 조건부 서식을 활용해 보도록 하겠습니다. C열 전체를 범위로 잡아도 좋고, 필요한 데이터만 범위로 잡아도 좋습니다만, 데이터는 계속 추가적으로 입력이 될 것이기 때문에, 저는 이렇게 조건부 서식을 설정할 때는 일반적으로 열 전체를 범위로 설정합니다. 아무튼 범위를 지정하였다면, 아래 화면 오른쪽 상단의 '조건부 서식' 메뉴를 누르고, '셀 강조 규칙', '발생 날짜' 메뉴를 차례대로 눌러주세요.

그럼 아래의 팝업창이 뜨는데, 점선 박스로 표시된 부분을 이번주로 고정해 주면, 아래와 같이, '조건'에 부합하는 셀들에 '진한 빨강 텍스트가 있는 연한 빨강 채우기'의 서식이 곧바로 적용이 됩니다. 물론 서식을 바꾸어줄 수도 있습니다. 셀에 다른 색들을 채울 수도 있고, 텍스트 색깔 역시 변경 가능합니다.  

그런데 단순이 이번주에 수강이 종료되는 학생들이 아니라, 오늘 날짜를 기준으로 7일 이내에 수강이 종료되는 학생들을 위와 같이 조건부 서식으로 표시하고 싶다면 어떻게 해야 할까요? 그렇다면, '오늘'과 '오늘부터 7일 이후의 날짜'라는 두가지 데이터가 먼저 필요합니다.

아주 간단합니다. 아래의 B1셀에 TODAY 함수를 사용하는 겁니다. '=TODAY()'만 넣어주면 바로 오늘 날짜를 반환해줍니다. 당연히 내일이 되면 내일 날짜가 자동으로 보여집니다. 그리고 7일 이후의 날짜가 필요하니, 바로 옆 셀에, '=B1+7'이라는 수식을 넣어줍니다. 자, '오늘'과 '오늘부터 7일 이후의 날짜'라는 두가지 데이터가 준비되었으니, 다시 조건부 서식으로 들어갑니다.

이번에는'조건부 서식', '셀 강조 규칙'까지는 같은데, '다음의 값 사이에 있음' 세부 메뉴를 눌러주세요. 그리고 이 다음의 값들에 B1셀과, C1셀을 차례대로 지정해 줍니다. 지정과 동시에 이미 새로운 조건에 해당하는 셀들에 '진한 빨강 텍스트가 있는 연한 빨갱 채우기' 서식이 바로 적용되었지요? 조건부 서식과 또 바로 옆 표 서식은 엑셀에서 함수만큼이나 정말 유용한 기능입니다.

특히 표 서식의 경우는 특정 서식으로 작성된 표를 자동으로 확장시켜주는 기능이 있답니다. 단순한 서식부터, 각각의 셀들에 적용되어 있는 함수 수식들까지 그대로 복사해서 차례대로 확장을 시켜주지요. 예를들자면, 아래의 표가 표 서식으로 만들어진 데이터라면, B19셀에 새로운 시작일을 넣는 순간, 그대로 아래의 서식 그대로 새로운 행이 하나 생겨나고, 동시에 C열에 적용된 EDATE 함수도 똑같이 복사된답니다. 참 유용한 기능입니다. 간단한 기능이기 때문에 아마 다른 엑셀 강좌에서 함께 다뤄보도록 하겠습니다.