이전 조건부 서식 (초급) 시간에선 데이터막대를 소개했다. 이번에 소개할 조건부 서식은 수식을 사용한 서식이다. 수식을 사용하면 복잡한 셀 서식도 지정할 수 있다. 대표적으로 간트차트, 특정 행 강조 이다. 그리고 꼭 알아야 할 한 가지가 더 있다. 그것은 바로 '절대참조' 이다. 수식과 절대참조의 하모니를 알아보자.

수식 규칙 적용

아래 그림은 간단한 성적 자료이다. 너무 간단해서 실망스러울 수도 있겠지만 조건부 서식의 수식기능을 이해하기 위한 더 없이 좋은 자료이다. 문제가 주어졌다. 총점 200 이상인 행을 색칠해보자. 오른쪽 표가 목표로 하는 상태이다.

조건부 서식 : 성적

다음 그림과 같이 B3:F10을 선택하고 조건부 서식의 새 규칙을 적용해보자. '수식을 사용하여 서식을 지정할 셀 결정'을 선택한다. 아래에 수식입력줄과 서식버튼이 생긴다. 설명에 써있듯이 수식이 참인 셀에 지정한 서식을 적용한다. 적절한 서식을 정하고, 수식은 =$F$3 >= 200 으로 입력하자.

조건부 서식 : 수식 규칙

수식의 의미

=$F$3 >= 200 는 가장 평범하게 적용할 수 있는 수식이다. 이 수식을 예제에 적용했다면 아무 변화가 없었을 것이다. 이제부터 수식의 의미에 대해서 설명하겠다. 절대참조 얘기가 나올 차례이다. 기억하고 있어야 할 것은 조건부 서식의 적용 범위이다. 규칙 관리자의 적용 대상을 보면 =$B$3:$F$10 라고 나온다. 수식을 적용 대상의 모든 셀에 적용한다고 생각하면 되지만, 엄밀히 각각 개별적으로 적용된다.

입력한 수식의 동작 과정을 설명하겠다. =$F$3 >= 200B3 셀에 적용되는 수식이다. 그렇다면 B4 셀에는 어떤 수식이 적용되는 것일까? =$F$3 >= 200 수식이 B3 셀에 입력되어 있다고 가정하고 B4셀까지 끌어내렸을 때의 수식이 B4셀에 적용된다. 여기서 F, 3 모두에 참조키워드$를 지정했기 때문에 B4 셀도 역시 =$F$3 >= 200 수식이 적용된다. 같은 방법으로 모든 셀($B$3:$F$10)에 =$F$3 >= 200이라는 수식이 적용된다. F3 셀 값은 138 이기 때문에 수식의 결과는 FALSE이다. 따라서 모든 셀의 수식 결과는 FALSE이고, 서식이 적용되지 않는다.

원하는 결과가 나오게 하기 위해선 어떻게 해야할까? 각 셀 별로 자신의 행에 있는 총점을 참고해서 수식을 지정해야 한다. 참조를 약간 수정하면 된다. 정답은 넷 중 하나이다. 모두 다 적용해 보길 바란다.

  1. =$F$3 >= 200
  2. =$F3 >= 200
  3. =F$3 >= 200
  4. =F3 >= 200

설명

B3:F3F3셀 값을 참조해야 하고, B4:F4 : F4, B5:F5 : F5 를 참조해야 한다. 공톰점을 찾자면 모든 셀은 자신의 행의 F열을 참조한다는 것이다. 그것을 수식으로 옮겨주면 된다. =$F3>=200 수식을 보자. $F이기 때문에 고정이다. 3은 상대참조이다. $F3B3셀 기준에서이다. B4셀은 $F4가 적용될 것이다. 따라서 우리가 원하는 결과를 얻을 수 있다.

마무리

조건부 서식에서의 수식 사용은 쉽지 않다. 절대참조가 어떻게 변할 것인지 머리 속으로 상상하면서 수식을 작성해야 하기 때문이다. 입력한 수식은 적용범위의 맨 왼쪽위셀에 적용되고, 나머지 셀들은 그 셀의 수식을 끌어서 채운다. 보기에도 어렵고 실재로도 헷갈리지만 몇 번 하다보면 익숙해질 것이라고 생각한다.