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

조건부 서식의 상위/하위 규칙도 별다를 것이 없습니다. 말 그대로 상위/하위를 구분해서 서식을 지정하는 형태입니다.

 

 

위 표 주요 목적은 합계 점수 하위 30% 학생을 구분하는 것입니다. 정렬도 좋은 방법이지만 정렬만으로는 30% 가 몇명인지 당장은 판별할수 없습니다. 물론 계산을 하면 되겠지요 

계산을 하자면 하위 30%는 12(총학생)*0.3 = 3.6 그래서 총 3.6명 입니다. 합계를 기준으로 내림차순 정렬후 아래로부터 3명까지 가 30%에 속하는 학생이네요.

반대로 상위 70%는 12(총학생)*0.7 = 8.4 로 70%에 속하는 학생 수는 8명입니다.

 

하지만 조건부 서식의 상위/하위 규칙을 사용하면 위의 계산을 필요없습니다. 단순하게 숫자만 입력하면 엑셀이 자동으로 계산해서 알려줄테니까요

 

 

위의 순으로 조건부 서식을 선택합니다.

 

 

결과

 

 

4명의 합계가 선택한 서식으로 변경되었습니다.

상위 70% 도 해보겠습니다.

 

 

상위 70%를 초록색 계열로 서식이 변경 되었습니다.

 

반응형

 

정렬도 해 보겠습니다.

 

 

결과

 

 

상/하위가 잘구분되고 정렬된 데이터가 완성되었습니다.

그런데 사실 위 표에서 한가지 짚고가야하는 사항이 있습니다. 혹시 눈치 채셨을까요?

 

위에서 계산해본 결과 하위 30%에 포함 되는 학생 수(셀 수)는 3명 이었습니다. 상위 70%는 8명 이었죠.

그런데 하위 30%에 4명이 선택되었습니다. 계산이 틀렸을까요? 아닙니다.

합계값에 F13셀 과 F14셀를 확인 하면 241로 동일합니다. 같은 값이니 같이 적용되는 것이죠. 즉 지정한 %를 넘어가더라도 동일한 값을 가지고 있다면 선택한 서식이 적용되는 것입니다.

 

상위/하위 규칙을 적용할때 주의할 점은 셀의 갯수가 나누어 떨어지지 않고 소수점으로 넘어가게 되면 여기도 저기도 포함 되지 않는 셀이 존재하게 됩니다. 위의 경우 합계의 총 개수(총 학생 수)는 12개(명)이고 

하위 30%에 포함 되는 셀 수(학생 수)는 3개(명), 상위 70%에 포함되는 셀 수(학생 수)는 8개(명) 입니다. 즉 1개의 셀이 30%에도 70%에도 포함되지 않게 됩니다.

위 표에서 김만호의 3차 성적이 82가 아니라 81이라면 하위 30%에 박정자는 포함될 수 없는 것이죠

 

 

조건부 서식의 상위/하위를 %로 정할 경우는 위의 내용을 예상하시고 적용해야 합니다.

물론 항목으로 정할 경우는 위와 같은 현상은 발생하지 않습니다.

 

조건부서식1.xlsx

0.02MB