[엑셀 문답] 여러 반이 섞여 있을 때 반 별 순위를 구하고 싶어요.

엑셀을 활용하는 조금의 어려운 방법입니다. 엑셀의 배열 수식을 활용하여 여러 그룹의 데이터가 섞여 있을 때 그룹별로 분리하여 작업하는 방법을 배워봅니다. 엑셀 배열 수식이 사용되기에 이전의 엑셀 배열 수식 게시물을 살펴주시면 도움될 것입니다.

**님의 질문입니다

제목 : 여러 그룹이 섞여 있을 때 그룹별 순위 구하기
내용 :

[그림1] 여러 반이 섞여 있는 성적표

엑셀을 활용하여 성적을 정리하고 순위를 구하려 합니다. 그런데 여러 반의 성적이 하나의 표에 섞여 있습니다. 반별 학생들의 성적 순위(석차)를 구하고 싶습니다. 엑셀에서 가능할까요? 만일 가능하다면 방법과 설명 부탁드립니다.

마늘빵의 답변입니다

.....
반별(그룹별)로 순위를 구하기 위해서는 각 반별로 묶어주는 과정이 필요합니다. 해당 그룹인지 아닌지를 알아야겠지요.
이를 위해 엑셀 배열 수식을 이용합니다. 질문으로 보아 엑셀 배열 수식을 이해하고 있다 생각되어 설명은 생략합니다.

[그림2] 엑셀 배열 수식 이용 반별 석차 구하기

반별 순위 필드를 질문하신 표에 추가하여 반별로 순위를 구하였습니다. ( G열 )
반별 순위 수식을 보면 중괄호 { } 로 묶여 있는 엑셀 배열 수식입니다. 또 순위를 구하기 위해 엑셀 RANK 함수를 사용하지 않았다는 것입니다. 좀 더 간단한 수식으로 쉽게 결과를 얻으려고 단순히 엑셀 SUM 함수만을 사용하였습니다.

반별 순위 수식을 보기 전에 생각해야 할 것은 석차란 석차를 구할 값보다 큰 값이 몇 개나 되는지 세고 여기에 1을 더한 것입니다. 이것은 곧 내림차순 순위입니다.

예로 2, 4, 5, 1, 3 의 값이 있습니다. 여기서 3의 내림차순 순위를 구하면 몇 위가 될까요? 그렇습니다. 3위입니다. 여러분은 어떻게 이 값을 구했나요? 3보다 큰 값이 몇 개나 되는지를 세었을 것입니다. 5, 4 둘입니다. 여기에 1을 더하여 3위가 결정되지요. 이를 그룹별 순위를 구하는데 활용하였습니다.

G2에 셀 포인터를 놓고 다음의 수식을 입력한 뒤 CTRL+SHIFT+ENTER 키를 눌러 엑셀 배열 수식으로 입력합니다.

=SUM(($A$2:$A$12=A2) * ($E$2:$E$12>E2)) + 1

이후 채우기 핸들을 G12까지 끌어 엑셀 수식 복사합니다. 위의 반별 순위 수식을 살펴봅니다. SUM 함수가 합을 구하는 함수란 것은 알고 있을 것입니다.

($A$2:$A$12=A2) 부분이 그룹별, 즉 여기서는 반별로 순위를 구하기 위해 반을 인식하는 부분입니다.

A2:A12 의 범위를 선택하고 절대 참조합니다. 이값을 A2 와 같은지 비교합니다. IF 문이 아니더라도 이런 비교는 가능합니다. 이의 결과는 참, 거짓으로 반환됩니다. 즉 1, 0 이 되는 것이지요. A2:A12 의 범위중 A2 와 일치하면 참을 반환합니다. 1 을 반환한 것이지요.

($E$2:$E$12>E2) 부분은 순위를 구하기 위해 E2보다 큰 값이 E2:E12 의 범위에 있는지를 찾는 것입니다.
역시 결과는 논리값 TRUE, FALSE 즉 1, 0 이 반환됩니다. E2 의 경우는 이보다 큰 값이 없기에 0을 반환하겠지요.

이 두 부분 수식을 * 으로 연결하게 되면 조건과 조건을 AND 연산을 한 꼴이 됩니다.
즉 1*0  이 되고 SUM 함수의 결과는 0 으로 반환됩니다. 여기에 +1 을 하여 1 위를 얻어낸 것입니다.

