- 최초 작성일: 2000-06-14
- 최종 수정일: 2026-09-29
- 조회수: 24 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: RANK 함수 응용
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
어떤 분이(아마 학생인듯...) 아주 머리아픈 질문을 한가지 해 오셨습니다. 처음에는 '학생의 질문이라 별로 보편적인 질문은 아니겠다' 싶었으나 자세히 살펴보니 그게 아니더군요. 여러 분야에서 응용이 될 것 같아서 소개를 해 드립니다.
질문 하나
안녕하세요. 홈페이지가 너무도 깔끔하고 좋은 내용으로 가득하군요. 많은 도움을 받고 있습니다.
전 지금 Exceller님의 전체 강좌파일을 다 다운받은 다음 하나하나 보고 있는데요… 지금 10회째를 보고
있습니다. Rank() 함수에 대해 보고 있는데 혹시 총점이 같을 경우 국어점수가 더 높은 사람이 더 높은
등급을 주려고 하는데 잘 안되는군요.
무슨 방법이 없을까요? 답변을 기다리겠습니다. 수고하세요.
- 최신 글을 불러오는 중...
RANK 함수 응용
핵심 요약
- RANK만 사용하면 총점이 같은 학생은 같은 석차가 되므로 추가 기준으로 동점을 구분해야 합니다.
- RANK 결과에 총점이 같고 국어 점수가 더 높은 사람의 수를 배열수식으로 더하면 동점 석차를 세분화할 수 있습니다.
- 조건 배열에서 TRUE와 FALSE가 1과 0으로 계산되고 곱셈이 AND 조건처럼 작동하는 원리를 활용합니다.
무슨 질문인지 이해가 가시는지? 아래와 같은 성적표가 있습니다.
| 성명 | 국어 | 총점 | 석차 |
| 갑동이 | 90 | 290 | 3 |
| 을동이 | 95 | 290 | 3 |
| 병동이 | 100 | 295 | 1 |
| 정동이 | 80 | 270 | 8 |
| 무동이 | 85 | 280 | 6 |
| 기동이 | 90 | 275 | 7 |
| 경동이 | 100 | 295 | 1 |
| 신동이 | 75 | 270 | 8 |
| 임동이 | 85 | 270 | 8 |
| 계동이 | 90 | 285 | 5 |
학창시절에 많이 보던 성적표이지요? 여기서 갑동이와 을동이의 총점이 290점으로 같아서 3등, 정동이와 신동이, 임동이의 총점이 같아서 세명이 8등입니다. 이처럼 총점이 같을 경우, 국어 점수가 높은 사람이 더 나은 석차를 받도록 할 수는 없겠느냐? 하는 것이 질문의 내용입니다.
어떻게 하면 좋을까요? 일단은 Rank() 함수를 써야 겠고… 그 다음에는 멍~~ ^^ 저는 아래와 같이 해보았습니다.
| 성명 | 국어 | 총점 | 석차 | 석차2 |
| 갑동이 | 90 | 290 | 3 | {=RANK(D46,$D$46:$D$55)+SUM(($D$46:$D$55=D46)*($C$46:$C$55>C46))} |
| 을동이 | 95 | 290 | 3 | {=RANK(D47,$D$46:$D$55)+SUM(($D$46:$D$55=D47)*($C$46:$C$55>C47))} |
| 병동이 | 99 | 295 | 1 | {=RANK(D48,$D$46:$D$55)+SUM(($D$46:$D$55=D48)*($C$46:$C$55>C48))} |
| 정동이 | 80 | 270 | 8 | {=RANK(D49,$D$46:$D$55)+SUM(($D$46:$D$55=D49)*($C$46:$C$55>C49))} |
| 무동이 | 85 | 280 | 6 | {=RANK(D50,$D$46:$D$55)+SUM(($D$46:$D$55=D50)*($C$46:$C$55>C50))} |
| 기동이 | 90 | 275 | 7 | {=RANK(D51,$D$46:$D$55)+SUM(($D$46:$D$55=D51)*($C$46:$C$55>C51))} |
| 경동이 | 100 | 295 | 1 | {=RANK(D52,$D$46:$D$55)+SUM(($D$46:$D$55=D52)*($C$46:$C$55>C52))} |
| 신동이 | 75 | 270 | 8 | {=RANK(D53,$D$46:$D$55)+SUM(($D$46:$D$55=D53)*($C$46:$C$55>C53))} |
| 임동이 | 85 | 270 | 8 | {=RANK(D54,$D$46:$D$55)+SUM(($D$46:$D$55=D54)*($C$46:$C$55>C54))} |
| 계동이 | 90 | 285 | 5 | {=RANK(D55,$D$46:$D$55)+SUM(($D$46:$D$55=D55)*($C$46:$C$55>C55))} |
참고: 병동이의 국어 점수가 첫 번째 표에서는 100점, 두 번째 표에서는 99점으로 다르게 적혀 있습니다(원문 그대로입니다). 두 번째 표의 석차2 열에는 결과값이 아니라 수식이 표시되어 있으며, 이 표의 값으로 계산하면 갑동이 4, 을동이 3, 병동이 2, 정동이 9, 무동이 6, 기동이 7, 경동이 1, 신동이 10, 임동이 8, 계동이 5가 됩니다. 아래 수식의 셀 주소는 원본 워크시트 기준입니다.
등수에 관계되는 사항이니까 Rank() 함수를 썼고, 그 다음에는 그동안 귀에 못이 박히게 들으셨던 배열수식을 활용하였습니다.(그동안 열심히 공부해 오셨다면 요 힌트에서 '아~!' 하는 감탄사가 나와야 하는데…)
"석차2" 부분에 어떤 공식이 들어 있는지 살펴볼까요?
{=RANK(D46,$D$46:$D$55)+SUM(($D$46:$D$55=D46)*($C$46:$C$55>C46))}① rank(d46,$d$46:$d$55) Rank() 함수에 대해서는 설명을 안드려도 다들 아실 것이고…
② sum(($d$46:$d$55=d46) 여기서부터 배열수식의 적용을 받습니다. d46:d55 영역(총점) 중에서 총점이 같은 것이 있는지를 찾습니다.
이 부분을 마우스로 선택한 후 즉석계산 기능, 즉 <F9>키를 눌러 보세요. {False;False;False;True;False…True;True;False}라고 나타날 것입니다. 즉 총점이 같으면 True를 다르면 False를 각각 나타내는 것입니다. True가 세개가 나타나지요?
③ ($c$46:$c$55)>c46 국어 점수가 현재 레코드보다 큰 것이 있는지를 찾습니다. 이 부분도 위와 같은 방법으로 지정하고 <F9>키를 눌러 보세요. {True…True}가 나타날 것입니다.
이제 감이 잡히십니까?
②에서의 {False;False;…;False}와 ③에서의 {True;True;…;True}는 각각 1:1로 연결(matching)이 됩니다.
예전에 변수에 대해 설명을 드릴 때, 컴퓨터는 어떤 조건값이 True이면 1, False면 0으로 받아들인다고 했는데 기억을 하시는지… 만약 기억에 없으시더라도 그렇게 이해를 하시기 바랍니다. 참이면 1, 거짓이면 0입니다.
이 두 조건이 *로 연결이 되어 있으니까 and조건, 즉 둘다 True일 경우에만 1의 값을 가지는 것이지요. 다시 설명드리자면,
| ① | ② | 결과값 |
| 1 | 1 | 1 |
| 1 | 0 | 0 |
| 0 | 1 | 0 |
| 0 | 0 | 0 |
각각의 조건은 True 아니면 False의 값을 갖게 되는데 *로 연결이 되어 있으니 둘다 true인 경우에만 위의 그림처럼 1의 값을 갖고 나머지는 모두 0의 값을 갖는 것입니다. 여기서 둘다 true라는 것은 "총점이 같으면서 국어점수가 현재 레코드보다 높은 것이 있다"는 얘기가 되지요.(참 친절하게 잘도 설명한다!!!)
이제 이해가 되십니까? 혹시 안되면 메일 주시고… 처음 접하는 부분은 하나도 없지요? 이래서 응용이 중요한 것입니다.
다음 시간에…
2000-06-14
정리 — RANK 함수 응용
| 구분 | 내용 |
|---|---|
| 문제 | 총점이 같을 때 국어 점수가 높은 사람에게 더 높은 석차 부여 |
| 수식 | {=RANK(D46,$D$46:$D$55)+SUM(($D$46:$D$55=D46)*($C$46:$C$55>C46))} |
| RANK 부분 | 총점 기준의 기본 석차 |
| SUM 부분 | 총점이 같으면서 국어 점수가 더 높은 사람의 수를 배열수식으로 계산해 더함 |
| 논리값 | True는 1, False는 0으로 계산되며 *로 연결하면 And 조건 |
| 확인 방법 | 수식의 일부를 선택하고 F9 키로 즉석 계산 |
자주 묻는 질문 (FAQ)
Q1. 총점이 같을 때 국어 점수로 석차를 가르려면 어떻게 하나요?
RANK 함수로 구한 석차에, 총점이 같으면서 국어 점수가 더 높은 사람의 수를 배열수식으로 계산해 더합니다.
Q2. 더해지는 SUM 부분은 어떻게 계산되나요?
총점이 같은지와 국어 점수가 더 높은지를 각각 True 또는 False로 구한 뒤 곱하면, 두 조건이 모두 True인 경우에만 1이 됩니다. 이 값들을 더하면 같은 총점 안에서 자신보다 국어 점수가 높은 사람의 수가 됩니다.
Q3. 배열수식의 중간 결과는 어떻게 확인하나요?
확인할 부분을 마우스로 선택하고 F9 키를 누르면 True와 False로 이루어진 배열이 표시됩니다.
마치며
이미 배운 RANK 함수와 배열수식, 논리값의 성질을 조합하면 새로운 조건도 해결할 수 있으니 응용해 보시기 바랍니다. VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
