Y&S 두 여자의 말랑말랑 생활공감

다른 글 보기

엑셀 조건부 서식 1 ('표시 형식'의 '사용자 지정'으로 작성) by Y

엑셀 조건부 서식 2 by Y

지난 번의 [홈]-[조건부 서식] 명령의 자동 조건부 서식에 이어 [새 규칙]에서 [수식을 사용하여 서식을 지정할 셀 결정]을 이용한 조건부 서식에 대해 알아본다.

수식을 사용하여 조건부 서식을 적용하는 순서

1. 조건부 서식을 지정할 셀 범위를 먼저 선택한다.

2. [홈]-[조건부 서식]-[새 규칙] 명령을 클릭한 후 '수식을 사용하여 서식을 지정할 셀 결정' 유형을 선택한다.

3. 수식 입력란에 IF 함수의 조건과 비슷하게 수식을 입력한다. 이 부분은 조건부 서식의 조건에 해당하는 부분이다.

4. 조건에 맞는 서식을 지정하기 위해 [서식] 버튼을 클릭하여 서식을 지정한다.

5. 모든 기능 지정이 완료되면 [확인] 버튼을 클릭하여 조건부 서식을 적용한다.

1번부터 4번까지의 과정 중에서 가장 어려워하는 부분이 2번 과정인 수식을 입력하는 부분이다.

특히나 IF 함수를 공부한 적이 없는 사람들은 수식을 입력하기 어려워할 것이다.

반면에 IF 함수를 잘 사용하는 사람이라면 2번 과정은 너무 쉬울 것이다. 왜냐하면 수식은 곧 IF 함수의 조건 부분과 참조만 빼고는 똑같기 때문이다.

수식을 이용하여 조건부 서식을 적용할 경우 수식을 입력하는 방법에 대해 알아본다.

수식 입력 방법

예1) 부서가 영업2부인 사원의 행 전체 데이터에 글꼴색 파랑, 굵게 기울임 서식을 적용하시오.

=$C5="영업2부"

예2) 5월 영업 실적이 50,000 이상인 사원의 이름, 부서 데이터에 셀 음영색 노랑을 적용하시오.

=$H5>=50000

예3) 5월 실적이 4월 실적보다 증가한 사원의 이름 데이터의 글꼴색을 빨강으로 적용하시오.

=$H5>$G5

예4) 1월~5월 실적이 모두 50,000 이상인 사원의 행 전체 데이터에 셀 음영색 노랑, 글꼴색 녹색을 적용하시오.

=AND($D5>=50000, $E5>=50000, $F5>=50000, $G5>=50000, $H5>=50000)

===> 모든 참조에서 열만 고정($)한 이유는 다른 열은 검사할 필요가 없으므로 고정한 것이고, 행은 고정 안하는 이유는 5, 6, 7, 8, 9, 10, 11 행까지 모두 검사해야 하기 때문이다. 즉, $C5는 C열은 고정(부서 필드 고정)하고, 행은 모든 사원을 검사해야 하므로 5 앞에는 $(고정 표시)가 없는 것이다. 이런 참조를 혼합참조라고 한다. 쉽게 작성하는 방법은 [C5] 셀을 클릭하여 선택하고 [F4] 키를 두 번 누르면 된다.

실습하기

수식에 대해서 어느 정도 이해가 됐다면 수식을 이용한 조건부 서식을 실습해보자.

문제 1) 부서가 영업2부인 사원의 행 전체 데이터에 글꼴색 파랑, 굵게 기울임 서식을 적용하시오.

1. 행 전체에 서식을 적용할 것이기 때문에 [B5:H11]까지 범위 선택을 한 후 [홈]-[조건부 서식]-[새 규칙] 명령을 클릭한다.

2. '수식을 사용하여 서식을 지정할 셀 결정'을 선택한다. 수식을 입력하기 위해 수식 입력란을 클릭한 후 조건에 해당하는 부서에서 첫 번째 데이터인 [C5] 셀을 클릭한다. '=C5'까지 자동으로 입력되면 열만 고정하기 위해 [F4] 키를 두 번 누른다. '=$C5'로 수식이 변경된다.

3. 나머지 수식을 입력하여 수식 '=$C5="영업2부"'를 완성시킨다. 수식의 의미는 '부서가 영업2부이면'이다. 그 후에 서식을 지정하기 위해 [서식] 버튼을 클릭한다.

4. [셀 서식] 대화 상자가 나타나면 문제에서 말한 두 가지 서식을 지정한다. 원하는 서식을 지정하면 된다. 참고로 셀 음영을 지정하기 위해서는 [채우기] 탭을 클릭하여 색을 지정하면 된다.

5. 다시 [새 서식 규칙] 대화 상자로 돌아오면 [확인] 버튼을 클릭한다.

6. 부서가 영업2부인 행에만 지정한 서식이 적용된 것을 볼 수 있다.

다른 문제를 통해 다른 유형의 경우도 실습해보자.

문제 2) 5월 영업 실적이 50,000 이상인 사원의 이름, 부서 데이터에 셀 음영색 노랑을 적용하시오.

