엑셀 중복값 성적순위 구하기

엑셀 성적순위 구하기 예제로 ① 중복값 제거 성적순위 ② 평균, 조건 평균 ③ 상위(%) 내 성적 순위를 구하는 등 엑셀 통계함수를 이용해 단순 성적계산에서 중복값 제거와 조건에 맞는 순위 등 다양한 성적 순위구하기 유형을  살펴봤다.

 

그간의 성적순위 엑셀 통계함수로 RANK, AVERAGE, SUM, SUMPRODUCT, COUNTIF 다뤘는데 오늘 살펴볼 내용은 중복값 성적순위 구하기다.

 

표현이 애매모호한가 다시말해 한 반에 여러 조로 구성되어 있는 경우 반 전체 성적순위와 같은 조 내 성적순위를 구하기로 설명될 수 있다.

 

앞서 다뤄본 성적순위 구하기 엑셀예제를 잠시 언급하자면...

 

1. 엑셀 순위구하기(rank함수), 중복값(중복순위, sum함수 조합) 없애기

 

 

2. 엑셀 평균구하기(average), 조건 평균구하기(averageif)

 

 

3. 엑셀 순위구하기(Sumproduct, Countif) 상위(%)순위 셀서식 적용

 

 

성적순위 구하기 엑셀예제 3가지 유형을 다뤄봤는데 간단한 성적계산은 가능하리라 믿고 싶다. 본론으로 돌아와 한반에 여러 조로 구성된 경우 전체 성적순위와 같은 조 내에서 성적순위는 어떻게 구할까?

 

아래 엑셀예제를 본다면 한반에 3개조가 있고 개별 성명은 배제된 자료다. 과목별(국어, 수학, 영어) 점수와 점수합계(SUM) 그리고 평균(AVERAGE)까지 앞서 설명한 엑셀 통계함수로 쉽게 구할 수 있다. 또한 전체 순위도 RANK함수로 간단히 결과를 도출할 수 있으리라 생각한다.

 

그림 마지막 항목에 같은조 순위에 대한 결과가 표시되었는데 SUMPRODUCT 함수를 이용해 이마저도 쉽게 구할 수 있다는 사실... 

 

 

항목별로 입력된 수식 구문은 아래와 같다.(※ 상세내용은 붙임 엑셀예제파일 참조)

 

○ 점수 합계 =SUM(B5:D5)
○ 점수 평균 =AVERAGE(B5:D5)
○ 전체 순위 =RANK(E5,$E$5:$E$12)
○ 같은조 순위 =SUMPRODUCT((A$5:A$12=A5)*((B$5:B$12>B5)+(B$5:B$12=B5)*(C$5:C$12>C5)+(B$5:B$12=B5)*(C$5:C$12=C5)*(D$5:D$12>D5)))+1

 

같은 조 순위는 같은 조에 과목별로 성적이 높은 값이 몇 개인지 단계별(국어 -> 수학 -> 영어)로 곱해서 결과를 도출하는 수식이다.

덧붙여 중복된 이름의 학생이 존재하는 경우 중복값(이름)을 제거한 총 인원수를 구하는 방법은

 

=SUM(1/COUNTIF(B4:B12,B4:B12))

 

이름 목록에서 중복값(이름)이 몇 개인지 추출한 후 중복된 이름수로 나누어 모두 합한다. 중복값(이름)이 4개인 경우 1/4+1/4+1/4+1/4 = 1 이 되어 같은 이름은 1명으로 중복값 제거된 총 인원수를 구할 수 있다.

 

엑셀예제파일-엑셀 중복값 성적순위 구하기.xlsx