엑셀 배열수식을 이용한 반별 순위 구하기

Question

3학년 학생의 모의고사 점수가 들어 있는 성적표에서 반별로 순위를 매기려고 합니다.
배열 수식을 사용하면 이렇게 그룹 별로 따로 순위 계산을 실행할 수 있을까요?

Answer

이런 경우에는 RANK 함수를 사용하여 순위를 구하는 것이 아니라 SUM 함수를 이용한 배열 수식으로 순위를 구합니다.
예를 들어 1반에서 3등인 학생을 생각해 보세요. 이 학생의 순위는 반이 같고 점수가 높은 학생의 수를 구한 다음 1을 더해주면 됩니다.

----차근 차근

① 아래 위쪽의 워크시트는 전체석차의 오름차순으로 데이터를 정렬해 놓은 상태입니다.
여기서 F3:F17 영역에 반별로 따로 석차를 구했습니다.
아래쪽 워크시트처럼 반의 오름차순으로 데이터를 정렬해 보면 반별석차의 결과를 쉽게 알아 볼 수 있습니다.

 

② 반별로 석차를 구하는 수식은 다음 방법으로 작성합니다.

수식의 구성은 =SUM(조건1*조건2)+1로 이루어집니다. 여기서 조건1은 같은 반인지 검사하고 조건 2는 점수가 더 높은지 검사합니다.

1등인 학생은 더 높은 점수가 없으므로 SUM 함수의 결과는 0이 됩니다. 그러니까 1을 더해서 1등으로 만드는 과정이 필요합니다.

F3 셀에 수식 =SUM((B3=$B$3:$B$17)*(D3<$D$3:$D$17)+1을 입력한 다음 Ctrl+Shift+Enter를 누른 것입니다. 채우기 핸들을 끌어 나머지 셀에 수식을 복사합니다.