동점 다음 순위를 건너뛰지 않고 그룹별 순위를 매기려면?
동점 다음에도 1·1·2·3처럼 순위가 이어지게 계산하세요. 그룹마다 점수를 중복 없이 모은 뒤 각 점수 이상인 값이 몇 종류인지 세면 됩니다.
동점자에게 같은 순위를 주고 다음 순위를 건너뛰지 않는 1·1·2·3 방식의 순위를 구해 보겠습니다. 한 그룹의 점수가 90·90·70·50이라면 점수 종류는 90·70·50입니다. 70점 이상인 점수는 90과 70 두 종류이므로 70점은 2위입니다. JOIN SHEET에서는 이 원리로 점수 목록을 만들어 원본에 연결한 뒤 순위를 계산합니다. 여러 단계를 거치는 작업이며 전용 순위 버튼을 사용하는 것은 아닙니다.
Group과 Entry로 항목을 구분합니다
아래 가상 점수표에서는 점수가 높을수록 높은 순위입니다. Group은 그룹, Entry는 그룹 안에서 각 항목을 구분하는 값입니다. 동명이인의 이름을 그대로 Entry로 쓰면 뒤의 요약에서 한 항목으로 묶일 수 있으므로, 서로 구별할 수 있는 식별자를 준비하세요.
| Group | Entry | Score |
|---|---|---|
| G1 | A | 90 |
| G1 | B | 90 |
| G1 | C | 70 |
| G1 | D | 50 |
| G2 | E | 70 |
| G2 | F | 50 |
G1의 90점 두 항목은 모두 1위이고 70점은 2위입니다. 70점인 G2의 E는 자기 그룹에서는 1위입니다. 전체 표를 한 번 정렬하는 것으로는 이 그룹별 순위 칼럼이 생기지 않습니다.
그룹별로 점수 종류를 한 번씩만 남기기
- 점수표를 불러와 Score가 숫자인지 확인하고 결과 기준으로 선택합니다. 빈 점수는 대상에서 제외하되, 원본과 점수 목록에 같은 조건을 적용합니다.
- ‘결과 정리’에서 ‘요약’을 누르고 ‘그룹화 기준’에서 Group과 Score를 하나씩 선택합니다. ‘집계 방식’의 기본값 ‘개수’는 유지하고 ‘결과 항목 이름’에 Occurrences를 입력하세요. ‘적용’을 눌러 아래 5행을 확인합니다.
- 결과를 Excel로 저장합니다. 이 점수 목록을 원본에 연결하려면 새 시트로 다시 불러와야 합니다. 아래에서는 불러온 목록을 Levels라고 부릅니다. Occurrences는 자료가 잘 묶였는지 확인하는 값이며 최종 순위에서는 사용하지 않습니다.
| Group | Score | Occurrences |
|---|---|---|
| G1 | 90 | 2 |
| G1 | 70 | 1 |
| G1 | 50 | 1 |
| G2 | 70 | 1 |
| G2 | 50 | 1 |
각 항목의 점수 이상인 값이 몇 종류인지 세기
새 작업에 원본 Scores와 점수 목록 Levels를 불러옵니다. Group을 기준으로 ‘일치하는 데이터만’으로 연결하고 Scores를 결과 기준으로 고르세요. 각 항목에 같은 그룹의 점수 목록이 붙습니다. Levels의 Group과 Occurrences는 숨기되, 비교에 사용할 Score는 표시합니다.
‘결과 정리’에서 다음 순서로 설정합니다.
- ‘계산’으로 Levels의 Score − 원본 Score를 ScoreGap으로 만듭니다. 예제에서는 중복 칼럼 이름 때문에 두 번째 점수가 Levels_Score로 표시됩니다.
- ‘필터’에서 ScoreGap이 0 ‘이상’인 행을 남깁니다. 같은 점수도 포함해야 가장 높은 점수의 순위가 1이 됩니다.
- ‘요약’을 추가하고 ‘그룹화 기준’에서 원본의 Group·Entry·Score를 하나씩 선택합니다. ‘집계 방식’은 ‘개수’를 유지하고 ‘결과 항목 이름’에 DenseRank를 입력하세요.
- ‘정렬’을 한 번 추가한 뒤 같은 단계의 ‘정렬 기준 추가’로 Group·DenseRank·Entry를 이 순서대로 넣습니다. 모두 ‘오름차순’으로 설정한 뒤 ‘적용’을 누릅니다.
점수가 같은 두 항목은 같은 순위입니다
| Group | Entry | Score | DenseRank |
|---|---|---|---|
| G1 | A | 90 | 1 |
| G1 | B | 90 | 1 |
| G1 | C | 70 | 2 |
| G1 | D | 50 | 3 |
| G2 | E | 70 | 1 |
| G2 | F | 50 | 2 |
G1의 D는 50점입니다. 같은 그룹에서 50점 이상인 값은 50·70·90의 세 종류이므로 3위입니다. 점수 목록에 90점을 두 번 남기면 D가 4위로 계산됩니다. 이런 결과가 나오면 첫 요약의 그룹화 기준에 Group·Score만 넣었는지 확인하세요.
연결 직후의 행 수는 그룹마다 ‘원본 항목 수 × 중복 없는 점수 수’로 늘어납니다. 이 예제는 4×3+2×2=16행이지만 그룹이 커지면 결과도 빠르게 커집니다. 작은 자료부터 확인하고, 자료가 많다면 전용 순위 기능이 있는 도구도 검토하세요. 이 방법에서는 동점자에게 같은 번호를 주며 동점자 사이의 우열은 정하지 않습니다.
내 파일로 시작해 보세요
JOIN SHEET에서 시작하기 ↗Excel·CSV 작업은 PC에서 이용해 주세요.
새 창에서 열리며 작업 중인 데이터를 바꾸지 않습니다.
예제 확인일:
출처
How To Effectively Dense Rank Groups in a Table Using Powerquery의 질문을 참고해 별도의 가상 데이터로 구성했습니다.
