Как отсортировать адреса по названию улицы/номеру в Excel?
При управлении списком адресов в Excel часто необходимо организовать или проанализировать данные, сортируя адреса либо по названию улицы, либо по номеру дома. Например, если вам нужно сгруппировать клиентов, проживающих на одной улице, или обработать доставки в порядке номеров домов, сортировка по этим компонентам является важной. Однако, поскольку типичные форматы адресов смешивают названия улиц и номера в одной ячейке, простая сортировка не даст ожидаемых результатов. В этой статье мы обсудим практические методы сортировки адресов по названию улицы или номеру дома в Excel, проанализируем их преимущества и области применения, а также предложим решения проблем и альтернативные варианты для различных потребностей пользователей.
Сортировка адресов по названию улицы с помощью дополнительного столбца в Excel
Сортировка адресов по номеру дома с помощью дополнительного столбца в Excel
Сортировка адресов по названию улицы с помощью дополнительного столбца в Excel
Чтобы отсортировать адреса по названию улицы в Excel, сначала нужно извлечь только названия улиц в дополнительный столбец. Этот подход прост и хорошо работает, когда формат адреса последовательный, например, "123 Яблочная улица". Он подходит для быстрых проектов или простых списков адресов.
1. Выберите пустой столбец рядом со списком адресов. Введите следующую формулу в первую ячейку вспомогательного столбца, чтобы извлечь название улицы:
=MID(A1,FIND(" ",A1)+1,255)(Здесь A1 ссылается на верхнюю ячейку ваших данных адреса — скорректируйте, если ваши данные начинаются в другом месте.)
После ввода формулы нажмите Enter затем перетащите маркер заполнения вниз, чтобы применить формулу ко всем строкам вашего диапазона адресов. Эта формула работает, находя первое пробел в каждом адресе, затем возвращая всё после этого пробела — название улицы и любые суффиксы. Убедитесь, что ваши адреса имеют одинаковую структуру; в противном случае формула может не разделить их должным образом.
2. Выделите весь вспомогательный столбец (столбец с извлеченными названиями улиц), затем перейдите на вкладку Данные и нажмите Сортировать по возрастанию. Это отсортирует названия улиц в возрастающем (алфавитном) порядке.
3. В появившемся диалоговом окне Предупреждение о сортировке выберите Расширить выделение, чтобы гарантировать, что полная информация об адресах останется вместе при сортировке.
4. Нажмите Сортировать. Ваш список адресов теперь будет переупорядочен на основе названий улиц, делая похожие улицы рядом.
Примечание: Этот метод лучше всего работает со стандартизированными форматами адресов. Если ваши ячейки адресов содержат нерегулярные шаблоны или несколько пробелов перед названием улицы, формулу, возможно, придется скорректировать. Всегда проверяйте несколько результатов на точность после использования формулы.
Преимущества: Простота, не требует дополнительных инструментов.
Недостатки: Зависит от согласованности форматирования; требуется дополнительная работа, если формат адреса различается.
Сортировка адресов по номеру дома с помощью дополнительного столбца в Excel
Если вам нужно отсортировать список адресов по числовому номеру дома — например, для назначения порядка доставки или определения соседних адресов — легко извлечь номер и использовать его для сортировки. Это также эффективно, даже если адреса находятся на разных улицах.
1. В пустой ячейке рядом со списком адресов введите следующую формулу для извлечения номера дома:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Где A1 — это первый адрес в вашем списке — скорректируйте по необходимости). Нажмите Enter после ввода. Эта формула работает, находя первый пробел и возвращая символы до него, преобразуя их в числовое значение. Если ваши адреса начинаются с цифр как номеров домов, эта формула будет работать правильно. Затем перетащите маркер заполнения вниз, чтобы применить формулу к остальной части списка.
2. Выберите созданный вспомогательный столбец, перейдите на вкладку Данные и нажмите Сортировать по возрастанию (или Сортировать от наименьшего к наибольшему для новых версий Excel).
3. В диалоговом окне Предупреждение о сортировке выберите Расширить выделение для сортировки полных строк.
4. Нажмите Сортировать для применения. Теперь ваши адреса отсортированы по извлеченному номеру дома.
Совет: Если вы предпочитаете сохранять номер дома как текст или вам не нужно выполнять числовую сортировку, вы также можете использовать:
=LEFT(A1,FIND(" ",A1)-1)Эта версия извлекает число как текстовую строку.
Меры предосторожности: Если адреса начинаются со слов вместо чисел (например, «Главная улица 5»), эти формулы не будут работать должным образом. Перепроверьте свои адресные данные перед использованием формулы.
Преимущества: Быстро и удобно для пользователя, если формат адреса простой.
Недостатки: Не справляется с адресами, где перед номером идут названия/суффиксы, а также с адресами с несколькими числами.
Код 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 без вспомогательных столбцов
Power Query, доступный в современных версиях Excel (Excel 2016 и выше, а также Microsoft 365), предоставляет гибкий способ разделения адресов на составляющие, такие как номер дома и название улицы, без использования формул. Это решение идеально, если вы предпочитаете избегать формул и вспомогательных столбцов, или если ваши адреса следуют разным форматам, которые базовые формулы не могут эффективно обрабатывать. Power Query также может сохранять ваши шаги, позволяя обновлять их по мере роста данных.
1Выберите ваши адресные данные и перейдите на вкладку Данные затем выберите Из таблицы/диапазона (создайте таблицу, если вас попросят об этом).
2В окне Power Query выберите ваш столбец с адресами, затем нажмите Разделить столбец > По разделителюВыберите Пробел в качестве разделителя, и выберите первый левый разделитель для Разделить на тип.
3Это разделит адрес на два столбца: номер дома и оставшееся название улицы/адрес. Переименуйте новые столбцы по необходимости.
4Чтобы отсортировать, нажмите стрелку в заголовке столбца либо столбца с названиями улиц, либо столбца с номерами домов и выберите Сортировать по возрастанию или Сортировать по убыванию.
5Нажмите Закрыть и загрузить чтобы вставить отсортированные результаты обратно в вашу таблицу.
Дополнительные советы:
- Если ваш шаблон адреса не последователен, вы можете дополнительно манипулировать столбцами в Power Query с помощью пользовательских разделений или преобразований.
- Шаги Power Query автоматически записываются; вы можете легко обновить данные, если источник изменится.
- Этот метод не изменяет ваши исходные данные, повышая безопасность для оригинальных записей.
Преимущества: Нет постоянного изменения вашего листа; надёжен для сложных шаблонов адресов; нет формул для управления.
Недостатки: Требуется Excel 2016 или новее; интерфейс может быть незнаком новым пользователям.
Обзор и предложения по устранению неполадок:
- Не забудьте проверить согласованность формата адреса перед применением формул или VBA.
- Всегда просматривайте результаты сортировки, чтобы подтвердить правильность, особенно после использования вспомогательных столбцов или кода.
- Для данных с неожиданной структурой (например, отсутствующие номера или названия улиц в конце) скорректируйте формулы или рассмотрите Power Query для более надёжного разделения.
- Регулярно создавайте резервные копии перед использованием VBA или продвинутых инструментов данных, чтобы избежать случайной потери данных.
- Выберите решение (формулы, VBA, Power Query), которое лучше всего соответствует вашему объёму данных, версии Excel и уровню комфорта с инструментом.
- Если вы не уверены, какой метод лучше, Power Query часто предлагает наибольшую гибкость и является самым безопасным для недеструктивного редактирования.
Связанные статьи:
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек