엑셀 배우기(32) - 조건부 서식(1) - 셀 강조 규칙

조건에 따라 색을 달리하거나, 글꼴 등을 변경해서 데이터에 대한 이해를 쉽게 하는 것이 조건부 서식입니다.

조건부서식(2022).xlsx

0.01MB

 

1. 위치 및 메뉴 구성

홈 탭의 스타일 그룹에 있습니다.

스타일 그룹만 보면 맨 왼쪽에 있습니다.

메뉴가 가로 실선으로 구분되어 있고, 메뉴별로 하위 메뉴가 있습니다.

정리하면 아래와 같습니다.

1단계 메뉴	2단계 메뉴
셀 강조 규칙	보다 큼, 보다 작음, 다음 값의 사이에 있음, 같음, 텍스트 포함, 발생 날짜, 중복값, 기타 규칙
상위/하위 규칙	상위 10개 항목, 상위 10%, 하위 10개 항목, 하위 10%, 평균 초과, 평균 미만, 기타 규칙
데이터 막대	그라데이션 채우기, 단색 채우기, 기타 규칙
색조	녹색 - 노랑 - 빨강 색조 등 3가색으로 구성된 것이 6개, 흰색 - 빵강 색조 등 2가지 색으로 구성된 것이 6개, 총 12가지, 기타 규칙
아이콘 집합	방향, 도형, 표시기, 추천, 기타 규칙
새 규칙
규칙 지우기	선택한 셀의 규칙 지우기, 시트 전체에서 규칙 지우기, 이 표에서 규칙 지우기, 이 피벗 테이블에서 규칙 지우기
규칙 관리

위 표에서 첫번째부터 다섯번째까지 메뉴의 끝에 '기타 규칙'이 있는데 이것을 누르면 여섯번째 새 규칙을 누르는 것과 같습니다.

2. 셀 강조 규칙

가. 보다 큼, 보다 작음, 다음 값의 사이에 있음, 같음

비교 연산자와 마찬가지로 같은지, 큰지, 작은지, 두 값 사이에 있는지를 비교해서 색상을 달리하는 것입니다.
E열에 증감율이 있는데, 5%이상만 색칠을 해보겠습니다.
① 범위를 선택하는 방법은 두 가지입니다. 먼저 E열을 클릭해서 E열 전체를 선택하거나, E2셀에서 Shift + Ctrl + End키를 눌러 E2셀에서 E열의 맨 아래셀까지 선택하는 것입니다. 첫번째 방법으로 E열을 클릭하고, 홈 탭에서 조건부 서식 - 셀 강조 규칙 - 보다 큼을 선택하면 -10%라고 조건이 입력되어 있고, 이 조건에 따라 오른쪽의 적용할 서식 '진한 빨강 텍스크가 있는 연한 빨강 채우기'가 되는데,

5%로 수정합니다. 확인 버튼을 누르지 안았는데도 5% 조건이 적용되어 서식이 바뀌었습니다. 확인 버튼을 눌러 확정합니다.

그런데 이상한 것은 5%든 -10%든 증감율(%)까지 연한 빨강으로 채워졌다는 것입니다. 아스키 코드표를 보면 문자는 65부터 시작하고 숫자는 48부터 시작해서 문자가 숫자보다 숫자가 커서 그렇습니다. 마이너스라도 마찬가지입니다. 따라서, 두번째 방법에 따라 E2셀부터 E열의 맨 아래셀까지 범위를 주는 두번째 방법이 좋습니다.

이번에는 범위를 E2셀에서 E열의 맨 아래줄까지 Shift + Ctrl + End키를 눌러 선택한 다음 E셀이 보이도록 마우스 휠을 위로 굴립니다. 그리고, 두 값 사이에 있는 것을 찾기 위해 조건부 서식 - 셀 강조 규칙 - 다음 값의 사이에 있음을 선택하면

두값이 모두 0%로 되어 있고, 0%도 선택이 안되어 있고, 기존 조건부 서식이 적용되고 있습니다 따라서, 왼쪽에는 -5%, 오른쪽에는 5%라고 주고, 적용할 서식을 콤보 상자 버튼을 눌러 다른 서식, 두번째를 선택합니다.

그러면 '보다 큼'의 조건과 '다음 값 사이에 있음'이라는 조건 2개가 모두 만족하는 경우는 노란색으로 바뀌고(예, E3셀), 첫번째 조건만 충족하는 경우는 빨간색(예, E2셀)으로 그대로 있습니다.

따라서, 규칙 지우기를 한 다음 하는 것이 좋습니다.
그리고, 범위를 B열과 C열을 선택한 후 특정 범위내 값을 찾아서 서식을 적용할 수도 있습니다.

나. 텍스트 포함

위에서 '같음'은 전체가 일치하는 것을 찾는데 비해, 텍스트 포함은 문자든 숫자든 입력한 값이 셀에 포함되어 있으면 조건을 충족합니다. 필터에 대한 설명 중 텍스트 필터 - '포함'과 동일합니다.
https://lsw3210.tistory.com/72

엑셀 배우기(6) - 필터, 필터 지우기, 다시 적용

