배열함수를 이용한 평균이 같을 때 국어점수로 순위 구하기-경주엑셀전문학원

Question

성적표를 작성하고 있습니다.
RANK.EQ 함수로 순위를 구하면 평균이 같을 경우 같은 순위가 매겨집니다.
만약 평균이 같을 경우 국어 점수가 높은 학생에게 더 높은 순위를 부여하려면 어떻게 하면 되나요?
또 평균과 국어 점수가 모두 같을 경우 영어 점수로 순위를 매길 수도 있을까요?

Answer

만약 평균이 80점인 학생이 3명 있고 순위가 1등일 경우, 국어 점수가 높은 학생에게 더 높은 순위를 부여해서 1등. 2등, 3등으로 순위를 정하겠다는 것이죠. 조건을 사용하여 순위를 구하려면 배열 수식을 이용해야 합니다.

 

Step by Step

① 아래 워크시트에서 마지막 세 학생의 평균, 순위, 조건 순위의 결과를 보면 평균이 같을 때 순위가 모두 2등으로 계산되어 있습니다. 조건 순위는 평균이 같을 때 국어 점수가 높은 학생에게 높은 순위를 부여한 것으로 2, 3, 4등으로 서로 다르게 순위가 계산됩니다.

 

② 조건 순위는 다음과 같은 방법으로 구합니다.

• 조건 순위를 작성하는 방법은 [원래 등수 + 평균이 같고 국어가 높은 학생의 수] 형식입니다.

원래 순위는 RANK.EQ 함수를 사용하면 되고, 평균이 같고 국어가 높은 학생의 수는 SUM 함수를 사용합니다.

• 조건 순위를 구하는 수식의 구성은 =RANK.EQ+SUM(조건1*조건2) 되겠지요.
• 즉 H3 셀에 =RANK.EQ(F3, $F$3:$F$11)+SUM((F3=$F$3:$F$11)*(C3<$C$3:$C$11))을 입력한 다음 Ctrl + Shift + Enter 를 누르고 채우기 핸들을 끌어 나머지 셀에 수식을 복사하면 됩니다.

학생 이은하의 조건 순위라면 원래 등수는 2등이고 총점이 같고 국어가 이은하 보다 높은 학생은 1명(백진희)이므로 2+1로 계산되어 3등이 되는 것입니다.

국어점수순위.xlsx

0.01MB

③ 아래 워크시트에서 조건순위2는 만약 평균이 같으면 국어가 높은 학생, 평균과 국어까지 같으면 영어가 높은 학생에서 더 높은 순위를 부여한 것입니다. 마지막 세 명의 학생의 원래 순위는 평균이 같으므로 모두 2등입니다. 조건순위1은 평균이 같을 때 국어 점수로 순위를 매긴 것으로 이은하와 백진희는 국어 점수마저 같으므로 두 명이 모두 2등입니다. 조건순위2는 평균과 국어가 모두 같을 때 영어로 순위를 매겨 2,3,4등으로 계산된 것입니다.

 

④ 조건2 순위를 구하는 수식은 다음 방법에 의해 작성됩니다.

• 수식은 [원래 등수+평균이 같고 국어가 높은 학생의 수+평균과 국어가 같고 영어가 높은 학생의 수]와같은 형식으로 이루어집니다.
• 즉 =RANK.EQ+SUM(조건1*조건2)+SUM(조건1*조건2*조건3)의 형식이 됩니다.
• I3 셀에 수식 =RANK.EQ(F3,$F$3:$F$11)+SUM((F3=$F$3:$F$11)*(C3<$C$3:$C$11))+SUM((F3=$F$3:$F$11)*(C3=$C$3:$C$11)*(D3<$D$3:$D$11))을 입력하고 Ctrl + Shift + Enter 를 누르고 채우기 핸들을 끌어 나머지 셀에 수식을 복사하면 됩니다.

 

국어점수순위3.xlsx

0.01MB