엑셀 중급 09 조건부 서식 수식 응용

안녕하세요 NYGD입니다.

오늘은 조건 서식 응용 편을 해볼까 합니다.

지난 시간에 봤었던 조건부 서식 기본 기능만 사용하셔도 무방합니다.

그렇게만 쓰셔도 데이터들은 한눈에 확 보실 수 있기 때문입니다.

 

하지만 그 기본 기능만으로는 조건부 서식을 제대로 쓴다는 느낌이 없죠....

오늘은 조건부 서식 중에서 수식을 사용해서 조건부 서식을 사용하는 법을 

알아보도록 합시다.

 

이게 무슨 말인고 하니....

조건부 서식 기능에 우리가 쓰는 함수를 혼합시켜서 사용한다는 의미입니다.

그러면 우리가 다양한 함수를 써서 셀들의 값들을 비교하거나

특정한 값이 있을 때 그 값에 색을 칠해

시각적으로 보기 좋게 표현을 할 수 있죠

 

기본 사용방법

먼저 내가 적용시키고 싶은 영역을 드래그해서 영역 선택을 해 줍니다.

그리고 Home 탭에 있는 조건부 서식을 클릭하신 후

새 규칙을 눌러주시면 됩니다.

조건부 서식 수식 사용법

그러면 아래와 같은 서식 규칙 편집이라는 창이 하나 뜰 텐데

여기서 맨 밑에 있는 

수식을 사용하여 서식을 지정할 셀 결정을 누르신 후

다음 수식이 참인 값의 서식 지정 부분에 

내가 원하는 함수 또는 논리 연산자 (AND, OR)과 비교 연산자 (=,>, <,=<,=>)를 사용해 주시면 되겠습니다.

조건부 서식 수식 편집 창

내가 원하는 서식을 적은 후 

아래 서식 (포맷)으로 클릭하셔서

그 서식을 만족할 경우 내가 원하는 색으로, 내가 원하는 바탕색으로 설정하신 후

확인을 누르시면 기본적으로 끝이 나게 됩니다.

 

반응형

 

실제로 사용하는 법은

제가 실제로 자주 했었던 일을 예제로 한번 들어 보겠습니다.

 

제가 했던 일은 온라인 어카운트 관리였는데 

그중에 했던 일중에 하나가

우리가 가지고 있는 재고와 1달 세일을 비교해서 

재고를 채우기 위해 지금 리오더를 넣어야 하는가? 하는 일을 했습니다.

아래 사진을 먼저 한번 보시죠

조건부 서식 실제 예제

 

위 사진 차트 설명

저는 제가 판매하는 상품들의 1달 판매 개수를 쫙 뽑은 후

현재 우리가 가지고 있는 재고를 쫙 뽑아서 같은 엑셀 파일 한 시트에 집어넣었습니다.

 

그리고 나선 기본적으로 1달 판매 개수가 현 재고보다 많으면 다시 리오더를 하는 작업을 했습니다.

물론 함수를 써서 그냥 작업을 해도 상관은 없지만....

함수만 사용하면 눈에 확 보이지 않기 때문에.... 

이런 작업을 할 때 조건부 서식 수식을 자주 사용했습니다.

 

여기서 제가 할 작업은 조건부 서식 수식으로 들어가서

만약 각 각 상품들의 1달 판매 개수가 > 현 재고 이면 푸른색으로 바탕색을 칠해보겠습니다.

 

먼저 아래와 같이 적용하고 싶은 영역을 선택 후 (열 머리글은 선택하지 마시고 데이터 부분만!!!!)

새 규칙을 만들어 줍니다.

새 규칙 만들기

그리고 새 서식 규칙 창에 들어가

 

조건 부분에 (항상 혼합 참조를 사용하셔야 합니다. 상대 참조를 사용할 경우 색이 이상하게 칠해집니다.)

=$B2>$C2을 쳐 줍니다.

(선택 영역의 첫 번째 행에서 해당하는 조건만 적으시면 됩니다.

우리는 2행부터 선택을 했기 때문에 $B1이 아닌 $B2값으로 비교를 합니다.)

 

위 조건의 뜻은 B2의 셀 값(한달판매 갯수) 이 C2의 셀값 (현 재고) 보다 크면

조건에 만족한다 라는 뜻입니다.

 

을 쳐준 후 

