34

В настоящее время я работаю с большим списком IP-адресов (их тысячи).

Однако, когда я сортирую столбец, содержащий IP-адреса, они не сортируются интуитивно понятным или легким для понимания способом.

Например, если я введу IP-адреса следующим образом:

И тогда, если я сортирую в порядке возрастания, я получаю это:

Можно ли отформатировать ячейки так, чтобы, например, IP-адрес 17.255.253.65 появлялся после 1.128.96.254 и до 103.236.162.56 при сортировке в порядке возрастания?

Если нет, есть ли другой способ для меня достичь этой конечной цели?

|источник

7 ответов7

42

Как вы, возможно, поняли, ваши 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), вы можете их скрыть.

|источник
10

Самое простое, трехэтапное решение, которое я могу предложить вам ,,,

  1. Выберите столбец IP-адрес, примените команду « Текст к столбцу» .

  2. В соседней колонке напишите эту формулу

    = СЦЕПИТЬ (В3, "", С3, "", D3, "", Е3)

  3. Наконец сортировка в порядке возрастания.

Проверьте снимок экрана.

NB:

Красный - это оригинальный IP-адрес (в столбце А).

Зеленый после применения текста к столбцу (столбец от B до E).

После нанесения черного цвета происходит конкатенация и сортировка (столбец F).

Причина заключается в том, что изначально IP-адрес очень прост: текстовые данные, и Excel не принимает формат ячейки, чтобы превратить его в номер.

Надеюсь, это поможет вам.

|источник
9

Вот функция 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

Простой пример:

Результат

Результат

Формулы

Формулы

Вы можете отсортировать по столбцу «Сортируемый» и скрыть его.

|источник
6

Вот ответ, который займет только 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-адреса, так как исправление ошибок в формуле сделает его еще хуже при разборе человеком.

|источник
3

Это похожая строка, которая преобразует октеты в 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)))
|источник
3

Если вы не хотите использовать формулы или VBA, используйте Power Query. (В Excel 2016, Get & Transform, в Excel 2010 или 2013 установите надстройку PowerQuery, чтобы следовать).

  1. Перенесите таблицу в редактор PowerQuery.
  2. Дублируйте столбец, щелкнув правой кнопкой мыши "Дублировать столбец"
  3. "Разделить столбец" разделителем на вкладке "Главная". Выберите "Каждое вхождение разделителя"
  4. Сортировать каждый столбец Asc. слева направо.
  5. Выберите ранее разделенные столбцы, щелкните правой кнопкой мыши и удалите, закройте и загрузите.
|источник
2

Как показано в вопросе, в столбце M указаны IP-адреса (IPv4), начиная с M2.

Получив хорошие ответы от каждого, вот мое решение. Требуется только 1 вспомогательный столбец. Мы пытаемся отформатировать адреса IPv4 в формат 012.198.043.009 , а затем отсортировать их:

  • 12.198.43.9 до 12 198 43 9 , затем до 012.198.043.009

  1. Отформатируйте адреса 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")

  2. Сортировать по столбцу 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") .

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .