Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как быстро отсортировать IP-адреса от меньшего к большему в Excel?

Author Sun Last modified

Обычно мы используем функцию Сортировка для сортировки строк в Excel. Однако, если нужно отсортировать IP-адреса, порядок сортировки может быть неправильным при использовании функции Сортировка напрямую, как показано на скриншоте ниже. Сейчас я предложу несколько способов быстрой и правильной сортировки IP-адресов в Excel.


Сортировка IP-адресов по формуле

Используйте формулу для заполнения IP-адресов, а затем выполните сортировку.

1. Выберите ячейку рядом с IP-адресом и введите эту формулу

=TEXT(LEFT(A1,FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND( ".",A1,1)+1,FIND(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND(".",A1,FIND(".",A1,1)+1)+1,FIND(".",A1, FIND(".",A1,FIND(".",A1,1)+1)+1)-FIND(".",A1,FIND(".",A1,1)+1)-1), "000") & "." & TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND( ".",A1,1)+1)+1)),"000")

нажмите Enter клавишу, и протяните маркер заполнения по ячейкам, чтобы применить эту формулу.
A screenshot of Excel with a helper formula to standardize IP addresses

2. Скопируйте результаты формулы и вставьте их как значения в следующий столбец. Смотрите скриншот:

A screenshot of the Paste Values Only option in Excel
A screenshot of pasted values for sorting IP addresses

3. Оставьте выделенными вставленные значения и нажмите Данные > Сортировка по возрастанию.
A screenshot of Sort A to Z button in Excel

4. В диалоговом окне Предупреждение о сортировке оставьте Развернуть выделение отмеченным.
A screenshot of Sort Warning dialog with Expand the Selection option

5. Нажмите Сортировка. Теперь IP-адреса будут отсортированы от меньшего к большему.
A screenshot showing correct IP address sorting result

Вы можете удалить вспомогательные столбцы.


Сортировка IP-адресов через VBA

Вот код VBA, который также может помочь вам.

1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. Нажмите Вставка > Модуль, скопируйте и вставьте код в пустой скрипт.

VBA: Заполнение IP-адресов

Sub FormatIP()
'UpdatbyExtendoffice20171215
    Dim xReg As New RegExp
    Dim xMatches As MatchCollection
    Dim xMatch As Match
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim xArr() As String
    On Error Resume Next
    Set xRg = Application.InputBox("Select cells:", "KuTools For Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    With xReg
        .Global = True
        .Pattern = "\d{1,3}\.+\d{1,3}\.+\d{1,3}\.+\d{1,3}"
        For Each xCell In xRg
            Set xMatches = .Execute(xCell.Value)
            If xMatches.Count = 0 Then GoTo xBreak
            For Each xMatch In xMatches
                xArr = Split(xMatch, ".")
                For I = 0 To UBound(xArr)
                    xArr(I) = Right("000" & xArr(I), 3)
                    If I <> UBound(xArr) Then
                        xArr(I) = xArr(I) & "."
                    End If
                Next
            Next
            xCell.Value = Join(xArr, "")
xBreak:
        Next
    End With
End Sub

A screenshot of Excel's VBA editor with IP address sorting code

3. Затем нажмите Инструменты > Ссылки и отметьте Microsoft VBScript Regular Expressions 5.5 в появившемся диалоговом окне.

A screenshot showing Microsoft VBScript Regular Expressions reference option
A screenshot showing the selection of VBScript Regular Expressions in Excel VBA

4. Нажмите OK и нажмите F5 клавишу, появится диалоговое окно, напоминающее выбрать диапазон для работы.
A screenshot showing the VBA range selection dialog for IP address sorting

5. Нажмите OK. После этого IP-адреса будут заполнены нулями.

6. Выберите IP-адреса и нажмите Данные > Сортировка по возрастанию для их сортировки.


Сортировка IP-адресов через «Текст по столбцам»

На самом деле, функция «Текст по столбцам» также может помочь вам в Excel.

1. Выберите ячейки, которые вы используете, и нажмите Данные > Текст по столбцам. Смотрите скриншот:
A screenshot of Excel’s Text to Columns option for IP address splitting

2. В диалоговом окне Мастер преобразования текста в столбцы сделайте следующее:

Отметьте Разделители и нажмите Далее;

Отметьте Другое и введите . в текстовое поле, затем нажмите Далее;

Выберите ячейку рядом с IP-адресом для размещения результата. Нажмите Готово.

A screenshot of Text to Columns Wizard with Delimited option selected
A screenshot of Text to Columns Wizard with period as delimiter for IP address splitting
A screenshot of Text to Columns Wizard with destination cell selection

3. Выберите все ячейки, содержащие IP-адреса и разделённые ячейки, и нажмите Данные > Сортировка.
A screenshot of Excel with split IP addresses and Sort option

4. В диалоговом окне Сортировка нажмите Добавить условия для сортировки данных из столбцов B до E (разделённых ячеек). Смотрите скриншот:
A screenshot of Sort dialog for multiple levels in Excel

5. Нажмите OK. Теперь столбцы отсортированы.
A screenshot of sorted IP addresses in Excel after Text to Columns sorting


Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

Повысьте свои навыки работы в 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 Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек