조건부 서식에 대한 개념과 활용

조건부 서식에 대한 이해

엑셀의 조건부.. 기본적으로 서식만 설정할 경우는 누구나 쉽게 서식 지정이 가능하다.

그러나, 수식으로 직접 작성하는 조건부 서식의 경우, 왜 수식에 절대참조($)표시를 지정하여야 하고, 행전체라는 것의 의미가 무엇인지에 대한 원리를 아는 경우는 드문 듯하다.

수식을 사용하는 조건부서식의 경우에는 다음의 3가지 사항을 이해하는 것이 전제조건이다.

1. 조건부서식은 절대적으로 셀참조 방식에 영향을 받는다.

2. 조건부서식은 선택영역에 영향을 받는다.

3. 사용자는 조건부서식이 적용될 영역의 첫 번째 셀만 수식작성을 하면, 엑셀이 나머지 셀들에 대해 자동으로 수식을 참고하여 서식적용을 한다.

우선, 단일 행이나 열에 대한 수식 기반으로 조건부서식을 확인해 보면

 

조건부 서식 예제

현재 영역의 첫 번째 셀이 F4이고, 주소도 상대참조이기 때문에 나머지 셀도 자기자신을 기준으로 수식비교를 통해 서식을 결정한다.

 

조건부서식 중 상대참조 기반의 수식

만약, 서식이 적용될 범위를 넓게 지정해준다면..

 

상대참조 기반의 수식 적용범위 확대

 

적용범위가 확대된 결과

왜, 위의 그림처럼 서식 지정이 되어지는지 혼란스러울 것이다.

여기에서의 비밀은 셀참조 방식에 있다.

우선, 선택영역은 B4:H8까지 범위에 있으며, 중요한 부문은 우리가 지정한 첫 번째 셀이 어느 위치를 참조하고 참조방법이 무엇이냐이다. 수식이 F4>2000000형태이므로 이것은 분명 우리가 알고 있는 상대참조방식이다.

따라서, 선택영역의 첫 번째 셀(B4)은 우리가 지정한 수식에 사용된 셀을 오른쪽으로 4번째 위치에 있는 값을 가지고 비교하여 결과가 TRUE이면 서식을 적용하고 그렇지않으면 적용하지 않는다.(F4가 2,200천원이므로 수식이 참이된다)

다음 행의 셀부터는 엑셀에서 자동으로 판단하여 수행하는데, 첫 번째 셀의 수식참조가 상대참조 이였기 때문에 행기준 상대참조를 하여 오른쪽으로 4번째 위치에 있는 값을 비교하므로 결과가 TRUE가 되어 서식적용이 되어진다.(즉, F5셀이 2,800천원이므로 수식이 참이된다)

그 다음행부터 보자. 다음 행은 오른쪽 4번째 위치(F6)셀이 2,000천원이므로 2,000을 초과하지 않으므로(F4>2,000,000) 결과가 FALSE가 되어 서식적용이 되지 않는다.

그렇다면 선택범위의 두 번째 열의 첫 번째 행에 있는 C4셀의 참조는 어떨까? 역시 첫 번째 셀의 상대 참조를 그대로 학습하여 오른쪽으로 4번째 있는 셀(G4)의 값과 지정된 서식을 가지고 비교하게 되는 것이다.(G4셀의 값은 문자이다. 숫자와 문자를 비교하게 되면 엑셀이 수식결과를 무조건 TRUE로 인정하는 것 같다. 따라서, C4:C8까지의 범위는 모두 서식적용이됨을 알 수 있다.)

직위필드(D4:D8)를 보면 첫 번째 셀의 상대 참조를 역시 학습하여 오른쪽으로 4번째 있는 셀(H4)의 값과 지정된 서식을 가지고 비교하게 된다.(H4셀에는 값이 없다. 값이 없을 경우 엑셀은 무조건 FALSE로 인정한다. 따라서, D4:D8 범위는 모두 서식이 적용되지 않는다)

만약 수식의 형태가 절대참조라면? 모든 셀이 수식에 지정된 절대참조셀을 참조하여 비교하므로 모든 수식이 똑같은 형태의 서식이 적용되어 진다.

 

조건부서식의 절대참조 기반 수식 작성

그렇다면 혼합참조를 사용한 경우는 어떨까?

먼저 열고정 혼합참조 먼저 확인하자. 수식은 $F4>2000000을 사용하겠다.

 

절대참조기반 수식 작성 결과

먼저 열을 고정시킨 혼합참조의 경우는 해당열이 고정되면서 참조를 하므로 선택 영역의 모든 행(B4:H8)이 F4열만 참조하게 된다. 이렇게 하여 수식을 적용하면 행전체가 서식적용이되는 것을 알 수 있다.

 

조건부 서식의 열고정 혼합참조 기반 수식 작성

즉, 열고정 혼합참조를 하면 열부분이 고정되므로 위의 예에서는 B4:H4영역의 셀들이 모두 F열만 참조하게 된다. 또한 모든 행이 4행이므로 상대참조위치는 4번행으로 지정되게된다.

따라서, B4셀도 F4셀의 수식참조, C4셀도 F4셀의 수식참조, D4셀도, E4셀도, F4셀도, G4, H4셀도 F4셀의 수식을 참조하게 되므로 F4셀의 수식비교결과가 TRUE가 되면 모든 셀이 서식적용되어 행전체가 서식적용이 되는 것이다.

 

열고정 혼합참조 기반 수식 적용 결과

행을 고정시킨 혼합참조라면 ? 수식은 F$4>2000000을 사용한다.

 

조건부 서식의 행고정 혼합참조 기반 수식 작성

행을 고정시키게되면 무조건 셀들이 4행의 값을 보고 참조하게 되며, 열부분은 상대참조이기 때문에 오른쪽으로 4번째 값을 참조하게 된다.

예를 들어 B4:B8 까지는 F$4셀을 모두 참조하게 되는 것이다. 왜냐하면 4번행은 고정된 상태에서 오른쪽으로 네 번째 열은 F열이기 때문이다.

따라서 행을 고정한 조건부서식의 수식은 열 전체에 대하여 서식을 지정하게 된다.

 

행고정 혼합참조 수식 적용 결과

지금까지 막연히 조건부서식을 무조건 혼합참조를 지정하여 행전체에 서식적용을 시킨 사용자가 많을 것이다.

이제 원리를 알았으니, 행전체가 아니라 일부만 적용하는 것도 충분히 가능하도록 수식 변형도 해볼 수 있을 것이다.