수식으로 조건부서식 지정

엑셀의 조건부 서식은 다음과 미리 정의된 다양한 규칙이 있으므로 셀을 선택하고 원하는 규칙을 고르기만 하면 손쉽게 사용할 수 있습니다. 편리하게 사용할 수는 있지만 복잡한 규칙을 적용하기에는 한계가 있습니다.

이런 경우 수식을 사용하면 복잡하고 다양한 조건으로 조건부 서식을 지정할 수 있습니다.

 

 

 

하나의 조건을 만족하면 전체 행에 서식 지정

다음과 같은 판매 자료가 있을 때 대리점 유형이 '직영'이면 전체 행에 배경색을 지정해 보겠습니다.

① 제목 열을 제외하고 판매 데이터가 입력된 [B5:F17] 범위를 선택합니다.

 

리본 메뉴 [홈] 탭 → [스타일] 그룹 → ② [조건부 서식] → [새 규칙]을 선택하면

 

[새 서식 규칙] 대화상자가 열립니다.

③ [규칙 유형 선택] 목록에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택하고

④ '다음 수식이 참인 값의 서식 지정' 입력 란을 마우스로 클릭 → [D5] 셀 선택(선택시 =$D$5로 절대참조가 표시됨)하고 [F4] 키를 번갈아 눌러서 =$D5(열고정 혼합참조)가 표시되도록 함 → ="직영" 입력

=$D5="직영"  (D5, D6, D7... 셀의 값이 '직영'인 조건을 만족하면)

⑤ <서식> 버튼 클릭

 

[셀 서식] 대화상자에서 ⑥ [표시형식], [글꼴], [테두리], [채우기] 탭을 선택하여 글꼴, 배경색 등을 원하는 모양으로 선택 → ⑦ <확인> 버튼을 클릭하면

 

다음과 같이 대리점유형이 '직영'이면 배경색이 주황색으로 표시됩니다.

 

 

여러 조건을 만족하면 전체 행에 서식 지정

앞에서는 하나의 조건을 만족하면 서식을 지정했는데, 이번에는 '대리점유형'이 '직영'이고 판매금액이 50만원이상이면 전체 행에 배경색을 지정해 보겠습니다.

앞에서 다룬 방식과 모든 순서가 동일하고 다음과 같이 판매금액 50만원 이상 조건이 추가되었습니다. AND 함수를 이용한 수식입니다. AND 함수는 여러 조건을 만족하는지 판별하는 함수입니다.

수식을 다음과 같이 입력하고 서식을 지정합니다.

=AND($D5="직영",$F5>=500000)

 

다음과 같이 대리점유형이 '직영'이고 판매금액이 50만원 이상이면 배경색이 주황색으로 표시됩니다.

 

 

조건을 만족하면 열 단위에 서식 지정

다음과 같이 월별 매출 자료가 있을 때 월 매출액 합계가 4억 이상이면 해당 월에 배경색을 지정해 보겠습니다.

① 제목 열을 제외하고 매출 데이터가 입력된 [C5:H13] 범위를 선택합니다.

 

리본 메뉴 [홈] 탭 → [스타일] 그룹 → [조건부 서식] → [새 규칙]을 선택하면 [새 서식 규칙] 대화상자가 열립니다.

② [규칙 유형 선택] 목록에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택하고

③ '다음 수식이 참인 값의 서식 지정' 입력란을 마우스로 클릭 → [C13] 셀 선택(선택시 =$C$13으로 절대참조가 표시됨)하고 [F4] 키를 번갈아 눌러서 =C$13(행고정 혼합참조)이 표시되도록 함 → >=400000 입력

=C$13>=400000 (C13, D13, E13...... 셀의 값이 4억보다 큰 조건을 만족하면)

 

 

[셀 서식] 대화상자에서 ⑤ [표시형식], [글꼴], [테두리], [채우기] 탭을 선택하여 글꼴, 배경색 등을 원하는 모양으로 선택 → ⑥ <확인> 버튼을 클릭하면

 

다음과 같이 월 매출이 4억 이상인 2월, 5월, 6월 열의 배경색이 연두색으로 표시됩니다.

 

 

---

관련 글