■ 목차
1. 조건부 서식이란 무엇인가?
2. 조건부 서식의 구성 및 작동 원리
3. 지정한 셀과 조건은 독립적이다.
4. 조건 지정은 현재 셀을 기준으로, 절대참조, 상대참조를 잘 사용해야 한다.
5. 조건부 서식 다중 조건 적용 - And, Or 함수로 조건을 더욱 풍성하게
** 핵심 요약
- 이번 시간에는 초보자, 중급자 할 것 없이 많이들 사용하고 계시지만 많이들 헷갈려하는 조건부 서식에 대한 개념을 좀 잡아 볼가 합니다.
- 초보분들에게는 조금 어려운 얘기가 될 수도 있는데 편하게 한 번 읽어 보시고 나중에 조건부 서식을 사용하시다가 내 입맛에 맞게 잘 사용하고 싶다고 느끼실 때 다시 한번 되짚어 보시면 조금이나마 도움이 될 수 있을 듯합니다.
- 본 내용은 조건부 서식, 셀 서식, 수식 등에 대한 기본적인 이해가 있으면 더욱 도움이 되는 내용입니다.
1. 조건부 서식이란 무엇인가?
- 조건부 서식이라고 하면 셀 서식을 지정해주는 방식의 하나 입니다.
- 셀 서식을 확정적으로 지정해주는 것이 아니라 조건을 주고 조건을 만족하는 경우(True)에만 셀 서식을 지정하는 방식입니다.
- 그래서 우리는 입력된 값이 양수인 경우 혹은 평균보다 큰 경우 처럼 조건을 만족하는 경우에만 서식을 지정해서 실시간으로 입력값을 셀 서식으로 구분해서 볼 수가 있습니다.
- "셀 서식"에서도 "양수 ; 음수 ; 0 ; 텍스트" 순서로 서식을 지정해주는 것이 가능합니다. 물론 간단한 조건에 따라 셀 서식을 다르게 지정하는 것도 가능하게 되어 있습니다.
- 하지만 셀 서식의 경우는 너무 제한적(표시형식, 색상 정도 가능)으로 적용이 가능하기 때문에 알고 계신 분이나 실제로 사용하고 계신 분은 거의 보지 못한 듯합니다.
- 아래는 예제는 셀 서식으로 조건에 따라 서식을 지정해주는 예제 입니다.
- 반면에 "조건부 서식"은 "조건을 만족하는 경우 우리가 원하는 서식을 지정"하는 기능입니다.
- 그리고 여기에서 "조건"에 따라서 엄청나게 많은 응용이 가능하고 "서식"을 사용해서도 응용이 가능합니다.
- 조건으로 Max 함수를 사용하게되면 최대값의 셀을 바로 구분하여 표시를 할 수 있고
- 조건으로 Average 함수를 사용하게 되면 평균보다 큰 값들과 작은 값들을 색상 등으로 구분이 가능합니다.
- 조건으로 등호 기호 (>, <, <=, >=, = , <>)를 사용하게 되면 특정 숫자에 대한 비교값으로 큰 값, 작은 값 등으로 구분이 가능합니다.
- 빈셀인지 여부를 확인하고 ( "=not(isblank(A1))" ) 입력값이 있는 경우에는 셀 테두리를 넣어 표을 자동으로 확장해 줄 수 있습니다.
- Percentile 함수라고 백분순위를 구해주는 함수를 이용해서 상위 10%, 하위 30% 등을 구분할 수있습니다.
- 또한 조건부 서식에만 제공하는 다양한 서식으로 데이터 값에 따라 "데이터 막대", "색조", "아이콘 집합"을 이용하여 신호등 체계 등으로 활용할 수 있습니다.
- 그리고 And, Or 함수로 조건은 더욱 풍성해 집니다.
- 무척이나 많은 부분에 응용이 가능할 것 같은데 하지만 원리자체는 간단합니다.
- "조건의 계산 결과 값이 참(True)인 경우 지정한 서식을 적용한다" 입니다.
2. 조건부 서식의 구성 및 작동 원리
- 아래는 조금 전의 셀 서식에서 구현한 조건을 "조건부 서식"으로 동일하게 구현한 내용입니다.
- 어떻게 구성이 되어 있고 적용이 되었는지 한 번 보시기 바랍니다.
- 조건부 서식은 3가지로 구성이 됩니다.
. 규칙 : 조건부 서식의 조건에 해당하는 내용으로 규칙을 점검/계산하여 참(True), 거짓(False) 판정을 합니다.
. 서식 : 조건을 만족하는 경우(True)에 적용되는 셀 서식입니다.
. 적용 대상 : 조건부 서식이 적용이 되는 셀 범위를 말합니다.
- 위의 예제를 기준으로 첫 번째 조건부 서식의 경우
. 규칙 : "=B2 < 100"
. 서식 : 빨강, 소수 첫 번째 자리까지 표시
. 적용 대상 : $B$2:$E$2 입니다.
- 즉 B2 셀이 100 미만인 경우에는 빨간색, 소수 첫 번째 자리까지 표시를 하라는 의미입니다.
- 그리고 이러한 조건부 서식을 "B2:E2"셀에 적용을 하겠다는 의미 입니다.
- "조건부 서식 규칙 관리자" 메뉴에서 첫 번째 조건부 서식을 마우스로 "더블 클릭"을 하게 되면 아래와 같이 "서식 규칙 편집"이 나오고
- 조건부 서식의 "조건" 및 "서식"을 지정하거나 수정을 할 수 있습니다.
- 그리고 여기에서 지정된 조건의 내용만을 복사하여 셀에 붙여 넣기를 해보시면 아래와 같이 조건의 결과가 참 (True)이 되고 지정된 서식 (빨강, 소수 첫째자리까지 표시)이 적용된다는 것을 알 수 있습니다.
- 다시 한번 얘기 드리지만 조건부 서식의 기본 원리는 "조건의 계산 결과 값이 참(True)인 경우 지정한 서식을 지정한 셀에 적용한다"입니다.
- 그리고 그렇게 적용하기 위해서 "조건, 지정한 서식, 지정한 셀" 이렇게 3가지가 필요하고 설정을 해줘야 합니다.
3. 지정한 셀과 조건은 독립적이다.
- 우리가 조건부 서식을 광범위하게 활용을 못 하는 이유 중에 하나입니다.
- 조건부 서식을 적용할 셀을 선택하고 조건을 입력하는 과정에서 많은 사람들이 나도 모르게 조건을 지정된 셀을 기준으로 작성을 하려고 합니다.
- 물론 지정된 셀을 기준으로 작성을 해도 되지만 사실 조건은 지정된 셀과는 무관하게( = 독립적으로) 작성이 가능합니다.
- 그냥 조건 그 자체로 작성이 가능하고 어떠한 조건이 들어가더라도 결국 마지막에 참(True), 거짓(Fasle)으로 판정을 합니다.
- 그리고 수식 등의 판정된 결과를 바탕으로 참(True)인 경우에 기계적으로 지정된 조건을 지정된 셀에 표시를 해주는 방식입니다.
- 이런 사고를 가져야만 풀수 있는 가장 흔한 문제로 특정 셀의 값이 합격인 경우 표에서 해당 셀을 포함한 전체 행의 색상을 넣는 문제입니다.
- 결국 "조건, 대상 셀, 서식" 모두 따로따로 독립적으로 각각의 역할을 하신다고 보면 됩니다.
- 서로 도와주고 끌어 주고 없습니다. 그냥 각각의 역할에 최선을 다한다고 이해하시면 됩니다. ^^
4. 조건 지정은 현재 셀을 기준으로, 절대참조, 상대참조를 잘 사용해야 한다.
- 가장 중요하고 어려운 부분입니다.
- 많은 분들이 조건부 서식을 내 마음대로 사용을 못 하는 가장 큰 이유입니다.
- 그리고 조건부 서식을 입력하는 수식란도 조금 불편하에 되어 있어서 (화살표 이동 불가 등) 한 목 단단히 하곤 합니다.
- 편집이 잘 안되고 편집의 경우 거의 수작업으로 수식을 모두 입력해야 합니다. ㅠㅠ
- 조건부 서식도 복사하여 적용하는 것이 가능한데 이런 경우 "조건" 부분도 같이 복사하여 적용이 됩니다.
- 이 때 "조건"부분의 수식도 일반적인 셀 복사와 동일하게 셀주소의 절대참조, 상대참조의 복사 적용방식이 적용되게 됩니다.
- 그리고 한꺼번에 여러 셀을 선택하여 조건부 서식을 적용할 때도 동일하게 적용이 됩니다.
- 여기서는 현재 선택된 셀을 기준으로 먼저 조건을 절대참조, 상대참조를 정확하게 작성을 하여 입력을 하게 되면
- 나머지 셀들에 대해서는 입력된 수식을 복사, 붙여 넣기 하듯이 수식이 복사되어 적용이 됩니다.
- 설명이 좀 어려운 것 같은데 예제을 보시면서 다시 한번 설며을 해보도록 하겠습니다.
- "불합격"인 경우에 전체 행을 색상과 빨간색 글꼴로 적용하는 예제입니다.
- 우선 먼저 조건부 서식을 지정할 범위인 "조형우(B3)" ~ "합격(F5)"까지 전체를 선택하고 아래 메뉴 패스를 참조하여 조건부 서식을 적용합니다.
- 메뉴 패스 : "홈" 탭 > "조건부 서식" > "새 규칙" > "수식을 사용하여 서식을 지정할 셀 결정"
- 현재 조건부 서식을 적용할 범위는 처음 마우스로 선택한 "B3:F5"입니다.
- 그리고 적용할 범위를 선택할 때 마우스로 "B3"부터 선택해서 "F5"까지 드래그를 한 상태입니다.
- 이렇게 "B3"부터 마우스 드래그로 범위를 선택하게 되면 선택된 전체 범위는 "B3:F5" 이지만
- "현재 셀"은 처음 선택하신 "B3"가 되게 됩니다. 그리고 표 선택 범위에서도 "B3"셀의 색상만 채우기 없이 표시가 되고 "이름 상자"도 "B3"로 표시가 되어 있습니다.
- 그리고 "조건부 서식에서" 규칙(=조건)을 입력하실 때는 바로 "현재 셀(B3)"을 기준으로 일단 입력을 해야 합니다.
- 그럼 나머지 범위의 셀들은? 맞습니다. 엑셀이 자동으로 "현재 셀(B3)" 기준으로 입력된 수식을 복사하여 적용하게 됩니다.
- 이렇게 "나머지 범위에 대해서 엑셀이 자동으로 복사하여 수식을 적용"하기 때문에 이 과정에서 셀 주소가 적절한 절대 참조/상대 참조로 잘 적용이 되어 있어야 오류 없이 조건부 서식을 사용할 수 있습니다.
- 그리고 우리들은 이렇게 조건이 복사되어 사용될 것을 고려해서 셀 주소를 적절하게 잘(?) 작성을 해주어야 합니다.
- 다시 한번 보시면 1) 여러 범위를 선택해서 조건부 서식을 적용해도 조건에 해당하는 수식은 "현재 셀"을 기준으로 작성합니다.
- 2) 나머지 범위는 "현재 셀"기준의 수식이 복사되어 적용되기 때문에 절대참조/상대참조 주소형식이 정확해야 오류가 없습니다.
- 상기 예제의 경우 "현재 셀"이 "B3" 셀이고 현재 셀에 적용되어야 하는 수식은 수식이 전체 셀에 복사된다고 봤을 때 " =$F3 = "불합격" "으로 입력을 해야 합니다.
- 선택된 전체 범위에 조건이 복사하여 적용이 되더라고 "F열"이 항상 고정이 되어야 하기 때문에 "$F3"의 혼합참조 형식의 셀주소를 입력하였습니다.
- 이렇게 조건이 입력이 되었다면 원하는 "셀 서식을 지정"하고 "확인"을 눌러주게 되면 완료가 됩니다.
- 여러 셀들의 범위로 조건부 서식을 적용하거나 복사하여 사용을 하는 경우 이렇게 복사되는 과정을 이해하고 적절한 절대 참조, 상대 참조 형식으로 미리 작성을 해주는 것이 요령입니다.
- 물론 처음에는 조금 어렵습니다. 원리와 내용을 이해하고 조금씩 사용하다 보면 자연스럽게 익숙해지는 내용이니깐 그렇게 걱정할 것도 없습니다.
5. 조건부 서식 다중 조건 적용 - And, Or 함수로 조건을 더욱 풍성하게
- 조건부 서식의 조건은 글자 그대로 조건입니다.
- 특히 수식을 사용하시는 경우 셀에서의 수식과 동일하게 And, Or 함수를 사용하는 것도 가능합니다.
- 여러 가지 조건을 And, Or 조건 및 함수를 사용해서 더욱 풍성하게 사용을 해보세요.
- 아래 예제는 평균이 60점 이상이고(And) 최저 점수가 40점 이상인 경우에 색상으로 구분하는 예시입니다.
- 적용된 규칙 ( = 조건 ) : =AND(AVERAGE($C3:$D3)>=60,MIN($C3:$D3)>=40)
- 많이 그리고 다양한 경우에 대해서 "조건부 서식"을 사용해 보시기 바랍니다. 그리고 이러한 어렵고 복잡한 수식을 사용하지 않고도
- 몇몇 자주 사용할 것 같은 조건부 서식은 엑셀이 친절하게 따로 메뉴로 만들어 놓았기 때문에 그 메뉴를 먼저 사용하셔도 됩니다.
- 하지만 근본적인 원리는 오늘 얘기한 내용이기 때문에 이해하시고 사용을 하시면 더욱더 풍요롭고 자유롭게 "조건부 서식"을 사용하실 수 있을 것입니다.
** 핵심 요약 : 조건부 서식 - 내 맘대로 안 되는 조건부 서식 원리가 어떻게 되나요?
1. 조건부 서식이란 무엇인가?
- 셀 서식에서도 조건에 따라 표시형식, 색상 정도는 지정이 가능하다. 그런데 거의 쓰는 사람이 없다.
- 조건부 서식은 조건을 만족하는 경우에 원하는 셀 서식 적용이 가능하다.
2. 조건부 서식의 구성 및 작동 원리
- 조건부 서식의 구성 3가지 : 규칙(=조건), 서식, 적용 대상
. 규칙 : 조건부 서식의 조건에 해당하는 내용으로 규칙을 점검/계산하여 참(True), 거짓(False) 판정을 합니다.
. 서식 : 조건을 만족하는 경우(True)에 적용되는 셀 서식이다.
. 적용 대상 : 조건부 서식이 적용이 되는 셀 범위를 말한다.
- 기본 원리는 "조건의 계산 결과 값이 참(True)인 경우 지정한 서식을 지정한 셀에 적용한다"이다.
3. 지정한 셀과 조건은 독립적이다.
- "조건, 대상 셀, 서식" 모두 따로따로 독립적으로 각각의 역할을 한다.
- 특히 조건은 그 자체로 작성이 가능하고 어떠한 조건이 들어가더라도 결국 마지막에 참(True), 거짓(Fasle)으로 판정을 한다.
4. 조건 지정은 현재 셀을 기준으로, 절대참조, 상대참조를 잘 사용해야 한다.
- 여러 범위를 선택해서 조건부 서식을 적용해도 조건에 해당하는 수식은 "현재 셀"을 기준으로 작성한다.
- 나머지 범위는 "현재 셀"기준의 수식이 복사되어 적용되기 때문에 절대참조/상대참조 주소형식이 정확해야 오류가 없다.
5. 조건부 서식 다중 조건 적용 - And, Or 함수로 조건을 더욱 풍성하게
- 여러 가지 조건을 And, Or 조건 및 함수를 사용해서 더욱 풍성하게 사용을 해보자.
[엑셀 특강 (개념 잡기)] - 999. 엑셀 기초 특강 목차
999. 엑셀 기초 특강 목차
[엑셀 특강 (개념 잡기)] - 001. (엑셀 기초 특강) 엑셀 데이터란? (feat. 숫자, 문자, 날짜 무엇이 다른가?) [엑셀 특강 (개념 잡기)] - 002. (엑셀 기초 특강) 엑셀 함수란? (feat. 왜 그렇게 어렵고 많아야
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글