기본작업3) 조건부서식

---

컴퓨터활용능력 1급 따기, 어렵지 않아요 :-)

 

1과목. 스프레드시트

---

이번 문제유형은 '조건부 서식'입니다.

서식을 적용하되 내가 지정한 조건에 해당하는 셀만 서식을 지정하라는 것입니다.

조건부서식은 자격증 시험뿐만 아니라 평소에 엑셀을 사용할때 사용하기에 아주 편리한 기능이니 잘 익혀두면 좋겠죠?

문제예시)

* 상공회의소 자격평가단 홈페이지에서 제공하는 연습문제입니다. *

문제를 요약해보겠습니다.

* 조건1. 미지급금액이 전체 미지급금액의 평균을 초과

* 조건2. 관계가 '피고용인'이 아님

(두가지 조건은 '~하고'로 AND(조건1, 조건2) 함수로 결합할 수 있음)

* 서식 지정할 셀 : 조건에 해당하는 행 전체

* 서식 : 굵은 글꼴, 빨강색 글꼴

일단 두가지 조건부터 하나의 식으로 만들어보겠습니다.

조건식을 작성할 때는 무조건 첫번째 데이터를 기준으로 작성하고 채우기 핸들(+)로 수식을 복사하시면 됩니다. (조건부서식은 식을 셀에 입력하는 것이 아니기에 채우기 핸들은 필요없지만 역시나 1번 데이터를 기준으로 합니다.)

	조건 1	조건 2
파악하기	미지급금액이 전체 미지급금액의 평균을 초과	관계가 '피고용인'이 아님
분석하기	미지급금액의 1번 레코드 = [K4]  미지급금액 전체 = [K4:K40]    -> 전체의 평균 = Average(K4:K40)  초과를 나타내는 연산자 [>]	관계의 1번 레코드 = [E4]  같지않음을 나타내는 연산자 [<>]
식 구성	K4 > Average(K4:K40)	E4 <> "피고용인"
결합하기	AND(K4 > Average(K4:K40) , E4 <> "피고용인")

짠. 완성입니다.

두번째 단계, [* 서식 지정할 셀 : 조건에 해당하는 행 전체] 에다가 수식을 넣고 서식을 설정해주면 되겠습니다.

제목행을 제외한 데이터 행 전체를 드래그 (or 맨 윗행인 4번 행만 드래그하셔도 괜찮습니다만 후속작업이 필요하기 때문에) 

[홈]-[스타일]-[조건부서식]-[새규칙]으로 들어갑니다. 

지문에서 규칙 유형을 '수식을 사용하여 서식을 지정할 셀 결정'을 쓰라고 하였습니다. 

맨 아래 규칙을 선택하면 수식 입력기가 나오고 그 곳에 아까 작성한 수식을 쓰면 되겠습니다.

여기서 주의할 점은 셀 주소에 달러표시($)가 여러군데 붙죠.

이것은 위치의 움직임을 자동으로 인식하는 엑셀의 특성 때문에 그렇습니다. 

우리는 수식을 딱 한번 작성하는 것처럼 보이지만, 앞서 드래그한 전체데이터! 그러니까 [B4]셀에서부터 [O40]셀까지 497개의 셀에 같은 수식을 한방에 넣는 작업입니다.

그래서 '미지급금액'이 있는 K열은 $기호를 넣어서 움직이지 않게 잠가버린 것이고요.

첫번째 레코드인 [B4 : O4] 셀은 [K4]를,

두번째 레코드인 [B5 : O5] 셀은 [K5]를 가지고 조건을 계산해야하니 숫자인 4행은 그대로 둔 것입니다.

'관계'를 나타내는 [E4]셀도 위와 마찬가지입니다.

다만 평균의 범위인 [K4 : K40]은 첫번째 레코드이건 마지막 레코드이건 평균을 계산하는 그 범위는 언제나 동일하기때문에 열과 행을 모두 $기호로 절대참조한 것입니다.   

이제 마지막으로 서식만 지정해주면 됩니다.

서식(F)... 버튼을 클릭하여 셀 서식을 열어주시고요.

글꼴 스타일과 색을 지정해주면 미리보기와 같이 빨갛고 굵은 텍스트로 서식이 지정되겠지요. 조건에 해당하는 행만요!

확인을 눌러서 조건부서식에서 벗어나주면 두가지 조건을 모두 만족하는 레코드의 행 전체에 서식이 적용된 것을 볼 수 있습니다.

시간여유가 있다면 서식이 적용된 셀들이 잘 적용이 된건지 간단하게 확인 작업을 거쳐주세요!

혹시나 관계가 '피고용인'인데도 굵은빨간 텍스트가 걸려있지는 않은지, 미지급금액의 평균은 3만9천원 정도 되는데 그 이하의 금액에 서식이 걸려있지 않은지?

조금만 신경써서 확인작업을 거치면 시험장에서의 실수를 대폭 줄일 수 있습니다.^^