동점일 때 과목 점수 순으로 등수 매기기 (2)

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=aZPUHnrB5IezGqrMzPPLw0zaZXMjf9/x

 

과목이 매우 많을 경우, 수식으로 처리하려면 아래 방식이 바람직합니다.

 

	A	B	C	D	E	F	G	H	I	J
1	국어	영어	수학	총점	국비교	영비교	수비교	평균	전	후
2	95	100	95	290	290.037	290.074	290.074	96.667	4	7
3	95	95	100	290	290.037	290.037	290.037	96.667	4	8
4	90	100	100	290	290.000	290.000	290.000	96.667	4	9
5	100	95	95	290	290.111	290.148	290.148	96.667	4	5
6	100	90	100	290	290.111	290.111	290.111	96.667	4	6
7	100	100	90	290	290.111	290.185	290.185	96.667	4	4
8	100	95	100	295	295.037	295.037	295.037	98.333	1	2
9	95	100	100	295	295.000	295.000	295.000	98.333	1	3
10	100	100	95	295	295.037	295.074	295.074	98.333	1	1
11
12	27

 

D2 =SUM(A2:C2)
E2 =D2+SUMPRODUCT((D$2:D$10=D2)*(A$2:A$10<A2))/$A$12
I2 =RANK(D2,$D$2:$D$10)
J2 =RANK(G2,$G$2:$G$10)
A12 =COUNTA(A2:A10)*COUNTA(A1:C1)

 

E2 잡아 F2:G2에 복사, D2:J2 잡아 아래로 복사하면 표가 완성됩니다.

 

수식을 간단히 설명하면, A12에 인원수*과목수를 계산해 둡니다 (예시에서는 9명*3과목=27). D2에 합계를 구해 두고 E2에서 합계가 같고 국어성적이 낮은 사람의 수를 구합니다 (동점자 중 자기 보다 국어성적이 낮은 사람이 많을수록 추가 점수를 받도록 함). 그 수를 A12로 나눈 값을 원래 총점에 더한 것이 E열의 값이며, 국어만 비교한 후의 총점이 됩니다.

 

A12로 나누는 이유는 추가 점수가 1점을 넘지 않게 하기 위해서입니다. 만약 원래 점수들의 단위가 90.3 식의 소수 첫째 자리라면 A12를 10으로 더 나눠 주는 식의 감안을 해 주어야 원하지 않는 에러를 막을 수 있습니다. 여하튼 이 방법을 응용하면 몇 과목이 되든 순위 재부여가 가능할 겁니다. (한 수식으로 처리하려면 SUMPRODUCT 중첩을 써야 하므로 과목 수에 기하급수로 수식이 길어지므로, 위 방식이 실전적이겠습니다.)