오늘은 지난번 엑셀 셀 서식 사용자지정 활용방법에 이어서 조건부서식에 대해 배워보겠습니다.
조건부 서식을 셀서식 기능에 더해 알아야 하는 이유는 셀 서식 기능의 한계 때문입니다.
어떤 한계가 있을까요?
1. 셀 서식 사용자지정 서식을 통해서는 최대 2개의 조건만을 적용할 수 있습니다.
2. 또한, 다양한 고급조건을 적용하는 데 제한이 있습니다.
따라서, Top 3 항목 강조하기나 평균 이상 혹은 상한/하한 값 강조하기 등 다양한 조건에 따른
서식을 지정할 때 조건부 서식 기능을 활용할 수 있습니다.
1. 엑셀 조건부 서식 기본 기능 사용하기
1) Top3항목 강조하기
먼저 아래의 부채비율에서 Top 3 항목을 조건부 서식을 통해 강조해 보겠습니다.
[홈] 탭 > [스타일] > [조건부서식] > [상위/하위 규칙] > 상위 10개 항목을 찾아 실행합니다.
그러면 기본 10개 항목까지를 모두 적용할 서식에 지정된 서식으로 보입니다.
Top 3 항목만을 강조하기 위해서는 빨간 네모박스의 숫자를 '3'으로 지정해주기만 하면 됩니다.
조건부 서식이 강력한 또 다른 이유는 지금과 같이 조건을 변경할 때마다 실제 적용됐을 때의 서식을
미리 보기 할 수 있다는 점입니다. (조건부 서식의 모든 기능에서 미리보기 가능한 것은 아닙니다.)
2) 평균 미만 값 강조하기
이번에는 평균 미만값을 강조해 보겠습니다.
아까와 동일하게 조건부서식을 적용할 범위를 선택한 뒤
[홈] 탭 > [조건부서식] > [상위/하위 규칙] > [평균미만]을 선택합니다.
[엑셀실무 Tip. 조건부서식 적용 유/무 확인이 필요할 때]
회사에서 다른 사람이 작성한 엑셀 보고서를 공유받을 때 해당 범위에 조건부서식이 적용된 것 같아
정확한 규칙을 확인하고 싶을 때가 있습니다. 그때 해당 범위에 어떤 조건부서식이 지정되었는지 확인할 수 있는 방법이 있습니다. 해당 범위를 선택한 후, [홈] 탭 > [조건부서식] > [규칙관리]를 통해 어떤 조건이 지정됐는지 확인 가능합니다.
2. 셀 참조로 조건부서식 적용하는 법
1) 보다 작음 조건부 서식
키가 표기된 열에서 조건 1에 입력된 키 보다 작은 셀들을 강조해 보겠습니다.
이번에도 [홈] 탭에서 [조건부서식] > [셀 강조규칙]의 [보다 작음] 버튼을 눌러줍니다.
아래와 같이 조건 입력 창이 나오고
값 또는 값이 지정된 셀을 참조할 수 있습니다.
키 145 미만인 경우를 강조하기 위해 조건 1이 입력된 셀을 범위로 지정해 줍니다.
그런 다음 적용할 서식 목록상자를 눌러 엑셀에서 기본적으로 제공되는 서식을 적용할 수도 있지만
이번에는 사용자 지정 서식을 활용해 보겠습니다.
사용자 지정 서식을 선택하여 아래와 같이 셀 서식 창이 띄워집니다.
글자색을 빨간색으로, 글꼴 스타일을 굵게를 적용해 보겠습니다.
이렇게 셀을 참조하여 조건부 서식을 지정했을 때의 장점은 무엇일까요?
바로 자동화가 가능하다는 점입니다.
조건으로 지정한 참조셀에 조건을 수정했을 때 바로바로 바뀐 조건에 맞게 서식이 변경되어 적용됩니다.
한번 살펴보겠습니다.
아까 조건에서 키 145 미만인 셀을 강조하였습니다.
이번에는 조건 입력된 셀의 값을 145 >> 135로 변경하여 키 135 미만인 셀을 강조해 보겠습니다.
조건부 서식 규칙관리 창을 별도로 열지 않아도 조건이 변경되었고 그에 맞게 서식이 반영된 것을 확인할 수 있습니다.
2) 두 개의 값 사이 셀 강조하기
다음은 저도 실무에서 자주 사용하고 있는 두 값의 사이 즉, 구간 내 값을 강조하는 서식을 지정하겠습니다.
다음과 같이 체질량지수 열에서 정상 범위인 20~24인 셀을 강조하는 조건을 지정해 보겠습니다.
똑같이 [홈] 탭 > [조건부서식] > [다음 값의 사이에 있음] 버튼을 눌러줍니다.
그런 다음 조건으로 지정할 20, 24가 입력된 셀을 각각 셀 참조로 입력하고
똑같이 서식 목록상자를 열어 사용자지정 서식을 눌러 줍니다. 위에서 했던 것과 마찬가지로 셀 서식 창이 열리고
그곳에서 글자색을 파란색으로 글꼴 스타일을 굵게 지정해 주고 확인으로 창을 닫아주면 됩니다.
3) 조건을 직접 지정하여 셀 강조하기
아래와 같이 이름이 입력된 열에서 성이 이 씨인 셀을 강조하려면 어떻게 해야 할까요?
조건부 서식 메뉴에 기본으로 제공되는 조건 중 텍스트 포함 버튼이 있기는 하지만
해당 기능을 사용하게 되면 성뿐만 아니라 이름에 이 씨가 포함되는 경우도 강조되는 문제가 생깁니다.
이 문제를 어떻게 해결할 수 있을까요?
바로 직접 조건문을 작성하여 해결할 수 있습니다.
조건부 서식에서 기본으로 제공되는 조건을 제외하고 직접 조건을 지정해 주기 위해서는
[새 규칙] 메뉴를 이용해야 합니다.
[새 규칙] > [수식을 사용하여 서식을 지정할 셀 결정] > [다음 수식이 참인 값의 서식 지정] 란에
직접 수식을 이용하여 조건을 지정하면 됩니다.
성이 이 씨일 때를 조건으로 지정하기 위해서 left 함수를 사용해 주겠습니다.
left(셀 값, N)
셀 값 : 텍스트 또는 숫자 데이터로 해당 데이터의 왼쪽부터 N번째 데이터를 가져오기 위한 참조 셀입니다.
N : 왼쪽부터 N번째 까지 가져오고 싶은 텍스트의 자릿수에 맞게 숫자로 지정해 줍니다.
성이 이 씨인 데이터를 찾고 있습니다. 즉, 이름의 왼쪽부터 첫째 자리 성만을 추출하면 되므로 이 경우 N=1이 됩니다.
위의 수식과 같이 left($B6,1) = $G$12로 조건을 지정해 주겠습니다.
엑셀의 셀 주소는 행과 열로 이루어져 있습니다.
행은 숫자로, 열을 알파벳으로 표기가 되어 있지요
여기서 $ 달러 표시는 절대참조를 위한 표기입니다. 절대참조의 개념은 행 또는 열 앞에 달러$ 표시를 추가하여
셀 참조 시 해당 $가 붙은 행 또는 열은 셀 참조 시 변경되지 않게 고정하겠다는 의미입니다.
우리가 조건부 서식을 적용할 셀의 범위는 이름이 입력된 이름 열입니다. 해당 열은 고정이 되어야 하고
조건부 서식이 이름 열의 첫 번째 행인 B6 이윤후가 입력된 셀부터 최원호(B7), 김연우(B8).... 차례로 내려가 방이수(B19) 셀까지 적용되어야 합니다.
따라서, left 함수 내 이름이 입력된 B6셀에서 열만 고정하기 위해 $B6으로 입력해 줍니다.
그런 다음 조건 '이'가 입력된 셀은 행/열 모두 고정되어야 하기 때문에 $G$12로 입력해 주는 것입니다.
조건을 작성하고 나서는 앞에서 했던 것과 같이 서식 버튼에서 원하는 서식을 지정하면 됩니다.
이번에는 서식 > 채우기 탭을 통해 조건을 만족하는 셀을 노란색으로 채워보겠습니다.
3. 조건부 서식 마우스 클릭 몇 번으로 데이트 시각화 하기
지금까지는 조건부 서식 새 규칙을 통해 직접 조건을 지정해 주는 것에 대해 알아봤습니다.
함수 활용 능력까지 있어야 해서 조금은 어렵게 느껴졌을 수 있을 것 같습니다.
이제는 클릭 몇 번으로 가독성을 높이고 시각화에 강점이 있는 조건부 서식 기능들을 알아보겠습니다.
1) 데이터 막대
데이터 막대 기능은 데이터의 값의 크기를 막대 길이로 표현해 주는 기능으로서 값들의 크기를 한눈에 비교하기 좋습니다.
적용하기 위해서는 조건부 서식을 지정할 범위를 선택합니다.
그런 다음 [홈] 탭 > [조건부서식] > [데이터 막대] 메뉴를 찾아들어가 줍니다.
원하는 데이터 막대의 색상 버튼을 눌러주기만 하면 끝입니다. 정말 간단하죠?
여기서 조금 더 보기 좋게 하기 위해서 데이터 막대의 최소, 최대 값을 직접 지정해 줄 수도 있습니다.
그렇게 하기 위해서 데이터 막대 조건부 서식이 지정된 데이터 범위를 지정하고 [조건부서식] > [규칙관리]로 들어갑니다.
그런 다음 [규칙 편집] 버튼을 눌러 규칙을 편집해 주면 됩니다.
규칙 편집 메뉴 > [규칙 설명 편집]에서 최솟값, 최댓값의 종류를 자동에서 숫자로, 값을 18, 35로 각각 지정해 줍니다.
그럼 왼쪽의 막대가 오른쪽의 막대와 같이 좀 더 차이를 직관적으로 볼 수 있게 반영되게 됩니다.
2)스파크 라인
스파크 라인은 범위 안의 값을 차트형식으로 표현해 주는 기능을 이야기합니다.
먼저, 스파크 라인을 적용할 범위를 지정해 줍니다.
그런 다음 [삽입] >
[삽입] 탭 > [스파크라인] > [열]을 선택해 줍니다.
스파크라인 만들기 창이 뜨고
원하는 데이터 선택란과 스파크라인을 배치할 위치를 지정해주어야 합니다.
스파크라인 배치 위치는 최초에 우리가 지정한 범위가 지정되어 있습니다.
맨 마지막 합계 열을 제외하고 1~6월까지의 데이터를 원하는 데이터로 선택해 주면 됩니다.
확인 버튼을 눌러 빠져나오면 아래와 같이 스파크 라인이 생성된 것을 확인할 수 있습니다.
스파크 라인의 장점은 이 값에서 최솟값, 최댓값 마지막값, 첫 번째 값을 강조할 수 있다는 점입니다.
스파크라인이 적용된 범위 내 셀을 선택하면 스파크라인 도구 창이 활성화됩니다.
[표시] 란에 높은 값에 체크하여 최댓값을 강조할 수 있고
나머지 값들에 대한 스파크라인 색 지정을 통해 보다 가독성을 높일 수 있습니다.
저는 실무에서 스파크 라인을 이러한 방식으로 사용하고 있습니다.
연속 생산 중인 제품에 대해 폭 방향으로 일정 크기에 시료를 재단하여 무게를 재서 관리하는 공정이 있습니다.
폭 방향으로 무게가 고르게 분포하는 것이 좋으나 실제로는 해당 공정에서 센터부 대비 사이드부가 꺼지게 됩니다.
센터부와 사이드부의 무게 차이를 최소화하는 것이 바람직하기 때문에 센터부와 사이드부의 무게 차이를 측정하고 차이가 크지 않도록 공정 조건을 수정하게 됩니다. 이때 폭방향으로 측정한 시료 무게들을 스파크 라인으로 표기하면 현재 프로파일 수준을 직관적으로 알 수 있고 공정 조건을 어떤 방향으로 수정할 지에 대해 직관적으로 판단 가이드를 제공합니다.
아래의 예시와 같이 오전 시업 시, 프로파일은 가운데 부의 무게가 솟은 형태의 프로파일이었습니다.
이를 인지하고 센터부 무게는 낮추고 사이드부 무게는 높이기 위해 공정 조건을 변동하였고 그 결과 오후부터는 무게 프로파일이 전반적으로 고르게 측정되는 것을 확인할 수 있습니다.
이렇게 오늘 정리한 조건부 서식 기능을 현업 실무에서 각자 직무에 맞게 응용해 보시기를 바랍니다.
감사합니다.
[함께 보면 좋은 엑셀 포스팅]
엑셀 기초, 셀 서식 기능으로 보고서 작성 효율 높이기
엑셀 표 기능 및 보고서 작성 시 가독성을 높이기 위한 규칙
엑셀 빠른 채우기를 통한 데이터 정리, 퇴근시간 단축하기
'실무엑셀기초' 카테고리의 다른 글
| 회사 엑셀실무 배우기, 엑셀 필터 기능 활용을 위한 단축키 콤보! (2) | 2023.09.04 |
|---|---|
| 엑셀 VLOOKUP 함수 실무 핵심 배우기, 생산 정보 추적하기 (0) | 2023.08.26 |
| 엑셀 기초 배우기, 셀 서식 기능 마스터로 보고서 작성 효율 높이기 (2) | 2023.07.29 |
| 엑셀 데이터 관리의 기본 규칙 (0) | 2023.07.27 |
| 엑셀 표, 보고서 작성 시, 깔끔하고 가독성을 높이기 위한 규칙 (0) | 2023.07.26 |
댓글