엑셀 배열 수식 이용해서 반별 성적 순위 매기는 방법

엑셀 배열 수식 이용해서 반별 성적 순위 매기는 방법

 

 환경: Microsoft Excel 2010

 

이번 예제는 배열 수식을 이용해서 반 그룹별로 체력장 순위를 매기는 방법에 대해 알아 보겠습니다. 순위를 매길 때 Rank 함수를 사용하는 것이 아니라 SUM 함수를 이용할 것입니다.

 

▼ 아래 그림과 같이 반별로 체력장 점수가 나누어져 있습니다. 반이라는 조건이 없다면 바로 Rank 함수를 사용하시면 되겠죠. 

 

▼ 이런 두 가지 조건에 해당하는 값을 구하기 위해 배열수식을 사용하였습니다. 아래 수식 테두리는 중괄호({}) 둘러 쌓여 있습니다. 배열수식을 뜻하는 것인데 중괄호({}) 가 없다면 식이 작동하지 않습니다. 그럼 식을 하나씩 분해해서 설명해 보겠습니다.

 

{=SUM((G3<$G$3:$G$11)*(A3=$A$3:$A$11))+1}

 

l  SUM() + 1 : SUM 함수로 구한 값에 +1 을 합니다.

l  (G3<$G$3:$G$11)*(A3=$A$3:$A$11) : 첫 번째 괄호와 두 번째 괄호값을 곱합니다.예를 들어 첫 번째 괄호 값이 TRUE 이고 두 번째 괄호 값이 FALSE 이면 1*0 = 0 이므로 0 이 됩니다. 1 이 나오려면 TRUE * TRUE 가 되야 되겠죠. 배열수식을 사용하였으므로 각 셀마다 조건을 측정해서 참과 거짓을 판단하게 됩니다. 그러니까 {TRUE, TRUE, FALSE, FALSE… } 가 되는 것이죠. 두 번째 괄호에 있는 식도 마찬가지 입니다. 두 괄호값이 곱해지면 다음과 같은 배열이 나오게 되겠죠. {1, 1, 1, 0, 0, 0 ….} 이렇게 나온 배열을 SUM 함수로 합치는 것입니다.

l  (G3<$G$3:$G$11) : G3 에 값을 영역과 비교해서 TRUE 혹은 FALSE 를 판단합니다. 이렇게 판단한 값은 1 과 0 이 되는 것이죠.

l  (A3=$A$3:$A$11) : A3 에 해당하는 동일한 반명이 있는지 체크해서 배열값을 만들게 됩니다. {TRUE, FALSE, TRUE… } 처럼되겠죠.

 

▼ 식을 다 만들었다면 배열수식을 적용하기 위해 Ctrl + Shift + Enter 눌러야 합니다. 수식에 중괄호 {} 가 들어간 것을 확인하고 첫 번째 셀을 드래그해서 모든 등수 값을 계산합니다.

 

 

▼ 이제 등수 값을 계산했으면 제대로 되었는지 확인하기 위해 필터를 적용해 보겠습니다. 홈 탭 > 정렬및필터 리본메뉴로 가서 머리글에 필터를 적용합니다.

 

▼ 머리글에 필터를 적용하고 반에서 1반만 체크합니다. 그리고 오른쪽에 등수를 확인해 보시기 바랍니다. 총점에 맞게 등수가 매겨 진 것을 확인할 수 있습니다. 이상으로 배열수식을 이용해서 두 가지 이상의 조건에 적용하는 방법에 대해 알아 보았습니다.