Large(큰 수), Small(작은 수) 함수

Aggregate 함수에 Large, Small, Percentile(백분위 수), Quatile(사분위 수) 함수가 있기 때문에 이들 함수에 대해 먼저 알아보겠습니다.

 

1. 의미

 

Large : 데이터 집합에서 k번째로 큰 값을 반환
Small : 데이터 집합에서 k번째로 작은 값을 반환

2. 구문

LARGE(array,k)
SMALL(array,k)

• Array k번째로 큰 값 또는 작은 값을 확인할 데이터 배열 또는 범위.
• k 데이터의 배열이나 셀 범위에서 가장 큰 값 또는 작은 값을 기준으로 한 상대 순위.

3. 사용 예

large,small.xlsx

0.01MB

max, min 함수는 최댓값, 최솟값을 구해주는데, rank.eq함수는 해당 값의 순위를 구해주는데, large, small 함수는 순서에 해당하는 큰 값, 작은 값을 구해주는 함수입니다.

가. 순서대로 큰 값 구하기

내림차순으로 정렬하면 큰 값부터 작은 값으로 정렬되는데 정렬하지 않고도 순서대로 큰 값을 구해주는 함수가 large함수입니다.

날짜를 가장 큰 값부터 차례대로 구해보겠습니다. 9행과 10행의 2016-03-16과 15행과 16행의 2020-07-01이 같은 값입니다.

E2셀에 =l(엘)이라고 입력하면 l로 시작하는 함수가 표시되므로 아래 화살표키를 누르거나 마우스로 Large함수를 더블클릭해서 입력할 수도 있고,

=lar까지 입력하면 lar이 포함된 함수들이 표시되는데, 맨 위에 Large가 있으므로 탭키를 눌러

Large를 입력하는데 (시작 괄호)까지 입력됩니다.

B2셀부터 B16셀을 순서에 해당하는 날짜를 구할 범위를 지정하기 위해 B2셀을 마우스로 클릭한 다음 Shift + Ctrl + ↓키를 누르거나, B2셀을 클릭하고 Shift키를 누른 상태에서 B16셀을 클릭합니다.

그리고, 절대 참조 형식으로 지정하기 위해 F4키를 누릅니다.

그리고, ,(쉼표)를 입력한 다음 1을 입력하고 )(닫는 괄호)를 입력하고 엔터 키를 누릅니다.
그러면 44013이라고 숫자 형식으로 보이는데, 이는 1900-1-1부터의 연속된 번호입니다.

날짜 형식으로 변경하기 위해 홈 탭 > 표시 형식 그룹 > 일반 오른쪽의 콤보 상자 버튼(∨)을 누른 후

간단한 날짜를 누릅니다.

그러면 44013이 2020-07-01로 표시형식이 바뀝니다.

그런데, 순서가 1, 2, 3식으로 변경돼야 하므로 쉼표 다음의 1을 선택한 다음

a2셀을 클릭해서 수정합니다. 그리고 엔터 키를 누릅니다.

이제 E2셀의 채우기 핸들을 더블 클릭해서

E16셀까지 수식을 채우면 크기 순으로 날짜를 반환됩니다.
순서 1과 2의 날짜가 같고, 8과 9의 날짜가 같습니다.

[row() 함수 이용하기]

A열에 숫자가 없다면 1, 2, 3을 일일이 입력해야 하는데,
행 번호를 구해주는 row함수를 이용하면 쉽게 1, 2, 등 숫자를 입력할 수 있습니다.

다시 말해 E2셀에서 A2대신 row()-1이라고 입력하면
row()의 결괏값이 2행이므로 2가 반환되고, 1을 빼면 1이 되기 때문에 row()-1은 1이 됩니다.

또는 row(a1)이라고 입력하면 a1셀은 1행이므로 1이 반환됩니다.

아래는 A2를 row(a1)이라고 수정한 후 채우기 핸들을 더블 클릭해서 16행까지 수식을 채운 경우입니다.

