엑셀 배열에서 k번째로 큰 값 구하기 / 작은 값 구하기

포스트 목차.

• 01. Large 함수
• 02. Small 함수
• 03. n번째로 큰/작은 값 구하기 예제

본 포스트에서는 여러 값들 중에서 k번째로 큰 값을 반환하는 Large 함수와 k번째로 작은 값을 반환하는 Small 함수를 소개한다.  위 두 함수를 이용하여 해당 값을 구해오는 예제도 함께 소개한다.

01. Large 함수

Large 함수는 입력받은 배열에서 n번째로 큰 값을 반환한다. 

인자는 다음과 같다.

- Array: 데이터들이 모여 있는 배열 (k 번째로 큰 값을 결정하는 데 필요한 배열 또는 데이터의 범위)

- K: 몇 번째로 큰 값인지를 나타내는 숫자

 

다음과 같은 데이터가 있을 때, 2번째로 큰 수를 반환하는 예를 살펴보자.

 

두 번째로 큰 수는 90이므로 90이 반환되면 우리가 원하는 결과를 얻는 것이다.

 

=LARGE(A1:A6, 2)

 

예측한 대로 90이 반환되는 것을 확인할 수 있다. 

 

이번에는 중복되는 데이터가 있을 경우에 LARGE 함수가 어떤 식으로 동작하는지 알아보자. 

 

{100, 90, 90, 80, 60, 60, 50, 40, 40, 30} 과 같은 데이터가 있을 때, K값을 1부터 10까지 증가시켜가며 LARGE 함수의 결과를 살펴보자.

 

 

 

=LARGE($K$1:$K$10, L1)

 

M열의 데이터를 채운 결과는 다음과 같다. 

 

중복된 값을 포함하여 정렬한 후 순서에 맞는 값을 반환한다는 것을 알 수 있다.

 

위의 두 예제에서는 이해를 돕기 위해 데이터가 정렬되어 있지만, 다음 그림처럼 데이터의 정렬 여부와는 상관 없이 동작한다.

 

원본 데이터의 순서를 변경한 경우

 

02. Small 함수

Small 함수는 입력받은 배열에서 n번째로 작은 값을 반환한다.

인자는 다음과 같다.

- Array: 데이터들이 모여 있는 배열 (k 번째로 작은 값을 결정하는 데 필요한 배열 또는 데이터의 범위)

- K: 몇 번째로 작은 값인지를 나타내는 숫자

 

다음과 같은 데이터가 있을 때, 2번째로 작은 수를 반환하는 예를 살펴보자.

 

두 번째로 작은 수는 40이므로 40이 반환되면 우리가 원하는 결과를 얻는 것이다.

 

=SMALL(A1:A6, 2)

 

예측한 대로 40이 반환되는 것을 확인할 수 있다. 

 

SMALL 함수도 LARGE 함수처럼 데이터의 순서로 반환 값을 결정한다. 중복되는 데이터가 있을 경우에 SMALL함수가 어떤 식으로 동작하는지 알아보자.

{100, 90, 90, 80, 60, 60, 50, 40, 40, 30} 과 같은 데이터가 있을 때, K값을 1부터 10까지 증가시켜가며 SMALL함수의 결과를 살펴보자.

=SMALL($J$1:$J$10, K1)

 

L열의 데이터를 채운 결과는 다음과 같다. 

중복된 값을 포함하여 정렬한 후 순서에 맞는 값을 반환한다는 것을 알 수 있다.

 

03. n번째로 큰/작은 값 구하기 예제

 

 

내림차순 정렬/오름차순 정렬 효과 만들기 예제

 

LARGE 함수 / SMALL 함수는 배열 수식을 지원하는 함수이다. 이번에는 배열 수식을 이용하여, 특정 데이터를 내림차순 정렬 한 값 및 오름차순으로 정렬한 값을 구해보자.

 

 

랜덤 함수로 A열에 값을 채운 상황이며, D열과 E열에 각각 내림차순, 오름차순으로 정렬한 값을 채울 예정이다. 

 

(1) D2부터 D16까지의 영역을 선택 후에 F2를 눌러서 D2셀을 편집한다.

(2) 배열 수식을 입력 후에 Shift + Ctrl + Enter를 입력하여 배열수식 입력을 완료한다. 

 

=LARGE(A2:A16,C2:C16)

A열의 값이 내림 차순으로 정렬되어 D열에 채워지는 것을 확인할 수 있다.

 

(3) E2부터 E16까지의 영역을 선택 후에 F2를 눌러서 E2셀을 편집한다.

 

(4) 배열 수식을 입력 후에 Shift + Ctrl + Enter를 입력하여 배열수식 입력을 완료한다.

 

=SMALL(A2:A16,C2:C16)

 

A열의 값이 오름 차순으로 정렬되어 E열에 채워지는 것을 확인할 수 있다.

 

배열 수식의 사용법이 궁금하면 다음 링크를 참고하기 바란다.

 

같이보면 좋은 글

• 엑셀에서 배열 수식 사용하기(1)
• 엑셀에서 배열 수식 사용하기(2)
• 엑셀을 이용하여 도수분포표 작성하기