1. 범위를 지정할 때 서식을 지정할 필드만 선택해야 한다. 여기에서는 이름과 부서만 선택하였다.

2. 수식과 서식을 지정한다.

3. 조건부 서식이 적용된 결과이다.

다른 글 보기

엑셀 조건부 서식 1 ('표시 형식'의 '사용자 지정'으로 작성) by Y

엑셀 조건부 서식 3 (수식을 이용한 조건부 서식) by Y

지난 번 사용자 지정 서식을 이용한 조건부 서식에 이어 [홈]-[조건부 서식] 명령을 이용한 조건부 서식에 대해 알아보자. [조건부 서식] 명령에는 자동으로 쉽게 지정할 수 있는 조건부 서식과 직접 조건을 지정하여 사용자가 원하는 대로 서식을 지정하는 조건부 서식이 있다.

 조건부 서식

①번은 2007에서 새롭게 추가된 기능으로 조건부 서식을 좀 더 쉽게 적용할 수 있게 자동화되어 있다.

②번은 사용자가 원하는 조건대로 서식을 지정할 수 있는 좀 더 고급 기능이라 할 수 있다.

여기에서는 ①번 기능에 대해 알아보고 실습해보겠다.

각 조건부 서식 설명

1. 셀 강조 규칙 - 선택한 범위 안에 있는 모든 셀을 검사하여 규칙에 맞는 셀에만 지정한 서식을 적용한다. 셀 우선 조건부 서식이다.

2. 상위/하위 규칙 - 선택한 범위에서 가장 큰 수 몇개(상위), 가장 작은 수 몇개(하위), 혹은 상위 몇%, 하위 몇%를 지정하여 서식을 지정하는 조건부 서식이다.

3. 데이터 막대 - 선택한 범위에 막대 서식이 적용되는데 막대의 길이가 자동으로 데이터의 크고 작음을 알아내어 표시하게 된다. [규칙 관리] 기능에서 적용되는 값을 사용자가 수정할 수 있다.

4. 색조 - 데이터 막대와 비슷하게 작용하는데 막대가 아닌 색으로 표시한다는 점이 다르다. 상단에 표시된 색이 대체적으로 큰 수를 표시하며, 아래로 내려갈수록 작은 수에 적용되는 색이다. [규칙 관리] 기능에서 적용되는 값을 사용자가 수정할 수 있다.

5. 아이콘 집합 - 데이터 막대, 색조 기능과 비슷하게 작용하는데, 기호가 표시된다는 점이 다르다. 왼쪽의 아이콘이 대체적으로 큰 수에 표시되며, 오른쪽으로 갈수록 작은 수에 표시되는 아이콘이다. [규칙 관리] 기능에서 적용되는 값을 사용자가 수정할 수 있다.

각 조건부 서식 실습하기

다른 글 보기

엑셀 조건부 서식 2 by Y

엑셀 조건부 서식 3 (수식을 이용한 조건부 서식) by Y

조건부 서식은 데이터 목록에서 원하는 부분만 다른 서식을 적용하는 것이다.

조건부 서식을 적용하는 방법

1. [셀서식]-[표시 형식]-[사용자 지정]에서 서식 코드를 이용하여 작성하는 방법

2. [홈]-[스타일]-[조건부 서식] 명령을 이용하는 방법

1번 방법보다는 2번 방법이 더 복잡한 조건을 적용할 수 있다. 여기에서는 1번 방법을 이용하여 간단하게 조건부 서식을 적용해본다.

[셀서식]-[표시 형식]-[사용자 지정]에서 서식 코드를 이용하여 작성하는 방법

1. 글자에 색상을 적용할 경우 대괄호([])안에 입력한다. 예) [노랑]

2. 조건을 입력할 경우에도 대괄호([])안에 입력한다. 예) [<10000]

3. 조건에 맞는 경우는 세미콜론(;) 앞의 서식을, 맞지 않을 경우는 세미콜론(;) 다음의 서식을 적용한다.

예) [빨강][>=15000]\#,##0;\#,##0

1. 조건부 서식을 적용할 범위를 지정한 후 [셀 서식]-[표시 형식] 창을 열기 위해 단추를 클릭한다.

2. [셀 서식]-[표시 형식] 대화 상자가 나타나면 [사용자 지정] 범주를 선택하고 '형식' 입력란의 내용을 모두 지운다.

3. '[빨강][>=15000]\#,##0;\#,##0' 서식 코드를 입력한다. \은 금액이라는 것을 표시하기 위해 붙이고, #,##0은 천단위 구분기호를 삽입하기 위해 입력한다.

4. 조건부 서식이 적용되어 15000 이상인 숫자에만 빨강 글자색이 적용된 것을 볼 수 있다.

또 다른 예이다. '총회비'를 범위 지정하고, '[빨강][>=35000]\#,##0;[파랑]\#,##0' 서식 코드를 입력한다.

조건부 서식이 적용되어 35000 이상은 빨강, 아닌 경우는 파랑 글자색이 적용된다.