엑셀 텍스트 숫자 정렬, SORT/SORTBY 함수 활용 팁

 

 

엑셀에서 텍스트와 숫자가 혼합된 데이터를 정렬하는 것은 생각보다 까다롭습니다. 기본 정렬 기능의 한계를 극복하고, SORT와 SORTBY 함수를 활용하여 데이터를 효율적으로 정렬하는 방법을 알아보세요. 텍스트 안에 숨어있는 숫자까지 정확하게 인식하여 정렬하는 전문가 수준의 팁을 지금 바로 확인하세요!

텍스트 숫자 정렬, 왜 어려울까요? 🤔

엑셀 기본 정렬은 텍스트에 포함된 숫자를 숫자값이 아닌 문자열의 일부로 인식합니다. "제품1", "제품10", "제품2"를 정렬하면 "제품1", "제품2", "제품10" 순서가 되어야 할 것 같지만, 엑셀은 "제품1", "제품10", "제품2" 순서로 정렬합니다. 🤦‍♀️ 이런 딜레마를 해결하려면 어떻게 해야 할까요? SORT와 SORTBY 함수가 바로 그 해답입니다!

기본 정렬의 한계

기본 정렬 기능은 텍스트 전체를 사전식 순서로 배열합니다. 숫자의 크기를 고려하지 않기 때문에, 텍스트 중간에 삽입된 숫자를 기준으로 정렬하기 어렵습니다. 예를 들어, 파일 이름이나 제품 코드처럼 텍스트와 숫자가 혼합된 데이터를 정렬할 때 문제가 발생할 수 있습니다.

SORT 함수: 보조 열을 이용한 정렬

SORT 함수는 지정한 열을 기준으로 데이터 범위를 정렬합니다. 텍스트에서 숫자 부분만 추출하여 보조 열을 만들고, 이를 기준으로 정렬하는 방법입니다.

보조 열 생성: 텍스트에서 숫자 추출하기

MID, VALUE, LEFT, RIGHT, FIND, LEN 함수 등을 조합하여 텍스트에서 숫자 부분만 추출하고 숫자 값으로 변환합니다. 예를 들어, "제품001_A" 형식의 데이터에서 숫자 "001"을 추출하려면 =VALUE(MID(A1,4,3)) 수식을 사용할 수 있습니다. 텍스트 구조가 복잡할 경우, FIND 함수를 사용하여 특정 문자의 위치를 찾고, LEFT, RIGHT, MID 함수를 조합하여 원하는 부분을 추출할 수 있습니다.

SORT 함수 적용: 깔끔하게 정렬하기

=SORT(정렬할 범위, 기준 열 번호, 정렬 순서, [기준 열 방향]) 형식으로 SORT 함수를 사용합니다. 정렬 순서는 1(오름차순) 또는 -1(내림차순)로 지정하고, 필요에 따라 기준 열 방향(1: 행 방향, 0: 열 방향)을 지정할 수 있습니다. 동적 배열 함수의 특성상 결과는 자동으로 출력됩니다. 만약 "제품001_A" 부터 "제품010_A" 까지의 데이터가 A1:A10에 있다면, 보조 열을 B열에 생성하고 =SORT(A1:B10, 2, 1) 수식을 사용하여 숫자 순서대로 정렬할 수 있습니다.

다중 조건 정렬: 한 번에 여러 기준 적용하기

SORT 함수는 여러 열을 기준으로 정렬할 수 있습니다. 예를 들어, =SORT(A1:C10, 2, 1, 3, -1) 수식은 2번째 열을 오름차순으로 정렬한 후, 같은 값을 가진 행들을 3번째 열을 내림차순으로 정렬합니다. 이를 활용하면 제품 번호와 버전 정보 등 다양한 기준으로 데이터를 정렬할 수 있습니다.

SORTBY 함수: 유연한 정렬 기준 설정

SORTBY 함수는 보조 열 없이도 텍스트 내의 숫자를 기준으로 정렬할 수 있습니다.

SORTBY 함수 적용: 보조 열 없이 정렬하기

=SORTBY(정렬할 배열, 기준 배열1, [정렬 순서1], [기준 배열2, 정렬 순서2], ...) 형식으로 사용합니다. MID 함수와 VALUE 함수, 혹은 *1과 같은 텍스트를 숫자로 변환하는 연산자를 조합하여 기준 배열을 생성합니다. 예를 들어, "제품001_A" 부터 "제품010_A" 까지의 데이터가 A1:A10에 있다면, =SORTBY(A1:A10,MID(A1:A10,4,3)*1,1) 수식을 사용하여 숫자 순서대로 정렬할 수 있습니다.

다중 정렬 기준: SORTBY 함수에서도 가능!

SORTBY 함수 역시 여러 기준을 사용할 수 있습니다. =SORTBY(A1:A10,MID(A1:A10,4,3)*1,1,RIGHT(A1:A10,1),-1) 수식은 숫자 부분을 오름차순으로 정렬한 후, 마지막 문자를 내림차순으로 정렬합니다.

오류 처리: IFERROR 함수로 안전하게!

텍스트에서 숫자를 추출하는 과정에서 오류가 발생할 수 있습니다. 예를 들어 숫자가 아닌 텍스트가 포함된 경우 VALUE 함수는 오류를 반환합니다. IFERROR 함수를 사용하여 오류 발생 시 특정 값을 반환하도록 설정하여 이러한 문제를 방지할 수 있습니다. 예를 들어 =IFERROR(VALUE(MID(A1,4,3)),0) 수식은 숫자 추출에 실패할 경우 0을 반환합니다.

SORT vs. SORTBY: 상황에 맞는 함수 선택

SORT 함수는 보조 열을 사용하지만, 정렬 기준이 명확하게 드러나 가독성이 좋습니다. SORTBY 함수는 보조 열 없이 간결하게 수식을 작성할 수 있지만, 복잡한 정렬 기준을 사용하는 경우 가독성이 떨어질 수 있습니다. 데이터 구조와 정렬 기준의 복잡도를 고려하여 적절한 함수를 선택하세요.

실무 활용 팁: 더욱 강력한 정렬 기술!

• 대소문자 구분 없이 정렬: EXACT 함수를 활용하여 대소문자를 구분하지 않고 정렬할 수 있습니다.
• 특정 문자 제거 후 정렬: SUBSTITUTE 함수를 사용하여 특정 문자를 제거한 후 정렬할 수 있습니다. 예를 들어, "-" 문자를 제거하고 숫자를 기준으로 정렬할 수 있습니다.
• 조건부 정렬: FILTER 함수와 SORT 또는 SORTBY 함수를 조합하여 특정 조건을 만족하는 데이터만 정렬할 수 있습니다.

SORT와 SORTBY 함수는 엑셀에서 데이터를 정렬하는 강력한 도구입니다. 이러한 함수들을 능숙하게 활용하면 데이터 분석 및 관리 업무의 효율성을 크게 향상시킬 수 있습니다. 다양한 상황에 맞는 정렬 기법을 연습하고 적용하여 데이터 분석 전문가로서 한 단계 더 성장하세요! 😊👍