LARGE와 SMALL로 최댓값과 최솟값 찾기

MAX는 가장 큰 값 하나를, MIN은 가장 작은 값 하나를 반환합니다. 그렇다면 두 번째로 큰 값이나 세 번째로 작은 값을 구하려면 어떻게 해야 할까요?

바로 이때 LARGE와 SMALL을 사용합니다. 이 함수들은 범위에서 n번째로 큰 값이나 n번째로 작은 값을 가져오며, 상위 N개 및 하위 N개 보고서의 기반이 됩니다.

LARGE(range, k)는 k번째로 큰 값을 반환합니다. 두 번째 인수인 k가 위치를 정합니다. 1은 가장 큰 값, 2는 두 번째로 큰 값이며 이후에도 같은 방식으로 이어집니다.

따라서 LARGE(A2:A20,1)은 MAX(A2:A20)와 같고, LARGE(A2:A20,2)는 두 번째로 큰 값을 반환합니다.

SMALL(range, k)는 아래쪽부터 LARGE와 같은 방식으로 작동합니다. SMALL(A2:A20,1)은 MIN(A2:A20)과 같고, SMALL(A2:A20,3)은 세 번째로 작은 값을 반환합니다.

실적이 가장 낮은 항목, 가장 저렴한 선택지 또는 데이터 집합에서 가장 이른 날짜를 찾을 때 SMALL을 사용하십시오.

상위 세 값을 나열하려면 k에 1, 2, 3을 넣은 LARGE 수식 세 개를 차례로 사용하면 됩니다. 더 깔끔한 방법은 보조 숫자를 참조하여 수식을 아래로 채우는 것입니다.

D2 셀에 1, D3 셀에 2, D4 셀에 3이 있다면 이 단일 수식을 아래로 채워 상위 3개 목록 전체를 만들 수 있습니다.

ROW 함수를 사용하면 보조 열 없이 k를 자동으로 만들 수 있습니다. ROW()-1은 첫 번째 행에서 1, 다음 행에서 2를 반환하며 이후에도 계속 증가합니다.

이 수식을 아래로 채우면 별도의 설정 없이 순위 목록이 만들어집니다. 목록이 시작되는 위치에 맞게 오프셋을 조정하십시오.

최신 버전의 Excel과 Google 스프레드시트에서는 SEQUENCE를 k 인수로 지정하여 수식 하나로 상위 5개 전체를 펼쳐 표시할 수 있습니다. SEQUENCE(5)는 1, 2, 3, 4, 5를 생성합니다.

그러면 LARGE가 한 번에 다섯 값을 반환하고, 값이 자동으로 아래쪽에 펼쳐집니다. 수식 하나로 전체 순위 목록을 만들 수 있습니다.

흔히 묻게 되는 업무 질문이 있습니다. 상위 3명의 고객이 매출에 얼마나 기여할까요? LARGE를 SEQUENCE(또는 배열 상수)와 함께 SUM으로 감싸 상위 값들의 합계를 구할 수 있습니다.

아래 수식은 범위에서 가장 큰 세 값을 더해 한 셀에 상위 3개의 총 기여도를 반환합니다.

k가 1보다 작거나 범위 안 숫자의 개수보다 크면 LARGE와 SMALL은 #NUM! 오류를 반환합니다.

예를 들어 숫자가 19개뿐인 목록에서 30번째로 큰 값을 요청하면 실패합니다. 보고서에서는 호출을 IFERROR로 감싸 이러한 상황을 처리하십시오.

값만 아는 것보다 그 값을 얻은 사람을 아는 것이 더 유용합니다. LARGE를 INDEX 및 MATCH와 결합하여 가장 큰 값에 연결된 이름을 가져오십시오.

이렇게 하면 가장 큰 매출액을 찾고 해당 행의 위치를 확인한 다음 A열에서 일치하는 이름을 반환하므로, 보고서에 "최고 영업 담당자: Maria"라고 표시할 수 있습니다.

다른 통계 함수와 마찬가지로 LARGE와 SMALL은 숫자만 셉니다. 범위의 텍스트와 빈 셀은 건너뛰므로 n번째로 큰 값에 영향을 주지 않습니다.

따라서 헤더 행이 포함된 열을 안전하게 지정할 수 있습니다. 단, 실수로 텍스트로 저장된 숫자는 무시되어 LARGE 또는 SMALL이 반환하는 값이 달라질 수 있으므로 주의하십시오.

B2:B20에 월별 매출이 있습니다. 경영진은 실적이 가장 좋은 달과 가장 나쁜 달을 각각 세 개씩 확인하려고 합니다.

• 최고 실적 세 개: =LARGE($B$2:$B$20,{1,2,3})
• 최저 실적 세 개: =SMALL($B$2:$B$20,{1,2,3})

각 수식을 펼쳐 표시할 수 있는 셀에 입력하면 대시보드에 사용할 상위 및 하위 현황을 즉시 확인할 수 있습니다.

LARGE와 SMALL을 얼마나 이해했는지 확인해 보십시오.

이제 MAX와 MIN을 넘어선 값을 구할 수 있습니다.

• LARGE(range,k)는 k번째로 큰 값을, SMALL(range,k)는 k번째로 작은 값을 반환합니다.
• k = 1이면 MAX 또는 MIN과 같고, k를 늘리면 두 번째 이후의 값을 구할 수 있습니다.
• SEQUENCE 또는 {1,2,3}과 같은 배열을 사용하면 수식 하나로 상위 N개 목록을 만들거나 합산할 수 있습니다.
• INDEX-MATCH와 함께 사용해 값에 레이블을 지정하고, 범위를 벗어난 k를 처리하려면 IFERROR로 감싸십시오.