LARGE와 SMALL 함수에 이어 RANK, RANK.EQ, RANK.AVG 함수를 알아보겠습니다.
이 세 함수는 기능상 LARGE와 SMALL과 비슷하지만, 개념은 반대인 함수들입니다. LARGE와 SMALL 함수가 순위별로 큰 값, 작은 값은 찾는 함수였다면, RANK, RANK.EQ, RANK.AVG는 큰 값, 작은 값에 따른 순위를 찾는 함수입니다.
그렇다면 세 함수의 인수들도 대략 LARGE나 SMALL과도 비슷할 거라고 예측할 수 있겠죠. LARGE와 SMALL은 "범위(array)"와 "순위(k)"를 인수로 가졌습니다. 그럼, RANK, RANK.EQ, RANK.AVG는 무엇을 인수로 가져야 할까요? 범위 중 순위를 찾는 것이니 일단 "범위"는 인수로 필요할 것입니다. 다음 순위를 찾는 것이니 일단 "순위(k)"를 인수로 가질 순 없을 것입니다. 순위를 알고 있다면 반환할 일은 없으니까요. 그럼, 무엇을 알아야 순위를 반환할 수 있을까요? 당연히 값을 알아야 범위 중 순위를 반환할 수 있습니다. 즉 "값"도 인수로 필요하다는 것이죠. 이런 식으로 필요한 인수를 유추하셨다면 성공하신 겁니다.
자세한 함수 정리는 일단 미루고 그러면 왜 함수가 3가지인지부터 알아보겠습니다.
모든 프로그램이 그렇지만 오랜 기간 사용하다 보면 사용자들은 기능상 새로운 요구가 생기가 됩니다. 그럼, 프로그램 개발자들은 그걸 반영하여 새로운 버전을 내놓죠. 엑셀도 마찬가지입니다. 해가 지날 때마다 오류를 수정하고 새 기능을 추가하여 새 버전을 내놓고 있습니다. 그러다 보니 자연스럽게 잘 쓰지 않거나 문제가 되는 기능들은 퇴화하여 사라지기도 합니다. 바로 RANK와 RANK.EQ가 그런 사이입니다.
RANK.EQ 함수는 엑셀 2010버전 부터 추가되었다고 합니다. 그전에는 동일한 결과를 얻기 위해서 RANK 함수를 사용했습니다. 즉 동일한 함수라는 얘기입니다. 동일한 함수를 뭐 하러 또 만들었을까요? 이제부터는 개인적인 생각이지만, 아마도 RANK.AVG가 생겼기 때문이라 생각합니다. 순위를 반환하는 방식을 EQ와 AVG로 나눴기 때문입니다. 여기서 EQ는 EQUAL을, AVG는 AVERAGE를 의미합니다. 즉 동일하게 순위를 구하거나 평균으로 순위를 구하겠다는 뜻입니다.
EQ = EQUAL(동일하게), AVG=AVERAGE(평균으로)
그럼, 무엇을 동일하게 하겠다는 것인지, 무엇을 평균으로 하겠다는 것인지가 문제입니다.
여기서 이 무엇은 바로 "동일 값"입니다. 범위 중 동일한 값이 있을 때 높은 순위로 동일하게 반환할 것인지, 순위 평균으로 반환할 것인지를 구분하는 것입니다.
무슨 말인지 아직 이해되지 않으시죠? 한국인의 정서에서는 사실 이해하기 쉽지 않습니다. 저도 한참 생각하고 "왜?"라고 반문했으니까요.
그럼, 예제를 통해 본격적으로 알아보겠습니다. 이 글을 모두 보신 뒤에는 분명 무슨 말인지 이해하실 수 있을 것으로 생각합니다.
RANK수 목록 내에서 지정한 수의 크기 순위를 반환합니다.
RANK(number,ref,[order])| number | 순위를 구하려는 수 |
| ref | 숫자 목록에 대한 참조 범위. 숫자 이외의 값은 무시됩니다. |
| order | [선택] 순위 결정 방법. 0 또는 생략 시 내림차순, 1은 오름차순 정렬 |
RANK.EQ수 목록 내에서 지정한 수의 크기 순위를 반환합니다. 동일한 순위가 있으면 상위 순위가 반환됩니다.
RANK.EQ(number,ref,[order])| number | 순위를 구하려는 수 |
| ref | 숫자 목록의 배열 또는 참조. 숫자 이외의 값은 무시됩니다. |
| order | [선택] 순위 결정 방법. 0 또는 생략 시 내림차순, 1은 오름차순 정렬 |
RANK.AVG수 목록 내에서 지정한 수의 크기 순위를 반환합니다. 동일한 순위가 있으면 평균 순위가 반환됩니다.
RANK.AVG(number,ref,[order])| number | 순위를 구하려는 수 |
| ref | 숫자 목록의 배열 또는 참조. 숫자 이외의 값은 무시됩니다. |
| order | [선택] 순위 결정 방법. 0 또는 생략 시 내림차순, 1은 오름차순 정렬 |
함수 정리는 위와 같습니다. 세 함수 모두 동일한 인수를 가지고 있으니 사용 방법 역시 같습니다. 요약하면, ref 범위에서 number 값을 찾고 order를 통해 내림차순 혹은 오름차순으로 순위를 반환한다는 것입니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
1. 사용법
예제 파일을 열고 RANK 함수부터 적용해 보겠습니다.
- G5 셀을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작(=)하고 함수명을 작성합니다. ⇒ =RANK(
그런데 이때 함수 힌트를 보면 다른 함수와 달리 경고 아이콘이 보입니다. 그리고 사용 힌트를 보면, "이 함수는 Excel 2007 및 이전 버전과 호환성을 위해 제공되는 함수"라고 보입니다. 이 얘기는 Excel 2007 이전 버전에서는 RANK.EQ나 RANK.AVG가 없어서 RANK로 문서를 만들었을 테니 RANK 함수를 사용한 문서도 볼 수 있게 하겠다는 의미입니다. 다시 말해 RANK를 사용할 수는 있지만 Excel 2010 버전 이상에서는 이 함수는 사용하지 말라는 경고입니다. 일단 무시하고 사용하겠습니다.
- 함수의 첫 번째 인수 힌트(number)를 확인하고 순위를 정할 값을 가진 셀을 참조합니다. ⇒ =RANK(F5
- 쉼표로 구분, 두 번째 인수 힌트(ref)를 확인하고 만족도 범위(F5:F16)를 클릭 드래그하여 참조하고 f4키를 눌러 절대 참조로 변경합니다. ⇒ =RANK(F5,$F$5:$F$16
- 절대 참조로 변경하는 이유는 자동 채우기를 진행했을 때 범위까지 따라 내려가지 않게 고정한 것입니다. - 쉼표로 구분, 세 번째 인수 힌트(order)를 확인하고 "0 내림차순"으로 선택하고 엔터(닫힌 괄호 생성 후 셀 완성) ⇒ =RANK(F5,$F$5:$F$16,0)
- 다시 수식을 완성한 G6 셀을 클릭하여 선택하고 G16 셀까지 자동 채우기(우측 하단에서 클릭 드래그)를 진행합니다.
- 최종, 자동 채우기 옵션 버튼을 클릭하고 "서식 없이 채우기"를 클릭합니다.
결과, 만족도 순위가 정상 반영됩니다. 절대 참조로 변경하는 이유가 이해되지 않는다면 아래 강좌를 다시 확인하시고 돌아와 주세요!
RANK.EQ, RANK.AVG도 동일한 방식이니 사용법 설명은 넘어가겠습니다. 함수명만 바꾸고 다시 자동 채우기를 진행하면 됩니다.
다음, RANK.EQ입니다. 진행해 보셨다면 아셨겠지만 RANK.EQ은 완벽히 RANK와 동일한 결과를 반환합니다. 위에서 RANK는 "Excel 2007 및 이전 버전과 호환성을 위해 제공되는 함수"라는 것을 확인했습니다. 2010 버전 이상을 사용하면 RANK를 사용하지 말고, RANK.EQ를 사용하라는 얘기입니다.
그렇다고 RANK 함수를 바로 사용 못 하게 할 수는 없습니다. 왜냐하면 이미 RANK로 만든 엑셀 문서가 있기 때문입니다. RANK를 사용하지 못하게 하면 RANK로 만든 문서는 더 이상 확인할 수 없겠죠. 그러나 언젠가 이 녀석들은 사용 불가능하게 될 확률이 높습니다.
엑셀에는 이렇게 사용을 권장하지 않는 함수들이 종종 있습니다. 함수 작성 시 위에서도 확인했지만, 함수 힌트에서 경고 표시(
)가 있는 녀석들입니다.
이런 경고가 붙은 함수는 반드시 도움말을 확인하고 대체된 함수로 사용하는 것이 좋습니다. 언젠가는 사용을 할 수 없게 될 것입니다.
2. RANK.EQ와 RANK.AVG의 차이
이제 남은 것은 RANK.EQ와 RANK.AVG의 차이입니다. "동일하게"와 "평균으로"의 차이만 확인하면 되겠습니다. 확인을 위해 몇 가지 작업을 하겠습니다.
- 만족도 순위 옆으로 "EQ"와 "AVG"항목을 입력하여 만들고, 만족도 순위(G4:G16) 범위로 서식 복사합니다.
- 확인을 위해 정렬을 변경하겠습니다. "만족도" 항목의 아무 셀이나 클릭하여 선택합니다.
- "홈 탭 > 정렬 및 필터 > 숫자 내림차순 정렬"을 클릭합니다. ⇒ 만족도 값이 내림차순으로 정렬되고 자연히 순위 또한 정렬됩니다.
- "AVG" 항목의 데이터는 소수점 첫째 자리까지 확인해야 합니다. 이유는 결과를 보고 다시 설명하겠습니다. 그러기 위해 일단 범위(I5:I16) 선택합니다.
- "자릿수 늘림" 버튼을 한 번 클릭하여 소수점 첫째 자리까지 표시하게 표시형식을 변경합니다.
만약 "서식 복사" 사용법을 모르신다면 아래 강좌를 확인하고 돌아와 주시길 바랍니다.
그리고 정렬에 대해 모르신다면 역시 아래 강좌를 보시고 돌아와 주세요.
뭘 하려는지 대략 아셨겠죠? 이제 EQ 항목은 RANK.EQ로, AVG 항목은 RANK.AVG 함수로 만들어 줍니다.
RANK 함수에서 했던 방식 그대로 하면 되니, 작성법은 넘어가고 결과만 확인하겠습니다.
RANK.EQ와 RANK.AVG는 보시는 바와 같이 함수의 첫 번째 인수(number)가 동일할 때 차이가 납니다.
위에서 80점은 왕조연과, 원호가 준 점수입니다. 굳이 순위를 따지면 4위와 5위가 동일한 점수죠. 이 경우 RANK.EQ는 둘 다 4위로 처리합니다. 같은 점수이니 당연합니다.
하지만 RANK.AVG는 4위와 5위의 평균 즉 (4+5)/2 값인 4.5로 처리합니다. 이게 RANK.AVG가 동일 값을 순위로 처리하는 방식입니다. 제가 "왜?"라고 생각하는데 동의가 되나요?
다음 75점도 보겠습니다. 이것도 굳이 순위를 매기자면, 6위, 7위, 8위입니다. 3명이 동일한 점수를 주었죠. 동일 점수이니 RANK.EQ는 상위 순위인 6위를 모두 반환합니다. 반면 RANK.AVG는 6, 7, 8위의 평균 (6+7+8)/3 값인 7위로 처리합니다. 즉 순위를 평균으로 처리한다는 것이죠.
추가로 AVG 범위 표시 형식을 소수점 첫째 자리로 변경한 이유도 설명하겠습니다. 보셨듯이 RANK.AVG는 순위를 평균으로 나타내기 때문에 동일 값이 몇 개냐에 따라 소수점 몇 자리까지 표시돼야 알 수 있습니다. 만약 소수점을 표시하지 않는 숫자 표시 형식에서는 1의 자리까지 모두 반올림하여 표시합니다. 즉 위에서 소수점 자릿수를 표시하지 않았다면 4.5는 5로 반올림해 버립니다. 4위는 없고 5위가 2개가 되는 것이죠.
설명은 끝났습니다. RANK.EQ와 RANK.AVG는 동일 값의 순위를 상위 순위로 동일하게 할 것인지, 평균으로 할 것인지를 구분하는 함수입니다.
그런데 저는 아직 RANK.AVG를 사용해야 하는 경우를 찾지 못했습니다. 혹시라도 알고 계시다면 저도 너무 궁금하니 꼭 댓글 남겨주셨으면 좋겠습니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
의견글 입니다.
()
