혼합 숫자와 텍스트를 정렬하는 방법, Excel의 다단계 번호

이 자습서는 Excel에서 영숫자 문자열을 주문하는 일반적인 문제를 설명하고 숫자와 텍스트로 숫자를 숫자로 정렬하는 작업 솔루션을 제공합니다.

Excel에서 별도로 텍스트와 숫자를 정렬하는 것은 ABC 또는 123만큼 쉽습니다. :) 제품 ID, SKU 및 문자와 숫자가 모두있는 다른 값을 다시 정리하면 문제가 발생할 수 있습니다. Excel의 알파벳 정렬은 문자열의 숫자 부분을 별도로 처리 할 수 ​​없습니다. 영숫자 찌르기가 어떻게 정렬되는지에 대해 더 많은 제어를 원한다면 아래 예제를 살펴보십시오.

• 텍스트로 숫자를 정렬합니다
• 혼합 숫자와 텍스트를 정렬하십시오
• 한 번에 문자열과 숫자로 문자열을 정렬하십시오
• 다단계 / 계층 번호를 정렬하십시오
• 텍스트로 접두사로 된 다단계 번호를 정렬하십시오

Excel의 텍스트로 숫자를 정렬하십시오

숫자 열을 가장 작은 것에서 가장 큰 Excel로 정렬 할 때 :

1, 2, 2, 3, 11, 12, 13, 110, 120, 130

때때로 다음과 같은 것을 얻을 수 있습니다.

1, 11, 110, 12, 120, 13, 130, 2, 3

숫자가 실제로 텍스트 일 ​​때 발생하며 문자를 기반으로 배열 된 단어와 같은 텍스트로 정렬 할 때 "Text -Numbers"는 값 대신 숫자를 기준으로 정렬됩니다.

대상 열에 텍스트로 형식화 된 숫자가 포함 된 경우 숫자를 정상적으로 정렬하는 데 필요한 모든 것은 텍스트를 숫자로 변환하는 것입니다. 결과는 아래 스크린 샷의 C 열이 있습니다.

반대로 숫자를 텍스트로 정렬 하려면 숫자를 먼저 텍스트로 변환 한 다음 홈 탭> 편집 그룹> 정렬 및 필터 > 정렬 A -Z를 클릭하십시오.

결과는 이전 스크린 샷의 A 열 A에서와 같습니다.

혼합 숫자와 텍스트를 Excel로 정렬하는 방법

내장 된 Excel 정렬 기능을 사용하면 영숫자 문자열 (결합 된 텍스트 및 숫자)은 항상 텍스트, 즉 문자 별 글로 숫자로 정렬됩니다. 문자 접두사 또는 접미사가 포함 된 숫자 열을 숫자로 정렬하려면 아래 단계를 수행하십시오. 그리고 우리가 달성하려는 결과는 다음과 같습니다.

Excel이 영숫자 문자열의 텍스트로 숫자를 처리하는 것을 방지하려면 헬퍼 열에서 해당 숫자를 추출하고 해당 열로 정렬합니다. 이렇게하면 원래 데이터를 변경하지 않아도되지만 원하는 방식으로 재 배열 할 수 있습니다.

1. 원래 값 옆의 열에 문자열에서 숫자를 추출하는 공식을 입력하십시오.

   이 예에서는 하이픈 ( "-") 후에 모든 문자를 간단히 추출 할 수 있습니다. Excel 365에서는 TextAfter 함수를 사용하여 수행 할 수 있습니다. 다른 텍스트 함수와 마찬가지로, 출력은 문자 나 숫자를 검색하는지 여부에 관계없이 항상 텍스트이므로 결과를 1로 곱하여 텍스트 문자열을 숫자로 변환합니다.

   =TEXTAFTER(A2, "-")*1

   구형 Excel 버전에서는 특정 문자 후 텍스트를 추출하는 세 가지 다른 기능의 조합을 사용하여 동일한 결과를 얻을 수 있습니다. 또한 *1 작업은 텍스트 출력을 숫자로 변환하기 위해 수행됩니다.

   =RIGHT(A2, LEN(A2) - SEARCH("-", A2)) *1

   

   팁. 셀의 모든 위치에서 숫자를 신속하게 추출하려면 Excel의 Ultimate Suite에 포함 된 추출 도구를 사용할 수 있습니다.

   

