엑셀(excel)함수 tip 39 - (percentrank, percentile함수로 상대순위 구하기)

,,., 2018. 9. 23. 08:08

percentrank, percentile함수는 상대순위를 구하는 함수입니다. 우리가 늘상 익숙해 있는 절대값과는 차이가 있습니다.

하단에서 개인별 과목별 점수가 있습니다. 그리고 평균의 값이 있습니다. round함수를 이용해서 평균값의 첫번째에서 반올림을 했습니다. round함수는 5이상일 경우에는 올리고 5미만(1~4.9999)일 경우에는 내리는(없애는)함수입니다.

if함수를 이용해서 70점을 기준으로 70점 이상일 경우에는 합격 70점 미만일 경우에는 불합격을 구해보면 하단과 같이 함수식은 =IF(G3>=70,"합격","불합격")으로 구해보면 하단과 같은 결과값이 나타납니다. 이때 70점이란 기준은 절대값은 나타냅니다.

● 절대값으로 합격불합격 정하기

▶ IF함수인수창에서 작업

▶ IF함수를 직접 입력

●percentrank를 이용해서 상대순위 구하기

하단에서 5개의 값이 있을 경우 PERCENTILE함수를 이용해서 상대값을 구했습니다. 여기에 사용된 함수식은 하단과 같습니다. 즉 =PERCENTILE($B$3:$B$7,1) 에서 1은 최대값이기 때문에 98점입니다. 당연히 0의 값은 최소값이기 때문에 67입니다.

여기에서 이용된 K의 값은 0~1까지의 범위로써 1의 값은 최대값이며 0의값은 최소값입니다.

percentrank함수는 절대의 순위가 아닌 상대의 순위값만을 나타냅니다. 즉 최고값이 100%, 최저값이 0%그 안에 해당되는 값들은 나누기를 통해서 순위만을 정해서 %로 나타냅니다. 즉 서로간의 상대순위만 중요하지 그 값(평균값)의 크기는 중요한 인자가 아닙니다. 이를 함수식으로 구해보면 [=percentrank($G$3:$G$7,$G3]함수로 구해보면 5개의 값들이 최대값 100%,75,50,25,0 다섯개 값으로 나타납니다.

합격여부를 결정할 때는 상대순위의 어느 값을 기준으로 하면 됩니다. 하단에서는50%보다 큰 점수를 기준으로 합격을 정했습니다. 50%이하는 불합격입니다. 이를 IF함수식을 이용하면 [=IF(I3.50%."합격","불합격")]입니다. 이처럼 상대적인 값을 비교해서 합격불합격을 정했습니다.

●percentile로상대순위 구하기

percentrank함수,percentile함수 두 함수 다 상대순위(값)을 구하는데 percentrank함수는 순위가 구해지면 균등으로 그 안의 값을 정합니다. 즉, 값의 크기는 고려하지 않습니다. 반면 percentile 함수는 상대크기를 정한 후에 그 값의 크기를 고려해서 나타냅니다. 즉 하단위의 함수 [percentile($F$3:$F$7,0.6)]의 의미는 각각의 평균값의 0.6에 해당하는 값(절대값)입니다. 이 값이 82.13이 나왔습니다.

percentile함수로 구한 82.13의 값과 평균값을 비교하여 합격불합격을 결정합니다. 여기에서 이용된 함수식은 =IF(F3>K3,"합격","불합격")]을 이용해서 82.13보다 크면 합격 적으면 불합격은 나타냅니다.