003_컴활_엑셀 조건부 서식 설정
컴활 1급 시험에서 고급필터와 쌍을 이뤄 나오는 문제가 바로 조건부 서식입니다. 조건부 서식과 고급필터와 마찬가지로 함수조건과 서식을 합하여 출제되는 문제로 5점이 배정됩니다.
제가 컴활 1급 실기시험을 공부하면서, 가장 고생했던 부분이 조건부 서식이었던 생각이 납니다. 조건부서식에서 가장 중요한 부분이 반드시 시작셀에서 시작하여 범위를 지정하여야 하는 데, 저는 편리한 위치 주로 아래에서 시작하여 범위를 지정하다 보니, 범위가 이상하게 지정되어 한동안 고생했습니다. 조건부 서식은 항상 반드시 시작셀에 시작해야 한단는 것을 명심하면서 조건부 서식을 공부하면 도움이 많이 됩니다.
01. 조건부 서식이란
엑셀에서 사용하는 조건부 서식은 조건에 따라 막대, 색조 및 아이콘 집합을 사용하여 주요셀이나 예외적인 값을 강조하고 데이터를 시작적으로 표시하고 할 때 사용하는 기능입니다. 컴활 1급 시헝에서 출제되는 분야는 새규칙에 따라 조건과 서식을 설정하는 내용이 거의 대부분 출제되고 있기 때문에 여기에서는 새규칙을 중심으로 설명드리겠습니다.
02. 새규칙으로 조건부 서식 설정방법
01) 식을 별도 셀에서 작성한다. 필드의 가장 윗셀(제목줄제외)을 클릭하고, 행전체 서식을 지정하기 위해 F4키를 2번 눌러 열이름 앞에만 $를 붙여 열고정을 한다.
02) 수식이 제대로 되었는 지 True/False 값을 확인한 후 복사한다.
03) 반드시 시작셀에서 시작하여 조건부서식을 적용할 영역을 범위 지정한다. 제목표시줄은 제외,
(※ 시작셀에서 시작하지 않고 범위지정시 오류발생)
04) 홈 → 스타일 → 조건부서식 → 새규칙 메뉴 → 새 서식 규칙 → 규칙 유형 중 ‘수식을 사용하여 서식을 지정할 셀 결정“을 선택 → 다음 수식이 참이 값의 서식 지정 → 서식 → 확인으로 완료됩니다
05) 다음 수식이 참이 값의 서식 지정란에 복사한 수식을 ⑦ 서식지정란에 붙여넣기 합니다
06) 서식란을 클릭하여 글꼴스타일 ‘굵은 기울임꼴’, 글꼴색 ‘표준색-주황’을 선택합니다.
07) 설정이 완료되었으면 ‘확인’으로 완료됩니다
03. 조건부 서식 설정 사례
문제 01) [A3:K33] 영역에서 ‘취업률’이 ‘전체취업률’의 평균이상이고, 성별이 “여”인 행전체애 대하여 글꼴스타일 ‘굵은 기울임꼴’, 글꼴색 ‘표준색-주황’으로 적용하시오. (컴퓨터활용능력 1급 실기 2019년 -B)
★ 단, 규칙유형은 ‘수식을 사용하여 서식을 지정할 셀 결정’을 사용하고 ,한개의 규칙으로만 작성하시오.
★ AVERAGE, AND 함수 사용
→ =AND($K4>=AVERAGE($K$4:$K$33),$C4="여")
01) 식을 별도 셀인 L4셀에서 작성한다. 필드의 가장 윗셀(제목줄제외)을 클릭하고, 행전체 서식을 지정하기 위해 F4키를 2번 눌러 열이름 앞에만 $를 붙여 열고정을 한다.
① ‘취업률’이 ‘전체취업률’의 평균이상이고K4>=AVERAGE($K$4:$K$33)
② 성별이 ‘여’인 ,C4="여"
③ 행전체 에 대하여 F4키를 2번 눌러 열이름 앞에만 $를 붙여 열고정을 한다.
④ And로 두식 연결 =AND(K4>=AVERAGE($K$4:$K$33),C4="여")
02) 수식이 제대로 되었는 지 True/False 값을 확인한 후 복사한다.
03) 반드시 시작셀에서 시작하여 조건부서식을 적용할 영역을 범위 지정한다. 제목표시줄은 제외,
(※ 시작셀에서 시작하지 않고 범위지정시 오류발생)
04) 조건부 서식 지정
① 홈 → ② 스타일 → ③ 조건부서식을 클릭합니다
④ 새규칙 메뉴를 선택하게 되면 새서식 규칙 팝업화면이 보이게 됩니다
⑤ 새 서식 규칙에서 ⑥ 규칙 유형 중 ‘수식을 사용하여 서식을 지정할 셀 결정“을 선택합니다
⑦ 다음 수식이 참이 값의 서식 지정란에 복사한 수식을 ⑦ 서식지정란에 붙여넣기 합니다
⑨ 설정이 완료되었으면 ‘확인’으로 완료되며, 해당되는 조건에 서식이 적용됨을 볼 수 있습니다
※ 만약 시작셀을 맨아래인 K33에서 시작하여 A4를 지정한 경우 오류가 발생하게 됩니다. 시험 공부 독학 초기에 저는 이렇케 범위를 지정하여 오류를 찾는 데 상당한 시간을 소비한 적이 있습니다.
01) 범위 지정시 시작셀을 하단에서 시작시
02) 범위조회시 오류 발생 : 지정범위가 오류이기 때문에 조건서식이 당연 틀리게 지정됩니다
|
<컴활_실기1급_엑셀_03_조건부서식.XLS 파일을 첨부합니다, 기출문제를 중심으로 10개의 문제를 시트별로 정리하였으며, 실습을 하시고 싶은 분은 다운로드 저장한 후 실습하시면 됩니다. 실행하기 위해서는 비밀번호가 필요한 데, 비밀번호는 7212번입니다.
|
컴활_실기1급_엑셀_03_조건부서식.xlsx
< 실습예제 10선 DP, 사용된 함수와 정답>
문제 02) [B3:N31] 영역에서 6월의 값이 6월의 평균보다 작고, 7월의 값이 7월의 평균보다 작은 데이터의 전체 행에 대하여 글꼴 스타일은 ‘굵게’, 글꼴색은 ‘표준색-빨강“으로 적용하시오. (컴퓨터활용능력 1급 실기 2018년 -C)
★ 단, 규칙유형은 ‘수식을 사용하여 서식을 지정할 셀 결정’을 사용하고, 한 개의 규칙으로만 작성하시오.
★ AVERAGE, AND 함수 사용
→ =AND($H3<AVERAGE($H$3:$H$31),$I3<AVERAGE($I$3:$I$31))
문제 03) [E3:S31]영역에 대해서 해당 열 번호가 홀수이면서 [E3:S3]영역의 월이 홀인 열전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오. (컴퓨터활용능력 1급 실기 2017년 -A)
★ 단, 규칙유형은 ‘수식을 사용하여 서식을 지정할 셀 결정’을 사용하고, 한 개의 규칙으로만 작성하시오.
★ AND, COLUMN, ISODD, MONTH 함수 사용
→ =AND(ISODD(COLUMN(E$31)),ISODD(MONTH(E$3)))
문제 04) [B3:G43] 영역에서 세 번째 행마다 글꼴 스타일 ‘기울임꼴’, 채우기 색 ‘표준 색-노랑’을 저굥하시오. (컴퓨터활용능력 1급 실기 2017년 -B)
★ 단, 규칙유형은 ‘수식을 사용하여 서식을 지정할 셀 결정’을 사용하고, 한 개의 규칙으로만 작성하시오.
★ ROW, MOD 함수 사용
→ =MOD(ROW($B3)-2,3)=0
문제 05) 구분(C3)이 건강보조제이고, 판매수량이 상위 12위까지인 전체 행에 대하여 글꼴 스타일은 ‘굵은 기울임꼴’, 글꼴 색은 ‘표준 색 - 녹색’으로 적용하는 조건부 서식을 작성하시오. (컴퓨터활용능력 1급 실기 2016년 -A)
★ 단, 규칙유형은 ‘수식을 사용하여 서식을 지정할 셀 결정’을 사용하고, 한 개의 규칙으로만 작성하시오.
★ AND, LARGE 함수 사용
→ =AND($C3="건강보조제",$H3>=LARGE($H$3:$H$26,12)) *12번째 구해진 수량값보다 큰 경우 해당
문제 06) “20”과 ‘강의코드’의 4~5번째 글자를 연결한 값과 ‘수업시작일’의 연도가 같지 않은 행 전체행에 대하여 채우기 색은 ‘주황’으로 적용하는 조건부 서식을 작성하시오. (컴퓨터활용능력 1급 실기 2016년 -B)
★ 단, 한개의 규칙으로만 작성하시오. ( VALUE, MID, YEAR함수와 & 연산자 사용)
→ =VALUE("20" & MID($A3,4,2)) <>YEAR($B3)
문제 07) 시스템의 현재날짜의 연도에서 출판일을 뺀 값이 2년이상 4년이하인 전체 행에 대하여 글꼴 스타일은 ‘굵은 기울임꼴’, 글꼴 색은 ‘표준색 - 파랑’으로 적용하는 조건부 서식을 작성하시오. (컴퓨터활용능력 1급 실기 2015년 -A)
★ 단, 한개의 규칙으로만 작성하시오 ( AND, YEAR, TODAY 함수 이용)
→ =AND(YEAR(TODAY())-YEAR($D3)>=2,YEAR(TODAY())-YEAR($D3)<=4)
문제 08) ‘사용량’의 상위 네 번째까지와 하위 네 번째까지인 행 전체에 대하여 글꼴 스타일은 ‘굵게’, 글꼴 색은 ‘파랑’으로 적용하는 조건부 서식을 작성하시오. (컴퓨터활용능력 1급 실기 2014년 -C)
★ 단, 수식으로 작성하시오
→ =OR($D3>=LARGE($D$3:$D$21,4),$D3<=SMALL($D$3:$D$21,4))
문제09) ‘제품모델명’의 첫글자가 "D"또는 “E"이고 가격(F3)이 1,500,000원 이상인 행 전체에 대하여 글꼴 스타일은 ‘굵게’, 글꼴 색은 ‘빨강’으로 적용하는 조건부 서식을 작성하시오. (컴퓨터활용능력 1급 실기 2014년 -C)
★ 단, 수식으로 작성하시오
→ =AND(OR(LEFT($A3,1)="D",LEFT($A3,1)="E"),$F3>=1500000)
문제 10) ‘출시가’와 ‘구입가’중 최대값, 최소값이 포함된 전체 행에 대하여 채우기 색을 ‘노랑’으로 설정하시오.
★ 단, 규칙유형은 ‘수식을 사용하여 서식을 지정할 셀 결정’을 사용하고, 한개의 규칙으로만 작성하시오.
★ OR, MAX, MIN 함수 사용
→ =OR(MAX($H3:$I3)=MAX($H$3:$I$51), MIN($H3:$I3)=MIN($H$3:$I$51))