엑셀 동점자 차례로 순위 매기기_상위 득점자 자동으로 추려내기 (배열함수)

Shortlisting (1).xlsx

0.02MB

 

예전에 어디선가 보고, 뭐에 쓸모가 있을까 싶어 흘렸는데... 기관의 채용 공고에 지원한 수 십명의 지원자들을 shortlisting 하기 위한 탬플릿을 만들 때... 유용하게 썼습니다.

 

단순 Rank 로 순위를 매기게 되면, 같은 순위의 사람들이 생겨서 (첨부 파일의) "Shortlisting template" 시트에서 상위 득점자를 자동으로 뽑기가 어렵더군요.

 

일단, 동점자들의 순위를 표에 나온 순서대로 다르게 매기기 위한 수식은 아래와 같습니다. (J23 셀)

 

{=SUM((ROW($I$5:$I$64)<ROW(I23))*($I$5:$I$64=I23),RANK(I23,$I$5:$I$64))} 

 

배열 수식을 사용했습니다. Sum 수식을 통해

1) I23 셀의 점수가 전체 집단에서 차지하는 순위

2) (ROW($I$5:$I$64)<ROW(I23))*($I$5:$I$64=I23)

  2-1) ROW($I$5:$I$64)<ROW(I23) 수식을 통해 I23 위에 위치한 18개 셀이 True 값으로, I23 셀 부터 I65 셀까지 42개 셀이 False 값으로 변환된 배열이 반환 (True; True; .... False; False) (Tip: 해당 수식만 선택해서 F9를 눌러보세요. 수식의 결과값을 보시면 한결 이해하기 쉬울수도...) 

  2-2) ($I$5:$I$64=I23) 수식을 통해 I5:I64 범위에서 I23 셀의 점수인 23을 가지고 있는 셀들은 True, 나머지는 False 로 변환된 배열이 반환

  2-3) 위의 두 수식을 * 로 연결하였으므로... True * True 일 때만 1을 반환하게 됩니다.

3) 최종적으로 I23 셀 위쪽으로 I17 셀에 같은 점수 (23) 이 있으므로, Sum(1, I23 의 순위)가 되어 Rank 로 나온 순위에 +1 이 되는 것입니다.

 

이리하여, 동점자들을 표에 나온 순서대로 순위를 매긴 후, 아래의 표처럼 상위 득점자 n 명을 자동으로 추려내는 양식을 손쉽게 만들 수 있게 됩니다. 표에 수식은 위에 나온 배열 수식에 비하면 쉬운 편이니, 차근 차근 보시면 이해하는 데 어려움은 없을 겁니다.