Excel에서 동적인 상위 10개 또는 N 리스트를 어떻게 생성합니까?

많은 프로젝트와 비즈니스 프로세스에서 성과나 수치에 따라 개인, 조직, 제품 또는 기타 대상을 순위 매기는 것이 종종 필요합니다. '상위 목록'은 최고 성적을 받은 학생들, 최고의 판매원, 또는 가장 많은 수익을 올린 부서 등 최고 성능을 발휘한 항목들을 강조하는 역할을 합니다. 예를 들어, 학생들의 성적표가 있고, 아래 스크린샷에 나와 있는 것처럼 상위 10명의 점수를 수상, 분석 또는 교육 성과 모니터링을 위해 동적으로 추출하고자 할 수 있습니다. Excel에서 동적인 상위 10개 또는 상위 N 리스트를 생성하면 데이터가 변경됨에 따라 결과가 자동으로 업데이트되어 시간을 절약하고 수동으로 순위를 매길 때 발생하는 오류를 줄일 수 있습니다. 이 가이드에서는 공식, 피벗 테이블 및 VBA 매크로를 포함하여 다양한 실용적인 솔루션을 소개하여 다양한 데이터 분석 요구를 효율적으로 충족할 수 있도록 도와줍니다.

Excel에서 동적인 상위 10개 리스트 만들기

Excel 2019 및 이전 버전에서는 동적인 상위 10개(또는 상위 N) 리스트를 생성하기 위해 상위 값을 추출하는 동시에 이름이나 ID를 추출하는 공식을 결합해야 합니다. 이 솔루션은 데이터가 변경될 때 리스트가 자동으로 업데이트되기를 원하는 경우 널리 사용되며 적합합니다. 다음 작업에서는 클래식 Excel 공식을 사용하여 이를 달성하는 방법을 설명합니다. 이러한 공식은 유연성을 제공하며 특별한 Excel 추가 기능이 필요하지 않지만 설정 과정은 일부 현대적인 동적 배열 함수와 비교했을 때 조금 더 복잡합니다.

동적인 상위 10개 리스트를 만드는 공식

1. 우선 값 범위에서 상위 10개 값을 추출해야 합니다. 빈 셀(예: G2 셀)에 다음 공식을 입력하세요. 공식을 입력한 후, 채우기 핸들을 드래그하여 동적인 상위 10개 값 리스트를 생성하세요. 스크린샷 참조:

2. 다음으로, 상위 값과 관련된 이름(또는 ID)을 표시하기 위해 아래 공식을 F2 셀에 입력하세요. 이는 배열 공식이므로, 입력 후 Ctrl + Shift + Enter를 눌러 확인하세요. 이 공식은 방금 추출한 상위 값에 해당하는 이름을 찾습니다:

3. 첫 번째 결과를 추출한 후, F2 셀에서 공식을 선택하고 필요한 행만큼 채우기 핸들을 드래그하여 공식을 복사하세요. 이렇게 하면 점수와 일치하는 모든 상위 항목의 이름이 동적으로 표시됩니다. 스크린샷 참조:

Kutools AI로 엑셀의 마법을 풀다

• 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
• 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
• VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
• 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
• 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.

AI 기반 도구로 엑셀 활용 능력을 강화하세요. 지금 다운로드하여 그 어느 때보다 높은 효율성을 경험해 보세요!

기준에 따른 동적인 상위 10개 리스트를 만드는 공식

일부 분석 작업에서는 특정 기준을 충족하는 항목만 표시하는 상위 목록이 필요할 수 있습니다. 예를 들어 특정 그룹, 팀 또는 카테고리로 상위 결과를 제한하는 경우입니다. 예를 들어 여러 클래스의 성적을 포함하는 전체 데이터 시트에서 '1반'의 상위 10개 점수만 식별하려고 할 수 있습니다. 다음은 이 시나리오에 대한 공식 사용 방법입니다:

1. 데이터 세트에서 지정된 기준(예: '1반')을 충족하는 상위 10개 값을 추출하는 것으로 시작하세요. 대상 셀(예: J2)에 다음 공식을 입력하세요:

