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

평균(합계) 점수가 같을 때 국어, 수학, 사회, 과학 과목 순으로 높은 점수에 등수를 새로 부여하는 방법입니다.

 

	B	C	D	E	F	G	H	I	J	K
2	국어	영어	수학	과학	합계	등수1		보조	등수2	참고
3	90	80	70	60	300	7		300.9080706	8	3210
4	85	95	65	75	320	3		320.8595658	4	2301
5	65	85	70	80	300	7		300.6585708	9	312
6	65	95	70	90	320	3		320.6595709	5	312
7	60	85	85	60	290	10		290.6085856	10	1331
8	95	95	85	90	365	1		365.9595859	1	3301
9	65	75	70	75	285	11		285.6575708	11	313
10	90	80	70	80	320	3		320.9080708	3	3202
11	65	60	75	70	270	12		270.6560757	12	1032
12	90	85	55	70	300	7		300.9085557	7	3201
13	95	85	70	60	310	6		310.9585706	6	3210
14	90	60	90	85	325	2		325.9060909	2	3031

 

G3 =RANK(F3,F$3:F$14,0) ; 합계로만 순위 부여

I3 =F3+SUMPRODUCT(B3:E3,10^((4-COLUMN(A$1:D$1))*2))/100000000

K3 =SUMPRODUCT(4-RANK(B3:E3,B3:E3,0),10^(4-COLUMN(A$1:D$1)))

G3를 J3에 복사, G3:K3 잡아 아래로 복사하면 표가 완성됩니다.

 

I3 수식은 SUMPRODUCT로 과목점수를 모두 이어붙인 문자열로 만드는 효과를 냅니다. (과목수가 많아지면 VBA로 RANK+문자열 붙이기 방식을 쓰면 되겠지요) 이로써 국영수과 순서로 100% 새 등수를 매길 수 있습니다. (K열의 참고는 각 과목(국영수과)의 상대비교에 따른 일련번호 발생인데 5,6행 등에서 보듯 잡아내지 못하는 경우가 있습니다.)