У Чака есть рабочий лист, который в одном столбце содержит серию IP-адресов. Они имеют знакомый формат 192.168.2.1. Если он сортирует адреса, они не сортируются по номерам. Например, Excel помещает 192.168.1.100 между 192.168.1.1 и 192.168.1.2. Чак задается вопросом, есть ли способ отсортировать столбец IP-адресов, чтобы они отображались в правильной последовательности.

Это происходит потому, что Excel рассматривает IP-адрес как текст, а не как число или серию чисел. Есть несколько способов обойти проблему, некоторые из которых я рассмотрю в этом совете. Вам следует выбрать подход, который соответствует вашим потребностям, в зависимости от ваших данных и того, как вы их используете.

Один из подходов — убедиться, что каждый октет ваших IP-адресов состоит из трех цифр. (Октет — это каждая часть IP-адреса, разделенная точками.) Например, вместо адреса 192.168.1.1 вы должны использовать 192.168.001.001. Эта «передняя панель» заполняет каждый октет нулями, и, если все ваши 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")

Эта формула довольно длинная, но все же это единая формула. Поместите его в столбец рядом с вашими первыми IP-адресами (при условии, что адрес находится в ячейке A1), а затем скопируйте его на необходимое количество строк. Когда вы выполняете сортировку, сортируйте по столбцу B, и адреса будут в правильной последовательности.

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

Function FormatIP(IPAddr As String) As String     Dim Dot1 As Integer     Dim Dot2 As Integer     Dim Dot3 As Integer     Dim Octet1 As String     Dim Octet2 As String     Dim Octet3 As String     Dim Octet4 As String

Dot1 = InStr(1, IPAddr, ".", vbTextCompare)

Dot2 = InStr(Dot1 + 1, IPAddr, ".", vbTextCompare)

Dot3 = InStr(Dot2 + 1, IPAddr, ".", vbTextCompare)



Octet1 = Left(IPAddr, Dot1-1)

Octet2 = Mid(IPAddr, Dot1+1, Dot2-Dot1-1)

Octet3 = Mid(IPAddr, Dot2+1, Dot3-Dot2-1)

Octet4 = Mid(IPAddr, Dot3+1, Len(IPAddr))



FormatIP = Right("000" & Octet1, 3) & "."

FormatIP = FormatIP & Right("000" & Octet2, 3) & "."

FormatIP = FormatIP & Right("000" & Octet3, 3) & "."

FormatIP = FormatIP & Right("000" & Octet4, 3)

End Function

Таким образом, в Excel вы можете использовать UDF таким образом, предполагая, что ваш исходный IP-адрес находится в ячейке A1:

=FormatIP(A1)

Другой подход — просто разделить IP-адреса на отдельные столбцы, поместив каждый октет в свой столбец. Это легко сделать, если вы используете инструмент «Текст в столбцы» следующим образом:

  1. Убедитесь, что справа от ваших IP-адресов есть три пустых столбца.

  2. Выберите диапазон ячеек, содержащий IP-адреса.

  3. Откройте вкладку «Данные» на ленте.

  4. Щелкните инструмент Текст в столбцы в группе Инструменты для работы с данными. Excel запускает мастер преобразования текста в столбцы. (См. Рис. 1.)

  5. Выберите переключатель с разделителями.

  6. Щелкните Далее.

  7. Установите флажок рядом с надписью Другое и справа от флажка введите точку.

  8. Щелкните Готово.

После этого вы можете отсортировать четыре столбца, как обычно, сортировку чисел. Затем, когда вы захотите снова соединить IP-адреса, вы можете использовать такую ​​формулу:

=A1 & "." & B1 & "." & C1 & "." & D1

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13481) применим к Microsoft Excel 2007, 2010, 2013 и 2016.