2. 추출한 숫자로 원본 데이터를 정렬하십시오. 숫자 를 선택한 경우 홈 탭> 정렬 및 필터 >로 이동하십시오. 팝업 된 대화 상자에서 선택 옵션 확장을 선택하고 정렬을 클릭하십시오. 자세한 내용은 줄을 정리하고 유지하는 방법을 참조하십시오.

   

   이제 문자와 숫자가 포함 된 문자열은 숫자로 정렬되며 필요한 경우 도우미 열을 제거하거나 숨길 수 있습니다.

   

한 번에 텍스트와 숫자로 문자열을 정렬하십시오

소스 문자열에 몇 가지 다른 요소가있는 경우 각 요소가 별도의 열에서 추출한 다음 여러 열로 정렬 할 수 있습니다. 결과적으로 문자열은 아래 스크린 샷의 D 열에서 마찬가지로 배열됩니다.

데이터 세트의 모든 문자열이 동일한 패턴을 가지므로 텍스트와 숫자를 다른 열로 분할하는 가장 쉬운 방법은 정규식을 사용하는 것입니다. 이것은 RegexPextract라는 사용자 정의 함수의 도움으로 수행 할 수 있습니다. 먼저이 튜토리얼에서 설명한대로 코드를 Excel에 추가합니다. 그 후 다음 공식을 사용하십시오.

셀 A2에서 텍스트의 첫 번째 발생을 추출하기 위해 B2의 공식은 다음과 같습니다.

=RegExpExtract(A2, "[^\d] ", 1)

첫 번째 숫자를 추출하려면 C2의 공식은 다음과 같습니다.

=RegExpExtract(A2, "\d ", 1)*1

두 번째 숫자를 추출하려면 D2의 공식은 다음과 같습니다.

=RegExpExtract(A2, "\d ", 2)*1

2 ^ND 및 3 ^RD 공식에서, 우리는 REGEXPEXTRART 출력에 1을 곱하여 추출 된 하위 문자열을 숫자로 바꿉니다.

Ultimate Suite 사용자는 Regex 도구를 활용하여 동일한 결과를 얻을 수 있습니다. 추출 된 숫자 문자열 (열 C 및 D)을 숫자로 변환하기 위해 하나 더 빠른 작업을 수행하면됩니다.

텍스트와 숫자가 분할되면 데이터 세트를 여러 열로 정렬하십시오.

1. 모든 열 (A2 : D16)을 선택하십시오.
2. 데이터 탭에서 정렬 및 필터 그룹의 정렬 버튼을 클릭하십시오.
3. 필요한만큼의 정렬 레벨을 추가하고 원하는 정렬 순서를 선택하십시오.
4. 완료되면 확인을 클릭합니다.

우리의 경우, 선택된 범위는 먼저 A에서 Z까지 텍스트 로, 그 다음 1 번 , 그리고 가장 작은 것부터 가장 큰 것부터 2 번 으로 정렬됩니다.

결과는 정확히 우리가 찾고 있던 것입니다.

팁. 문자열을 다르게 다시 정리하려면 정렬 대화 상자에서 레벨 순서를 변경하십시오.

Excel에서 다단계 / 계층 숫자를 정렬하는 방법

Microsoft Excel에서는 1.1, 1.1.1, 1.1.2와 같은 다단계 숫자가 문자열이며 숫자가 아닌 텍스트로 정렬합니다.

다단계 문자열을 숫자로 정렬하려면 다음 구문이있는 사용자 정의 사용자 정의 함수를 사용할 수 있습니다.

