순위 목록은 성과, 품질 등 특정 기준에 따라 기업이나 개인의 순위를 매기는 데 널리 활용됩니다. 이 글에서는 엑셀에서 동적 TOP 10 목록을 만드는 다양한 방법을 소개합니다. 동적 목록의 가장 큰 장점은 원본 데이터가 변경되면 결과도 자동으로 업데이트된다는 점입니다. 매번 손으로 순위를 다시 정리하는 번거로움을 없애 주죠.
엑셀에서 동적 TOP 10 목록을 만드는 8가지 방법
함수 조합부터 조건부 서식, 피벗 테이블까지 여러 가지 방법을 단계별로 살펴보겠습니다. 예제로는 학생들의 점수 데이터 세트를 사용하며, 이 데이터를 바탕으로 동적 TOP 10 목록을 만들어 보겠습니다.
1. LARGE 함수로 TOP 10 목록 만들기
LARGE 함수는 지정한 범위에서 k번째로 큰 값을 찾아 반환합니다.
먼저 데이터 옆 열에 1~10까지 순번을 입력해 둡니다.
상위 10개 점수를 구한 뒤, 해당하는 학생 이름을 가져오는 순서로 진행합니다.
1단계:
- LARGE 함수와 ROW 함수를 조합한 수식을 사용합니다.
- G5 셀에 아래 수식을 입력합니다.
=LARGE($C$5:$C$18,ROW(F5)-ROW($F$4))
2단계:
- Enter 키를 누른 후 채우기 핸들을 아래로 드래그합니다.
이렇게 하면 상위 10개 점수를 구할 수 있습니다. 같은 작업은 ROWS 함수로도 가능합니다.
대안 수식:
동일한 결과를 얻는 또 다른 수식입니다.
사용 방법:
- G5 셀에 아래 수식을 입력합니다.
=LARGE($C$5:$C$18,ROWS(C$5:C5))
- Enter 키를 누르고 채우기 핸들을 끝까지 드래그합니다.
이제 상위 10개 점수에 해당하는 학생 이름까지 표시하고 싶다면 아래와 같이 진행하세요.
사용 방법:
- F5 셀에 아래 수식을 입력합니다.
=INDEX($B$5:$B$18,MATCH(G5,$C$5:$C$18,0))
- Enter 키를 누른 후 채우기 핸들을 아래로 드래그합니다.
점수와 함께 상위 10명의 이름을 모두 확인할 수 있습니다.
2. INDEX·MATCH·LARGE 함수 조합으로 TOP 10 목록 만들기
이번에는 INDEX, MATCH, LARGE 함수를 하나의 수식으로 결합해 보겠습니다. 별도의 보조 열 없이 상위 10명의 이름을 바로 구할 수 있습니다.
1단계:
- F5 셀로 이동합니다.
- 아래 수식을 입력합니다.
=INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0))
2단계:
- Enter 키를 누르고 채우기 핸들을 아래로 드래그합니다.
보조 열의 도움 없이 상위 10명의 이름을 바로 얻을 수 있습니다.
수식 분석:
- LARGE($C$5:$C$18,E5)
E5 셀의 순번 값에 따라 범위에서 k번째로 큰 점수를 반환합니다.
결과: 96
- MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0)
해당 점수가 범위 내 몇 번째 위치에 있는지 위치 번호를 반환합니다.
결과: 10
- INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0))
그 위치에 있는 이름을 최종적으로 반환하여 상위 10명의 명단을 완성합니다.
결과: {Jose, Henderson, Gerg, Josef, Aaron, Jose, Jessica, Joe, Rogers, Allisa}
3. 배열 수식으로 동적 TOP 10 목록 만들기
이 방법은 배열 수식을 활용합니다. INDEX, SORT, SEQUENCE 함수의 조합으로, 수식 하나만 입력하면 이름과 점수가 한꺼번에 표시됩니다.
1단계:
- F5 셀로 이동합니다.
- 아래 수식을 복사해 붙여넣습니다.
=INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2})
2단계:
- Enter 키를 누릅니다.
수식 하나만으로 이름과 점수가 동시에 나타납니다. 채우기 핸들을 드래그할 필요도 없습니다.
수식 분석:
- SEQUENCE(10)
1부터 10까지의 연속된 숫자 배열을 생성합니다.
결과: {1,2,3,4,5,6,7,8,9,10}
- SORT(B5:C18,2,-1)
두 번째 열(점수)을 기준으로 데이터를 내림차순 정렬합니다.
결과: {Jose 96, Henderson 95, Gerg 89, Josef 84, Aaron 80, Jose 71, Jessica 70, Joe 65, Rogers 61, Allisa 60, Joe 57, Allisa 49, John 45, Mitchel 40}
- INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2})
정렬된 데이터 중 처음 10행의 이름과 점수 두 열을 반환합니다.
결과: {Jose 96, Henderson 95, Gerg 89, Josef 84, Aaron 80, Jose 71, Jessica 70, Joe 65, Rogers 61, Allisa 60}
4. XLOOKUP 함수로 TOP 10 목록 디자인하기
XLOOKUP 함수는 지정한 범위나 배열에서 값을 검색하고, 일치하는 항목에 대응하는 결과를 반환합니다.
여기서는 XLOOKUP 함수와 LARGE 함수를 조합하여 상위 10명의 이름 목록을 구해 보겠습니다.
1단계:
- F5 셀에 아래 수식을 입력합니다.
=XLOOKUP(LARGE($C$5:$C$18,E5),$C$5:$C$18,$B$5:$B$18)
2단계:
- Enter 키를 누릅니다.
- 이후 채우기 핸들을 아래로 드래그합니다.
※ 참고: XLOOKUP, SORT, SEQUENCE, FILTER 함수는 Microsoft 365 및 Excel 2021 이상 버전에서 사용할 수 있습니다.
5. SORT와 FILTER 함수 조합으로 TOP 10 목록 만들기
FILTER 함수는 주어진 조건에 맞는 값들만 걸러내는 기능을 제공합니다.
이번에는 SORT 함수와 FILTER 함수를 LARGE 함수와 결합하여 TOP 10 목록을 만들어 보겠습니다.
1단계:
- F5 셀로 이동합니다.
- 아래 수식을 입력합니다.
=SORT(FILTER(B5:C18,C5:C18>=LARGE(C5:C18,10)),2,-1)
2단계:
- Enter 키만 누르면 됩니다.
이 방법 역시 학생의 이름과 점수를 한 번에 얻을 수 있습니다.
6. 중복 데이터가 있을 때 동적 TOP 10 목록 만들기
동일한 점수가 반복되는 중복 데이터가 있다면, 아래 방법으로 중복을 건너뛰면서 TOP 10 목록을 만들 수 있습니다.
1단계:
- F5 셀로 이동합니다.
- 아래 수식을 입력합니다.
=INDEX($B$5:$B$18, MATCH(1, ($C$5:$C$18=LARGE($C$5:$C$18, E5)) * (COUNTIF(F$4:F4, $B$5:$B$18)=0), 0))
2단계:
- Enter 키를 누른 후 채우기 핸들을 드래그합니다.
COUNTIF 부분이 이미 목록에 등장한 이름을 제외해 주기 때문에, 같은 이름이 중복 출력되지 않고 서로 다른 상위 10명의 이름만 표시됩니다.
7. 조건부 서식·필터·정렬 도구로 동적 TOP 10 목록 만들기
함수 대신 여러 도구를 조합해서도 TOP 10 목록을 만들 수 있습니다. 조건부 서식, 필터, 정렬을 차례로 활용하는 방법입니다.
1단계:
- C5부터 C18 셀을 선택합니다.
- 리본 메뉴에서 조건부 서식을 클릭합니다.
- 앞/뒤 규칙에서 상위 10개 항목을 선택합니다.
2단계:
- 대화상자가 나타나면 값이 10으로 설정되어 있는지 확인합니다.
- 확인을 누릅니다.
3단계:
- C4부터 C18 셀을 선택합니다.
- 마우스 오른쪽 버튼을 클릭합니다.
- 필터 목록에서 선택한 셀 색으로 필터를 선택합니다.
4단계:
- 색 기준 필터 옵션을 클릭합니다.
상위 10개 데이터가 필터링되어 표시됩니다. 이제 정렬을 해 보겠습니다.
5단계:
- 다시 마우스 오른쪽 버튼을 클릭합니다.
- 정렬 목록에서 큰 순서대로 정렬을 선택합니다.
6단계:
- 선택 영역 확장을 선택합니다.
- 정렬 버튼을 누릅니다.
깔끔하게 정렬된 TOP 10 데이터가 완성됩니다.
8. 피벗 테이블로 TOP 10 목록 만들기
마지막으로 피벗 테이블을 이용해 TOP 10 목록을 만들어 보겠습니다.
1단계:
- 삽입 탭으로 이동합니다.
- 피벗 테이블 목록에서 테이블/범위에서를 선택합니다.
2단계:
- 데이터 세트에서 사용할 범위를 지정합니다.
- 확인을 누릅니다.
3단계:
- 피벗 테이블 필드에서 이름(Name)과 점수(Mark)를 체크합니다.
4단계:
- 행 레이블로 이동합니다.
- 값 필터에서 Top 10을 선택합니다.
5단계:
- 대화상자가 나타나면 확인을 클릭합니다.
TOP 10 목록이 완성되었습니다. 다만 데이터가 정렬되지 않은 상태라면, 행 레이블의 정렬 옵션을 이용해 점수 기준 내림차순으로 정리하면 됩니다.
마무리
지금까지 엑셀에서 동적 TOP 10 목록을 만드는 8가지 방법을 알아보았습니다. 간단한 LARGE 함수 조합부터 배열 수식, 조건부 서식과 피벗 테이블까지 상황에 맞는 방법을 골라 활용해 보세요. 데이터가 바뀌어도 자동으로 갱신되는 동적 목록으로 업무 효율을 높일 수 있습니다.
함께 읽으면 좋은 글
- 엑셀 OFFSET 함수로 동적 드롭다운 목록 만들기(3가지 방법)
- 엑셀 VBA로 동적 드롭다운 목록 만드는 방법