들어가기 전에
찬바람 불 때 시작했던 [Excel 입문] 강의도 이제 서서히 마무리를 준비할 때가 왔습니다. 앞으로 2강만 더 하면 “도무지 끝이 보이지 않던” 엑셀이라는 산의 정상에 이르게 됩니다. 이제 가쁜 호흡을 추스리고 주변도 둘러보시기 바랍니다(복습하시라는 얘깁니다.ㅎㅎ).
논리식과 조건 분기를 이해하면 엑셀 활용 역량이 급격히 확장됩니다. "조건부 서식" 기능도 그 중 하나입니다. 조건부 서식을 이용하면 특정한 조건을 충족하는 데이터에 대해 다양한 서식을 자동으로 지정할 수 있습니다.
조건부 서식은 지정한 조건에 부합하는 데이터에 다양한 서식을 설정함으로써 한눈에 식별할 수 있게 해주는 편리하고 강력한 기능입니다. 준비 되셨나요? 출발합시다.
조건부 서식 기본 사용 방법
'직원별 OA 평가 결과' 데이터에서 '평균'이 전체 평균보다 작은 값에 대해 특정한 서식을 지정해 보겠습니다.
(1) J20 셀에 전체 평균을 구합니다. 이 정도는 이제 더 이상 일도 아니죠? J20 셀에는 이런 수식이 들어 있습니다.
=AVERAGE(J4:J18)
(2) 조건부 서식을 적용할 영역(여기서는 J4:J18 셀)을 범위로 지정하고 [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [셀 강조 규칙] - [보다 작음]을 선택합니다.
(3) 대화상자에서 [다음 값보다 작은 셀의 서식 지정] 입력란을 클릭하고 값을 지정합니다. 직접 입력해도 되고 셀이 미리 입력된 값을 사용할 수도 있습니다. 여기서는 뒤의 방법을 사용합니다. (1)에서 작성한 수식이 들어 있는 J20 셀을 지정합니다. 셀을 클릭하면 자동으로 절대 주소 형태로 변경됩니다.
(4) 기본 서식을 그대로 사용하거나 [적용할 서식] 드롭다운을 클릭하고 서식을 선택합니다.
(5) [확인] 버튼을 클릭합니다. 전체 평균보다 작은 셀에 대해서는 한꺼번에 지정한 서식이 표시되므로 굳이 데이터를 들여다보지 않아도 한눈에 면모를 파악할 수 있습니다.
조건부 서식을 지우는 2가지 방법
조건부 서식은 일반적인 방법으로는 제거할 수 없습니다. 조건부 서식은 두 가지 방법으로 지울 수 있습니다.
선택한 영역의 조건부 서식 지우기
- 조건부 서식을 제거할 영역을 범위로 지정합니다.
- [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [규칙 지우기] - [선택한 셀의 규칙 지우기]를 선택합니다.
현재 시트 전체의 조건부 서식 지우기
현재 시트에 조건부 서식이 여러 개가 지정되어 있다면 한꺼번에 조건부 서식을 제거할 수 있습니다. [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [규칙 지우기] - [시트 전체에서 규칙 지우기]를 선택합니다. 범위를 별도로 지정할 필요도 없습니다.
상위/하위 데이터에 조건부 서식 지정하기
'직원별 OA 평가 결과' 데이터에서 상위 5명을 뽑아 시상을 하고자 합니다. 이런 경우에도 조건부 서식을 이용하면 쉽게 해결할 수 있습니다.
(1) 조건부 서식을 적용할 J4:J18 셀을 범위로 지정하고 [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [셀 강조 규칙] - [보다 작음]을 선택합니다. 만약 이전에 지정한 조건부 서식이 있다면 지우고 진행합니다.
(2) 대화상자에서 왼쪽 입력란에는 '5'를 입력합니다. 적용할 서식은 이미 만들어진 것을 사용할 수도 있지만 사용자가 직접 지정할 수도 있습니다. [사용자 지정 서식]을 선택합니다.
(3) [셀 서식] 대화상자에서 적절한 서식을 선택합니다. 여기서는 빨간 배경색에 흰 글씨를 선택하고 [확인] 버튼을 누릅니다.
(4) [상위 10개 항목] 대화상자가 다시 나타납니다. [확인]을 클릭하면 상위 5명에 대해 지정한 서식이 표시됩니다.
상위 n개 항목뿐만 아니라 하위 n개 항목, 상/하위 n%, 평균 초과/미만인 데이터에 대해서도 쉽게 조건부 서식을 지정할 수 있습니다. 한번씩 눌러가면서 확인해 보시기 바랍니다.
조건부 서식이 아니더라도 RANK나 LARGE 함수 등을 이용하여 원하는 결과를 얻을 수 있습니다만, 이런 경우라면 조건부 서식이 가장 간편해 보입니다.
데이터 막대, 색조, 아이콘 집합 사용
색이 지정된 데이터 막대나 그라데이션으로 서식을 지정할 수도 있습니다. 사용하는 방법은 비슷합니다. 조건부 서식을 지정할 영역을 범위로 지정하고 [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [데이터 막대]나 [색조], 또는 [아이콘 집합] 중 하나를 선택하고 적당한 스타일을 지정하면 됩니다. 예를 들어, [데이터 막대] 중에서 [파랑 데이터 막대]를 선택하면 다음과 같은 형태가 됩니다.
여러 가지 조건을 충족하는 데이터 일괄 강조하기
이번에는 두 가지 조건을 충족하는 데이터의 행 전체에 조건부 서식을 지정하여 강조해 보겠습니다. 예를 들어, 엑셀 점수가 90점 이상이면서 전체 평균보다 높을 경우 행 전체에 노란색으로 서식을 지정합니다.
'논리와 조건' 함수를 공부할 때, "조건이 2가지 이상인 경우 AND나 OR 함수를 사용한다"라고 배운 사실을 기억하시리라 생각합니다. "그런 걸 언제 가르쳐 줬어요?" 라고 생각되는 분은 [여기]를 눌러서 다시 정리하고 오세요. 이제 기억이 새록새록 나죠?
(1) 조건부 서식을 지정할 영역(B4:J18)을 범위로 지정합니다.
(2) [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [새 규칙]을 선택합니다.
(3) [새 서식 규칙] 대화상자에서 [다음 수식이 참인 경우 서식 지정] 란을 클릭하고 수식을 작성합니다. 두 가지 조건을 동시에 충족해야 하므로 AND 함수를 사용하고, 각 조건은 쉼표(,)로 구분합니다.
=and($G4>=90,$J4>=$J$20)
여기서 G4와 J4 셀은 혼합 주소 형태로 작성했습니다. 왜그럴까요? 엑셀이나 평균의 경우, 열은 G나 J로 고정이 되어야 하는 반면, 행은 4행, 5행, 6행,... 등 계속 바뀌어야 하기 때문입니다.
(4) 수식을 작성했으면 [서식] 버튼을 눌러서 서식을 지정합니다. 대화상자에서 [채우기] 탭을 클릭하고 노란색을 선택한 다음 [확인]을 누릅니다.
(5) [새 서식 규칙] 대화상자가 다시 나타납니다. [확인]을 클릭하면 두 가지 조건을 동시에 충족하는 데이터들에 대해 지정한 서식이 표시됩니다.
나가며
이로써 여러분은 지정한 조건에 자동으로 서식을 지정할 수 있는 강력한 도구를 얻게 되었습니다. 축하합니다. 엑셀의 기본 개념이나 필수 함수에 정통할수록 더욱 파워풀해진다는 사실을 깨닫는 계기가 되었기를 바랍니다.
'Excel' 카테고리의 다른 글
| TOP 3 자동 강조 Excel 차트 만드는 방법 (91) | 2024.04.23 |
|---|---|
| [Excel 입문] 17. 데이터 통합과 부분합—3차원 참조의 한계 극복 (7) | 2024.04.22 |
| Excel에서 대소문자를 변경하는 3가지 방법 (7) | 2024.04.11 |
| Excel 뺄셈 마스터가 되는 방법 (7) | 2024.04.09 |
| Excel에서 선행 0이 제거되지 않도록 하는 5가지 방법 (6) | 2024.04.08 |
