엑셀 상위 N개 항목 필터링 해결 방법
엑셀 상위 N개 항목 필터링 목차 바로가기
상위 N개 항목 필터링, 핵심 요약
상위 N개 항목 필터링 함수 공식
=SORT(FILTER(범위,조건범위>=LARGE(조건범위,N)),열번호,-1)
'본 공식은 M365/엑셀 2021 이후 사용자만 사용가능합니다.
'본 공식은 M365/엑셀 2021 이후 사용자만 사용가능합니다.
- 범위 : 필터를 적용할 전체 범위입니다.
- 조건범위 : 상위 N개 항목을 구분하는 조건이 입력된 범위입니다.
- 열번호 : 조건범위의 열번호입니다. (예제에서는 3번째 열에 있으므로 열번호는 '3' 입니다.)
- N : 출력할 상위 항목 개수입니다.
상위 N개 항목, 자동필터로 필터링하기
자동필터 적용 (Ctrl + Shift + L) → ① 필터 버튼 클릭 → ② 숫자 필터 →
③ 상위 10 클릭 → ④ 내림차순 정렬
'피벗테이블을 사용하면 원본 데이터를 유지하고 필터를 적용할 수 있습니다.😉
③ 상위 10 클릭 → ④ 내림차순 정렬
'피벗테이블을 사용하면 원본 데이터를 유지하고 필터를 적용할 수 있습니다.😉
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀 공식] 상위 N개 항목 필터링 공식예제파일
방법 1. FILTER와 LARGE함수를 사용하는 방법
오늘은 엑셀에서 ①함수를 사용하거나 ②자동필터를 사용해서 상위 N개 값을 추출하는 방법을 알아보겠습니다.
예제파일을 다운로드 후, [영업팀 전체 성과평가 점수표]로 이동하면 각 직원별 평가점수가 정리되어 있습니다. 우선 함수를 사용해서 평가점수가 높은 상위 5개 직원을 필터링 후 내림차순으로 정렬하는 방법을 알아보겠습니다.
- FILTER+LARGE 함수로 상위 N개 필터링 : 우선 FILTER 함수와 LARGE 함수를 사용해 평가점수가 높은 5개 직원을 필터링합니다. LARGE 함수는 범위에서 n번째로 큰 값을 반환하는 함수입니다. 따라서=LARGE(C3:C19,5) 는 C3:C19 범위에서 5번째 큰 값을 반환합니다.
=FILTER(범위, 조건)
=FILTER(범위, 점수범위>=LARGE(점수범위,N))
=FILTER(A3:C19, C3:C19>=LARGE(C3:C19,5))
FILTER함수와 LARGE함수로 상위 N개 항목을 필터링합니다. - SORT 함수로 정렬하기 : 이제 SORT 함수를 사용해 방금 필터링 된 배열을 정렬하면, 상위 N개 항목 필터링 작업이 마무리됩니다.
=SORT(정렬할 범위, 열 번호, 정렬순서)
=SORT(FILTER(A3:C19,C3:C19>=LARGE(C3:C19,5)),3,-1)
필터링 된 범위를 SORT 함수로 내림차순 정렬합니다.
방법 2. 자동필터로 상위 N개 항목 필터링하기
- 자동필터 적용하기 : 원본 데이터 범위에서 ① 임의의 셀을 선택 한 후 ② [데이터]탭 - [필터]버튼을 클릭하면 ③표의 각 머리글에 자동필터가 적용됩니다.
범위에서 임의의 셀을 선택한 후, 자동필터를 적용합니다. 오빠두Tip : 또는 컨쉬엘(Ctrl + Shift + L) 단축키로 필터를 빠르게 적용할 수 있습니다. 실무자 필수 단축키 목록은 아래 강의를 참고해주세요!
- 상위 N개 항목 필터링 : 이제 필터를 적용할 범위 머릿글의 ①[필터] 버튼을 클릭한 후 필터 창이 열리면 ②[숫자필터] - [상위 10]을 클릭합니다.
'성과평가점수' 필드 - [숫자필터] - [상위 10] 선택하기 - [상위10 자동필터] 대화상자가 열리면 표시할 항목의 개수를 5로 변경한 후 [확인]을 클릭합니다. 버튼을 클릭하면 상위 5개 항목이 필터링됩니다.
상위 10 자동필터 대화상자에서 조건을 적용합니다. - 내림차순 정렬하기 : 이제 필터링 된 범위를 내림차순으로 정렬합니다. 정렬할 범위 머릿글의 ①[필터]버튼을 클릭한 후, ②[숫자 내림차순 정렬]을 클릭하면 점수 기준으로 정렬되면서 상위 N개 항목 필터링 작업이 마무리됩니다.
마지막 단계로 자동필터에서 내림차순을 적용하면 상위 N개 항목 필터링이 마무리됩니다. 오빠두Tip : 피벗테이블을 사용하면 원본데이터를 유지한 상태로 상위 N개 항목을 필터링할 수 있습니다. 피벗테이블 기초 사용법은 아래 5분 영상강의를 참고해주세요!