2. 공식을 입력한 후, 배열 공식으로 확인하기 위해 Ctrl + Shift + Enter를 누르고 다른 셀을 채우기 위해 채우기 핸들을 드래그하세요. 공식은 선택한 조건(예: '1반'의 모든 점수)과 일치하는 최고 10개 값을 반환합니다.

3. 기준에 따른 상위 값에 해당하는 이름을 나열하려면 아래 공식을 I2 셀에 복사하여 붙여넣고 배열 공식으로 Ctrl + Shift + Enter를 누르세요. 그런 다음 전체 이름 목록을 생성하기 위해 필요에 따라 채우기 핸들을 아래로 드래그하세요.

공식의 범위를 실제 데이터 설정에 맞게 조정해야 합니다. 배열 공식으로 큰 데이터 범위를 사용하면 성능이 저하될 수 있으므로 주의하세요. 상위 10개에 중복 값이 나타나면 공식은 반복되는 점수를 적절히 처리하고 동일한 점수를 가진 여러 학생 이름을 제공합니다.

Office 365에서 동적인 상위 10개 리스트 만들기

이전 버전의 Excel에서는 여러 함수를 배열 공식과 결합해야 하지만, Office 365(및 Excel 2021)에서는 INDEX, SORT, SEQUENCE 및 FILTER와 같은 동적 배열 함수를 도입하여 워크플로를 크게 간소화합니다. 이러한 함수는 동적인 상위 10개 리스트를 쉽게 만들고 오류를 줄이며, 특히 자주 증가하거나 변경되는 테이블에 유용합니다. 데이터가 계속 업데이트되는 환경에서 이러한 함수는 분석을 간소화하고 더 빠른 비즈니스 결정을 내릴 수 있도록 도와줍니다.

동적인 상위 10개 리스트를 만드는 공식

Office 365를 사용하여 동적인 상위 10개 리스트를 추출하고 표시하려면 원하는 출력 셀에 아래 공식을 입력하세요. 필요에 따라 범위와 숫자를 조정하기만 하면 되며, 데이터가 변경될 때마다 공식이 자동으로 최신 상위 10개 결과를 표시합니다.

Enter 키를 단순히 누르세요. 완전한 상위 10개 리스트가 즉시 나타나며 데이터나 수정된 점수가 즉시 반영되어 동적으로 유지됩니다.

기준에 따른 동적인 상위 10개 리스트를 만드는 공식

특정 그룹(예: '1반')에 대해 상위 10개를 추출해야 하는 경우 이러한 고급 Office 365 함수는 기준을 충족하는 행만 포함하면서 상위 N 리스트를 생성할 수 있습니다. 원하는 위치에 공식을 배치하고 필요에 따라 범위와 기준 셀을 조정하세요:

공식을 입력한 후 Enter 키를 단순히 누르세요. 특정 기준에 따라 필터링되고 순위가 매겨진 상위 10개 리스트가 즉시 표시되며 데이터를 수정하거나 기준을 변경할 때마다 업데이트됩니다.

피벗 테이블로 동적인 상위 10개 리스트 만들기

피벗 테이블: 상위 N 결과를 대화형으로 자동 표시

동적인 상위 N 리스트를 만드는 또 다른 방법은 Excel의 피벗 테이블 기능을 사용하는 것입니다. 이 방법은 특히 대규모 데이터 세트, 대화형 분석(예: 상위 항목 수를 빠르게 변경하거나 필터 적용), 또는 복잡한 공식을 피하고자 할 때 적합합니다. 피벗 테이블은 사용자 친화적이며 데이터가 변경될 때 자동으로 업데이트되기 때문에 대시보드나 보고서와 같이 다른 사람들과 공유하는 데 유용합니다.

피벗 테이블을 사용하여 동적인 상위 N 리스트를 만드는 방법:

1. 데이터 테이블 내 아무 곳이나 클릭한 다음 삽입 > 피벗 테이블로 이동합니다.
2. 피벗 테이블 대화 상자에서 피벗 테이블을 배치할 위치를 선택하고 확인을 클릭합니다.
3. '이름'(또는 유사한 식별자) 필드를 행 영역으로 드래그합니다.
4. '점수'(또는 값 열)를 값 영역으로 드래그합니다. 일반적으로 기본값은 '합계' 또는 '개수'입니다 — 상위 목록에서는 일반적으로 '합계' 또는 '최대값'을 원합니다. 필요하다면 우클릭하고 값 요약 방식을 선택하여 값 필드 계산을 변경합니다.
5. '점수' 열을 내림차순으로 정렬하려면 값 중 하나를 우클릭하고 정렬 > 가장 큰 값부터 가장 작은 값으로 정렬을 선택합니다.
6. 상위 N 결과로 제한하려면 행 레이블의 드롭다운 화살표를 클릭하고 값 필터 > 상위 10...을 선택하고, 숫자(예: 상위 10)와 필터 기준이 되는 필드를 설정한 다음 확인을 클릭합니다.

이제 피벗 테이블은 동적인 상위 10개(또는 지정한 N 개수)를 표시합니다. 상위 N을 변경하려면 필터 설정으로 돌아가세요. 데이터가 변경되면 피벗 테이블을 새로 고쳐 순위를 즉시 업데이트할 수 있습니다.

이 접근법의 장점은 빠른 설정, 쉬운 정렬 및 대화형 조정 가능성이 포함됩니다. 그러나 피벗 테이블은 다른 열에서 해당 행을 자동으로 추가할 수 없으며 행 또는 값 영역에 포함되지 않은 경우가 많습니다. 고급 사용자는 그룹화, 슬라이서 작성 또는 Top N 필터를 대시보드에 통합하여 보고서를 더욱 사용자 정의할 수 있습니다.

VBA를 사용하여 동적인 상위 10개 리스트 만들기

VBA 매크로: 자동으로 생성하고 상위 N 리스트 새로 고침

VBA 매크로는 광범위하거나 자주 업데이트되는 데이터를 다루는 사용자에게 동적인 상위 N 리스트의 추출과 새로 고침을 자동화하는 데 적합합니다. 매크로는 반복적인 작업을 줄이고 일관성을 유지하는 데 이상적입니다. 데이터를 정렬하고 실행할 때마다 상위 N 행만 특정 위치로 복사하는 루틴을 만들 수 있습니다.

동적인 상위 N 리스트를 생성하기 위해 VBA 매크로를 사용하려면 다음 단계를 따르세요:

1. 개발 도구 > Visual Basic을 클릭하여 VBA 편집기를 엽니다. (개발 도구 탭이 보이지 않는 경우 파일 > 옵션 > 리본 사용자 정의로 이동하여 '개발 도구'를 활성화하세요.)
2. VBA 창에서 삽입 > 모듈을 클릭하여 새 모듈을 추가합니다.
3. 다음 VBA 코드를 모듈에 붙여넣으세요:

Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
    Dim DataRange As Range
    Dim OutputRange As Range
    Dim N As Integer
    Dim ws As Worksheet, tempWS As Worksheet
    Dim xTitleId As String
    Dim LastCol As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
    Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
    N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
    
    If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
    
    ' Create a temporary worksheet to avoid sorting original data
    Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    DataRange.Copy tempWS.Range("A1")
    
    ' Determine last column for sorting key
    LastCol = DataRange.Columns.Count
    
    ' Sort in temporary sheet
    tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
    
    ' Copy headers and top N rows to output
    tempWS.Rows(1).Copy Destination:=OutputRange
    tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
    
    ' Optional: Delete temporary sheet
    Application.DisplayAlerts = False
    tempWS.Delete
    Application.DisplayAlerts = True
    
    Application.CutCopyMode = False
End Sub

4. 매크로를 실행하려면 데이터가 헤더가 포함된 표 형식으로 잘 구성되어 있는지 확인하세요. F5 를 누르거나 VBA 편집기에서 버튼을 클릭하세요. 다음을 수행하라는 메시지가 표시됩니다:

1. 헤더를 포함하여 데이터 범위를 선택하세요.
2. 결과를 붙여넣을 출력 셀을 선택하세요.
3. N(예: 상위 10의 경우 10)을 입력하세요.

