Transcript 첨부화일 : 조건부서식 고급필터 (994253 Bytes)

조건부서식 & 고급필터
1. 조건부 서식
 조건부 서식은 조건을 지정하여 조건에 만족한다면 서식을 변경하고 만족
하지 않다면 서식을 변경하지 않는 기능이다.
 서식을 변경할 범위를 선택한다. 시험에서 행 전체라는 의미가 포함한다면
서식 변경 대상은 표의 전체 범위가 된다. 단 필드 이름들은 서식 변경 대
상에서 제외됨을 기억하자.
 위 그림을 참조하면 B3 셀이 다른 범위와 달리 흰 셀임을 알 수 있다.
즉, B3 셀에 조건을 적용하여 만족되면 서식이 변경되고 만족되지 않
으면 서식이 변경되지 않는다. 나머지 셀 범위는 변경이 되며 적용된다.
 [홈]-[조건부 서식]-[규칙 관리] 메뉴를 클릭하면 규칙 관리자 창이 나타난다.
서식 규칙 표시는 현재 선택한 영역에 적용
하므로 ‘현재 선택 영역’으로 선택한다.
 [새 규칙] 메뉴를 클릭하면 규칙을 작성할 창이 나타난다. 규칙을 수정하거
나 삭제할 경우 편집 및 삭제를 클릭하여 작업한다.
 조건부 서식은 수식으로 작성을 하므로
마지막 규칙 유형을 선택하여 아래 부분
에 조건을 수식으로 작성한다.
조건 작성 영역
 클릭하면 서식 창이 나타나고 조건에
만족하면 변경할 서식을 수정
 조건은 논리 수식으로 작성이 된다. 논리 수식이란 수식의 결과가
True/False로 나오는 수식이다.
 가입지점이 호남인 조건을 작성하여 보자. 조건은 흰 셀(B3)을 포함하는
레코드를 기준으로 작성이 된다.
F3 = “호남”
 첫 레코드의 가입지점은 F3 셀이다. 따라서, F3=“호남”으로 작성하면 만일
‘호남’이라면 True 결과가 되고 서식을 변경하게 된다. 또한 ‘호남’이 아니
라면 False 결과가 되고 서식은 변경되지 않는다.
 시험에서 조건이 둘 이상이 나올 수 있다. 그때는 AND와 OR 함수를 사용하
여 조건을 작성한다. 조건이 모두 만족하면 서식을 변경할 경우 AND 함수를,
조건 중 하나만 만족해도 서식을 변경할 경우 OR 함수를 사용하여 작성한다.
AND ( 논리수식1 , 논리수식2 , … )
OR ( 논리수식1 , 논리수식2 , … )
조건에 사용되는 논리수식은 모두
True/False 결과가 나오는 수식이다.
 보험종류가 연금으로 시작하는 조건을 추가하면 다음 조건으로 작성된다.
LEFT( E3 , 2 ) = “연금”
흰 셀을 포함하는 레코드의 보험종류는 E3
셀이고 LEFT 함수를 이용해 문자를 가져와
서 조건을 비교한다.
 E3셀에서 가져온 왼쪽 2글자가 “연금”이면 True, “연금”이 아니면 False 결과를
나타낸다.
= AND ( F3 = “호남” , LEFT( E3 , 2 ) = “연금” )
 작성한 두 논리수식이 모두 True 라면 서식이 변경된다.
 서식을 변경할 셀은 범위 내에서 행을 이동하며 서식을 변경한다. 또한
조건 셀도 같이 행을 이동하게 된다. 따라서 가입자명 별로 조건을 비교
하여 서식을 변경하게 된다.
 하지만 범위로 지정되어 있으면 열도 변경이 된다. 조건의 셀이 열이 변
경이 될 경우 다른 열에서 같은 조건을 비교하게 되어 그림과 같이 결과
가 다르게 나타난다.
= AND ( $F3 = “호남” , LEFT( $E3 , 2 ) = “연금” )
 따라서 조건 수식 셀의 열 부분에 절대참조 기호인 $ 기호를 입력해
열이 변경되지 않도록 고정한다.
 두 조건이 만족하는 2개의 레코드가 글자색이 “빨강”인 서식이 적용
되어 표시된다.
2. 고급필터
 조건이 만족하는 데이터를 필터하는 기능이다.
 [데이터]-[고급] 메뉴를 사용한다. 단, 메뉴를 사용하기 전에 조건을 먼저
