이번 문제유형은 '조건부 서식'입니다.
서식을 적용하되 내가 지정한 조건에 해당하는 셀만 서식을 지정하라는 것입니다.
조건부서식은 자격증 시험뿐만 아니라 평소에 엑셀을 사용할때 사용하기에 아주 편리한 기능이니 잘 익혀두면 좋겠죠?
| 문제예시) |
* 상공회의소 자격평가단 홈페이지에서 제공하는 연습문제입니다. * |
문제를 요약해보겠습니다.
* 조건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천원 정도 되는데 그 이하의 금액에 서식이 걸려있지 않은지?
조금만 신경써서 확인작업을 거치면 시험장에서의 실수를 대폭 줄일 수 있습니다.^^
