엑셀 조건부서식 기초부터 응용까지 완벽정리! :: 기초입문 9강
엑셀 조건부서식 기초 강의 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초입문] 엑셀 조건부서식 완벽 정리예제파일
엑셀 조건부서식은 말 그대로 '셀의 값이 특정 조건을 만족하는 경우에 적용하는 서식'을 뜻 합니다. 이전 '셀 서식의 모든것' 강의에서 설명을 드렸지만, 기본 셀 서식에서도 세미콜론(;)이나 대괄호([])를 사용하여 다소 제한적이지만 간단하게 조건부 서식을 적용할 수 있습니다.
셀 서식은 "양수 ; 음수 ; 0 ; 텍스트" 또는 "[조건] 서식 ; [조건] 서식" 형태로 조건별 서식을 지정할 수 있지만, 조건부서식에 비하면 많이 제한적입니다.
셀 서식을 사용한 조건별 서식과 다르게, 조건부서식은 하나의 셀에 여러개의 서식을 지정할 수 있으며 "상위 3개항목", "특정 텍스트 포함", "평균 미만" 등 다양한 조건에 대한 서식을 지정할 수도 있다는 장점이 있습니다.
엑셀 조건부서식 기초예제
조건부 서식을 사용하면 범위 내 상위 n 개 항목을 손쉽게 강조할 수 있습니다.
- 예제파일 첫번째시트의 왼쪽 표에서 합계를 제외한 부채비율 범위를 선택합니다.
예제파일 첫번째 시트에서 부채비율 범위를 선택합니다. - 홈 - 조건부서식 - 상위/하위규칙에서 '상위 10개 항목'을 선택합니다.
조건부서식 - 상위/하위규칙 - 상위 10개 항목을 선택합니다. - "다음 상위 순위에 속하는 셀의 서식지정"에서 숫자 '10'을 '3'으로 변경합니다.
값을 10에서 3으로 변경합니다. - '확인'을 눌러 마무리하면, 상위 3개 항목에 조건부서식이 적용되었습니다.
상위 3개 항목에 조건부서식이 적용되었습니다.
범위 내 값의 산술평균을 계산하여 평균 미만인 값을 자동으로 강조 할 수도 있습니다.
- 예제파일 첫번째시트의 오른쪽 표에서 키(cm) 범위를 선택합니다.
예제파일 첫번째 시트에서 키 범위를 선택합니다. - 홈 - 조건부서식 - 상위하위규칙에서 '평균 미만'을 선택합니다.
조건부서식 - 상위/하위규칙 - 평균 미만을 선택합니다. - '확인'을 눌러 마무리하면, 평균 미만인 값에 조건부서식이 적용되었습니다.
평균 미만인 항목에 조건부 서식이 적용되었습니다.
다른 셀을 참조하여 실시간으로 값을 강조하는 엑셀 조건부서식
다른 셀을 참조하여 조건부서식을 적용하면 참조하는 셀의 값이 바뀔 때마다 바뀐 조건을 만족하는 셀의 서식이 실시간으로 갱신되도록 할 수도 있습니다. 예제파일 두번째 시트로 이동한 뒤, 다른 셀 주소를 참조하여 실시간으로 적용되는 조건부서식 4가지 예제를 하나씩 살펴보겠습니다.
- 키(cm) 범위를 선택한 뒤, 홈 - 조건부서식 - 셀 강조규칙에서 '보다 작음'을 선택합니다.
조건부서식 - 셀 강조규칙 - 보다 작음을 선택합니다. - '다음 값보다 작은 셀의 서식 지정'에서 값으로 $G$6 셀을 참조합니다.
조건의 값으로 G6 셀을 참조합니다. - 적용할 서식에서 '사용자 지정 서식..'을 선택합니다.
적용할 서식에서 사용자 지정 서식을 클릭합니다. - 글꼴 스타일을 굵게, 색상은 빨간색을 선택합니다.
글꼴 스타일을 굵은 빨간색 글씨로 변경합니다. - '확인'을 눌러 마무리하면 키가 145미만인 경우에 서식이 굵은 빨간색 글씨로 적용되었습니다.
키가 145 이하인 경우에 조건부서식이 적용 되었습니다.
- 체질량지수 범위를 선택한 뒤, 홈 - 조건부서식 - 셀 강조규칙에서 '다음 값의 사이에 있음'을 선택합니다.
조건부서식 - 셀 강조규칙 - 다음 값의 사이에 있음을 선택합니다. - 두 값으로 $G$9셀과 $H$9셀을 참조합니다.
각 값으로 G10셀과 H9 셀을 참조합니다. - 적용할 서식에서 '사용자 지정 서식..'을 선택합니다.
적용할 서식에서 사용자 지정 서식을 선택합니다. - 글꼴 스타일을 굵게, 색상은 파란색을 선택합니다.
글꼴 스타일을 굵은 파란색 글씨로 변경합니다. - '확인'을 눌러 마무리하면 체질량지수가 20과 24사이인 경우에 굵은 파란색 글씨로 서식이 적용됩니다.
체질량지수가 20과 24 사이인 경우에 조건부서식이 적용되었습니다.
- 이름 범위를 선택한 뒤, 홈 - 조건부서식 - 새규칙을 선택합니다.
조건부서식 - 새규칙을 선택합니다. - 새 서식 규칙에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. - 엑셀 화면 좌측상단의 이름상자를 확인하여, 현재 선택된 범위에서 활성화 된 셀 주소를 확인합니다.
선택 된 범위에서 활성화 된 셀 주소를 확인합니다. - 조건부서식의 수식을 아래와 같이 입력합니다.
=LEFT(활성화된셀주소,1) = $G$12
조건부서식으로 적용 될 수식을 입력합니다. - LEFT 함수에 입력된 셀주소에서, 숫자 앞의 $표시를 지워줍니다.
B6셀에서 숫자 앞의 달러($) 표시를 지워줍니다. - 서식을 클릭한 뒤, 채우기에서 배경색을 노란색으로 선택합니다.
서식으로 이동한 뒤, 채우기 색상을 노란색으로 변경합니다. - '확인'을 눌러 마무리하면 특정 성씨를 가진 학생의 이름이 노란색 배경으로 강조되었습니다.
특정 성씨를 가진 학생의 조건부서식이 적용되었습니다.
- 키(cm)와 몸무게(kg)범위를 선택합니다.
키와 몸무게 범위를 선택합니다. - 홈 - 조건부서식 - 새규칙으로 이동한 뒤, '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
수식을 사용하여 지정할 셀 결정을 선택합니다. - 엑셀 화면 좌측상단의 이름상자를 확인하여, 현재 선택된 범위에서 활성화 된 셀 주소를 확인합니다.
화면 좌측상단 이름상자에서 활성화 된 셀 주소를 확인합니다. - 조건부서식의 수식을 아래와 같이 입력합니다.
=AND('셀주소'<$G$15,'옆의 셀주소'<$H$15)
조건부서식으로 적용 될 수식을 입력합니다. - 셀주소와 옆의 셀주소에서 숫자 앞의 $표시를 지워줍니다.
C6셀, D6셀의 숫자 앞 달러($) 표시를 지워줍니다. - 서식을 클릭한 뒤, 채우기에서 배경색을 초록색으로 변경합니다.
서식으로 이동한 뒤, 채우기 색상을 초록색으로 변경합니다. - '확인'을 눌러 마무리하면 키와 몸무게 조건을 모두 만족할 경우의 서식이 초록색 배경으로 변경됩니다.
여러 조건을 만족하는 경우에 조건부서식이 적용되었습니다.
엑셀 데이터막대는 선택된 범위의 값을 '막대 형식 그래프'로 시각화하여 셀 안에 표시해주는 조건부서식인데요. 몇번의 클릭만으로 손쉽게 적용할 수 있는 아주 훌륭한 시각화 도구입니다.
- 세번째 시트로 이동한 뒤, 체질량지수 범위를 선택합니다.
체질량지수 범위를 선택합니다. - 홈 - 조건부서식 - 데이터막대에서 원하는 형식의 데이터 막대를 적용합니다.
조건부서식 - 데이터막대를 선택합니다. - 데이터막대 조건부서식 적용이 마무리 되었습니다.
선택한 범위에 데이터막대 적용이 완료되었습니다.
상황에 따라 데이터막대의 최소값과 최대값을 조절하여 데이터막대의 값을 더욱 정확하게 표시할 수도 있는데요. 방법은 아주 간단합니다.
- 데이터막대가 적용된 범위를 선택한 뒤, 홈 - 조건부서식 - 규칙관리로 이동합니다.
범위를 선택한 채로, 조건부서식 - 규칙관리에 이동합니다. - 데이터 막대를 선택한 뒤, 규칙편집을 클릭합니다.
데이터막대를 선택한 뒤, 규칙편집으로 이동합니다. - 최소값과 최대값을 모두 '숫자'로 변경한 뒤, 원하는 값으로 변경합니다.
최소값과 최대값을 숫자로 변경한 뒤, 값을 입력합니다. - '확인'을 눌러 마무리하면 데이터막대의 최소값, 최대값 설정이 완료됩니다.
데이터 막대의 최소값, 최대값 설정이 완료되었습니다.
엑셀 스파크라인은 시간대별로 나열된 값을 꺾은선 또는 막대그래프 형식으로 시각화하여 셀 안에 표시해주는 엑셀의 시각화 도구인데요. 정확히 비교하자면 조건부서식은 아니지만, 위에서 알아본 데이터막대와 같이 응용하면 보고서 작성 시 아주 훌륭하게 사용할 수 있는 기능 중 하나입니다.
- 예제파일의 네번째 시트로 이동한 뒤, 스파크라인을 출력할 범위(J6:J14)를 선택합니다.
스파크라인을 출력할 범위를 선택합니다. - 삽입 - 스파크라인에서 '열'을 선택합니다.
삽입 - 스파크라인을 선택합니다. - 스파크라인의 데이터로 입력 될 범위(C6:H14)를 선택합니다.
데이터로 입력될 범위를 선택합니다. - '확인'을 눌러 마무리하면 스파크라인이 삽입되었습니다.
스파크라인 삽입이 완료되었습니다. - 스파크라인은 값의 최대값, 최소값등을 자동으로 강조할 수 있는데요. 상단의 스파크라인 옵션으로 이동한 뒤, 표시에서 '높은 점'을 선택합니다.
스파크라인 옵션에서 높은값을 체크하여 활성화합니다. - 최대값의 색상이 빨간색으로 변경되었습니다.
최대값이 빨간색으로 강조되었습니다. - 스파크라인 옵션에서 '스파크라인 색'을 변경하면 스파크라인의 색상을 변경할 수 있습니다.
스파크라인 색을 변경하여 기본색상을 변경할 수 있습니다. - 스파크라인 옵션의 '표식 색'을 변경하면 각 최대값, 최소값 지점의 표시색상을 변경할 수 있습니다.
표식색을 선택하여 각 표식의 색상을 변경할 수 있습니다. - 스파크라인 옵션에서 '지우기'를 선택하면 선택된 범위에 적용된 스파크라인을 지울 수 있습니다.
스파크라인 - 지우기를 클릭하면 스파크라인이 삭제됩니다.
엑셀의 아이콘집합은 각 조건을 만족할 경우 출력할 아이콘을 지정하여 셀 안에 출력하는 기능이며, 최대 4개까지 조건을 지정할 수 있습니다. 아이콘집합에 대한 자세한 설명 및 실전 사용법은 이전에 올려드린 '아이콘집합 실전사용법' 강의에서 다뤄드렸으니 궁금하신 분은 아래 관련 링크를 참고해주세요.
이번 강의에서는 아이콘집합의 사용법만 간단하게 짚고 넘어가겠습니다.
- 예제파일 다섯번째 시트로 이동한 뒤, 체질량지수 범위를 선택합니다.
체질량지수 범위를 선택합니다. - 홈 - 조건부서식 - 아이콘집합 - 도형에서 아이콘이 4개인 신호등 모양의 아이콘집합을 선택합니다.
조건부서식 - 아이콘집합에서 4개의 조건이 있는 도형을 선택합니다. - 아이콘집합이 적용된 범위를 선택한 뒤, 조건부서식 - 규칙관리 - 규칙편집으로 이동합니다.
아이콘집합을 선택한 뒤, 규칙편집으로 이동합니다. - 규칙 설명 편집의 값을 아래 그림과 같이 변경합니다.
주의사항 : 종류를 모두 '숫자'로 선택한 뒤 값을 입력합니다.
종류를 모두 숫자로 변경합니다. 이후 값을 그림과 같이 설정합니다. - '확인'을 눌러 마무리하면 선택된 범위에 아이콘 집합이 적용되었습니다.
아이콘집합 적용이 완료되었습니다.
조건부서식은 셀의 값에 영향을 주지 않습니다. 또한 파일에 치명적인 영향을 미치는 것 또한 아니여서 사용시 크게 주의해야 할 점은 없습니다.
하지만 넓은 범위에 조건부서식을 적용 시 알아두면 좋은 점이 한가지 있는데요. 바로 '셀 참조방식을 적절히 사용하는 것'입니다.
조건부서식과 셀 참조방식을 활용하면, 이전 강의에서 설명드린 '버튼 클릭으로 표를 강조하는 방법'처럼 표에서 원하는 부분을 실시간으로 강조하는 보고서 양식을 만들 수도 있습니다.
이번 강의에서는 부서명을 선택했을 때, 선택된 부서의 모든 직원이 강조되는 표를 만들어 보겠습니다.
- 예제파일 여섯번째 시트로 이동한 뒤, B4셀을 시작으로 부서명~연봉까지 범위를 선택합니다.
B4 셀을 시작으로 부서명부터 연봉까지 범위를 선택합니다. - 홈 - 조건부서식 - 새규칙에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. - 화면 좌측의 이름상자를 확인하여, 선택된 범위에서 활성화 된 셀의 주소를 확인합니다.
(B4셀이 활성화 된 것을 확인할 수 있습니다.)
선택된 범위에서 활성화 된 셀을 확인합니다. - 조건부서식의 수식을 아래와 같이 입력합니다.
=$B4=$H$4
'$B4셀에서 $B가 고정되어 있으므로, C4:F4 범위는 $B4=$H$4일 경우 조건부서식이 동시에 적용됩니다.
조건부서식으로 적용 될 수식을 입력합니다. - 서식으로 이동한 뒤 채우기 색상을 옅은 노란색으로 변경합니다.
서식을 클릭한 뒤, 채우기색상을 옅은 노란색으로 변경합니다. - 확인을 눌러 마무리한 뒤, 부서명을 변경하면 표에서 해당 부서의 모든 직원이 실시간으로 강조됩니다.
부서를 선택하면 해당 부서의 직원이 강조됩니다.
- 예제파일 첫번째시트의 왼쪽 표에서 합계를 제외한 부채비율 범위를 선택합니다.
