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

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

Неправильная сортировка функцией Сортировка

Правильная сортировка

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

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

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

Сортировка 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 клавишу, и протяните маркер заполнения по ячейкам, чтобы применить эту формулу.

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

3. Оставьте выделенными вставленные значения и нажмите Данные > Сортировка по возрастанию.

4. В диалоговом окне Предупреждение о сортировке оставьте Развернуть выделение отмеченным.

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

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

Сортировка IP-адресов через 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

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

4. Нажмите OK и нажмите F5 клавишу, появится диалоговое окно, напоминающее выбрать диапазон для работы.

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

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

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

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

1. Выберите ячейки, которые вы используете, и нажмите Данные > Текст по столбцам. Смотрите скриншот:

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

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

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

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

3. Выберите все ячейки, содержащие IP-адреса и разделённые ячейки, и нажмите Данные > Сортировка.

4. В диалоговом окне Сортировка нажмите Добавить условия для сортировки данных из столбцов B до E (разделённых ячеек). Смотрите скриншот:

5. Нажмите OK. Теперь столбцы отсортированы.