0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Как получить ячейки в Excel, которые содержат IP-адреса для правильной сортировки?

Сейчас я имею дело с тысячами IP-адресов.

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

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

введите описание изображения здесь

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

введите описание изображения здесь

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

Можно ли достичь этой цели каким-то другим способом?

Как вы, возможно, заметили, ваши IP-адреса рассматриваются как текст, а не как цифры. Они упорядочены как текст, то есть адреса, начинающиеся с «162», предшествуют адресам, начинающимся с «20». (Потому что «1» идет перед «2».

Вы можете использовать формулу, представленную в этом ответе: https://stackoverflow.com/a/31615838/4424957, чтобы разделить IP-адрес на его части.

Как показано ниже, если ваши IP-адреса находятся в столбце A, вам следует добавить столбец BE.

введите описание изображения здесь

В ячейке B2 и скопируйте его в столбцы BE во всех строках, чтобы получить четыре части каждого IP-адреса. Чтобы отсортировать весь диапазон, отсортируйте столбцы с B по E (в таком порядке), как показано ниже:

введите описание изображения здесь

Если вы не хотите видеть вспомогательные столбцы (BE), вы можете скрыть их.

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

Вы должны выбрать столбец IP-адрес, затем вставить текст в столбец команды «.

В соседнем столбце введите следующую формулу

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

И наконец, сортировка по возрастанию.

См. скриншот.

введите описание изображения здесь

NB:

Красный — это исходный IP-адрес (в столбце A).

Читайте так же:
Как автозаполнить текстовое поле при вводе в Excel?

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

ЧерныйПосле применения цвета происходит конкатенация и сортировка (столбец F).

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

Я надеюсь, что это поможет вам.

Некоторое время назад я написал функцию VBA для решения той же проблемы. Адрес IPv4 генерируется в аддитивной форме, которая имеет правильный порядок сортировки.

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

Результат

Формулы

Вы можете сортировать и скрывать колонку Sortable.

Вот решение, которое занимает 1 столбец вашей таблицы и преобразует адреса IPv4 в систему нумерации по основанию 10.

Данные, которые вы помещаете в столбец M, начинаются в ячейке M1 (метка M2). Инкапсуляция в виде кода приводит к ужасному беспорядку, поэтому я использовал 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. Для этого используйте Get & Transform в Excel 2016 и PowerQuery в Excel 2010 или 2013.

  1. Перенесите таблицу в редактор PowerQuery.
  2. Дублируйте колонку, щелкнув правой кнопкой мыши на «Дублировать колонку»
  3. Разделите колонку на вкладке Главная. Выберите «Каждое вхождение разделителя»
  4. Отсортируйте каждый столбец Asc. слева направо.
  5. Выберите ранее разделенные колонки, щелкните правой кнопкой мыши и удалите, закройте и загрузите.
Читайте так же:
Как быстро преобразовать таблицу массивов в таблицу списка в Excel?

Это аналогичная строка, которая преобразует октеты в трехзначные поля, позволяя правильно сортировать.

10.1.0.15 становится 10001000015.

Как показано в вопросе, в столбце 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 , и заполните вниз:

Сортировка по столбцу N

Explaination

С помощью SUBSTITUTE, используя точку . с 6 пробелами, мы получаем следующее, чтобы оно могло быть извлечено правильно:

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

x

x

x

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

Функция сортировки Excel обычно используется для сортировки строк. Это может привести к неправильному порядку сортировки, если мы используем функцию сортировки напрямую, как показано ниже. Теперь я могу быстро и правильно сортировать IP-адреса в Excel, используя несколько методов.

Неправильная сортировка по функции сортировкиПравильная сортировка
сортировка документов по IP 1сортировка документов по IP 2
Сортировать IP-адрес по формуле

Используйте формулу, чтобы заполнить IP-адрес и сортировку.

1. Выберите ячейку рядом с IP-адресом и введите в нее формулу.

пресс Перейти и перетащите маркер заполнения над ячейками, чтобы применить эту формулу.
сортировка документов по IP 3

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

сортировка документов по IP 4
сортировка документов по IP 5

3. Не снимая выделения с вставленного значения, нажмите Данные > Сортировка от А до Я.
сортировка документов по IP 6

4. в Сортировка товаров диалог, держать Расширить выбор проверено.
сортировка документов по IP 7

5. щелчок Сортировать. Теперь IP-адреса отсортированы по возрастанию.
сортировка документов по IP 2

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

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

1. Нажмите Alt + F11 ключи для включения Microsoft Visual Basic для приложений окно.

Читайте так же:
Как быстро определить или извлечь AM и PM из строки DateTime в Excel?

2. Вставьте код в пустой сценарий, выбрав Вставка > Модули.

VBA: заполнить IP-адрес

сортировка документов по IP 8

3. Затем нажмите Инструменты > Справка и во всплывающем диалоговом окне установите флажок Регулярные выражения Microsoft VBScript 5.5.

сортировка документов по IP 9
сортировка документов по IP 10

4. Нажмите OK и нажмите F5 появится диалоговое окно с напоминанием о выборе диапазона для работы.
сортировка документов по IP 11

5. Нажмите OK. Затем IP-адреса заполняются нулями.

6. Выберите IP-адреса и нажмите Данные > Сортировка от A до Z, чтобы отсортировать их.

Сортировка IP-адреса по тексту в столбцы

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

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

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

Чтобы проверить разделительный, нажмите кнопку Далее;

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

Установите флажок рядом с IP-адресом для размещения результата. Нажмите Готово.

сортировка документов по IP 13
сортировка документов по IP 14
сортировка документов по IP 15

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector