[필기 풀이] 컴활2급 기출문제 2017년1회-28번 엑셀 수식 조건부서식

2017년도 1회 컴퓨터활용능력 2급 필기 기출문제 28번을 풀이한 것이다.

엑셀 조건부 서식에서 수식을 사용하여 배경색을 지정하는 문제이다. 바른 수식은?

28. 아래 워크시트와 같이 평점이 3.0 미만인 행 전체에 셀 배경색을 지정하고자 한다. 다음 중 이를 위해 조건부 서식 설정에서 사용할 수식으로 옳은 것은?

① =$D2<3 ② =$D$2<3

③ =D2<3 ④ =D$2<3

풀이 :

엑셀은 조건을 만족할 때만 서식이 적용되는 조건부 서식이란 기능이 있다. 이 기능에는 조건을 적용하는 여러가지 방법이 있다. 단순한 선택으로 조건 적용하는 방법과 수식을 이용하는 조건 설정이다. 문제는 수식 적용 방법을 묻는다. 작성하면서 직접 확인해 보자. 

엑셀 수식 사용 조건부 서식 작성

조건부 서식 - 임의 수식으로 서식 적용 방법을 단계별로 간략히 살펴본다.

[그림1] 엑셀 수식 조건부 서식 - 새 규칙 편집

서식 규칙 - 작성 준비

1단계: 조건부 서식이 적용될 범위를 선택한다. ( A2:D9 의 셀 범위 선택 )

2단계: 엑셀 리본 메뉴 > 홈 탭 > 조건부 서식 > 새 규칙 메뉴 실행.

서식 규칙 - 편집

3단계: 수식을 사용할 수 있는 마지막의 규칙 유형 선택.

4단계: 참과 거짓을 결과 반환하는 논리식으로 수식 작성. ( 열고정 혼합참조, =$D2<3 )

[그림2] 조건부 서식의 수식이 참 일때 서식

서식 규칙 - 셀 서식

5단계: 편집 단계에서 '서식' 버튼 클릭으로 셀 서식 창 열기.

6단계: 채우기 탭에서 배경색을 '노랑' 으로 선택 후 '확인'.

이상의 단계를 완료하면, A2:D9 범위의 셀 서식은 평점이 3 미만을 만족하는 행들이 모두 '노랑' 배경색으로 지정된다.

수식 조건부 서식 관련 링크 :

[엑셀 문답] 조건부 서식에서 수식 사용의 기본

[필기 풀이] 컴활2급 기출문제 2016년1회-34번 엑셀 조건부 서식

엑셀 조건부 서식 - 수식 분석 문제 풀이

이제, 왜 수식을 4단계처럼 설정하였는지 좀 더 자세히 알아보자.

1. 조건부 서식에서의 수식은 참과 거짓을 반환하여야 한다. 이 결과가 참이면 서식이 적용되고, 거짓이라면 서식은 무시되는 것이다. 따라서, 수식은 =A>B , =A<=B , =A=B 등과 같은 논리식으로 작성되어야 한다.

논리식 관련 링크 :

[실무 엑셀] 엑셀의 기본적인 연산자와 기호의 의미

2. 평점이 3 미만인지를 묻고 있기에, =평점<3 의 논리식 형태가 될 것이다.

3. 이제, 이 수식에서 '평점' 을 첫번째 평점 셀인 D2 가 대신한다. 여기서, 핵심은 참조 방법을 제대로 선택하는 것이다.

- 상대 참조인 D2 로 지정하면, 선택 범위인 A2:D9 의 모든 셀이 각각 비교된다.

- 절대 참조인 $D$2 로 지정하면, D2 의 단일 셀만이 비교된다.

- 혼함 참조인 $D2 로 지정하면, 선택 범위내 D열의 모든 셀이 비교된다. 상대 참조로 모든 행이 참조되지만, 절대 참조된 열은 벗어나지 못하는 것이다.

우리가 원하는 참조 방법은 무엇인가? 평점, 열고정 혼합참조가 최적이다.

참조 방법 관련 링크 :

[엑셀] 상대 참조, 절대 참조, 혼합 참조의 차이 배우기

참고1: 혼합 참조인 D$2 로 지정하면, 2행만의 각 셀들( A2:D2 )이 비교된다. 한 학생의 각 셀이 비교되는 것이다.

참고2: 수식 비교 범위는 선택 범위 내로 제한되며, 서식 적용 영역도 기본으로 선택 범위가 된다.

열방향 조건부 서식 링크 :

[필기 풀이] 컴활2급 기출문제 2006년1회-34번 엑셀 홀수 서식

4. 이런 이유로, =$D2<3 이 조건부 서식에서 수식으로 사용된다.

엑셀에서 수식을 사용하는 조건부 서식의 기본은 이렇게 참거짓을 반환하는 논리 수식과 원하는 비교값을 추출하는 참조 유형(상대,절대,혼합)이다. 이번 문제도 참조 유형의 바른 선택이 정답의 전부이다.

참고로, 선택 범위의 행방향(수직) 서식 적용을 위해서는 열고정 혼합참조( 예: $A1 )를, 열방향(수평) 서식 적용을 위해서는 행고정 혼합참조( 예: A$1 )가 일반적이다.

관련 기출 문제 :

[필기 풀이] 컴활2급 기출 2016년3회-25번 엑셀 조건부 서식 고유

정답 : 1번

이전 문제 :

[필기 풀이] 컴활2급 기출 2017년1회-27번 엑셀 사용자 지정 셀서식

마늘빵.