[엑셀]수식을 통한 조건부서식 지정하기

1. 작성이유

주식 조건을 보다보니 제가 보기 편하게 하기 위해서 조건부 서식을 지정할 때가 있었습니다. 그런데 규칙 관리에 대해 이해가 부족하다보니 수식을 통한 조건부서식 적용에 어려움이 있어서 이걸 정리하고 공유하고자 작성했습니다.

2. 조건부서식 수식으로 정하기 순서

조건부서식은 홈 → 조건부서식 → 규칙관리를 들어가면 아래와 같은 화면이 나오게 됩니다.

규칙관리 화면에서 새 규칙을 누르면 다양한 규칙을 지정할 수 있습니다. 이번에 알아볼 것은 수식을 사용하여 서식을 지정하는 방법을 익혀보기 위함이니 맨 아래에 있는 수식을 사용하여 서식을 지정할 셀 결정을 눌러줍니다.

여기서 빨간색 네모에는 참일 수식을 쓰고 파란색 네모에는 위의 수식이 참일 경우 적용될 서식을 정하면 됩니다.

 

3. 예제로 조건부서식 정하기

위와 같은 엑셀표가 있는데 여기서 현재가가 20만 원 이하이고 PER 12이하인 종목만 붉은 글자에 노란 배경으로 만들어보고자 합니다. 위에 적을 수식을 구성해보면 아래와 같습니다.

위의 수식을 보면 E2는 현재가이고 앞에 달러는 뒤의 값을 고정시키는 것입니다. E를 고정하고 숫자 2는 고정하지 않는다는 것으로 범위에서 종(위아래)으로 움직이는 것은 제한하지 않는다는 것입니다.

위의 그림과 같이 움직일텐데 E열에 있는 현재가를 200,000과 비교하게 됩니다. 
E2<200,000
E3<200,000
E4<200,000
E5<200,000
....
와 같이 비교되게 됩니다.

그럼 만약 반대로 E$2라고 수식을 쓰면 어떻게 될까요?
횡(가로 세로)으로 비교하고 종으로는 비교할 수 없어집니다. 저 범위에서 비교하면 아래와 같습니다.

E2<200000
F2<200000
G2<200000
H2<200000
...
와 같이 비교되게 됩니다.

이제 서식을 지정해보면 붉은색 글자와 노란색 배경을 선택했습니다. 그러면 아래와 같이 설정이 완료됩니다.

 

이제 확인을 누르면 규칙관리에서 아래와 같이 규칙이 뜨게 됩니다.

이제 적용대상을 설정해야 하는데 옆에 버튼을 누르게 되면 적용범위를 선택할 수 있습니다.

범위를 위와 같이 드래그해서 선택하면 됩니다. 그러면 서식 적용대상을 선택할 수 있고 아래와 같이 적용된 모습을 확인할 수 있습니다.

만약 서식 적용을 E에서 I까지만 하고 싶다면 어떻게 해야 할까요?

적용범위를 =$E$2:$I$6 로 위와 같이 지정하면 아래와 같이 지정된 범위만 서식이 변하는 것을 확인할 수 있습니다.

4. 총평

조건부서식으로 수식 설정할 때 항상 헷갈렸는데 이 포스팅을 보면서 다들 헷갈리셨을 때 도움을 받았으면 합니다.