Как получить ячейки в Excel, которые содержат IP-адреса для правильной сортировки?
В настоящее время я работаю с большим списком IP-адресов (их тысячи).
Однако, когда я сортирую столбец, содержащий IP-адреса, они не сортируются интуитивно понятным или простым способом.
Например, если я введу IP-адреса следующим образом:
И тогда, если я сортирую в порядке возрастания, я получаю это:
Есть ли способ для меня отформатировать ячейки так, чтобы, например, IP-адрес 17.255.253.65 появился после 1.128.96.254 и до 103.236.162.56 при сортировке в порядке возрастания?
Если нет, есть ли другой способ для меня достичь этой конечной цели?
8 ответов
Как вы, возможно, поняли, ваши IP-адреса рассматриваются как текст, а не как числа. Они сортируются как текстовые, что означает, что адреса, начинающиеся с "162", будут предшествовать адресам, начинающимся с "20". (потому что символ "1" предшествует символу "2".
Вы можете использовать формулу, представленную в этом ответе: https://stackoverflow.com/a/31615838/4424957 чтобы разделить IP-адрес на его части.
Если ваши IP-адреса находятся в столбцах A, добавьте столбцы BE, как показано ниже.
Введите формулу
=VALUE(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",999)),(B$1)*999-998,999)))
в ячейке B2 и скопируйте его в столбцы BE во всех строках, чтобы получить четыре части каждого IP-адреса. Теперь рассортируйте весь диапазон по столбцам от B до E (в указанном порядке), как показано ниже:
Если вы не хотите видеть вспомогательные столбцы (BE), вы можете их скрыть.
Самое простое, трехэтапное решение, которое я могу предложить вам,,,
Выберите столбец IP-адрес, примените команду " Текст к столбцу".
В соседней колонке напишите эту формулу
= СЦЕПИТЬ (В3,"", С3,"",D3,"", Е3)
Наконец сортировка в порядке возрастания.
Проверьте снимок экрана.
NB:
Красный - это оригинальный IP-адрес (в столбце A).
Зеленый после применения текста к столбцу (столбец от B до E).
После нанесения черного цвета происходит конкатенация и сортировка (столбец F).
Причина заключается в том, что изначально IP-адрес очень прост: текстовые данные, и Excel не принимает формат ячейки, чтобы превратить его в номер.
Надеюсь, это поможет вам.
Вот функция VBA, которую я написал некоторое время назад для решения той же проблемы. Он генерирует версию IPv4-адреса с добавками, которая сортируется правильно.
Function SortAddress(Address As String) ' format address as XXX.XXX.XXX.XXX to permit sorting
Dim FirstByte As Integer, LastByte As Integer, I As Integer
SortAddress = ""
FirstByte = 1
For I = 0 To 2 ' process the first three bytes
LastByte = InStr(FirstByte, Address, ".") ' find the dot
' append the byte as 3 digits followed by dot
SortAddress = SortAddress & Format(Mid(Address, FirstByte, LastByte - FirstByte), "000\.")
FirstByte = LastByte + 1 ' shift the start pointer
Next I
SortAddress = SortAddress & Format(Mid(Address, FirstByte), "000") ' process the last byte
End Function
Простой пример:
Результат
Формулы
Вы можете отсортировать по столбцу "Сортируемый" и скрыть его.
Вот ответ, который займет только 1 столбец вашей таблицы и преобразует адрес IPv4 в нумерацию с основанием 10.
Поскольку вы помещаете свои данные в столбец "M", это начинается в ячейке M2 (метка M1). Инкапсуляция в виде кода дает один ужасный беспорядок, поэтому я использовал blockquote:
= INT (ВЛЕВО (M2, FIND(".", M2) - 1)) * 256 ^ 3 + INT(MID(M2, FIND(".", M2) + 1, FIND(".", M2, FIND(".", M2) + 1) - FIND(".", M2)-1)) * 256 ^ 2 + INT(MID(M2, FIND(".", M2, FIND(".", M2) + 1) + 1, FIND(".", M2, FIND(".", M2, FIND(".", M2) + 1) + 1) - FIND(".", M2, FIND(".", M2) + 1) - 1)) * 256 + INT(ВПРАВО (M2, LEN(M2) - НАЙТИ (".", M2, НАЙТИ (".", M2, НАЙТИ (".", M2) + 1) + 1)))
Не совсем легко читаемая формула, но вы можете просто скопировать и вставить в свою ячейку (предпочтительно N2 или что-то еще в той же строке, что и ваш первый IP-адрес). Это предполагает правильное форматирование IP-адреса, так как исправление ошибок в формуле сделает его еще хуже при разборе человеком.
Если вы не хотите использовать формулы или VBA, используйте Power Query. (В Excel 2016, Get & Transform, в Excel 2010 или 2013 установите надстройку PowerQuery, чтобы следовать ей).
- Перенесите таблицу в редактор PowerQuery.
- Дублируйте столбец, щелкнув правой кнопкой мыши "Дублировать столбец"
- "Разделить столбец" разделителем на вкладке "Главная". Выберите "Каждое вхождение разделителя"
- Сортировать каждый столбец Asc. слева направо.
- Выберите ранее разделенные столбцы, щелкните правой кнопкой мыши и удалите, закройте и загрузите.
Это похожая строка, которая преобразует октеты в 3-значные поля, что позволяет выполнять надлежащую сортировку.
10.1.0.15 становится 10001000015,
=LEFT(B85, FIND(".", B85) - 1) * 1000000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 1)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - FIND(".", B85) - 1) * 1000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 3)) - FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - 1) * 1000
+ RIGHT(B85, LEN(B85) - FIND("x", SUBSTITUTE(B85, ".", "x", 3)))
Как показано в вопросе, в столбце M указаны IP-адреса (IPv4), начиная с M2.
Получив хорошие ответы от каждого, вот мое решение. Требуется только 1 вспомогательный столбец. Мы пытаемся отформатировать адреса IPv4 в 012.198.043.009 отформатировать и затем отсортировать их:
12.198.43.9в12 198 43 9затем012.198.043.009
Отформатируйте адреса IPv4 в
012.198.043.009отформатируйте, введя в N2, и заполните вниз:= TEXT( LEFT(SUBSTITUTE(M2, ".", " "), 3 ), "000") & "." & TEXT( MID(SUBSTITUTE(M2, ".", " "), 8, 5 ), "000") & "." & TEXT( MID(SUBSTITUTE(M2, ".", " "), 15, 7), "000") & "." & TEXT(RIGHT(SUBSTITUTE(M2, ".", " "), 3 ), "000")Сортировать по столбцу N
Explaination
От SUBSTITUTE в точку . с 6 пробелами мы получаем следующее, чтобы их можно было правильно извлечь:
|123456789|123546789|123456789|
1.1.1.1 -> 1 1 1 1
11.11.11.11 -> 11 11 11 11
111.111.111.111 -> 111 111 111 111
=1= ==2== ===3===
- Символ 1-3 содержит и содержит только первую часть.
- Символ 8-12 содержит и содержит только вторую часть.
- Символ 15-21 содержит и содержит только третью часть.
- Самые правые 3 символа содержат и содержат только четвертую часть.
А затем, извлеките и отформатируйте каждую часть TEXT(..., "000"),
Если вы используете последнюю версию Excel в Windows, вы можете использовать следующую формулу для вычисления 32-разрядного десятичного значения IP-адреса.
=SUMPRODUCT(FILTERXML("<I><o v="""&SUBSTITUTE([@ipbase],".","""/><o v=""")&"""/></I>","//o/@v"),{16777216;65536;256;1})
Затем вы можете сортировать, используя производное десятичное значение. Замена [@ipbase] с местоположением ячейки действительного IP4-адреса.
Функция "FILTERXML" работает только в Windows, поэтому вам не повезло, если вы используете другую операционную систему.
У меня также есть одна строка, которая даст вам IP в мягком формате (000.000.000.000) для альфа-сортировки.
= TEXT(MID(A1,1,FIND(".",A1)),"000") & "." &
TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-1-FIND(".",A1)),"000") & "." &
TEXT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-1-FIND(".",A1,FIND(".",A1)+1)),"000") & "." &
TEXT(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)),"000")