조건부 서식 ❸ 상위/하위 규칙

하지만 개념적으로는 셀 강조 규칙과 차이 좀 있습니다. 셀 강조 규칙의 경우 직접적으로 특정 기준값 정하고 정해진 기준값과 선택한 범위의 각 셀 값을 비교하여 셀의 서식을 변경하지만, 상위/하위 규칙의 경우는 직접적인 기준값을 정하지 않고 선택한 범위 셀 중 값이 큰 상위 몇 개 또는 셀 값이 작은 하위 몇 개 등으로 정해 서식을 변경합니다. 
즉 셀 강조 규칙은 절대 평가를, 상위/하위 규칙은 상대 평가를 의미하게 됩니다.

예제 파일은 엑셀 시험 현황입니다. 총 3차의 시험이 진행되었고, 학생별로 차수 점수와 총합계 점수가 정리된 표입니다.

데이터가 모두 수집되었으니, 통과된 학생과 유급된 학생을 구분하려 합니다. 일단 유급된 학생을 걸러내는 것이 문제가 되겠네요. 유급의 기준은 하위 30%입니다.

그런데 상위, 하위를 구분할 때 기준을 개수로 하면 아무런 문제가 없지만 지금처럼 %로 하면 애매한 상태가 될 수 있습니다.

현재 학생 수는 총 12명입니다. 하위 4개 항목이라 하면 당연히 점수가 낮은 4명의 학생만 찾아내면 됩니다. 
하지만 지금처럼 하위 30%라 하면 계산을 해봐야 합니다.  학생 총수는 12명이니 하위 30%는 12명*0.3=3.6명이 되게 됩니다. 같은 방법으로 상위 70%는 12명*0.7=8.4명이 되게 되죠. 그럼, 하위 점수로 4번째 학생은 하위에 속할까요? 상위에 속할까요?

애매하죠. 사람을 나눌 수는 없습니다. 즉 상위에도 속하지 않고 하위에도 속하지 않는 항목이 생기게 된 것이죠. 상위에 속하는 학생 수는 8명, 하위에 속하는 학생은 3명, 이도 저도 아닌 학생이 1명이 됩니다. 

하위 30%를 진행해 보겠습니다.

이어서 상위도 적용하겠습니다.

상위/하위가 잘 구분되었습니다. 

이번에는 정렬도 진행해 보겠습니다. 정렬의 방법은 몇 가지 있지만 정렬은 나중에 자세히 정리하기로 하고 일단 가장 간단한 방법만 알려드리겠습니다. 

그림처럼 표의 레코드가 바뀌고 합계 값이 높은 순에서 낮은 순으로 정렬되었습니다. 간단하죠.

그런데 좀 이상한 것 없나요? 위에서 알아본 바로는 하위 30%에 속하는 항목의 개수는 3개였죠. 그런데 4개의 항목이 하위의 서식으로 적용되었습니다. 뭔가 잘못 알아본 것일까요? 아닙니다. 하위 3번째 4번째를 확인해 보세요. 모두 240입니다. 하위 3번째와 4번째가 동일 값인 거죠. 다시 말하면 하위 4번째는 없는 것입니다. 3번째가 2개인 것이죠. 그래서 3개여야 되지만 4개로 하위 서식이 적용된 것입니다.
그렇다면 하위 4번째, 조조의 합계 점수가 만약 241점이었다면 조조는 상위도 하위도 속하지 않는 순위가 될 것입니다.

현재 합계는 SUM 함수를 통해 1, 2, 3차의 시험 점수를 합계 낸 것이니 조조의 3차 시험을 1점 올려 보면 확인할 수 있을 것입니다.

조조의 3차 시험 점수를 1점 올려주었더니 상위도, 하위도 속하지 않는 항목으로 확인됩니다.

지금 같은 이유로 사실 상대 평가를 하고자 할 때 %를 사용하는 것보다 항목 개수를 사용하는 것이 훨씬 직관적이고 보기 편한 결과를 얻을 수 있습니다.

이것으로 조건부 서식의 상위/하위 규칙을 마칩니다. 상위/하위 규칙의 나머지 규칙은 별다른 것 없으니 직접 해보시기 바랍니다.

추가로 규칙의 기준값이나 서식을 변경하고 싶다면 조건부 서식 > 규칙 관리를 통해 진행하면 됩니다. 조건부 서식을 적용하면 규칙이 생기고 규칙은 항상 조건부 서식 > 규칙 관리를 통해 수정, 삭제, 순위 변경을 할 수 있으니까요. 앞의 두 강좌에서 언급한 내용입니다.