이번 강좌는 조건부 서식의 아이콘 집합입니다. 아이콘 집합은 셀의 좌측에 여러 가지 아이콘을 표시하여 시각화합니다. 일상에서 주로 사용하는 화살표, 신호등, 별표 등을 아이콘으로 사용하므로 문서를 작성하는 사람과 읽는 사람이 특별히 합의하지 않더라도 데이터 크고 작음을 쉽게 파악할 수 있습니다.
보통 3가지, 4가지, 5가지 등으로 데이터의 크고 작음을 구분하고 각각 다른 아이콘으로 표시하는 형태입니다. 조건부 서식의 상위/하위 규칙과 매우 유사한 기능이지만 전혀 다른 결과를 반환합니다. 그래서 아이콘 집합을 알아보기 전에 "상위/하위 규칙"을 이해하고 있으면 이해하는 데 도움이 됩니다. "상위/하위 규칙"이 기억나지 않는다면 이전 강좌를 한번 보시고 오시길 바랍니다.
예제 파일을 확인하겠습니다.
예제 파일은 데이터 막대에서 사용한 것과 동일한 파일입니다. 이번에는 실매출에 데이터 막대가 아니라 아이콘 집합의 3색 신호등을 적용해 보겠습니다.
적용 방법도 별다른 것 없습니다. 이전과 동일하게 범위를 선택하고 조건부 서식 > 아이콘 집합 > 적용할 아이콘을 클릭하면 끝납니다.
어떤 것은 초록색 신호등, 어떤 것은 노란색, 어떤 것은 빨간색 신호등 아이콘이 셀의 좌측에 표시되었습니다. 대략 살펴보면 초록색 아이콘 셀 값이 크고, 노란색은 중간 정도, 빨간색은 작은 값이란 것 정도는 확인됩니다. 어떤 느낌인진 알 것 같죠. 그럼, 각 아이콘을 나눈 기준이 있다는 것이네요. 이걸 알아보려면 어떻게 해야 할까요?
조건부 서식을 적용하면, 규칙이 생긴다는 것 기억하지요? 그 규칙을 확인하면 됩니다.
- 조건부 서식을 적용한 아무 셀이나 클릭하여 선택합니다.
- 조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다.
- 적용된 규칙 중 편집할 규칙을 클릭하여 선택합니다. => 선택된 규칙이 파란색 배경으로 변경됩니다.
- 규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.
서식 규칙 편집 대화상자를 보면 어떤 기준으로 아이콘이 적용되었는지 알 수 있습니다.
선택 범위 값 중 67% 이상은 초록색,
67% 미만 33% 이상은 노란색,
33% 미만은 빨간색이 적용되었습니다.
즉 선택한 범위의 값을 큰 값, 중간값, 작은 값으로 33%씩 3등분 했다는 것입니다. 아이콘이 4개라면 25%씩 4등분, 5개라면 20%씩 5등분하는 것이죠.
그리고 값은 바꿀 수 있게 되어 있으니 원하는 수치가 있다면 값만 변경하면 됩니다. 간단해 보이죠.
조건부 서식의 상위/하위 규칙도 "상위 OO%", "하위 OO%"로 만들 수 있었습니다. 같이 적용하고 비교해 보겠습니다.
주의하실 건 "상위/하위 규칙"은 "OO%"를 포함하여 적용하기 때문에 이상이나 이하로만 사용할 수 있습니다. 초과, 미만은 사용할 수 없다는 것입니다. 그럼, 위와 같이 이상으로 되게 하려면 상위 규칙으로 진행해야 합니다.
- 홈 탭 > 조건부 서식 > 상위/하위 규칙 > 상위 10%를 클릭 => 상위 10% 대화상자가 나옵니다.
- 67% 입력하고 서식은 "진한 노랑 텍스트가 있는 노랑 채우기"를 선택한 후 하단 확인 버튼을 클릭합니다. => 상위 67% 이상의 서식이 노랑 계열로 변경됩니다.
- 다시 조건부 서식> 상위/하위 규칙 > 상위 10%를 클릭 => 상위 10% 대화상자가 나옵니다.
- 33% 입력하고 서식은 "진한 녹색 텍스트가 있는 녹색 채우기"를 선택한 후 하단 확인 버튼을 클릭합니다. => 상위 33% 이상의 서식이 초록 계열로 변경됩니다.
먼저 상위 67% 적용하고 33% 나중에 적용한 이유는, 나중에 적용한 규칙이 우선순위가 되기 때문입니다. 규칙의 순위가 높은 것부터 우선 적용되는 것 기억하시죠?
기억 안 나신다면, 아래 강좌 보신 후 돌아와 주세요.
상위/하위 규칙까지 적용했습니다. 비교를 위해 정렬도 간단하게 하겠습니다.
- 정렬하고자 하는 항목의 아무 셀이나 클릭하여 선택합니다.
- 홈 탭 > 정렬 및 필터 > 숫자 내림차순 정렬을 클릭합니다.
실매출 기준 내림차순으로 표가 정렬되었습니다.
어떤가요? 좀 이상하죠? 아이콘 집합과 상위/하위 모두 동일하게 3등분 했기 때문에 서식과 신호등 색이 동일한 계열이어야 합니다. 다시 말해 초록 계열 서식은 초록색 신호등, 노란 계열 서식은 노란 신호등이 나와야 하죠. 하지만 장먼옥의 경우 노란 계열 서식인데 초록색 신호등이 표시 되었습니다. 이유는 바로 아이콘 집합과 상위/하위 규칙의 기준 차이 때문입니다.
상위/하위 규칙의 경우 기준은 항목의 개수입니다. 즉 선택한 셀의 개수라는 것이죠. 지금 선택했던 범위의 셀의 개수는 10개입니다.
상위 67% 이상은 10*0.67 = 6.7이므로 6개 이상이 되어 6개 이상이 노란 계열 서식이 되고,
상위 33% 이상은 10*0.33 = 3.3이므로 3개 이상이 되어 3개 이상이 초록 계열 서식이 됩니다.
반면 아이콘 집합의 기준은 항목의 개수가 아니라 선택한 범위의 값이 기준입니다. 즉 최소값 2,250,000부터 최대값 3,675,000을 3등분한 것이 됩니다.
3,675,000 - 2,250,000 = 1,425,000을 3등분한 것이죠. 결과,
노란색 신호등은 2,250,000+(1,425,000/3) = 2,725,000이므로 셀 값이 2,725,000 이상에서 표시되고,
초록색 신호등은 3,675,000-(1,425,000/3) = 3,200,000이므로 셀 값이 3,200,000 이상에서 표시됩니다.
결과, 위와 같이 상위/하위 규칙으로 3등분을 할 경우 반드시 3/3/4개로 셀이 개수가 나누어져 서식이 적용되지만,
아이콘 집합을 사용할 경우 셀 값의 차에 의해 신호등이 다르게 나타납니다. 극단적으로 노란색 신호등이 나오지 않을 수도 있게 됩니다. 위의 경우라면 노란색 신호등의 값이 만약 2,725,000보다 작을 경우가 되겠습니다. 그럴 경우는 실매출이 작은 값으로 몰려 있다는 얘기가 되겠죠.
위와 같은 이유로 아이콘 집합을 별다른 설정 없이 진행하면, 기본값인 백분율로 나누기 때문에 셀 값의 크고 작음을 대략적인 분포 정도로만 알 수 있을 뿐, 셀 값이 몇 이상부터 적용된 것인지 알 수 없습니다. 물론 위처럼 계산을 해볼 수는 있겠죠. 그러자면 또 생각하고 다시 계산을 해보는 절차가 필요하겠죠. 귀찮은 작업이 되어 버립니다. 그래서 실무에서는 셀 값을 백분율로 진행하지 않고, 직관적으로 셀 값 그대로 사용하는 편을 선호합니다.
3,300,000 이상은 초록색 신호등,
3,300,000 미만 2,300,000 이상 노란색 신호등,
2,300,000 미만 빨간색 신호등
의 형태로 사용하는 것이죠. 규칙을 수정하여 적용해 보겠습니다.
- 조건부 서식이 적용된 범위 중 아무 셀이나 클릭하여 선택합니다.
- 조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다.
- 적용된 규칙 중 편집할 규칙을 클릭하여 선택합니다. => 선택된 규칙이 파란색 배경으로 변경됩니다.
- 규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.
- 아이콘 집합의 값의 종류는 기본 백분율로 선택되어 있습니다. 종류를 백분율에서 숫자로 변경합니다.
- 값을 원하는 기준 숫자 그대로 작성합니다. 저는 3,300,000과 2,300,000으로 적용했습니다.
- 서식 규칙 편집 대화상자의 확인 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 닫히고 조건부 서식 규칙 관리자 대화상자 다시 나옵니다.
- 최종, 조건부 서식 규칙 관리자 대화상자의 확인 버튼을 클릭하여 변경된 규칙을 적용합니다.
결과, 명확하게 3,300,000과 2,300,000을 기준으로 나눈 아이콘 집합이 적용됩니다.
의견글 입니다.
()
