Excel에서 거리 이름/번호로 주소를 정렬하는 방법은 무엇입니까?
Excel에서 주소 목록을 관리할 때, 일반적으로 거리 이름이나 번호에 따라 데이터를 정리하거나 분석해야 합니다. 예를 들어, 같은 거리에 사는 고객들을 그룹화해야 하거나, 집 번호 순서대로 배송을 처리해야 하는 경우, 이러한 구성 요소로 정렬하는 것이 필수적입니다. 그러나 일반적인 주소 형식에서는 거리 이름과 번호가 하나의 셀에 섞여 있어 단순한 정렬로는 원하는 결과를 얻을 수 없습니다. 이 문서에서는 Excel에서 거리 이름 또는 번호별로 주소를 정렬하는 실용적인 방법을 논의하고, 각 방법의 장점 및 적용 시나리오를 분석하며, 다양한 사용자 요구에 맞춘 문제 해결 및 대체 솔루션을 제공합니다.
Excel에서 도우미 열을 사용하여 거리 이름으로 주소 정렬하기
Excel에서 도우미 열을 사용하여 거리 번호로 주소 정렬하기
VBA를 사용하여 거리 이름 또는 번호를 자동으로 추출하고 정렬하기
도우미 열 없이 Power Query를 사용하여 거리 이름 또는 번호로 주소 정렬하기
Excel에서 도우미 열을 사용하여 거리 이름으로 주소 정렬하기
Excel에서 거리 이름으로 주소를 정렬하려면 먼저 도우미 열에 거리 이름만 추출해야 합니다. 이 접근법은 "123 Apple St"와 같이 주소 형식이 일관된 경우 간단하고 잘 작동합니다. 빠른 프로젝트나 간단한 주소 목록에 적합합니다.
주소 목록 옆의 빈 열을 선택하세요. 아래 공식을 도우미 열의 첫 번째 셀에 입력하여 거리 이름을 추출합니다:
=MID(A1,FIND(" ",A1)+1,255)(여기서 A1은 주소 데이터의 맨 위 셀을 가리키며, 데이터가 다른 위치에서 시작하면 조정하세요.)
공식을 입력한 후 Enter 를 누르고 채우기 핸들을 아래로 드래그하여 주소 범위의 모든 행에 공식을 적용합니다. 이 공식은 각 주소에서 첫 번째 공백을 찾아 그 이후의 모든 내용(거리 이름 및 접미사)을 반환합니다. 주소가 동일한 구조를 따르는지 확인하세요. 그렇지 않으면 공식이 예상대로 분리되지 않을 수 있습니다.
2. 도우미 열 전체(추출된 거리 이름이 있는 열)를 강조 표시한 다음, 데이터 탭으로 이동하여 오름차순 정렬(A to Z)을 클릭합니다. 이렇게 하면 거리 이름이 알파벳순(오름차순)으로 정렬됩니다.
3. 나타나는 정렬 경고 대화 상자에서 전체 선택 확장을 선택하여 정렬 시 전체 주소 정보가 함께 유지되도록 합니다.
4. 정렬을 클릭합니다. 이제 주소 목록이 거리 이름 기준으로 재정렬되어 유사한 거리가 함께 표시됩니다.
참고: 이 방법은 표준화된 주소 형식에서 가장 효과적입니다. 주소 셀에 불규칙한 패턴이나 거리 이름 앞에 여러 개의 공백이 포함된 경우 공식을 조정해야 할 수도 있습니다. 공식 사용 후 항상 몇 가지 결과를 확인하여 정확성을 점검하세요.
장점: 간단하며 추가 도구가 필요하지 않습니다.
단점: 일관된 형식에 의존; 주소 형식이 다양하면 추가 작업이 필요합니다.
Excel에서 도우미 열을 사용하여 거리 번호로 주소 정렬하기
배송 순서를 지정하거나 인접 주소를 식별하기 위해 주소 목록을 숫자로 된 거리 번호로 정렬해야 하는 경우, 번호를 추출하여 이를 정렬에 사용하는 것이 쉽습니다. 주소가 서로 다른 거리에 있더라도 효과적입니다.
1. 주소 목록 옆의 빈 셀에 다음 공식을 입력하여 거리 번호를 추출합니다:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(여기서 A1은 목록의 첫 번째 주소입니다 - 필요한 경우 조정하세요.) 입력 후 Enter 키를 누릅니다. 이 공식은 첫 번째 공백을 찾고 그 전의 문자를 반환한 다음, 이를 숫자 값으로 변환합니다. 주소에 거리 번호로 시작하는 숫자가 있는 경우 이 공식이 올바르게 작동합니다. 그런 다음 채우기 핸들을 아래로 드래그하여 나머지 목록에 공식을 적용합니다.
2. 방금 생성한 도우미 열을 선택하고, 데이터 탭으로 이동하여 오름차순 정렬(A to Z) 또는 최신 Excel 버전의 경우 가장 작은 것부터 큰 것 순으로 정렬(Sort Smallest to Largest)을 클릭합니다.
3. 정렬 경고 대화 상자에서 전체 선택 확장을 선택하여 전체 행을 정렬합니다.
4. 정렬을 클릭하여 적용합니다. 이제 주소가 추출된 거리 번호별로 정렬되었습니다.
팁: 거리 번호를 텍스트로 유지하고 싶거나 숫자 정렬을 수행할 필요가 없는 경우에도 다음과 같이 사용할 수 있습니다:
=LEFT(A1,FIND(" ",A1)-1)이 버전은 숫자를 텍스트 문자열로 추출합니다.
주의사항: 주소가 숫자가 아닌 단어로 시작하는 경우("Main Street5" 등), 이러한 공식은 의도대로 작동하지 않을 수 있습니다. 공식을 사용하기 전에 주소 데이터를 다시 한 번 확인하세요.
장점: 주소 형식이 간단할 경우 빠르고 사용자 친화적입니다.
단점: 숫자 앞에 이름/접미사가 있는 주소나 여러 개의 숫자가 있는 주소는 처리할 수 없습니다.
VBA 코드 - 거리 이름/번호를 추출하고 매크로를 사용하여 주소 목록을 자동으로 정렬
더 크고 복잡한 주소 목록을 다루거나 데이터에 다양한 주소 구조가 포함된 경우 VBA를 사용하여 정렬 과정을 자동화하는 것이 매우 효과적입니다. VBA를 사용하면 거리 이름이나 번호를 신속하게 추출하고 주소 목록을 자동으로 정렬하며 수작업을 최소화할 수 있습니다. 이 솔루션은 주기적으로 정렬을 수행해야 하거나 정렬을 워크플로에 통합하려는 경우 적합합니다.
참고: 이 VBA 매크로는 A열의 각 주소에서 첫 번째 공백 이후 부분(거리 이름)을 추출하고 이를 기반으로 전체 목록을 정렬합니다. 또한 약간의 수정을 통해 거리 번호를 추출하고 정렬하는 데도 사용할 수 있습니다.
1. 개발자 탭 > Visual Basic을 클릭합니다. 나타나는 창에서 삽입 > 모듈을 클릭하고 아래 VBA 코드를 모듈 창에 붙여넣으세요:
Sub SortAddressesByStreetName()
Dim ws As Worksheet
Dim lastRow As Long
Dim tempCol As Long
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
tempCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
' Create helper column with street names
For i = 1 To lastRow
ws.Cells(i, tempCol).Value = Trim(Mid(ws.Cells(i, 1).Value, InStr(ws.Cells(i, 1).Value, " ") + 1))
Next i
' Sort the whole data range by the helper column
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(1, tempCol), ws.Cells(lastRow, tempCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, tempCol))
.Header = xlNo
.Apply
End With
' Delete helper column
ws.Columns(tempCol).Delete
End Sub2코드를 실행하려면 주소 목록이 활성화된 상태에서 
버튼을 클릭하거나 F5를 누릅니다. 이제 A열의 주소 목록이 거리 이름별로 알파벳순으로 정렬됩니다.
이 버전은 첫 번째 공백 이전의 숫자만 추출하고 숫자 순서대로 정렬합니다.
문제 해결:
- 주소가 A열에 있는지 확인하거나 코드를 데이터 위치에 맞게 업데이트하세요.
- 데이터에 헤더가 포함된 경우 필요에 따라 Header = xlYes 헤더 행이 정렬되지 않도록 조정해야 할 수 있습니다.
- 대량의 VBA 코드를 실행하기 전에 항상 백업을 만드세요.
장점: 도우미 열이 필요 없으며, 대규모 데이터셋이나 반복적인 정렬에 적합합니다.
단점: 초기 설정에는 매크로 권한과 VBA에 대한 기본 이해가 필요합니다.
다른 내장 Excel 방법 - 도우미 열 없이 Power Query를 사용하여 주소 열을 분할하고 직접 정렬하기
Power Query(Excel 2016 이상 및 Microsoft 365에서 사용 가능)는 거리 번호와 거리 이름과 같은 구성 요소로 주소를 분할하는 유연하고 공식이 필요 없는 방법을 제공합니다. 이 솔루션은 공식과 도우미 열을 피하고 싶거나 기본 공식으로 효율적으로 처리할 수 없는 다양한 형식의 주소를 다룰 때 이상적입니다. Power Query는 데이터가 증가함에 따라 단계를 저장하여 쉽게 업데이트할 수 있습니다.
1. 주소 데이터를 선택하고 데이터 탭으로 이동한 다음 표/범위에서 (프롬프트가 표시되면 표를 생성하세요).
2. Power Query 창에서 주소 열을 선택한 다음 열 분할 > 구분 기호로 분할을 클릭합니다. 공백 을 구분 기호로 선택하고, 왼쪽에서 첫 번째 구분 기호를 선택합니다. 왼쪽에서 첫 번째 구분 기호 분할 위치 유형으로 설정합니다. 분할 위치 유형.
3이렇게 하면 주소가 두 열로 분할됩니다: 거리 번호와 남은 거리 이름/주소. 새 열의 이름을 필요에 맞게 변경하세요.
4정렬하려면 거리 이름 또는 거리 번호 열 머리글의 화살표를 클릭하고 오름차순 정렬 또는 내림차순 정렬.
5을 선택합니다. 닫기 및 로드 를 클릭하여 정렬된 결과를 다시 시트에 삽입합니다.
추가 팁:
- 주소 패턴이 일관되지 않은 경우 Power Query에서 사용자 정의 분할 또는 변환을 사용하여 열을 추가로 조작할 수 있습니다.
- Power Query 단계는 자동으로 기록되므로 소스가 변경되면 데이터를 쉽게 새로 고칠 수 있습니다.
- 이 방법은 원본 데이터를 변경하지 않으므로 원본 기록의 안전성을 높입니다.
장점: 시트에 영구적인 변경이 없음; 복잡한 주소 패턴에 강력하며 관리할 공식이 없습니다.
단점: Excel 2016 이상이 필요하며, 새로운 사용자는 인터페이스에 익숙하지 않을 수 있습니다.
요약 및 문제 해결 제안:
- 공식이나 VBA를 적용하기 전에 주소 형식의 일관성을 확인하세요.
- 특히 도우미 열이나 코드를 사용한 후에는 항상 정렬 결과를 미리 확인하여 정확성을 확인하세요.
- 예상치 못한 구조의 데이터(예: 번호나 거리 이름이 끝에 없는 경우)의 경우 공식을 조정하거나 더 강력한 분리를 위해 Power Query를 고려하세요.
- VBA 또는 고급 데이터 도구를 사용하기 전에 정기적으로 백업을 만들어 우발적인 데이터 손실을 방지하세요.
- 데이터 규모, Excel 버전 및 도구 사용에 대한 자신감에 가장 적합한 솔루션(공식, VBA, Power Query)을 선택하세요.
- 어떤 방법이 가장 좋은지 확실하지 않은 경우, Power Query는 가장 유연하며 비파괴적 편집에 가장 안전한 옵션입니다.
관련 기사:
최고의 오피스 생산성 도구
| 🤖 | Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행 | 코드 생성 | 사용자 정의 수식 생성 | 데이터 분석 및 차트 생성 | Kutools Functions 호출… |
| 인기 기능: 중복 찾기, 강조 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 또는 셀 병합 | 반올림(수식 없이) ... | |
| 슈퍼 LOOKUP: 다중 조건 VLOOKUP | 다중 값 VLOOKUP | 다중 시트 조회 | 퍼지 매치 .... | |
| 고급 드롭다운 목록: 드롭다운 목록 빠르게 생성 | 종속 드롭다운 목록 | 다중 선택 드롭다운 목록 .... | |
| 열 관리자: 지정한 수의 열 추가 | 열 이동 | 숨겨진 열의 표시 상태 전환 | 범위 및 열 비교 ... | |
| 추천 기능: 그리드 포커스 | 디자인 보기 | 향상된 수식 표시줄 | 통합 문서 & 시트 관리자 | 자동 텍스트 라이브러리 | 날짜 선택기 | 데이터 병합 | 셀 암호화/해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵게/이탤릭/취소선 필터 등) ... | |
| 15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...) | 50+ 종류의 차트(간트 차트, ...) | 40+ 실용적 수식(생일을 기반으로 나이 계산, ...) | 19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...) | 12 가지 변환 도구(단어로 변환하기, 통화 변환, ...) | 7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...) | ... 등 다양 |
Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다. 가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...
Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다
- Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
- 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
- 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!
모든 Kutools 추가 기능. 한 번에 설치
Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.
- 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
- 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
- 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
- 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
- 최고의 가성비 — 개별 추가 기능 구매 대비 절약