엑셀의 전통적인 필터링(자동 필터, 고급 필터)과 정렬(기본, 다단계, 사용자 지정) 기능에 이어 엑셀 365 사용자를 위한 혁신적인 동적 배열 정렬 함수들을 알아볼 차례입니다. 이 함수들은 원본 데이터를 직접 변경하지 않고도, 수식을 통해 원하는 기준으로 정렬된 새로운 데이터 배열을 동적으로 생성합니다.
SORT와 SORTBY 함수를 통해 엑셀 데이터 분석의 유연성과 자동화를 한 차원 끌어올리고, 동적인 보고서와 대시보드를 구축하는 데 필수적인 고급 기술 사용할 수 있습니다.
왜 동적 정렬 함수를 마스터해야 할까요?
기존의 엑셀 정렬 기능은 원본 데이터의 순서를 직접 바꿉니다. 이는 때때로 편리하지만, 다음과 같은 한계가 있습니다.
- 원본 데이터 변경: 원본 데이터의 순서가 바뀌므로, 다른 분석에 영향을 줄 수 있습니다.
- 수동 실행: 정렬 기준이 변경될 때마다 수동으로 '정렬' 기능을 다시 실행해야 합니다.
- 동적 보고서의 한계: 사용자의 입력에 따라 실시간으로 정렬 순서가 바뀌는 동적 보고서 구현이 어렵습니다.
SORT와 SORTBY 함수는 이러한 문제들을 해결합니다.
- 비파괴적 정렬: 원본 데이터는 그대로 유지하면서, 정렬된 새로운 데이터 배열을 별도의 공간에 반환합니다.
- 자동 업데이트: 수식 기반이므로, 원본 데이터나 정렬 기준이 변경되면 결과가 자동으로 업데이트됩니다.
- 동적 보고서 구현: 사용자의 선택에 따라 정렬 기준이나 순서를 실시간으로 변경하는 대시보드를 쉽게 만들 수 있습니다.
이 동적 배열 함수들은 엑셀 365 사용자의 데이터 분석 워크플로우를 혁신적으로 변화시키는 '게임 체인저'입니다.
주요 함수들
다음 동적 배열 정렬 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- SORT: 배열을 하나 이상의 열을 기준으로 정렬하여 새 배열 반환
- SORTBY: 배열을 하나 이상의 다른 배열(열)을 기준으로 정렬하여 새 배열 반환
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 동적 정렬의 강력함과 편리함을 경험해 봅시다.
샘플 데이터는 아래 '판매 기록' 테이블을 사용하겠습니다.
| 주문 ID | 고객명 | 제품 카테고리 | 제품명 | 판매액 (천 원) | 주문일 | 지역 |
| ORD001 | 김철수 | 전자제품 | 노트북 | 1,200 | 2025-05-10 | 서울 |
| ORD002 | 이영희 | 의류 | 티셔츠 | 80 | 2025-05-12 | 부산 |
| ORD003 | 박지성 | 전자제품 | 스마트폰 | 950 | 2025-05-15 | 서울 |
| ORD004 | 최민수 | 식품 | 과일 | 50 | 2025-05-18 | 대구 |
| ORD005 | 김철수 | 의류 | 바지 | 150 | 2025-05-20 | 부산 |
| ORD006 | 이영희 | 전자제품 | 태블릿 | 800 | 2025-06-01 | 서울 |
| ORD007 | 박지성 | 식품 | 채소 | 40 | 2025-06-05 | 대구 |
| ORD008 | 최민수 | 의류 | 스커트 | 110 | 2025-06-08 | 부산 |
| ORD009 | 김철수 | 전자제품 | 노트북 | 1,100 | 2025-06-10 | 대전 |
| ORD010 | 이영희 | 의류 | 코트 | 300 | 2025-06-15 | 서울 |
1. SORT: 배열을 하나 이상의 열을 기준으로 정렬
SORT 함수는 지정된 배열(범위)을 선택한 열의 값에 따라 정렬하여 새로운 배열로 반환합니다. 가장 단순한 형태의 동적 정렬입니다.
- 기본 형식: =SORT(배열, [정렬_기준_인덱스], [정렬_순서], [세로_정렬_여부])
- 배열: 정렬할 전체 데이터 범위.
- 정렬_기준_인덱스 (선택 사항): 배열 내에서 정렬 기준으로 사용할 열의 번호 (1부터 시작). 생략 시 첫 번째 열이 기준.
- 정렬_순서 (선택 사항): 1 (오름차순, 기본값) 또는 -1 (내림차순).
- 세로_정렬_여부 (선택 사항): TRUE (기본값, 열을 기준으로 행을 정렬) 또는 FALSE (행을 기준으로 열을 정렬).
실무 예제 1 (단일 기준 정렬): 전체 판매 기록을 '판매액 (천 원)'을 기준으로 내림차순 정렬하여 새로운 테이블로 표시하세요.
해결 방법: I1 셀 (데이터가 나타날 시작 셀)에 다음 수식을 입력합니다. =SORT(A1:G11, 5, -1)
- A1:G11: 원본 데이터 범위 (헤더 포함).
- 5: '판매액 (천 원)' 열이 다섯 번째 열이므로 5.
- -1: 내림차순 정렬.
결과: I1 셀부터 원본 데이터가 '판매액 (천 원)' 기준 내림차순으로 정렬되어 동적으로 펼쳐집니다. (ORD001(1200), ORD009(1100), ORD003(950), ... 순)
최상급 실무 활용: 동적인 순위표 생성, 실시간 성과 대시보드, 특정 기준에 따른 데이터 목록 자동 업데이트 등 원본 데이터를 건드리지 않고 정렬된 결과를 즉시 확인해야 할 때 매우 유용합니다.
정렬 기준 인덱스와 순서 인수를 다른 셀에 입력하고 수식에 참조시키면 사용자가 드롭다운 목록 등으로 정렬 기준을 선택할 수 있는 완전히 동적인 정렬 시스템을 만들 수 있습니다.
2. SORTBY: 여러 기준 또는 다른 배열을 기준으로 정렬
SORTBY 함수는 SORT보다 더 강력합니다. 지정된 배열(원본 데이터)을 정렬하되, 정렬 기준으로 사용할 하나 이상의 다른 배열(열)을 지정하여 정렬할 수 있습니다. 이는 다단계 정렬을 수식으로 구현하는 핵심 함수입니다.
- 기본 형식: =SORTBY(배열, 기준_배열1, [정렬_순서1], [기준_배열2], [정렬_순서2], ...)
- 배열: 정렬하여 반환할 전체 데이터 범위.
- 기준_배열1: 첫 번째 정렬 기준으로 사용할 열 또는 행 범위 (원본 배열 내에 있을 필요 없음).
- 정렬_순서1 (선택 사항): 1 (오름차순, 기본값) 또는 -1 (내림차순).
- 기준_배열2, 정렬_순서2, ...: 추가적인 정렬 기준 (다단계 정렬).
실무 예제 1 (다단계 정렬): 전체 판매 기록을 '지역'을 기준으로 오름차순 정렬하고, 동일 지역 내에서는 '판매액 (천 원)'을 기준으로 내림차순 정렬하여 새로운 테이블로 표시하세요.
해결 방법: I1 셀에 다음 수식을 입력합니다. =SORTBY(A1:G11, G1:G11, 1, E1:E11, -1)
- A1:G11: 정렬할 원본 데이터.
- G1:G11: 첫 번째 정렬 기준인 '지역' 열. 1은 오름차순.
- E1:E11: 두 번째 정렬 기준인 '판매액 (천 원)' 열. -1은 내림차순.
결과: I1 셀부터 원본 데이터가 '지역' 기준 오름차순, '판매액' 기준 내림차순으로 정렬되어 동적으로 펼쳐집니다. (대구->대전->부산->서울 순으로 지역 정렬, 각 지역 내에서 판매액 높은 순)
최상급 실무 활용: 동적인 다단계 순위표, 조직별/직급별 성과 보고서, 복잡한 분류 기준에 따른 데이터 정렬 등 전통적인 정렬 기능의 다단계 정렬을 수식으로 구현합니다. SORTBY의 가장 큰 장점은 정렬 기준 열(기준_배열)이 원본 배열 안에 포함될 필요가 없다는 점입니다. 즉, 보조 계산 열을 정렬 기준으로 사용할 수도 있습니다.
실무 예제 2 (사용자 지정 목록 정렬의 동적 구현 - 응용): SORTBY 함수는 사용자 지정 목록을 직접 지원하지 않지만, MATCH 함수와 결합하여 유사한 효과를 낼 수 있습니다. '제품 카테고리'를 '전자제품', '의류', '식품' 순서로 정렬하여 표시하세요.
해결 방법:
- 사용자 지정 순서 정의: 비어있는 셀(예: J15:J17)에 원하는 정렬 순서를 입력합니다.
- J15: 전자제품
- J16: 의류
- J17: 식품
- I1 셀에 다음 수식을 입력합니다. =SORTBY(A1:G11, MATCH(C1:C11, J15:J17, 0))
- MATCH(C1:C11, J15:J17, 0): '제품 카테고리' 열(C1:C11)의 각 항목이 J15:J17 목록에서 몇 번째 위치하는지 숫자로 반환합니다. (예: "전자제품"은 1, "의류"는 2, "식품"은 3) SORTBY는 이 숫자 배열을 기준으로 정렬합니다.
결과: I1 셀부터 원본 데이터가 '전자제품' → '의류' → '식품' 순으로 정렬되어 동적으로 펼쳐집니다.
최상급 실무 활용: 고객 등급, 프로젝트 우선순위, 직급 등 미리 정의된 텍스트 순서에 따른 동적인 정렬이 필요할 때 매우 유용합니다. MATCH 함수와 SORTBY를 결합하면 전통적인 사용자 지정 정렬 기능을 수식으로 자동화하여, 유연하고 재사용 가능한 정렬 시스템을 구축할 수 있습니다.
엑셀 365 사용자를 위한 혁신적인 동적 배열 정렬 함수들인 SORT와 SORTBY은 원본 데이터를 변경하지 않고도, 수식을 통해 원하는 기준으로 정렬된 새로운 배열을 동적으로 생성함으로써, 데이터 분석의 유연성과 자동화를 획기적으로 향상시킵니다.
특히 SORTBY는 다단계 정렬과 MATCH를 활용한 사용자 지정 목록 정렬까지 동적으로 구현할 수 있게 해줍니다.