HierarchYnumber (범위, del, max_level)

어디:

• 범위 는 정렬 할 범위입니다.
• DEL은 레벨, 일반적으로 포인트 (.)를 분리하는 데 사용되는 구분 기자입니다.
• Max_Level 은 계층 구조의 최대 레벨 수입니다.

그리고 다음은 기능의 코드입니다.

계층 구조 번호를 정렬하는 사용자 정의 기능

함수 hierarchynumber ( 범위 로 범위, del as string , max_level 은 정수 로 max_level) 변형 부품 = split (range.value, del) index = 0 ~ ubound ( part ) hierarchynumber = hierarchynumber part (index) * (10 ^ ( max_level -index)))

함수는 다단계 숫자 문자열을 정렬 할 수있는 숫자를 생성하는 것입니다. 보다 정확하게는 계층 문자열을 개별 번호 (부품)로 나누고 아래 알고리즘을 기반으로 각 부분을 처리하고 결과를 추가합니다.

part(Index) * (10 ^ ((level - Index) * 2))

예를 들어, 1.1의 경우 함수는이 결과를 생성합니다.

1*(10^((2-0)*2)) 1*(10^((2-1)*2)) = 10100

1.2의 경우 :

1*(10^((2-0)*2)) 2*(10^((2-1)*2)) = 10200

사용자 정의 함수를 사용하여 계층 숫자를 정렬하는 방법 :

1. HierarchYnumber 함수의 코드를 통합 문서의 표준 코드 모듈에 삽입하고 매크로 가능 통합 문서 (.XLSM)로 저장하십시오. 자세한 지침은 여기에 있습니다.
2. 첫 번째 계층 문자열 옆에있는 빈 셀에서 계층 동력 공식을 입력하고 Enter 키를 누릅니다.
3. 필요한만큼의 행을 통해 공식을 아래로 드래그하십시오.
4. 공식 열에 따라 데이터 세트를 정렬하십시오.

A 열 A에서 최대 3 레벨의 다단계 숫자 문자열이 있다고 가정합니다. B2의 공식은 다음과 같습니다.

=HierarchyNumber(A2, ".", 3)

공식을 복사 한 후에는이 결과를 얻을 수 있습니다.

그런 다음 전체 데이터 세트 (A2 : B19)를 공식 열로 가장 작은 것부터 가장 큰 것부터 정렬합니다.

다단계 숫자 문자열을 숫자로 정렬하십시오.

텍스트로 접두사로 된 다단계 번호를 정렬하십시오

이 예제에서는 다단계 숫자가 "레벨 1.1.1"이라는 텍스트로 접두사가있는 약간 다른 경우에 대해 설명합니다. 첫눈에, 작업은 더 복잡하게 들리지만 실제로 솔루션은 더 간단합니다 :)

먼저 다단계 숫자 하위 문자열을 별도 열로 추출합니다. 이를 위해 Ultimate Suite에 포함 된 추출 도구를 사용할 수 있습니다.

또는 공간 후 모든 문자를 가져 오는 공식을 작성할 수 있습니다.

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

그런 다음 사용자 지정 정렬을하십시오. Excel 정렬 대화 상자에서, 먼저 추출 된 계층 구조 번호로 먼저 두 가지 레벨을 추가 한 다음 원래 문자열로 확인하고 확인을 클릭하십시오.

다음 경고가 팝업되며 " 숫자와 숫자를 별도로 정렬하기 "로 선택합니다.

결과적으로 다단계 숫자를 포함하는 문자열은 숫자로 정렬됩니다.

이것이 숫자 문자열과 다단계 숫자를 Excel로 정렬하는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙겠습니다!

사용 가능한 다운로드

Excel에서 혼합 번호 및 텍스트를 정렬합니다 - 예제 (.xlsm 파일) Ultimate Suite 완전 기능 시험판 (.exe 파일)

위 내용은 혼합 숫자와 텍스트를 정렬하는 방법, Excel의 다단계 번호의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!