매크로는 상위 N 항목(헤더 포함)을 지정한 위치에 복사합니다.

처음 테스트할 때는 워크북의 백업 또는 복사본에서 사용하는 것이 좋습니다. 오류가 발생한 경우(예: 잘못된 범위 선택) 다시 실행하고 범위와 데이터 레이아웃이 올바른지 확인하세요.

이 솔루션은 반복적인 보고 작업을 자동화하고, 대시보드를 만들거나 수동 공식이나 정렬 없이 상위 N 보고서를 빠르게 업데이트하는 데 이상적입니다. 특정 열로 정렬하거나 결과를 다른 워크북으로 내보내는 등 더 복잡한 순위 논리를 위한 VBA 스크립트를 추가로 사용자 정의할 수도 있습니다.

문제 해결: 매크로가 예상대로 작동하지 않는 경우 데이터 테이블에 적절한 헤더가 있는지 확인하고, 정렬 문제를 방지하기 위해 데이터 유형을 수정하고, 각 프롬프트에서 셀 참조가 정확히 선택되었는지 확인하세요. 매크로 실행 전 항상 작업을 저장하여 의도치 않은 데이터 변경을 방지하세요.

요약하자면, Excel은 전통적인 공식부터 강력한 Office 365 함수, 대화형 분석을 위한 피벗 테이블, 그리고 고급 자동화를 위한 VBA 매크로까지 동적인 상위 N 리스트를 생성하고 유지하는 다양한 방법을 지원합니다. 자신의 워크플로와 데이터 규모에 가장 적합한 방법을 선택하세요. 공식을 사용하는 것은 대부분의 수동 분석에 효과적이며, Office 365 함수는 가장 간단하고 강력한 방법을 제공하고, 피벗 테이블은 빠르고 유연한 요약에 탁월하며, VBA는 대규모 반복 순위 작업을 자동화하는 데 특히 유용합니다. 항상 공식이나 코드의 무결성을 확인하고 프로젝트가 발전함에 따라 데이터 구조의 변경에 맞게 셀 참조를 수정하세요.

최고의 오피스 생산성 도구

🤖	Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행   |  코드 생성  |  사용자 정의 수식 생성  |  데이터 분석 및 차트 생성  |  Kutools Functions 호출…
	인기 기능: 중복 찾기, 강조 또는 중복 표시   |  빈 행 삭제   |  데이터 손실 없이 열 또는 셀 병합   |   반올림(수식 없이) ...
	슈퍼 LOOKUP: 다중 조건 VLOOKUP  |   다중 값 VLOOKUP  |   다중 시트 조회   |   퍼지 매치 ....
	고급 드롭다운 목록: 드롭다운 목록 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭다운 목록 ....
	열 관리자: 지정한 수의 열 추가  |  열 이동  |  숨겨진 열의 표시 상태 전환  |  범위 및 열 비교 ...
	추천 기능: 그리드 포커스   |  디자인 보기   |   향상된 수식 표시줄   |  통합 문서 & 시트 관리자   |  자동 텍스트 라이브러리   |  날짜 선택기   |  데이터 병합   |  셀 암호화/해독   |  목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터(굵게/이탤릭/취소선 필터 등) ...
	15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...)  |  50+ 종류의 차트(간트 차트, ...)  |  40+ 실용적 수식(생일을 기반으로 나이 계산, ...)  |  19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...)  |  12 가지 변환 도구(단어로 변환하기, 통화 변환, ...)  |  7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...)  |  ... 등 다양

Kutools는 사용자가 선호하는 언어로 이용할 수 있습니다 – 영어, 스페인어, 독일어, 프랑스어, 중국어 및40개 이상의 언어 지원!

Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다.  가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...

Kutools for Excel의 자세한 정보... 무료 다운로드...

---

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다

• Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
• 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
• 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!

Office Tab의 자세한 정보... 무료 다운로드...

---

모든 Kutools 추가 기능. 한 번에 설치

Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.

• 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
• 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
• 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
• 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
• 최고의 가성비 — 개별 추가 기능 구매 대비 절약

Kutools for Office의 자세한 정보... 무료 다운로드...