엑셀 SORT 기능 - 수식으로 자동 정렬

SORT 기능

Excel의 SORT 함수는 배열 또는 범위의 내용을 열 또는 행별로 오름차순 또는 내림차순으로 정렬합니다.

SORT는 동적 배열 함수 그룹에 속합니다. 그 결과 원본 배열의 모양에 따라 수직 또는 수평으로 인접한 셀에 자동으로 유출되는 동적 배열이 생성됩니다.

SORT 함수의 구문은 다음과 같습니다.

SORT(array, [sort_index], [sort_order], [by_col])

:

Array(필수) - 정렬할 값의 배열 또는 셀 범위입니다. 텍스트, 숫자, 날짜, 시간 등을 포함한 모든 값이 될 수 있습니다.

Sort_index (선택 사항) - 정렬 기준으로 사용할 열 또는 행을 나타내는 정수입니다. 생략하면 기본 인덱스 1이 사용됩니다.

Sort_order (선택 사항) - 정렬 순서를 정의합니다.

• 1 또는 생략(기본값) - 오름차순, 즉 가장 작은 순서에서 가장 큰 순서
• -1 - 내림차순, 즉 가장 큰 것부터 가장 작은 것까지

By_col (선택 사항) - 정렬 방향을 나타내는 논리 값:

• FALSE 또는 생략(기본값) - 행별로 정렬합니다. 대부분의 경우 이 옵션을 사용합니다.
• TRUE - 열을 기준으로 정렬합니다. 데이터가 열에서 가로로 구성된 경우 이 옵션을 사용합니다.

 

 SORT 기능-팁 및 메모

SORT는 새로운 동적 배열 함수이므로 알아야 할 몇 가지 특수성이 있습니다.

• 현재 SORT 함수는 Microsoft 365 및 Excel 2021에서만 사용할 수 있습니다. Excel 2019, Excel 2016은 동적 배열 수식을 지원하지 않으므로 이러한 버전에서는 SORT 함수를 사용할 수 없습니다.
• SORT 수식에서 반환된 배열이 최종 결과인 경우(즉, 다른 함수로 전달되지 않은 경우) Excel은 적절한 크기의 범위를 동적으로 만들고 정렬된 값으로 채웁니다. 따라서 수식을 입력하는 셀의 아래쪽 또는 오른쪽에 항상 빈 셀이 충분한지 확인하십시오., 그렇지 않으면 #SPILL 오류가 발생합니다.
• 원본 데이터가 변경되면 결과가 동적으로 업데이트됩니다. 그러나 수식에 제공된 배열은 참조된 배열 외부에 추가되는 새 항목을 포함하도록 자동으로 확장되지 않습니다. 이러한 항목을 포함하려면 수식에서 배열 참조를 업데이트하거나, 원본 범위를 테이블로 변환하거나, 동적 명명된 범위를 만들어야 합니다.

 

기본 Excel SORT 수식

이 예제에서는 Excel에서 데이터를 오름차순 및 내림차순으로 정렬하는 기본 수식을 보여줍니다.

데이터가 아래 스크린샷과 같이 알파벳순으로 정렬되어 있다고 가정합니다. 데이터를 나누거나 혼합하지 않고 B 열의 숫자를 정렬하려고합니다.

 

오름차순으로 정렬하는 수식

B 열의 값을 가장 작은 값에서 가장 큰 값으로 정렬하려면 사용할 수식은 다음과 같습니다.

 

=SORT(A2:B8, 2, 1)

• A2:B8은 소스 배열입니다.
• 2는 정렬 기준으로 사용할 열 번호입니다.
• 1은 오름차순 정렬 순서입니다.

데이터가 행으로 구성되어 있으므로 마지막 인수를 생략하여 기본값으로 FALSE - 행별 정렬을 지정할 수 있습니다.
빈 셀(이 경우 D2)에 수식을 입력하고 Enter 키를 누르면 결과가 D2:E8로 자동으로 유출됩니다.

 

내림차순으로 정렬하는 수식

데이터를 내림차순으로, 즉 가장 큰 것에서 가장 작은 것으로 정렬하려면 다음과 같이 sort_order 인수를 -1로 설정합니다.

=SORT(A2:B8, 2, -1)

대상 범위의 왼쪽 상단 셀에 수식을 입력하면 다음과 같은 결과를 얻을 수 있습니다.

비슷한 방식으로 텍스트 값을 알파벳순으로 A에서 Z까지 또는 Z에서 A까지 정렬할 수 있습니다.

 

수식을 사용하여 Excel에서 데이터를 정렬하는 방법

아래 예는 Excel에서 SORT 함수의 몇 가지 일반적인 용도와 몇 가지 중요하지 않은 용도를 보여줍니다.

Excel SORT 열

Excel에서 데이터를 정렬할 때 대부분의 경우 행 순서가 변경됩니다. 그러나 데이터가 레이블이 포함된 행과 레코드가 포함된 열로 가로로 구성된 경우 위에서 아래로가 아니라 왼쪽에서 오른쪽으로 정렬해야 할 수 있습니다.