필터 설명시 사용했던 데이터를 G열에서 R열에 붙여넣었습니다.

① 열 숨기기
설명이 용이하도록 A열을 마우스로 클릭한 후 F열까지 끈 다음, 마우스 오른쪽 버튼을 누른 후 숨기기를 하겠습니다.

A열부터 F열까지 숨겨지고 G열이 맨 왼쪽으로 됐습니다.

② 조건부 서식 적용범위 지정
L2셀에서 마우스를 클릭하고 Shift + Ctrl + End키를 눌러 L열의 맨 아랫줄까지 선택합니다.

③ 텍스트 포함 조건부 서식 적용
홈 탭의 조건부 서식을 이용하는 방법과 범위 지정시 오른쪽 아래에 표시되는 빠른 분석 도구를 이용하는 두 가지 방법이 있습니다.
㉮ 첫번째 방법으로 홈 탭에서 조건부 서식 - 셀 강조 규칙 - 텍스트 포함을 누릅니다.
㉯ 두번째 방법으로는 위 그림 오른쪽 아래를 보면 '빠른 분석 도구'가 표시되므로, 이것을 눌러서 표시되는 서식 메뉴 아래 텍스트를 누르는 것입니다.

위 화면에서 텍스트를 클릭하면 아래와 같이 텍스트 입력 칸에 '나주'가 표시되고 '나주'가 진한 빨강 텍스트가 있는 연한 빨강 채우기 서식이 적용돼서 표시됩니다.

위 화면에서 확인 버튼을 눌러 확정합니다.

O2셀에서 O26셀까지 선택한 후 텍스트 입력 칸에 000을 입력하면 000이 포함된 값에 서식이 적용됩니다. 확인 버튼을 눌러 확정합니다.

④ '같음'과 비교
'같음'은 전체가 일치하는 것을 조건으로 하므로, 비교할 값으로 부분 값인 '대한'을 입력하면 '대한통운'과 같지 않으므로 서식이 적용되지 않는데,

'대한통운'이라고 입력하면 '대한통운'에 서식이 적용됩니다.

숫자를 입력하는 경우에도 전체가 일치해야 합니다.

다. 발생 날짜

H열을 선택하고 셀 강조 규칙 - 발생 날짜를 선택하니, 어제부터 지난 달, 다음 달 등 최근 날짜만 선택이 가능합니다. 따라서 2013을 2022로 바꿔야 하겠습니다.

ⓛ 2013년을 2022년으로 바꾸기
㉮ 방법 1
신기하게 Ctrl + H(바꾸기) 키를 누르고, 찾을 내용에 2013, 바꿀 내용에 2022라고 입력하고 '모두 바꾸기'버튼을 누르니 연도가 2022로 바뀝니다.

Microsoft 365는 날짜에서 연도 바꾸기가 되는데,

확인 버튼 누르고, 닫기 버튼 누르면 2022년으로 되는데, Ctrl + Z키 또는 홈 탭에서 실행 취소 명령을 눌러 2013년으로 돌립니다.

㉯ 방법 2
위 방법 1이 안된다면 좀 복잡하게 연도를 바꿔야 합니다.
S2셀에 =date(2022,month(h2),day(h2)) 라고 입력하고 엔터키를 누릅니다. date함수는 인수로 연,월,일을 받는데, 2013을 2022로 바꾸기 위해 연도로 2022를 입력한 것이고, 월은 month함수를 이용해 h2셀에서 월을 구해서 가져오고, 일은 day 함수를 이용해 h2셀에서 일자를 가져오는 것입니다. 날짜가 2022-01-01로 잘 바뀌었습니다.

그러면, S2셀 오른쪽 아래 네모 모양의 채우기 핸들을 더블 클릭해서 S26셀까지 수식을 붙여넣습니다.

그리고, 복사 명령을 누른 다음 H2셀을 클릭하고 붙여넣기 아래 부분을 클릭한 후 값으로 붙여넣기를 클릭합니다.

선이 유지된 채로 날짜가 잘 붙여넣어졌습니다.

만약 붙여넣기 윗 부분 아이콘을 눌러 붙여넣기 명령을 실행했다면

아래와 같이 #REF! 에러가 발생했을 겁니다. 왜냐하면 S열에서 H열(H열은 S열에서 -11번째)을 참고하는데, H열에 붙여넣으면 H열(8번째)에서 참조할 열이 A열 왼쪽이여야 하므로 REF, 다시 말해 참조 에러가 발생하는 것입니다.

실행 취소, 지금은 붙여넣기 취소 명령을 눌러 붙여넣기를 취소해서 원래대로 돌립니다.

② 발생 날짜 선택
여러 가지 중에서 '지난 주'를 선택합니다. 오늘이 8.14이므로 8.9이 선택됐습니다. 확인 버튼을 눌러 확정합니다.

 

라. 중복값, 고유값

i2셀에서 i26셀까지 범위를 선택한 후 셀 강조 규칙에서 중복값을 선택하면 모든 중복값이 선택됩니다.

위 화면에서 중복을 누른 후 고유로 바꾸면

고유한 값 1750과 7436에 조건부 서식이 적용됩니다.

확인 버튼을 눌러 확정합니다.