얼마전 병원에서 근무중인 사람이라며 블로그 비밀댓글로 질문을 해왔다. 엑셀로 환자 정보를 관리하는데 환자의 진료기록 일자로부터 경과한 기간별로 셀에 채우기를 다르게 하고 싶다는 것이었다.
구체적으로 다음과 같이 채우기를 하길 원한다고 한다.
특정 일자로부터 30일까지는 연한 파란색
특정 일자로부터 30~ 50일까지는 옅은 주황색
특정일자에서 2개월이 되기 7일 전부터는 빨간색
특정일자로 부터 2개월 경과시 노란색
문제를 어떻게 해결해야할지 갈피를 못잡고 있는 것 같았는데 해결 방법은 다음과 같다.
① 이 문제는 엑셀 조건부서식을 사용하면 쉽게 해결할 수 있다. 위 4가지 조건을 각각 조건부서식의 규칙으로 만들어 규칙에 따른 서식을 적용하기만 하면 된다.
② 규칙들은 모두 특정 일자로부터 경과한 기간을 검사하는 수식이 된다. 특정 일자로부터 경과한 기간은 DATEDIF 함수를 사용하여 구할 수 있다.
엑셀 조건부서식으로 경과일에 따라 서식지정하기
위 표는 실제로 병원에서 사용하는 양식은 아니고 단지 특정 일자로부터 경과한 기간에 따라 적용서식을 다르게 하는 방법을 보이기 위해 날짜정보만 입력한 형태다. 그리고 다음 과정을 따라하면 된다.
① 서식을 적용할 날짜를 모두 선택한 후 홈->조건부서식->새규칙을 선택한다.
새 서식 규칙 대화상자가 뜨면 수식을 사용하여 서식을 지정할 셀 결정을 선택한 다음의 수식을 입력한다.
=DATEDIF($G$2,B2,"d")<=30
특정일자에 해당하는 셀 주소는 절대참조로, 조건을 검사해서 셀 서식을 적용할 날짜의 가장 첫 번째 셀주소를 상대참조로 넣어준다.
세 번재 인자 "d"는 DATEDIF 함수에게 두 날짜 사이의 일자를 구하라는 뜻이다.("m"이면 개월수 등등.. 이 블로그의 datedif 함수 참고)
이제 오른쪽 아래 서식을 눌러 채우기 설정을 연한 파랑으로 해준 후 모두 확인을 눌러서 닫아보자.
2017년 1월 1일부터 2017년 1월 31일까지 30일 경과한 날짜까지 연한 파란색의 채우기가 적용되었다.
나머지 조건에 대해서도 이와 같은 방법으로 조건부서식을 적용하면 된다. 나머지 조건들로 조건부 서식을 만들어보자. 그리고 다음과 같이 서식이 적용되었는지 확인해보자.
이 결과는 제대로 적용되지 않은 결과다. 왜 이런 결과가 나왔냐면 조건부서식에서 규칙을 여러개 만들 때는 규칙간에 우선순위가 존재하기 때문이다. 그리고 우리는 조건을 잘 해석해서 우선순위를 제대로 적용해야한다. 그 전에 4개의 규칙에 대한 수식을 정리해 보겠다.
① 특정 일자로부터 30일까지는 연한 파란색
② 특정 일자로부터 30~ 50일까지는 옅은 주황색
③ 특정일자에서 2개월이 되기 7일 전부터는 빨간색
④ 특정일자로 부터 2개월 경과시 노란색
각각 순서대로 다음 수식에 대응된다.
=DATEDIF($G$2,B2,"d")<=30
=DATEDIF($G$2, B2, "d")<=50
=DATEDIF($G$2,B2+6,"m") =2
=DATEDIF($G$2,B2, "m")>=2
조건부 서식으로 규칙을 만들 때는 나중에 만든 규칙이 우선순위가 높게 된다. 따라서 조건부 서식으로 위 조건의 순서대로 적용한 후의 우선순위는 ④ ③ ② ① 이 된다.
우리는 우선순위를 ④ ③ ① ② 으로 바꿔야한다. 왜냐면 조건 ②의 경우 수식에서 50이하의 경우만 고려하였다. 따라서 ①번 수식의 범위를 포함한다. 결국 우선순위가 ②가 ①보다 높다면 ①번 수식이 ②번 수식에 가려져서 적용되지 않게 된다. 다음은 규칙의 우선순위를 바꾸는 방법이다.
조건부서식 -> 규칙관리를 선택한다.
①번 조건에 대한 규칙을 선택한 후 위쪽 화살표▲를 클릭해서 우선순위를 높인다. (위쪽에 있는 규칙이 우선순위가 높다.)
모두 확인을 눌러 빠져나온 후 제대로 적용되었는지 확인하자.
제대로 서식이 적용되었다.