작성하여야 한다.
 고급필터의 조건은 워크시트의 셀에 입력을 하여야 한다. 조건 입력 셀은
문제에서 제시한다.
 조건 입력은 필드와 조건을 다른 셀에 입력하여 작성한다. 그리고 2개 이
상의 조건은 AND와 OR 조건으로 작성하여야 한다.
필드의 높이는 조건에 상관없이 같은 높이로 작성
필드1
필드2
필드1
조건1
조건2
조건1
AND 조건일 경우 조건의 행
높이는 같은 높이에 작성
필드2
조건2
OR 조건일 경우 조건의 행
높이를 다른 높이로 작성
 조건부 서식에 사용한 조건을 B18 셀부터 입력하여 보자. 두 조건이 AND
조건이므로 다음 그림같이 작성하면 된다. (오른쪽은 OR 조건일 때의 그림)
 고급필터에서 조건 작성에 만능문자를 사용할 수 있다.
* : 글자수 제한없이 비교
? : 입력한 개수만큼 비교
A로 시작하는
A*
A로 끝나는
*A
A를 포함하는
*A*
 고급필터는 원하는 필드만 추출하여 결과를 나타낼 수 도 있다. 결과를 나
타낼 셀(B21)부터 원하는 필드의 이름만을 순서대로 작성한다.
가입자명부터 보장액까지 5개 필드만 결과로 표시
목록범위
조건범위
복사범위
↑
두 조건을 만족하는 고급필터 결과
3개의 범위를
차례대로 범위
지정한다.
 시험에서는 결과를 ‘다른 장소에 복사’를 한다. 그리고 필터를 적용할 목록
범위와 작성한 조건 범위, 그리고 결과를 나타낼 필드 범위인 복사 위치를
입력하고 확인을 클릭하면 결과가 나타난다.
조건식에 함수 사용하기
 고급필터 조건 작성시 함수가 사용될 수 있다. 이 때는 단순 조건으로 작성
하는 것이 아니라 True/False 결과가 나오는 논리 수식으로 작성하는데 기본
적인 개념은 앞에서 작성한 조건부 서식과 유사하다.
 보험종류가 “연금”으로 시작하는 데이터를 추출하여보자. 조건의 작성은 첫
번째 레코드를 기준으로 작성을 하고, 이 때 사용할 함수는 LEFT( 문자, 개
수 ) 를 사용하여 작성한다.
 =LEFT( E3, 2 ) : 함수의 결과를 생각해 보면 E3셀에 “연금보험”이 입력되어
있으므로 왼쪽에서 2글자 가져오면 “연금”이라는 결과가 나타난다.
보험종류
연금 ← =LEFT(E3,2) 의 결과
 여기까지만 작성할 경우 보험종류 필드에서 “연금”이라는 데이터를 검색하
여 추출한다. 하지만 실제 데이터에는 “연금”이라는 데이터는 없다.
 따라서 함수만 사용하는 것이 아니라 조건의 결과를 셀과 비교하여 참인지
아닌지를 비교하는 구조로 작성하여야 한다.
 따라서, 비교연산자와 함께 조건을 작성하여 논리 수식으로 작성을 한다.
 =LEFT( E3, 2 ) = “연금”
보험종류
TRUE

=LEFT(E3,2)=“연금” 의 결과, 만일 비교 셀이 “연금”이면 TRUE,
“연금”이 아니면 FALSE 가 나타나는데 비교 셀의 결과일 뿐 고급
필터에는 영향을 미치지 않는다. 즉, 논리 결과는 신경 쓰지 말 것.
 또한 조건에 함수가 사용되어 논리수식으로 작성할 경우 조건 작성에 사용
한 필드의 이름은 사용할 수 없다. 필드이름을 변경하여야 하는데 아무 글
자나 입력할 수 있지만(빈칸도 가능), 기존 데이터의 필드의 이름들은 사용
할 수 없다.
조건
TRUE
← 필드이름 변경. 기존 필드 이름을 제외한 모든 글자 가능
빈칸도 가능
 마지막으로 조건에 사용한 셀 주소의 열 부분에 절대참조($)를 붙여주면
된다. 이유는 조건부 서식과 동일.
 단, SUM, AVERAGE, MAX, MIN, RANK 등의 함수는 조건 비교 행이 변경될 때
비교할 함수 결과가 변경되면 안되는 경우도 있다. 이때는 행과 열에 모두 절대
참조가 입력되므로 절대참조의 의미를 반드시 이해하여 사용하자!