엑셀에서 데이터를 정렬할 때는 보통 데이터 → 정렬 기능이나 SORT 함수를 사용합니다.
하지만 부서를 먼저 정렬하고 같은 부서 안에서는 실적이 높은 순서로 정렬하는 것처럼 여러 조건을 순서대로 적용해야 한다면 SORTBY 함수가 편리합니다.
SORTBY 함수는 정렬할 데이터와 정렬 기준 범위를 각각 지정할 수 있으며, 조건을 여러 개 추가하는 것도 가능합니다.
또한 원본 데이터가 변경되면 정렬 결과도 자동으로 다시 계산되기 때문에 직원 명단, 매출 현황, 재고 목록처럼 계속 업데이트되는 자료를 관리할 때 유용합니다.
이번 글에서는 엑셀 SORTBY 함수 사용법과 여러 조건으로 데이터를 자동 정렬하는 방법을 알아보겠습니다.
엑셀 SORTBY 함수란?
SORTBY 함수는 지정한 배열을 다른 범위의 값을 기준으로 자동 정렬하는 함수입니다.
기본적인 구조는 다음과 같습니다.
=SORTBY(array,by_array1,[sort_order1],[by_array2,sort_order2],…)
각 인수의 의미는 다음과 같습니다.
array
정렬한 결과를 표시할 전체 데이터 범위입니다.
by_array1
첫 번째 정렬 기준이 되는 범위입니다.
sort_order1
첫 번째 기준의 정렬 방향입니다.
1: 오름차순-1: 내림차순
이후 두 번째, 세 번째 정렬 기준이 필요하다면 by_array2, sort_order2 등을 계속 추가할 수 있습니다.
예를 들어 다음 수식은 A2:D10 데이터를 D열을 기준으로 높은 값부터 정렬합니다.
=SORTBY(A2:D10,D2:D10,-1)
SORTBY 함수 기본 사용법
다음과 같은 직원 명단이 있다고 가정해 보겠습니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김철수 | 영업팀 | 대리 | 120 |
| 이영희 | 총무팀 | 과장 | 80 |
| 박민수 | 영업팀 | 사원 | 95 |
| 김민지 | 개발팀 | 대리 | 110 |
| 최영수 | 영업팀 | 과장 | 150 |
| 정수진 | 개발팀 | 사원 | 90 |
데이터는 A2:D7에 입력되어 있다고 가정하겠습니다.
실적이 높은 직원부터 정렬하려면 다음과 같이 입력합니다.
=SORTBY(A2:D7,D2:D7,-1)
여기에서
A2:D7
은 정렬할 전체 데이터이고,
D2:D7
은 정렬 기준인 실적입니다.
마지막 -1은 내림차순을 의미합니다.
결과는 다음과 같습니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 최영수 | 영업팀 | 과장 | 150 |
| 김철수 | 영업팀 | 대리 | 120 |
| 김민지 | 개발팀 | 대리 | 110 |
| 박민수 | 영업팀 | 사원 | 95 |
| 정수진 | 개발팀 | 사원 | 90 |
| 이영희 | 총무팀 | 과장 | 80 |
원본 데이터의 순서는 변경되지 않고 별도의 위치에 정렬된 결과가 나타납니다.
SORTBY 오름차순 정렬 방법
SORTBY 함수에서 정렬 순서를 1로 지정하면 오름차순으로 정렬됩니다.
예를 들어 실적이 낮은 직원부터 높은 직원 순서로 정렬하려면 다음과 같이 사용합니다.
=SORTBY(A2:D7,D2:D7,1)
숫자라면
작은 값 → 큰 값
순서로 정렬됩니다.
텍스트 데이터라면 일반적으로 문자 정렬 순서에 따라 결과가 표시됩니다.
따라서 이름 목록이나 부서 목록을 정렬할 때도 사용할 수 있습니다.
SORTBY 내림차순 정렬 방법
반대로 정렬 순서를 -1로 지정하면 내림차순입니다.
=SORTBY(A2:D7,D2:D7,-1)
실적이나 매출액처럼 높은 값부터 확인하고 싶은 데이터에서는 내림차순을 자주 사용합니다.
정리하면 SORTBY의 정렬 방향은 다음과 같이 기억하면 쉽습니다.
1 = 오름차순
-1 = 내림차순
SORTBY 함수로 여러 조건 정렬하기
SORTBY 함수의 가장 유용한 기능 중 하나는 여러 정렬 기준을 순서대로 지정할 수 있다는 것입니다.
예를 들어 직원 데이터를
1순위 : 부서 오름차순
2순위 : 실적 내림차순
으로 정렬한다고 가정하겠습니다.
다음과 같이 작성합니다.
=SORTBY(A2:D7,B2:B7,1,D2:D7,-1)
수식을 나눠서 보면 어렵지 않습니다.
첫 번째 조건은
B2:B7,1
입니다.
B열의 부서를 오름차순으로 정렬합니다.
두 번째 조건은
D2:D7,-1
입니다.
첫 번째 조건에서 같은 값이 나온 경우 D열의 실적을 내림차순으로 정렬합니다.
즉, 먼저 부서별로 묶은 다음 같은 부서 안에서는 실적이 높은 직원부터 표시하는 방식입니다.
SORTBY 3개 조건으로 정렬하기
조건은 두 개뿐만 아니라 세 개 이상도 지정할 수 있습니다.
예를 들어
1순위 : 부서 오름차순
2순위 : 직급 오름차순
3순위 : 실적 내림차순
으로 정렬한다면 다음과 같이 작성할 수 있습니다.
=SORTBY(A2:D100,B2:B100,1,C2:C100,1,D2:D100,-1)
SORTBY에서는
정렬범위1, 정렬방향1, 정렬범위2, 정렬방향2…
형태로 조건을 계속 추가합니다.
따라서 복잡한 업무 데이터에서도 여러 기준을 순서대로 적용할 수 있습니다.
날짜를 최신순으로 자동 정렬하기
SORTBY 함수는 날짜 데이터에도 활용할 수 있습니다.
예를 들어
A열 : 주문일
B열 : 상품명
C열 : 고객명
D열 : 금액
으로 구성된 주문내역이 있다고 가정하겠습니다.
A2:D100의 데이터를 최근 주문부터 표시하려면 다음과 같이 사용할 수 있습니다.
=SORTBY(A2:D100,A2:A100,-1)
날짜를 내림차순으로 정렬하기 때문에
최근 날짜 → 과거 날짜
순서로 나타납니다.
반대로 오래된 날짜부터 확인하려면 다음과 같이 사용합니다.
=SORTBY(A2:D100,A2:A100,1)
거래내역이나 주문내역, 업무일지 등을 최신순으로 표시할 때 유용합니다.
SORT 함수와 SORTBY 함수 차이는?
SORT와 SORTBY는 모두 동적 배열 방식으로 데이터를 자동 정렬할 수 있지만 정렬 기준을 지정하는 방법이 다릅니다.
예를 들어 A2:D100에서 네 번째 열인 실적을 내림차순으로 정렬해 보겠습니다.
SORT 함수에서는 다음과 같이 작성합니다.
=SORT(A2:D100,4,-1)
4라는 숫자를 사용해 A2:D100 범위의 네 번째 열을 기준으로 지정합니다.
SORTBY에서는 다음과 같이 작성합니다.
=SORTBY(A2:D100,D2:D100,-1)
정렬 기준인 D2:D100 범위를 직접 지정합니다.
즉,
SORT
정렬할 배열 안에서 몇 번째 열인지 지정
SORTBY
정렬 기준이 되는 범위를 직접 지정
하는 차이가 있습니다.
간단한 데이터에서는 SORT 함수가 짧고 편리하지만, 정렬 기준이 여러 개라면 SORTBY 함수가 수식을 이해하고 관리하기 편한 경우가 많습니다.
SORTBY 함수의 또 다른 장점
SORTBY는 정렬 결과에 포함하지 않는 범위를 기준으로 정렬할 수도 있습니다.
예를 들어 A열에 이름, B열에 부서, C열에 실적이 있고 결과에는 이름과 부서만 표시하고 싶다고 가정해 보겠습니다.
다음과 같이 사용할 수 있습니다.
=SORTBY(A2:B100,C2:C100,-1)
결과로 가져오는 데이터는 A2:B100이지만 정렬 기준은 C2:C100입니다.
따라서 실적순으로 정렬하면서 결과에는 이름과 부서만 표시할 수 있습니다.
SORTBY라는 이름처럼 ‘무엇을 표시할 것인지’와 ‘무엇을 기준으로 정렬할 것인지’를 구분해서 지정할 수 있다는 것이 장점입니다.
FILTER + SORTBY 함수 함께 사용하기
SORTBY 함수는 FILTER 함수와 조합하면 더욱 유용합니다.
예를 들어 직원 데이터에서 영업팀 직원만 추출하고 실적이 높은 순서로 정렬하고 싶다고 가정해 보겠습니다.
이때는 FILTER로 추출된 결과 자체를 기준으로 정렬하도록 구성하는 것이 안전합니다.
예를 들어 LET 함수를 함께 사용할 수 있는 환경이라면 다음과 같이 작성할 수 있습니다.
=LET(data,FILTER(A2:D100,B2:B100=”영업팀”),SORTBY(data,CHOOSECOLS(data,4),-1))
먼저 FILTER 함수로 영업팀 데이터를 추출하고, 그 결과의 네 번째 열인 실적을 기준으로 SORTBY가 내림차순 정렬합니다.
단순한 경우에는 SORT 함수를 이용해 다음처럼 작성하는 것도 간결합니다.
=SORT(FILTER(A2:D100,B2:B100=”영업팀”),4,-1)
따라서 조건별 데이터를 추출한 뒤 정렬하는 작업에서는 상황에 따라 SORT와 SORTBY 중 더 읽기 쉬운 방법을 선택하면 됩니다.
드롭다운과 SORTBY 함수 활용하기
드롭다운에서 선택한 조건에 따라 데이터를 추출하고 정렬하는 조회표도 만들 수 있습니다.
예를 들어 F2 셀에 부서를 선택하는 드롭다운이 있다고 가정하겠습니다.
F2에서
영업팀
개발팀
총무팀
등을 선택할 수 있도록 설정합니다.
선택한 부서의 데이터만 가져온 뒤 실적순으로 정렬하려면 FILTER와 정렬 함수를 함께 사용할 수 있습니다.
예를 들어 다음과 같이 구성할 수 있습니다.
=SORT(FILTER(A2:D100,B2:B100=F2),4,-1)
이제 F2에서 영업팀을 선택하면 영업팀 직원만 실적이 높은 순서대로 표시되고, 개발팀을 선택하면 개발팀 직원만 다시 자동으로 정렬됩니다.
드롭다운 → FILTER → SORT
조합만으로도 간단한 데이터 조회 화면을 만들 수 있습니다.
UNIQUE + SORTBY 함수 활용하기
UNIQUE 함수와 SORTBY 함수도 함께 활용할 수 있습니다.
예를 들어 B열의 부서에서 중복을 제거한 목록을 만들고 싶다면 기본적으로
=UNIQUE(B2:B100)
을 사용할 수 있습니다.
단순히 중복 제거 후 문자순으로 정렬하는 목적이라면
=SORT(UNIQUE(B2:B100))
처럼 SORT 함수가 더 간단합니다.
SORTBY는 고유값 목록을 다른 값이나 계산 결과를 기준으로 정렬해야 할 때 활용도가 높습니다.
즉, 모든 경우에 SORTBY를 사용하는 것이 아니라 정렬 목적에 따라 SORT와 SORTBY를 구분해서 사용하는 것이 좋습니다.
원본 데이터가 변경되면 자동으로 정렬될까?
SORTBY 함수는 동적 배열 함수이므로 원본 값이 변경되면 정렬 결과도 자동으로 다시 계산됩니다.
예를 들어 실적이
120 → 180
으로 변경되면 해당 직원의 위치가 새로운 실적에 맞게 변경됩니다.
따라서 데이터가 계속 수정되는 상황에서 매번
데이터 → 정렬
을 다시 실행할 필요가 없습니다.
특히 실적표, 매출표, 재고 현황처럼 숫자가 자주 변경되는 자료에서 유용합니다.
SORTBY 함수에서 #SPILL! 오류가 발생한다면?
SORTBY 함수는 하나의 셀에 수식을 입력해도 결과가 여러 셀로 자동 확장됩니다.
이것을 동적 배열이라고 합니다.
결과가 표시되어야 할 영역에 이미 다른 데이터가 있거나 병합된 셀이 있으면 정상적으로 결과를 표시하지 못할 수 있습니다.
이때 대표적으로 나타나는 오류가
#SPILL!
입니다.
SORTBY 함수에서 #SPILL! 오류가 발생한다면 결과가 표시될 주변 영역에 기존 데이터나 병합된 셀이 있는지 확인해 보시기 바랍니다.
SORTBY 함수가 안 될 때 확인할 사항
SORTBY는 동적 배열을 지원하는 엑셀 환경에서 사용할 수 있습니다.
수식을 입력했는데 함수 이름이 인식되지 않거나 #NAME? 오류가 발생한다면 사용 중인 엑셀 버전에서 SORTBY 함수를 지원하는지 확인할 필요가 있습니다.
지원되지 않는 버전에서는 엑셀의 기본 정렬 기능이나 다른 방식으로 데이터를 정렬해야 할 수 있습니다.
엑셀 SORTBY 함수 사용법 정리
SORTBY 함수의 기본 구조는 다음과 같습니다.
=SORTBY(array,by_array1,[sort_order1],…)
실적을 높은 순서대로 정렬하려면
=SORTBY(A2:D100,D2:D100,-1)
처럼 사용할 수 있습니다.
부서 오름차순 → 실적 내림차순처럼 두 가지 기준으로 정렬하려면
=SORTBY(A2:D100,B2:B100,1,D2:D100,-1)
을 사용합니다.
세 가지 조건이라면
=SORTBY(A2:D100,B2:B100,1,C2:C100,1,D2:D100,-1)
처럼 조건을 계속 추가할 수 있습니다.
SORT 함수와 비교하면
=SORT(A2:D100,4,-1)
처럼 열 번호를 지정하는 것이 SORT이고,
=SORTBY(A2:D100,D2:D100,-1)
처럼 정렬 기준 범위를 직접 지정하는 것이 SORTBY라고 이해하면 쉽습니다.
단순히 하나의 열을 기준으로 정렬한다면 SORT 함수만으로 충분한 경우가 많지만, 여러 조건을 적용하거나 결과 범위와 별도의 데이터를 기준으로 정렬해야 한다면 SORTBY 함수가 유용합니다.
FILTER, UNIQUE, 드롭다운과 같은 기능까지 함께 활용하면 조건 선택부터 데이터 추출, 중복 제거, 자동 정렬까지 연결된 엑셀 조회표를 만들 수 있으므로 데이터 관리 업무를 자주 한다면 함께 알아두는 것을 추천합니다.
“엑셀 SORTBY 함수 사용법 (여러 조건으로 자동 정렬)”에 대한 1개의 생각