실습 데이터 다운로드 ↓

조건부서식-수식.xlsx

 

조건부 서식을 제대로 활용하려면 수식으로 조건을 만들 수 있어야 합니다.

조건부 서식은 IF함수의 논리를 기반으로 하고 있습니다.

조건에 일치하면 서식을 적용하고 조건이 일치하지 않으면 서식을 적용하지 않습니다.

평가 결과가 합격인 직원 데이터 행에 색상으로 강조 해보겠습니다.

IF함수를 사용하여 합격 이라고 문자로 결과를 표시해도 되지만 조건부 서식을 사용하여 색상으로 표시하면 한 눈에 합격 정보를 확인할 수 있는 장점이 있습니다.

데이터가 간단한 경우에는 두 결과의 차이가 크게 없을 수 있지만 복잡하고 데이터가 방대한 경우 조건부 서식이 시각적인 효과에 있어 최고라 할 수 있습니다.

 

 

 

평균 점수가 70점 이상인 데이터에 서식을 적용해보겠습니다.

서식을 적용할 범위에 블록을 지정합니다.

조건은 평균점수이지만 서식을 적용 할 범위는 행 전체기 때문에 성명 범위를 포함해서 블록을 지정합니다.

 

 

[]-[조건부 서식]-[새 규칙]메뉴를 실행합니다.

 

규칙 유형 중에 [수식을 사용하여 서식을 지정할 셀 결정]을 선택합니다.

첫 번째 직원의 평균점수(C4)을 지정하고 F42번 눌러 $C4(혼합 참조)로 변경합니다.

평균점수 열이 조건 열에 해당되기 때문에 열은 고정시켜줍니다.

그런다음 >=70 이라고 수식을 마저 작성합니다.

수식을 다시 적어보면 =$C4>=70 이라고 입력합니다.
조건 작성이 끝나면 [서식]버튼을 누릅니다.

 

[채우기]탭을 선택하고 셀의 배경색을 선택하고 확인 버튼을 누릅니다.

 

 

색상으로 구분하니 조건에 일치하는 데이터가 한 눈에 보입니다.

 

보충설명을 하겠습니다.

조건을 비교할 범위는 평균점수 범위(C4:C13)입니다.

평균 점수 범위가 고정된 상태에서 첫 번째 평균점수를 조건과 비교한 후 조건에 일치하면 색상을 그 행 전체에 적용하고 다음 행에 해당하는 두 번째 평균 점수를 조건과 비교하게 됩니다.

이런 방법으로 마지막 평균점수까지 비교합니다.

이 내용을 식으로 표현하면 $C4>=70이 되는 겁니다.

, 열은 고정되고 행만 변경되는 혼합 참조를 사용합니다.

 

 

 

함수식을 사용하여 조건을 작성해보겠습니다~

 

평균점수가 50점 이상이면서 과제를 제출한 경우가 수료에 해당됩니다.

수료 여부를 IF함수 결과처럼 문자로 표시하는 것이 아니라 해당 데이터 행 전체에 색상으로 표시를 해보겠습니다.

서식을 적용할 데이터 범위에 블록을 지정합니다.

 

 

[]-[조건부 서식]-[새 규칙]메뉴를 실행합니다.

규칙 유형을 [수식을 사용하여 서식을 지정할 셀 결정]을 선택합니다.

 

조건이 2개인 경우 2개의 조건이 모두 일치하는 경우에 색상으로 적용할 것인지, 2개의 조건 중에 하나라도 일치하면 색상을 적용할지를 판단하는 AND 또는 OR함수를 사용해야 합니다.

여기에서는 2개 조건 모두가 일치하는 경우에 색상을 적용할거기 때문에 AND함수를 사용합니다.
조건식은 =AND($C4>=50,$D4="제출") 입력하고 [서식]버튼을 누릅니다.

$C4$D4는 혼합 참조를 합니다.

 

셀 배경색을 적용하기 위해 [채우기]탭에서 색상을 선택하고 [확인]버튼을 누릅니다.

수식과 함수를 잘 다룰 줄 안다면 조건부 서식 기능과 조합하여 다양한 활용이 가능합니다.

 

 

 

수식과 함수를 잘 다룰줄 안다면 조건부 서식 기능과 조합하여 다양한 활용이 가능합니다.
다른 함수를 조건부 서식에 적용하여 활용하는 방법을 다음번 강좌에서 소개드리겠습니다.

동영상 강좌도 준비했습니다.

도움이 되시길 바랍니다.

짤막한 강좌 한정희 강사였습니다.

 

 

  1. 오종철 2018.11.21 11:09

    선생님 강의 유용하게 잘 보고 있습니다 ^^ 혹시 모의고사 문제 풀이는 없나요?

  2. 신입사원 2018.12.23 21:19

    신입사원인데 선생님 강좌가 정말 많이 도움되었습니다. 이런 강좌 만들어 주셔서 정말 감사합니다 ㅠㅠㅠ

  3. 34234234 2019.02.23 12:11

    님 좀 짱인듯 굳

  4. 홀이봉방공 2019.03.11 14:09

    질문있습니다!
    조건부서식에서 수식 규칙 편집에서

    =and($C4>=50, $D4="제출" 로하면 서식이 적용 되는데

    =IF(AND($C4>=50, $D4="제출"),"","")처럼 if 함수를 하면 서식이 적용되지 않습니다.

    if를 활용한 조건부 서식을 쓰고 싶다면 어떻게 해야할까요?

  5. 카멜리아 2019.04.12 10:33

    안녕하세요 선생님,
    올려주신 강의 유용하게 잘 듣고 있습니다. 감사합니다.
    혹시 여기 서식에서 혼합참조가 아닌 c4로 셀지정해도 결과 같게 나오는데, 혼합참조를 하는 이유가 있나요?

  6. 엑셀마스터 2019.05.01 16:38

    강의 잘 보고 있습니다ㅎㅎ 감사합니다.

    마지막 예시 중, '=AND($C4>=50, $D4="제출")'에서 혼합참조를 하지 않고 '=AND(C4>=50, D4="제출")'로 해도 가능하다고 생각했는데, 다른 결과가 나옵니다. 이 예시에서 드래그를 통한 '열'의 이동이 없어서 혼합참조가 필요 없다고 생각했는데, 왜 이런 결과가 나오는 걸까요?

  7. 대박! 선생님 화이팅!!

+ Recent posts