Excel에서 열을 기준으로 정렬하려면 by_col 인수를 TRUE로 설정합니다. 이 경우 sort_index는 열이 아닌 행을 나타냅니다.

 

예를 들어, 아래 데이터를 수량별로 정렬하려면 가장 높은 것에서 가장 낮은 것까지 다음 공식을 사용하십시오.

 

=SORT(B1:H2, 2, 1, TRUE)

• B1:H2는 정렬할 원본 데이터입니다.
• 2는 두 번째 행의 숫자를 정렬하기 때문에 정렬 인덱스입니다
• -1은 내림차순 정렬 순서를 나타냅니다.
• TRUE는 행이 아닌 열을 정렬하는 것을 의미합니다

 

여러 열을 기준으로 서로 다른 순서로 정렬(다단계 정렬)

 

복잡한 데이터 모델로 작업할 때 다단계 정렬이 필요한 경우가 많습니다. sort_index 및 sort_order 인수에 대한 배열 상수를 제공하는 것입니다.

예를 들어, 아래 데이터를 먼저 A에서 Z까지 지역(열 A)별로 정렬한 다음 수량을 기준으로 정렬합니다.

(C 열)을 가장 작은 인수에서 가장 큰 인수로 설정합니다.

• 배열은 A2:C13의 데이터입니다.
• Sort_index는 배열 상수 {1,3}으로, 먼저 지역(첫 번째 열)을 기준으로 정렬한 다음 Qty를 기준으로 정렬하기 때문입니다. (1 번째 열).
• Sort_order배열 상수 {1,-1}은(는) 배열 상수 {1,-3}로, 첫 번째 열은 오름차순으로, 세번째 열은 내림차순으로 정렬되어야 합니다.
• By_col 행은 기본값인 행을 정렬하기 때문에 생략됩니다.

인수를 종합하면 다음과 같은 공식을 얻을 수 있습니다.

=SORT(A2:C13, {1,3}, {1,-1})

그리고 그것은 완벽하게 작동합니다! 첫 번째 열의 텍스트 값은 알파벳순으로 정렬되고 세 번째 열의 숫자는 가장 큰 값에서 가장 작은 숫자로 정렬됩니다.

Excel에서 정렬 및 필터링

몇 가지 기준으로 데이터를 필터링하고 출력을 순서대로 배치하려는 경우 SORT 및 FILTER 함수를 함께 사용하십시오.

SORT(FILTER(array, criteria_range=criteria), [sort_index], [sort_order], [by_col])

FILTER 함수는 사용자가 정의한 기준에 따라 값 배열을 가져오고 해당 배열을 SORT의 첫 번째 인수에 전달합니다.

이 공식의 가장 좋은 점은 누를 필요 없이 결과를 동적 유출 범위로 출력한다는 것입니다. Ctrl + Shift + Enter 또는 복사할 셀 수를 추측합니다.

평소와 같이 맨 위 셀에 수식을 입력하고 Enter 키를 누릅니다.

 

예를 들어 A30:B30의 소스 데이터에서 수량이 2(>=9)보다 큰 항목을 추출하고 결과를 오름차순으로 정렬합니다.

이를 위해 먼저 아래 이미지와 같이 E2 셀에서 조건을 설정합니다.

그런 다음 다음과 같이 Excel SORT 수식을 작성하십시오.

=SORT(FILTER(A2:B9, B2:B9>=E2), 2)

 

FILTER 함수에 의해 생성된 배열 외에도 sort_index 인수(열 2)만 지정합니다. 나머지 두 인수는 기본값이 필요한 대로 정확하게 작동하기 때문에 생략됩니다(오름차순, 행순으로 정렬).

 

N개의 가장 큰 값 또는 가장 작은 값을 가져오고 결과 정렬

거대한 대량의 정보를 분석 할 때 특정 수의 상위 값을 추출해야하는 경우가 종종 있습니다.

어쩌면 추출 할뿐만 아니라 원하는 순서로 정렬 할 수도 있습니다. 그리고 이상적으로는 결과에 포함할 열을 선택하는 것이 좋습니다. 

다음은 일반적인 공식입니다.

INDEX(SORT(…), SEQUENCE(n), {column1_to_return, column2_to_return, …})

여기서 n은 반환할 값의 수입니다.

 

아래 데이터 세트에서 C 열의 숫자를 기반으로 상위 3개 목록을 가져오려고 한다고 가정합니다.

이 작업을 수행하려면 먼저 배열 A2 : C13을 내림차순으로 3번째 열로 정렬합니다.

SORT(A2:C13, 3, -1)

 

그런 다음 INDEX 함수의 첫 번째 (배열) 인수에 위의 수식을 중첩하여 배열을 가장 높은 것부터 가장 작은 것 순으로 정렬합니다.

