엑셀에서 "조건부 서식"을 얼마나 활용하시나요?

매크로를 잘 활용하는 사람이라면 "조건부 서식" 또한 잘 활용할 줄 아는 사람일 겁니다. 매크로의 파워풀한 기능 만큼 조건부 서식은 데이터 표현을 풍부하게 해 주며,  데이터 비교/분석 결과를 시각화 해 줌으로써 자료는 보는 사람으로 하여금 직관적으로 데이터 이해할 수 있도록 도와줍니다. 오늘은 이러한 장점을 가지고 있는 조건부 서식을 활용하는 방법에 대해 알아 보도록 하겠습니다.

 

1. 조건부 서식의 이해 & 기초

홈 메뉴에 "조건부 서식" 툴바를 클릭하면 (그림1)과 같이 기본적으로 제공되는 다양한 조건부 서식을 볼 수 있습니다. (그림1)의 활용 예는 "조건부 서식"을 이용하여 차액이 +인 경우와 -인 경우를 값에 따라 시각적으로 표현한 것 입니다. 차액이 +인 경우는 녹색이고 크기에 따라 막대 크기로 증가하며, - 금액인 경우는 빨간색 막대이고 크기에 따라 막대 크기도 좌측으로 증가 합니다. 조건부 서식을 사용하여 예상한 비용과 실제 사용 비용이 얼마나 차이가 났는지 시각적으로 표현해 줌으로써 데이터를 좀더 직관적이고 쉽게 이해할 수 있습니다.

(그림1) 조건부 서식

 

2. 조건부 서식을 활용한 재고 목록 관리 (예)

아래 재고 목록 관리 엑셀은 조건부 서식을 이용하여 재고 수량이 재주문 수준에 미달하면 재주문 항목을 자동 마킹 표기하며, 재고관리 중단된 항목이면 재고 데이터를 취소선 표기한 예 입니다. 데이터 만으로는 재고 상태를 직관적으로 인지하기 어렵지만 조건부 서식을 함께 사용하면 데이터 상태를 직관적이고 쉽게 인지할 수 있게 해줍니다. 이와 같이 데이터에 따라 자동으로 셀 서식이 변경되게 하려면 조건부 서식을 어떻게 설정하면 되는지 아래 예시를 보면서 알아보겠습니다.

조건부 서식 활용 예.xlsx

0.49MB

(그림1) 표 형식을 이용한 재고 목록 관리 예시

 

• 재고 목록 표에 적용된 3개의 조건부 서식 

표 데이터를 선택한 후 홈 메뉴의 "조건부 서식" 툴바를 선택, 팝업 메뉴에서 "규칙 관리" 메뉴를 클릭하면 아래 (그림3)과 같이 조건부 서식 규칙 관리자 화면이 오픈 됩니다. (그림3)은 재고 목록 표에 적용된 조건부 서식 목록으로 표에 3가지 "규칙"에 따라 "적용 대상" 범위에 "서식"이 적용된 것을 보여줍니다. 아래 3가지 조건부 서식에 대해 차례 대로 알아보도록 하겠습니다.

(그림3) 조건부 서식 규칙 관리자

 

• 첫번째 조건부 서식은 "중단된 항목인가요?"의 값이 "예"인 경우 해당 행 재고를 취소선 표시하기 입니다. 

(그림3)에서 첫번째 규칙을 더블클릭하면 아래 (그림4)와 같은 "서식 규칙 편집" 화면이 오픈됩니다. 첫번째 규칙은 "수식을 사용하여 서식을 지정할 셀 결정"이라는 규칙에서 보듯이 셀에 적용된 수식이("=$L4="예") 참인 경우 "적용 대상" Cell범위에 서식이 적용 됩니다.

$L4="예" 수식은 컬럼 "L"은 고정하고 행은 Row와 함께 변하도록 하여, 각 행에 "L"컬럼 값이 "예"인지 비교하는 수식 입니다. 수식의 결과 값이 참이면 해당 행의 "적용 대상" Cell 범위에 설정된 서식이 적용됩니다.

(그림4) 서식 규칙 편집 (1)

 

• 두번째 조건부 서식은 "재주문" 컬럼의 값이 1인 경우 해당 행 재고의 다탕색을 노란색으로 표시하기 입니다. 

(그림3)에서 두번째 규칙을 더블클릭하면 아래 (그림5)와 같은 "서식 규칙 편집" 화면이 오픈됩니다. 두번째 규칙도 "수식을 사용하여 서식을 지정할 셀 결정"하는 규칙으로 이번에는 $B4=1 수식을 적용 합니다. 이 수식도 컬럼 "B"은 고정하고 행은 Row와 함께 변하도록 하여, 각 행에 "B"컬럼 값이 1인지 비교하는 수식 입니다. 수식의 결과 값이 참이면 해당 행의 "적용 대상" Cell 범위에 설정된 서식이 적용됩니다.

"B" 컬럼읜 매크로가 적용된 컬럼으로 "재고 수량" <= "재주문 수준" 이고 "재주문할 항목을 강조 표시 할까요?"가 예이면 "B"컬럼의 값이 1이 되는 매크로 입니다. "B"컬럼 매크로 결과 값이 보이지 않는 이유 다음 세번째 조건부 서식 지정으로 표기되지 않았을 뿐 "B"컬럼도 매크로 결과 값을 가지고 있는 컬럼 입니다. 

(그림5) 서식 규칙 편집 (2)

 

• 세번째 조건부 서식은 "재주문" 컬럼의 값이 1보다 크거나 같은 경우 해당 "재주문" 항목을 체크 아이콘으로 표시하기 입니다. 

(그림3)에서 세번째 규칙을 더블클릭하면 아래 (그림6)와 같은 "서식 규칙 편집" 화면이 오픈됩니다. 세번째 규칙은 "셀 값을 기준으로 모든 셀의 서식 지정"하는 수식이 아닌 규칙에 따라 서식을 적용 합니다. "적용 대상"  Cell 범위("$B$4:$B$28")에서 셀 값이 규칙에 맞으면 해당 Cell을 값이 아닌 아이콘으로 표기하는 서식이 적용됩니다. 셀의 데이터 값이 아닌 아이콘만 표시 되기 원하는 경우 "아이콘만 표시"를 체크 합니다. 체크하지 않은 경우에는 값과 함께 아이콘이 표기 됩니다.

(그림6) 서식 규칙 편집 (3)

 

여기까지 조건부 서식을 이용하여 표 데이터를 쉽고 직관적으로 관리하는 방법을 예제를 가지고 알아 봤습니다.