1. Rank함수 X 배열함수 사용 예시
예전에 기관의 채용 공고에 지원한 수십명의 지원자들을 shortlisting 하기 위한 양식을 만들 때 유용하게 사용했던 Rank함수 X 배열함수 공유 드립니다. 아래 예시 파일 참고하세요.
항상 필요한 건 아니고, 보통 시험 성적 순으로 순위를 나열하기 위해 단순 Rank함수를 사용하게 되면, 같은 순위의 사람들이 중복되어서, 특히 응사자간의 성적 차이가 크지 않을 때, 상위 득점자 몇 명을 정확하게 구분하기가 힘들더라구요. 예를 들어, 필기 시험을 보고 성적 순으로 10명에게만 면접 기회를 주려고 하는데… 예시 파일 처럼, 응시자가 수십명/ 수백명인데 중복 순위까지 있게 되면 상위 10명을 추출하는 게 쉽지만은 않습니다.
2. Rank함수 X 배열함수 수식 설명
일단, 동점자들의 순위를 동점자 중복없이 매기기 위한 수식은 아래와 같습니다. (첨부 파일의 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 이 되는 것입니다.
3. 상위 득점자 N명을 자동으로 추출하는 양식
위에나온 Rank함수 X 배열함수를 활용하여, 동점자들을 표에 나온 순서대로 순위를 매기면, 아래의 표처럼 상위 득점자 n 명을 자동으로 추려내는 양식도 손쉽게 만들 수 있게 됩니다. (첨부 파일에는 두 번째 탭에 만들어 놓았으니 참고하시기 바랍니다.)
표에서 사용한 아래의 수식은
=IFERROR(VLOOKUP($C4,Longlist!$C$5:$I$64,7,FALSE),””) (셀 D4 참고)
위에서 설명드린 배열 수식에 비하면 많이 쉬운 편이니 (^^), 차근 차근 보시면 이해하는 데 어려움은 없을 겁니다.
Rank함수 X 배열함수 이외에 다른 쉽고 유용한 엑셀 팁들을 보고 싶으다면 아래 링크 클릭해주세요!