서식에 들어가 파란색 배경을 선택해준 후 OK를 눌러줍니다.

그러고 나면

아래와 같이 한 달 판매 개수가 현 재고보다 많은 애들은 

이렇게 파란색으로 칠이 되게 됩니다.

조건부 서식 적용 후

그리고 저는 이 파란색의 상품을 리오더를 하는 것이죠

실제 사용법은 의외로 간단합니다.

 

하지만 조건부 서식의 수식을 제대로 사용하기 위해선 

좀 더 디테일하게 알아야 할 것들이 몇 있습니다.

 

1. 수식에 셀을 사용할 때는 절대 상대 참조를 쓰지 않는다.

만약 상대 참조를 쓰면 내가 원하는 부분에 색칠이 안되고 이상하게 됩니다.

아래 사진을 보시죠

상대 참조 사용할 경우

위에 제가 실제로 사용했던 방법대로 인데 

수식 입력 란에 혼합 참조가 아니라 상대 참조로 할 경우

상대 참조 사용할 경우

위와 같이.. 이상한 영역에 색이 칠해지게 됩니다.

이유는 엑셀에서 참조라는 부분에 대한 이해가 필요합니다.

오늘은 사용법이 위주기 때문에 왜 그런가에 대한 건 다음에 기회가 되면 알아보도록 하죠

 

2. 선택 영역을 잘 선택해야 한다.

위 예제에선 저는 열 머리글을 선택하지 않았습니다.

영역 선택

그 이유는 즉슨 

만약 우리가 열 머리글도 포함해서 영역을 선택후 1번째 행부터 조건을 걸어 버리면

열 머릿글도 그 조건에 해당이 되어 버려 열 머릿글도 색이 칠해져 버리게 됩니다.

이렇게 조건부 서식을 이용해서 조건이 걸린 열 머리글에 다른 색을 입히려 해도 

입혀지지 않습니다.

 

예를 한번 보죠

열 머릿글 선택후 조건

이 예제에선 제가 열 머리글을 다 선택 후 

조건 부분에

첫 행에 해당하는 부분에서 비교 조건을 해야 하므로 $b1 <$c1으로 걸었습니다.

(뜻 : 재고가 판매보다 많을 때 파란색으로 색칠)

열 머릿글 선택후 조건

그러면 실제로는 B1의 값과 C1의 값은 숫자가 아니기 때문에 비교가 되지 않아야 하는데 

이상하게 조건에 부합하다고 하면서 색이 칠해졌습니다.

 

만약 혼자 데이터만 보고 작업을 하면 열 머리글을 선택하셔도 무방합니다만

만약 이런 데이터 정리된 것을 상사에게 보고를 해야 하는 상황이면

 

이렇게 색을 칠한 후 

열 머리글 부분에 Bold 처리도 하고 색도 다르게 입히고 해야 할 것인데

이 상태에선 

열 머릿글 색이 안바뀜

보시면 제가 열 머리글을 선택한 후 빨간색으로 바탕색을 바꿨지만... 

바꿔지지 않습니다.

 

3. 수식 조건을 쓸 때 적용될 영역의 첫 번째 행, 열, 셀만 작성한다

이게 무슨 말인고 하니

우리가 조건부 서식의 수식 부분에 조건을 넣을 때 

우리는 선택한 영역의 첫 번째 열, 행 부분의 셀에 관해 조건을 넣으면 된다는 말입니다.

6번 행부터 선택시

만약 우리가 선택 한 영역이 6행부터 시작하면

위 사진처럼 수식을 6번째 행에 있는 셀에 관해서 넣으면 된다는 말입니다.

=$B6 <$C6

 

이 실수는 위에 2번에서 말씀드렸던 선택 영역과 혼합해서 잘 일어납니다.

자주 일어 나는 실수

잘 못해서 선택 영역에 열 멀릿 글을 선택 후

실제 조건 값에선 우리는 2행부터 비교를 해야 하니

조건 값에 =$B2>$C2를 넣습니다.

그러면... 어떻게 될까요?

이상한데 색이 입혀짐

필요 없는데 색이 들어가고.. 필요 한데 색이 안 들어가고..

네... 아무 짝에 쓸모없게 되어 버리죠..

 

그래서 제가 알려드린 위 3가지는 꼭 이해하시고 명심하시길 바랄게요~

그럼 이만~