G3 의 값을 살펴보면 G3의 수식은 상대 참조한 수식 부분만 변하여 다음과 같습니다.
=SUM(($A$2:$A$12=A3) * ($E$2:$E$12>E3)) + 1

A3, 즉 1반인 값들 중 E3의 값보다 큰 값은 없습니다. SUM 은 0을 반환합니다. +1 로 인해 1위가 됩니다. 1반의 1위가 되는 것이지요.

그럼 2위나 3위는 어떻게 결정될까요. 2반의 3위인 G8 셀의 수식 결과가 결정되는 과정을 살펴봅니다.
=SUM(($A$2:$A$12=A8)*($E$2:$E$12>E8))+1

역시 수식에서 변화는 A8, E8 로 바뀐 것 말고는 없습니다. 이들만이 엑셀 상대참조를 하였기 때문이지요.
먼저 SUM 함수로 합을 구합니다. 그런데 그 인수로 위에서 계속 설명 드린 두 개의 조건의 곱이 있습니다.

위 SUM 수식이 의미하는 것은 범위와 셀 값을 비교한 배열 수식입니다. 범위 각각의 셀과 셀을 비교하여 결과를 구하는 것이지요.

(A2=A8)*(E2>E8) +         결과는 0*1=0
(A3=A8)*(E3>E8) +         결과는 0*1=0
(A4=A8)*(E4>E8) +         결과는 1*1=1
(A5=A8)*(E5>E8) +         결과는 0*1=0
(A6=A8)*(E6>E8) +         결과는 0*1=0
(A7=A8)*(E7>E8) +         결과는 0*1=0
(A8=A8)*(E8>E8) +         결과는 1*0=0
(A9=A8)*(E9>E8) +         결과는 1*1=1
(A10=A8)*(E10>E8) +      결과는 0*1=0
(A11=A8)*(E11>E8) +      결과는 0*1=0
(A12=A8)*(E12>E8)         결과는 0*1=0

각 결과들의 합은 2가 되고 여기에 1을 더합니다. 최종결과는 3 이 되는 것입니다.

이렇게 그룹별 순위를 구할 수 있네요. G2셀의 수식 대신 다음의 수식을 사용하셔도 좋습니니다. 이 수식은 G2 수식의 순위계산 부분을 이상으로 비교하는 것입니다. 그렇게 되면 뒤에 +1이 필요 없어집니다.
=SUM(($A$2:$A$12=A2) * ($E$2:$E$12>=E2))
물론 다른 방법도 있을 수 있습니다.
.....

참고 - 논리식의 결과를 명확히 숫자로 바꾸기

논리식의 결과는 TRUE 혹은 FALSE 입니다. 이 논리식이 다른 함수식에 포함되어 있다면 이는 수치로 변환되어 계산됩니다. 그래서 위의 수식에는 전혀 상관이 없습니다. 하지만 다음의 예를 보세요.

=SUM((10=10), 2)
=SUM(A1, 2)

A1 셀에 =(10=10) 의 수식이 입력되어 있어도 위의 두 수식은 다른 결과를 반환합니다. 위의 수식은 3을 그 아래의 수식은 2를 반환합니다. 함수식에 포함된 논리식과 참조된 논리식은 다른 결과를 가져오는 것입니다.
그래서 논리값 TRUE, FALSE 를 숫자 1, 0 으로 명확히 변환합니다.
이때 사용되는 함수는 N() 이란 인수를 숫자로 변환하는 함수입니다. TRUE는 1로, FALSE는 0으로, 문자열은 0, 날짜는 날짜 일련번호, 시간은 시간 일련번호로 변환됩니다.

위의 수식에 이 함수를 적용해 봅니다. 물론 위의 수식은 문제가 없습니다.
=SUM(N($A$2:$A$12=A2)*N($E$2:$E$12>E2))+1

위에서 사용된 엑셀 배열 수식은 컴퓨터활용능력 1급의 범위 입니다. 어렵게 느껴질수 있습니다.

[엑셀 활용] 엑셀 배열 수식 배우기 - 먼저 맛보기
[엑셀 활용] 엑셀 배열 수식 배우기 - 사용하기
[엑셀] 상대참조와 절대참조,혼합참조의 차이를 살펴봅니다

마늘빵.