메뉴
실무 위키›응용 공식›엑셀 숫자와 문자가 섞인 코드를 번호 순서대로 정렬하는 공식

엑셀 숫자와 문자가 섞인 코드를 번호 순서대로 정렬하는 공식

2021
지원 버전 자세히 보기
엑셀 2021엑셀 2024M365웹 엑셀

같은 문자 접두사가 붙은 품목 코드를 숫자 부분의 실제 크기대로 정렬합니다.

예제파일 다운로드
엑셀 숫자와 문자가 섞인 코드를 번호 순서대로 정렬하는 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=SORT ( 정렬할 범위, 3, TRUE )
=VALUE ( SUBSTITUTE ( 코드 셀, 제거할 문자, "" ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
정렬할 범위필수품목 코드, 품목명, VALUE 함수로 바꾼 숫자 정렬값을 함께 포함한 범위입니다.
코드 셀필수문자 접두사와 번호가 함께 입력된 첫 코드 셀이며, 아래로 채울 때 각 행의 코드를 참조하는 셀입니다.
제거할 문자필수모든 코드에 공통으로 붙은 문자 접두사입니다. 이 문자를 지운 뒤 숫자만 남아야 하는 기준값입니다. 예) PRD-
E2=SORT(A2:C8,3,TRUE)
C2=VALUE(SUBSTITUTE(A2,$J$2,""))
A
B
C
D
E
F
G
H
I
J
K
1
품목 코드
품목명
숫자 정렬값
정렬 코드
품목명
정렬값
제거 기준
값
2
PRD-100
모니터 암
100
PRD-1
USB 허브
1
공통 문자
PRD-
3
PRD-2
노트북 받침대
2
PRD-2
노트북 받침대
2
4
PRD-11
무선 키보드
11
PRD-3
무선 마우스
3
5
PRD-1
USB 허브
1
PRD-10
키보드 손목받침
10
6
PRD-20
웹캠
20
PRD-11
무선 키보드
11
7
PRD-3
무선 마우스
3
PRD-20
웹캠
20
8
PRD-10
키보드 손목받침
10
PRD-100
모니터 암
100
9
모든 코드는 PRD-숫자 형식으로 통일합니다. PRD-10을 글자로 정렬하면 PRD-2보다 앞서지만, 숫자 정렬값 10과 2로 바꾸면 번호 크기대로 정렬됩니다.
이 공식이 사용하는 함수
SORT 함수 VALUE 함수 SUBSTITUTE 함수

동작 원리

SUBSTITUTE 함수가 공통 문자 접두사를 지우고, VALUE 함수가 남은 번호를 숫자로 바꾸면 SORT 함수가 번호의 크기대로 행을 정렬합니다.

예제 시트에서 PRD-100의 숫자 정렬값 C2 셀과 번호순 목록이 시작되는 E2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

코드에서 공통 문자를 지웁니다

A2의 PRD-100에서 J2의 공통 문자 PRD-를 지우면 숫자 모양의 텍스트 100이 남습니다.

=SUBSTITUTE ( A2, $J$2, "" )
= "100" 숫자처럼 보이지만 아직 텍스트입니다.
2

남은 번호를 숫자로 바꿉니다

VALUE 함수가 텍스트 100을 숫자 100으로 바꿉니다. 따라서 2, 10, 100처럼 자릿수가 달라도 실제 크기를 비교할 수 있습니다.

=VALUE ( "100" )
= 100
3

숫자 정렬값이 작은 행부터 배치합니다

세 번째 열의 숫자 정렬값 1, 2, 3, 10, 11, 20, 100을 오름차순으로 정렬하면 품목 코드와 품목명이 같은 행 단위로 함께 이동합니다.

=SORT ( A2:C8, 3, TRUE )
= {PRD-1, USB 허브, 1; PRD-2, 노트북 받침대, 2; PRD-3, 무선 마우스, 3; PRD-10, 키보드 손목받침, 10; PRD-11, 무선 키보드, 11; PRD-20, 웹캠, 20; PRD-100, 모니터 암, 100} E2:G8 범위에 일곱 행으로 표시됩니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료