A1셀로 지정했지만 아래로 내려가면서 A2식으로 수식이 수정됩니다.

row() 함수는 괄호 안에 인수를 입력하지 않으면 row함수가 들어 있는 셀이 되므로 E2셀의 경우 row()라고 하면 행 번호 2가 구해지므로 -1을 하면 1이 돼서 같은 결과가 구해집니다.

rank.eq함수를 이용해 B15셀과 B16셀의 날짜의 순위를 구하면 모두 1인데,
※ 수식은 =RANK.EQ(B15,$B$2:$B$16)

E3셀에서 largeg함수를 이용해 두 번째 값을 구해도 에러 없이 첫 번째와 같은 값이 반환합니다.

나. 순서대로 작은 값 구하기

이번에는 G2셀부터 G16셀까지 작은 값부터 순서대로 표시해 보겠습니다.

수식은 =SMALL($D$2:$D$16,ROW(G1))입니다.

이 때는 G2셀의 채우기 핸들을 더블 클릭해도 좌, 우에 쫓아갈 값이 없어서 G16셀까지 복사되지 않으므로
G2셀을 Ctrl + C키를 눌러 복사한 다음 연속된 범위를 선택하기 위해 Shift키를 누른 상태에서 G16셀을 클릭하면 G2셀부터 G16셀까지 선택되므로 마우스 오른쪽 버튼을 누른 후 fx라고 되어 있는 수식으로 붙여넣기 아이콘을 누르면 수식이 붙여 넣어집니다.

붙여넣기 첫 번째 붙여넣기 아이콘을 눌러도 되지만 다른 때를 생각해서 수식으로 붙여넣기 아이콘을 누른 것이 좋습니다.

복사할 범위와 붙여 넣을 범위가 G2셀이 중복일 때는 마우스 오른쪽버튼을 눌러 붙여 넣어야 하며,
Ctrl + V키를 사용할 때는 복사할 범위와 붙여 넣을 범위가 중복되면 안 되므로 G3셀부터 G16셀까지 범위를 선택한 후 Ctrl + V키를 눌러야 합니다.

다. 지점을 제한해서 큰 값을 순서대로 구하기

예를 들어 지점명이 A인 것만 찾아서 크기 순서대로 판매금액을 구하려면
large함수 안에 배열 수식을 작성해서 A지점에 해당하는 판매금액만 추출한 다음 row()-1을 이용해 순번을 입력하는 식으로 해야 합니다.

지점명이 "A"인 것만 찾으려면 H2셀에 =($C$2:$C$16="A")라고 입력하고 배열수식이므로 Shift+Ctrl+Enter를 누릅니다.
그러면 수식 양쪽 끝에 중괄호가 생깁니다.

마이크로소프트 365인 경우에는 배열 수식이라도 엔터키만 눌러도 되는데 위와는 달리 h2셀부터 H16셀까지 수식에 대한 값이 표시되고, 파란 선으로 둘러싸입니다.

이제 판매금액을 반환받기 위해 두 가지 조건이 모두 만족하여야 하므로 * 기호를 넣은 다음 D2:D16을 선택한 다음 F4키를 누르고 엔터키를 누르면 아래와 같이 판매금액이 지점명이 A인 줄에만 표시됩니다.
D2:D16에 괄호로 감싸는 것이 확실하지만 괄호를 안 넣어도 값에 영향은 없습니다.

이제 large(와 순번을 row()-1로 추가하면 수식은 =LARGE(($C$2:$C$16="A")*D2:$D$16,ROW()-1)이 되는데 위와는 달리 H2셀에만 값이 표시됩니다. Shift + Ctrl + Enter키를 눌러도 마찬가지입니다.

이제 H2셀의 채우기 핸들을 더블 클릭하면 A지점에 해당하는 판매금액만이 1등부터 7등까지만 표시되고, 나머지 지점명에 대한 판매금액은 0으로 표시됩니다.