Как быстро отсортировать IP-адреса по возрастанию в Excel?
Обычно для сортировки текстовых данных в 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 и перетащите маркер заполнения на нужные ячейки, чтобы применить эту формулу.
2. Скопируйте результаты формулы и вставьте их как значения в следующий столбец. См. скриншот:
 |
 |
3. Оставьте вставленные значения выделенными и нажмите Данные > По возрастанию.
4. В диалоговом окне Предупреждение о сортировке оставьте выбранной опцию Расширить выделенный диапазон.
5. Нажмите кнопку Сортировка. Теперь IP-адреса отсортированы по возрастанию.
Вспомогательные столбцы можно легко удалить.
Сортировка 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
3. Затем выберите Сервис > Ссылки и установите флажок напротив Microsoft VBScript Regular Expressions 5,5 во всплывающем диалоговом окне.
 |
 |
4. Нажмите кнопку ОК, затем — клавишу F5. После этого появится диалоговое окно с запросом на выбор диапазона для обработки.
5. Нажмите кнопку ОК. Теперь к IP-адресам добавлены нули.
6. Выделите IP-адреса и нажмите Данные > По возрастанию, чтобы отсортировать их.
Сортировка IP-адресов с помощью «Текст по столбцам»
На самом деле, функция «Текст по столбцам» тоже может вам пригодиться в Excel.
1. Выделите нужные ячейки и выберите Данные > Текст по столбцам. См. скриншот:
В диалоговом окне Мастер преобразования текста в столбцывыполните следующие действия:
Установите переключатель С разделителямии нажмите кнопку Далее;
Установите флажок Другоеи введите символ .в поле ввода, затем нажмите кнопку Далее;
Выберите ячейку рядом с IP-адресом для размещения результата и нажмите кнопку Готово.
 |
 |
 |
3. Выделите все ячейки с IP-адресами, перейдите на вкладку Данные и нажмите Сортировка.
В диалоговом окне Сортировка щёлкните Добавить условия, чтобы отсортировать данные по столбцам B–E («Разделить ячейки»). См. скриншот:
5. Нажмите кнопку ОК. Теперь столбцы отсортированы.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек