다른 글 보기

엑셀 조건부 서식 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. 조건부 서식이 적용된 결과이다.

 

 

 

 

 

 
 
 
 
Posted by 두여자 Y&S

댓글을 달아 주세요

  1. 학생2

    대박ㅠㅠ감사합니다.

    2013.11.18 14:35 [ ADDR : EDIT/ DEL : REPLY ]
  2. KARMA

    나이 많은 사람이 스승이 아니고 많이 알고있는 사람이 스승이죠
    오늘 제 인생의 또한분의 스승을 만나네요

    한참동안 고민하고 안풀리든 엑셀문제를 푸네요! 업무에 많은 도움이 되었습니다. _._)

    2014.02.11 20:19 [ ADDR : EDIT/ DEL : REPLY ]
  3. arepos

    질문 하나 더 드려봅니다..
    실습 2번문제에서 문제를 약간 바꿔서, 사원 이름에 '김' 이 들어가면 부서 셀에 노란색이 칠해지게 하는 경우...
    부서 C5:C11 지정 > 조건부서식 > 수식을 사용하여 서식을 지정할.. > 참인 값의 지정: $B5
    이 다음, 특정텍스트 "김" 포함을 어떻게 지정을 해줘야 하는 걸까요.
    이런 특정텍스트 포함시 셀서식변경 하는 방법은 불가능 한건지.. 답변 부탁 드리겠습니다. ㅠ

    2014.08.01 13:43 [ ADDR : EDIT/ DEL : REPLY ]
    • 이름의 첫자에 '김'이 들어가는 조건 =left($B5,1)="김"
      회사명의 끝에 '통상'이 들어가는 조건 =right($B5,2)="통상"
      이름의 중간에 '선'이 들어가는 조건 =mid($B5,2,1)="선"

      위의 방법처럼, left, right, mid 함수를 이용하면 왼쪽, 오른쪽, 특정 위치의 글자가 포함된 조건을 만들 수 있습니다.

      하지만, 말씀하신 '김'이 이름에 포함된..이라는 조건을 사용하려면 FIND, IFERROR 함수를 사용하면 됩니다.
      =IFERROR(FIND("김",$B5),"없음")<>"없음"

      find 함수는 B5셀에서 '김'이라는 문자를 찾아서 있으면 시작 위치를 숫자로 알려주고, 없으면 value 에러가 표시됩니다.
      iferror 함수는 find 함수 결과가 에러가 아니면 그대로 값을 표시하고 에러라면 두 번째 인수값(여기에서는 '없음')을 표시합니다.
      즉, '김'이라는 문자가 없으면 에러가 나서 함수값이 '없음'이 됩니다.
      결과적으로 이 수식의 뜻은 iferror 함수값이 '없음'이 아니라면(<>) 즉, 찾는 값이 있다면...이라는 조건이 됩니다.

      애초에 엑세스처럼 엑셀도 수식에 '*'을 사용할 수 있다면 간단하긴 할텐데 그게 아니라 함수로 만드려니 복잡하긴 하네요.
      더 쉬운 방법이 있을지도 모르니 함 찾아보세요^^

      2014.08.01 15:51 신고 [ ADDR : EDIT/ DEL ]
  4. molecole

    책만 보고 독학 중인데 ㅠㅠ 설명이 없는 부분이 너무 많아서 낑낑대던 차에 너무 감사한 자료에요 ㅠ.ㅠ.ㅠ
    잘 보고 갑니다, 감사합니다 !!!!!

    저도 질문 하나만 드려도 될지 모르겠어요ㅠ.ㅠ
    조건부 서식을 설정하고 다 맞게 했는데도 결과물에 ####이 잔뜩 채워지는 칸이 있는데 왜 그런걸까요?ㅠㅠㅠ?
    뭐가 잘못된 건지 감도 안 잡혀요

    2015.01.08 00:25 [ ADDR : EDIT/ DEL : REPLY ]
    • 숫자가 너무 길어 넘치는 경우 흔히 볼 수 있는 에러 표시입니다.
      조건부 서식에 글자를 굵게 한다든지, 크게 할 경우 문자는 상관없지만 숫자는 열너비를 초과하는 경우가 많은데 그건 열너비를 넓혀주면 되니까 놀라지 마세요^^

      2015.01.09 12:35 신고 [ ADDR : EDIT/ DEL ]