반환할 행 수를 나타내는 두 번째(row_num) 인수의 경우 SEQUENCE 함수를 사용하여 필요한 일련 번호를 생성합니다.

 

3개의 상위 값이 필요하므로 수식에 직접 수직 배열 상수 {3;1;2}을 제공하는 것과 동일한 SEQUENCE(3)를 사용합니다.

반환할 열 수를 정의하는 세 번째(col_num) 인수의 경우 열 번호를 수평 배열 상수 형식으로 제공합니다.

 

열 B와 C를 반환하려고 하므로 {2,3} 배열을 사용합니다.

결국 다음 공식을 얻습니다.

 

=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})

그리고 우리가 원하는 결과를 정확하게 생성합니다.

3개의 하위 값을 반환하려면 원본 데이터를 가장 작은 값에서 가장 큰 값으로 정렬하기만 하면 됩니다. 이를 위해 sort_order 인수를 -1에서 1로 변경합니다.

 

=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})

 

특정 위치에 정렬된 값 반환

다른 각도에서 보면 특정 정렬 위치만 반환하려면 어떻게 해야 합니까? 예를 들어, 정렬된 목록에서 1위만, 2위만, 3위만? 이를 수행하려면 위에서 설명한 INDEX SORT 공식의 단순화된 버전을 사용합니다:

INDEX(SORT(…), n, {column1_to_return, column2_to_return, …})

여기서 n은 관심 위치입니다.

 

예를 들어, 위에서(즉, 내림차순으로 정렬된 데이터에서) 특정 위치를 가져오려면 다음 공식을 사용합니다.

=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})

 

하단에서 특정 위치를 얻으려면(즉, 오름차순으로 정렬된 데이터에서) 다음을 사용하십시오.

=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})

 

여기서 A2:C13은 원본 데이터이고, F1은 위로부터의 위치이고, I1은 아래로부터의 위치이고, {2,3}은 반환할 열입니다.

 

Excel 표를 사용하여 정렬 배열이 자동으로 확장

이미 알고 있듯이 정렬된 배열은 원본 데이터를 변경할 때 자동으로 업데이트됩니다. 이는 SORT를 포함한 모든 동적 배열 함수의 표준 동작입니다. 그러나 참조된 배열 외부에 새 항목을 추가하면 수식에 자동으로 포함되지 않습니다.

 

수식이 이러한 변경 내용에 응답하도록 하려면 원본 범위를 완전한 기능의 Excel 표로 변환하고 수식에 구조적 참조를 사용합니다.

 

실제로 어떻게 작동하는지 보려면 다음 예를 고려하십시오.

아래 Excel SORT 수식을 사용하여 A2 : B8 범위의 값을 알파벳순으로 정렬한다고 가정합니다.

=SORT(A2:B8, 1, 1)

 

그런 다음 9행에 새 항목을 입력합니다. 새로 추가된 항목이 유출 범위에서 제외된 것을 보고 실망합니다.

이제 원본 범위를 테이블로 변환합니다. 이를 위해 열 머리글(A1:B8)을 포함한 범위를 선택하고 Ctrl + T. 수식을 작성할 때 마우스를 사용하여 원본 범위를 선택하면 테이블 이름이 수식에 자동으로 삽입됩니다

=SORT(Table1, 1, 1)

 

마지막 행 바로 아래에 새 항목을 입력하면 테이블이 자동으로 확장되고 새 데이터가 SORT 수식의 유출 범위에 포함됩니다.

 

Excel SORT 기능이 작동하지 않는 이유

SORT 수식에서 오류가 발생하는 경우 다음과 같은 이유 때문일 가능성이 큽니다.

 

#NAME 오류: 이전 Excel 버전

SORT는 새로운 기능이며 Excel 365 및 Excel 2021에서만 작동합니다. 이 기능이 지원되지 않는 이전 버전에서는 #NAME? 오류가 발생합니다.

 

#SPILL 오류: 무언가가 유출 범위를 차단합니다.

유출 범위에 있는 하나 이상의 셀이 완전히 비어 있거나 병합되지 않은 경우 #SPILL! 오류가 표시됩니다. 문제를 해결하려면 막힌 부분을 제거하기만 하면 됩니다. 

 

#VALUE 오류: 잘못된 인수

#VALUE에 부딪힐 때마다! 오류가 발생하면 sort_index 및 sort_order 인수를 확인하십시오. Sort_index 배열인 열 수를 초과해서는 안 되며 sort_order 1(오름차순) 또는 -1(내림차순)이어야 합니다.

 

#REF 오류: 원본 통합 문서가 닫혔습니다.

동적 배열은 통합 문서 간의 참조를 제한적으로 지원하므로 SORT 함수를 사용하려면 두 파일이 모두 열려 있어야 합니다. 원본 통합 문서가 닫히면 수식이 #REF throw합니다! 오류. 이 문제를 해결하려면 참조된 파일을 열기만 하면 됩니다.